irratic behaviour with sp_executesql

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)

irratic behaviour with sp_executesql

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-19 : 10:42:52

Pieter writes "After battling for days, I've realised that SQL Server 7 does not behave consistently at all:

I have written 2 stored procedures, the one calling the other

creating a the new database in VB, (even in query analyzer) worked fine, (see code below)

running the code, when calling the 1st stored procedure, produces the following error:

Server: Msg 8630, Level 16, State 77, Line 1
Internal Query Processor Error: The query processor encountered an unexpected error during execution.

changing the second stored procedure (by deleting one of the + char(13) 's and pressing apply, running
it again, getting the same error, adding the + char(13) again, pressing apply running
it again, changing it again, by deleting another of the + char(13) 's and pressing apply, running
it again) and ...., wait for it,........ IT WORKS!

as soon as I've dropped the database and recreated it, I have to go through the same mission to get it working again :(

Can anyone give me advice on this irratic behaviour?

Thanking in advance

VB Code:

dbname = "LX2Archive_" & ShipmentDescription & "_" & Format(Now(), "yyyymmdd")
sql = "Create Database " & dbname
Set l_rs = GetRS(StraightSQL, sql, "", "", rsoDynamic)
' SET NEW DB'S BULKCOPY TO TRUE
sql = "exec sp_dboption '" & dbname & "', 'select into/bulkcopy', 'TRUE'"
Set l_rs = GetRS(StraightSQL, sql, "", "", rsoDynamic)

where GetRS is my ADO connection, firing off the query

The first Storedproc is called from VB as:

sql = "exec sp_ArchiveShipment '" & dbname & "', " & ShipmentUSN
Set l_rs = GetRS(StraightSQL, sql, "", "", rsoDynamic)

First StoredProc Code:

CREATE PROCEDURE sp_ArchiveShipment
@DBName Varchar(50),
@ShipmentUSN int
AS
DECLARE @Filename nVarChar(50)
DECLARE @NewDBFilename nVarChar(100)
DECLARE @LogEx2Filename nVarChar(100)
DECLARE @stmt nVarChar(200)

---- CREATE DUPLICATE OF LIVE DARRA INTO @DBName
DECLARE Archive_cursor CURSOR FOR
select name from sysobjects where xtype = 'U' order by name

OPEN Archive_cursor
---- Perform the first fetch.
FETCH NEXT FROM Archive_cursor into @Filename
---- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
---- This is executed as long as the previous fetch succeeds.
set @NewDBFilename = @DBName + '.dbo.' + @Filename
set @LogEx2Filename = N'LogEx2.dbo.' + @Filename
EXECUTE sp_ArchiveShipmentItem @NewDBFileName, @LogEx2Filename
FETCH NEXT FROM Archive_cursor into @Filename
END
--print 'Loop Done'
CLOSE Archive_cursor
DEALLOCATE Archive_cursor
--print 'Cursor Deallocated'

Second Storedproc Code

CREATE PROCEDURE sp_ArchiveShipmentItem
@ToDBTableName nVarchar(100),
@FromDBTableName nVarchar(100)
AS

DECLARE @SQLString nVarChar(200)

SET @SQLString = N'SELECT * into ' + char(13)
SET @SQLString = @SQLString + @ToDBTableName
SET @SQLString = @SQLString + N' from ' + char(13)
SET @SQLString = @SQLString + @FromDBTableName + char(13)
PRINT @SQLString
EXEC sp_executesql @SQLString"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources