none
Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible f

    Question

  • Hi,
              I'm having an SSIS package which gives the following error when executed :

    Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
    Task failed: Create Linked Server

    The package has a single Execute SQL task with the properties listed below :

    General Properties
    Result Set    : None
    ConnectionType     : OLEDB
    Connection    : Connected to a Local Database (DB1)
    SQLSourceType    : Direct Input
    SQL Statement    : exec(?)
    IsQueryStorePro    : False
    BypassPrepare    : False

    Parameter Mapping Properties
    variableName          Direction    DataType    ParameterName
    User::AddLinkSql   Input        Varchar        0


    'AddLinkSql' is a global variable of package scope of type string with the value
    Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'

    When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"

    I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
    it gives the same parse error message.

    I would appreciate if anybody can help me out of this issue by suggeting where the problem is.

    Thanks in Advance.

    Wednesday, January 23, 2008 10:38 AM

Answers

  • I'm not sure why it's failing. I tried similar statements in Katmai and SP2, and they seem to work correctly.

     

    Are you using SP2? You didn't mention a ParameterSize column when you were listing the info about the input parameter, so it looks like you're using an older version of 2005. (Note, if you install SSIS after you've upgraded SQL Server to SP2, you need to re-apply SP2 to update SSIS)

     

    A number of fixes went into the Exec SQL Task in SP2, particularly related to parameter binding. One of them might have resolved this issue.

     

    Is using a Variable as a source a problem for the package?

     

    ~Matt

    Thursday, January 24, 2008 5:41 PM

All replies

  • My guess...change the @DataSrc to your domain name instead of localhost.

     

    Adam

    Wednesday, January 23, 2008 10:51 AM
  • Adam,

              I tried with setting 'servername\instancename' for @DataSrc as suggested in BOL and that was of no use.

    Wednesday, January 23, 2008 1:07 PM
  •  

    Why do you have a second "exec" listed within your variable SQL statement?

     

    Looks to me like the entire SQL statement is going to get populated such that: exec(exec sp_......)

    Wednesday, January 23, 2008 3:14 PM
    Moderator
  • Hi Ramesh,

     

    Try setting BypassPrepare to true. The task will fail to parse any query that has parameter markers in it.

     

    Another way to do it is to not use parameters at all. You should be able to change the SQLSourceType to "Variable", and SourceVariable to User::AddLinkSql.

     

    Hope that helps!

    ~Matt

    Wednesday, January 23, 2008 7:14 PM
  • Phil, I have tried without the second "exec" inside the variable, still it would not run. This is the value we had for the variable in DTS, and it used to work fine. By the way, in DTS it works even without the second "exec". What value would you suggest for the variable in SSIS ?

     

    Thursday, January 24, 2008 5:25 AM
  • Matt ,

    setting the BypassPrepare to true does not make any difference and task fails to execute.

     

    Yes, by changing the SQLSourceType to "Variable" and using the global variable would resolve the issue, as I have already found out, but still I would like to know the exact reason for the task failing with the use of input parameter, as it was already running in DTS.

     

    Thanks!

    Thursday, January 24, 2008 5:31 AM
  • Is there any ActiveX running in the DTS package that SSIS doesn't like?

     

    SSIS no longer supports ActiveX.

     

    Adam

    Thursday, January 24, 2008 10:57 AM
  • There is no ActiveX running in this package. For the sake of simplicity, I had taken the production package and removed all the other tasks, keeping just one Execute SQL Task to see what caused the problem.

    Ramesh
    Thursday, January 24, 2008 11:28 AM
  • I'm not sure why it's failing. I tried similar statements in Katmai and SP2, and they seem to work correctly.

     

    Are you using SP2? You didn't mention a ParameterSize column when you were listing the info about the input parameter, so it looks like you're using an older version of 2005. (Note, if you install SSIS after you've upgraded SQL Server to SP2, you need to re-apply SP2 to update SSIS)

     

    A number of fixes went into the Exec SQL Task in SP2, particularly related to parameter binding. One of them might have resolved this issue.

     

    Is using a Variable as a source a problem for the package?

     

    ~Matt

    Thursday, January 24, 2008 5:41 PM
  • Matt,
            Your guess on Service Pack version was spot on!. We noticed the missing column "ParameterSize", when we
            tried to reproduce the issue on my colleague's machine yesterday, and was wondering
            if it was because of driver dll's issue.  After reading your comments, we checked the
            Service packs and my instance was on SP1 while his was on SP2. So updating to
            SP2 resolved this issue. 
           
            We were aware of the method of using Varible input for SQL Statement and were sure that would work
            for us, but just were curious as to why the package fails with input parameters.      
           
            Thanks for giving us the hint about Service pack!
    Friday, January 25, 2008 7:52 AM
  • I have this error:

    [Execute SQL Task] Error: Executing the query "" failed with the following error: "Attempted to read or write protected memory. This is often an indication that other memory is corrupt.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    when I try to execute my dts in windows 2000 server, my dts was made in SSIS 2005 and It was working fine until yerterday when windows have and erro in a system file, we recover the file and we have to reinstall de SQL SERVER 2005, until that the dts dont works and that error throw.

    Please help me!

    Friday, March 16, 2012 10:11 PM