Multiple server SQL query in .Net

Question

I have a query that currently runs successfully on SQL Server Management Studio, gathering information from 2 databases on the SQL server where I run the query, and one database on a linked SQL Server. The query has this structure:

SELECT TOP 10
    DB1.id_number as BLN,
    DB1.field1,
    DB2.field2,
    DB3.field3
FROM
    LinkedServer.database1.dbo.table1 DB1
INNER JOIN
    database2.dbo.table2 DB2 ON DB1.id_number = DB2.id_number
INNER JOIN
    database3.dbo.table3 DB3 ON DB3.id_number2  = DB1.id_number2
WHERE 
    DB1.id_number IS NOT NULL
    AND DB1.field1 IS NOT NULL

How can I run this same query from a .Net application? I am looking for a solution that doesn't require saving a View on the database.

In whatever solution you propose, please detail connection strings and security issues.

Thank you.

Do you mean run a functionally identical query that doesn't use a linked server? — Dan Guzman
– Dan Guzman, Commented Sep 1, 2017 at 18:46
You would put this query in a stored procedure just like every other data access step in your application. There is nothing different about it. — Sean Lange
– Sean Lange, Commented Sep 1, 2017 at 19:04
A handy reference regarding connection strings: connectionstrings.com/sql-server — Xedni
– Xedni, Commented Sep 1, 2017 at 20:25
I don't have access to modifying the DB, so I cannot create a stored procedure or view for the data I am extracting. I want to run this same query, but I can't just open the SQLConnection to the main DB, create a new SQLCommand and then get my results with a SQLDataReader. Apparently involving a linked server in the query requires something else that I am missing. It seems to me that there should be a way to pass three connection strings, one for DB2, one for DB3 and one for the linked server DB1, but I haven't found it. — David Ruiz
– David Ruiz, Commented Sep 5, 2017 at 14:49
The same credentials you are using on the SQL Server Management Studio to run the query should be enough to run the exact same query with a SQLCommand on .Net — Shago
– Shago, Commented Sep 24, 2017 at 20:18

Shago · Accepted Answer · 2017-09-01 18:50:06Z

2

You can run the query using SqlCommand. Although doing it with an ORM may be a little tricky, if it even can be done.

answered Sep 1, 2017 at 18:50

Shago

6054 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Ruiz Over a year ago

How would you include the linked server in the SqlCommand? Would you use more than one connection?

Shago Over a year ago

No need for different connection. The server you are connecting to knows how to access the linked server.

David Ruiz Over a year ago

Ok, and what about credentials to access the linked servers? Would the same credentials for the main server be used when connecting to the linked servers?

Shago Over a year ago

If you need to connect directly to the linked server you would need a different connection with a different connection string. If you would query the linked server only from the main server then you just need credentials to the main server, credentials to the linked server would be stored on the link configuration between servers.

Collectives™ on Stack Overflow

Multiple server SQL query in .Net

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related