0

I have two servers, both has a sql server . Let's say srv1 has a sql-srv1 and srv2 with a sql-srv2 , i want to get some informations within sql-srv2 but making requests from sql-srv1.

my project is to create a script which could export data within sql-srv2 , in creating a bat files and to execute the script every day with task scheduler but first of all i have to connect the 2 sql-server and i don't know guys how to connect it, i'm a beginner. I just want to create a script like

sqlcmd -S . -d demo -E -s";" -w 700 -Q "select * from etudiant" > C:\troisiemetest%date%.csv

which can run everyday on the 2 sql servers linked. Thank you

4
  • Seems like a perfect use for an SSIS package executed from an SQL Agent job ;) Maybe you want to have a look at this as it seems to me like a better approch then a batch file that connect to SQL using SQLCMD Feb 6, 2020 at 15:41
  • Why the mysql tag if you are only using SQL Server?
    – user1822
    Feb 6, 2020 at 16:35
  • So is this MySQL or SQL Server??? They're 2 completely different products. Please tag properly.
    – Eric
    Feb 6, 2020 at 16:42
  • @Eric SQL server , sorry Feb 7, 2020 at 11:11

1 Answer 1

1

Welcome to the group....You can also use linked server, below link could help you

https://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server

As per the comments, adding the details from the thread mentioned

Querying across 2 different databases is a distributed query. Here is a list of some techniques plus the pros and cons:

Linked servers: Provide access to a wider variety of data sources than SQL Server replication provides

Linked servers: Connect with data sources that replication does not support or which require ad hoc access

Linked servers: Perform better than OPENDATASOURCE or OPENROWSET

OPENDATASOURCE and OPENROWSET functions: Convenient for retrieving data from data sources on an ad hoc basis. OPENROWSET has BULK facilities as well that may/may not require a format file which might be fiddly

OPENQUERY: Doesn't support variables All are T-SQL solutions. Relatively easy to implement and set up All are dependent on connection between source and destionation which might affect performance and scalability

Follow these steps to create a Linked Server: 1. Server Objects -> Linked Servers -> New Linked Server 2. Provide Remote Server Name. 3. Select Remote Server Type (SQL Server or Other). 4. Select Security -> Be made using this security context and provide login and password of remote server.

1
  • is it necessary that the ip class of the two servers is the same ? the first server ip is like 170.20.x..x and the second 192.168.x.x . is the connexion possible between them ? Feb 7, 2020 at 5:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.