help with syntax of sqlquery activity

Permanently deleted user

December 20, 2012 02:23

I am getting an error in my sqlquery activity, i think its my syntax.  I am querying a sqlserver database.

Here is my query:

SELECT distinct State, FILE_NAME, doc_type, township, range

 

FROM [records].[dbo].[OR957]

 

WHERE township = @Argument1

 

AND range = @Argument2

 

I am passing 2 parameters from the parameters form:

Argument1, In, string, townshipVar

 

Argument2, In, string, rangeVar

 

previously assigned in the workflow above the activity

townshipVar = "170n"

rangeVar = "030w"

 

I am getting this error:

Error: Aborted exception: 'One or more errors occurred.'.

 One or more errors occurred.

 

 Must declare the scalar variable "@Argument1".

 

If I hardwire in the township and range values and put tics around them, the query works fine, as shown below:

SELECT distinct State, FILE_NAME, doc_type, township, range

 

FROM [records].[dbo].[OR957]

 

WHERE township = '170n'

 

AND range = '030w'

Any suggestions?

 

Jeff

 

 

 

 

 

0

• 

  Permanently deleted user

  December 20, 2012 03:40

  Hi Jeff

  you need to call your parameters @ Argument1 and @ Argument2

  Thanks

  Ralph

  0
• 

  Permanently deleted user

  December 20, 2012 04:23

  Hi Jeff

  can you try the following in SQLServer Management Studio?

   

  declare @Argument1 nvarchar(20)

   

  declare @Argument2 nvarchar(20)

  set @Argument1 = '170n'

   

  set @Argument2 = '030w'

  SELECT distinct State, FILE_NAME, doc_type, township, [range]

   

  FROM [records].[dbo].[OR957]

   

  WHERE township = @Argument1

   

  AND [range] = @Argument2

   

  What result do you get when you run that?

  Thanks

  Ralph

  PS you need to set your timezone in your profile. I am quite sure that you are not -12 GMT ;-)

  0
• 

  Permanently deleted user

  December 20, 2012 05:24

  Ralph--

  This works below:

  declare @Argument1 nvarchar(20)

   

  declare @Argument2 nvarchar(20)

  set @Argument1 = '170n'

   

  set @Argument2 = '030w'

   

  SELECT distinct State, FILE_NAME, doc_type, township, [range]

   

  FROM [records].[dbo].[OR957]

   

  WHERE [township] = @Argument1

   

  AND [range] = @Argument2

   

   

  but I do not know how I will pass the township and range arguments to the set @Argument1 and set@argument2?

  any advice there?

  J

  0
• 

  Permanently deleted user

  December 20, 2012 05:55

  Hi Jeff

  can you please try running the following workflow?

  (Stripped down, need to reset the username and password for the db connection. Note also changed the connectionstring type to SQLClient

  Thanks

  Ralph

  <Activity mc:Ignorable="sap sads" x:Class="{x:Null}"

   

  xmlns="http://schemas.microsoft.com/netfx/2009/xaml/activities"

   

  xmlns:eac="clr-namespace:ESRI.ArcGIS.Client;assembly=ESRI.ArcGIS.Client"

   

  xmlns:eacg="clr-namespace:ESRI.ArcGIS.Client.Geometry;assembly=ESRI.ArcGIS.Client"

   

  xmlns:eact="clr-namespace:ESRI.ArcGIS.Client.Tasks;assembly=ESRI.ArcGIS.Client"

   

  xmlns:esri="http://schemas.esri.com/arcgis/client/2009"

   

  xmlns:gce="clr-namespace:Geocortex.Core.Extensions;assembly=Geocortex.Core"

   

  xmlns:gcx="http://apis.geocortex.com/2009"

   

  xmlns:gfc="clr-namespace:Geocortex.Forms.Client;assembly=Geocortex.EssentialsWpfApi"

   

  xmlns:gfci="clr-namespace:Geocortex.Forms.Client.Items;assembly=Geocortex.EssentialsWpfApi"

   

  xmlns:gr="clr-namespace:Geocortex.Reporting;assembly=Geocortex.Reporting"

   

  xmlns:gwa="clr-namespace:Geocortex.Workflow.Activities;assembly=Geocortex.Workflow"

   

  xmlns:gwa1="clr-namespace:Geocortex.Workflow.Activities;assembly=Geocortex.Workflow.Activities"

   

  xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

   

  xmlns:mva="clr-namespace:Microsoft.VisualBasic.Activities;assembly=System.Activities"

   

  xmlns:s="clr-namespace:System;assembly=System.Core"

   

  xmlns:s1="clr-namespace:System;assembly=System"

   

  xmlns:s2="clr-namespace:System;assembly=mscorlib"

   

  xmlns:s3="clr-namespace:System;assembly=System.ServiceModel"

   

  xmlns:s4="clr-namespace:System;assembly=System.Drawing.Design"

   

  xmlns:s5="clr-namespace:System;assembly=System.Design"

   

  xmlns:s6="clr-namespace:System;assembly=System.Web.Services"

   

  xmlns:sa="clr-namespace:System.Activities;assembly=System.Activities"

   

  xmlns:sads="http://schemas.microsoft.com/netfx/2010/xaml/activities/debugger"

   

  xmlns:sap="http://schemas.microsoft.com/netfx/2009/xaml/activities/presentation"

   

  xmlns:scg="clr-namespace:System.Collections.Generic;assembly=mscorlib"

   

  xmlns:scg1="clr-namespace:System.Collections.Generic;assembly=System"

   

  xmlns:scg2="clr-namespace:System.Collections.Generic;assembly=System.Core"

   

  xmlns:scg3="clr-namespace:System.Collections.Generic;assembly=System.ServiceModel"

   

  xmlns:sd="clr-namespace:System.Data;assembly=System.Data"

   

  xmlns:sl="clr-namespace:System.Linq;assembly=System.Core"

   

  xmlns:sxs="clr-namespace:System.Xml.Serialization;assembly=System.Xml"

   

  xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">

   

    <x:Members>

   

      <x:Property Name="Argument1" Type="InArgument(x:String)" />

   

      <x:Property Name="Argument2" Type="InArgument(x:String)" />

   

    </x:Members>

   

    <sap:VirtualizedContainerService.HintSize>335,774</sap:VirtualizedContainerService.HintSize>

   

    <mva:VisualBasic.Settings>Assembly references and imported namespaces serialized as XML namespaces</mva:VisualBasic.Settings>

   

    <Sequence sap:VirtualizedContainerService.HintSize="295,694" mva:VisualBasic.Settings="Assembly references and imported namespaces serialized as XML namespaces">

   

      <Sequence.Variables>

   

        <Variable x:TypeArguments="sd:DataTable" Default="[new System.Data.DataTable]" Name="dataTable1" />

   

        <Variable x:TypeArguments="scg:IList(gcx:DataItem)" Name="iList1" />

   

        <Variable x:TypeArguments="s2:Byte[]" Name="byteArray1" />

   

        <Variable x:TypeArguments="esri:FeatureSet" Name="featureSet1" />

   

        <Variable x:TypeArguments="s2:Byte[]" Name="byteArray2" />

   

        <Variable x:TypeArguments="x:Int32" Name="string1" />

   

        <Variable x:TypeArguments="x:String" Name="csv_table" />

   

        <Variable x:TypeArguments="sd:DataTable" Name="iList2" />

   

        <Variable x:TypeArguments="x:String" Name="selection" />

   

        <Variable x:TypeArguments="sd:DataSet" Default="[new System.Data.DataSet]" Name="dataSet1" />

   

        <Variable x:TypeArguments="x:Int32" Name="i" />

   

        <Variable x:TypeArguments="x:Int32" Name="j" />

   

        <Variable x:TypeArguments="x:String" Name="townshipVar" />

   

        <Variable x:TypeArguments="x:String" Name="rangeVar" />

   

        <Variable x:TypeArguments="x:String" Name="myquery" />

   

      </Sequence.Variables>

   

      <sap:WorkflowViewStateService.ViewState>

   

        <scg:Dictionary x:TypeArguments="x:String, x:Object">

   

          <x:Boolean x:Key="IsExpanded">True</x:Boolean>

   

        </scg:Dictionary>

   

      </sap:WorkflowViewStateService.ViewState>

   

      <Assign sap:VirtualizedContainerService.HintSize="273,59">

   

        <Assign.To>

   

          <OutArgument x:TypeArguments="x:String">[townshipVar]</OutArgument>

   

        </Assign.To>

   

        <Assign.Value>

   

          <InArgument x:TypeArguments="x:String">170n</InArgument>

   

        </Assign.Value>

   

      </Assign>

   

      <Assign sap:VirtualizedContainerService.HintSize="273,59">

   

        <Assign.To>

   

          <OutArgument x:TypeArguments="x:String">[rangeVar]</OutArgument>

   

        </Assign.To>

   

        <Assign.Value>

   

          <InArgument x:TypeArguments="x:String">030w</InArgument>

   

        </Assign.Value>

   

      </Assign>

   

      <gwa1:SqlQuery CommandText="SELECT distinct State, FILE_NAME, doc_type, township, [range] FROM [OR957] WHERE township = @Argument1 AND [range] = @Argument2" ConnectionString="Data Source=ilmorso3db1vm.blm.doi.net;Initial Catalog=records;Integrated Security=False;User ID=xxxxxx;Password=xxxxx" sap:VirtualizedContainerService.HintSize="273,128" ProviderName="System.Data.SqlClient" Results="[dataTable1]">

   

        <gwa1:SqlQuery.Parameters>

   

          <InArgument x:TypeArguments="x:String" x:Key="@Argument1">[townshipVar]</InArgument>

   

          <InArgument x:TypeArguments="x:String" x:Key="@Argument2">[rangeVar]</InArgument>

   

        </gwa1:SqlQuery.Parameters>

   

      </gwa1:SqlQuery>

   

      <gwa1:AddToDataSet DataSet="[dataSet1]" DataTable="[dataTable1]" sap:VirtualizedContainerService.HintSize="273,82" />

   

      <gwa1:Alert sap:VirtualizedContainerService.HintSize="273,82" Text="[dataTable1.Rows.Count.ToString]" Title="Row Count" />

   

    </Sequence>

   

  </Activity>

  0
• 

  Permanently deleted user

  December 20, 2012 05:57

  Hi Jeff

  just managed to post after seeing that you had succeeded!

  What are you going to use system.drawing.image and imageconvert libraries for?

  Regards

  Ralph

  0
• 

  Permanently deleted user

  December 20, 2012 06:14

  I tried a different connection type and connection string and it all works as expected.  I guess I had the wrong connection driver so I had a lot of problems.  Check these out.  once i made these changes the workflow ran even better than before!

  Set variables somewhere above the sqlquery  in the workflow:

  townshipVar = "017n"

  rangeVar = "030w"

   

   

  Connection string:

  Data Source=ilmorso3db1vm.blm.doi.net;Initial Catalog=records;Integrated Security=False;User ID=xxxx;Password=xxxx

  Connection type:

  System.Data.SqlClient

  Command:

  SELECT distinct State, FILE_NAME, doc_type, township, range

   

  FROM [records].[dbo].[OR957]

   

  WHERE [township] = @Argument1

   

  AND [range] = @Argument2

   

  Parameters:

  @Argument1, in, string, townshipVar

  @Argument2,in,string,rangeVar

  Thank you for your patience!!

  Jeff

   

  0
• 

  Permanently deleted user

  December 20, 2012 06:36

  Hi Jeff

  pleased to hear it has improved.

  Regards

  Ralph

  0

Please sign in to leave a comment.