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

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 2

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.