How to run a .sql file in sqlcmd mode in Javascript via TestComplete

[TCqauser]

I have a JavaScript file where I connect to the database and have to run a .sql file in order to create lots of tables etc.

I have managed to read the file, but when I execute it, it fails. The reason is that the .sql file has to be run in sqlcmd mode in order to execute.

Now I am stuck how to enable sqlcmd mode in my JavaScript file, in order to run it in TC.

Would be thankful for any assist and help.

Here comes my Javascript file:

-----------------------

function ExecuteScript()
{

var s = aqFile.ReadWholeTextFile("C:\\Database\\SQLQuery.sql", aqFile.ctUTF8);

var server = "xx.xx.xx.xx";
var dbmaster = "master";

aCon = ADO.CreateConnection();
aCon.ConnectionString = "Driver={SQL Server};Server=" + server + "; Database=" + dbmaster + ";Trusted_Connection=yes;";
aCon.Open();

queryStringSource = s;

aCmd = ADO.CreateCommand();
aCmd.ActiveConnection = aCon;
aCmd.CommandType = adCmdText;
aCmd.CommandText = queryStringSource;
aCmd.CommandTimeout = 300;
aRecSet = aCmd.Execute();

aCon.Close();

}

[BenoitB]

Not sure to understand, your question is about how to make these ?

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

BTW dont forget about checking connection state (connection.State) and post-Execute errors checking (connection.errors.count) .

[TCqauser]

No its about running sql queries in SQLCMD mode.

Its quite easy doing it directly via SQL Server Management Studio, but I want to do it via my JS script I have.

https://www.sqlservercentral.com/blogs/enable-sqlcmd-mode-in-sql-server-management-studio

If SQLCMD mode is not enabled the query wont run.

The connection to the database works fine, I am able to restore the database, so no problem with that.

[AlexKaras · Accepted solution]

Hi,

Does WshShell.Run() help?

[tristaanogre · Accepted solution]

---

@AlexKaras wrote:

Hi,

Does WshShell.Run() help?

---

I was just going to suggest that.  This is how I've done SQLCMD in the past.  I've created a command line routine that uses WshShell.Run that executes the SQLCMD command line to run the file.

[TCqauser]

Can you please share the command if possible?

[tristaanogre]

Here's microsoft's documentation on the sqlcmd command-line syntax.

https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-the-utility?view=sql-server-ver15

All you need to do is, when you want to execute sqlcmd, to format the command line as documented and do something like

WshShell.Run("sqlcmd blah blah blah")

[TCqauser]

I managed to make it work.

In TC I ran this command:

WshShell.Run("powershell -file C:\\MyScript.ps1");

and in my 'MyScript.ps1' file I had this:

invoke-sqlcmd -ServerInstance xxx -Database xxx -InputFile C:\\Temp\\Sqlscript.sql

Never worked for me to run sqlcmd or invoke-sqlcmd directly in WshShell.Run(), in TC.

Thanks for your help.

[AlexKaras]

Hi,

The below is just my comment as you already got it working 🙂

> Never worked for me to run sqlcmd or invoke-sqlcmd directly in WshShell.Run(), in TC.

invoke-sqlcmd is related to PowerShell, thus it will never work outside it.

As for the sqlcmd utility, like Robert wrote, you should compose proper command line for it and provide this command line to WshShell.Run() (properly escaping quotes, if any).