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 |||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 |||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 register dll files needed for DTS

I found the information of installing DTS service without installing SQL server on the disc:

INSTALLATION NOTES FOR DATA TRANSFORMATION SERVICES (DTS)
------------------
The following files must be registered using the regsvr32.exe utility:
dtsffile.dll
dtspkg.dll
dtspump.dll
axscphst.dll

Example: regsvr32 80\Tools\Binn\dtsffile.dll

The sqlunirl.dll file should reside in the system folder (i.e., \winnt\system32 or \windows\system).

when I attempted to register the dlls, the message
:"loadLibrary("...\dtsffile.dll")failed - The speified module could not be
found.

is there anything I have to do before I can get the dll registered?

Thanks in advance.problem solved, please consult here:

http://www.sqldts.com/default.aspx?6,105,225,0,1

fail an execute sql task

I have a dts package that is essentially three transformation tasks that creates 3 text files. The transform data is accomplished through an sql query. Before I execute the transform tasks I would like to peform a couple of simple existence checks to verify data: i.e,

if exists (select * from table where week_ending = endoflastweek(getdate())

I could put the statement into the sql (if exists, begin/end) of each of the transformation tasks and prevent the process from executing, but it would be easier to put it into an "execute sql task" and if it succeeds, fail the task or make some kind of declaration not to proceed. I'm not sure how to do this...I know this can be done with an activeX script,

Main = DTSTaskExecResult_Failure

Is there a way to do this in an execute sql task step?I am not sure if you are looking for raiserror or return here. Look up both in Books Online, and see what you think. I am leaning raiserror.

Extracting RDL file from published reports in RS 2000

In order to have a backup of the rdl files for reporting, does exist a manner
to extract the rdl files inside RS starting from published reports?
In other terms, is it possible to revert the report publishing process in
order to have the rdl files?
Many thanksOn Sep 11, 3:44 am, Pasquale <Pasqu...@.discussions.microsoft.com>
wrote:
> In order to have a backup of the rdl files for reporting, does exist a manner
> to extract the rdl files inside RS starting from published reports?
> In other terms, is it possible to revert the report publishing process in
> order to have the rdl files?
> Many thanks
Try this application, works great for me. http://sqldbatips.com/showarticle.asp?ID=62|||Oh, well! It's very useful.
Many thanks
"Tim Toennies" wrote:
> On Sep 11, 3:44 am, Pasquale <Pasqu...@.discussions.microsoft.com>
> wrote:
> > In order to have a backup of the rdl files for reporting, does exist a manner
> > to extract the rdl files inside RS starting from published reports?
> > In other terms, is it possible to revert the report publishing process in
> > order to have the rdl files?
> > Many thanks
> Try this application, works great for me. http://sqldbatips.com/showarticle.asp?ID=62
>

Extracting dates from XML stored in SQL Server 2005

I have recently started storing xml files within SQL 2005. I have a problem extracting a date of format 20-02-2007 out of the XML and storing it in a field of datetime. The database is in American format (2007-02-20) and is failing when I try and insert the date. In the past when extracting the date at .Net level and passing through to SQL Server this was never a problem. I have tried to convert the date first but still fails. Is there an easy way to get around this problem or do I need to store the date as a string?

Thanks

Danny

You could use CONVERT function in you T-SQL statements with 105 style:

declare @.dt varchar(10)

set @.dt ='20-02-2007'

select convert(datetime,@.dt,105)

--RESULT

2007-02-20 00:00:00.000

|||

Yes this worked, but I had to convert to varchar before converting to datetime (cannot convert XML directly to datetime).

Thanks

Danny

Extracting data from SQL Server to XML documents

Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!
If you're using SQL Server 2000, you can use a variety of techniques -
including FOR XML queries, annotated schemas, etc. See SQL Server Books
Online, and install SQLXML 3.0
(http://www.microsoft.com/downloads/d...isplayLang=en.).
For earlier versions, you'd have to write a custom solution - there's no
built-in support for XML.
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!
|||thx for the info!
"Graeme Malcolm" <graemem_cm@.hotmail.com> a crit dans le message de
news:u4T2%23UjVFHA.1796@.TK2MSFTNGP15.phx.gbl...
> If you're using SQL Server 2000, you can use a variety of techniques -
> including FOR XML queries, annotated schemas, etc. See SQL Server Books
> Online, and install SQLXML 3.0
>
(http://www.microsoft.com/downloads/d...a154-8e23-47d2
-a033-764259cfb53b&DisplayLang=en.).
> For earlier versions, you'd have to write a custom solution - there's no
> built-in support for XML.
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
> news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys, here's the situation.
> I need to extract data from SQL Server to multiple XML files, on a daily
> basis.
> What is the best strategy to accomplish this task?
> I did try two things already. These method were using stored procedures.
> The first one was the use of the System Stored Procedure sp_makewebtask
> using template files. This task failed due to a dll error.
> My second attempt was using the "bcp" utility. It didn't work because of
the
> version of SQL Server we are using right now (too old).
> Is there any other ways to generate XML from SQL Server? Can we use DTS to
> accomplished this task?
> Thanks for the help!
>
>

Extracting data from SQL Server to XML documents

Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!If you're using SQL Server 2000, you can use a variety of techniques -
including FOR XML queries, annotated schemas, etc. See SQL Server Books
Online, and install SQLXML 3.0
(http://www.microsoft.com/downloads/...DisplayLang=en.).
For earlier versions, you'd have to write a custom solution - there's no
built-in support for XML.
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
Hey guys, here's the situation.
I need to extract data from SQL Server to multiple XML files, on a daily
basis.
What is the best strategy to accomplish this task?
I did try two things already. These method were using stored procedures.
The first one was the use of the System Stored Procedure sp_makewebtask
using template files. This task failed due to a dll error.
My second attempt was using the "bcp" utility. It didn't work because of the
version of SQL Server we are using right now (too old).
Is there any other ways to generate XML from SQL Server? Can we use DTS to
accomplished this task?
Thanks for the help!|||thx for the info!
"Graeme Malcolm" <graemem_cm@.hotmail.com> a crit dans le message de
news:u4T2%23UjVFHA.1796@.TK2MSFTNGP15.phx.gbl...
> If you're using SQL Server 2000, you can use a variety of techniques -
> including FOR XML queries, annotated schemas, etc. See SQL Server Books
> Online, and install SQLXML 3.0
>
(http://www.microsoft.com/downloads/...4a154-8e23-47d2
-a033-764259cfb53b&DisplayLang=en.).
> For earlier versions, you'd have to write a custom solution - there's no
> built-in support for XML.
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Dominic Feron" <dominic.feron@.dessausoprin.com> wrote in message
> news:%23W6QGyiVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys, here's the situation.
> I need to extract data from SQL Server to multiple XML files, on a daily
> basis.
> What is the best strategy to accomplish this task?
> I did try two things already. These method were using stored procedures.
> The first one was the use of the System Stored Procedure sp_makewebtask
> using template files. This task failed due to a dll error.
> My second attempt was using the "bcp" utility. It didn't work because of
the
> version of SQL Server we are using right now (too old).
> Is there any other ways to generate XML from SQL Server? Can we use DTS to
> accomplished this task?
> Thanks for the help!
>
>

EXTRACTING DATA FROM FOXPRO/ACCESS 2000 INT SQL SERVER 2005

Dear Sir/Madam,

I have some data which is stores in Foxpro dbf files & Access 200 MDB file. Can i extract import these files into SQL Server 2005

Yes.|||

Hi Wilfi,

You can use the FoxPro and Visual FoxPro OLE DB data provider to access the FoxPro DBFs. It is downloadable from http://msdn2.microsoft.com/en-us/vfoxpro/bb190232.aspx .

|||

Dear Cindy,

What i wanted is that, the data which is stored in Access 2000 mdb file to be exported into SQL SERVER 2005.

thanks & with regards,

wilfi

extracting data from a varchar variable?

Guys.

I have some data like this one:

'Uploadfiles\CompanyID\ProjectCode\Files\File1.doc'

P.S: the values may vary each time.

I need to programmatically extract from that kind of string only file1.doc

Ho can I dynamically do it? any function? statement? etc? please help.

The idea is getting only the string after the last "\"

Thanks
Frdeclare @.t varchar(256)

select @.t = 'Uploadfiles\CompanyID\ProjectCode\Files\File1.doc'

select reverse(substring(reverse(@.t),1,patindex('%\%',reverse(@.t))-1))