HOW CAN I SELECT THE DATA FROM MYTABLE WHERE USERID = NULL ?? THANKS IN ADVANCE ...more >>

Hi All i have a select statement like this select pro_name, pro_sales, date_sales from inventory where date_sales < '01/01/2004 ' and date_sales < '03/31/2004' now i need to select another period to compare data select pro_name, pro_sales, date_sales from inventory where date_sal...more >>

Hi: A very easy question, I'm sure. When I issue a Select command using 'Distinct' what does the distinct actually refer to: the next field, all the following fields or what? TIA, Martin Hart. ...more >>

Im trying to do some dynamic sql here but when I write this: set @sqlwhere = @sqlwhere + '(art.artistid =' + @artistid +'and (art.inventoryid = subartist.inventoryid and subartist.artistid ' + '=' + @subartist +' )) ' and then: exec (@sqlstatement) I get this error message: Serv...more >>

I have a table like this: Tab1 (Group varchar(5), ID int, Value money) Here is a test data set: Group ID Value A 1001 100 A 1003 200 A 1004 300 B 1122 400 B 1123 500 B 1155 900 B 1166 800 Group pl...more >>

Hi, I have this query that returns the count of duplicate serial numbers (per SN) SELECT Count(cb.serialnumber) AS Cnt FROM db..tablename cb GROUP BY cb.serialnumber HAVING Count(cb.serialnumber) >= 2 Each duplicate SN listing still has a unique rowID in another column, What I woul...more >>

Hi, I have 2 questions about STATISTICS: Question1) Do I need to run UPDATE STATISTICS regularly or SQL server do that itself? Question2) Is statistics something that is being used by query optimizer or SQL server uses statistics after it decides to use an index to make index usage faste...more >>

Forgive the newbie programmer. I am assisting someone else with a project to automate their notifications (sans notification services, which seems like overkill). Specifically, the manager wants the day's logs (backup logs, etc., not tran logs) emailed to him on a nightly basis. Since SQL Mail...more >>

Hi, This error is raised on SQL 7: ODBC: Msg 0, Level 19, State 1 SqlDumpExceptionHandler: Process 28 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process The VIEW on which I execute the query is: CREATE VIEW vCumulatiefOverzicht AS SE...more >>

Good evening, I am tyring to use RAISERROR in UDF and getting ERROR 443 ("Invalid use of 'RAISEERROR' within a function"). Is using RAISERROR in UDF not supported in SQL Server 2000? Any input is appreciated. Vlad...more >>

Hi folks! :O) I have a strange bug right now.. Here's four queries I run on my server --- select o.name, o.id from sysobjects o where name like 'vwtime%' -- select object_name(1569440665) -- select object_id(object_name(1569440665)) -- select * from vwTimeSheet --- and here's...more >>

My problem is that I have two tables. One has registered users and the other has the number of times that they have tried to activate. Most activate in one try but others have to try several times. What I have is a userid, once in the member table. Possibly more then once in the Activations...more >>

Is the a performance difference between these 2 queries? #1 Select Count (*) From SomeTable Where PK=1 #2 Select Count (PK) From SomeTable Where PK=1 Does the use of PK instead of * affect the performance in any way? If so, why? Thanks! -- Joe Fallon ...more >>

If I have a table with an index on a single column and I write a query that uses 2 columns, which is a better strategy: ====================================================== Option #1 Add the 2nd column to the existing index so it becomes a covering index. =====================================...more >>

Hi, I am a bit confused. I am not sure it is even possible to do this, so any help would be appreciated. I am trying to write a stored procedure for SqlServer that will take a string as input. I would like to have this string be the entire Where stmt for a select, w/o the actual words where in...more >>

Hi, Let's say a query tries to convert UTC time into local time. The program can figure out the number of hours between UTC and local time, and construct the query appropriately. However, that is only valid for some dates - since due to daylights savings time, some dates will be an extra h...more >>

Can this be done? I would like to search for the correct stored procedure on SQL Server/MySQL... Thanks Jack ...more >>

Hi, I'm trying to match a word using the Like statement. For instance = if I was trying to match "fried", I would like this to occur "fried eggs" <-- would match "give me something fried" <-- would match "I like fried food" <-- would match "Siegfried" <-- would not match Usually when I ma...more >>

I want to trace a restore. I am using EM to do the restore and Profiler to trace it. The thing is after I start the trace and kick off the restore, I dont see anything getting traced. Can someone enlighten me on why this is happening? Thanks....more >>

Is there a way to query for the last 10 entries in a table? That is, I have an invoices table with a primary key of InvNum. I would like to be able to query for the last 10 entries or the last 20 entries. Is this possible? ...more >>

How do I get the previous Sun. Example if today Monday then I should previous week from Sun 8/1 - 8/7 Sat. The week start on Sunday and end on Sat of previous week. Thanks in advance. DECLARE @cDate DATETIME, @prevMonday DATETIME, @prevSunday DATETIME, ...more >>

Hello, A few days ago a few people replied to a question I posted about using Select in a While loop (thank you all for your replies). I was advised that cursors would be better than select. But then I read a post by someone that "Cursors are evil and should be avoided". Below are two ...more >>

Without details, or special circumstances, which form is better in SQL Server: SELECT * FROM B WHERE B.SomeID NOT IN ( SELECT SomeID FROM A) or SELECT B.*, A.SomeID FROM B LEFT JOIN A ON B.SomeID = A.SomeID WHERE A.SomeID IS NULL i want everything from "B" where a cor...more >>

Hi all- I have a series of queries that I need to loop over. The queries are below: Query #1 SELECT ClientKey, CompanyName, HttpHost, DBPrefix, DBUserID, DBPassword FROM Clients WHERE Status = 3 AND ClientKey != 0 I then need to loop over this query when using the next one: Query #2 ...more >>

Hi, all, I have a detail table like this: CustID, Recdate,mins 1001,6/2/2004,2.0 1001,6/10/2004,3.0 1001,6/17/2004,22.0 1001,6/22/2004,8,0 1001,6/30/2004,4.0 1002,6/3/2004,2.0 1002,6/11/2004,3.0 1002,6/18/2004,22.0 1002,6/23/2004,8,0 1003,6/30/2004,4.0 ...... I want to get summary of...more >>

I get the following error message: "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options" I've read mush of the online information, and i am aware of how to prevent this in CREATING the SP SET ANSI_NULLS ON SET ANSI_WARNINGS ON go <create sp> go SET ANSI_NULLS OFF ...more >>

I have an oddball problem. I've got two tables - one (call it TABLE_X) contains a single column with a list of words (e.g. "now","is","the","time",etc) and the other is several rows, each with several columns (we'll call it TABLE_Y), e.g. col1 = "Now is the time", col2 = "for all good men",...more >>

I haven't had to do any real DB design work since the SQL Server 6 days. Recently I ran into a design question and I hope someone can clarify it for me. I used to get my wrist slapped if I'd use a field of type varchar as a Primary Key on a table. What I was told was that it was "best practic...more >>

I'm able to use SQL-DMO to scrip database users and permissions, and use DMO to script database roles. But how do I script the assignment of a user to a role? Which DMO object and what script settings? This is easy in Enterprise Manager, but I'm interested in doing this via code with DMO. ...more >>

Is there a quick way to change the owner of all the stored procedures in a database? I have a few hundred stored procedures that I have to update and I want to avoid doing it all with sp_changeobjectowner. TIA! ...more >>

Hi to all. Please take a look at this simple code and the inline comments: -- start here -- declare @t varchar(2000) set @t='12345678901234567890' -- 20 chars set @t=@t+@t -- @t now contains 40chars set @t=@t+@t -- 80 set @t=@t+@t -- 160 set @t=@t+@t -- 320 set @t=@t+@t -- 640 chars ...more >>

Hi, Does anyone know if there's a SQL command to ROLLBACK. As a MS SQL user, can I rollback changes done to the database? Cheers Marc...more >>

how can i determine if a specific transaction exists? i have some error handling logic in my procedure that uses the rollback transaction function if an error is found - however, this statement fails when the transaction doesn't exist yet. i know i could solve this by moving the BEGIN TRANSACT...more >>

I have (conceptually) a two-dimensional data array in tables and am wondering if it is possible to get SQL-Server to return the two-dimensional data. I have a C# implementation which retrieves the data with multiple queries and creates the datatable I need but I'm hoping for stored-procedure so...more >>

I have a function where I am battling a bit with the calculations... In Excel the Function would look like this; =((G4*AE4)+(Q4*AF4)+(R4*AG4))/AD4 That formatted into fields it would be: =((Engine*service250)+(TotalLube*service1000)+(Hydraulic*service2000)/(Utili zationPAnnum) Now I used t...more >>

Hello, I'm trying to find some information on best practices for when you need to insert multiple records at a time into a database. One example; I have a database that stores "Articles" and each Article can have many "Keywords". I use a separate table to keep track of the matchings bet...more >>

Now that I've found all the duplicate entries in a table, how do I delete the duplicate records? Find duplicates works just fine: select lname, fname, address from tblContacts group by lname, fname, address having count(*) > 1 In a test trying to delete the duplicate entries, it de...more >>

Hi I've got a big list in my WHERE clause, and I was wondering what clause to use. Should I use OR or IN, is there any difference, in terms of record retrieval and speed. -- Kind Regards Rikesh (SQL2K-SP3/W2K-SP4) ...more >>

I need to ask advice. I've been told by some friends that with performance enhancements in 2003 Server that SQL Server 2000 can handle a database and transaction loads as large as Oracle. Other people say that for a really big application you still need to go to Oracle. Does anyone know where I c...more >>

HI i want to install "Configure SQL XML Support in IIS" on the IIS machine. my SQL Server located in other machine . i must install SQL SERVER (may be only client) on IIS machine ? can i install only "Configure SQL XML Support in IIS" on the IIS machine ? Thank you ...more >>

I would like to find a good, free way to compare, let's say, my beta/test database to my production database. I'm sure they're out there, I just don't know of any....more >>

background: sql2k on nt5. there is a view in master database, and a sysusers table in each database. if an account is found in sysusers table, but not in syslogins table, what does that mean? how did that happen, and how to clean it up or sync up? thank you. ...more >>

I need to open two cursors and retreive the records from both in a nested way as in master detail report. outer cursor being the master and inner being the detail. ...more >>

I got this problem where I need to look up values in two columns from another table. I can get OUTER LEFT JOIN working when looking up one column from a table but when looking up at two columns from a table I can't get the SQL syntax correct. The scenario is A table has definitions for...more >>

I want to run a stored procedure on server2 by calling it from server1. I can make this work but if server2 is unavailable server1 fails to talk to it and the transaction gets rolled back. Can I do some Transact-SQL code so that if calling the procedure on server2 fails something differe...more >>

Hi, I have 2 tables. First with all postalcodes in european counties (only 2 first signs). Second with neighbour codes. This table says that code with ID for e.g. 2312 has neughbours 2416, 2418, 3212, ... - all in separete rows. Now I would like to create stored procedure which could retur...more >>

In my opinion, you're a bastard. Thanks for your understanding...more >>

intAbsID strLogonName intYear strAbsRsnCode dtmAbsStart dtmAbsEnd decDuration 927 AdrianT 2004 ANNUAL 23-Feb-04 27-Feb-04 5 928 AdrianT 2004 ANNUAL 01-Mar-04 01-Mar-04 1 1466 AdrianT 2004 ANNUAL 04-May-04 10-May-04 5 1803 AdrianT 2004 ANNUAL 05-Jul-04 09-Jul-04 5 1295 AdrianT 2004 COL 04-Mar-04...more >>

hi all of us, I've got four users which currently are updating and modifyng tables as well as stored procedures. Well, all of them start up query analyzer using the same login and password. This means that any change in any object always appear as 'dbo' as owner. I'd like to know how to ...more >>

Hi, Which is the best trigger type to use in this situation... I need to check that a user has authority to edit any certain row in a table. If their accessid is not held against the row they are trying to edit then I must prevent the update. Otherwise if their accessid is held against the...more >>

What is the general SQL syntax statement for accessing blob objects? I've looked mySQL,they said there is a limitation and the limitation is you have to 'trim' each field so that they are the same length before you do a 'compare' on it. I'm wondering SQL Server would have been the same. I used t...more >>

test...more >>

hi every one. I'm trying to write an extended stored procedure that do some special random selecting records from a table (based on some extra criteria) I thought of using ado in side the dell but could not figure how the connection is going to be made thinking of the SQL server Sample (xp_od...more >>