June 26, 2002 at 9:33 am
Hi there,
I am stuck with my problem for a sp which user passes in a flag value for keeping a new created table during process of the sp. I am using SQL Server 7 with the SQL statement SELECT ... INTO ... WHERE 1=2 for creating a structure of the new table. I have embedded the following code before and after the EXEC of the SELECT statements:
SELECT @DB_NAME = db_name()
EXEC sp_dboption @DB_NAME, 'select into/bulkcopy', 'TRUE'
SET @EXEC_SQL = 'USE ' + @DB_NAME
EXEC (@EXEC_SQL)
..... SELECT ... INTO... statement
EXEC sp_dboption @DB_NAME, 'select into/bulkcopy', 'FALSE'
SET @EXEC_SQL = 'USE ' + @DB_NAME
EXEC (@EXEC_SQL)
-----------------
However, I got an error message for "The procedure 'sp_dboption' cannot be executed within a transaction."
I have no clue how could I solve this problem. Anyone could help!
Millions thanks,
Arthur
June 26, 2002 at 9:40 am
Why not just build the table? It wouldn't be that hard to read information_schema.columns for the table and create the new table based on this. Of course, permissions are an issue.
Steve Jones
June 26, 2002 at 9:46 am
I can't because we are using dynamic SQL in which user create the table based on another select statement to collect the column names (might concatenate a variable value to create an unique column name). Anyway very dynamic. Create a temp table wouldn't be the case according to the sp requirements. So idea that I could work around with this?
Arthur
June 26, 2002 at 9:54 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply