December 2, 2006 at 1:15 am
Hi there
I written some tsql that uses a cursor to update certain tables in a database.
--CURSOR TO REPLACE SYS__DB VALUES WITH 20202
DECLARE @V_TABLE_NAME NVARCHAR(128)
DECLARE @V_UPDATE_SYS_DB NVARCHAR(128)
DECLARE @V_DB_NAME NVARCHAR(128)
DECLARE dbnames_cursor CURSOR FOR
SELECT ENT_ID FROM VehiclesSYS.dbo.ENTITIES
WHERE ENT_IS_VIRTUAL = 0
ORDER BY ENT_ID;
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor
INTO @V_TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
/* Process each database here. */
SET @V_DB_NAME = N'USE PEOPLE'
EXEC sp_executesql @V_DB_NAME
SET @V_UPDATE_SYS_DB = N'UPDATE @V_TABLE_NAME SET SYS__DB = 20202'
EXEC sp_executesql @V_UPDATE_SYS_DB
The problem lies with the update statement. The parameter @V_TABLE_NAME returns a list of tables, each table which is returned by the parameter @V_TABLE_NAME needs to have every row in its SYS__DB column updated to a value of 20202.
The rest of the tsql works fine, its just the update statement which is causing problems. I think its due to the fact that I have the parameter @V_TABLE_NAME in a string. But I dont know how write the update statement so that every table that @V_TABLE_NAME returns has its SYS__DB column updated. The reason Im using another database to return a list of tables is because VehiclesSYS.dbo.ENTITIES contains the full list of all the tables that every database uses.
Any help or suggestions would be greatly appreciate
December 2, 2006 at 10:03 am
Replace your update with the following...
SET @V_UPDATE_SYS_DB = N'UPDATE '+@V_TABLE_NAME+' SET SYS__DB = 20202'
MohammedU
Microsoft SQL Server MVP
December 2, 2006 at 3:58 pm
Thanks for that, but it didnt work, I got an incorrect syntax error on the last line:
EXEC sp_executesql @V_UPDATE_SYS_DB
December 2, 2006 at 5:16 pm
If you want to use sp_executesql, you can do something like this, or check BOL for accurate syntax:
EXEC sp_executesql @V_UPDATE_SYS_DB, N'@V_TABLE_NAME NVARCHAR(128)', @V_TABLE_NAME = @V_TABLE_NAME
If you are going by the solution given by Mohammed, try just this:
EXEC (@V_UPDATE_SYS_DB)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply