Executing an Alter Table statement generated by dynamic sql to a Linked Server -
i have situation need add field table on linked server. specifications of dynamic , being done in tql / stored procedures , can not change. code generating statement fine , if copy paste new ssms window , execute works.. problem need dynamically generate statement (i doing fine, think). need execute statement in sproc, part not working.
here code:
set @altersql = @destinationservername + '.[' + @destinationdbname +'].' + @destinationschemaname + '.sp_executesql n'' alter table ' + @destinationtablename + ' add ' + @tempcolumn + ' int' + char(39)
the above creates when expose via print statement:
addb15.[fsparallel].dbo.sp_executesql n' alter table node add importidentity int'
after create statement use:
exec @altersql
and returns following error:
msg 2812, level 16, state 62, procedure etldynamicimport, line 244 not find stored procedure 'fsparallel.dbo.sp_executesql n' alter table node add importidentity int''.
can please advise on this? on project deadline , have googled no end , can not working.
looks you're missing exec
. try:
exec sp_executesql n'alter table fsparallel.dbo.node add importidentity int';
Comments
Post a Comment