June 13, 2014 at 11:29 am
Hello,
I have been using dynamic sql in an inner cursor and I get an error about the fetched variable of the outer cursor. How can I fix it?
DECLARE A1 CURSOR GLOBAL FOR
SELECT Iid, Server, Dbname
FROM@OuterTable;
OPEN A1;
FETCH NEXT FROM A1 INTO @aId, @aServer, @aDbname;
WHILE @@fetch_status <> -1
BEGIN
SET @SQLCursor = 'DECLARE D1 CURSOR GLOBAL FOR
SELECT Id, StatusId, Server, DbName FROM [' + @aServer + '].['+@aDbname+'].dbo.Rooms;';
EXEC sp_executesql @SQLCursor;
OPEN D1;
FETCH NEXT FROM D1 INTO @dId, @dStatusId, @dServer, @dDbname;
WHILE @@fetch_status <> -1
BEGIN
SET @sql='SELECT @aId, @dId, @dStatusId, d.DateCreated, d.DocumentSize, dah.ActivityId, dah.DocDeleted
FROM [' + @dServer + '].['+@dDbname+'].dbo.Documents d
INNER JOIN DocumentActivityHistory dah ON d.Id = dah.DocumentId;';
INSERT INTO @DocActivity
EXEC sp_executesql @sql; --, '@aId nvarchar(50) output',@aId output;
FETCH NEXT FROM D1 INTO @dId, @dStatusId, @dServer, @dDbname;
END
CLOSE D1;
DEALLOCATE D1;
FETCH NEXT FROM A1 INTO @aId, @aServer, @aDbname;
END
CLOSE A1;
DEALLOCATE A1;
And the error is;
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@aId".
Thanks,
June 13, 2014 at 11:56 am
Quick question and bear with me hear, but why do you need the cursor, the inner sql has no exec statement?
😎
June 13, 2014 at 12:03 pm
Eirikur Eiriksson (6/13/2014)
Quick question and bear with me hear, but why do you need the cursor, the inner sql has no exec statement?😎
SET @sql='SELECT @aId, @dId, @dStatusId, d.DateCreated, d.DocumentSize, dah.ActivityId, dah.DocDeleted
FROM [' + @dServer + '].['+@dDbname+'].dbo.Documents d
INNER JOIN DocumentActivityHistory dah ON d.Id = dah.DocumentId;';
INSERT INTO @DocActivity
EXEC sp_executesql @sql;
The inner loop actually selects and inserts into @DocActivity.
June 13, 2014 at 12:04 pm
Kuzey (6/13/2014)
Hello,I have been using dynamic sql in an inner cursor and I get an error about the fetched variable of the outer cursor. How can I fix it?
And the error is;
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@aId".
Thanks,
You commented the parameters code. If you uncomment it, you need to use @dId instead of @aId on the third parameter for sp_executesql.
June 13, 2014 at 12:05 pm
There are two types or databases in our systems; parent and child. This script is supposed to go through the parent databases first (as outer loop), pick up child db names from parent db, then loop through child databases, and populate a table variable.
June 13, 2014 at 12:15 pm
Luis Cazares (6/13/2014)
Kuzey (6/13/2014)
Hello,You commented the parameters code. If you uncomment it, you need to use @dId instead of @aId on the third parameter for sp_executesql.
When I run this;
EXEC sp_executesql @sql, '@aId nvarchar(50) output', @dId output ;
I get;
Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.
June 13, 2014 at 12:17 pm
Kuzey (6/13/2014)
Eirikur Eiriksson (6/13/2014)
Quick question and bear with me hear, but why do you need the cursor, the inner sql has no exec statement?😎
SET @sql='SELECT @aId, @dId, @dStatusId, d.DateCreated, d.DocumentSize, dah.ActivityId, dah.DocDeleted
FROM [' + @dServer + '].['+@dDbname+'].dbo.Documents d
INNER JOIN DocumentActivityHistory dah ON d.Id = dah.DocumentId;';
INSERT INTO @DocActivity
EXEC sp_executesql @sql;
The inner loop actually selects and inserts into @DocActivity.
Sorry, missed the exec, phone playing tricks on my:-P
First, change the EXEC to PRINT and check the actual dynamic code!
😎
June 13, 2014 at 12:19 pm
Kuzey (6/13/2014)
Luis Cazares (6/13/2014)
Kuzey (6/13/2014)
Hello,You commented the parameters code. If you uncomment it, you need to use @dId instead of @aId on the third parameter for sp_executesql.
When I run this;
EXEC sp_executesql @sql, '@aId nvarchar(50) output', @dId output ;I get;
Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.
EXEC sp_executesql @sql, N'@aId nvarchar(50) output', @dId output ;
You need the N to denote the nvarchar.
😎
June 13, 2014 at 12:24 pm
You're using more variables than the ones declared. It should be more like this:
EXEC sp_executesql @sql, N'@aId nvarchar(50) output, @dId nvarchar(50), @dStatusId nvarchar(50)',@aId output, @dId, @dStatusId;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply