April 9, 2010 at 1:26 am
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
April 9, 2010 at 1:34 am
Hi,
If you select from the synonym does that work?
April 9, 2010 at 2:06 am
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
April 9, 2010 at 2:55 am
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.
April 9, 2010 at 3:49 am
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
April 9, 2010 at 3:52 am
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!
April 9, 2010 at 4:11 am
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
April 9, 2010 at 4:20 am
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.)
April 9, 2010 at 4:22 am
replace (local)\XXXXX with what "select @@servername" returns
April 9, 2010 at 4:44 am
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
April 9, 2010 at 5:33 am
Hi David,
Finally I found the solution by create temporary table.
My first query in stored procedure:
...
SELECTA1.INVOICE_INCAS A,
A1.TRANSACTION_IDAS B,
A2.ITEM_IDAS C,
A2.ITEM_NAMEAS D,
A1.UNIT_IDAS E,
A1.UNIT_NAMEAS F,
A1.ITEM_QUANTITYAS G,
A1.ITEM_STOCK_0001AS H,
A1.ITEM_STOCK_0002AS I,
A1.ITEM_STOCK_0003AS J,
CASE
WHEN ITEM_STOCK_0003 = 1 THEN 'BALANCE'
ELSE 'NOT BALANCE'
ENDAS K,
A1.ITEM_STOCK_0300AS AD,
A1.ITEM_STOCK_0301AS AE,
A1.ITEM_STOCK_0302AS AF,
A2.ITEM_CATEGORY01AS AG,
A2.ITEM_CATEGORY02AS AH,
A2.ITEM_STATUS01AS AI,
A2.ITEM_STATUS02AS AJ,
A2.ITEM_STATUS03AS AK,
A2.ITEM_STATUS04AS AL,
A2.ITEM_STATUS05AS AM,
@JUMLAHBARANGASPOA,
@JUMLAHTERDISPATCHASPOB
FROMT0804_01_102 A1
INNER JOINT0801_04_103 A2
ONA1.ITEM_ID=A2.ITEM_ID
WHEREINVOICE_INC=@INVOICE_INC
AND
(
A2.ITEM_IDLIKE'%'+@SEARCH01+'%'
ORA2.ITEM_NAMELIKE'%'+@SEARCH01+'%'
)
ORDER BYA2.ITEM_ID
...
Then, I replace it into become this:
...
select * into #ekoindriyawan from T0801_04_103
SELECTA1.INVOICE_INCAS A,
A1.TRANSACTION_IDAS B,
A2.ITEM_IDAS C,
A2.ITEM_NAMEAS D,
A1.UNIT_IDAS E,
A1.UNIT_NAMEAS F,
A1.ITEM_QUANTITYAS G,
A1.ITEM_STOCK_0001AS H,
A1.ITEM_STOCK_0002AS I,
A1.ITEM_STOCK_0003AS J,
CASE
WHEN ITEM_STOCK_0003 = 1 THEN 'BALANCE'
ELSE 'NOT BALANCE'
ENDAS K,
A1.ITEM_STOCK_0300AS AD,
A1.ITEM_STOCK_0301AS AE,
A1.ITEM_STOCK_0302AS AF,
A2.ITEM_CATEGORY01AS AG,
A2.ITEM_CATEGORY02AS AH,
A2.ITEM_STATUS01AS AI,
A2.ITEM_STATUS02AS AJ,
A2.ITEM_STATUS03AS AK,
A2.ITEM_STATUS04AS AL,
A2.ITEM_STATUS05AS AM,
@JUMLAHBARANGASPOA,
@JUMLAHTERDISPATCHASPOB
FROMT0804_01_102 A1
INNER JOIN#ekoindriyawan A2
ONA1.ITEM_ID=A2.ITEM_ID
WHEREINVOICE_INC=@INVOICE_INC
AND
(
A2.ITEM_IDLIKE'%'+@SEARCH01+'%'
ORA2.ITEM_NAMELIKE'%'+@SEARCH01+'%'
)
ORDER BYA2.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.
April 9, 2010 at 5:42 am
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. 🙂
April 9, 2010 at 7:49 am
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
April 9, 2010 at 8:09 am
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 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply