Nocount

Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
SELECT...
Please, check my theSpoke:
http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
Paperback Writer wrote:
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only
> for SELECT...
It good practice to have a SET NOCOUNT ON at the top of every stored
procedure and also to execute upon initial connection if there is a
chance that ad-hoc SQL will be sent from the application to the server.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Its not needed really.
Bojidar Alexandrov
"Paperback Writer" <newsgroupms@.gmail.com> wrote in message
news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
> SELECT...
> --
> Please, check my theSpoke:
> http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
>
|||hahahahahahahahah
Classic.
In our environment the single biggest performance gain was obtained by using
this set option but don't take anybodys word for it. Test for yourself.
When push came to shove and I had to convice the managers of our development
team I used this simple example.
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount on
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount off
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
results from first select
Elapsed Time, Msec
7516
results from second select
(99999 row(s) affected)
Elapsed Time, Msec
7780
"Bojidar Alexandrov" wrote:

> Its not needed really.
> Bojidar Alexandrov
> "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
> news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
>
|||I had a loop with
BEGIN TRAN
UPDATE
COMMIT
And counter transaction per second. With the setting off, I has 800, with on, I had 2000...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...[vbcol=seagreen]
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by using
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our development
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
|||Tibor, the thing with update (or delete statements) is very clear that it
takes too much time to transfer these (x rows affected) as another
recordset, but original question was for Select statements only. When there
are staments returning stats like update and delete you are very right.
Im not sure that the example from Joe is right, so will test myself.
Bojidar Alexandrov
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23fBUhFaFHA.616@.TK2MSFTNGP12.phx.gbl...
> I had a loop with
> BEGIN TRAN
> UPDATE
> COMMIT
> And counter transaction per second. With the setting off, I has 800, with
on, I had 2000...[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
using[vbcol=seagreen]
development[vbcol=seagreen]
for
>
|||I've just tested out and do not think you are right. Mine results from 10
trys on table with about 100k rows are on the average same.
Bojidar Alexandrov
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by
using
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our
development[vbcol=seagreen]
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
for[vbcol=seagreen]

Nocount

Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
SELECT...
Please, check my theSpoke:
http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspxPaperback Writer wrote:
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only
> for SELECT...
It good practice to have a SET NOCOUNT ON at the top of every stored
procedure and also to execute upon initial connection if there is a
chance that ad-hoc SQL will be sent from the application to the server.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Its not needed really.
Bojidar Alexandrov
"Paperback Writer" <newsgroupms@.gmail.com> wrote in message
news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
> SELECT...
> --
> Please, check my theSpoke:
> http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
>|||hahahahahahahahah
Classic.
In our environment the single biggest performance gain was obtained by using
this set option but don't take anybodys word for it. Test for yourself.
When push came to shove and I had to convice the managers of our development
team I used this simple example.
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount on
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount off
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
results from first select
Elapsed Time, Msec
--
7516
results from second select
(99999 row(s) affected)
Elapsed Time, Msec
--
7780
"Bojidar Alexandrov" wrote:

> Its not needed really.
> Bojidar Alexandrov
> "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
> news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
>|||I had a loop with
BEGIN TRAN
UPDATE
COMMIT
And counter transaction per second. With the setting off, I has 800, with on
, I had 2000...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...[vbcol=seagreen]
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by usi
ng
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our developme
nt
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
>|||Tibor, the thing with update (or delete statements) is very clear that it
takes too much time to transfer these (x rows affected) as another
recordset, but original question was for Select statements only. When there
are staments returning stats like update and delete you are very right.
Im not sure that the example from Joe is right, so will test myself.
Bojidar Alexandrov
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23fBUhFaFHA.616@.TK2MSFTNGP12.phx.gbl...
> I had a loop with
> BEGIN TRAN
> UPDATE
> COMMIT
> And counter transaction per second. With the setting off, I has 800, with
on, I had 2000...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
using[vbcol=seagreen]
development[vbcol=seagreen]
for[vbcol=seagreen]
>|||I've just tested out and do not think you are right. Mine results from 10
trys on table with about 100k rows are on the average same.
Bojidar Alexandrov
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by
using
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our
development[vbcol=seagreen]
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
>
for[vbcol=seagreen]

Nocount

Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
SELECT...
--
Please, check my theSpoke:
http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspxPaperback Writer wrote:
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only
> for SELECT...
It good practice to have a SET NOCOUNT ON at the top of every stored
procedure and also to execute upon initial connection if there is a
chance that ad-hoc SQL will be sent from the application to the server.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Its not needed really.
Bojidar Alexandrov
"Paperback Writer" <newsgroupms@.gmail.com> wrote in message
news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
> SELECT...
> --
> Please, check my theSpoke:
> http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
>|||hahahahahahahahah
Classic.
In our environment the single biggest performance gain was obtained by using
this set option but don't take anybodys word for it. Test for yourself.
When push came to shove and I had to convice the managers of our development
team I used this simple example.
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount on
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount off
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
results from first select
Elapsed Time, Msec
--
7516
results from second select
(99999 row(s) affected)
Elapsed Time, Msec
--
7780
"Bojidar Alexandrov" wrote:
> Its not needed really.
> Bojidar Alexandrov
> "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
> news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> > Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
> > SELECT...
> >
> > --
> > Please, check my theSpoke:
> > http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
> >
> >
>|||I had a loop with
BEGIN TRAN
UPDATE
COMMIT
And counter transaction per second. With the setting off, I has 800, with on, I had 2000...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by using
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our development
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
>> Its not needed really.
>> Bojidar Alexandrov
>> "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
>> news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
>> > Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
>> > SELECT...
>> >
>> > --
>> > Please, check my theSpoke:
>> > http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
>> >
>> >
>>|||Tibor, the thing with update (or delete statements) is very clear that it
takes too much time to transfer these (x rows affected) as another
recordset, but original question was for Select statements only. When there
are staments returning stats like update and delete you are very right.
Im not sure that the example from Joe is right, so will test myself.
Bojidar Alexandrov
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23fBUhFaFHA.616@.TK2MSFTNGP12.phx.gbl...
> I had a loop with
> BEGIN TRAN
> UPDATE
> COMMIT
> And counter transaction per second. With the setting off, I has 800, with
on, I had 2000...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
> > hahahahahahahahah
> > Classic.
> > In our environment the single biggest performance gain was obtained by
using
> > this set option but don't take anybodys word for it. Test for yourself.
> > When push came to shove and I had to convice the managers of our
development
> > team I used this simple example.
> > dbcc dropcleanbuffers
> > go
> > dbcc freeproccache
> > go
> > set nocount on
> > go
> > DECLARE @.start_time DATETIME
> > SELECT @.start_time = GETDATE()
> > select * from BIG_SALES2
> > SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> > GO
> > dbcc dropcleanbuffers
> > go
> > dbcc freeproccache
> > go
> > set nocount off
> > go
> > DECLARE @.start_time DATETIME
> > SELECT @.start_time = GETDATE()
> > select * from BIG_SALES2
> > SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> > GO
> >
> > results from first select
> >
> > Elapsed Time, Msec
> > --
> > 7516
> >
> > results from second select
> > (99999 row(s) affected)
> > Elapsed Time, Msec
> > --
> > 7780
> >
> >
> > "Bojidar Alexandrov" wrote:
> >
> >> Its not needed really.
> >>
> >> Bojidar Alexandrov
> >>
> >> "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
> >> news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> >> > Hi, I'd like to know if i need to set Nocount On/Off in a proc only
for
> >> > SELECT...
> >> >
> >> > --
> >> > Please, check my theSpoke:
> >> > http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
> >> >
> >> >
> >>
> >>
>|||I've just tested out and do not think you are right. Mine results from 10
trys on table with about 100k rows are on the average same.
Bojidar Alexandrov
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by
using
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our
development
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
> > Its not needed really.
> >
> > Bojidar Alexandrov
> >
> > "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
> > news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> > > Hi, I'd like to know if i need to set Nocount On/Off in a proc only
for
> > > SELECT...
> > >
> > > --
> > > Please, check my theSpoke:
> > > http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
> > >
> > >
> >
> >

No Temp tables in SQLDatasource???

Please tell me there's something I haven't set. I've done several tests now. If your final return in a stored proc is from a temp table (ala #mytable ) the system cannot read the schema - for that matter, it won't run at all, complaining that the object #mytable doesn't exists.

It can't possible be that temp tables aren't allowed in procs used by SQLDatasource - please tell me what I am doing wrong.

This proc, when fed to a sqldatasource, fails in the designer with #temp does not exists.

CREATE PROCEDURE dbo.repTest_Temp
AS
BEGIN

CREATE TABLE #Temp
(
[iTestID] uniqueidentifier,
[bTest] [bit],
[cTest] [varchar]
)

INSERT INTO #Temp
SELECT *
FROM tTest

SELECT *
FROM #Temp

END

try:

CREATE PROCEDURE dbo.repTest_Temp
AS
BEGIN

CREATE TABLE #Temp
(
[iTestID] uniqueidentifier,
[bTest] [bit],
[cTest] varchar(10)
)

INSERT INTO #Temp
SELECT *
FROM tTest

SELECT *
FROM #Temp

END

It still won't read the schema, but you can do whatever you want to by manually entering the fields you need. I bound it to a gridview, and created 3 bound fields, adding the name of each field, and it showed up in my page just fine -- although I replaced the INSERT statement with "INSERTINTO #zzTemp(iTestID,bTest,cTest)VALUES(newid(),0,'test')"

|||

That's the same proc I posted - and no it won't load in a sqldatasource. I wasn't asking for a work around - I'm fully aware I can manually bind the grid. Kinda defeats the purpose of having a visual designer, don't you think?

What I asked was IF you can use a proc in a SQLDatasource that has it's final select from a temp table. I thought I made this clear by supplying the proc. If you can't, that's yet ONE more thing screwed up with the visual designer.

It's still blowing me away how useless the visual designer is to do any real work.

|||

I changed the sproc, if you notice the definition of one of your fields from [varchar] to varchar(10).

Let me try and make this clearer for you:

What I asked was IF you can use a proc in a SQLDatasource that has it's final select from a temp table. I thought I made this clear by supplying the proc.

What I answered was yes. You can use a proc in a SQLDatasource that has it's final select from a temp table. I thought I made this clear by supplying the proc I used, as well as how I implemented it, and got a gridview to databind to said sproc.

From your last message, you aren't interested in how, you just want to complain. I'm not here to make you feel better. I gave you a solution on how to get done what you needed, and it took me a total of 10 minutes. Take it and use it, or complain some more. I'm done with the thread.

|||

i realize this is considerably after the fact for the originating parties, i will still post my findings for anyone else that might experience this problem. i would define the problem as visual studio barking whenever i tried to drag a stored procedure to the .xsd designer interface which selected from a temp table. im posting here because this is the only thread i could find on the subject. i have rewritten the original posted procedure to show the solution. simply use a TABLE variable instead of a temp table.

CREATE PROCEDURE dbo.repTest_Temp
AS

DECLARE @.Temp TABLE (iTestID uniqueidentifier,
bTest bit,
cTest varchar(10))

INSERT @.Temp
SELECT *
FROM tTest

SELECT *
FROM @.Temp

this worked like a charm for me. hopefully someone else will find this information useful!

|||Another solution is to use Global Temp table instead of local temp table which goes out of scope quickly. ## global temp table and # local temp table. Hope this helps.