Location for Temporary File

I have created the following table in Northwind Database:
CREATE TABLE #dts(c1 char(1), dt datetime)
INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
However, when I search in both Northwind and Tempdb
databases, I am not able to find it. I would like to know
where is the Temporary Table being created ?
Thanks
Hi,
All the # (temp) tables and ## (global temp) table will be created in TEMPDB
database. But for temp tables the name will be
created with a unique prefix. This is because temp tables are created every
session and each session a unqie table needs to be created.
How to see this..
Select name from tempdb..sysobjects where name like 'dts%'
Thanks
Hari
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks
|||In TempDB
Run the query after your creation
IF OBJECT_ID('tempdb..#dts') IS NOT NULL
PRINT 'TempDB'
ELSE
PRINT 'No'
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
> I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks
|||Dear Hari,
I do find it by using your query.
In this way, if I want to make query of that temporary table, should I use
the table name dts?
Besides, you mention that temp tables are created every session, does it
mean that it will be deleted automatically at a specific time ? If yes, when
(Like - When I stop SQL Service) ?
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the # (temp) tables and ## (global temp) table will be created in
> TEMPDB database. But for temp tables the name will be
> created with a unique prefix. This is because temp tables are created
> every session and each session a unqie table needs to be created.
> How to see this..
> Select name from tempdb..sysobjects where name like 'dts%'
> Thanks
> Hari
> SQL Server MVP
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>
|||Why bother about the physical name? Just use the temp table name you gave it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks
|||Peter wrote:
> Dear Hari,
> I do find it by using your query.
> In this way, if I want to make query of that temporary table, should I use
> the table name dts?
Why not use the temp name (#dts)?

> Besides, you mention that temp tables are created every session, does it
> mean that it will be deleted automatically at a specific time ? If yes, when
> (Like - When I stop SQL Service) ?
>
The table will be dropped as soon as the connection is closed.
Are you trying to access this table from a different connection?
If so, you might want a global temp as already specified ##TableName.
This should be available until the service is restarted.
The beauty of temp tables is they are localized to your current
connection so you can use them in a sp for instance and guarantee that
nothing else will disturb your temp table.
You might well be able to query the name from tempdb and then use it
'normally' but this would defeat the purpose of temp tables and could
well introduce some nasty side effects.
Cheers
JB

> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
>
>

Location for Temporary File

I have created the following table in Northwind Database:
CREATE TABLE #dts(c1 char(1), dt datetime)
INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
However, when I search in both Northwind and Tempdb
databases, I am not able to find it. I would like to know
where is the Temporary Table being created ?
ThanksHi,
All the # (temp) tables and ## (global temp) table will be created in TEMPDB
database. But for temp tables the name will be
created with a unique prefix. This is because temp tables are created every
session and each session a unqie table needs to be created.
How to see this..
Select name from tempdb..sysobjects where name like 'dts%'
Thanks
Hari
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||In TempDB
Run the query after your creation
IF OBJECT_ID('tempdb..#dts') IS NOT NULL
PRINT 'TempDB'
ELSE
PRINT 'No'
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
> I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||Dear Hari,
I do find it by using your query.
In this way, if I want to make query of that temporary table, should I use
the table name dts?
Besides, you mention that temp tables are created every session, does it
mean that it will be deleted automatically at a specific time ? If yes, when
(Like - When I stop SQL Service) ?
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the # (temp) tables and ## (global temp) table will be created in
> TEMPDB database. But for temp tables the name will be
> created with a unique prefix. This is because temp tables are created
> every session and each session a unqie table needs to be created.
> How to see this..
> Select name from tempdb..sysobjects where name like 'dts%'
> Thanks
> Hari
> SQL Server MVP
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>|||Why bother about the physical name? Just use the temp table name you gave it
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||Peter wrote:
> Dear Hari,
> I do find it by using your query.
> In this way, if I want to make query of that temporary table, should I use
> the table name dts?
Why not use the temp name (#dts)?

> Besides, you mention that temp tables are created every session, does it
> mean that it will be deleted automatically at a specific time ? If yes, wh
en
> (Like - When I stop SQL Service) ?
>
The table will be dropped as soon as the connection is closed.
Are you trying to access this table from a different connection?
If so, you might want a global temp as already specified ##TableName.
This should be available until the service is restarted.
The beauty of temp tables is they are localized to your current
connection so you can use them in a sp for instance and guarantee that
nothing else will disturb your temp table.
You might well be able to query the name from tempdb and then use it
'normally' but this would defeat the purpose of temp tables and could
well introduce some nasty side effects.
Cheers
JB

> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
>
>

Location for Temporary File

I have created the following table in Northwind Database:
CREATE TABLE #dts(c1 char(1), dt datetime)
INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
However, when I search in both Northwind and Tempdb
databases, I am not able to find it. I would like to know
where is the Temporary Table being created ?
ThanksHi,
All the # (temp) tables and ## (global temp) table will be created in TEMPDB
database. But for temp tables the name will be
created with a unique prefix. This is because temp tables are created every
session and each session a unqie table needs to be created.
How to see this..
Select name from tempdb..sysobjects where name like 'dts%'
Thanks
Hari
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||In TempDB
Run the query after your creation
IF OBJECT_ID('tempdb..#dts') IS NOT NULL
PRINT 'TempDB'
ELSE
PRINT 'No'
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
> I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||Dear Hari,
I do find it by using your query.
In this way, if I want to make query of that temporary table, should I use
the table name dts?
Besides, you mention that temp tables are created every session, does it
mean that it will be deleted automatically at a specific time ? If yes, when
(Like - When I stop SQL Service) ?
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the # (temp) tables and ## (global temp) table will be created in
> TEMPDB database. But for temp tables the name will be
> created with a unique prefix. This is because temp tables are created
> every session and each session a unqie table needs to be created.
> How to see this..
> Select name from tempdb..sysobjects where name like 'dts%'
> Thanks
> Hari
> SQL Server MVP
>
> "Peter" <anonymous@.discussions.microsoft.com> wrote in message
> news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>>I have created the following table in Northwind Database:
>> CREATE TABLE #dts(c1 char(1), dt datetime)
>> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
>> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
>> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
>> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
>> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
>> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
>> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
>> However, when I search in both Northwind and Tempdb
>> databases, I am not able to find it. I would like to know
>> where is the Temporary Table being created ?
>> Thanks
>|||Why bother about the physical name? Just use the temp table name you gave it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>I have created the following table in Northwind Database:
> CREATE TABLE #dts(c1 char(1), dt datetime)
> INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
> INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
> INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
> INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
> INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
> INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
> However, when I search in both Northwind and Tempdb
> databases, I am not able to find it. I would like to know
> where is the Temporary Table being created ?
> Thanks|||Peter wrote:
> Dear Hari,
> I do find it by using your query.
> In this way, if I want to make query of that temporary table, should I use
> the table name dts?
Why not use the temp name (#dts)?
> Besides, you mention that temp tables are created every session, does it
> mean that it will be deleted automatically at a specific time ? If yes, when
> (Like - When I stop SQL Service) ?
>
The table will be dropped as soon as the connection is closed.
Are you trying to access this table from a different connection?
If so, you might want a global temp as already specified ##TableName.
This should be available until the service is restarted.
The beauty of temp tables is they are localized to your current
connection so you can use them in a sp for instance and guarantee that
nothing else will disturb your temp table.
You might well be able to query the name from tempdb and then use it
'normally' but this would defeat the purpose of temp tables and could
well introduce some nasty side effects.
Cheers
JB
> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eA7wxCZbFHA.3712@.TK2MSFTNGP09.phx.gbl...
>>Hi,
>>All the # (temp) tables and ## (global temp) table will be created in
>>TEMPDB database. But for temp tables the name will be
>>created with a unique prefix. This is because temp tables are created
>>every session and each session a unqie table needs to be created.
>>How to see this..
>>Select name from tempdb..sysobjects where name like 'dts%'
>>Thanks
>>Hari
>>SQL Server MVP
>>
>>"Peter" <anonymous@.discussions.microsoft.com> wrote in message
>>news:0a0f01c56d8c$8f52e270$a401280a@.phx.gbl...
>>I have created the following table in Northwind Database:
>>CREATE TABLE #dts(c1 char(1), dt datetime)
>>INSERT INTO #dts (c1, dt) VALUES('a', '20040305 09:12:59')
>>INSERT INTO #dts (c1, dt) VALUES('b', '20040305 16:03:12')
>>INSERT INTO #dts (c1, dt) VALUES('c', '20040306 00:00:00')
>>INSERT INTO #dts (c1, dt) VALUES('d', '20040306 02:41:32')
>>INSERT INTO #dts (c1, dt) VALUES('e', '20040315 11:45:17')
>>INSERT INTO #dts (c1, dt) VALUES('f', '20040412 09:12:59')
>>INSERT INTO #dts (c1, dt) VALUES('g', '20040523 11:43:25')
>>However, when I search in both Northwind and Tempdb
>>databases, I am not able to find it. I would like to know
>>where is the Temporary Table being created ?
>>Thanks
>>
>

Local Temporary Tables

I have created a local temporary table (#Temp) and placed data in it.

When I do:
SELECT * #Temp

The result is a set of rows with no columns.

The SELECT statement is in the same procedure as the creation of #Temp; so I
don't believe this is a scoping issue. Does that make sense?

Thank you in advance,
EricBeringer wrote:
> I have created a local temporary table (#Temp) and placed data in it.
> When I do:
> SELECT * #Temp
> The result is a set of rows with no columns.
> The SELECT statement is in the same procedure as the creation of #Temp; so I
> don't believe this is a scoping issue. Does that make sense?
> Thank you in advance,
> Eric

No, it doesn't make sense. First, your code above is invalid SQL.
Second, you don't provide any DDL or sample data for us to reproduce the
problem.

Zach|||Please be patient with me. I'm learning how to do this stuff by myelf and
just begining! :)

At anyrate here is an example:
ALTER PROCEDURE proc1

AS

SET NOCOUNT ON

CREATE TABLE #TempTable(my_text CHAR(10))

INSERT INTO #TempTable(my_text) VALUES ('test')

INSERT INTO #TempTable(my_text) VALUES ('test2')

INSERT INTO #TempTable(my_text) VALUES ('test3')

SELECT * FROM #TempTable

The result (visually in Access 2002, in datasheet view) after executing the
procedure is simply a row header with three rows and no columns. Of note,
if the SET NOCOUNT ON is commented out there is nothing.

Thanks again,

Eric

"nib" <individual_news@.nibsworld.com> wrote in message
news:2tqnlcF21lmapU2@.uni-berlin.de...
> Beringer wrote:
>> I have created a local temporary table (#Temp) and placed data in it.
>>
>> When I do:
>> SELECT * #Temp
>>
>> The result is a set of rows with no columns.
>>
>> The SELECT statement is in the same procedure as the creation of #Temp;
>> so I don't believe this is a scoping issue. Does that make sense?
>>
>> Thank you in advance,
>> Eric
>>
>>
> No, it doesn't make sense. First, your code above is invalid SQL. Second,
> you don't provide any DDL or sample data for us to reproduce the problem.
> Zach|||I do similar stored procedures like this all the time. Except I create the
SP via Enterprise Manager and Check Syntax etc . Have you tried executing
this from Query Analyzer just to see if it works? Or just create a stored
procedure in Enterprise Manager and EXECUTE it from Query Analyzer...

The column in your example should have one column heading and three rows of
data (if I am reading it correctly).

Is it generating any errors?

Barry

"Beringer" <borden_eric@.invalid.com> wrote in message
news:l1Xdd.56381$kz3.16039@.fed1read02...
> Please be patient with me. I'm learning how to do this stuff by myelf and
> just begining! :)
> At anyrate here is an example:
> ALTER PROCEDURE proc1
> AS
> SET NOCOUNT ON
> CREATE TABLE #TempTable(my_text CHAR(10))
> INSERT INTO #TempTable(my_text) VALUES ('test')
> INSERT INTO #TempTable(my_text) VALUES ('test2')
> INSERT INTO #TempTable(my_text) VALUES ('test3')
> SELECT * FROM #TempTable
> The result (visually in Access 2002, in datasheet view) after executing
> the procedure is simply a row header with three rows and no columns. Of
> note, if the SET NOCOUNT ON is commented out there is nothing.
> Thanks again,
> Eric
> "nib" <individual_news@.nibsworld.com> wrote in message
> news:2tqnlcF21lmapU2@.uni-berlin.de...
>> Beringer wrote:
>>> I have created a local temporary table (#Temp) and placed data in it.
>>>
>>> When I do:
>>> SELECT * #Temp
>>>
>>> The result is a set of rows with no columns.
>>>
>>> The SELECT statement is in the same procedure as the creation of #Temp;
>>> so I don't believe this is a scoping issue. Does that make sense?
>>>
>>> Thank you in advance,
>>> Eric
>>>
>>>
>>
>> No, it doesn't make sense. First, your code above is invalid SQL. Second,
>> you don't provide any DDL or sample data for us to reproduce the problem.
>>
>> Zach

Local temporary table schema

Is there a way to get the schema (columns) of a local temporary table in SQL
Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
multiple connections use the same temp table name, there doesn't seem to be a
way to differentiate between them. Each table name is appended with a bunch
of underscores and a random number (i.e.
"#MyTable__________________________________________________________________________________________________________0000000002B0"),
but that doesn't seem to help much.
Thanks,
Rich WoodThis is a multi-part message in MIME format.
--=_NextPart_000_01D9_01C69935.4E112260
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
It seems odd that you would need to do this, since your code for the =current connection had to know how to create the #Temp Table.
However, if you must, you could use this as a starting point:
SELECT * FROM Tempdb..sysobjects WHERE name LIKE '%#Temp%'
SELECT * FROM Tempdb..syscolumns WHERE id =3D object_ID('tempdb..#Temp')
-- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Rich Wood" <RichWood@.newsgroup.nospam> wrote in message =news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com...
> Is there a way to get the schema (columns) of a local temporary table =in SQL > Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but =if > multiple connections use the same temp table name, there doesn't seem =to be a > way to differentiate between them. Each table name is appended with a =bunch > of underscores and a random number (i.e. > ="#MyTable________________________________________________________________=__________________________________________0000000002B0"), > but that doesn't seem to help much.
> > Thanks,
> > Rich Wood
--=_NextPart_000_01D9_01C69935.4E112260
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

It seems odd that you would need to do =this, since your code for the current connection had to know how to create the #Temp =Table.
However, if you must, you could use =this as a starting point:
SELECT *
FROM =Tempdb..sysobjects WHERE name LIKE ='%#Temp%'
SELECT *
FROM Tempdb..syscolumns
WHERE id =3D object_ID('tempdb..#Temp')
-- Arnie Rowland, YACE* "To =be successful, your heart must accompany your knowledge."
*Yet Another Certification =Exam
"Rich Wood" wrote in message news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com...> =Is there a way to get the schema (columns) of a local temporary table in SQL > =Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if > =multiple connections use the same temp table name, there doesn't seem to be a => way to differentiate between them. Each table name is appended with a =bunch > of underscores and a random number (i.e. > "#MyTable________________________________________________________________=__________________________________________0000000002B0"), > but that doesn't seem to help much.> > =Thanks,> > Rich Wood

---

--=_NextPart_000_01D9_01C69935.4E112260--|||Arnie Rowland wrote:
> It seems odd that you would need to do this, since your code for the curr=ent connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-07=54-45EB-8978-6391D4939CD1@.microsoft.com...
> > Is there a way to get the schema (columns) of a local temporary table i=n SQL
> > Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but =if
> > multiple connections use the same temp table name, there doesn't seem t=o be a
> > way to differentiate between them. Each table name is appended with a b=unch
> > of underscores and a random number (i.e.
> > "#MyTable______________________________________________________________=____________________________________________0000000002B0"),
> > but that doesn't seem to help much.
> >
> > Thanks,
> >
> > Rich Wood
> --=3D_NextPart_000_01D9_01C69935.4E112260
> Content-Type: text/html; charset=3DUtf-8
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 2562
> =EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> &

---

>
>
>
>
>
> It seems odd that you would need to do t=his, since
> your code for the current connection had to know how to create the #Temp
> Table.
>
> However, if you must, you could use this= as a
> starting point:
> SELECT *
> FROM Tempdb..sysobje=cts
>
> WHERE name LIKE '%#Temp%'= face=3D"Courier New" size=3D2>
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> -- Arnie Rowland, YACE* "To =be
> successful, your heart must accompany your knowledge."
>
> *Yet Another Certification Exam
>
>
> "Rich Wood" < href=3D"mailto:RichWood@.newsgroup.nospam"> size=3D2>RichWood@.newsgroup.nospam=> wrote in
> message href=3D"news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com"> size=3D2>news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com face=3DArial size=3D2>...> Is= there a way to
> get the schema (columns) of a local temporary table in SQL > Serve=r 2005?
> TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if > mul=tiple
> connections use the same temp table name, there doesn't seem to be a =>
> way to differentiate between them. Each table name is appended with a bun=ch
> > of underscores and a random number (i.e. >
> "#MyTable________________________________________________________________=__________________________________________0000000002B0"),
> > but that doesn't seem to help much.> > Thanks,=>
> > Rich Wood

---

> --=3D_NextPart_000_01D9_01C69935.4E112260--
use
sp_help 'tempdb..#temp'
Regards
Amish Shah|||Hi Rich,
Thank you for your posting!
You could use the object_ID function to get the object id of the temporary
table you current user created.
As Arnie mentioned, you could use the following statement to query the
information.
SELECT *
FROM Tempdb..sysobjects
WHERE id = object_ID('tempdb..#TEMP')
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#TEMP')
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||To make a long story short, the local temporary table is created dynamically
with the result of a query -- so even though I create it locally I don't know
the column names at design time.
Thanks for your response -- using the object_id function to query the
tempdb..syscolumns table worked.
Rich Wood
"Arnie Rowland" wrote:
> It seems odd that you would need to do this, since your code for the current connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id = object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com...
> > Is there a way to get the schema (columns) of a local temporary table in SQL
> > Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
> > multiple connections use the same temp table name, there doesn't seem to be a
> > way to differentiate between them. Each table name is appended with a bunch
> > of underscores and a random number (i.e.
> > "#MyTable__________________________________________________________________________________________________________0000000002B0"),
> > but that doesn't seem to help much.
> >
> > Thanks,
> >
> > Rich Wood

Local temporary table schema

It seems odd that you would need to do this, since your code for the current
connection had to know how to create the #Temp Table.
However, if you must, you could use this as a starting point:
SELECT *
FROM Tempdb..sysobjects
WHERE name LIKE '%#Temp%'
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#Temp')
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-0754-45EB-8978-6391D4
939CD1@.microsoft.com...
> Is there a way to get the schema (columns) of a local temporary table in S
QL
> Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
> multiple connections use the same temp table name, there doesn't seem to b
e a
> way to differentiate between them. Each table name is appended with a bunc
h
> of underscores and a random number (i.e.
> " #MyTable________________________________
_________________________________
________________________________________
_0000000002B0"),
> but that doesn't seem to help much.
>
> Thanks,
>
> Rich WoodIs there a way to get the schema (columns) of a local temporary table in SQL
Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
multiple connections use the same temp table name, there doesn't seem to be
a
way to differentiate between them. Each table name is appended with a bunch
of underscores and a random number (i.e.
" #MyTable________________________________
___________________________________
_______________________________________0
000000002B0"),
but that doesn't seem to help much.
Thanks,
Rich Wood|||It seems odd that you would need to do this, since your code for the current
connection had to know how to create the #Temp Table.
However, if you must, you could use this as a starting point:
SELECT *
FROM Tempdb..sysobjects
WHERE name LIKE '%#Temp%'
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#Temp')
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-0754-45EB-8978-6391D4
939CD1@.microsoft.com...
> Is there a way to get the schema (columns) of a local temporary table in S
QL
> Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
> multiple connections use the same temp table name, there doesn't seem to b
e a
> way to differentiate between them. Each table name is appended with a bunc
h
> of underscores and a random number (i.e.
> " #MyTable________________________________
_________________________________
________________________________________
_0000000002B0"),
> but that doesn't seem to help much.
>
> Thanks,
>
> Rich Wood|||Arnie Rowland wrote:

> It seems odd that you would need to do this, since your code for the curr=
ent connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-07=
54-45EB-8978-6391D4939CD1@.microsoft.com...
n SQL[vbcol=seagreen]
if[vbcol=seagreen]
o be a[vbcol=seagreen]
unch[vbcol=seagreen]
________________________________________
____0000000002B0"),[vbcol=seagreen]
> --=3D_NextPart_000_01D9_01C69935.4E112260
> Content-Type: text/html; charset=3DUtf-8
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 2562
> =EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
> <META content=3D"MSHTML 6.00.5296.0" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=3DArial size=3D2>It seems odd that you would need to do t=
his, since
> your code for the current connection had to know how to create the #Temp
> Table.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>However, if you must, you could use this=
as a
> starting point:</FONT></DIV>
> <DIV><BR><FONT face=3D"Courier New" size=3D2>SELECT * </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>FROM Tempdb..sysobje=

cts
> </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>WHERE name LIKE '%#Temp%'</FONT>=
</DIV><FONT
> face=3D"Courier New" size=3D2>
> <DIV><BR>SELECT * </DIV>
> <DIV>FROM Tempdb..syscolumns </DIV>
> <DIV>WHERE id =3D object_ID('tempdb..#Temp')</FONT></DIV>
> <DIV><BR><FONT face=3DArial size=3D2>-- <BR>Arnie Rowland, YACE* <BR>"To =
be
> successful, your heart must accompany your knowledge."</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>*Yet Another certification Exam</FONT></=
DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>"Rich Wood" <</FONT><A
> href=3D"mailto:RichWood@.newsgroup.nospam"><FONT face=3DArial
> size=3D2>RichWood@.newsgroup.nospam</FONT></A><FONT face=3DArial size=3D2>=
> wrote in
> message </FONT><A
> href=3D"news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com"><FONT fa=
ce=3DArial
> size=3D2>news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com</FONT></=
A><FONT
> face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> Is=
there a way to
> get the schema (columns) of a local temporary table in SQL <BR>> Serve=
r 2005?
> TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if <BR>> mul=
tiple
> connections use the same temp table name, there doesn't seem to be a <BR>=
>
> way to differentiate between them. Each table name is appended with a bun=
ch
> <BR>> of underscores and a random number (i.e. <BR>>
> " #MyTable________________________________
________________________________=
________________________________________
__0000000002B0"),
> <BR>> but that doesn't seem to help much.<BR>> <BR>> Thanks,<BR>=
>
> <BR>> Rich Wood</FONT></BODY></HTML>
> --=3D_NextPart_000_01D9_01C69935.4E112260--
use=20
sp_help 'tempdb..#temp'
Regards
Amish Shah|||Hi Rich,
Thank you for your posting!
You could use the object_ID function to get the object id of the temporary
table you current user created.
As Arnie mentioned, you could use the following statement to query the
information.
SELECT *
FROM Tempdb..sysobjects
WHERE id = object_ID('tempdb..#TEMP')
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#TEMP')
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||To make a long story short, the local temporary table is created dynamically
with the result of a query -- so even though I create it locally I don't kno
w
the column names at design time.
Thanks for your response -- using the object_id function to query the
tempdb..syscolumns table worked.
Rich Wood
"Arnie Rowland" wrote:
[vbcol=seagreen]
> It seems odd that you would need to do this, since your code for the curre
nt connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id = object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-075
4-45EB-8978-6391D4939CD1@.microsoft.com...|||Arnie Rowland wrote:

> It seems odd that you would need to do this, since your code for the curr=
ent connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-07=
54-45EB-8978-6391D4939CD1@.microsoft.com...
n SQL[vbcol=seagreen]
if[vbcol=seagreen]
o be a[vbcol=seagreen]
unch[vbcol=seagreen]
________________________________________
____0000000002B0"),[vbcol=seagreen]
> --=3D_NextPart_000_01D9_01C69935.4E112260
> Content-Type: text/html; charset=3DUtf-8
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 2562
> =EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
> <META content=3D"MSHTML 6.00.5296.0" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=3DArial size=3D2>It seems odd that you would need to do t=
his, since
> your code for the current connection had to know how to create the #Temp
> Table.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>However, if you must, you could use this=
as a
> starting point:</FONT></DIV>
> <DIV><BR><FONT face=3D"Courier New" size=3D2>SELECT * </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>FROM Tempdb..sysobje=

cts
> </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>WHERE name LIKE '%#Temp%'</FONT>=
</DIV><FONT
> face=3D"Courier New" size=3D2>
> <DIV><BR>SELECT * </DIV>
> <DIV>FROM Tempdb..syscolumns </DIV>
> <DIV>WHERE id =3D object_ID('tempdb..#Temp')</FONT></DIV>
> <DIV><BR><FONT face=3DArial size=3D2>-- <BR>Arnie Rowland, YACE* <BR>"To =
be
> successful, your heart must accompany your knowledge."</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>*Yet Another certification Exam</FONT></=
DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>"Rich Wood" <</FONT><A
> href=3D"mailto:RichWood@.newsgroup.nospam"><FONT face=3DArial
> size=3D2>RichWood@.newsgroup.nospam</FONT></A><FONT face=3DArial size=3D2>=
> wrote in
> message </FONT><A
> href=3D"news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com"><FONT fa=
ce=3DArial
> size=3D2>news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com</FONT></=
A><FONT
> face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> Is=
there a way to
> get the schema (columns) of a local temporary table in SQL <BR>> Serve=
r 2005?
> TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if <BR>> mul=
tiple
> connections use the same temp table name, there doesn't seem to be a <BR>=
>
> way to differentiate between them. Each table name is appended with a bun=
ch
> <BR>> of underscores and a random number (i.e. <BR>>
> " #MyTable________________________________
________________________________=
________________________________________
__0000000002B0"),
> <BR>> but that doesn't seem to help much.<BR>> <BR>> Thanks,<BR>=
>
> <BR>> Rich Wood</FONT></BODY></HTML>
> --=3D_NextPart_000_01D9_01C69935.4E112260--
use=20
sp_help 'tempdb..#temp'
Regards
Amish Shah|||Hi Rich,
Thank you for your posting!
You could use the object_ID function to get the object id of the temporary
table you current user created.
As Arnie mentioned, you could use the following statement to query the
information.
SELECT *
FROM Tempdb..sysobjects
WHERE id = object_ID('tempdb..#TEMP')
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#TEMP')
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||To make a long story short, the local temporary table is created dynamically
with the result of a query -- so even though I create it locally I don't kno
w
the column names at design time.
Thanks for your response -- using the object_id function to query the
tempdb..syscolumns table worked.
Rich Wood
"Arnie Rowland" wrote:
[vbcol=seagreen]
> It seems odd that you would need to do this, since your code for the curre
nt connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id = object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-075
4-45EB-8978-6391D4939CD1@.microsoft.com...

Local Temporary Table Bottlenecks?

Greetings,
I'm fairly new to stored procedures and temporary tables and i was going
through this tutorial for a banner ad system.
http://aspnet.4guysfromrolla.com/ar...033104-1.2.aspx
it's a nice write up, how over i'm concearned over some of its logic. In
part two, the author discusses how he generates a temporary table each time
the NRCA_sp_Get_Random_Banner Stored Procedure is called. This would happen
d
each time a banner ad is requested from a page.
My concearn regards scalibility. Let's say that there is a high traffic
website that display banners on its homepage.
1) Would that create a bottle neck?
2) what would happen if two browser requests invoke the
NRCA_sp_Get_Random_Banner Stored Procedure at the same time?
3) Would the temp database create two temporary tables? Or, would one
request fail?
4) would it be better to make an actual table that is simply updated when
new ads are inserted or old ones are modified and query it?"Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
news:3DDE7E76-CB03-49BB-8EA4-1D8B0A8D97E5@.microsoft.com...
> 1) Would that create a bottle neck?
Maybe. It depends on many factors, such as memory, the disk system
tempdb is on, and how it's configured. See the following KBA for some
hints:
http://support.microsoft.com/default.aspx/kb/328551?

> 2) what would happen if two browser requests invoke the
> NRCA_sp_Get_Random_Banner Stored Procedure at the same time?
Temp tables (except global temp tables, created with ##) are local to the
connection that created them. So it would work as expected.

> 3) Would the temp database create two temporary tables? Or, would one
> request fail?
Two.

> 4) would it be better to make an actual table that is simply updated when
> new ads are inserted or old ones are modified and query it?
I probably wouldn't use a table at all; I didn't read the article in
much depth, but I would probably do something like:
SELECT TOP 1
Ad_Id
FROM tbl_NRCA_Banner_Ads
WHERE
Page = @.Page
AND StartDate < getdate()
AND EndDate > getdate()
AND Active = 1
AND Weight <= @.MaxWeight
ORDER BY NEWID()
I believe this has the same net effect as the temp table approach -- but you
should test on your end to make sure.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thanks for the info Adam, i appreciate it. The reason the temporary table i
s
created is because each ad has a weight factor that will increase the
probability of its beign shows. So, an ad with weight 3 is three times more
likely to be shown that one with weight one.
So, the temporary table does this...
say there are two ads
ad one, weight 1
ad two, weight 3
i creates a temporary table with four records..
id | ad
1 ad one
2 ad two
3 ad two
4 ad two
so, ad two is given three entries (instead of one). Then a random selection
is made to get an ad and that will be the one that is displayed.
is there a better alternative to temp tables for this?
ad two
"Adam Machanic" wrote:

> "Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
> news:3DDE7E76-CB03-49BB-8EA4-1D8B0A8D97E5@.microsoft.com...
> Maybe. It depends on many factors, such as memory, the disk system
> tempdb is on, and how it's configured. See the following KBA for some
> hints:
> http://support.microsoft.com/default.aspx/kb/328551?
>
> Temp tables (except global temp tables, created with ##) are local to th
e
> connection that created them. So it would work as expected.
>
> Two.
>
> I probably wouldn't use a table at all; I didn't read the article in
> much depth, but I would probably do something like:
> SELECT TOP 1
> Ad_Id
> FROM tbl_NRCA_Banner_Ads
> WHERE
> Page = @.Page
> AND StartDate < getdate()
> AND EndDate > getdate()
> AND Active = 1
> AND Weight <= @.MaxWeight
> ORDER BY NEWID()
> I believe this has the same net effect as the temp table approach -- but y
ou
> should test on your end to make sure.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>

Loading XML data using DTS.

I am trying to load XML data into SQL Server 2000/sp3a. Since the XML document is in a file, I used DTS to first load the data into a temporary file (with a text field, used varchar(5000) as well) and then copy it into a variable for use in sp_xml_prepare
document.
Everything works fine except that of the date field. For instance, I have defined it as
<ReceivedDate>2004-03-29</ReceivedDate>
However, once it is loaded into the temporary table it is saved as
<ReceivedDate>2004-29-03</ReceivedDate>
I think I am missing something. Please help.
Thanks,
MZeeshan
I'm new at XML, but what I've played with so far, SQL does not create a
root element for your xml file. It looks like it's using the first
record's properties/child elements to name your table?
Robert
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Well... I ve created a table myself for getting the parsed data. I am getting that through SQL server provided T-SQL utility OPENXML. That is working fine. However, there is some problem in transformation.
|||This is unrelated to the DTS question.
You can add a root element using the root node property of ADO/OLEDB/URL
queries, use an explicit mode query to add it or (only available in the
upcoming SQL Server 2005) use the ROOT directive on a FOR XML clause.
Best regards
Michael
"Robert Taylor" <anonymous@.devdex.com> wrote in message
news:ejkj$lpQEHA.1440@.TK2MSFTNGP10.phx.gbl...
> I'm new at XML, but what I've played with so far, SQL does not create a
> root element for your xml file. It looks like it's using the first
> record's properties/child elements to name your table?
> Robert
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!