What's the ASP.NET Connection String Format for a Linked Server?

Question

I've got a database server that I am unable to connect to using the credentials I've been provided. However, on the staging version of the same server, there's a linked server that points to the production database. Both the staging server and the linked server have the same schema.

I've been reassured that I should expect to be able to connect to the live server before we go live. Unfortunately, I've reached a point in my development where I need more than the token sample records that are currently in the staging database. So, I was hoping to connect to the linked server.

Thus far in my development against this schema has been against the staging server itself, using Subsonic objects. That all works fine.

I can connect via SQL Server Management Studio to that linked server and execute my queries directly. I can also execute 'manual" queries in C# against the linked server by having my connection string hook up to the staging server and running my queries as

SELECT * FROM OpenQuery([LINKEDSERVER],'QUERY')

However, the Subsonic objects are what's enabling me to bring this project in on time and under budget, so I'm not looking to do straight queries in my code.

What I'm looking for is whether there's a way to state the connection string to the linked server. I've looked at lots of forum entries, etc. on the topic and most of the answers seem to completely gloss over the "linked server" portion of the question, focusing on basic connection string syntax.

Answer 1

Maybe this will help you: http://www.connectionstrings.com/?carrier=sqlserver2005

http://www.connectionstrings.com/

Answer 2

Thanks Juan. Already looked there and, while there's lots of nice examples of connection strings, "linked server" isn't one of the ones they include.

Answer 3

creating a linked server from .NET doesn't make any sense since a linked server is nothing but a connection from one sqlserver to another server (sql, file, excel, sybase etc etc), in essence it is just a connection string (you can impersonate and do some other stuff when creating a linked server).

Answer 4

SQLMenace, note that I didn't say I was trying to create a linked server. I'm trying to connect to what's already there.

There exists a server [Server A] that I am unable to connect to directly (for networking or other reasons). Normally, this is the end of discussion until you clean up the problem that gets in the way. However, there exists another server [Server B]. I am able to connect to Server B. Server B is able to connect to Server A and has had that link formalized in the form of a linked server.

I am able to connect to Server A via Server B's link to Server A from within SQL Server Management Studio. However, because that functioning connection is embedded in the query directly, I'd like to know if you can embed that link into the connection string directly, like you do with an instance name.

The answer may well be "you can't". Do I believe this is a good idea for the final solution? No. Would I have pursued this setup deliberately from the beginning if I had control over the SQL Server instances? Absolutely not. Am I stuck and unable to do development against this data until someone fixes the network routing? Yes. Do I think that there might, in the future or right now, be someone else who is stuck with only a linked server as an option? Yes.

Hence the question.

Answer 5

I don't believe that you can access a linked server directly from an application without the OpenQuery syntax. Depending on the complexity of your schema, it might make sense to write a routine or sproc to populate your staging database with data from your live database.

You might also consider looking at Redgates SQL Data Generator or any other data gen tool. Redgates is pretty easy to use.

One other idea - can you get a backup of the live database that you can install in development to do your testing? If its just data for development and testing that you seek, you probably want to stay away from connecting to your production database at all.

Answer 6

Create testing stored procedures on server B that reference the data on server A via the linked server. e.g. if your regular sproc references a table on Server B say:

databaseA.dbo.tableName

then use the linked servername to reference the same database/table on server A:

linkedServerName.databaseA.dbo.tableName

If server A is identical in its database/table/column names than you will be able to do this by some quick find/replace work.

Answer 7

One Way is to create two connection strings and access the approperiate database when required. Second option is create connection for Database A only and create a link server For Databse B in Database.good article, i really like it. I am doing a bit on research about Asp.net connection and i found also macrotesting www.macrotesting.com to be very good source. Thanks for you article.....

Regards... Meganathan .J