servers in SQL 2005.

The below works perfectly in SQL 2000.
I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODBC.
I get below errors when I do the following:
EXEC sp_addlinkedsrvlogin 'nav4', 'true'
Select name from openquery(nav4, 'select navn from country')
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. The
provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "nav4".
Above also happens no matter what I try, can anybody help ?
(I have also tried to create the linked server with sp_addlinkedserver, this
dont work either)
/Brian
May check the compatible driver is used for NAVSION and check KBA
http://support.microsoft.com/kb/818182 fyi.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"hummel" wrote:

> The below works perfectly in SQL 2000.
> I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODBC.
>
> I get below errors when I do the following:
> EXEC sp_addlinkedsrvlogin 'nav4', 'true'
> Select name from openquery(nav4, 'select navn from country')
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. The
> provider reported an unexpected catastrophic failure.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "nav4".
> Above also happens no matter what I try, can anybody help ?
> (I have also tried to create the linked server with sp_addlinkedserver, this
> dont work either)
> /Brian
>
|||Hi
This update does not work, I have installed SP1 and these files are newer
than the version
that can be downloaded from below. (it also not worked before installing the
sp1)
But thanks for your suggestion
/Brian
Satya SKJ wrote:[vbcol=seagreen]
>May check the compatible driver is used for NAVSION and check KBA
>http://support.microsoft.com/kb/818182 fyi.
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com

servers in SQL 2005.

The below works perfectly in SQL 2000.
I have setup a Linked server to Navision 4.01 with a OLE DB provider for ODB
C.
I get below errors when I do the following:
EXEC sp_addlinkedsrvlogin 'nav4', 'true'
Select name from openquery(nav4, 'select navn from country')
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "nav4" reported an error. Th
e
provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "nav4".
Above also happens no matter what I try, can anybody help ?
(I have also tried to create the linked server with sp_addlinkedserver, this
dont work either)
/BrianMay check the compatible driver is used for NAVSION and check KBA
http://support.microsoft.com/kb/818182 fyi.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"hummel" wrote:

> The below works perfectly in SQL 2000.
> I have setup a Linked server to Navision 4.01 with a OLE DB provider for O
DBC.
>
> I get below errors when I do the following:
> EXEC sp_addlinkedsrvlogin 'nav4', 'true'
> Select name from openquery(nav4, 'select navn from country')
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "MSDASQL" for linked server "nav4" reported an error.
The
> provider reported an unexpected catastrophic failure.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "nav4".
> Above also happens no matter what I try, can anybody help ?
> (I have also tried to create the linked server with sp_addlinkedserver, th
is
> dont work either)
> /Brian
>|||Hi
This update does not work, I have installed SP1 and these files are newer
than the version
that can be downloaded from below. (it also not worked before installing the
sp1)
But thanks for your suggestion
/Brian
Satya SKJ wrote:[vbcol=seagreen]
>May check the compatible driver is used for NAVSION and check KBA
>http://support.microsoft.com/kb/818182 fyi.
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com

server, stored procedure and Views (Heterogeneous Errors)

I have linked to another SQL Instance, created a view and a stored procedure
to access it. This works great through MS Query, but not via SQL Triggers or
through calling through code in another application.
The views and sprocs create without errors, it's only when running them.
Any Ideas?Ray
Can you show us how you call the statemnet?
"Ray" <rayc@.rsc.com> wrote in message
news:FEEE24DA-571B-4C46-887B-33D4F772553E@.microsoft.com...
>I have linked to another SQL Instance, created a view and a stored
>procedure
> to access it. This works great through MS Query, but not via SQL Triggers
> or
> through calling through code in another application.
> The views and sprocs create without errors, it's only when running them.
> Any Ideas?|||you will have to use the four part naming convention
linkedservername.database.owner.object
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ray" wrote:

> I have linked to another SQL Instance, created a view and a stored procedu
re
> to access it. This works great through MS Query, but not via SQL Triggers
or
> through calling through code in another application.
> The views and sprocs create without errors, it's only when running them.
> Any Ideas?

server with IBM DB2 UDB for iSeries IDMDASQL OLE Provider Errors

Hello Everyone

I’m having problems retrieving data from IBM DB2 AS/400 linked server

I have managed to connect a linked server using the IBM DB2 UDB for iSeries "IBMDASQL" OLE DB Provider.

But when I run a query to the database I get only the field names and the following errors:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "iseries" reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "IBMDASQL" for linked server "iseries".

My Server is Standard 64bit edition on cluster with CTP 2 installed .

I don’t have the right to use Microsoft OLE DB provider …..?

Thank you.

Firstly check to correct the Access Denied error and ensure the user you are using to COnnect to IBM DB2 should have access.

Also check this http://www-128.ibm.com/developerworks/forums/dw_thread.jsp?forum=292&thread=143817&cat=5 link on IBM forums.

|||

Hello Satya

Thank you for your time.

I had my DB2 user tested with a non cluster 32bit installation.

I already posted my case to mentioned IBM link.

My problem is that I can’t use a Local system account for my 64bit clustered environment.

After executing a simple select query against the linked server I get an empty result set at the result tab with all table fields included and the errors

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDASQL" for linked server "GRATHD1" reported an error. Access denied.

Msg 7301, Level 16, State 2, Line 1

Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "IBMDASQL" for linked server "GRATHD1".

at the messages tab.

Thank you

|||

I would like to know how you setup the link. I have tried repeatedly to get a link for AS/400 to work, but can't seem to find the proper combination of parameters. Searching the internet has provided no help to me.

Thanks

|||

Hello Lee

I'm posting the script produced by my linked server

Notice that if you set the SQL Server service account to "local system" the linked server works.

/****** Object: LinkedServer [GRATHD1] Script Date: 01/30/2007 11:15:54 ******/
EXEC master.dbo.sp_addlinkedserver @.server = N'GRATHD1', @.srvproduct=N'IBM DB2 UDB for GRATHD1', @.provider=N'IBMDASQL', @.datasrc=N'GRATHD1'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'GRATHD1',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'as400username',@.rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'GRATHD1',@.useself=N'False',@.locallogin=N'DomainName\UserName',@.rmtuser=N'AS400username',@.rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'collation compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'data access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'dist', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'pub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'rpc', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'rpc out', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'sub', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'connect timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'collation name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'lazy schema validation', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'query timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'GRATHD1', @.optname=N'use remote collation', @.optvalue=N'true'

|||

you musto add the options

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

|||

PS to the Microsoft people

When the server is running under the local system account and not a domain user this option is not necessary.