Linked Server to DB2
-
Koen, could you please add the script for creating the linked server with ODBC. Thanks in advance!
-
Ignacio A. Salom Rangel (7/28/2011)
Koen, could you please add the script for creating the linked server with ODBC. Thanks in advance!Here you go:
/****** Object: LinkedServer [LINKED_ODBC_VILLANEXT] Script Date: 07/28/2011 15:03:58 ******/
EXEC master.dbo.sp_addlinkedserver
@server = N'LINKED_ODBC_TEST'
,@srvproduct=N'iSeries'
,@provider=N'MSDASQL'
,@datasrc=N'mySource'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'LINKED_ODBC_TEST'
,@useself=N'False'
,@locallogin=NULL
,@rmtuser=N'myUser'
,@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
mySource is a System DSN created for the ODBC. Using this in an .udl file also works. This Linked Server can create a successful connection, but somehow doesn't show only a small portion of the tables (system tables? I don't know)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Gianluca Sartori (7/28/2011)
I have found that SSIS ignores the settings in the UDL file and requires entering ALL the connection parameters in the connection properties.SQL Server has always been more forgiving and doesn't require the full connection string. However, if you can connect testing the UDL, you can edit the file with notepad and copy/paste the contents in the connection string field of the linked server.
I have used the connection string from the .UDL file in the linked server definition. It is exactly the same.
This is a bit driving me nuts. I'll go back developing SSIS for a while 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
I feel your pain...
(NetMon + ProcMon + EventViewer + Policy Editor + Any log files you can find) is the only tools you have.
Good luck!
-- Gianluca Sartori
-
Koen Verbeeck (7/28/2011)
Ignacio A. Salom Rangel (7/28/2011)
Koen, could you please add the script for creating the linked server with ODBC. Thanks in advance!Here you go:
/****** Object: LinkedServer [LINKED_ODBC_VILLANEXT] Script Date: 07/28/2011 15:03:58 ******/
EXEC master.dbo.sp_addlinkedserver
@server = N'LINKED_ODBC_TEST'
,@srvproduct=N'iSeries'
,@provider=N'MSDASQL'
,@datasrc=N'mySource'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'LINKED_ODBC_TEST'
,@useself=N'False'
,@locallogin=NULL
,@rmtuser=N'myUser'
,@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_ODBC_VILLANEXT', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
mySource is a System DSN created for the ODBC. Using this in an .udl file also works. This Linked Server can create a successful connection, but somehow doesn't show only a small portion of the tables (system tables? I don't know)
Try choosing as provider "Microsoft OLE DB Provider for ODBC Drivers"
Also try setting the "collation compatible" value to 'false'. Check why here.
-
Koen Verbeeck (7/28/2011)
Gianluca Sartori (7/28/2011)
I have found that SSIS ignores the settings in the UDL file and requires entering ALL the connection parameters in the connection properties.SQL Server has always been more forgiving and doesn't require the full connection string. However, if you can connect testing the UDL, you can edit the file with notepad and copy/paste the contents in the connection string field of the linked server.
I have used the connection string from the .UDL file in the linked server definition. It is exactly the same.
This is a bit driving me nuts. I'll go back developing SSIS for a while 🙂
Maybe a silly idea, but can it have anything to do your 32-bit/64-bit SQL Server/Windows environment. I lately got a new laptop and moved from Windows XP (32-bit)/SQL Server 2005(32-bit) to Windows 7(64-bit)/SQL Server 2005(64-bit). I experienced all kind of trouble accessing external resources, but in my case mostly with SSIS. I figured out there are two ODBC Data Source Administrators (%windir%\system32\odbcad32.exe (64-bit) and %windir%\SysWOW64\odbcad32.exe (32-bit). Also there are 32-bit en 64-bit versions of dtexec.exe. However, dtexecUI.exe comes only in a 32-bit version. Furthermore, BIDS contains an option Run64BitRuntime to run a package in either 32- or 64-bit mode. How are you running your SSIS packages if they are succeeding?
Maybe just something to look at?
Peter
-
I feel your pain. It has been several years since I've dealt with this because I was fortunate enough to get out of the shop that had SQL Server & DB2400 to SQL only shops.
If I remember correctly the issue(s) I had were around libraries and making sure that the ODBC/OLE DB connection included all the libraries needed and that the AS400 user had access to all the libraries.
I had to look up the IBM terminolgy to come up with Libraries.
I did find that OLE DB was faster than ODBC.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Ignacio A. Salom Rangel (7/28/2011)
Try choosing as provider "Microsoft OLE DB Provider for ODBC Drivers"Also try setting the "collation compatible" value to 'false'. Check why here.
Both suggestions didn't work, unfortunately.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Peter Brinkhaus (7/28/2011)
Maybe a silly idea, but can it have anything to do your 32-bit/64-bit SQL Server/Windows environment. I lately got a new laptop and moved from Windows XP (32-bit)/SQL Server 2005(32-bit) to Windows 7(64-bit)/SQL Server 2005(64-bit). I experienced all kind of trouble accessing external resources, but in my case mostly with SSIS. I figured out there are two ODBC Data Source Administrators (%windir%\system32\odbcad32.exe (64-bit) and %windir%\SysWOW64\odbcad32.exe (32-bit). Also there are 32-bit en 64-bit versions of dtexec.exe. However, dtexecUI.exe comes only in a 32-bit version. Furthermore, BIDS contains an option Run64BitRuntime to run a package in either 32- or 64-bit mode. How are you running your SSIS packages if they are succeeding?Maybe just something to look at?
I ran the SSIS package in 32-bit and 64-bit mode, and both succeeded. It's just SQL Server itself that is struggling with something. (apparently a TCPIP error, which I don't get in SSIS or in the .udl file)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (7/28/2011)
Ignacio A. Salom Rangel (7/28/2011)
Try choosing as provider "Microsoft OLE DB Provider for ODBC Drivers"Also try setting the "collation compatible" value to 'false'. Check why here.
Both suggestions didn't work, unfortunately.
Did you get any errors when trying them? If you did please attach the errors. If you are able to browse some of the tables, I would have expected that changing the "collation compatible" value to 'false' would have solve the problem.
-
Ignacio A. Salom Rangel (7/29/2011)
Koen Verbeeck (7/28/2011)
Ignacio A. Salom Rangel (7/28/2011)
Try choosing as provider "Microsoft OLE DB Provider for ODBC Drivers"Also try setting the "collation compatible" value to 'false'. Check why here.
Both suggestions didn't work, unfortunately.
Did you get any errors when trying them? If you did please attach the errors. If you are able to browse some of the tables, I would have expected that changing the "collation compatible" value to 'false' would have solve the problem.
For the first suggestion I got the error that the "Microsoft OLE DB Provider for ODBC Drivers" was not found. The name MSDASQL should be used.
The second suggestion didn't result in any other behaviour, i.e. catastrophic failure (what a sense for drama that provider has :-)).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (7/29/2011)
Ignacio A. Salom Rangel (7/29/2011)
Koen Verbeeck (7/28/2011)
Ignacio A. Salom Rangel (7/28/2011)
Try choosing as provider "Microsoft OLE DB Provider for ODBC Drivers"Also try setting the "collation compatible" value to 'false'. Check why here.
Both suggestions didn't work, unfortunately.
Did you get any errors when trying them? If you did please attach the errors. If you are able to browse some of the tables, I would have expected that changing the "collation compatible" value to 'false' would have solve the problem.
For the first suggestion I got the error that the "Microsoft OLE DB Provider for ODBC Drivers" was not found. The name MSDASQL should be used.
The second suggestion didn't result in any other behaviour, i.e. catastrophic failure (what a sense for drama that provider has :-)).
Koen,
I found this post on the internet, I hope it will help you. There have been a few complains about the same issue check this. Good luck!
-
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)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
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)
I'm glad I could help! 😉
-
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)
Great!
I'm glad you got it to work.
-- Gianluca Sartori
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply