0

I have two databases, both on the same server the default is named XXX the second is XXX\MSSQKSERVERVIS7

I need to copy a table from the second db to the default. When I attempt to use the server name I get an error that the \ is incorrect syntax. The query is quite simple I run the query from the default instance 

  SELECT * 
  INTO table 
  FROM XXX\MSSQLSERVERVIS7.[db].[dbo].[table]

If I put brackets around the server name I get the error that the object doesn't exist.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Terry S
  • 23
  • 1
  • 6
  • This SO answer may provide some additional help for your problem. http://stackoverflow.com/questions/187770/copy-tables-from-one-database-to-another-in-sql-server – Xgongiveittoya Dec 18 '15 at 21:20

2 Answers2

2

What you have is 2 SQL Server instances, both running on the same physical server. XXX is the default instance and XXX\MSSQKSERVERVIS7 is a named instance. From the SQL Server perspective they are 2 separate servers. You can't directly query between them without a little extra configuration.

You need to create a linked server from the default instance (XXX) to the named instance (XXX\MSSQLSERVERVIS7). Then you would use the linked server name in your SELECT.

squillman
  • 13,363
  • 3
  • 41
  • 60
  • The server is linked. I can transfer the opposite direction without a problem. I have checked using SELECT * FROM sys.servers. Both have the other server as a linked server but I still get the error. – Terry S Dec 21 '15 at 14:01
0

I found the problem. It was in the security settings when linking the servers. One was set correctly and worked. The other wasn't.

Terry S
  • 23
  • 1
  • 6