Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

compilation error Expand / Collapse
Author
Message
Posted Monday, February 2, 2009 11:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 1:40 AM
Points: 26, Visits: 104
I got following error while this x_TruncateTable proc:

Server: Msg 197, Level 15, State 1, Procedure x_TruncateTable, Line 126
EXECUTE cannot be used as a source when inserting into a table variable.

plz clarify on this.I am executing it on sql server 2000.

thx
Post #648594
Posted Tuesday, February 3, 2009 1:11 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:46 AM
Points: 533, Visits: 990
can you post the code?

-Vikas Bindra
Post #648618
Posted Tuesday, February 3, 2009 1:45 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:46 AM
Points: 533, Visits: 990
As the code is not posted, I am guessing you are doing something like:

INSERT INTO @yourTableVar Execute yourSP

Please note the following things about the table variables
- You can not use the table variable to get the output of stored procedure.
- You can not use SELECT INTO with table variables.
- you can not use table variable in: INSERT INTO @tableVar SELECT * FROM anyTable.

and lot more...



-Vikas Bindra
Post #648629
Posted Tuesday, February 3, 2009 7:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 42,469, Visits: 35,540
vikas bindra (2/3/2009)
you can not use table variable in: INSERT INTO @tableVar SELECT * FROM anyTable.


You can't?

create table TestingVariables (id int, strng varchar(10))
insert into TestingVariables (id, strng) values (1,'a')
insert into TestingVariables (id, strng) values (2,'b')
insert into TestingVariables (id, strng) values (3,'c')
insert into TestingVariables (id, strng) values (4,'d')
GO

declare @Testng2 table (id int, somestring varchar(10))

insert into @Testng2
select * from TestingVariables

select * from @Testng2
GO

drop table TestingVariables




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #648862
Posted Wednesday, February 4, 2009 12:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:46 AM
Points: 533, Visits: 990
Hey Gail! Was this possible in SQL 2000 also?

-Vikas Bindra
Post #649563
Posted Wednesday, February 4, 2009 9:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 42,469, Visits: 35,540
vikas bindra (2/4/2009)
Hey Gail! Was this possible in SQL 2000 also?


Try it and find out. My SQL 2000 instance is 400km away.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #649958
Posted Wednesday, February 4, 2009 10:58 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:46 AM
Points: 533, Visits: 990
I too doesn't have SQL 2000.

-Vikas Bindra
Post #650573
Posted Thursday, February 5, 2009 11:08 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 3,194, Visits: 2,293
It workd on SQL 2000 ...

My version -


--------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2273 (Intel X86)
Mar 7 2008 22:19:58
Copyright (c) 1988-2003 Microsoft Corporation
Stand

(1 row(s) affected)


The results:


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(4 row(s) affected)

id somestring
----------- ----------
1 a
2 b
3 c
4 d

(4 row(s) affected)





Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #651103
Posted Thursday, February 5, 2009 1:36 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 42,469, Visits: 35,540
rudy komacsar (2/5/2009)
It workd on SQL 2000 ...


Thanks for the test.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #651231
Posted Friday, February 6, 2009 1:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:46 AM
Points: 533, Visits: 990
Thanks Rudy!!

-Vikas Bindra
Post #651441
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse