To put this simply I am trying to connect to a different database based on the agent machine I am running on. We are doing this to have workflows run on dev/uat/prod environments based simply on the default agent for the workflow. I am trying to use Open SQL Connection to accomplish this. In order to use a different pre-defined connection string for each database I have to change the agent and edit the task through the workflow. When I run the workflow on the 2nd database machine defined I the error message below. This same code works if I set the default agent to ServerTwo. I want to know if one Session SQL Connection can point at different databases in the same task. The code is shown attached. Ignore the upcase stuff, I had to change the server names because they are our internal names.

Error Message:
7/7/2015 7:47:25 AM,Task 'dbTest' running on agent 'ServerOne' failed because: Session DBServerEnv could not be found. (Step 10)
7/7/2015 7:47:25 AM,Workflow 'dbTest' failed.

Category Survey AutoMate BPA Server 9 version: Windows version:

Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
<AMVARIABLE NAME="SQLSERVER">%GetComputerName()%</AMVARIABLE> <AMIF EXPRESSION="%%UCase(%Trim(%SQLSERVER%)%)% = 'ServerOne'%" USECOMPLEXUI="YES"> <!-- <AMRUN FILE="d:/temp/msgbat.bat ProdEnvironment" />--> <AMSQLOPENCONNECTION SESSION="DBServEnv" PREDEFINEDCONNECTION="MssqlServerOne" /> <AMELSE /> <!-- <AMRUN FILE="d:/temp/msgbat.bat DevEnvironment" />--> <AMSQLOPENCONNECTION SESSION="DBServerEnv" PREDEFINEDCONNECTION="mssqlServerTwo" /> </AMIF> <!--<AMRUN FILE="d:/temp/msgbat.bat %SQLSERVER%" />--> <AMSQLSTOREDPROC SESSION="DBServerEnv" PROCEDURE="tomtest..xranon" NAMES="@RETURN_VALUE" VARIABLES="&amp;amp;amp;amp;quot;&amp;amp;amp;amp;quot;" TYPES="Integer" DIRECTIONS="ReturnValue" VALUES="&amp;amp;amp;amp;quot;&amp;amp;amp;amp;quot;" PRECISIONS="10" NUMSCALES="0" SIZES="0" ATTRIBUTES="&amp;amp;amp;amp;quot;&amp;amp;amp;amp;quot;" /> <!--<AMSQLQUERY SESSION="DBServerEnv">select top 10 * from dbone..tablea</AMSQLQUERY>--> <AMSQLQUERY SESSION="DBServerEnv">select top 10 * from staging..line</AMSQLQUERY> <AMSQLCLOSECONNECTION SESSION="DBServerEnv" />

Edited: 07/07/2015 at 08:59 AM by TonGordon

Hello TonGordon,

By using the 'Set Variable' activity in conjunction with your IF logic we should be able to 'toggle' which DB to connect to within one task.

Please try the attach code and let me know if this helps.

Category Survey

Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)
<AMVARIABLE NAME="DBconnection"></AMVARIABLE> <AMVARIABLE NAME="SQLSERVER">%GetComputerName()%</AMVARIABLE> <AMIF EXPRESSION="%%UCase(%Trim(%SQLSERVER%)%)% = 'ServerOne'%" USECOMPLEXUI="YES"> <!-- <AMRUN FILE="d:/temp/msgbat.bat ProdEnvironment" />--> <AMSQLOPENCONNECTION SESSION="DBServEnv" PREDEFINEDCONNECTION="MssqlServerOne" /> <AMSET VARIABLENAME="DBconnection">DBServEnv</AMSET> <AMELSE /> <!-- <AMRUN FILE="d:/temp/msgbat.bat DevEnvironment" />--> <AMSQLOPENCONNECTION SESSION="DBServerEnv" PREDEFINEDCONNECTION="mssqlServerTwo" /> <AMSET VARIABLENAME="DBconnection">DBServerEnv</AMSET> </AMIF> <!--<AMRUN FILE="d:/temp/msgbat.bat %SQLSERVER%" />--> <AMSQLSTOREDPROC SESSION="%DBconnection%" PROCEDURE="tomtest..xranon" NAMES="@RETURN_VALUE" VARIABLES="&amp;amp;amp;quot;&amp;amp;amp;quot;" TYPES="Integer" DIRECTIONS="ReturnValue" VALUES="&amp;amp;amp;quot;&amp;amp;amp;quot;" PRECISIONS="10" NUMSCALES="0" SIZES="0" ATTRIBUTES="&amp;amp;amp;quot;&amp;amp;amp;quot;" /> <!--<AMSQLQUERY SESSION="DBServerEnv">select top 10 * from dbone..tablea</AMSQLQUERY>--> <AMSQLQUERY SESSION="%DBconnection%">select top 10 * from staging..line</AMSQLQUERY> <AMSQLCLOSECONNECTION SESSION="DBServerEnv" />

-------------------------

Valentineo McGowan | Technical Consultant
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1 213.738.6966 | F: +1.213.738.7665

Valentineo,

Sorry, this was my fault. From an earlier version I had a mis-spelling of the SQL Session variable in the first part of the if statement. When I fixed that everything worked. And I didn't need to set DBconnection explicitly once I found that so the BPA code is working the way it should. Thanks for the help.

Category Survey AutoMate BPA Server 9 version: Windows version: