bougeac
asked on
Insert using OPENQUERY into a linked server is failing with OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error
Hi, I am trying to insert data via a linked server into a navision database...
I am using sql server 2000 and am using Navisions C/ODBC driver.
I can query the linked server and pull back data no problem...
But when I attempt the following it all goes wrong :
insert into OPENQUERY(NAVISION_DB,'SEL
VALUES ('Fund','Test','SQLServer'
The following is displayed :
Server: Msg 7399, Level 16, State 1, Procedure NAV_DATE_TEST, Line 33
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Any ideas, this is driving me insane!!!
Chris
I am using sql server 2000 and am using Navisions C/ODBC driver.
I can query the linked server and pull back data no problem...
But when I attempt the following it all goes wrong :
insert into OPENQUERY(NAVISION_DB,'SEL
VALUES ('Fund','Test','SQLServer'
The following is displayed :
Server: Msg 7399, Level 16, State 1, Procedure NAV_DATE_TEST, Line 33
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Any ideas, this is driving me insane!!!
Chris
ASKER
Hi, the linked server is pointing to a NAVISION C/Side database.
The target columns appear to be native NAVISION datatypes :
Type = "option"
Code = "code"
Description = "text"
Also another weird thing, after the insert attempt fails, my connection to the linked server from within sqlserver dies.
So when i click on the linked server to expand the tables i get an error message containing
MSDASQL IDBInitialize::Initialize returned 0x80004005
Help!
The target columns appear to be native NAVISION datatypes :
Type = "option"
Code = "code"
Description = "text"
Also another weird thing, after the insert attempt fails, my connection to the linked server from within sqlserver dies.
So when i click on the linked server to expand the tables i get an error message containing
MSDASQL IDBInitialize::Initialize returned 0x80004005
Help!
Try this
insert into OPENQUERY(NAVISION_DB,'SEL
VALUES ('Fund','Test','SQLServer'
insert into OPENQUERY(NAVISION_DB,'SEL
VALUES ('Fund','Test','SQLServer'
Note In the INSERT statement, a where 1=0 predicate is used to avoid retrieving data from the remote server, which can result in slower performance
ASKER
Hi Imran, thanks for the reply.
Unfortunately, apart from the "where clause" my statement is identical to yours and fails accordingly.
cheers,
chris
Unfortunately, apart from the "where clause" my statement is identical to yours and fails accordingly.
cheers,
chris
>>Unfortunately, apart from the "where clause" my statement is identical to yours and fails accordingly.
I know but did you try mine
I know but did you try mine
ASKER
Hi Imran,
yes , i tried your statement and it generates the same error I was getting before.
cheers,
Chris
yes , i tried your statement and it generates the same error I was getting before.
cheers,
Chris
is this statement working
select * from OPENQUERY(NAVISION_DB,'SEL
select * from OPENQUERY(NAVISION_DB,'SEL
ASKER
Hi Imran,
yep, that statement works fine.
This is whats frustrating, I can suck data OUT but not alter ,wondering if its some sort of permissions/security
yep, that statement works fine.
This is whats frustrating, I can suck data OUT but not alter ,wondering if its some sort of permissions/security
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
also, what kind of database is the linked server NAVISION_DB poinint to?