September 30, 2010 at 12:36 am
Hello I get an error when I run the following script, it seems I cant run an exec statment within a cursor select, is this true? if it is any ideads on how to resolve the issue below?
declare @Cname as varchar(100),
@sql as nvarchar(max),
@src_database as varchar(100),
@dest_database as varchar(100),
@cnt as int,
@rownum as int,
@pr_operators as varchar(50), --Backup database
@pr_data_admin as varchar(50),--Backup database
@Broker as varchar(50),--Backup database
@brokercompany as varchar(50),--Backup database
@BrokerOffice as varchar(50),--Backup database
@BrokerProductDetails as varchar(50),--Backup database
@db_type as varchar(10),
@sqln as nvarchar(500),
@ParmDefinition nvarchar(500)
-->USER SET VARIABLES<--
set @src_database = 'DB_Admin'--Source database name
set @db_type = 'lts'--Destination database name
----pr_data_admin----
--Set the amount of rows returned
set @sqln = ('select @CntOUT = count(*) from prpc_' + @db_type + '.sys.objects so inner join prpc_' + @db_type + '.sys.columns sc
on so.object_id = sc.object_id where so.name = ''pr_data_admin''')
SET @ParmDefinition = N'@CntOUT varchar(100) OUTPUT';
exec sp_executesql @sqln, @ParmDefinition, @CntOUT=@cnt OUTPUT;
if @cnt < 1 select 'no data in table' else
Begin
set @sql = 'INSERT INTO [prpc_' + @db_type + ']..[pr_data_admin]SELECT '
set @rownum = 0
DECLARE cur CURSOR FOR
exec ('select sc.name from prpc_' + @db_type + '.sys.objects so
inner join prpc_' + @db_type + '.sys.columns sc
on so.object_id = sc.object_id
where so.name = ''pr_data_admin''
order by column_id')
OPEN cur FETCH NEXT FROM cur INTO @Cname
WHILE @@FETCH_STATUS = 0
BEGIN
set @rownum = @rownum + 1
set @sql = @sql + '[' + @Cname + ']'
if @rownum < @cnt
else set @sql = @sql + ' from [' + @Src_database + ']..' + @pr_data_admin
FETCH NEXT FROM cur INTO @Cname
END
CLOSE cur DEALLOCATE cur
select @sql
end
September 30, 2010 at 12:58 am
Whats your error message ? can you post it ?
September 30, 2010 at 8:27 am
I get an incorrect syntax near exec?
I think the placement of the "Exec"/"Dynamic Sql" statement is invalid.
You can always pre-fetch the rows you need with an Insert...Exec(your statement) into a temp. table and use it as the source for the Cursor Declare statement.
As for an alternative way of solving your problem... what are you trying to do?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply