Synonyms problem

  • I've create Linked Server from another server.

    Then, I create a Synonyms that represent the linked server.

    Then, I create a View that access the table through synonyms.

    Then, I create a Stored Procedure with View.

    Now, I Open Delphi and put TADOConnection + TADOStoredProcedure.

    I Always get an error message Cannot create new connection because in manual or distributed transaction mode

    my query is :

    Select * From <View>

    But, when I change the query into :

    Select <Linked Server>.<Database>.<Schema>.<Table>

    Everything working properly.

    What cause this issue, help me please...

    Regards,

    Eko Indriyawan

  • Hi,

    If you select from the synonym does that work?

  • Thanks for your replies,

    FYI, When I execute the stored procedure with SSMS, there are no error message and still working properly.

    But, when I Execute the stored procedure with Delphi through TADOStoredProcedure raised error like I said above.

    Regards,

    Eko Indriyawan

  • Sounds to me like this may be a "double hop" issue, where authentication is not working.

    In short, if you're going from A to B to C (Delphi to SQLSvr1 to Linked Server) authentication works from A to B but no further. To test this, you could try calling the stored proc via SSMS but on a different machine than the one where the linked server is set up. In that way you get another A to B to C scenario.

    If you want more details, try googling for Kerberos.

  • David McKinney (4/9/2010)


    Sounds to me like this may be a "double hop" issue, where authentication is not working.

    In short, if you're going from A to B to C (Delphi to SQLSvr1 to Linked Server) authentication works from A to B but no further. To test this, you could try calling the stored proc via SSMS but on a different machine than the one where the linked server is set up. In that way you get another A to B to C scenario.

    If you want more details, try googling for Kerberos.

    Hi David McKinney,

    Thanks for your advise.

    Now, I've test with different machine but still show error message.

    I've tried to investigate and found the issue that it caused by index.

    When I drop the index(CONSTRAINT PK_T0801_04_103 PRIMARY KEY CLUSTERED (IDNO)), I can execute that stored procedure via delphi.

    Can you explain about this issue...

    For a while, I drop my index in order my application can running.

    regards,

    Eko Indriyawan

  • WAIT A MINUTE! ARE YOU SURE YOU WANT TO DROP YOUR INDEX?

    Not entirely sure what's going on, but it looks like your going to drop (have dropped) the primary key constraint on a table. i.e. your stored procedure is trying to violate the primary key.

    Primary keys are there for a reason, and you shouldn't drop them to get your application running!

  • David McKinney (4/9/2010)


    WAIT A MINUTE! ARE YOU SURE YOU WANT TO DROP YOUR INDEX?

    Not entirely sure what's going on, but it looks like your going to drop (have dropped) the primary key constraint on a table. i.e. your stored procedure is trying to violate the primary key.

    Primary keys are there for a reason, and you shouldn't drop them to get your application running!

    Hi David,

    How about the connection string...? Do you think that it caused by the configuration of connection string?

    My connection string is : ADO?AuxDriver=SQLNCLI10.1;SCHEMAS=1;Server=(LOCAL)\XXXXX;Database=YYYYY;UserID=EkoIndriyawan;Password=C4FD3EF9-5909-4DFB-9F8E-371A73A84DED;Min Pool Size=5;Max Pool Size=100;

    From my Connection String above, is there any something missing?

    King Regards,

    Eko Indriyawan

  • My connection string is : ADO?AuxDriver=SQLNCLI10.1;SCHEMAS=1;Server=(LOCAL)\XXXXX;Database=YYYYY;UserID=EkoIndriyawan;Password=C4FD3EF9-5909-4DFB-9F8E-371A73A84DED;Min Pool Size=5;Max Pool Size=100;

    This is your connection string from Delphi to the first sql server, right?

    And you've also got a second sql server, which is why you have a linked server, right?

    What I can see from your connection string is (LOCAL)\XXXXX which means you've got a named instance (named XXXXX) of a sql server. (SQLEXPRESS?) However, have you tried replacing (LOCAL) with the name of the server? (What's local to one server is not local to another.)

  • replace (local)\XXXXX with what "select @@servername" returns

  • Hi david,

    Sorry, my connectionstring for the name server, instance and database is not like above, I write like that because I don't want another people know it.

    FYI, for (LOCAL) i replace with Name of my Computer Server

    From the connectionstring above, do you see that any configuration is missing?

    How about Integrated Security, Persist Security Info, MARS Connection, etc.

    Are there needed to make delphi could access stored procedure(Have Index table)?

    Regards,

    Eko Indriyawan

  • Hi David,

    Finally I found the solution by create temporary table.

    My first query in stored procedure:

    ...

    SELECT A1.INVOICE_INC AS A,

    A1.TRANSACTION_ID AS B,

    A2.ITEM_ID AS C,

    A2.ITEM_NAME AS D,

    A1.UNIT_ID AS E,

    A1.UNIT_NAME AS F,

    A1.ITEM_QUANTITY AS G,

    A1.ITEM_STOCK_0001 AS H,

    A1.ITEM_STOCK_0002 AS I,

    A1.ITEM_STOCK_0003 AS J,

    CASE

    WHEN ITEM_STOCK_0003 = 1 THEN 'BALANCE'

    ELSE 'NOT BALANCE'

    END AS K,

    A1.ITEM_STOCK_0300 AS AD,

    A1.ITEM_STOCK_0301 AS AE,

    A1.ITEM_STOCK_0302 AS AF,

    A2.ITEM_CATEGORY01 AS AG,

    A2.ITEM_CATEGORY02 AS AH,

    A2.ITEM_STATUS01 AS AI,

    A2.ITEM_STATUS02 AS AJ,

    A2.ITEM_STATUS03 AS AK,

    A2.ITEM_STATUS04 AS AL,

    A2.ITEM_STATUS05 AS AM,

    @JUMLAHBARANG AS POA,

    @JUMLAHTERDISPATCH AS POB

    FROM T0804_01_102 A1

    INNER JOIN T0801_04_103 A2

    ON A1.ITEM_ID = A2.ITEM_ID

    WHERE INVOICE_INC = @INVOICE_INC

    AND

    (

    A2.ITEM_ID LIKE '%' + @SEARCH01 + '%'

    OR A2.ITEM_NAME LIKE '%' + @SEARCH01 + '%'

    )

    ORDER BY A2.ITEM_ID

    ...

    Then, I replace it into become this:

    ...

    select * into #ekoindriyawan from T0801_04_103

    SELECT A1.INVOICE_INC AS A,

    A1.TRANSACTION_ID AS B,

    A2.ITEM_ID AS C,

    A2.ITEM_NAME AS D,

    A1.UNIT_ID AS E,

    A1.UNIT_NAME AS F,

    A1.ITEM_QUANTITY AS G,

    A1.ITEM_STOCK_0001 AS H,

    A1.ITEM_STOCK_0002 AS I,

    A1.ITEM_STOCK_0003 AS J,

    CASE

    WHEN ITEM_STOCK_0003 = 1 THEN 'BALANCE'

    ELSE 'NOT BALANCE'

    END AS K,

    A1.ITEM_STOCK_0300 AS AD,

    A1.ITEM_STOCK_0301 AS AE,

    A1.ITEM_STOCK_0302 AS AF,

    A2.ITEM_CATEGORY01 AS AG,

    A2.ITEM_CATEGORY02 AS AH,

    A2.ITEM_STATUS01 AS AI,

    A2.ITEM_STATUS02 AS AJ,

    A2.ITEM_STATUS03 AS AK,

    A2.ITEM_STATUS04 AS AL,

    A2.ITEM_STATUS05 AS AM,

    @JUMLAHBARANG AS POA,

    @JUMLAHTERDISPATCH AS POB

    FROM T0804_01_102 A1

    INNER JOIN #ekoindriyawan A2

    ON A1.ITEM_ID = A2.ITEM_ID

    WHERE INVOICE_INC = @INVOICE_INC

    AND

    (

    A2.ITEM_ID LIKE '%' + @SEARCH01 + '%'

    OR A2.ITEM_NAME LIKE '%' + @SEARCH01 + '%'

    )

    ORDER BY A2.ITEM_ID

    drop table #ekoindriyawan

    ...

    Thanks for your attention to try solve this issue.

    Now, I do not need drop my index, and my application can running with normal.

    I hope this solution can help to another that facing to the same problem.

    Regards,

    Eko Indriyawan.

  • I hope this solution can help to another that facing to the same problem.

    I very much doubt it...but pleased you (think you) have found a solution. 🙂

  • Hi David,

    I did not lie, that this is a solution and I've applied to all stored procedures and all working properly.

    Let you read this article http://support.microsoft.com/kb/272358

    The Microsoft OLE DB Provider for SQL Server does not allow more than one active connection within the scope of a transaction.

    Because transactions and locking are managed by SQL Server on a per-connection basis, SQLOLEDB does not permit more than one active connection within the scope of a transaction.

    Regards,

    Eko Indriyawan

  • I didn't mean to suggest you were lying..and apologise if that's what you understood.

    What I meant was that the 'solution' you posted has no apparent link to the problem you faced. Perhaps with the additional information in the Knowledge Base article, your solution might make some sense, but in isolation all that was apparent was that you replaced a select with a select into a temporary table. It's not clear what you do with this temporary table afterwards...apparently you just drop it!? If so, why create it?

    Looking at the KB article, I suspect (but could be way out) that the issue is that your stored procedures return multiple recordsets (MARS), in which case your temporary table trick would suppress one of them. But I repeat that I would very much doubt that anyone faced with the error you received would be enlightened by the script you posted.

    However I didn't mean to be disrespectful, and can understand that it came across like that. It was good of you to post your solution, and uncharitable of me to respond as I did.

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

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