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
> ---------------------------------------------------------------------
> Post : [email protected]
> Subscribe : [EMAIL PROTECTED]
> Unsubscribe: [EMAIL PROTECTED]
> ---------------------------------------------------------------------
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
---------------------------------------------------------------------
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/
