Powershell calls to SQL - thread safe?

[Neil Hayes]

This should be simple and certainly not rocket science, yet I can't get this to work 100% of the time.

1. I have several jobs that are allowed to run concurrently.

2. All jobs use the same Powershell Script

3. Each job has different environment variables making them unique.

What does the Powershell script do?

1. Query database master tables

2. Drops SQL databases pertaining to a naming convention base on the environment variables - This makes the jobs unique.

3. Restore SQL databases

4. Insert/select data on SQL tables

5. execute business logic

A really simple process

In the Powershell script a string of the SQL command is build and then passed to the function CallSQL along with the database to use - here is the function below.

Import-Module “sqlps” -DisableNameChecking

function CallSQL($dbToUse, $QueryString) #-Call SQL and run the passed code

{

    

    Write-host "DB to use is : $dbToUse "

    write-host "execute SQL : $QueryString"

    Return $ResultSet = Invoke-Sqlcmd –ServerInstance $env:computername –Database $dbToUse –Query $QueryString

}

If I run one Jenkins job at a time or sequentially this works a tread.....no issues....perfect.

If I should run two jobs concurrently then one job will fail on executing the SQL select statement to a table. It fails because the 'other' job is busy running a restore or an entirely different database not related to the job that has failed. 

TS901db is in status ONLINE

Execute Select * from [TS901db].[dbo].[Admin]  WHERE XYZ='EDU1'
DB to use is : Master 
execute SQL : Select * from [TS901db].[dbo].[Admin]  WHERE XYZ='EDU1'
Invoke-Sqlcmd : Database 'TS024_EDU6' cannot be opened. It is in the 
middle of a restore.
At C:\Jenkins\scripts\Data.ps1:19 char:25
+     Return $ResultSet = Invoke-Sqlcmd -ServerInstance $env:computername 
-Databas ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerS 
   hellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShe 
   ll.GetScriptCommand
 

So due to the timing of the two jobs, 1 doing a restore (TS024_EDU6) and the other doing a select (TS901db) , the invoke-Sqlcmd seems to have picked up data from another thread.

You can see from the function that I write to the log the SQL statement to execute, the next line actually does the SQL statement, yet the results pertain to another database.

Question:

How do I resolve this?

I need the jobs to run concurrently (each job may go on for 4 -5 hours) and I need to add more similar jobs. 

Open to ideas.

Neil

[Neil Hayes]

Anyone?

[Maciej Jaros]

It's not a Jenkins problem. if you would run this from two PS terminals then you would probably run into the same issues.

I would guess that PS is sharing SQL connection or something like that. Maybe you should use `osql` or `sqlcmd` to run commands you need to run...

In any case not a Jenkins issue really so you are asking in a wrong place.

Regards,
Nux.