| Author |
Message
|
| scravr |
 Posted: Fri Oct 26, 2007 9:53 am Post subject: MB V6 - MSSQL 2005 |
 |
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
Dose anyone know how to connect and update MSSQL 2005 via MB-V6?
What needs to be configured (ODBC, JDB) ?
What ver/csd of MB do I need?
How to careate/configure the Data Connection?
?
?
?
? |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Fri Oct 26, 2007 10:00 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
|
| Back to top |
|
|
| scravr |
| Posted: Fri Oct 26, 2007 11:55 am Post subject: MB V6 - MSSQL 2005 |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
I seen this link before, but it dose not have what i looking for.
what i need is to create a db connection for a flow and insert into mssql2005. i created an odbc connection on "MSFT odbc admin", but i cannot see it or connect to it on WBI toolkit.
can i use odbc to connect to mssql2005, or do i need jdbc? how can i connect with each?
when trying to create new connection (with data prospective) i see only mssql2000 ONLY.
help, help ,helpo..... |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Oct 26, 2007 12:07 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
The Toolkit uses JDBC.
There isn't a requirement to create a DB connection in the Toolkit before deploying a flow that makes a DB connection in Runtime. Runtime only uses ODBC - unless you specifically write Java code that specifically makes JDBC connections.
The DB connections in Toolkit are for design purposes only, and really only allow you to avoid the warnings that pop up if the database structure isn't know to the Toolkit.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| scravr |
| Posted: Fri Oct 26, 2007 12:40 pm Post subject: MB V6 - MSSQL 2005 |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
i am not talking about deply yet.
i guess i am missing something:
on database-insert node, i need to map many xmlnsc elements to DB-fields. how should i do that? do i need to gener the db schema and put it as a target? how does toollkit know the db-fields name/properties/etc. ?
i like to use odbc. i have no java code. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Oct 26, 2007 12:48 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Okay, yes.
If you want to map from the DB, then you do need to create a toolkit connection, so that the Mapping Editor can know about the tables and columns and etc.
What problems are you having when you try and create a new DB connection in the Toolkit?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| scravr |
| Posted: Mon Oct 29, 2007 10:56 am Post subject: MB V6 - MSSQL 2005 |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
All is on WIN 2003 server:
1. I created an ODBC connection name (with MSFT ODBC Admin) to MSSQL DB (on some other win box) with UID/PW
2. Testing the ODBC connections itself was SUCCESS.
3. On toolkit: I created a DB connection to con1 with same UID/PW + DB definisions. Testing was SUCCESS and I can get Sample Contents.
4. When deply the msg-flow (mqinput-->datainsert (map xml-to-con1 table)--->mqoutput I get error on the DB. It looks like MB uses db2admin as a UID.
Help - Help -Help
here is a trace:
------------------
Timestamps are formatted in local time, 240 minutes before GMT.
Trace written by version 6003; formatter version 6003
2007-10-29 13:50:49.359634 6112 UserTrace BIP4040I: The Execution Group ''Prod'' has processed a configuration message successfully.
A configuration message has been processed successfully. Any configuration changes have been made and stored persistently.
No user action required.
2007-10-29 13:50:49.360393 6112 UserTrace BIP2638I: The MQ output node 'ConfigurationMessageFlow.outputNode' attempted to write a message to queue ''SYSTEM.BROKER.EXECUTIONGROUP.REPLY'' connected to queue manager ''MBQMGR''. The MQCC was '0' and the MQRC was '0'.
2007-10-29 13:50:49.360404 6112 UserTrace BIP2622I: Message successfully output by output node 'ConfigurationMessageFlow.outputNode' to queue ''SYSTEM.BROKER.EXECUTIONGROUP.REPLY'' on queue manager ''MBQMGR''.
2007-10-29 13:50:53.630702 3024 UserTrace BIP2632I: Message received and propagated to 'out' terminal of MQ input node 'DataShare_DOH_MF_PROJ_102907.MQInput'.
2007-10-29 13:50:53.630767 3024 UserTrace BIP6060I: Parser type ''Properties'' created on behalf of node 'DataShare_DOH_MF_PROJ_102907.MQInput' to handle portion of incoming message of length 0 bytes beginning at offset '0'.
2007-10-29 13:50:53.630863 3024 UserTrace BIP6061I: Parser type ''MQMD'' created on behalf of node 'DataShare_DOH_MF_PROJ_102907.MQInput' to handle portion of incoming message of length '364' bytes beginning at offset '0'. Parser type selected based on value ''MQHMD'' from previous parser.
2007-10-29 13:50:53.630905 3024 UserTrace BIP6061I: Parser type ''XMLNSC'' created on behalf of node 'DataShare_DOH_MF_PROJ_102907.MQInput' to handle portion of incoming message of length '10884' bytes beginning at offset '364'. Parser type selected based on value ''XMLNSC'' from previous parser.
2007-10-29 13:50:53.689117 3024 UserTrace BIP2231E: Error detected whilst processing a message in node 'DataShare_DOH_MF_PROJ_102907.MQInput'.
The message broker detected an error whilst processing a message in node 'DataShare_DOH_MF_PROJ_102907.MQInput'. The message has been augmented with an exception list and has been propagated to the node's failure terminal for further processing.
See the following messages for details of the error.
2007-10-29 13:50:53.689147 3024 RecoverableException BIP2230E: Error detected whilst processing a message in node 'DataShare_DOH_MF_PROJ_102907.DataInsert.ComIbmDatabase'.
The message broker detected an error whilst processing a message in node 'DataShare_DOH_MF_PROJ_102907.DataInsert.ComIbmDatabase'. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2007-10-29 13:50:53.689243 3024 DatabaseException BIP2321E: Database error: ODBC return code '-1'.
The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
2007-10-29 13:50:53.689250 3024 DatabaseException BIP2322E: Database error: SQL State ''28000''; Native Error Code '18456'; Error Text ''[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'db2admin'.''.
The error has the following diagnostic information: SQL State ''28000'' SQL Native Error Code '18456' SQL Error Text ''[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'db2admin'.''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error.
2007-10-29 13:50:53.689258 3024 DatabaseException BIP2322E: Database error: SQL State ''IM006''; Native Error Code '0'; Error Text ''[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed''.
The error has the following diagnostic information: SQL State ''IM006'' SQL Native Error Code '0' SQL Error Text ''[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error.
2007-10-29 13:50:53.689311 3024 UserTrace BIP2540I: Node 'DataShare_DOH_MF_PROJ_102907.ErrorsTrace': Finished evaluating expression ''CURRENT_DATE'' at ('', '2.29'). The result was ''DATE '2007-10-29'''.
...
...
...
...
3024 UserTrace BIP4060I: Data ''ErrorsTrace Node:
Date: 10/29/2007
Time: 13:50
Environment is:
Exception List is:
(
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbDatabaseNode.cpp'
(0x03000000):Line = 295
(0x03000000):Function = 'ImbDatabaseNode::evaluate'
(0x03000000):Type = 'ComIbmDatabaseNode'
(0x03000000):Name = 'DataShare_DOH_MF_PROJ_102907#FCMComposite_1_6.ComIbmDataInsert#FCMComposite_1_1'
(0x03000000):Label = 'DataShare_DOH_MF_PROJ_102907.DataInsert.ComIbmDatabase'
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2230
(0x03000000):Text = 'Caught exception and rethrowing'
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 227
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2321
(0x03000000):Text = 'Root SQL exception'
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '-1'
)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 355
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2322
(0x03000000):Text = 'Child SQL exception'
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '28000'
)
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '18456'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'db2admin'.'
)
)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S600_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 355
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2322
(0x03000000):Text = 'Child SQL exception'
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'IM006'
)
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '0'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed'
)
)
)
)
)
PLEASE HELP. |
|
| Back to top |
|
|
|
|
