Linked Server - SELECT .. INTO

Question

Post reply

Linked Server - SELECT .. INTO

hindle.steve

SSCrazy

Points: 2057
More actions
October 4, 2011 at 3:19 am

#244770

I am trying to insert data into a database on a remote server using linked server:
SELECT * INTO [LinkedServerName].[RemoteDBName].dbo.[RemotetableName] FROM [LocalDBName].dbo.[LocalTableName]
However I ma getting therror:
Error 117 The object name '...' contains more than the maximum number of prefixes. The maximum is 2.
Is this possible as all the examples I have seen have performed the insert from the remote server as it were.
Thanks
Steve

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You can't do this. A linked server table is not a valid target for SELECT ... INTO.

You will have to create the table and the insert data with INSERT INTO.

Hope this helps

Gianluca

-- Gianluca Sartori

shatrughna Ten Centuries Points: 1037 More actions · Answer 2

Hi,

Check this post.

http://www.sql-server-helper.com/error-messages/msg-117.aspx

Shatrughna

hindle.steve SSCrazy Points: 2057 More actions · Answer 3

Thanks for the confirmation.I ended up creating linked server on the destination server back to the source and executing this:

SELECT * INTO DBName.dbo.' + @TableName + ' FROM ' + @LinkedServerDestination + '.DBName.dbo.TableName) AT ' + @LinkedServerSource

hindle.steve SSCrazy Points: 2057 More actions · Answer 4

One final issue. I'm trying to dynamically build the execute_sql part:

SET @DynamicSQL = @LinkedServerREP + '.<DBName>.dbo.sp_executesql '

SET @SQL = 'N''DROP TABLE ' + @TableName + ''''

--PRINT @SQL

EXECUTE @DynamicSQL @SQL

This errors with "Incorrect syntax near 'DROP TABLE <tablename>". When I do a PRINT the string looks fine? Can anyone see what the issue is? A missing/additional quote somewhere?

Also, I have declared @SQL as NVarChar. Does this mean I still have to include the N when building the string?

Any help with this I would appreciate.

Thanks

Steev

shatrughna Ten Centuries Points: 1037 More actions · Answer 5

Hi,

Try to write your T-SQL as below.

DECLARE @TABLE1 NVARCHAR(30)

DECLARE @TABLE2 NVARCHAR(30)

DECLARE @SQL NVARCHAR(1000)

SET @TABLE1 = 'dbo.City'

SET @TABLE2 = 'dbo.Marks'

SET @SQL = N'SELECT * FROM '+@TABLE1+';'+'SELECT * FROM '+@TABLE2

EXEC(@SQL)

Shatrughna

chandan_jha18 SSChampion Points: 11330 More actions · Answer 6

hindle.steve (10/4/2011)
I am trying to insert data into a database on a remote server using linked server:
SELECT * INTO [LinkedServerName].[RemoteDBName].dbo.[RemotetableName] FROM [LocalDBName].dbo.[LocalTableName]
However I ma getting therror:
Error 117 The object name '...' contains more than the maximum number of prefixes. The maximum is 2.
Is this possible as all the examples I have seen have performed the insert from the remote server as it were.
Thanks
Steve

If you want to insert data to server B by reading something from server A, create your queries on server B thus read the data from link server and insert locally rather than doing the other way round.

lets assume that you want to insert in a table kept on servr B so go to server B and write something like:

select * into B.database.dbo.tabB

from

openquery(A,'select col1,col2,col3 from database.dbo.tabA where...')

This will work better always.

Do check some old posts by Linchi Shea who wrote some nice articles on linked server performance. Whatever solution you implement, please make sure they are able to scale and does not haunt you in the long run.

Thanks

Chandan

hindle.steve SSCrazy Points: 2057 More actions · Answer 7

Thanks some good info there. I did get it working using:

SET @DynamicSQL = @LinkedServerREP + '.<DBName2>.dbo.sp_executesql '

SET @SQL = N'DROP TABLE ' + @TableName + ''

EXECUTE @DynamicSQL @SQL

--This creates and populates a new table each time

EXECUTE ('SELECT * INTO <DBName2>.dbo.' + @TableName + ' FROM ' + @LinkedServerDB + '.<DBName1>.dbo.' + @TableToCopy + '') AT [LinkedServerREP]

It was the N national character bit before the DROP string - I was trying to build it into the string which was an error.

I like the last post, but in our case the rep database may be restored from a live database and so these sprocs would be lost (although I would have to create the linked server(s) again) - so we're trying to push everything from live.

Thanks again