cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Highlighted
Occasional Contributor

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

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();

}

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Hero

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

Hi,

 

Does WshShell.Run() help?

 

Regards,
Alex
[Community Expert Group]
____
[Community Expert Group] members are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Postings made by [Community Expert Group] members
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
[Community Expert Group] signature is used with permission by SmartBear Software.
http://smartbear.com/forums/f83/t86934/community-experts/
================================

View solution in original post

Highlighted
Community Hero

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


@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.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available

View solution in original post

8 REPLIES 8
Highlighted
Community Leader

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

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) .

 

Un sourire et ça repart

Highlighted
Occasional Contributor

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

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.

Highlighted
Community Hero

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

Hi,

 

Does WshShell.Run() help?

 

Regards,
Alex
[Community Expert Group]
____
[Community Expert Group] members are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Postings made by [Community Expert Group] members
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
[Community Expert Group] signature is used with permission by SmartBear Software.
http://smartbear.com/forums/f83/t86934/community-experts/
================================

View solution in original post

Highlighted
Community Hero

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


@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.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available

View solution in original post

Highlighted
Occasional Contributor

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

Can you please share the command if possible?

Highlighted
Community Hero

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

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")

Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
Highlighted
Occasional Contributor

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

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.

Highlighted
Community Hero

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

Hi,

 

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

 

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).

 

Regards,
Alex
[Community Expert Group]
____
[Community Expert Group] members are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Postings made by [Community Expert Group] members
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
[Community Expert Group] signature is used with permission by SmartBear Software.
http://smartbear.com/forums/f83/t86934/community-experts/
================================