July 29, 2011 at 10:50 am
Koen Verbeeck (7/29/2011)
Oh for f*ck's sake! (excuse my language)I have found the issue.
It's a combination of two things:
First of all, the driver needs to be executed out of process. So thanks Gianluca for pointing out that option and for your excellent article.
If the linked server uses the OLE DB provider in process, it doesn't work.
Secondly, Gianluca was right:
...probably the connection string is malformed.
If you look at the script, you'll see it is nicely formatted. I do this all the time, making the code more readable. I can go totally OCD on that, it's just a habit of me. SQL Server ignores most of the white space anyway. Apparently, the white space in the connection string isn't ignored, resulting in a malformed connection string :crazy:
I came up with the idea to try a connection string on one single line, after reading the last article that Ignacio provided. So Ignacio, thanks a bunch for that link.
*bangs head against the wall repeatedly*
Wasted a couple of hours, but definately learned a lot about linked server, so not a total waste of time after all.
Thanks again!
(I can finally sleep again at night :-D)
Congratulations!
Finding your way through a mess like that is a pain while it's happening, but next time it will seem easy.
Tom
December 28, 2011 at 12:01 pm
Koen:
I'm in the process setting up a new SQL 2008 R2 server and accessing our AS400 as we've done on SQL 2005. I read through this thread and the linked articles but am still having an issue with the Catastrophic Failure.
I've installed ISeries Access, Created an ODBC Entry like we have always done on the other servers and copied the linked Server over from a SQL2005 server.
I've checked to make sure that MSDASQL on the new SQL 2008R2 Server is set to "Allow Process" is enabled.
The linked Server command is below.. Could you provide your steps/linked Server commands so I could compare? I thought I had everything right but it may be something else.
/****** Object: LinkedServer [AS400] Script Date: 12/28/2011 11:22:40 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'AS400', @srvproduct=N'OLEDB', @provider=N'MSDASQL', @datasrc=N'AS400'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AS400',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='password'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'collation name', @optvalue=N'Latin1_General_CI_AS'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'use remote collation', @optvalue=N'true'
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply