Generating variable data

Database stress testing tool

Moderator: Support Team

Generating variable data

Postby Nick Northfield » Tue Jan 08, 2013 10:32 am

Hi,

Is it possible to use the variable data markers (?1, ?2, etc) to query that database and get a result to be used in a statement?


The statement below (taken from SQL Server Profiler) uses the value 307378 in the stored procedure spr_SPOperationList. I need to be able to get this value from the database at run time for it to work correctly.

exec spr_SPOperationList '8B06D155-F75A-43F7-A8A5-BAC5428495F1',0,default,307378,default,default,default


Can I do something like this: -

exec spr_SPOperationList '8B06D155-F75A-43F7-A8A5-BAC5428495F1',0,default,?1,default,default,default

In the variable data test file I would put the following statement to retrieve the value for the variable data marker: -

select TMLastOperationRecordID from tblSYLocalPolicyCounter


Any attempt to do this results in the following statement being executed...

exec spr_SPOperationList '8B06D155-F75A-43F7-A8A5-BAC5428495F1',0,default,select TMLastOperationRecordID from tblSYLocalPolicyCounter,default,default,default


Regards,

Nick.
Nick Northfield
 

Re: Generating variable data

Postby Support Team » Tue Jan 08, 2013 6:07 pm

Yes, Nick.
You can use "data file" as a source for variables value.
Also you can use built-in data generation engine to create complex values dynamically.

About your example. You can use $Rtint() pattern for variable definition to generate random integer value.
Support Team
 
Posts: 248
Joined: Sun Mar 26, 2006 3:48 pm

Re: Generating variable data

Postby Nick Northfield » Wed Jan 09, 2013 11:45 am

Hi Support Team,

I am using a data file with the following statement: -

select TMLastOperationRecordID from tblSYLocalPolicyCounter

This (when run independently in SQL Server) gets me the value I need for LastOperationRecordID, but when the script is run in DTM DB Stress this SQL statement is not executed, it is simply placed into the main SQL statement where the variable placeholder (?1) was. At this point the statement cannot run as it is syntactically incorrect, see below: -

exec spr_SPOperationList '8B06D155-F75A-43F7-A8A5-BAC5428495F1',0,default,select TMLastOperationRecordID from tblSYLocalPolicyCounter,default,default,default

I cannot use the random value generator as this will mean the script will be working with incorrect values and will ultimately fail.

Essentially my script is in two parts. The first part places a record in the database using LastOperationRecordID - this is an incremental counter so its value is not known until the record is saved. The second part removes the record by referencing LastOperationRecordID as set in the first part. If I run one of these scripts on its own it works fine as it is always using the same value, but once the value is incremented above what the script expects it will never remove the record. This is why I need to, during execution, query the database for the correct value to be used in the second part of the script.

Is there any way I can dynamically query the database to retrieve a value which can then be used in a script?

Regards,

Nick.
Nick Northfield
 

Re: Generating variable data

Postby Support Team » Wed Jan 09, 2013 11:57 am

My colleague will contact you by e-mail for details.
Support Team
 
Posts: 248
Joined: Sun Mar 26, 2006 3:48 pm

Re: Generating variable data

Postby Support Team » Wed Jan 09, 2013 12:46 pm

Nick,

I'm sorry for misunderstanding. The problem is clear now.
Please try to:
1) specify following statement in the task property
exec spr_SPOperationList '8B06D155-F75A-43F7-A8A5-BAC5428495F1',0,default,?1,default,default,default

At the "parameter" tab
2) switch to "use generated value" for ?1 parameter
3) select "custom pattern" as type of the parameter
4) enter following pattern to "custom expression or pattern" edit box:
$Query(select TMLastOperationRecordID from tblSYLocalPolicyCounter)

Save task and run it.
Support Team
 
Posts: 248
Joined: Sun Mar 26, 2006 3:48 pm


Return to DTM DB Stress

Who is online

Users browsing this forum: No registered users and 2 guests

cron