Ray: Thank you so much. That was exactly the problem. Appreciate the help.
Andy
----- Original Message -----
Your dynamic SQL is executing outside of the scope of your other SQL, so
your temporary table is gone as soon as @sSQL is finished executing.
Try using a global temporary table by prefixing the name with ## instead
of #. Example:
Declare @sSQL Varchar(1000)
SET @iIDentAlbumStart = (SELECT MAX(i_AlbumID) + 1 FROM tAlbum)
SET @sSQL = 'CREATE TABLE ##tempAlbum (
i_AlbumNewID int IDENTITY (' + CONVERT(Varchar(10),@iIDentAlbumStart) +
', 1) NOT
NULL ,
fk_ValIDFonts int NULL
) ON [PRIMARY]'
EXEC sp_sqlexec @sSQL
SELECT * FROM ##tempAlbum
DROP TABLE ##tempAlbum
Ray at work
>-----Original Message-----
> From: Andy and Kim Mills [mailto:[EMAIL PROTECTED]
>Sent: Tuesday, January 25, 2005 11:01 AM
>
To: [email protected]
>
Subject: [ASP] PART II - Create Table With Identity Column
>
>
>
>Hello Guys: I am getting an 'invalid object name #tempAlbum'
>error, and not sure
>
why. Below is my code for creating the temp table with the
>identity value. However:
>when I try to access the #tempAlbum, I cannot do so.
>DECLARE @iIDentAlbumStart
>
int
>
Declare @sSQL Varchar(1000) SET @iIDentAlbumStart = (SELECT
>MAX(i_AlbumID) + 1
>
FROM tAlbum)
>SET @sSQL = 'CREATE TABLE #tempAlbum (
>
i_AlbumNewID int IDENTITY (' +
>CONVERT(Varchar(10),@iIDentAlbumStart) + ', 1) NOT
>
NULL ,
>
fk_ValIDFonts int NULL
>) ON [PRIMARY]'
>EXEC sp_sqlexec @sSQL
>
SELECT * FROM #tempAlbum
>
Thanks Andy
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>--------------------------------------------------------------
>
-------
>Home :
>[http://groups.yahoo.com/group/active-server-pages]http://groups.yahoo.com/group/active-server-pages
>---------------------------------------------------------------------
>
Post : [email protected]
>
Subscribe : [EMAIL PROTECTED]
> Unsubscribe: [EMAIL PROTECTED]
> ---------------------------------------------------------------------
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
---------------------------------------------------------------------
Home :
[http://groups.yahoo.com/group/active-server-pages]http://groups.yahoo.com/group/active-server-pages
---------------------------------------------------------------------
Post : [email protected]
Subscribe : [EMAIL PROTECTED]
Unsubscribe: [EMAIL PROTECTED]
---------------------------------------------------------------------
Yahoo! Groups Sponsor
ADVERTISEMENT
[http://us.ard.yahoo.com/SIG=129ighcq6/M=297844.5692940.6932969.3356155/D=groups/S=1705115381:HM/EXP=1106755884/A=2533537/R=0/SIG=10v7of2am/*http://www.target.com/careers]
--------------------------------------------------------------------------------
Yahoo! Groups Links
� To visit your group on the web, go to:
[http://groups.yahoo.com/group/active-server-pages/]
http://groups.yahoo.com/group/active-server-pages/
To unsubscribe from this group, send an email to:
[mailto:[EMAIL PROTECTED] [EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the
[http://docs.yahoo.com/info/terms/]Yahoo!
Terms of Service .
[Non-text portions of this message have been removed]
---------------------------------------------------------------------
Home : http://groups.yahoo.com/group/active-server-pages
---------------------------------------------------------------------
Post : [email protected]
Subscribe : [EMAIL PROTECTED]
Unsubscribe: [EMAIL PROTECTED]
---------------------------------------------------------------------
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/active-server-pages/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
