Hi, i am trying to create a generic trigger which will audit changes to data in a table. Now it is possible to know which column has been changed by using Update() or Column_update() But even with dynamic SQL i can't figure out how to insert the values i want into an audit table. ...more >>

I have a datetime column and wish to select all rows in a date range. Thus, I have SELECT * FROM MyTable WHERE MyDateTimeColumn BETWEEN '1 July 2007' AND '3 July 2007' The MyDateTimeColumn contains values for 1 July 2007 ranging from 00:00:00 to 23:59:59. It also contains similar values ...more >>

Hi! I'm looking for a way to make sure that the value of a primary key, unique-autoIncremental field in a table, will be above a certain number... Is there a standard way to do that? BTW I wanted also to ask: Is it the standard that auto-incremental fields start from 1? Thanks a lot! ...more >>

Hi, I've just read an article on-line about a funciton in sql 2005 called sys.fn_IsBitSetInBitmask. I want to use this logic on sql 2000 server. The author claims you can copy it from sql 2005 and move it to sql 2000. But how ? I can't find out how to view the contents of this function throu...more >>

Background: I have developed a Unit Testing framework on SQL Server 2000 (in T-SQL) which I am using to test a number of stored procedures. Typically, each test creates a temp table, populates it from a SP with the INSERT <table> EXEC <SP> syntax, then interrogates the temp table contents...more >>

I need to update a very large table (>25 million records), flagging 500,000 using an update statement. My update statement looks like this and I need to do this in 500k batches: UPDATE tablename SET flag = 1 WHERE flag = 0 I've tried using SET ROWCOUNT 500000, but it still seems to be pre...more >>

I am basically new to sql server 2000. In some columns I have the value that is exactly ' '. The value is single apostrophe. 1 space and another single apostrophe. Can you tell me how to look fof the value in columns that really look ' ' ??...more >>

I have a query where I have 3 fields.. UniqueID, StartDate, and UniqueID2. SELECT UniqueID, Min(StartDate) as MinStartDate, First(UniqueID2) as NewUniqueID FROM QueryA GROUP BY UniqueID ORDER BY UniqueID In Access this would give me the record with the oldest StartDate and then give me t...more >>

In SQL Server 2000, I have a curious situation where a stored procedure containing only a single Select statement is encountering a deadlock error ("Transaction (Process ID 616) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transactio...more >>

I have a hierarchy and table structures that I need to traverse and show all objects in all classes regardless of whether the relationship between classes is defined and valid. If I traverse the ProductRelationships table I am able to show valid relationships between classes based on the defi...more >>

I have a stored proc (see below) that is not returning the new VendorID when I execute it. Can someone point out what I am doing wrong? Thanks. For example: EXEC dbo.fd_insVendor 'New VendorName, 0 USE [Filedata] GO /****** Object: StoredProcedure [dbo].[fd_insVendor] Script Date...more >>

I just wanted to run this by you all and make sure I am understanding the cause of this behavior and maybe clarify things. Say you have a link server defined at "design time" - when you create the stored procedure. You connect (or call the new helper procedure) within a TRY block and CATCH an...more >>

Hello. In Sybase SQL Anywhere v5 I used to be able to query: SELECT IF condition THEN valuetrue ELSE valuefalse ENDIF FROM table How can I do something similar in MS SQL 2000? I've seen I can use CASE, but I think I need an IF because the condition I need to check is kind of : ...more >>

Hi, I am moving an application from Access to SQL Server. I had a date restrictor which calculated Last Weeks Sales (with Monday being the first day of the week) on a rolling basis. It is below Between DateValue(Now())-Weekday(DateValue(Now()),2) And DateValue(Now())-Weekday(DateValue(...more >>

Hello, I've run into a little problem running a query off of a linked server. The statement is generated using Dynamic SQL and this maybe the source of the problem. When i try to run set @sql ='SELECT * FROM myServer.DB005.DBO.SomeTable_xxx AS SomeTable2' Exec @sql I get Msg 7202, L...more >>

Hello: If SQL returns a negative number as a number in parentheses, what T-SQL syntax can be used to convert and display a negative symbol in front of the number instead of a surrounding set of parentheses? In other words, I need ($395.59) to show up as -395.59. Thanks! childofthe1...more >>

Just getting my first crack at 2005's try features, and coded something like this: begin tran begin try *cause error* commit tran end try begin catch raiserror(@msg,16,1) rollback tran end catch but it never got to the rollback! If I changed the order of the begins to: ...more >>

While examining a system, I found an SQLagent job that uses 'xp_sqlmaint'. Since the system also has a maintenance plan which does backups, I'm confused. The full step is: T-SQL, master DB, Command: EXECUTE master.dbo.xp_sqlmaint N'-PlanID 80F3F906-5350-4690-9372-8FFBE85E3B7B -WriteHi...more >>

Yesterday I was going through threads and found that Service#14 or something like that was using cpu. (SS 2000 SP4 and Win 2003) How can I find which service this maps too?...more >>

I have the following 3 tables: ProductClasses (PK - ProductClassID) ProductObjects (PK - ProdnodeID, ProductClassID) ProductRelationships (PK - ParentProdNodeID, ChildProdNodeID) I need to display the ProductClassName from the ProductClasses table for the ParentProdNodeID and ChildProdNode...more >>

Hi, I'm needing to develop an interactive guide for our department, where for set thresholds it would either continue or direct to a word document. Ie if required spending is < =A335,000 then the user would be directed to a webpage of information on how to proceed. If spending >=A335,000...more >>

Hi, In my apps, I've a stored proc which executed some dynamic request build like this : ' INSERT INTO ' + @STR_TABLE + ' SELECT ' + @STR_COLONNES + ' FROM ' + @STR_TABLE_SELECT + ' WHERE T = ' + @STR_T + ' AND ID = ' + @STR_ID The request is execute with EXECUTE([...]). When I look in t...more >>

I have a table partitioned on a key(column(s)). There are 367 partitions. There are clustered and non-clustered indexes on the table which are aligned. The average number of rows in each partition is 17 million. The number of rows in each partition also varies. For e.g. Partition 10 has 2 mil...more >>

Im posting this in both the .NET general group and SQL group since several other post mention that this may be a problem with ASP.NET and not SQL Out of the blue, any SQL 2005 SPs we have that execute dynamic SQL generate the following error: A severe error occurred on the current command....more >>

Hi, I have a DTS package (on SQL Server 2000) that calls an SP to get some data from teradata(using linked server). The DTS package will be scheduled to run everyday. Within my SP, I want to send emails in case any error occurs. I check @@error in a few places (where sql could fail) and wan...more >>

Within a vb.net application, I need to be able to find the default language for a user and report if it is not as expected. I have written a function in vb.net which does just this, it calls sp_helplogins and returns the 'language' from the results set. however it is very slow, taking 2 or 3 seco...more >>

If I added the sum(coinin) to my select statement like below, why does it give me the error Arithmetic overflow error converting expression to data type int.? SELECT id, coinin=(max(coinin) - min(coinin)), sum(coinin) as Total from BUFFER WHERE TransDate > '2007-09-22 07:59:59.999' and Tran...more >>

xp_cmdshell rows are returned as nvarchar(255). Is there any way to overcome this 255 limitation? Regards,...more >>

xp_cmdshell rows returned as nvarchar(255). Is there any way to overcome this limitation? Regards, ...more >>

3 SQL 2000 databases (Dev, Stage & live). Under the Options in EM, "Use Quoted Identifiers" is not selected on all 3. When I script a view using Enterprise manager, on the Dev, I get this line before the create statement SET QUOTED_IDENTIFIER ON GO Yet on Staging and live I get SET...more >>

Hi, I need a query to return the same row for multiple times. I'm using in statement for this query Table name:testv Column details vid varchar(2) vname varchar(10) Table creation and datas create table testv(vid varchar(2), vname varchar(5)) insert into testv values('1','mm') insert i...more >>

When running a query against a SQL 2000 sp3 database, I am having problems using the COALESCE function in a dynamic WHERE clause. I would expect the following statement: per.Surname LIKE COALESCE ('Ash%', per.Surname)) to return every row where the first three characters of the Surname...more >>

Hi There, i want to select distinct records form the DB and the column on which i will be appling distinct will come from the client application. to make things clear ,here i am giving some samples which depicts my problem. create table T1 (id int primary key, name nvarchar(50)) --this is m...more >>

I'm having an argument with someone and I'm wondering if someone could help me. How would you convert this query to an OUTER JOIN? SELECT * FROM demographics d WHERE NOT EXISTS ( SELECT m1.memberid FROM members m1 WHERE m1.memberid = d.membersid AND create_date ...more >>

Hi, I use the following sp (SQL 2005 Express) for my asp.net paging. CREATE PROCEDURE [dbo].[GetCategories_sp] @bActive bit, @iPageNumber int, @iPageSize int, @ioTotalRowCount int OUTPUT AS BEGIN SET NOCOUNT ON; with Temp as ( select row_number() over(order by SortOrder) as Row...more >>