Unable to Execute SQL Task with parameterized stored procedure -- Error 0xC002F210

• caustrom

  Old Hand

  Points: 385

  More actions

  February 19, 2012 at 6:41 am

  #250944

  I am unable to execute a "Execute SQL Task" with a parameterized stored procedure ... get error: 0xC002F210 at Retrieve Tool Usage, Execute SQL Task: Executing the query "EXECUTE golder.sp_ArcGISTool_AggregrateBy_DateSite..." failed with the following error: "Failed to create an IDataAdapter object. This provider may not be fully supported with the Execute SQL Task. Error message 'Could not find stored procedure 'EXECUTE golder.sp_ArcGISTool_AggregrateBy_DateSiteAndUser

  '.'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  Task failed: Retrieve Tool Usage

  Task Definition:

  Connection Type: ADO.NET

  SQL Source Type: Direct Input

  SQL Statement: EXECUTE golder.sp_ArcGISTool_AggregrateBy_DateSiteAndUser

  IsQueryStoredProcedure: True

  BypassPrepare: True

  Parameter Mappings:

  Variable Name: User::v_ServerName

  Direction: Input

  Data Type: String

  Parameter Name: @p_ServerName

  Variable Name: User::v_Error

  Direction: Output

  Data Type: String

  Parameter Name: @p_Error

  Result Sets:

  Result Name: 0

  Variable Name: User::v_ResultSet

  Variables:

  Variable: v_ResultSet

  Data Type: Object

  Value: System.Object

  Variable: v_Error

  Data Type: String

  Value:

  Variable: v_ServerName

  Data Type: String

  Value: CAL1-S-AGSPROD2

  Any assistance would be appreciated!

• Phil Parkin

  SSC Guru

  Points: 247154

  More actions

  February 19, 2012 at 7:36 am

  #1448984

  Have you tried with a SQL OLEDB connection?

  ---

• caustrom

  Old Hand

  Points: 385

  More actions

  February 19, 2012 at 9:28 am

  #1448993

  Yes ... The stored procedure executes dynamic SQL with SQL injection! It has 14 optional input parameters & 1 output parameter.

  CREATE PROCEDURE [golder].[sp_ArcGISTool_AggregrateBy_DateSiteAndUser]

  (

  @p_ServerName VARCHAR(128) = 'CAL1-S-AGSPROD2',

  @p_ClientName VARCHAR(128) = '',

  @p_SiteLogicalName VARCHAR(128) = '',

  @p_SiteCategory VARCHAR(128) = '',

  @p_SiteType VARCHAR(128) = '',

  @p_SiteIsActive INT = 0,

  @p_UserName VARCHAR(128) = '',

  @p_UserGroupName VARCHAR(128) = '',

  @p_UserIsActive INT = 0,

  @p_InitialExtent INT = 0,

  @p_DateTime DATETIME = NULL,

  @p_DateInterval VARCHAR(128) = 'WEEK',

  @p_ExcludeOrientisAdministrators INT = 0,

  @p_SortOrder VARCHAR(2048) = 'DEFAULT',

  @p_Error VARCHAR(MAX) OUTPUT

  )

  AS

  BEGIN

  ....

  END

• caustrom

  Old Hand

  Points: 385

  More actions

  February 19, 2012 at 1:37 pm

  #1449013

  Modifications:

  1. Create a "new" ADO.NET connection

  2. Removed the "EXECUTE" from SQL Statement: golder.sp_ArcGISTool_AggregrateBy_DateSiteAndUser

  3. Assigned Parameter Size: 128 to Parameter Name: @p_ServerName

  4. Assigned Parameter Size: -1 to output Parameter Name: @p_Error

  Error: 0xC002F210 at Retrieve Tool Usage, Execute SQL Task: Executing the query "golder.sp_ArcGISTool_AggregrateBy_DateSiteAndUser" failed with the following error: "The type of the value being assigned to variable "User::v_Error" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

  ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  Question:

  1. How to assign VARCHAR(MAX) to String?