hello, I have a job that reads from a SQL database table and writes to a file on the data services job server.
We are on XI 3.0.
I have confirmed the data direct drivers are able to connect to our SQL Server 2000 database. We can see tables, even see data in the designer, and our DBA has confirmed he saw our user log in when we execute a job. When we execute a job it gives us a generic Data Direct error message that means nothing to us:
“SQL submitted to ODBC data source resulted in error <[DataDirect][ODBC SQL Server Driver]211132111321113211142111421114211152111521115>. The SQL submitted is …”
We have configured the odbc.ini in the dataservices /bin directory to point at our data direct installation: /opt/bo_odbc directory, and configured the odbc.ini in the /opt/bod_odbc directory to match our sql server database settings.
Also, I would note that The job is able to connect, preview data in the tables, it’s just not able to query the data in the tables through a regular query transform.
Please advise of what’s wrong…
I saw another post on here somewhere that indicated that the data direct drivers provided by SAP are not compatable with BOE XIR3. I don’t know if this means that they aren’t compatable with BODI XIR3?
no, if the drivers are not compatible with BOE XI 3.1 that doesn’t automatically implies that its not supported by DS. Check the DS PAR for the version supported by DS
We haven’t installed any SP, major releases or fix packs above 3.0.
Also I tried the link provided and tried downloading the PDF attached. The PDF link didn’t work.
I ran the job and had our SQL Server DBA run a tracer on the Server to see what’s happening.
He came back with interesting results. The query he saw, is nothing to do with the job we’re running. SO, I don’t know if Data Direct is mis translating the SQL or if it’s submitting something before data services runs, but it has absolutely nothing to do with Job I’m running… I’m really confused now as I don’t know where this SQL is coming from. Here’s what the DBA saw when the job ran (Why is it querying the character set, etc from the system table?)
Its running this statement
select 504,c.name,c.description,c.definition from master.dbo.syscharsets c where c.id = convert(tinyint, databasepropertyex( db_name(), ‘sqlcharset’)) set quoted_identifier off
When I run the query, the data returned is:
(No column name) name description definition
504 iso_1 ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Yes you will be able to see the data from the Designer, as the ODBC connection is established from local system(windows system) to MS SQL server Database that works fine in windows.
as Job server on UNIX, need to make sure that the connection from Jobserver is established to MS SQL server.
as menctioned above your Data Direct Drivers are located in /opt/bo_odbc location, make sure that the drivers under this folder are able to connect to MS SQL Database.
there is an example program to test the driver connexion
under your install_location in the location of the DataDirect ODBC driver
(/opt/odbc)
there is a directory named “example” (install_location/example)
do :
cd example
./example
It appears to be a problem with data Direct drivers (they were downloaded from SAP).
It looks like the connection connects through the driver example program. However, any SQL I execute returns an error. So, it’s able to connect, but not translate / send any SQL Statements:
SQLSTATE = 01000
NATIVE ERROR = 5701
MSG = [DataDirect][ODBC SQL Server Driver][SQL Server]Changed database context t
o ‘PQUERYMWMO’.
SQLSTATE = 01000
NATIVE ERROR = 5703
MSG = [DataDirect][ODBC SQL Server Driver][SQL Server]Changed language setting t
o us_english.
Enter SQL statements (Press ENTER to QUIT)
SQL> select address.pin, address.addr_type from dbo.address address
SQLSTATE = HY000
NATIVE ERROR = 0
MSG = [DataDirect][ODBC SQL Server Driver]21113211132111321114211142111421115211
1521115
The bolded error is the error I get reported in Data Services as well.
your error sounds like your odbc.ini file isn’t setup correctly, either missing the Server name entry, or missing the odbc connection identifyer in the odbc.ini file.
Mine is able to find the sql server and even authenticates to it. But, all SQL sent to the SQL server through the drivers, does not work.
