Utilizing SQL 2005 Linked Server to connect to external systems via OLE DB
10 March 2007You can utilize MS SQL Server 2005’s Linked Servers ability to connect to any OLE DB provider. Here we are connecting to an iSeries (AS/400) DB2 using the IBMDA400 ole db provider
Here is the command to add a linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'MYSERVER', @srvproduct=N'IBMDA400', @provider=N'IBMDA400', @datasrc=N'MYSERVER', @provstr=N'Transport Product=Client Access; Force Translate=0; DATA SOURCE=MYSERVER; USER ID=MYUSERID; PASSWORD=MYPASSWORD; Connect Timeout=30;'
You’ll notice the Force Translate is set to 0, this is to translate the CSSID 65535 characters. For some reason on my system a 0 makes this work… I have seen other people use a value of 1 or 37 for this.
Querying data on a Linked Server
I had to use the OpenQuery statement to access the data on my linked server. Although I have seen other syntax that would suggest that you can access the linked server with out this. I was unable to make that syntax work with my instance.
Here is an example query I was able to execute against my JD Edwards library:
SELECT * FROM OpenQuery(JDEPROD, 'select * from F0911 where gldgj > 107001')
Why is this SO COOL!?
Not only can you select data from a linked system, but you can use most of the extra select parameters with this, like the INTO clause, or the TOP clause.
I was able to use this to transfer data from my JD Edwards EnterpriseOne production library with a command like this:
SELECT *
INTO #glData
FROM OpenQuery('select * from f0911 where glfy = 7')
This is just another option you have for integrating, reporting, or data warehousing. Think of composite reporting with your financial system, your project management system, your time keeper system, your HR system, your <insert anything here> system!
Drop me a note if any of you have used this, your likes / dislikes about this method.
No comments yet
Leave a Reply
You must be logged in to post a comment.