Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 sql problem
 Forum Locked
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

A custom title

United Arab Emirates
1408 Posts

Posted - 03/10/2002 :  10:53:16  Show Profile
You are absolutely right , nr.

i tried this

select * from openrowset('MSDASQL',
'DRIVER={SQL Server};SERVER=(local);UID=sa;PWD=pppppp',
'set nocount on create #a(id int) select @@spid ')

i get the following results
Single run with go between each statement
#a spid = 36
#a spid = 38
#a spid = 37
#a spid = 36 -- error

when i try it after couple of minutes . i get a spid of
and again 39
Am not sure why this cycle of spid's is repeating after every three statements .

When i run it separeately giving a gap of a min.
#a - 36
#a - 15
#a - 33
#a - 15 -- 15 again and i dont get a error???
#a - 15 -- No Error??

Do these temp tables gets destroyed after a specific interval??


Edited by - Nazim on 03/10/2002 10:59:21
Go to Top of Page


United Kingdom
12543 Posts

Posted - 03/10/2002 :  13:13:51  Show Profile  Visit nr's Homepage
It holds the connection which the openrowset creates - which also keeps the temp table. After a minute or two the connection is dropped which also drops the temp table.

Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Esteemed SQL Purist

547 Posts

Posted - 03/11/2002 :  12:13:12  Show Profile  Visit jcelko's Homepage
Yes, you can do this if you know how many columns you wish to use. However, it is a really bad idea. It destroys First Normal Form and should be done in the front end, not in the database. This is a report and not a query.

Joe Celko, SQL Guru
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000