February 19, 2012 at 6:41 am
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!
February 19, 2012 at 7:36 am
Have you tried with a SQL OLEDB connection?
February 19, 2012 at 9:28 am
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
February 19, 2012 at 1:37 pm
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?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply