0

I have created a "Link Server" in my SQL server 2012. Now I want to get data from a table which is under a "Link Server". So for that, what will be the connection string to connect with a database under "Link Server"?

In the console application I have tried this.

<add key="smartdb" value="Data Source=192.168.1.141; Initial Catalog=mydb;User Id=sa;Password=sa123" />

But it's not working. I don't know where to set the database name, which is under "Linked server".

BSMP
  • 4,596
  • 8
  • 33
  • 44
Maruf Ahammed Juwel
  • 149
  • 3
  • 10
  • https://stackoverflow.com/a/4091984/2993606 – SouravA Apr 24 '18 at 06:34
  • https://learn.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?view=sql-server-2017 or you can use 4 part names (Server.Database.Schema.Table) where the server name is the name of the linked server. – Pred Apr 24 '18 at 06:35

3 Answers3

0

You will have to use a four-part query to connect to your linked server:

<server-name>.<database-name>.<schema-name>.<table-name>

So in your connection string change the data source.

DEEPAK LAKHOTIA
  • 993
  • 5
  • 10
  • Yes. First i need a connection. So in connection string how to set database name. – Maruf Ahammed Juwel Apr 24 '18 at 06:40
  • You said, that you've created the linked server already... you can specify the initial catalog there, or you can set up the user on the remote server to have the desired database as default. You can specify the database in the query (using 3 or 4 part names). `OPENQUERY` has no 'database' parameter, and when you are using 4 part names, that name contains the database name already. So what exactly you have done and working and what is the exact problem? – Pred Apr 24 '18 at 06:44
  • @MarufAhammedJuwel First connect to your parent sql server where you have the linked sql server created and from there you run your queries as open queries and get the data. Open query: https://learn.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?view=sql-server-2017 https://www.mssqltips.com/sqlservertip/1757/dynamic-sql-execution-on-remote-sql-server-using-exec-at/ – DEEPAK LAKHOTIA Apr 24 '18 at 06:55
0

Try this:

   <add name="sqlConnection" connectionString="your connection string" providerName="System.Data.SqlClient"/>

Tell me if it works.

BSMP
  • 4,596
  • 8
  • 33
  • 44
Alexis Villar
  • 371
  • 1
  • 3
  • 15
0

I found a solution by putting initial catalog "master". Then I can query Like:

<add key="smartdb" value="Data Source=192.168.1.141; Initial Catalog=master;User Id=sa;Password=sa123" />

and query 

select * from MYLINK.SPL.dbo.Departments

Now I can get result expected.

BSMP
  • 4,596
  • 8
  • 33
  • 44
Maruf Ahammed Juwel
  • 149
  • 3
  • 10