SQLTeam.com | Weblogs | Forums

Run a batch file using SqlAgent or SSMS


#1

I am trying to run a batch file using SQL Agent.

.bat file: it opens a saved putty session using plink. Which is a mysql server hosted in AWS with keys saved in putty session.
plink -load putty_ss -l scar

I need to have this connection open for the linked server in SQL to work.

When i try to run from SSMS
EXEC master..xp_cmdshell 'K:\Open.bat it lists out the plink.exe commands and does not seem to execute the command after plink. I tried with quotes and w.o quotes no luck.

If i double click the bat file it can open the connection to the mysql server with my username with no issues, that way i can query the mysql db with the linked server in SQL.

I also tried using CMDExec in SQL and i get "the system cannot find the file specified" (There was a topic in this forum for that error but it wasnt useful as i had everything set correctly to begin with)

Questions:
Can I open a bat file that opens a putty saved session using plink via SQL JOB?
Has anyone come across this scenario?
Is there a easier way? I have hit the brick wall and if anyone can help that will be great!


#2

I`d give it a go with PoSH


#3

Same issue there as well.

When i run with Start PowerShell in SQL Server it could open the connection.
But i cannot open the saved session when in a sql agent and the next step is to extract the data from mysql and that crashes as the connection was not open (BUMMER)


#4

maybe another approach might be to create a linked server to your MySql?


#5

I have my linked server successfully created. The linked server was created using putty session and port forwarding within the session as it uses SSH.
But for the linked server to work the Putty Session has to be open. I do not know if there is a way to have the session always open just like other linked servers.

If i query the linked server: All i get is

OLE DB provider "MSDASQL" for linked server returned message "[MySQL][ODBC 5.3(a) Driver]Can't connect to MySQL server on 'localhost' (10061)".
Msg 7303, Level 16, State 1, Line 6
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server.

If i have the putty session open then it works well.


#6

Maybe i cannot do it via SQL. It has to be a code development to run the Stored Proc.


#7

Would this work?


#8

This is to create a Linked server if you have direct access to the public IP of the remote database and it will not work in my scenario.

What i have is a mysql database in AWS and i have to connect using SSH tunneling.
So i had to create SSH port forwarding and used a PUTTY to create the linked server.


#9

Sounds like a permission issue. Check the permissions of the proxy used to run the problematic script


#10

It was a permission issue and more to it.

When a putty session is saved it gets saved under the user who creates it. SQL has no way of knowing it, the reason why it didnt run in SQL Agent or in SQL Job.
Even when i got it to run, you cannot automate the task in SQL jobs as the command window will always run and that step never gets completed for the next step in a job to be executed.