ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Exception with MSSQL Query using passthru .

Post new topic  Reply to topic
 Exception with MSSQL Query using passthru . « View previous topic :: View next topic » 
Author Message
akill
PostPosted: Mon Aug 07, 2017 11:31 am    Post subject: Exception with MSSQL Query using passthru . Reply with quote

Novice

Joined: 26 Jun 2017
Posts: 16

Hi all ,
I'm getting the below exception while executing select query using passthru .
Exception :
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation .

Here i am adding the code which i have used in my ESQL :

Code:
SET query = 'SELECT *
            FROM '||TableName||' AS C
            WHERE C.card_id =
            (SELECT max(C1.card_id)
            FROM '||TableName||' AS C1
            WHERE C1.branch_code = ?
            AND C1.customer_no = ?
            AND C1.cardprogram = ?)';
SET DUPREC.CardID[] = PASSTHRU(query TO Database.{DSNNAME} VALUES('SSS','AAA','CCCC'));


Note : It is working fine if i give values directly instead of questionmark(?) i.e.,Not Using VALUES in PASSTHRU.

Thanks in advance .
Back to top
View user's profile Send private message
hotshot
PostPosted: Tue Aug 08, 2017 5:49 am    Post subject: Reply with quote

Novice

Joined: 04 Jan 2013
Posts: 19

Hi akill,

To make sure the fine gentlemen here haste in providing their expertise and knowledge, you should try to include in your posts as much as possible of the relevant information for your current issue, like the version and platform you are working on, what you are trying to achieve, what is the exact broker error message you are getting and what have you done already to try and investigate it. Code tags are helpful for readability.

That being said, there isn't something obviously incorrect about your SQL which would yield the noted error. I would advise that you activate and read the user trace for your flow to find out how the SQL that the broker is trying to execute actually looks like. Take that SQL and execute it from an SQL client to see whether it shows more details for that error.

If that doesn't help, you could also try and activate the odbc trace at the broker level and see what additional bits of info you can find there.
Have fun!

Cheers,
B
Back to top
View user's profile Send private message
mqjeff
PostPosted: Tue Aug 08, 2017 5:55 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

hotshot wrote:
Hi akill,

To make sure the fine gentlemen here


Of whatever gender they choose to have, by accident of birth or otherwise.
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
hotshot
PostPosted: Tue Aug 08, 2017 5:59 am    Post subject: Reply with quote

Novice

Joined: 04 Jan 2013
Posts: 19

mqjeff wrote:
hotshot wrote:
Hi akill,

To make sure the fine gentlemen here


Of whatever gender they choose to have, by accident of birth or otherwise.


Oh yes, this stands true for the fine ladies here as well.
Back to top
View user's profile Send private message
timber
PostPosted: Tue Aug 08, 2017 8:26 am    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

Maybe hotshot was expecting the gentlemen to be mostly picky and reluctant, but the ladies to be automatically helpful.
( I've always had a good imagination )
Back to top
View user's profile Send private message
akill
PostPosted: Tue Aug 08, 2017 11:45 am    Post subject: Reply with quote

Novice

Joined: 26 Jun 2017
Posts: 16

hotshot wrote:
Hi akill,

To make sure the fine gentlemen here haste in providing their expertise and knowledge, you should try to include in your posts as much as possible of the relevant information for your current issue, like the version and platform you are working on, what you are trying to achieve, what is the exact broker error message you are getting and what have you done already to try and investigate it. Code tags are helpful for readability.

That being said, there isn't something obviously incorrect about your SQL which would yield the noted error. I would advise that you activate and read the user trace for your flow to find out how the SQL that the broker is trying to execute actually looks like. Take that SQL and execute it from an SQL client to see whether it shows more details for that error.

If that doesn't help, you could also try and activate the odbc trace at the broker level and see what additional bits of info you can find there.
Have fun!

Cheers,
B


Hi , As it was my first question i confused may be , Here are exception and broker version :

Exception :

Text:CHARACTER:[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
DatabaseException
File:CHARACTER:F:\build\S1000_slot1\S1000_P\src\DataFlowEngine\MessageServices\ImbOdbc.cpp
Line:INTEGER:3899
Function:CHARACTER:ImbOdbcStatement::checkRcInner
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2322
Text:CHARACTER:Child SQL exception
Insert
Type:INTEGER:5
Text:CHARACTER:42000
Insert
Type:INTEGER:2
Text:CHARACTER:0
Insert
Type:INTEGER:5
Text:CHARACTER:[Microsoft][ODBC SQL Server Driver]Syntax error or access violation


Version :
Am using IIB V10.0.08

Please let me know if i miss anything , Thanks
Back to top
View user's profile Send private message
Vitor
PostPosted: Tue Aug 08, 2017 11:56 am    Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

akill wrote:
Please let me know if i miss anything , Thanks


What happens if you run the query without hard coded values but through a standard client?

What does the ODBC trace say?
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
timber
PostPosted: Tue Aug 08, 2017 11:58 am    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

I agree with hotshot. Take a user trace to see what IIB is actually doing with your SQL and its parameters. If you don't know how, you'll probably find some posts on this forum that list the required commands.

I can't see anything obviously wrong with your code.
Back to top
View user's profile Send private message
hotshot
PostPosted: Wed Aug 09, 2017 5:48 am    Post subject: Reply with quote

Novice

Joined: 04 Jan 2013
Posts: 19

timber wrote:
Maybe hotshot was expecting the gentlemen to be mostly picky and reluctant, but the ladies to be automatically helpful.
( I've always had a good imagination )

What can I say, we are each a product of our own experiences

timber wrote:
Take a user trace to see what IIB is actually doing with your SQL and its parameters. If you don't know how, you'll probably find some posts on this forum that list the required commands.

Some pointers to get you started on this: mqsichangetrace, run your test, mqsireadlog + mqsiformatlog. You would then see in it what the SQL looks like when the broker before it is about to run it - right before the error.
Back to top
View user's profile Send private message
Vitor
PostPosted: Wed Aug 09, 2017 6:31 am    Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

hotshot wrote:
timber wrote:
Maybe hotshot was expecting the gentlemen to be mostly picky and reluctant, but the ladies to be automatically helpful.
( I've always had a good imagination )

What can I say, we are each a product of our own experiences


For the record, I've never been a gentleman (even before the surgery) and if exerk claims to be, I want DNA verification of species.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Wed Aug 09, 2017 8:41 pm    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY

Vitor wrote:

For the record, I've never been a gentleman (even before the surgery) and if exerk claims to be, I want DNA verification of species.


What surgery? Did you do a reverse Jenner?
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Exception with MSSQL Query using passthru .
Jump to:  



You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.