Post reply

OPENQUERY running stored procedure twice

  • April 3, 2012 at 4:31 pm

    #271484

    Hey All,

    I am using openquery to run a MySQL stored procedure that writes to a table in the MySQL db. The issue that is happening is that this store procedure runs twice when called only once.

    It is causing it to fail due to a primary key constraint on the table.

    select * from openquery(mysql_proddb,'call productiondb.GET_ITEM_ALL');

    Any help would be greatly appreciated.

    Thank you.

  • April 4, 2012 at 3:14 am

    #1468688

    I suspect that the duplicate call could be due to metadata discovery.

    Try changing the "Lazy Schema Validation" property to "True" on the linked server properties page.

    -- Gianluca Sartori

  • April 4, 2012 at 10:19 am

    #1469003

    It looks like this solution would work, unfortunately we do not have the enterprise edition of sql server. Is there another way of doing this? We are using the standard edition.

    Thank you.

  • April 4, 2012 at 10:43 am

    #1469016

    danielgostrer (4/3/2012)

    Hey All,

    I am using openquery to run a MySQL stored procedure that writes to a table in the MySQL db. The issue that is happening is that this store procedure runs twice when called only once.

    It is causing it to fail due to a primary key constraint on the table.

    select * from openquery(mysql_proddb,'call productiondb.GET_ITEM_ALL');

    Any help would be greatly appreciated.

    Thank you.

    Shouldn't you just be using this?

    EXEC('CALL productiondb.GET_ITEM_ALL') AT mysql_proddb; Does this result in the same problem?

    Jared
    CE - Microsoft

  • April 4, 2012 at 12:22 pm

    #1469079

    thats great! works like a charm.

  • April 4, 2012 at 12:26 pm

    #1469083

    Glad to help!

    Jared
    CE - Microsoft

  • April 23, 2012 at 5:10 am

    #1477398

    Hi Jared,

    Nice to see the solution here, In may case same issue with MSSQL Linked server, do you have any idea to solve this as well !

    I am calling like:

    SELECT *

    FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=192.168.0.1;UID=user;PWD=pass',

    'Exec DB.dbo.SPA_TEST ''1'' ')

    the Above Query Executed twice, What can i do ??

  • April 23, 2012 at 7:09 am

    #1477467

    raghu_ktm (4/23/2012)

    Hi Jared,

    Nice to see the solution here, In may case same issue with MSSQL Linked server, do you have any idea to solve this as well !

    I am calling like:

    SELECT *

    FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=192.168.0.1;UID=user;PWD=pass',

    'Exec DB.dbo.SPA_TEST ''1'' ')

    the Above Query Executed twice, What can i do ??

    Are you not able to use the example I gave and apply it to your own? It looks like your setup is not to a linked server, but using a direct connection string. Not sure "exactly" on how to adapt this as I have only used this with a linked server.

    Jared
    CE - Microsoft

  • April 23, 2012 at 7:18 am

    #1477472

    raghu_ktm (4/23/2012)

    Hi Jared,

    Nice to see the solution here, In may case same issue with MSSQL Linked server, do you have any idea to solve this as well !

    I am calling like:

    SELECT *

    FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=192.168.0.1;UID=user;PWD=pass',

    'Exec DB.dbo.SPA_TEST ''1'' ')

    the Above Query Executed twice, What can i do ??

    You could try to include SET FMTONLY OFF; before the EXEC, but I'm not sure this will help.

    -- Gianluca Sartori

  • April 24, 2012 at 3:32 am

    #1477988

    I have tried on both way, but Procedure runs Twice in both case.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply