Hi I would like to be able to work with TWO different SQL Servers and Database in ONE SQL script. ie: - Transfer data to/from MyDASPServer_Database from/to MyLocalServer_Datebase - Work on MyDASPServer_Database while also working on MyLocalServer_Datebase in the same SQL script I know I can do this with a SSIS dtsx package but I would like to be able to do it in a Script also. If this is possible could someone please give me the syntax, to connect to two differernt SQL 2008 R2 servers, or a link that explains how to do this. SQL Server 2008 R2 at Discount ASP.NET Thanks in advance FrankA
You should take a look at this link. In order to work with different databases on different servers, you need to specify the four-part object qualifier: server.database.schema.object For example: [sql2k801.discountasp.net].[mydb_name].[dbo].[customer_table] You also need to setup the DiscountASP.NET database as linked server in your local SQL Server Instance. You can only setup a linked server from your local SQL Server instance. It doesn't work the other way around (i.e. we don't allow linked servers from the DiscountASP.NET database servers).
RayH Thank You for your response. I think I got it working with your help. When I am connected to my Local Server and I execute the following "SELECT" I get the response I expect from my DASP Database. SELECT Top(10) * FROM [MySQLServer??????.discountasp.net].[SQL??????_??????_MyDASPDatabase].[dbo].[MyTable????????] Note: I have hidden some character for security, but I think you get the idea. Took a little time to get the Linked server set up but I think it is working. ONE more question: ???????? When I use my local server now, does the Linked Server cause me to be connected to my DASP server even when I am not using the DASP Server OR does the Link Server only active when the "FROM [MySQLServer??????.discountasp.net].[SQL??????_??????_MyDASPDatabase].[dbo].[MyTable????????]" is used on a TSQL command ???????? Thanks again Frank A
I'm actually not sure. I think you only create the connection when you execute the T-SQL command. You might want to review the Microsoft documentation: http://msdn.microsoft.com/en-us/library/ff772782.aspx http://technet.microsoft.com/en-us/library/ms188279.aspx
