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

Order Import 'Cannot fetch a row...'

Nancy Sheleheda
$organization

Once every three years or so, I have to do an order import, mostly from one facility to another within our own consortia's database (our sub-licensee's like to do co-productions).  And every time, I waste at least a day fiddling with the import xml file and troubleshooting esoteric errors.  Today is no different, and I'm hoping someone has seen this before and can help.  We would really like to use the importing barcode feature and it's time to print tickets!

First, the problem is not with SQL Server being unable to read the file.  About 3 hours ago, I got a test file to import just fine.  And the xml format file and the data txt file are sitting smack dab on the C: drive of the SQL Server.

I have tried the standard format xml file as delivered with the 12.5.1 installation files.

I have tried commenting out specific elements of the above xml file.

I have tried deleting specific elements of the above file.

I have had 2 other colleagues proof the txt file and compare it to the above xml files.

I have restarted SQL Server (of course, I'm doing this in TEST).

I have not tried opening a bottle of wine.  Yet.

Here is the error message:

"Error getting data from C:\Order_Import\DTH_Test2.txt5 - Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". The import file data may be out of sync with the specified format file.

There is also an error message above it,saying that "the file OrderImportStandardFormat.xml does not have a valid extension (xml) and will be skipped."  I would have normally thought this was a smoking gun, except I did get a wee test import to go about 3 hours ago.  Plus I see other people on this forum have gotten that error, and also had successful imports. 

Thanks!

  • $organization

    Hey Nancy,

    Usually when I experience that error, it is because:

    • a column in the format file that doesn't exist in the data file
    • columns aren't in the right order
    • a column in the data file that doesn't exist in the format file

    Looks like you've tried to fix all of that though, so the only other thing that springs to mind is to check if your first column has data in it? I've found that if the first column doesn't have data in it, it can sometimes shift everything a column to the left.

    Hope that helps?

    BV

  • $organization

    Hi Nancy,

    You’ve probably already tried this, but when I get that error, after all of the things you’ve tried and then the wine, I try removing all but the first row of data. If that goes in successfully, I try half of the data, and so on. I guess my point is that, depending on the file format, the error might be due to an unexpected character hiding in your data that makes the bcp think there’s an extra field in that row, so you may just have to identify the bad character.

     

    Good luck!

    ~Katie

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Beau Vigushin
    Sent: Wednesday, September 14, 2016 5:59 PM
    To: Catherine Lachance-Duffy
    Subject: Re: [Tessitura Technical Forum] Order Import 'Cannot fetch a row...'

     

    Hey Nancy,

    Usually when I experience that error, it is because:

    • a column in the format file that doesn't exist in the data file
    • columns aren't in the right order
    • a column in the data file that doesn't exist in the format file

    Looks like you've tried to fix all of that though, so the only other thing that springs to mind is to check if your first column has data in it? I've found that if the first column doesn't have data in it, it can sometimes shift everything a column to the left.

    Hope that helps?

    BV

    From: Nancy Sheleheda <bounce-nancysheleheda3263@tessituranetwork.com>
    Sent: 9/14/2016 5:17:55 PM

    Once every three years or so, I have to do an order import, mostly from one facility to another within our own consortia's database (our sub-licensee's like to do co-productions).  And every time, I waste at least a day fiddling with the import xml file and troubleshooting esoteric errors.  Today is no different, and I'm hoping someone has seen this before and can help.  We would really like to use the importing barcode feature and it's time to print tickets!

    First, the problem is not with SQL Server being unable to read the file.  About 3 hours ago, I got a test file to import just fine.  And the xml format file and the data txt file are sitting smack dab on the C: drive of the SQL Server.

    I have tried the standard format xml file as delivered with the 12.5.1 installation files.

    I have tried commenting out specific elements of the above xml file.

    I have tried deleting specific elements of the above file.

    I have had 2 other colleagues proof the txt file and compare it to the above xml files.

    I have restarted SQL Server (of course, I'm doing this in TEST).

    I have not tried opening a bottle of wine.  Yet.

    Here is the error message:

    "Error getting data from C:\Order_Import\DTH_Test2.txt5 - Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". The import file data may be out of sync with the specified format file.

    There is also an error message above it,saying that "the file OrderImportStandardFormat.xml does not have a valid extension (xml) and will be skipped."  I would have normally thought this was a smoking gun, except I did get a wee test import to go about 3 hours ago.  Plus I see other people on this forum have gotten that error, and also had successful imports. 

    Thanks!




    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

    Oh friends... you are so helpful.  Although, neither of your suggestions was the actual problem, they got me further down the path of troubleshooting and then I got a response from the helpdesk with the actual answer.

    The standard OrderImport xml file has defined the 'source' field as only having 30 characters.  The import staging table (TW_ORDER_IMPORT) has defined the source field as 80 characters.  So once I increased the xml definition to 80, everything worked.  (Never mind that the TX_APPEAL_MEDIA_TYPE source_name spec is 50 characters... these aren't the droids you're looking for.)

    I need to start a little spreadsheet of all the things to check whenever I have to do one of these imports every few years.

  • $organization

    Share that spreadsheet, please.   Makes me wish Tessitura Network had a function to collaborate on a spreadsheet, a la Google Sheets.

     

    I’ve been on the same hunt like the others, but only came to the topic late with suggestions already made by others.   

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nancy Sheleheda
    Sent: Thursday, September 15, 2016 10:00 AM
    To: John Trimble <J.Trimble@dbdt.com>
    Subject: Re: [Tessitura Technical Forum] Order Import 'Cannot fetch a row...'

     

    Oh friends... you are so helpful.  Although, neither of your suggestions was the actual problem, they got me further down the path of troubleshooting and then I got a response from the helpdesk with the actual answer.

    The standard OrderImport xml file has defined the 'source' field as only having 30 characters.  The import staging table (TW_ORDER_IMPORT) has defined the source field as 80 characters.  So once I increased the xml definition to 80, everything worked.  (Never mind that the TX_APPEAL_MEDIA_TYPE source_name spec is 50 characters... these aren't the droids you're looking for.)

    I need to start a little spreadsheet of all the things to check whenever I have to do one of these imports every few years.

    From: Nancy Sheleheda <bounce-nancysheleheda3263@tessituranetwork.com>
    Sent: 9/14/2016 5:17:55 PM

    Once every three years or so, I have to do an order import, mostly from one facility to another within our own consortia's database (our sub-licensee's like to do co-productions).  And every time, I waste at least a day fiddling with the import xml file and troubleshooting esoteric errors.  Today is no different, and I'm hoping someone has seen this before and can help.  We would really like to use the importing barcode feature and it's time to print tickets!

    First, the problem is not with SQL Server being unable to read the file.  About 3 hours ago, I got a test file to import just fine.  And the xml format file and the data txt file are sitting smack dab on the C: drive of the SQL Server.

    I have tried the standard format xml file as delivered with the 12.5.1 installation files.

    I have tried commenting out specific elements of the above xml file.

    I have tried deleting specific elements of the above file.

    I have had 2 other colleagues proof the txt file and compare it to the above xml files.

    I have restarted SQL Server (of course, I'm doing this in TEST).

    I have not tried opening a bottle of wine.  Yet.

    Here is the error message:

    "Error getting data from C:\Order_Import\DTH_Test2.txt5 - Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". The import file data may be out of sync with the specified format file.

    There is also an error message above it,saying that "the file OrderImportStandardFormat.xml does not have a valid extension (xml) and will be skipped."  I would have normally thought this was a smoking gun, except I did get a wee test import to go about 3 hours ago.  Plus I see other people on this forum have gotten that error, and also had successful imports. 

    Thanks!




    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

    Thanks for the heads-up about the source field length, Nancy! Now you’ve saved all of us some time on future imports! J

     

    ~Katie

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nancy Sheleheda
    Sent: Thursday, September 15, 2016 10:59 AM
    To: Catherine Lachance-Duffy
    Subject: Re: [Tessitura Technical Forum] Order Import 'Cannot fetch a row...'

     

    Oh friends... you are so helpful.  Although, neither of your suggestions was the actual problem, they got me further down the path of troubleshooting and then I got a response from the helpdesk with the actual answer.

    The standard OrderImport xml file has defined the 'source' field as only having 30 characters.  The import staging table (TW_ORDER_IMPORT) has defined the source field as 80 characters.  So once I increased the xml definition to 80, everything worked.  (Never mind that the TX_APPEAL_MEDIA_TYPE source_name spec is 50 characters... these aren't the droids you're looking for.)

    I need to start a little spreadsheet of all the things to check whenever I have to do one of these imports every few years.

    From: Nancy Sheleheda <bounce-nancysheleheda3263@tessituranetwork.com>
    Sent: 9/14/2016 5:17:55 PM

    Once every three years or so, I have to do an order import, mostly from one facility to another within our own consortia's database (our sub-licensee's like to do co-productions).  And every time, I waste at least a day fiddling with the import xml file and troubleshooting esoteric errors.  Today is no different, and I'm hoping someone has seen this before and can help.  We would really like to use the importing barcode feature and it's time to print tickets!

    First, the problem is not with SQL Server being unable to read the file.  About 3 hours ago, I got a test file to import just fine.  And the xml format file and the data txt file are sitting smack dab on the C: drive of the SQL Server.

    I have tried the standard format xml file as delivered with the 12.5.1 installation files.

    I have tried commenting out specific elements of the above xml file.

    I have tried deleting specific elements of the above file.

    I have had 2 other colleagues proof the txt file and compare it to the above xml files.

    I have restarted SQL Server (of course, I'm doing this in TEST).

    I have not tried opening a bottle of wine.  Yet.

    Here is the error message:

    "Error getting data from C:\Order_Import\DTH_Test2.txt5 - Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". The import file data may be out of sync with the specified format file.

    There is also an error message above it,saying that "the file OrderImportStandardFormat.xml does not have a valid extension (xml) and will be skipped."  I would have normally thought this was a smoking gun, except I did get a wee test import to go about 3 hours ago.  Plus I see other people on this forum have gotten that error, and also had successful imports. 

    Thanks!




    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 Nancy Sheleheda

    Thanks Nancy - This post just helped me kick my error ball a little further down the field.