Running statements in cursor returns Invalid object name error (SQL Server 2014)

Question

I am trying to execute statements gained from a cursor, and I keep getting an "Invalid object name" error. To start with, the cursor pulls information from a table that has a database name in one column, and a SQL statement in another. It is defined as such:

DECLARE commands CURSOR FOR 
    SELECT 
        REPLACE(DBNAME, DBNAME, 'USE ' + DBNAME),
        REPLACE(REPLACE(REPLACE(DESCRIPTION, 'OLDLINKEDSERVER', 'NEWLINKEDSERVER'), 'CREATE VIEW', 'ALTER VIEW'), 'CREATE  VIEW', 'ALTER VIEW') AS CMD
    FROM 
        #TMP2

Then, I define two commands:

DECLARE @cmd1 NVARCHAR(MAX)
DECLARE @cmd2 NVARCHAR(MAX)

So cmd1 is really just "USE DBName" where DBName is the name of the database, and cmd2 is a SQL statement to be run on that database.

For some reason, whenever I then iterate through the cursor:

OPEN Commands

FETCH NEXT FROM Commands INTO @cmd1, @cmd2

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql @cmd1
    EXEC sp_executesql @cmd2

    FETCH NEXT FROM Commands INTO @cmd1, @cmd2
END

CLOSE commands
DEALLOCATE commands

I get an error that says:

Msg 208, Level 16, State 6, Procedure NameOfView, Line 34
Invalid object name 'dbo.NameOfView'.

I've tried adding a "GO" command to cmd1, but it didn't work (I guess GO is only good for client tools). I tried using "exec(@cmd1)" instead of exec sp_executesql(@cm1)". If I tried to make it one command instead of two, it then says "ALTER VIEW must be the first statement in a query batch."

What can I do to get my cursor to work correctly?

EDIT: The following code is the working solution I found, thanks to IsItGreyOrGray (not sure if I'm supposed to edit it into my original post, or create a new one):

code for cursor

declare commands cursor for
SELECT REPLACE(DBNAME,DBNAME, 'USE ' + DBNAME),
REPLACE(REPLACE(REPLACE(DESCRIPTION,'OLDLINKEDSERVER', 'NEWLINKEDSERVER'),'CREATE VIEW', 'ALTER VIEW'), '''', '''''') AS CMD
FROM #TMP2

declarations

declare @cmd1 varchar(max)
declare @cmd2 varchar(max)

iterate through cursor

open commands
fetch next from commands into @cmd1, @cmd2
while @@FETCH_STATUS=0
begin
declare @cmd3 VARCHAR(MAX) = CONCAT(@cmd1, '; declare @cmd1 varchar(max) = ''', @cmd2, ''' exec(@cmd1)')
exec(@cmd3)
fetch next from commands into @cmd1, @cmd2
end
close commands
deallocate commands

Probably not building the dynamic SQL correctly, change the EXECUTE to Print statements so you can see what the statement actually is and try running that (and post here) — Brad
– Brad, Commented Sep 20, 2018 at 17:34
I did do that, and the print statements were just as I needed. I even copied and pasted from the print statements and executed them individually and it worked. It's strange to me that I can run the print statements, but yet the statements won't work inside of this while loop! — nightmare637
– nightmare637, Commented Sep 20, 2018 at 17:41
The DB that your using for the create/alter statements when you try to execute that in the loop, are you sure its the same one your in when you execute the print statements? (you may be using a different DB in your SQL window then the one the script for the loops are using) — Brad
– Brad, Commented Sep 20, 2018 at 17:43
I suspected it might be that; however, that is precisely why I have cmd1 be "USE DBName". I'm wondering if it's losing the database context after each command... If that is the case, what can I do to retain the context? — nightmare637
– nightmare637, Commented Sep 20, 2018 at 17:45
Have you considered building out the dynamic SQL using newline to mirror what you would expect to see from a created 'Alter View' script ? As an example, PRINT 'USE ['+ 'DBNAME' + ']' + CHAR(9) + CHAR(13) + 'GO' + CHAR(13) + 'ALTER VIEW' — Brent
– Brent, Commented Sep 20, 2018 at 17:50

GreyOrGray · Accepted Answer · 2018-09-20 19:36:23Z

1

Your issue is context switching.

Printing out the results will give you a working script that changes the databases correctly and makes the schema changes. But that's because it will all run in the context of your query window.

However, each EXEC runs in its own context.

If I run the following script, the output is "master". In @cmd1 I successfully switch to MSDB, but as soon as that command is complete, the context is lost and I'm back in MASTER. @Cmd2 then runs in its own context, which has not been switched from the database it was called from, so it runs in MASTER

 USE master
 GO
 DECLARE @cmd1 VARCHAR(1000) = 'USE msdb'
 DECLARE @cmd2 VARCHAR(1000) = 'SELECT DB_NAME()'

 EXEC(@cmd1)
 EXEC(@cmd2)

In order to make your cursor executions work, you'll need to combine @cmd1 with @cmd2 to make a single executable variable with something along the lines of

'USE [database1]; CREATE TABLE test (id INT)'

Since you're getting the query batch failures you might need to nest your executions...

This fails:

USE master
GO

DECLARE @cmd1 VARCHAR(1000) = 'USE msdb'
DECLARE @cmd2 VARCHAR(1000) = 'create procedure test as SELECT DB_NAME()'
DECLARE @cmd3 VARCHAR(MAX) = CONCAT(@cmd1, ' ', @cmd2)
EXEC (@cmd3)

This succeeds:

DECLARE @cmd1 VARCHAR(1000) = 'USE msdb'
DECLARE @cmd2 VARCHAR(1000) = 'create procedure test as SELECT DB_NAME()'
DECLARE @cmd3 VARCHAR(MAX) = CONCAT(@cmd1, '; declare @cmd1 varchar(max) = ''', @cmd2,''' exec(@cmd1)' )
EXEC (@cmd3)

edited Sep 20, 2018 at 19:36

answered Sep 20, 2018 at 18:19

GreyOrGray

1,73811 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

nightmare637 Over a year ago

I tried altering my statement as you suggested. However, if I modify it to say 'USE [database1]; CREATE TABLE', it says that CREATE TABLE must be the first statement in a query batch! What could I do to fix that? By the way, thank you very much for your answer! This explains why the object is not getting found.

GreyOrGray Over a year ago

added an option to try

nightmare637 Over a year ago

I feel this is much closer to the solution I need; the issue now is that my SQL code has some quotation marks in it, thus I cannot try it out (it is breaking up the text). I'm researching now how to include all statements without the quotations in them.

nightmare637 Over a year ago

Thank you so much!!! It fixed my problem! I had to nest my executions, exactly as you said. I did have to use another instance of REPLACE() to swap out all instances of a single quotation with double quotations in my cursor. After that, it was smooth sailing. Again, thank you!!!

nightmare637 Over a year ago

For anyone else struggling with this, I edited my original post to include the updated/correct code.

Collectives™ on Stack Overflow

Running statements in cursor returns Invalid object name error (SQL Server 2014)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related