cancel
Showing results for 
Search instead for 
Did you mean: 

How can I get TC to run query against SQL database?

SOLVED
Contributor

How can I get TC to run query against SQL database?

Hi everyone,

 

I have a test that I need to ask TC to run a query in SQL database to update some data, but I have no knowledge about database at all. I read about ADO Object and tried to do the same as showing in the example, but I was not sure what the value after ConnectionString was ("Provider=Microsoft.Jet.OLEDB.4.0;" + _"Data Source=C:\Users\Public\Documents\TestComplete 14 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb").

 

I asked our developer, and I was told that our database connecting string was something like "data source=xxx.xx.xxx.xxx;initial catalog=xxxx;integrated security=true;multipleactiveresultsets=true;app=xxxx", so is this the value I should place after AConnection.ConnectionString = ? I tried so, but I got a runtime error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." Not sure if it was because the connection code I executed was wrong or because the Microsoft Data Access Components was missing, because I tried to install the downloaded file ( v 2.8 SP1), but nothing happens after I double clicked on it.

 

I am totally lost, can someone please help me  to fix this?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Contributor

Re: How can I get TC to run query against SQL database?

Nevermind, I figured out, it should be DBConnection.Execute(Query to run)

7 REPLIES 7
Community Hero

Re: How can I get TC to run query against SQL database?

Hi,

 

> Multiple-step OLE DB operation generated errors.

This means that executed SQL contained more than one statement. ADO supports one statement per SQL, so instead of batch SQL execution you need to execute it line-by-line.

 

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/
================================
Contributor

Re: How can I get TC to run query against SQL database?

Hi @AlexKaras , I asked our database guy to take a look at my script below, and he doesn't know why it failed either. So my script is like this:

11 Sub try
12
13 Set AConnection = ADO.CreateConnection
14
15 AConnection.ConnectionString = "Provider=SQLNCLI11;data source=xxx-xxx-xxx.xx.xxxx.xxx;initial catalog=xxxxx;integrated security=true;multipleactiveresultsets=true;app=xxxxx;"
16
17 AConnection.Open
18
19
20 AConnection.Close
21
22 End Sub

 

Below is the error details returned:

VBScript runtime error.

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Error location:
Unit: "E-Academy\E-Academy\Script\WilliamTestScript"
Line: 17 Column: 3.

 

Looks like it was complainting about line 17 - AConnection.Open

 

Can you please give more instruction how to fix it? BTW, do I have to have SQL server 20xx installed on the machine first in order to run the script? because currently I don't have it nor the Native Client on the machine.

 

Thanks!

Community Hero

Re: How can I get TC to run query against SQL database?

Hi,

 

Does the same error occur if you remove 'multipleactiveresultsets=true;' from the connection string?

 

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/
================================
Contributor

Re: How can I get TC to run query against SQL database?

Yes, I removed 'multipleactiveresultsets=true;' from the connection string, and tried on both machines that have Native Client 11 and doesn't have Native Client 11.

 

It still returned the same VBScript runtime error "

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." on the machine that has Client 11, and it returned a different runtime error "
Provider cannot be found. It may not be properly installed.
" on the machine that doesn't have client 11.
Contributor

Re: How can I get TC to run query against SQL database?

Update, looks like the connection string I used was wrong. I take advantage of the wizard to build a connection string, and used it in the script, and it worked.

Contributor

Re: How can I get TC to run query against SQL database?

Hi @AlexKaras I think I still need your help regarding this. Now I can setup the SQL connection, but when I tried to execute the query below, it returned me another error: "Object doesn't support named arguments: DBConnection.CommandText".

 

Set DBConnection = ADO.CreateConnection
DBConnection.ConnectionString = "Provider=xxxx;Integrated Security=xxxx;Persist Security Info=False;User ID=xxxx;Initial Catalog=xxxx;Data Source=xxxx"
DBConnection.Open

DBConnection.CommandText = "My Query to Run"
DBConnection.Execute()

DBConnection.Close

Highlighted
Contributor

Re: How can I get TC to run query against SQL database?

Nevermind, I figured out, it should be DBConnection.Execute(Query to run)

New Here?
Join us and watch the welcome video:
SeptemberNews
Join the September Hub-bub to show off, learn and win