The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Creating a SQL View which calls a non-sql database/table
Posted: 27 Apr 07 4:09 PM
|
fiogf49gjkf0d I'm trying to whip something simple together that will let me display non-SQL Server data inside SalesLogix (v7) using a SQL View. Can anyone share your experience? RJ, I'm not buying a utility  |
|
|
| |
|
Re: Creating a SQL View which calls a non-sql database/table
Posted: 27 Apr 07 4:30 PM
|
fiogf49gjkf0d Well, we need to demo a scenario where data can be viewed coming from an AS400 system. However, if I can just figure out how to pull data from an ODBC source I'd be doing well. So far I can't even pull data from an Excel spreadsheet. |
|
|
|
Re: Creating a SQL View which calls a non-sql database/table
Posted: 27 Apr 07 4:39 PM
|
fiogf49gjkf0d Look into linked servers for SQL. It allows you to create a linked server for some other OLEDB data source in SQL. You can then use that linked server for queries etc in SQL statements.
You create linked servers using the sp_addlinkedserver proc, where you specify the OLEDB provider to use and the other conection details. Once you've added the linked server you can open queries from it using OPENQUERY and other means built into SQL.
A google seach will return some useful results: http://www.google.com/search?hl=en&q=sql+linked+server
-Ryan
|
|
|
|
Re: Creating a SQL View which calls a non-sql database/table
Posted: 28 Apr 07 6:25 AM
|
fiogf49gjkf0d There is some IBM software you can install on your machine (the name escapes me, but I want to say client express for some reason). It gives you terminal emulation and odbc access. Once you have the odbc drivers, then you just need somebody to guide you through the data structures.
I've tried the link server route, and there must me some magic dust that was required because I never got it to work. I DID notice that there were lots of 3rd party utilities to make it easier... hmmm... maybe there is a connection there.
BTW... both the AS400's I dealt with had cryptic table and field names (each no more than 8 characters - a legacy from the 70's) so you will most likely need help from IT.
have fun...
ws |
|
|
|
Re: Creating a SQL View which calls a non-sql database/table
Posted: 29 Apr 07 9:00 PM
|
fiogf49gjkf0d Client Access is the name of the IBM term emulation software (but it was a few years ago - so may have been superseded).
I used the ODBC drivers that came with it to get data out of the AS/400 and into Access - seemed to work OK (I ended up using pass-thru queries for speed). Don't expect the implementation of SQL to give you everything that SQL Server gives you though ... |
|
|
|
Re: Creating a SQL View which calls a non-sql database/table
Posted: 30 Apr 07 9:30 AM
|
fiogf49gjkf0d I'm not having any problem with linked servers. ie, I can link an excel spreadsheet directly. However, I'm having a great deal of difficulty create a linked server using the "Microsoft OLEDB Provider for ODBC Drivers". I'm still search for a good example in this area.
The first article in the google search you provided had an excellent example of the former. |
|
|
|