| Author |
Message
|
| rkford11 |
 Posted: Thu Mar 23, 2006 3:36 pm Post subject: error while accessing sql server database |
 |
|
Partisan
Joined: 06 Jun 2004
Posts: 316
|
Our Environment
windows 2003
WBIMB 6.0
WMQ 6.0
I am accessing SQL server database and when i test the ODBC connection it works fine.
But when i deploy the message flow on to the broker and input a message to extract the data it gives me the following error
Database error: SQL State ''42000''; Native Error Code '8180'; Error Text ''[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.''.
The error has the following diagnostic information: SQL State ''42000''SQL Native Error Code '8180' SQL Error Text ''[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.''
Please guide me through this.
Thanks |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Thu Mar 23, 2006 8:27 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
|
| Back to top |
|
|
| dsriksha |
| Posted: Fri Mar 24, 2006 6:01 am Post subject: |
|
|
Voyager
Joined: 27 Feb 2005
Posts: 95
|
Hi RKFORD11
are you using any database update or insert in your flows? |
|
| Back to top |
|
|
| sarat |
| Posted: Fri Mar 24, 2006 8:08 am Post subject: |
|
|
Centurion
Joined: 29 Jun 2005
Posts: 136
Location: India
|
Hi rkford11,
First try to hit the database with some sample java program. I mean check whether you are able to interact with data base or not. Le me know successful
_________________
With Regards,
Sarat. |
|
| Back to top |
|
|
| JT |
| Posted: Fri Mar 24, 2006 8:32 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Post the SQL statement that produces the error. |
|
| Back to top |
|
|
| rkford11 |
| Posted: Fri Mar 24, 2006 8:43 am Post subject: |
|
|
Partisan
Joined: 06 Jun 2004
Posts: 316
|
| JT wrote: |
| Post the SQL statement that produces the error. |
I am able to interact with the database on the server from query analyzer on my desktop and also thru TOAD
This is my statement
SET OutputRoot.XML.Data[] = SELECT * FROM Database.dbo.AppTable AS AB WHERE AB.ID ='376ADZ6D';
FYI it's
SQL Server version 2000 service pack 3a
Thanks |
|
| Back to top |
|
|
| dsriksha |
| Posted: Fri Mar 24, 2006 9:04 am Post subject: |
|
|
Voyager
Joined: 27 Feb 2005
Posts: 95
|
This is my statement
SET OutputRoot.XML.Data[] = SELECT * FROM Database.dbo.AppTable AS AB WHERE AB.ID ='376ADZ6D';
Thanks[/quote]
Hi Rkford11 kindly try your esql statement like this
SELECT AB.* FROM Database.dbo.AppTable AS AB WHERE AB.ID ='376ADZ6D';
And What is dbo is it part of table name |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Sat Mar 25, 2006 6:38 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| dsriksha wrote: |
Hi Rkford11 kindly try your esql statement like this
SELECT AB.* FROM Database.dbo.AppTable AS AB WHERE AB.ID ='376ADZ6D'; |
Actually, the whole statement should be
| Code: |
| SET OutputRoot.XML.Data[] = (SELECT AB.* FROM Database.dbo.AppTable AS AB WHERE AB.ID ='376ADZ6D'); |
And I'd assume that dbo is the schema name, for the database owner on SQLServer.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mgk |
| Posted: Sat Mar 25, 2006 7:43 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1638
|
In V6, the ( ) around a SELECT are not required (but it is not a problem to use them). Also, the single * does work for a SELECT in the select list, so in this case the original statement as posted looks correct to me. dbo on SQLServer is indeed the schema the table is supposed to be in (the default schema name). I would make sure that the permissions were correct for the broker DB user to access that table (and that the table is in the schema dbo).
Also it would be useful to know what version of SQL server you are using, and if the DB is local or remote. In addition, are you using SQLServer or Windows Authentication (as you must use SQLServer auth).
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| rkford11 |
| Posted: Wed Mar 29, 2006 9:20 am Post subject: |
|
|
Partisan
Joined: 06 Jun 2004
Posts: 316
|
The space character in the column name of the table is the culprit of the issue.
columns were defined as Column One, Column Two, Column Three
I modified my statement to
SET OutputRoot.XML.DATA = SELECT AB."Column One" FROM Database.dbo.AppTable AS AB WHERE AB.ID='376ADZ6D';
but it still throws me the same error.
Please advice me whether it is possible for the broker( SQL Driver) to extract data when the column names are defined with space character in between.
Thanks |
|
| Back to top |
|
|
| dsriksha |
| Posted: Wed Mar 29, 2006 12:08 pm Post subject: |
|
|
Voyager
Joined: 27 Feb 2005
Posts: 95
|
| rkford11 wrote: |
The space character in the column name of the table is the culprit of the issue.
columns were defined as Column One, Column Two, Column Three
I modified my statement to
SET OutputRoot.XML.DATA = SELECT AB."Column One" FROM Database.dbo.AppTable AS AB WHERE AB.ID='376ADZ6D';
but it still throws me the same error.
Please advice me whether it is possible for the broker( SQL Driver) to extract data when the column names are defined with space character in between.
Thanks |
This may work.....
Set your column name to a variable like this.
Set Environment.variable= 'column||null||name';
Then use EVAL function.
Enjoy  |
|
| Back to top |
|
|
| JT |
| Posted: Wed Mar 29, 2006 12:38 pm Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
First, configure your SQL statement so that it executes properly from the database command-line, then take that statement and wrap it with PASSTHRU. For example:
| Code: |
| SET OutputRoot.XML.DATA = PASSTHRU('SELECT AB."Column One" FROM Database.dbo.AppTable AS AB WHERE AB.ID=?','376ADZ6D'); |
|
|
| Back to top |
|
|
| klabran |
| Posted: Thu Mar 30, 2006 7:23 am Post subject: |
|
|
Master
Joined: 19 Feb 2004
Posts: 259
Location: Flagstaff AZ
|
| If this is still an issue with the column names then add an alias to your column names in the select statement or create a stored procedure that aliases the column names on return. The alias will be what is returned in both these cases. |
|
| Back to top |
|
|
|
|
