April 22, 2015 at 8:11 am
I have a situation that I need to add a field to a table over linked server. The specifications of this is dynamic and it is being done in TQL / Stored procedures and this can not change. My code is generating the statement just fine and if I copy paste it to a new SSMS window and execute it WORKS.. The problem is I need to dynamically generate the statement (I am doing that just fine, I THINK). THEN I need to execute the statement IN THE SPROC, this part is not working.
Here is the code:
SET @AlterSQL = @DestinationServerName + '.[' + @DestinationDBName +'].' + @DestinationSchemaName + '.sp_executesql N'' ALTER TABLE '
+ @DestinationTableName + ' ADD ' + @TempColumn + ' int' + CHAR(39)
The above Creates this when I expose it via a PRINT statement:
addb15.[FSParallel].dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int'
After I create the statement I use:
EXEC @AlterSQL
And this returns the following error:
Msg 2812, Level 16, State 62, Procedure ETLDynamicImport, Line 244
Could not find stored procedure 'FSParallel.dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int''.
Can someone PLEASE advise on this? I am on a project Deadline and have googled this to no end and just can not get it working.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 22, 2015 at 8:21 am
Try this:
EXEC ('ALTER TABLE Node ADD ImportIdentity int') AT LinkedServerName
-- Gianluca Sartori
April 22, 2015 at 8:29 am
This did not work or I don't know the context in which to use it.
I am setting the statement to a variable. then executing that variable.
Example:
DECLARE @sql nvarchar(4000)
SET @sql = 'xxxxxx'
In my case:
SET @AlterSQL = @DestinationServerName + '.[' + @DestinationDBName +'].' + @DestinationSchemaName + '.sp_executesql N'' ALTER TABLE '
+ @DestinationTableName + ' ADD ' + @TempColumn + ' int' + CHAR(39)
THEN The next line is: EXEC (@AlterSQL)
How would I use your example above? I am not executing the created SQL manually, it needs to execute within the proc, then the proc continues on to do other things.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 22, 2015 at 8:30 am
sp_executesql isn't in your linked server db. It's in master.
The issue is that you really need to execute this as master.dbo.sp_executesql, but through a linked server.
You'd be better off using a stored proc on the other side that can take parameters, build the statement and have the sp_executesql run from that side.
April 22, 2015 at 8:33 am
Agreed but I can not. We are ETL'ing data from 100 + databases, servers. And I can not add anything to those (client machines). I Can add them as a link server and that is as far as I can go.
There must be a way to do this via my proc; as a linked server.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 22, 2015 at 8:45 am
Steve Jones - SSC Editor (4/22/2015)
sp_executesql isn't in your linked server db. It's in master.
Actually, to run in the context of a particular database, you can issue:
EXEC MyDatabase.sys.sp_executesql 'MyCommand'
-- Gianluca Sartori
April 22, 2015 at 8:47 am
It is being called from a SPROC. The call is going to a Linked Server so that wont work unfortunately .
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 22, 2015 at 8:52 am
There is no need for sp_executesql.
This should work as is.
SET @AlterSQL = 'ALTER TABLE ' + @DestinationTableName
+ ' ADD ' + @TempColumn + ' int;'
EXEC (@AlterSQL) AT LinkedServerName;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 22, 2015 at 8:55 am
Jeffery Williams (4/22/2015)
This did not work or I don't know the context in which to use it.I am setting the statement to a variable. then executing that variable.
Example:
DECLARE @sql nvarchar(4000)
SET @sql = 'xxxxxx'
In my case:
SET @AlterSQL = @DestinationServerName + '.[' + @DestinationDBName +'].' + @DestinationSchemaName + '.sp_executesql N'' ALTER TABLE '
+ @DestinationTableName + ' ADD ' + @TempColumn + ' int' + CHAR(39)
THEN The next line is: EXEC (@AlterSQL)
How would I use your example above? I am not executing the created SQL manually, it needs to execute within the proc, then the proc continues on to do other things.
DECLARE @sql nvarchar(max)
DECLARE @SQL2 nvarchar(max)
SET @sql2 = 'EXEC (@sql) AT ' + QUOTENAME(@DestinationServerName)
SET @sql = ' ALTER TABLE ' + QUOTENAME(@DestinationDBName) + '.' + QUOTENAME(@DestinationSchemaName) + '.' + QUOTENAME(@DestinationTableName) + ' ADD ' + QUOTENAME(@TempColumn) + ' int' + CHAR(39)
EXEC sp_executesql @sql2, N'@sql nvarchar(max)', @sql
-- Gianluca Sartori
April 22, 2015 at 8:59 am
I believe I am closer sir.
I did the following outside my proc:
DECLARE @SQL1 nvarchar(4000)
SET @SQL1 = 'ALTER TABLE ' + 'Node '
+ ' ADD ' + 'TempColumn' + ' int'
EXEC (@SQL1) AT addb15
After I execute I get he following error:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ADDB15' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name. .
Now I am performing this operation FROM --> to the same servers and such as I had been in the sproc. The script that the sproc creates works if executed manually.
I am not sure why it is asking for a login with this method?? Any ideas on next steps?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 22, 2015 at 8:59 am
Jeffery Williams (4/22/2015)
Agreed but I can not. We are ETL'ing data from 100 + databases, servers. And I can not add anything to those (client machines). I Can add them as a link server and that is as far as I can go.There must be a way to do this via my proc; as a linked server.
I'm confused. Are you using a linked server to access client databases? Are you trying to add a column to a table in a database you connect to using the linked server? And looping back, isn't that a client database where you just said you can't add anything?
April 22, 2015 at 9:00 am
SSCarpal Tunnel Trying now.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 22, 2015 at 9:05 am
spaghettidba,
Ok same thing here. I THINK It is really close. I copied your code exactly. I changed your @sql names to what I am using.
I get the following error now:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ADDB15' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name. .
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 22, 2015 at 9:10 am
I can not add procs or any other actual object. I can add the field that I need to, naturally. And after the import I will grab the NEW Identity created as a result of the consolidation AND what the identity was from the source server and copy that data to a Translation table. When that is complete I will drop the column I created. It is just a place holder to maintain the original Identity value; this is done so that I can map (update) the FK constrains on related tables.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 22, 2015 at 9:22 am
Alternative method:
DECLARE @AlterSQL nvarchar(4000)
SET @AlterSQL = 'EXEC ' + QUOTENAME(@DestinationServerName) + '.' + QUOTENAME(@DestinationDBName) +'.sys.sp_executesql N'' ALTER TABLE '
+ QUOTENAME(@DestinationSchemaName) + '.' + QUOTENAME(@DestinationTableName) + ' ADD ' + QUOTENAME(@TempColumn) + ' int'''
EXEC (@AlterSQL)
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply