Showing posts with label ole. Show all posts
Showing posts with label ole. Show all posts

Thursday, March 29, 2012

Failed to retrieve long data for column

Hi,

We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.

Things were fine until now where am getting an error like this. alongside the log also reads like,

There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.

SSIS Error Code: DTS_E_InducedTransformFailureOnError.

Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!

Thanks in Advance

Have you check to see if you are running out of physical disk space?

Unless you have a directory specified in the BLOBTempStoragePath property of the data flow, the default location will be the directory specified by the TEMP environment variable.

Make sure that the directory listed by either of those isn't running out of disk space. If so, then set BLOBTempStoragePath to a directory containing more disk space.|||

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

|||

MShetty wrote:

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

The BLOB storage path is used to hold the contents of a varbinary field if it cannot fit into memory.

So point that parameter to a directory that contains enough disk space and try it again.|||Thanks Phil.. It worked Smile|||I got the same message. Failed to retrieve long data for column I was running an ssis job to import tables from an access database to sql2000 so we could change the back end to sql. I had everything working ok. Then I had to change the source connection from the dev access back end to the production access back end so i could get the latest data. One field in one of 83 tables kept blowing up. It was an Ntext field. I tried everything in these posts to no avial. Finally I noticed that the size of the production mdb was 3x the size of my dev mdb. So, I compacted the production mdb and reran the ssis package. It worked like a charm. Hope this may help someone else out there.
tom in stumptown

Failed to retrieve long data for column

Hi,

We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.

Things were fine until now where am getting an error like this. alongside the log also reads like,

There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.

SSIS Error Code: DTS_E_InducedTransformFailureOnError.

Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!

Thanks in Advance

Have you check to see if you are running out of physical disk space?

Unless you have a directory specified in the BLOBTempStoragePath property of the data flow, the default location will be the directory specified by the TEMP environment variable.

Make sure that the directory listed by either of those isn't running out of disk space. If so, then set BLOBTempStoragePath to a directory containing more disk space.|||

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

|||

MShetty wrote:

Hi Phil,

Thanks for the valuable inputs,Is it ok if I just point a Directory in a Physical drive that has enough disk space is it? What purpose does this BlobStorage Serve ?

The BLOB storage path is used to hold the contents of a varbinary field if it cannot fit into memory.

So point that parameter to a directory that contains enough disk space and try it again.|||Thanks Phil.. It worked Smile|||I got the same message. Failed to retrieve long data for column I was running an ssis job to import tables from an access database to sql2000 so we could change the back end to sql. I had everything working ok. Then I had to change the source connection from the dev access back end to the production access back end so i could get the latest data. One field in one of 83 tables kept blowing up. It was an Ntext field. I tried everything in these posts to no avial. Finally I noticed that the size of the production mdb was 3x the size of my dev mdb. So, I compacted the production mdb and reran the ssis package. It worked like a charm. Hope this may help someone else out there.
tom in stumptown

Friday, March 9, 2012

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.
I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command