Query a BASIS/BBJ server database using SQL Server
I installed the BBj ODBC Driver and established an ODBC connection to a remote server using the ODBC Data Source Administrator and now I would like to query this server through SQL Server using the Microsoft SQL Server Administrator but when I do a Import Data Wizard I do not get the option to select the "BBj ODBC" connection I created.
Does anyone know how to do this?
Does anyone know how to do this?
Kevin Cross
I have not used BBj specifically, but have had luck connecting to systems through ODBC from Linked Server within SQL. Then you do queries using SELECT * FROM OPENQUERY(LinkServerName, 'SELECT STATEMENT IN TARGET SYSTEM SYNTAX')
ASKER
Ok, I have a table on the server called "INVE" that I want to get from SQL. I only want this table because the database is huge. The ODBC connection name in Windows is "OSAS Data". I modifed the code and attached it.
Does this look correct?
Does this look correct?
SELECT * FROM OPENQUERY(OSAS Data, 'SELECT table INVE)
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I renamed the Systems DSN to "OSASData" (removing the space)
I went into SQL Server Management Studio/SERVER/Server Objects/Linked Servers/ and created a new linked server. Under the "Provider" option there is no selection for BBj ODBC. I have the BBj ODBC driver installed because I needed that initially just to create the ODBC link to it under "Data Sources (ODBC)" in the administrative tools folder.
Maybe this is just not meant to happen?
I went into SQL Server Management Studio/SERVER/Server Objects/Linked Servers/ and created a new linked server. Under the "Provider" option there is no selection for BBj ODBC. I have the BBj ODBC driver installed because I needed that initially just to create the ODBC link to it under "Data Sources (ODBC)" in the administrative tools folder.
Maybe this is just not meant to happen?
You use the BBj ODBC driver to create the DSN, but I believe you just need to use the Microsoft OLE DB Provider for ODBC Drivers in the link server setup following the instructions on what fields to fill in for ODBC drivers from link above and should work out for you.
I could have sworn the link showed the steps, but just checked again and it doesn't specific. With a system DSN, try creating linked server and picking the provider name I stated above and then put the OSASData in the text box labeled "Data source:".
That should be it, but could be specific other settings for Product name, Provider string, Location, Catalog for BBj that someone may be able to identify as I am not as familiar with that specific system.
That should be it, but could be specific other settings for Product name, Provider string, Location, Catalog for BBj that someone may be able to identify as I am not as familiar with that specific system.
ASKER
It looks like it is possible to Query a BBJ server using SQL but it is not possible with the OSAS application that we are running due to locked fields. The only way linking is possible is if no one else is accessing the system at the same time the query is running.