Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

The object name ... contains more than the maximum number of prefixes. The maximum is 2. Expand / Collapse
Author
Message
Posted Thursday, June 16, 2011 9:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 12, 2013 8:51 AM
Points: 1, Visits: 3
Assuming the servers have been set up as linked servers, you can run a query in the context of ServerB while still using a connection to ServerA (assuming having privileges on both databases and servers)

exec ServerB.DatabaseB.dbo.sp_executesql N'INSERT INTO ServerA.DatabaseA.dbo.TableA SELECT * FROM DatabaseB.dbo.TableB(or ViewB or FunctionB)'

To use with a function, you can do something similar to
exec ServerB.DatabaseB.dbo.sp_executesql N'INSERT INTO ServerA.DatabaseA.dbo.TableA SELECT * FROM DatabaseB.dbo.
FuncB(@input)',N'@input varchar(MAX)', @input='whatever'

This allows you to bypass issues when you need the 4 part name, but of course I would recommend testing your query directly on ServerB first, and then applying the above
Post #1126656
Posted Friday, April 27, 2012 10:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 16, 2014 12:39 PM
Points: 93, Visits: 300
I've also found that this works: EXEC(‘TRUNCATE TABLE [Test_DB].dbo.thisTable’) AT [Link-Server]
Post #1291705
Posted Friday, May 16, 2014 12:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 9:08 AM
Points: 3, Visits: 424
One needs to use the square brackets [ ] in this case (one of the few cases when there is no escape).

So the incorrect syntax SERVER.DOMAIN.DATABASE.SCHEMA.TABLE must become

[SERVER].[DATABASE].[SCHEMA].[TABLE]

e.g.

SELECT TOP 1 *
FROM [MYSERVER.MYDOMAIN.MYCORP.MYCOMPANY.COM].[MYDATABASE].[dbo].[MYTABLE] ;
Post #1571858
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse