Ask a Question

Can I push a set of lines to execute in SQL instead of line by line

SOLVED
herbie68nl
Occasional Contributor

Can I push a set of lines to execute in SQL instead of line by line

I Use VB Script

 

I recorded a trace file using the SQL profiler. This gives me all "exec ...." (stored) procedures that are executed by my desktop application.

 

Now I want to reuse tose lines so I can simulate the load without using the desktop client of the application

But as far as I found out now, I only can execute those lines one by one

 

Sub Test1()
  Dim arrTraceLine(4), numCount
  Set aTable = ADO.CreateADOQuery
  aTable.ConnectionString = "<Connection String>"
 

  arrTraceLine(0)= "exec dbo.<SP_Name1> @a_field1=N'100',@a_field2_t=N'9999999999',@a_field3_v=N''"

  arrTraceLine(1)= "exec dbo.<SP_Name2> @a_field1=100,@a_field2=212102,@a_field3_t=N'9999'"

  arrTraceLine(2)= "exec dba.<DB_Name3> @field1=100,@field2=NULL,@field3=NULL,@field4=N'1"
  arrTraceLine(3)= "exec dbo.<SP_Name3> @a_field1=1,@a_field2=87,@a_field3=N'',@a_field4=2"

  For numCount = 0 to 3
    aTable.SQL = arrTraceLine(numCount)
    aTable.ExecSQL
  Next
End Sub

 

In this Case I would prefer to push the TraceLines as one complete batch

For Example

  aTable.SQL = "exec dbo.<SP_Name1> @a_field1=N'100',@a_field2_t=N'9999999999',@a_field3_v=N'' " &_

               "exec dbo.<SP_Name2> @a_field1=100,@a_field2=212102,@a_field3_t=N'9999' " &_

               "exec dba.<DB_Name3> @field1=100,@field2=NULL,@field3=NULL,@field4=N'1 " &_
               "exec dbo.<SP_Name3> @a_field1=1,@a_field2=87,@a_field3=N'',@a_field4=2"

  atable.ExecuteSQL

 

This way I hope for a more readable Trace as result.

I think I need another option for  ADO.CreateADOQuery But What do I fhave to use.

I also tried ADO.CreateADOStoredProc But I have to split up each parameter in name, type and value this makes the script not very readable.

 

Can someone help me

Regards,

Herbert
1 ACCEPTED SOLUTION

Accepted Solutions
TanyaYatskovska
Community Manager

Re: Can I push a set of lines to execute in SQL instead of line by line

Hi Herbie68nl,

 

As far as I understand, you want to pass several SQL requests at once. This is impossible in ADO.

You need to think on how to change your request so that it selects all data you need, but in one call.

 

 

---------
Tanya Yatskovskaya
SmartBear Community and Education Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️

View solution in original post

3 REPLIES 3
TanyaYatskovska
Community Manager

Re: Can I push a set of lines to execute in SQL instead of line by line

Hi Herbie68nl,

 

As far as I understand, you want to pass several SQL requests at once. This is impossible in ADO.

You need to think on how to change your request so that it selects all data you need, but in one call.

 

 

---------
Tanya Yatskovskaya
SmartBear Community and Education Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️

View solution in original post

herbie68nl
Occasional Contributor

Re: Can I push a set of lines to execute in SQL instead of line by line

Thank you Tanya for your answer.

 

I already thaougt about this. But cound find a way to do this. We want to simulate the load to SQL from our application, and therefore I need to do it this way. 

But 'Can't be done' is also an answer and maybe it is the most clear answer of all. We are arleady searching for other tools where we can do this kind of testing.

Regards,

Herbert
TanyaYatskovska
Community Manager

Re: Can I push a set of lines to execute in SQL instead of line by line

This behavior isn’t related to TestComplete. You use ADO in this sample. This is one of the main specifics of ADO. If you want to test the load of your SQL server, I would suggest that you look at TestComplete’s Distributed Testing feature. Instead of executing the requests one by one (as you currently do), you can execute them simultaneously. I’m not familiar with your requirements, but that sounds reasonable for me.

---------
Tanya Yatskovskaya
SmartBear Community and Education Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
cancel
Showing results for 
Search instead for 
Did you mean: