To use sp_trace_setevent [ @traceid = ] trace_id, where can I get the trace_id? Thanks Victor ...more >>

Is there anyway to return multiple columns from a sql function? I want to be able to return, for instance, one varchar value and one money value. Thanks...more >>

Hey Group I am trying to update a stored procedure which copies data from one database to another every day. Currently, it executes (both read and write) on the same server. I have moved 1 of the databases to another server and linked it to the original (using a linked server). Now I'd like...more >>

i run st. proc. which inserts/updates/deletes data in some tables and ocasionaly error ocures because of constraint violations or some triggers get trigger and 'RAISEERROR'. i'd like to log these errors to some other table and after each statement i ask if @@error<>0 begin --log error en...more >>

Hi all, I have a query that works, but it's very ugly. It uses three subqueries with NOT EXISTS, all nested in each other and all correlated. I'd like to know if it's possible to achieve the same result with a simpler query. Note: I'm not after tricks to improve performance on this one. I al...more >>

Hi , I'm going to just start a new post. Thanks again for all those who helped me. I would like to add this at the end of the current month: (current) and (month-to-date), see below print ' 2004 ' print ' ' Select DATENAME(MONTH, dateofex)...more >>

SQL 2K Sample data id move_type move_date ----------------------------- 1 A 07/01/2004 1 C 07/02/2004 1 E 07/05/2004 2 A 07/05/2004 2 B 07/05/2004 2 C 07/06...more >>

I have a table "Entries" that includes two contact fields ("Contact1" and "Contact2") which are int values pointing to an entry in a master "Names" table. "Contact1" always has a pointer to a valid Names entry while "Contact2" can be Null in some rows. As part of an email list process, I have ...more >>

I added several indexes and statistics to a SQL 2000 database, and the next day I found that 4 of the statistics no longer exist. Is there any way that the auto create/update statistics could have caused this? Or anything else, other than dropping them? In addition, what effect would th...more >>

I'm using SQL Server 2000. I need to execute one stored procedure for each record in the following: Select ID from Customers Where Australia = 1 so how do I make a new stored procedure that will call another procedure for each of those records returned in the above query, where the other pr...more >>

OH boy ... this is turning out to be a nightmare ... I rebooted Windows 2003 server, and it told me that my system configuration had changed significantly, and that I needed to reactivate the product. WTH? Nothing has changed. I opened up device manager, and ... THERE'S NOTHING THERE!...more >>

Hi, all, what is the correct syntax for this task: insert into table1 (field1,field2) values ('10','abcde') WHERE id = 'demo_01' and login = 'demo@demo.com' Thanks in advance!...more >>

I need to connect to a SQL server that has force protocol encryption checked. From my ASP.net application, or any other application for that matter, specifically in my connection string, what parameter do I need to use to take advantage of this encryption? Thanks!...more >>

Hi All, Can someone throw me an insight on this Running this query on PRODUCTION Db! SELECT * FROM Orders (NOLOCK) where OrderId=123 Is the NOLOCK Necessary? please explain. TIA ...more >>

Hi, I had a question concerning about nvarchar and varchar. When you open a design table, in the column length you put a number in it. It is the number of bytes or characters? In another word, i have a column called userName,whose datatype is nvarchar for the internationalization. I...more >>

I have a table that has FKs pointing to several other tables. Some of the FK values are invalid (other tables had to be restored and some of the entries pointed to by the FKs are now invalid). I remember seeing a query that identified FKs that were orphans but I cannot find that information now. ...more >>

Hello I was just wondering about a simple one....... I have a table with a column of type smallint, and most values are 0 in that column..... should i use where Foo>0 when i sum it up or not? any difference in performance? SELECT SUM(Foo) FROM Foo.....WHERE Foo>0 or SELECT SUM(Fo...more >>

What would be the best way of inserting 1000-5000 records into an SQL database (same table) very quickly? I know I can do it one at a time but I gotta think there's probably a more efficient solution than to run 1000s of separate insert commands. Any ideas? Thanks! ...more >>

If I create a linked server against an oracle server, again, sufficient access exist for the linked server security credential to write to the oracle database, is it possible to insert data into the oracle database? Thanks....more >>

Hi All, I am trying to write the following trigger and am getting an error. CREATE TRIGGER MakrAllJeopardiesClosed ON dbo.CIR_DATES FOR UPDATE AS IF NOT(DATE_Completed Is Null) BEGIN UPDATE JDATA SET JStatus = 'Closed' WHERE JDATA.RID = RID AND JStatus = 'Active' ROLLBACK END ---------...more >>

Yes, I want to create a loggin and set its password. Any ideas ? TIA ...more >>

SQL Server 2000 With the following DDL CREATE TABLE [MyTemp] ( [User] [varchar] (10) , [Job] [int] NULL , [JobDate] [datetime] NULL ) GO INSERT INTO MyTemp VALUES ('Fred',122, '1 Mar 2002') INSERT INTO MyTemp VALUES ('Fred',123, '1 Mar 2003') INSERT INTO MyTemp VALUES ('John',123,...more >>

I have table ORDERS: ID(identity 1,1) productID quantity ------------------------------------- 1 1 10 2 1 20 3 1 22 4 1 30 5 2 2 .... .... I wou...more >>

Hi All, I have the following requirement: I need to know if the current user is within a particular Role. Example: Create Procedure dbo.usp_Test_User AS SELECT (CASE WHEN USER_NAME In Good_Role Then '1' Else '0' END AS [InRole...more >>

I'm trying to create a Select statement that will give me the dates of all the Sundays in the year. 08/07/04 08/14/04 08/21/04 ..... and so on. I've looked at all the date functions and can't find one. Thanks. Big E ...more >>

Ok, I have a table with a createDt that has a default constaint. Then I have another field in the same table with JoinedDate, that is may or may not be populated. I also have a country code field as well as usernumber. I need to be able to figure out how many times somebody tried to join...more >>

[id] [HR] [casetype] A 1 casetype1 B 1 casetype1 C 1 casetype1 A 1 casetype2 B 1 casetype2 C 1 casetype2 A 1 <NULL> B 1 <NULL> C 1 <NULL> Hi, From the above table what sql statement can give me the following result [ID_GroupedBy] [HR_Sum] [casetype1_Count...more >>

Hello, I am trying to take a column that stores date and time and get fields that are dates without the time so I can use them as part of my sort with like dates grouped together, and sorted alphabetically by another field. I have been using the following: SELECT CAST(CONVERT(char(10),te...more >>

I need to return a recordset that is the equivalent to a query that returns a customer name and the last order they made (but not the previous orders). I know I have seen this here before, but I don't remember how this is done. Can someone please point me in the right direction? Thanks, ...more >>

The query below, given an employee and a year, returns sales totals by month, for that employee during that year. USE Northwind GO DECLARE @employeeID INT DECLARE @year INT SET @employeeID = 5 SET @year = 1996 SELECT DatePart(mm, OrderDate) AS Month, Sum(UnitPrice * Quantity - ...more >>

I have a table containing imported stuff from an external system. CREATE TABLE dbo.FileDump ( UserId varchar(50) null, Roles varchar(100) null ) The userid contains a userid. Roles contains data in the format "K2#G56#Fret" I need to split th...more >>

DDL and sample data CREATE TABLE [CustomerMaster] ( [ID] [int] NOT NULL , [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_CustomerMaster] PRIMARY KEY CLUSTERED ( [ID] ) ) CREATE TABLE [CustomerAddress] ( [ID] [int] N...more >>

Hi All, Got a question on select: I have a table call userCode, that has a field name code char(2): Table: userCode code: A1 A2 A3 A4 .... I need a select to return one record like this: A1,A2,A3,A4.... No with any loop or cursor. Any ideas? -- Raymond Yap ...more >>

I am inserting a new row into a table where a primary key value is automatically generated on insert. I need the value of the primary key to use as a foreign key in another table. How do I get that primary key after the insert? Thanks....more >>

Can anyone tell me how I can return what record is causing an error when executing "Insert Into ColumnList Select ColumnList" in a transaction. I'm currently using the following code to do this. BEGIN TRANSACTION DECLARE @IsError bit SET @IsError = 0 INSERT INTO database2.dbo.SomeTa...more >>

How can I Group By a Count? Essentially I need to be able to execute a query such as. SELECT A, B, C, COUNT(DISTINCT B), COUNT(C) FROM D GROUP BY A, B, C, COUNT(DISTINCT B) Spencer...more >>

Hi Sql Gurus, create temptab ( col1 col2 ) go Insert temptab exec SP go whenever there is a change in SP result set i need to recreate with correct table struture. Is there any way or some trick I can create the structure of temptab dynamically so that I no need to chan...more >>

Hi all, I can get command status from @result. DECLARE @result int EXEC @result = xp_cmdshell 'dir *.exe' IF (@result = 0) PRINT 'Success' ELSE PRINT 'Failure' Is there a way i can get the output ( The dir list)Thanks for your time.Steve ...more >>

Hi again, I need a third column to list the totals for each month before the where clause? Select datename(month, dateofex)as [Month 2004], count(pcid)as [FNA Count 2004] from patientclinical where (fna = 'y' or biopsies = 'y') and y...more >>

I have a feild in table with a default value is getdate() Every time I use a query on that table it shows the current date and time. I thought that if I set field's default value to getdate() when new row is inserted the it will be filled with the time of insertion. How can I fix that? TI...more >>

I am generating a varchar variable and for some reason it fails on the following line of the where clause, and (se.departureDateTime < dateadd(day,1,''+ @endDate +'')) The error recieved is 'Syntax error converting datetime from character string.' I had this problem in regards to other va...more >>

We have Sql2k on windows2003 (With sql2k servicepack3) I have a network drive Z: mapping to \\192.168.2.100 \xyfolder on this server When I try exec master.dbo.xp_cmdshell 'dir Z:' I am getting "The system cannot find the path specified." if I use UNC path,it is working fine exec m...more >>

How do you raise a number to a pwer in sql server? When I type: select 2^3 I get 1 instead of 8..whats the syntax? thanks...more >>

SQLCE Push pb I have a VB.net compact framework program written in VB. Net on a pocket pc 2003 that uses a SQL CE database. I'm using RDA to sync the ce db with a sql server db. I have SQL Server tables with "dbo" owner and some have specific owner. The tables with owner "dbo" ...more >>

I have a database with a variety of values in a field, let's say it's called Description. As common, users entered data in a variety of ways, sometimes they said File # 12345, sometimes they said File 12345, sometimes they said #12345, etc. I need to do a query that gives me the number of dif...more >>

Hi! I'm experiencing something strange... I have 2 databases who have the same indexes but when I execute a query on both, the first DB is fast but the second hangs... The execution plan are significally different for some tables that have the same indexes... I really need help to go ...more >>

Following code does not work for some reason. Does anyone know why and how to solve it? declare @y table (a int, b int null) insert into @y (a,b) values (1,10) insert into @y (a,b) values (2,20) declare @x table (a int, b int null) create table #x (a int, b int null) insert into @x (...more >>

I know I can use a CASE statement in the where clause of my query but I was wondering if you can specify what type of clause to use using the same CASE statement. For example the following WHERE clause works fine, and m.mediaid LIKE case when len(@media) = 1 then @media end My ...more >>

I need to be able to update records in a table without using a cursor (because of the speed issue - my actual database is very large). Here are some sample tables and data: --template header create table tmplt_head (th_id int identity, vndr_nm varchar(10) not null, acct_nbr int not null, ...more >>

Hello, Below is a stored procedure whose SQL has where clause with an IN statement, because I would like to pass in a list of values if the user wishes. The stored proc will work if I pass one value int strCount, but when I pass in a list to strCounty it throws an error. In other words, this ...more >>

Hi I have a sql query which returns a set of rows with one of the columns being Datetime datatype Can anybody tell me how to order rows by date in a sql query When I use the ORDER BY clause in the SQL query, it orders by it by month and then by day and then by year Thanks Jag...more >>

Hi, I'm trying to write a stored proc that will run an other stored proc and add a integer identity column to the results. Basicly i want to add a column that numbers each row. eg. new | Stored Proc Data column| | id | ClientNo Name 1 | 0002 ...more >>

I have declared a variable as a varchar which contains a number of values i.e. @MyVar = '1, 2, 4, 5' and I want to be able to use this as part of the filter for the data. Problem is that if I use an IN clause I am confronted with the error 'Syntax error converting the varchar value '1, 2,4, 5'...more >>

Dear all, How to write this query when we have the following table: ID Name 21234 A 34234 A e3423 G 21341 C 32423 C I want to count how many ID's the persons have.. The output must like: Name Total A 2 G 1 C 2 TIA...more >>