Anyone?
On Tuesday, 28 June 2016 07:46:02 UTC+2, Neil Hayes wrote:
>
> 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
>
>
>
--
You received this message because you are subscribed to the Google Groups
"Jenkins Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jenkinsci-users/5913b865-03b0-4eb4-929a-6fb0aa20d46a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
