Replication cursor [modified]
-
I am adding articles to be replicated but I would like to use a cursor to do it due the amount of tables it may take forever to select all from the wizard. I have written a cursor and I am having issues running it. Any suggestion will be greatly appreciated. Cursor: declare @tableName varchar(100) declare c cursor for select table_name from information_schema.tables where table_type = 'base table' open c fetch next from c into @tableName while @@fetch_status = 0 begin --print 'table name: ' + @tableName --Adding the transactional articles exec sp_addarticle @publication = N'One_Way_Trans', @article = @tableName , @source_owner = N'dbo', @source_object = @tableName, @destination_table = @tableName, @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000000CEF3, @status = 16, @vertical_partition = N'false', @ins_cmd ='CALL sp_MSins_' + @tableName, @del_cmd = N'CALL sp_MSdel_' + @tableName, @upd_cmd = N'MCALL sp_MSupd_' + @tableName, @filter = null, @sync_object = null, @auto_identity_range = N'false' fetch next from c into @tableName end close c deallocate c I am getting the following error when I run this script: Line 99: Incorrect syntax near '+'. Here are the values I can not populate with @tableName. @ins_cmd ='CALL sp_MSins_' + @tableName, @del_cmd = N'CALL sp_MSdel_' + @tableName, @upd_cmd = N'MCALL sp_MSupd_' + @tableName, PS: I got the code below to do exactly what I want it to do but notice it is only printing the reults not executing the stored procedure. declare @tableName varchar(100) declare c cursor for select top 10 table_name from information_schema.tables where table_type = 'base table' open c fetch next from c into @tableName while @@fetch_status = 0 begin --print 'table name: ' + @tableName --Adding the transactional articles print 'exec sp_addarticle @publication = N''One_Way_Trans'', @article = N ''' + @tableName + ''' , @source_owner = N''dbo'', @source_object = N''' + @tableName + ''', @destination_table = N'''+ @tableName + ''', @type = N''logbased'', @creation_script = null, @description = null, @pre_creation_cmd = N''drop'', @schema_option = 0x000000000000CEF3, @status = 16, @vertical_partition = N''false'', @ins_cmd = N''CALL sp_MSins_' + '' + @tableName + ''', @del_cmd = N''CALL sp_MSdel_' + ''+ @tableName + ''', @upd_cmd = N''MCALL sp_MSupd_' + '' + @tableName + ''', @filter = null, @sync_object = null, @au
-
I am adding articles to be replicated but I would like to use a cursor to do it due the amount of tables it may take forever to select all from the wizard. I have written a cursor and I am having issues running it. Any suggestion will be greatly appreciated. Cursor: declare @tableName varchar(100) declare c cursor for select table_name from information_schema.tables where table_type = 'base table' open c fetch next from c into @tableName while @@fetch_status = 0 begin --print 'table name: ' + @tableName --Adding the transactional articles exec sp_addarticle @publication = N'One_Way_Trans', @article = @tableName , @source_owner = N'dbo', @source_object = @tableName, @destination_table = @tableName, @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000000CEF3, @status = 16, @vertical_partition = N'false', @ins_cmd ='CALL sp_MSins_' + @tableName, @del_cmd = N'CALL sp_MSdel_' + @tableName, @upd_cmd = N'MCALL sp_MSupd_' + @tableName, @filter = null, @sync_object = null, @auto_identity_range = N'false' fetch next from c into @tableName end close c deallocate c I am getting the following error when I run this script: Line 99: Incorrect syntax near '+'. Here are the values I can not populate with @tableName. @ins_cmd ='CALL sp_MSins_' + @tableName, @del_cmd = N'CALL sp_MSdel_' + @tableName, @upd_cmd = N'MCALL sp_MSupd_' + @tableName, PS: I got the code below to do exactly what I want it to do but notice it is only printing the reults not executing the stored procedure. declare @tableName varchar(100) declare c cursor for select top 10 table_name from information_schema.tables where table_type = 'base table' open c fetch next from c into @tableName while @@fetch_status = 0 begin --print 'table name: ' + @tableName --Adding the transactional articles print 'exec sp_addarticle @publication = N''One_Way_Trans'', @article = N ''' + @tableName + ''' , @source_owner = N''dbo'', @source_object = N''' + @tableName + ''', @destination_table = N'''+ @tableName + ''', @type = N''logbased'', @creation_script = null, @description = null, @pre_creation_cmd = N''drop'', @schema_option = 0x000000000000CEF3, @status = 16, @vertical_partition = N''false'', @ins_cmd = N''CALL sp_MSins_' + '' + @tableName + ''', @del_cmd = N''CALL sp_MSdel_' + ''+ @tableName + ''', @upd_cmd = N''MCALL sp_MSupd_' + '' + @tableName + ''', @filter = null, @sync_object = null, @au
Never Mind, I got it working. What I did was I ran the "print" script, pulled copied the result and rn it in Quer Analzer. It ran just fine and created all my Articles. PS: I could not believe I was such a dork. But then again no where tells you that you need to run one script to create another. Thanks guys. OBTW: It would be nice if someone can explain wh I can not add a variable name to a stored procedure dnamically.
Greg Coding makes the world go round!!!