• Replies 6 replies
  • Subscribers 338 subscribers
  • Views 46 views
  • Users 0 members are here
Related

Error on a new customer report

Matthew Hodge
$organization

Hi all,

I'm in the process of setting up a custom report where the code was generously lent to us. I've set the report (well, it's a utility, really) up in Report Setup but when I go to run it, I get the following message:

 

Select Error: SQLSTATE = 22018

Microsoft OLE DB provider for SQL Server

Error converting data type nvarchar to int.

 

Here's what I've checked for so far:

- I've run the actual procedure itself, and that pulls all the information I want it to.

- In InfoMaker, I tried running the report by just entering the correct retrieval arguments and seeing if that worked. It did.

 

So does that mean I'm right in assuming that the error is somewhere in Tessitura talking to InfoMaker? If that's the case, is there anything obvious I should look out for?

 

I'll keep plugging away at it, but if anybody has anything they can suggest in the meantime to try, that would be fantastic.

 

Cheers,

 

Matt

  • $organization

    Should also add that when I click OK on that first error I listed, I get a second one:

     

    Report terminated due to a Datawindow Retrieval Error - Error

    Code: 8114, Error Text: Select Error: SQLSTATE = 22018, etc (as per previous error message).

     

    Which would seem to make me even more sure that it's something to do with the data being passed back to Tessitura.

  • $organization in reply to Matthew Hodge

    The first thing to check here is that the report parameters (in the Tessitura Report Setup application) match the order and datatypes of the parameters as defined in the Infomaker datawindow.  Furthermore, make sure that the report parameters being passed from the datawindow match the stored procedure (or SQL query) parameters.  You are most likely passing a string/text value into a parameter that is designated as an integer….that is if you are able to run the procedure in a query window correctly.

     

    It could also be that the code in the procedure is trying to implicitly convert a string (varchar or nvarchar) value into an integer somewhere in the body of the procedure.

     

    Hope that helps.

    -Ryan

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Matthew Hodge
    Sent: Thursday, April 15, 2010 7:43 PM
    To: Ryan Creps
    Subject: Re: [Tessitura Technical Forum] Error on a new customer report

     

    Should also add that when I click OK on that first error I listed, I get a second one:

     

    Report terminated due to a Datawindow Retrieval Error - Error

    Code: 8114, Error Text: Select Error: SQLSTATE = 22018, etc (as per previous error message).

     

    Which would seem to make me even more sure that it's something to do with the data being passed back to Tessitura.

    From: Matthew Hodge <bounce-matthewhodge3604@tessituranetwork.com>
    Sent: 4/15/2010 9:37:24 PM

    Hi all,

    I'm in the process of setting up a custom report where the code was generously lent to us. I've set the report (well, it's a utility, really) up in Report Setup but when I go to run it, I get the following message:

     

    Select Error: SQLSTATE = 22018

    Microsoft OLE DB provider for SQL Server

    Error converting data type nvarchar to int.

     

    Here's what I've checked for so far:

    - I've run the actual procedure itself, and that pulls all the information I want it to.

    - In InfoMaker, I tried running the report by just entering the correct retrieval arguments and seeing if that worked. It did.

     

    So does that mean I'm right in assuming that the error is somewhere in Tessitura talking to InfoMaker? If that's the case, is there anything obvious I should look out for?

     

    I'll keep plugging away at it, but if anybody has anything they can suggest in the meantime to try, that would be fantastic.

     

    Cheers,

     

    Matt




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • $organization in reply to Ryan Creps (Past Staff Member)

    Actually, it's now started throwing up the same error in InfoMaker as well, when I ran it straight in the datawindow by entering the paramaters in as Retrieval Arguments. So it probably is something in the procedure . . . back in to have a closer look!

  • $organization

    Hi Matt,

    Good day.

    Are you trying to set up a infobreaker report with a datetime parameter in it?

    I had a similar problem days before.

    What happened is the infobreaker try to send something like '0' to a datetime parameter , this caused an error.

    you can catch this with sql proflier when you select the sp from infobreaker list(before the error pop-up).

     

    here is my code to prevent this kind of errors from happening:

    ALTER procedure [dbo].[LRP_BANK_PAYMENT]
    @post_no varchar(50),
    @gl varchar(50),
    @timeCheck varchar(50)
    as
    begin
    if  @timeCheck = '0'
    set @timeCheck='21:30'

    have fun

    Ben

  • $organization in reply to Ben Gu

    Thank you for your help, everyone. What it turned out to be was that something was wrong with the stored procedure call in Infomaker. I'm not sure how it ended up that way, but if you can imagine the procedure as being something like this:

     

    execute dbo.LRP_PROCEDURE NAME;1 @Parameter1 = :Parameter1, @Parameter2 = :Parameter2, etc.

    What had happened was that the colon had disappeared between the = and the Parameter name (e.g. @Parameter2 = Parameter2)

    So instead of picking up the real Parameter 2, which was meant to be an integer, it was getting the name "Parameter2" which was an nvarchar which threw the whole thing for a loop.

    Whew! . . . Now I can actually see whether the *report* does what I want it to do.

     

    Thanks everyone!

  • $organization in reply to Matthew Hodge

    Thanks for tipping me off about the Profiler as well. That was how I discovered what was going on.