Hi there! I need to create tables in a stored procedure where the name of this table is supplied in argument from outside of the stored procedure.. I mean : create table @mytable etc....in my sp So I wanted to declare my argument like this create proc myproc ( @mytable table(id int pr...more >>

hi, I am using DTS to export to a text file and i have a field that is a VARCHAR(8000) field. It is being truncated to 255. When I look at the destination tab in DTS, it says that the size is 8000. Any ideas? please advise. rafael ...more >>

I don't want to have to start over again at 3 billion or so when I hit it. ...more >>

Hi Friends, SQL 2000 If I change the port on SQL server , does it need a restart? Thanks, Pat ...more >>

Let's say that I have a database that has customer name, invoice date, and amount in it. If an entry exists in this database, then the company owes us money. I am trying to put together a report that, for each company, details how much is owed that is 30 days out from a given date, how much is ...more >>

I have a table like this FileID Line# Status1 status2 === ==== ===== ====== 1 0 10 1 1 1 10 1 1 2 10 ...more >>

Hello, I have an application database that has a role with specific permission to each objects like table, proc, views. There are about 200+ objects with different permission. some are select, insert, ddr. How can I write a SQL statement to show a report of this role with all the differe...more >>

I am trying to find the ISO 639-1 (two character code) Language list, prefereably with both the English and native names for each language. So far I have had no luck whatsoever. Does anybody know where I might obtain this list? TIA Michael MacGregor Database Architect ...more >>

What's the best way to change numeric column to Identity column? Table: Orders Field: OrderID (numeric) must be change to Identity Records: 120,000 Thanks, Walter ...more >>

In a table I have a column called ModifyDateTime which stores the last time that record was modified. When a record is inserted this column is updated through a default and when a record is updated a trigger is used. This is all working fairly well except I would like the ModifyDateTime column...more >>

In the following query I sometimes have DIVIDE BY ZERO issues. How can I put the CASE (SOME VARIABLE) WHEN 0 THEN 0 ELSE statement into the query so that I don't get the error. SUM(MedExp) is sometimes = 0 SUM(TotalMonths) is sometimes = 0 SUM(TotalMem) is sometimes = 0 I'm not sure how t...more >>

Hi, I have a scenario: CREATE TABLE [dbo].[Test1] ( [PersonID] [int] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Test2] ( [SpeechID] [int] IDENTITY (1, 1) NOT NULL , [PersonID] [int] NOT NULL ,...more >>

I have used the following query to get an age from datetime: select age from (select case when dateadd(year, datediff(year, birthdate, getdate()), birthdate) > getdate() then datediff(year, birthdate, getdate()) -1 else datediff(year, birthdate, getdate()) end as age from userprofile) as a...more >>

Hi, I have a scenario: CREATE TABLE [dbo].[Test1] ( [PersonID] [int] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Test2] ( [SpeechID] [int] IDENTITY (1, 1) NOT NULL , [PersonID] [int] NOT NULL ,...more >>

ok, i have a partitoned view of 5 years worth or partitioned monthly data. each undertable has a check constraint on a date column when i run this I get a proper execution plan (note i am reviewing the actual execution plan, not the estimated) select * from viewname where datecol = cas...more >>

Hi I have a date time field in my database and i want to do a query that will return just the time by its self. Whats the best of doing it? Thanks in advance ...more >>

I am trying to write a query where it will select records where they were created within the last 5mins. Each record will have a field with a time value (varchar (5) this field cannot be changed) to mark when the record was created. I need the query to select CURRENT_TIMESTAMP and then use th...more >>

I have data in one table of one database which has an AssetTag field and a SerialNumber field. The serial number data is populated, however the asset tag data is not populated. I have an external file that has the serial numbers mapped to the appropriate asset tags. (I can import the file to...more >>

hey all, i have a table with about 1300 rows the records are grouped by a type and i want to assign each type a numbering sequence. what's the best way to do this? For instance, Rec1 Type1 Seq1 Rec2 Type1 Seq2 Rec3 Type2 Seq1 Rec4 Type2 Seq2 Rec5 Type2 Seq3 The seq columns is what...more >>

Hi Everyone, We have a database that has numbers and we want to CONVERT all the numbers to 0. How would we go about writing a script that can do such a thing. Thanks in advance for everyones help. Jay :)...more >>

Hello, I have a query I'm working on in the Query Analyzer. My query has variables....I wanted to know if there is a way to track the variable and see its value (I.e. step through) is there a debug mode in the Query Analyzer? Thanks in advance. ...more >>

hey all, how would i clear values for a column for all rows but not delete the row? thanks, rodchar...more >>

Hello How can I update column 4 in my table? The field names in the table change regularly. I know the field I'm updating is column 4 from ....... select name from sysobjects where id in (select object_id('TEST')) and coliid=4 So something like update TEST set col4 ='a value' whe...more >>

We are trying to decide wether to place SQL Server 2000 on the IIS box or another separate box. What are the pros and cons of doing this? I'm thinking something like performance (shared memory vs. TCP), memory/cpu consumption of the SQL server. Currently the IIS box has an average CPU usage of ...more >>

This is a design question but I couldn't find the appropriate newsgroup for SQL Server Design so I am posting this here. I have 2 tables Customer and Company. Customer has fields like AntiVirusCompanyID, EmailCompanyID, SpamFilteringCompanyID and WebFilteringCompanyID . All these company ID'...more >>

Ok, maybe I am more lost than I realized. (I am a "new user" of SQL Server 2000 and posted this question in the "newusers" group, then thought that perhaps I should have posted it to "programming" instead. So, this is a delayed cross-post, sorry if you had to see it twice.) I have the d...more >>

Somehow my database log has (ldf file) has grown to over 60GB! and the database it is associated with is only 32MB, how can I compress this file? it seems to be getting larger by the hour and now disk space is running out.. thanks! ...more >>

Hi all, I know there is some way to do this that I am just not getting and I hope someone here can help me. I have written the following query to return a group of records for a mailing list: SELECT DISTINCT Company ,NCCIRiskID ,PolicyNumber ,Address1 ,City ,State ,Zip ...more >>

my question has to do with the performance of user-defined function that returns a table variable vs. traditional temp tables. i have a function that consists of a select statement that populates and returns a table variable. the select is fairly complex and takes a siginificant amount of time...more >>

Hi, I am seeing some unusual behavior with log shipping (SQL Server 2000) and I can't find the cause. Typically we are seeing 20-30 MB every 15 minues in our TRN files. Today, the TRN files are now 100-600 MB every 15 minutes! I don't know of any changes in the server that could affect this. ...more >>

Hi We are trying to alert a user from a trigger in SQL Server. We are currently using a Trigger to produce an email to the user, which is not ideal due to the quantity of times the alert will be raised during the day. Any suggestions would be ideal. Thanks B ...more >>

MSDN says the following at http://msdn.microsoft.com/library/default.asp?url=/library/en- us/tsqlref/ts_aa-az_2gtz.asp: "ALTER VIEW permissions default to members of the db_owner and db_ddladmin fixed database roles, and to the view owner. These permissions are not transferable. To alte...more >>

Hi, I currently have a column in a table that is a default of getdate()). My question : Is there a way to capture just the date without the time appended? For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexes on this datetimestamp field? Would a clustered index on th...more >>

Hey all, Figured I'd get everyone's input on this. The stored proc below works fine, no errors, however the ASP.NET page which calls it takes forever to load (it averages 25 seconds per search). Anyone have any insight on how I can boost the speed? 25-35 seconds is too dang long. For those wh...more >>

I am posting this a second time only since it got grouped with another old thread on Google Groups. They really need to fix that auto-grouping by subject routine. The query below is taking 3-4 seconds to run under a light load, which seems to be a bit lengthy for the indexes that are in place ...more >>

Does SQL Server take time zones into account when writing a time like this to the database: Update [Trials] Set [Edited]='10:00' Where [ID]=1 We're currently in BST (British Summer Time) here in the UK, so 10:00 is GMT+1, i.e. 09:00 GMT. Does the SQL Server store 10:00 or 09:00 and convert...more >>

The query below is taking 3-4 seconds to run under a light load, which seems to be a bit lengthy for the indexes that are in place and the amount of data that exists in the tables. I have outlined everything below, including all table definitions, indexes, and row counts. Any help at all will be...more >>

Hi Everyone, I am a bit of a newbie to SQL, I have created a table to hold supplier invoice details which has three columns to hold currency values i.e. Nett Value VAT Value Gross Value The columns for VAT and gross calculate automatically from the Nett value, however the values in th...more >>

Dear Friends We have setup a server with replicaiton which will work as a backup server now the problem i have is permission on the objects as the same is created as new sql database there are no permission on the objects, I have created the same roles in the database now i want to give the...more >>

Hi, I'm hoping someone can help with a bit of a tricky SQL problem we're having. We're using MSSQL 2000. We're trying to implement a search routine for hotel availability, a simplified schema is given below (watch out for word wrapping!): --This table holds possible stock for various r...more >>

Hi folks What are the advantages of specifying more than one backup file in the backup operation. One advantage I know is, it can be spread across multiple hard drives if it is a huge database. Are there any other advantages for doing so. Thanks in advance. ----- *** Sent via Develo...more >>

Hi , I have one asp page that send sms to the users. i just want to send that sms in a particular time. Since i coudnt execute that asp page in a particular time, i just want to make use of sql server job Schedule. i used the sql server job to run that particular asp page using xp_cmdshel...more >>

Hi everybody, In my program, I use the following statement to get a quick count of the records in a given table: SELECT [rows] FROM sysindexes WHERE [indid] = 0 AND [name] = 'table_name' I don't know if it's just a bug with SQL, but it sometimes happens that when there are changes to a ...more >>

Hi, I need some help. I've spent nearly a week of work and lunch time trying to crack this nut, and I guess I am finally stuck. I have a table with financial data like invoices. The tables are huge. Hundreds of millions of rows per year. I want to store some additional information related to g...more >>

...more >>

Hi I'm developing an ASP app to be the new front end to an existing SQL Server DB. The db has tables for Exhibitions, Venues and Organisers (amongst other things). Because it is a legacy db there are tables which contain old data and I have taken the decision to delete Exhibitions held 2 year...more >>

I have inherited a database and system where tables have like four fields at the end that must remain at the end. So, if I need to add new fields to a table, I need to add them before those fields. The problem is that, when I do this, it "breaks" views and queries that have "SELECT *" from th...more >>