Question

BAQ Error Question - Bad SQL statement

T
Userlevel 3

An odd situation with a BAQ that we have never seen before… In Analyze the BAQ says “Syntax is OK” but when testing we get an error - “Bad SQL statement”.  Not sure if this is related to the fact that is it returning 0 rows or something else at run-time and the error is causing zero rows.  The intent of the BAQ is to create a tab delimited text file with one line of header values and a second line of the data values.  We eventually want the text field to be deposited in a secure FTP site , like a drop box, where BarTender can see it and process the label information.  The SQL “not real” code under the General tab is long but we don’t see anything obvious in it.  The actual table links are in a sub-report and the Top Level SubQuery has only the sub-report with no links to anything else and the display is one huge calculated field (see below) from the fields passed from the SubQuery.

Any ideas what the Bad SQL means even when the Syntax is OK?  It says to check the Server Event Logs but I am not sure where that is for  Cloud/SaaS environment.

Thanks,
-Todd C.

select 
    ('Printer_c' + Char(7) +  'UserID_c' + Char(7) +    'Company' + Char(7) +    'SysDate' + Char(7) +    'TranNum' + Char(7) +    'PartNum' + Char(7) +    'WareHouseCode' + Char(7) +    'BinNum' + Char(7) +    'TranClass' + Char(7) +    'TranType' + Char(7) +    'InventoryTrans' + Char(7) +    'TranDate' + Char(7) +    'TranQty' + Char(7) +    'UM' + Char(7) +    'JobNum' + Char(7) +    'AssemblySeq' + Char(7) +    'JobSeq' + Char(7) +    'PONum' + Char(7) +    'POLine' + Char(7) +    'PORelNum' + Char(7) +    'WareHouse2' + Char(7) +    'BinNum2' + Char(7) +    'OrderNum' + Char(7) +    'OrderLine' + Char(7) +    'OrderRelNum' + Char(7) +    'EntryPerson' + Char(7) +    'PartDescription' + Char(7) +    'RevisionNum' + Char(7) +    'VendorNum' + Char(7) +    'PackSlip' + Char(7) +    'LotNum' + Char(7) +    'VendorNum' + Char(7) +    'VendorID' + Char(7) +    'Name' + Char(13) +   SubQuery1.UD01_Printer_c + Char(7) +  SubQuery1.UD01_UserID_c + Char(7) +  SubQuery1.PartTran_Company + Char(7) +  convert(varchar, SubQuery1.PartTran_SysDate, 101) + Char(7) +  SubQuery1.PartTran_TranNum + Char(7) +  SubQuery1.PartTran_PartNum + Char(7) +  SubQuery1.PartTran_WareHouseCode + Char(7) +  SubQuery1.PartTran_BinNum + Char(7) +  SubQuery1.PartTran_TranClass + Char(7) +  SubQuery1.PartTran_TranType + Char(7) +  SubQuery1.PartTran_InventoryTrans + Char(7) +  convert(varchar, SubQuery1.PartTran_TranDate, 101) + Char(7) +  SubQuery1.PartTran_TranQty + Char(7) +  SubQuery1.PartTran_UM + Char(7) +  SubQuery1.PartTran_JobNum + Char(7) +  SubQuery1.PartTran_AssemblySeq + Char(7) +  SubQuery1.PartTran_JobSeq + Char(7) +  SubQuery1.PartTran_PONum + Char(7) +  SubQuery1.PartTran_POLine + Char(7) +  SubQuery1.PartTran_PORelNum + Char(7) +  SubQuery1.PartTran_WareHouse2 + Char(7) +  SubQuery1.PartTran_BinNum2 + Char(7) +  SubQuery1.PartTran_OrderNum + Char(7) +  SubQuery1.PartTran_OrderLine + Char(7) +  SubQuery1.PartTran_OrderRelNum + Char(7) +  SubQuery1.PartTran_EntryPerson + Char(7) +  SubQuery1.PartTran_PartDescription + Char(7) +  SubQuery1.PartTran_RevisionNum + Char(7) +  SubQuery1.PartTran_VendorNum + Char(7) +  SubQuery1.PartTran_PackSlip + Char(7) +  SubQuery1.PartTran_LotNum + Char(7) +  SubQuery1.Vendor_VendorNum + Char(7) +  SubQuery1.Vendor_VendorID + Char(7) +  SubQuery1.Vendor_Name) as [Calculated_BartenderFormat]
from  (select 
    [UD01].[Printer_c] as [UD01_Printer_c],
    [UD01].[UserID_c] as [UD01_UserID_c],
    [PartTran].[Company] as [PartTran_Company],
    [PartTran].[SysDate] as [PartTran_SysDate],
    [PartTran].[TranNum] as [PartTran_TranNum],
    [PartTran].[PartNum] as [PartTran_PartNum],
    [PartTran].[WareHouseCode] as [PartTran_WareHouseCode],
    [PartTran].[BinNum] as [PartTran_BinNum],
    [PartTran].[TranClass] as [PartTran_TranClass],
    [PartTran].[TranType] as [PartTran_TranType],
    [PartTran].[InventoryTrans] as [PartTran_InventoryTrans],
    [PartTran].[TranDate] as [PartTran_TranDate],
    [PartTran].[TranQty] as [PartTran_TranQty],
    [PartTran].[UM] as [PartTran_UM],
    [PartTran].[JobNum] as [PartTran_JobNum],
    [PartTran].[AssemblySeq] as [PartTran_AssemblySeq],
    [PartTran].[JobSeq] as [PartTran_JobSeq],
    [PartTran].[PONum] as [PartTran_PONum],
    [PartTran].[POLine] as [PartTran_POLine],
    [PartTran].[PORelNum] as [PartTran_PORelNum],
    [PartTran].[WareHouse2] as [PartTran_WareHouse2],
    [PartTran].[BinNum2] as [PartTran_BinNum2],
    [PartTran].[OrderNum] as [PartTran_OrderNum],
    [PartTran].[OrderLine] as [PartTran_OrderLine],
    [PartTran].[OrderRelNum] as [PartTran_OrderRelNum],
    [PartTran].[EntryPerson] as [PartTran_EntryPerson],
    [PartTran].[PartDescription] as [PartTran_PartDescription],
    [PartTran].[RevisionNum] as [PartTran_RevisionNum],
    [PartTran].[VendorNum] as [PartTran_VendorNum],
    [PartTran].[PackSlip] as [PartTran_PackSlip],
    [PartTran].[LotNum] as [PartTran_LotNum],
    [Vendor].[VendorNum] as [Vendor_VendorNum],
    [Vendor].[VendorID] as [Vendor_VendorID],
    [Vendor].[Name] as [Vendor_Name]
from Erp.PartTran as PartTran
inner join Ice.UD01 as UD01 on 
    UD01.Company = PartTran.Company
    and ( UD01.UserID_c = @CurrentUserID  )

left outer join Erp.Vendor as Vendor on 
    PartTran.Company = Vendor.Company
    and PartTran.VendorNum = Vendor.VendorNum
where (PartTran.SysDate = @Today  and (PartTran.SysTime >= @CurrentTime  and PartTran.SysTime < Constants.CurrentTime+1100 ) and PartTran.TranType like 'PUR-%'  and PartTran.EntryPerson = @CurrentUserID))  as SubQuery1

 

 
  

Todd Caughey Epicor Contract Consultant, Randstad USA, Plymouth, MN

8 replies

J
Userlevel 1

Have you checked out the SQL server logs That will usually give you  more information on the error.

T
Userlevel 3
  1.  I typoed the Char(7) that should have been Char(9) for Horizontal Tab.  Fixed with no effect.
  2.  Where are the server logs.  I have no access to SQL in the Cloud.  Do I have to request a log file from Cloud Support?

Thanks,
-Todd

 

Todd Caughey Epicor Contract Consultant, Randstad USA, Plymouth, MN
J
Userlevel 1

Sorry, I do not know. We are on premises. If you have a contact at EPICOR that can get you the information, the server logs have specifics which I have found helpful

S

Do you have any Calculated fields?  The Analyze button is just going to do a syntax check.   You can pass a syntax check and still get a bad SQL statement error at run-time.  For example, if you have a Calculated field that is dividing two fields.  That would pass syntax, but at run-time, if you have a denominator that evaluates to 0, that will throw a bad SQL statement (divide by zero error).

Unrelated, check your first PartTran criteria using SysTime.

 

T
Userlevel 3

OK, I found out that in order to get the details of the Error Log  we must open a Cloud Support case.  Just one more thing out of our hands.  The initial response from regular support was:

yes unfortunately there isn't a way for cloud customers to see the log file, we'll need to create a cloud task to get the full error in the log.

They did the cloud task (pretty quickly too) and we got the response:

Conversion failed when converting the varchar value '‚' to data type int.

So we are now trying to track this down in the BAQ.  The only conversions we can see are two Date to Text - Convert(nvarchar, PartTran_SysDate, 101) where 101 is mm/dd/yyyy format - the default.  But we are not understanding the ‘,’ to int part.  No divides by zero or other issues like that (thanks ssearle) .  

But the main take away is that in the Cloud there is a barrier to getting error log info.  It would be nice if there was an interface option for Cloud sites to enter some selected reference info (BAQ name, time window, etc...) and have it send the relevant log info for errors.  We might need to raise an enhancement request with Epicor.  At the least it would take load off of their support group.

-Todd 


 

Todd Caughey Epicor Contract Consultant, Randstad USA, Plymouth, MN
A

add a clause that defines the return if the product of a calculated field is 0.

T
Userlevel 3

We finally resolved this on Friday.  In the TopLevel subquery there was only one display field that was a concatenation of all the fields for the tab delimited output.  What we had not noticed was that in the actual subquery where the linking and criteria were happening some of the output fields from that were numeric fields so when passed to the top level they were not compatible with string functions.  We created calculated fields to convert everyone of them to string (nvarchar), used those to merge and the error went away - the output looked great too.

The cloud support involvement for error messages like this still bugs me but they seemed to be pretty responsive and now we know where to go to get details.

-Todd

 

Todd Caughey Epicor Contract Consultant, Randstad USA, Plymouth, MN
A

awesome!!

Reply


Terms & Conditions