Hi all. I have a little puzzle with temporary tables that I need some help with. Here's the scenario... I want to create a temporary table like this... Create Table #Tmp1( Id int not null, AnotherId int not null ) Which works fine, however after the initial population, the tab...more >>

hello, I need to add a blackslash to my select statement e.g. SELECT domain + '\' + userid from LoginStuff but when I try and do it this gives me the following error: Syntax error converting the varchar value '\' to a column of data type int. Any idea? thanks ...more >>

Please I need some guidance! Can I SQL from a Text box? If so how do I go about it, will anybody show me an example. Thanks orsonros ...more >>

Hi, When I use this syntax : select * FROM OPENROWSET(BULK N'C:\WINDOWS\winhlp32.exe', SINGLE_BLOB) as MyColumn SQL 2005 returns : BulkColumn -------------------------------------------------------------------------------------------------------------------------------------------------...more >>

I have the back up of master(master_db.bak), distribution(distribution_db.bak), model(model_db.bak, model_tlog.trn), pubs(pubs_db.bak), Northwind(Northwind_db.bak),msdb(msdb_db.bak) and compny(compny_db.bak,compny_db.trn). I have to restore all these databases. What will be the correct sequence ...more >>

The documentation for this table describes the resolution for several timers as microseconds. I looked it up and a microsecond is 1 millionth of a second. That is 1000 time better resolution than the timers in the other documentation I have read. I didn't think there was a timer that fine on m...more >>

So if I have this very basic query: SELECT itemID, itemNum FROM items ORDER BY itemNum ASC I would get a table as so (b/c itemNum, is treated as text): itemID itemNum --------------- 1 AL1100 4 AL101 3 AL900B 2 AL602 I really need it to be: itemID itemNum --------------- 4 AL101 ...more >>

This picture outs tears in your eye...:'(:'( http://cdn.news.aol.com/aolnews_photos/01/01/20060802125009990013 http://cdn.news.aol.com/aolnews_photos/0b/05/20060802125509990029 http://cdn.news.aol.com/aolnews_photos/08/02/20060802125309990002 http://cdn.news.aol.com/aolnews_photos/02/04/...more >>

Hi, I have a table 'A' with a column 'a' which has a certain records with 5 chars and another set of records with 8 chars starting with "xyz" (xyz + 5 chars). How can display the records with alpha sort on the 5 chars records first and then alpha sort on the 8 char records which starts with ...more >>

We have a stored procedure joining a large table (10 million rows) and a small table (less than 10,000 rows) by using the IN operator to process selected multiple values (product codes) passed on from front-end application to return a list of distinct customer names. This was working fine unt...more >>

After deleting a record in a table I have to delete the file whose name is in the field Fname of the deleted record. I'm trying to find the T-SQL command that lets me delete a file on disk to use in trigger Possible? Any help appreciated, Bob ...more >>

I need some data model guru's professional opinions out there for a data model question in a business situation. If I have some book title information (like ISBN, title, author, price and so on), and location info (such as store id and department category, department will be like sections such...more >>

Hello Friends, I have few tables in our database. For instance in the Vehicle I have VehicleID as primary key, MakeID as Foreign key, ModelID as Foreinkey. In the object explorer when I expand the table I see and identify the column as FKs with the greay shaded color and PKs as golden yellow. ...more >>

When I run the query as select * from a Join b On a.ID = b.MID where (b.Author IN ('Poe', 'Hemingway', 'London')) I got less rows returned from select * from a Join b On a.ID = b.MID where (select author from aut) but select author from aut got 3 records 'Poe', 'Hemingway', and 'Lon...more >>

Hello, I have a sql 2000 server and a sql 2005 server. I have a linked server set up between them. I have a stored proc on the sql200 server doint an insert into a table on the sql2005 server. There is an identity feild on the insert table. After the insert I try to retrieve the @@identity val...more >>

We have a 3rd party application that when people are away holiday or sickness enters data as so, id is the id of the person. Id, sdate, edate, daysaway ,reason 123 2006/08/01 2006/08/02 2 holiday 333 2006/08/01 2006/08/04 4 sick I ...more >>

BOL says this about the KILL command: Use KILL very carefully, especially when critical processes are running. You cannot kill your own process. Other processes not to kill are: AWAITING COMMAND CHECKPOINT SLEEP LAZY WRITER LOCK MONITOR SELECT SIGNAL HANDLER Those all make sense exce...more >>

I can never seem to get this to work for me. I have some values in the database that I know have 3-4 trailing spaces after the value (it's an email address that was cut and pasted into the db), so I am trying to remove those spaces in my query, but they are still there in the query results. Am...more >>

I try to drop a user... DROP USER [myuser] Msg 15138, Level 16, State 1, Line 1 The database principal owns a certificate in the database, and cannot be dropped. ....so I try to drop the certificate... DROP CERTIFICATE myusercert The certificate cannot be dropped because one or more entiti...more >>

Can someone please refer a script to me that would loop through all use defined stored procedures and replace the database owner of the SP with dbo? I'm finding that there are a few different database owners assocated with some of the SPs, and some SPs don't have any at all. I want everything...more >>

i am getting following error message when i run EXEC master..xp_execresultset N'SELECT ''SELECT * FROM '' + s.name + ''.'' + o.name FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.type = ''U'' ', 'AdventureWorks'; "Could not find stored procedure 'maste...more >>

All Gurus; when you put ## in front of a table what does it mean? I know That # means a temp table I forget what ## means? Bill *** Sent via Developersdex http://www.developersdex.com ***...more >>

First, I created a BLANK database in SQL 2005 called TimeMatters5. I have a SQL 2000 database I backed up about a year ago. I ran the following command: RESTORE FILELISTONLY FROM DISK = 'D:\temp\TimeMatters5_db_200503300200.bak' and it gave me the following logical names: TimeMatters5 an...more >>

This picture outs tears in your eye...:'(:'( http://cdn.news.aol.com/aolnews_photos/01/01/20060802125009990013 http://cdn.news.aol.com/aolnews_photos/0b/05/20060802125509990029 http://cdn.news.aol.com/aolnews_photos/08/02/20060802125309990002 http://cdn.news.aol.com/aolnews_photos/02/04/...more >>

Hello Friends, For some reason the below query is complaining about 'syntax error and where clause' here is the SQL ******************************************************************************************************* SELECT VS.VehicleSaleID, VS.SellerID, VS.BuyerID,...more >>

I'm working in SQL 2005: select complaintid, incidentdt from complaint The column incidentdt is a DateTime Column, and the values are stored in UTC. I need the simplest possible way to convert incidentdt to local time. I thought this was going to be trivial, but when I look at difference...more >>

Hi there, I have a very strange problem that I hope someone will be able to help me with. Firstly, the setup: I'm using SQL server 2005 and accessing a view (which accesses a table) on a Linked SQL2000 server. The view returns a field that is of type ntext(16) and I am trying to insert i...more >>

Can someone help me write a query for following condition i have a table with 2 columns as production shift ---------------------------- 1000 A 2000 B 3000 C 4000 B 5000 C 6000 ...more >>

Hi - I'm upgrading an application to use SQL Server 2005 (express) from MS Access. I'm having trouble with this query though, and I suspect it's to do with the IIf. could anyone please offer an alternative to enable it to work in SQL? Thanks, Mark SELECT count(*) as num, iif(sum(G1_...more >>

Hi all, I am working on an SQL query. Just wondering how you would implement the below. (I am using a simplified example) Say you have a COLUMN in a set of results returned called "STATUS". In the STATUS column you could have either a 1 or 0 returned for a particular row. Now my ques...more >>

Hey I'm trying to create a stored procedure. Below is the output from my stored procedure. It doesn't give the result I want. I'm a bit stucked in this procedure. The output only prints "test1", it skips "test2" and "test3" Perhaps some of you guys see why my stored procedure only prints "t...more >>

How to store a very long (around 10,000 – 100,000 character long) dynamic sql query? I am using MS SQL Server 2000. I have three tables and with the help of these tables I create dynamic sql query bcoz these are metadata tables. So length of query depends upon no. of records in tables....more >>

I've got two tables. One table with all possible transactioncodes (TABLE ABC) another with the transactions entered per day (TABLE XYZ). Example: Table ABC contains: Trc Description 40 Order 50 Return 60 Cancel 70 Payment 80 Invalid 90 Replacement Table...more >>

Hey asp.net 2.0 If I test the stored procedure below with for example this value: tag1, tag2, tag3, tag4. Then the Tag table will get values in this order: tag2, tag1, tag3, tag4. I don't understand why it isn't tag1, tag2, tag3, tag4. I also replaced the insert statments below with simp...more >>

Hi All I have SQL Server Express 2005 running on a stand alone computer In code in my VB.net 2005 application I want to be able to backup and restore from my app Back up works OK but restore fails due to (I think ) connections to SQl server still being open (Restore fails from sql expr...more >>

Hi, I'm currently trying to programming a stored procedure to maintains indexes accross tables of a database. But when I try to execute my code I have this error : Msg 102, Level 15, State 1, Line 44 Incorrect syntax near '@IndexName'. Msg 102, Level 15, State 1, Line 50 Incorrec...more >>

Hi, Is there a way to delete the trace files on the sql server without using xp_cmdshell. Since, xp_cmdshell can be disabled in SQL Server 2005 I dont want to go with this option. This option should work on both 2000 as well as 2005 :) Any other suggestions ? TIA Thanks P...more >>