Exec (string), but temp table not found

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Exec (string), but temp table not found

Author

Topic

Mad Ray
Starting Member

3 Posts

Posted - 2008-07-05 : 07:12:15

Hi All,

i created a procedure that MUST
1. construct an SQL string
2. execute it.
3. query the results.

i created the sql string and it inputs the results in a hash table.
later when trying to get data from the hash table i am getting an "Invalid object name " error.

i tried a simpler query but with the same result.

create PROCEDURE [dbo].[test_string]
AS
declare @sqlStr varchar(2500)
set @sqlStr = 'select * into #lll from sysobjects '
exec (@sqlstr)
select * from #lll
GO

call test_string,
i will get

(216 row(s) affected)

Server: Msg 208, Level 16, State 1, Procedure test_string, Line 14
Invalid object name '#lll'.

Regards
Ray

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-05 : 09:38:25

the temp table #lll only exists within the context of the exec(), it is automatically drop when exec() completed. Use global temp table instead.

KH
[spoiler]Time is always against us[/spoiler]

Mad Ray
Starting Member

3 Posts

Posted - 2008-07-05 : 10:00:19

Thanks,
i tried something else, and it seems it is working fine now, i converted the temp table to a "non-temp table" concatinating the SPID to it. and it is working well.
thanks alot Khatan again.
Ray

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-07 : 05:59:00

You could do


CREATE PROCEDURE test_string
AS BEGIN
	CREATE TABLE #dump (
		[name] NVARCHAR(128)
		, [id] INT
		, [xtype] CHAR(2)
		, [uid] SMALLINT
		, [info] SMALLINT
		, [status] INT
		, [base_schema_ver] INT
		, [replinfo] INT
		, [parent_obj] INT
		, [crdate] DATETIME
		, [ftcatid] SMALLINT
		, [schema_ver] INT
		, [stats_schema_ver] INT
		, [type] CHAR(2)
		, [userstat] SMALLINT
		, [sysstat] SMALLINT
		, [indexdel] SMALLINT
		, [refdate] DATETIME
		, [version] INT
		, [deltrig] INT
		, [instrig] INT
		, [updtrig] INT
		, [seltrig] INT
		, [category] INT
		, [cache] SMALLINT
	)

	DECLARE @sql VARCHAR(2500)

	SET @sql = 'INSERT INTO #dump SELECT * FROM sysObjects'

	EXEC (@Sql)

	SELECT * FROM #dump
END

instead and you wouldn't have to use a process key'd table or a global temp table. (wouldn't a global temp table be terminated (if nothing is referencing it) when the EXEC finished anyway)?

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-07 : 06:01:56

And, if you are having trouble specifying the right datatypes and lengths to use for something like this you can get them with a...


SELECT * INTO _dump FROM sysObjects WHERE 1 = 0

SELECT ', [' + COLUMN_NAME + '] ' + UPPER(DATA_TYPE) + ISNULL('(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ')', '') FROM information_Schema.columns WHERE TABLE_NAME = '_dump'

DROP TABLE _dump

-------------
Charlie

Mad Ray
Starting Member

3 Posts

Posted - 2008-07-08 : 02:50:22

thanks a lot Charlie,
this looks the best solution for me to use.
i will adopt your first method, by creating the #table, then fill it in a sql string to execute.

very nice, simple and straight to the point.

Ray

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources