0

I need to copy some tables from a SQL Server 2016 instance to a SQL Server 2008 instance like

select * 
into [sql8].[DatabaseA].[dbo].[Customers]
from [DatabaseA].[dbo].[Customers]

but I get an error:

Msg 117, Level 15, State 1, Line 9
The object name 'sql8.DatabaseA.dbo.Customers' contains more than the maximum number of prefixes. The maximum is 2.

I have tried generating a script of the data but my machine runs out of memory during SQLCMD execution from the command line.

Looking for recommendations / pointer.

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Deano
  • 1
  • 1
  • 2
    Possible duplicate of [error when insert into linked server](https://stackoverflow.com/questions/4744878/error-when-insert-into-linked-server) – Evaldas Buinauskas Jul 09 '17 at 15:38

1 Answers1

0

I'm guessing you may need to set up the sql8 server as a linked server from the Server holding the DB you're trying to get the data into. In the image I would be trying to get the data into a db on the MJAYWCO1 server. [sql8] would be the server you want to create a link "to". "[sql8].[DatabaseA].[dbo].[Customers]"

To Do this from the ssms GUI goto Server.ServerObjects.LinkedServers: enter image description here

Another possibility: Have you tried to import it directly to the new DB? Assuming you can connect to the old database from the new database with creds...

enter image description here

enter image description here

If this doesn't work, you can use the Export Data from the DB you are trying to get the data (under Import data in the second Image) from the "old DB" to create an XML or .CSV file, or whatever might be an applicable format. Use this and the Import Wizard from the "new DB"

Please forgive me if I misunderstood the question as English is my first language and I went to government schools.

Jay Wheeler
  • 379
  • 2
  • 7