dynamic SQL drop and select into

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

SQL Server 2000 Forums

SQL Server Development (2000)

dynamic SQL drop and select into

Author

Topic

caelms
Starting Member

7 Posts

Posted - 2003-07-08 : 16:31:06

The following procedure is processed in a production environment. It drops a table if it exists and then does a select into to recreate it to another database after the users have completed their processing.

The procedure works and recreats the table but I they get the following error message:

'Could not find table name 'object_id'. Check sysobjects'

CREATE PROCEDURE quick_save_test
@wrk_save_prefix varchar(255), --test 20030630
@wrk_table varchar(255) --ce_cfscodes

AS
SET CONCAT_NULL_YIELDS_NULL OFF
BEGIN TRANSACTION

DECLARE @wrk_quicksaved_tablename varchar(255)
DECLARE @wrk_exec varchar(255)
DECLARE @wrk_message varchar(255)
DECLARE @wrk_count_exec varchar(255)
DECLARE @wrk_column_descriptor varchar(255)
DECLARE @wrk_workarea_prefix varchar(255)
DECLARE @wrk_saved_rowcount int

if @wrk_save_prefix = ' 'or
@wrk_table = ' '
BEGIN
SELECT @wrk_message = 'ERROR: Prefix / Saved Tablename Cannot be Blank '
RAISERROR (@wrk_message, 0, -1)
ROLLBACK TRAN
RETURN -1
END

/*
Build name of Quick-Saved Table
*/
SELECT @wrk_workarea_prefix = 'workarea.dbo.'
SELECT @wrk_quicksaved_tablename = @wrk_workarea_prefix + @wrk_save_prefix + @wrk_table
SELECT @wrk_column_descriptor = '@wrk_row_count'
/*
Build DROP TABLE command
*/
if exists (select * from workarea.dbo.sysobjects where id = object_id(@wrk_quicksaved_tablename) and sysstat & 0xf = 3)
BEGIN
SELECT @wrk_exec = ' DROP TABLE '
SELECT @wrk_exec = @wrk_exec + @wrk_quicksaved_tablename
exec (@wrk_exec)
IF @@ERROR <> 0
BEGIN
SELECT @wrk_message = 'ERROR: UNABLE TO DROP TABLE ' + @wrk_quicksaved_tablename
RAISERROR (@wrk_message, 0, -1)
END
END
/*
Build SELECT/INTO command
*/
SELECT @wrk_exec = ' SELECT * '
SELECT @wrk_exec = @wrk_exec + ' INTO '
SELECT @wrk_exec = @wrk_exec + @wrk_quicksaved_tablename
SELECT @wrk_exec = @wrk_exec + ' FROM '
SELECT @wrk_exec = @wrk_exec + @wrk_table

exec (@wrk_exec)
SELECT @wrk_saved_rowcount = @@rowcount
IF @@ERROR <> 0
BEGIN
SELECT @wrk_message = 'ERROR: UNABLE TO PERFORM A SELECT INTO FOR ' + @wrk_quicksaved_tablename
RAISERROR (@wrk_message, 0, -1)
ROLLBACK TRAN
RETURN -1
END
COMMIT TRAN
return @wrk_saved_rowcount
GO
SET QUOTED_IDENTIFIER OFF

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-08 : 17:02:11

I ran your stored procedure several times on my machine and it works fine. I do not get any errors (well after I figured out what was needed to run the stored procedure).

What I would do is store the dynamic SQL statements in a table so that you can go back and see what SQL was running. I believe that if you run SQL Profiler that you'll only be able to see that the stored procedure is executing and not the individual commands. So you need to save the dynamic SQL somewhere. We store it in a table for debugging purposes. When we go into production, we remove this feature. Since you are already in production with this, you could do this temporarily.

Here is what to do if you want to go by my suggestion:

CREATE TABLE StoreDynamicSQL
(
DynamicSQL VARCHAR(7000) NOT NULL,
CreateDate DATETIME NOT NULL
)

Right before you run EXEC(@wrk_exec),
INSERT INTO StoreDynamicSQL
SELECT @wrk_exec, GETDATE()

Then when someone gets the error, go to this table and see what statements ran around that time and then run them in Query Analyzer or figure out what went wrong.

Tara

caelms
Starting Member

7 Posts

Posted - 2003-07-09 : 07:46:11

I'll give it a try and post the results either today or tomorrow. I also ran it several times on the test server with no problems. So I think it is going to be interesting to find what the cause of the error could be. Thanks for your help.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-09 : 11:55:14

Well this seem to work fine (which is what I think you're doing)...

BUT there GOT to be a better way than building a destroying onjects on the fly..gonna keep the catalog real busy.

Is all of the table structure always the same? or do they vary?

This should help with the object checking...



USE Northwind
GO

CREATE TABLE myTable99 (col1 int)
GO

CREATE PROC mySproc99
	@tbname sysname
AS

DECLARE @sql varchar(8000), @rc int

SELECT @sql = 'select 1 from sysobjects where id = object_id('+''''+@tbname+''''+') and sysstat & 0xf = 3'

EXEC(@sql)

SELECT @rc = @@ROWCOUNT

SELECT 'ROWS: ' + Convert(varchar(3),@rc)

IF @rc <> 0
	BEGIN
		SELECT @sql = 'DROP TABLE '+ @tbname
		EXEC(@sql)
		SELECT @@Error
	END


SELECT @sql = 'SELECT 1 AS Col1 INTO ' + @tbname

EXEC (@sql)

SELECT @@Error

SELECT @sql = 'SELECT * FROM ' + @tbname

EXEC (@sql)

SELECT @@Error

GO

EXEC mySproc99 'myTable99'
GO

DROP TABLE myTable99
GO

DROP PROC mySproc99
GO

Brett

8-)

caelms
Starting Member

7 Posts

Posted - 2003-07-09 : 14:26:01

This proc renames the table to another database with another prefix. The structure of the source table is whatever the batch program accepts through the procedure.

Along with Tara recommendations, I have tried to check the following
suggestion but not yet in production, where the error occurs:

If OBJECT_ID(@wrk_quicksaved_tablename) IS NOT NULL
then create the dynamic sql to drop it.

We probably won't be trying this until next week so I'll folow up with a post then. Thanks for everyone's suggestions.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-09 : 14:43:03

Well this should help with the drop as well if Tara's doesn't do it:

(It's in the code I posted above)


SELECT @sql = 'select 1 from sysobjects where id = object_id('+''''+@tbname+''''+') and sysstat & 0xf = 3'

EXEC(@sql)

SELECT @rc = @@ROWCOUNT

SELECT 'ROWS: ' + Convert(varchar(3),@rc)

IF @rc <> 0
	BEGIN
		SELECT @sql = 'DROP TABLE '+ @tbname
		EXEC(@sql)
		SELECT @@Error
	END

If @@ROWCOUNT is 0 then it doesn't exist.

If it's <> 1 then it does (in reality it will only be a 1 or 0)

GOOD LUCK

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources