Run *.sql file direct on target machine, save PRINT output to result file.

[Rijswijker]

Hi i am struggling with a script, I want to run a SQL script on multiple machines.

What i do now is, copy a *.sql file to the target machine and run it with SQLCMD and write the output to a result file and copy the result file back to my machine.

What i want is:
I make a SQL Connection to the target machine and run the selected *.sql script directly without copying it.

But, i want only the result of the PRINT jobs in the script.

For example: (the content of the .sql script)

PRINT 'Start' 
PRINT '-----------------------------------------------------------------------------------------------------------------------------'
PRINT 'Facility Name:   ' + @ges 
PRINT 'Release/Version: ' + @rel + '.' + @upd + '.' + @rev
PRINT 'Hotfix:          ' + @HFName
PRINT '----------------------------------------------'
PRINT ''

BEGIN
    PRINT ''
    PRINT 'affected keydetectors for 125 KHz cards'
    PRINT '=========================================================='
    PRINT ''
    SELECT GER.GerNr AS 'DeviceNo', GER.GerBez AS 'DeviceDesignation', GER.GerKbez AS 'DeviceAbbreviation', KOMPONENTE.Info AS 'Info' FROM GER, KOMPONENTE 
    WHERE GER.GerNr = KOMPONENTE.GerNr AND GER.IstNichtVorhanden = 0 AND ( KOMPONENTE.Info LIKE '%VKM%' OR KOMPONENTE.Info LIKE '%VKX%')
    ORDER BY GER.GerNr 
END

How can i do this?

[mLipok]

ADO.Au3 udf

[Rijswijker]

  On 3/5/2016 at 1:45 PM, mLipok said:

ADO.Au3 udf

Expand  

Can you give a example?

I am now using _SQL.au3 udf and i think it is not possible with that script.
I also didn't see a function in de ADO.au3 udf what i can use.

Edited March 5, 2016 by Rijswijker

[mLipok]

start to read here:

Command Object (ADO)
https://msdn.microsoft.com/en-us/library/ms677502(v=vs.85).aspx
Command Object Parameters
https://msdn.microsoft.com/en-us/library/ms675101(v=vs.85).aspx

 

[Rijswijker]

  On 3/6/2016 at 1:02 PM, mLipok said:

start to read here:

Command Object (ADO)
https://msdn.microsoft.com/en-us/library/ms677502(v=vs.85).aspx
Command Object Parameters
https://msdn.microsoft.com/en-us/library/ms675101(v=vs.85).aspx

 

Expand  

Sorry but i don't understand how to convert this to Autoit.

If my problem is difficult to realize, you can also specify that. Then I let my script as he is now.

We are not all software developers in daily life.

Thanks in advance.

[mLipok]

Working on.

https://support.microsoft.com/en-us/kb/194792

  Quote

There are important issues to consider when attempting to retrieve RAISERROR/PRINT/RETURN values from SQL Server stored procedures through ActiveX Data Objects (ADO). Here are three issues:

• RAISERROR statements in SQL Server must be a severity level of 11-18.
• PRINT statements in SQL Server can also populate the ADO errors collection. However, PRINT statements are severity level zero (0) so, at least one RAISERROR statement is required in the stored procedure to retrieve a PRINT statement with ADO through the Errors collection.
• RETURN values in a stored procedure must be associated with at least one resultset.

Expand  

 

[mLipok]

If you want to use ADO I think you can not just use PRINT, you should store all information in variables and after script finish work you can grab this as OUT param with COMMAND object.

Are you interested in such solution ?

 

[Rijswijker]

Yes I am.

[mLipok]

Here:

 

You can see how to do that.

mLipok