0

I'm getting a login error with the below sqlcmd to connect to a linked server:

SQLCMD -E -dlinkedserver -ig:\temp\script.sql -o g:\temp\test.txt

My linked server which is an oracle connects through the app account from the linked server.

Is there anything wrong with the above code, it tried to login to the linked server, run a scripts, and log to a file.

  • The linked server exists inside a SQL Server instance. sqlcmd has no idea what that linked server is. Reference your SQL Server instance in sqlcmd and then have your sql script reference the linked server. – Tara Kizer Mar 20 '18 at 17:24
  • The purpose is to connect to oracle server copy data to a sql instance – SQL_NoExpert Mar 20 '18 at 17:25
  • @user9394033 Then SQLCMD needs to connect to SQL Server first and reference the linked server in the script. – Aaron Bertrand Mar 20 '18 at 17:25
  • can you show an example pls. – SQL_NoExpert Mar 20 '18 at 17:27
  • Linked server (sql instance ) connects to oracle server and then executes a query and log to a text file this is the whole idea – SQL_NoExpert Mar 20 '18 at 17:31
4

If you want to use the linked server defined in a SQL Server instance, then you need to connect to the SQL Server instance that hosts the linked server, and make sure that your commands reference the linked server objects using 4-part naming. The SQLCMD program has no idea what a linked server is, so it can't connect to a linked server directly.

Let's say your linked server is named MyLinkedServer then your .sql file should look something like this:

INSERT SQLServerDatabase.dbo.TableName(columns) -- SQL Server table
  SELECT columns FROM MyLinkedServer..[Schema].[TableName]; -- Oracle table

Or:

INSERT SQLServerDatabase.dbo.TableName(columns) -- SQL Server table
  SELECT columns FROM OPENQUERY
  (
    MyLinkedServer, 
    N'SELECT columns FROM [Schema].[TableName]' -- Oracle table
  );

And your SQLCMD would not say anything about the linked server name at all, because it gets information about the linked server from inside the script:

SQLCMD -E -S Server\Instance -i g:\...\file.sql -o g:\...\log.txt

Of course there are other ways to run queries against Oracle if you don't need to rely on (a) SQLCMD or (b) the linked server defined in the SQL Server instance.

  • SQLCMD -E -S Server\Instance -i g:\...\file.sql -o g:\...\log.txt---I will try this route since my sql query references the linked server – SQL_NoExpert Mar 20 '18 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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