March 13, 2003 at 7:48 pm
Hi,
I use SQL2K run under WIN2K advanced Server for my accounting database. One of my SP has to access tables on other server with SQL 6.5 run under WIN2K Adv too. I use OPENDATASOURCE to access those table in 6.5. An error has occured when i try to execute this statement :
"INSERT INTO OPENDATASOURCE('SQLOLEDB','Data Source=MyDataSource;User ID=MyUserAccount;Password=MyPa$$w0rd;').accounting.dbo.CUSTOMER_DEPOSIT_VOUCHER
(batch_id,voucher_id)
VALUES
('A'
,'XXX'
)
result:
Server: Msg 16902, Level 16, State 1, Line 1
sp_cursor: The parameter 'table' is invalid
But if i try to only select this table the error doesn't occure.
I've been experienced with this error 3 times, Jun 2002, nov 2002 and today mar 2003. I've to reinstall SQL2K again to fix this error.
is there any idea how should i do to prevent this error occure again in next time. and how do i fix the problem without reinstall SQL2K again..?
Thanx in advance.
ali
I edited to remove user specific info in the connection string... - Brian
Edited by - bkelley on 03/13/2003 11:10:12 PM
ali
March 13, 2003 at 10:32 pm
please don't tell me that is your REAL sa password in that post. You might want to remove that, or obscure it.
Tim C.
//Will write code for food
Tim C //Will code for food
March 13, 2003 at 11:12 pm
Thanks, Tim, for the catch on the connection string. I've edited the post accordingly. Just as an FYI to anyone who might post, if you post a question and use some of your code or connection information, please modify the post such that information specific to your environment isn't revealed. You never know who might be reading the posts and decide to use the information against you.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
March 13, 2003 at 11:37 pm
Thanks alot all, i forgot to remove uid and pwd, i just paste from error msg. thanks again.
ali
ali
March 14, 2003 at 12:20 am
Ok, how are you executing this query? and does it ever work? From your post it sounds like it works for a bit, then stops, and you reinstall, repeat process.... Is that what is happening? Also, your connection string is hardcoded to use the ADO 2.0 libararies, do you wish to use a later version? If so change 'SQLOLEDB' to read 'SQLOLEDB.1'. Then the latest MDAC version you have will be used, but 'SQLOLEDB' still points at the 2.0 type lib.
Tim C.
//Will write code for food
Tim C //Will code for food
March 17, 2003 at 2:28 am
MDAC Installed is MDAC 2.6,
here i tried again
INSERT INTO OPENDATASOURCE('SQLOLEDB.1','Data Source=MYDns;User ID=MyUID;Password=MYPwd;').accounting.dbo.CUSTOMER_DEPOSIT_VOUCHER
(batch_id,voucher_id)
VALUES
('A'
,'B10000010101'
)
Result :
Server: Msg 7343, Level 16, State 2, Line 1
OLE DB provider 'SQLOLEDB.1' could not INSERT INTO table '[SQLOLEDB.1]'. Unknown provider error.
[OLE/DB provider returned message: sp_cursor: The parameter 'table' is invalid
]
otherwise ,
select batch_id,voucher_id,gl_account_id
from OPENDATASOURCE('SQLOLEDB.1','Data Source=MyDSN;User ID=MyUID;Password=MyPWD;').accounting.dbo.CUSTOMER_DEPOSIT_VOUCHER
Result:
A960800098 81000000000
A960800166 81000000000
how it could be ..???
quote:
Ok, how are you executing this query? and does it ever work? From your post it sounds like it works for a bit, then stops, and you reinstall, repeat process.... Is that what is happening? Also, your connection string is hardcoded to use the ADO 2.0 libararies, do you wish to use a later version? If so change 'SQLOLEDB' to read 'SQLOLEDB.1'. Then the latest MDAC version you have will be used, but 'SQLOLEDB' still points at the 2.0 type lib.Tim C.
//Will write code for food
ali
March 17, 2003 at 9:07 am
Can you verify that the accounting.dbo.CUSTOMER_DEPOSIT_VOUCHER with that EXACT
namespace exists? Many times I see errors like this occur because the owner is
not the dbo, or some other version of the namespace does not exist. Usually if
everything is created under one owner, I omit the ownership part of the
namespace chain.
IE:
Instead Of :
accounting.dbo.CUSTOMER_DEPOSIT_VOUCHER
I would use :
accounting..CUSTOMER_DEPOSIT_VOUCHER
Of course that would cause issues if there were multiple owners of that same
table name. I hate it when that happens....
Tim C.
//Will write code for food
Edited by - tcartwright on 03/17/2003 09:08:44 AM
Tim C //Will code for food
March 17, 2003 at 7:16 pm
I checked db owner is dbo, as your suggestion i use accounting..customer_deposit_voucher and give the result as
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB.1' reported an error. Access denied.
do you know why it happened, i mean is it possible that some of mdac file was corrupt ..?
quote:
Can you verify that the accounting.dbo.CUSTOMER_DEPOSIT_VOUCHER with that EXACTnamespace exists? Many times I see errors like this occur because the owner is
not the dbo, or some other version of the namespace does not exist. Usually if
everything is created under one owner, I omit the ownership part of the
namespace chain.
IE:
Instead Of :
accounting.dbo.CUSTOMER_DEPOSIT_VOUCHER
I would use :
accounting..CUSTOMER_DEPOSIT_VOUCHER
Of course that would cause issues if there were multiple owners of that same
table name. I hate it when that happens....
Tim C.
//Will write code for food
Edited by - tcartwright on 03/17/2003 09:08:44 AM
ali
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply