Help w/ Not enough storage is available to complete this operation.

I've got a delimited text file that aprox 5.5GB that I'm trying to
load into a table on SQL 2000 (Windows Server 2003) and I keep getting
the error "Not enough storage is available to complete this
operation."
Changing the commit size doesn't seem to make a difference, but when I
changed a couple the the varchar field sizes from less then 4 to 5,
helped. I checked with the admin on the server and was told all SP
and hotfixes were loaded.
The dang file used to load fine on SQL 7 (Windows Adv Server 2000)
when I used to run the same DTS there so it can't be a problem with
the text file.
I'm at a loss on what to try next. Anyone got any suggestions? Below
is the error log.
****************************************************************************************************
The execution of the following DTS Package failed:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step
'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700
Package Name: ADDRESS_TRANS_LOAD
Package Description: (null)
Package ID: {40416C9A-D5FA-4898-B8D6-4C7BE4359405}
Package Version: {DD97E07A-D00A-4B7D-8D57-BB0369932439}
Package Execution Lineage: {316E7F9E-E7B3-472B-A285-F38972E0FA11}
Executed On: PSQL0V2436771
Executed By: ADDR_jobs
Execution Started: 7/27/2007 11:19:02 PM
Execution Completed: 7/28/2007 3:13:07 AM
Total Execution Time: 14042.532 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:Not enough storage is available to complete
this operation.
(Microsoft Data Transformation Services (DTS) Data Pump (8007000e):
Not enough storage is available to complete this operation.
)
Step Error code: 8007000E
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/27/2007 11:19:03 PM
Step Execution Completed: 7/28/2007 3:13:07 AM
Total Step Execution Time: 14041.591 seconds
Progress count in Step: 8055000
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 7/27/2007 11:19:02 PM
Step Execution Completed: 7/27/2007 11:19:03 PM
Total Step Execution Time: 0.591 seconds
Progress count in Step: 0
****************************************************************************************************
BTW, it's not a limitation on the size of my DB.. I've still got over
20 GB of unused space allocated to me on the server farm.
BULK INSERT doesn't seem to work either. It says it completed
successfully but nothing ever gets inserted. Below is the BULK INSERT
command I was using... What the heck can be causing my problem?
****************************************************************************************************
BULK INSERT address_trans
FROM '\\VPMY024210\WebData$\Proddata\address_trans.txt'
WITH (
FIRSTROW = 2,
FIELDTERMINATOR = '~'
ROWTERMINATOR = '\r\n'
)
****************************************************************************************************
I really need to get this data loaded... Someone PLEASE HELP!!> BTW, it's not a limitation on the size of my DB.. I've still got over
> 20 GB of unused space allocated to me on the server farm.
How big is the file? How much space is available on C: and the data drive?
How much RAM is available? Have you tried loading from a local drive
instead of a \\share\? Your description sounds like you are using a hosted
SQL Server provided by someone. If so, have you brought the error to their
attention?
--
Aaron Bertrand
SQL Server MVP|||On Jul 28, 1:43 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > BTW, it's not a limitation on the size of my DB.. I've still got over
> > 20 GB of unused space allocated to me on the server farm.
> How big is the file? How much space is available on C: and the data drive?
> How much RAM is available? Have you tried loading from a local drive
> instead of a \\share\? Your description sounds like you are using a hosted
> SQL Server provided by someone. If so, have you brought the error to their
> attention?
> --
> Aaron Bertrand
> SQL Server MVP
The file is 5.5GB and I've got 25GB allocated (Used 5GB) on the SQL
server. On the shared drive, where the file is located, I've got 70GB
allocated (used 20GB, leaving 50GB fee). I have no idea on the amount
of RAM is available on the SQL server, but on a guess, it's gotta have
over 8GB. 8GB of ram is their minimum spec for a server. I haven't
loaded from a local drive, due to user/security issues.
As for speaking with the admin, it wasn't much help. All I was told
was that all Hotfixes and SP were installed and to play with the
commit size.

Help using sp_xml_preparedocument

Hi all,

We are using a VB component to create and save an XML document on our
local LAN.

We want to load this straight into a DB Table using a query based on
OPENXML. Trouble is, the sp_xml_preparedocument command always throws an
error.

Here is some code:

>>>> CODE >>>>
DECLARE @.idoc int
DECLARE @.doc varchar(1000)

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
<<<< END OF CODE <<<<

where @.doc is the filename of our file on the LAN to be opened, and
@.idoc is the handle to be created.

the error is:
XML parsing error: Invalid at the top level of the document.

Here is some of the XML Document saved on the LAN that should be opened
by sp_xml_preparedocument and prepared.

<?xml version="1.0" encoding="UTF-8" ?>
<xdata created_by="OSA" created_at="Thu Nov 13 11:33:33 EST 2003">
<rows row_count="1000">
<row>
<COL1>714761905</COL1>
<COL2>714761905</COL2>
<COL3>714761905</COL3>
</row>
</xdata
Any advice or tips whould be very helpful...

thanks

Phil

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!The xml doc you posted is not well-formed; it is mussing the </rows> end
tag. In any case, the @.doc parameter specifies the actual xml text, not
the path a file containing xml.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"no spam" <anonymous@.devdex.com> wrote in message
news:3fb4f782$0$198$75868355@.news.frii.net...
> Hi all,
> We are using a VB component to create and save an XML document on our
> local LAN.
> We want to load this straight into a DB Table using a query based on
> OPENXML. Trouble is, the sp_xml_preparedocument command always throws
an
> error.
> Here is some code:
> >>>> CODE >>>>
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> <<<< END OF CODE <<<<
> where @.doc is the filename of our file on the LAN to be opened, and
> @.idoc is the handle to be created.
> the error is:
> XML parsing error: Invalid at the top level of the document.
> Here is some of the XML Document saved on the LAN that should be
opened
> by sp_xml_preparedocument and prepared.
> <?xml version="1.0" encoding="UTF-8" ?>
> <xdata created_by="OSA" created_at="Thu Nov 13 11:33:33 EST 2003">
> <rows row_count="1000">
> <row>
> <COL1>714761905</COL1>
> <COL2>714761905</COL2>
> <COL3>714761905</COL3>
> </row>
> </xdata>
> Any advice or tips whould be very helpful...
> thanks
> Phil
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Help using Lookup Transformation

Hi All,

I am doing something really simple and it doesnt work, may be I am missing something, What I am trying to accomplish is to load a fact table using lookup transaformation, however my source data was different from the data in my dimension (or the datatype ) I had to use a data conversion task before my lookup , so the data flow is something like this source -> Data Conversion -> Lookup -> destination , I am getting an error at my lookup task where it says the "[Lookup [82]] Error: Row yielded no match during lookup". and then it just fails. I know for sure that there has to be matching data. donno what is it that I am missing.

Thanks

Things to look out for...

Trailing spaces on either side of the lookup (source data or lookup data). If one has trailing spaces and the other doesn't, it won't match.

CaSE. The lookups are case sensitive.|||

Hi,

I am pretty sure that the case is matching and also I have tried using LTRIM(RTRIM) for the trailing spaces, still getting the same error, any thing else I need to look into?

Thanks

|||

db_guy wrote:

Hi,

I am pretty sure that the case is matching and also I have tried using LTRIM(RTRIM) for the trailing spaces, still getting the same error, any thing else I need to look into?

Thanks

Redirect the errors (error output, red arrow) to a flat file source, OLE DB destination, or even a Row Count Transformation, and add a data viewer to inspect the data that does not match.|||

I am still struggling with the loading this fact , the problem I have now is the package runs succesfully but nothing gets loaded into the fact, I am redirecting the rows into a rows count transformation but how do I see whats not getting into the fact or how do I see the values in those variables.

Thanks

|||

db_guy wrote:

I am still struggling with the loading this fact , the problem I have now is the package runs succesfully but nothing gets loaded into the fact, I am redirecting the rows into a rows count transformation but how do I see whats not getting into the fact or how do I see the values in those variables.

Thanks

Add a data viewer to the red arrow connecting the lookup to the row count. (Double click on it and select data viewers, then add a new data viewer)|||

Hi,

I have attached data viewers and one thing I noticed is they behave in a weird way, sometimes they have 0 records and sometimes they have data, donno why.I have three lookups that I use , and then finally they all load the fact table

Thanks

|||Connect all lookup error flows to a Union All component, and then to a row counter. Add the data viewer between the union all and the row counter. This will let you see all of the lookup error outputs.

Or, just hook each lookup up to their own row counter and add data viewers there. You're data is getting caught up somewhere.|||

I think using the data viewers I have narrowed down the problem thanks for that, however I have another question how do I do lookup where I have a join which is something like this

INNER JOIN dbo.Dim_Time T WITH (NOLOCK)

ON SUBSTRING(T.MonthYear,1,3) = SUBSTRING(s.Fiscal_Time,1,3)

AND SUBSTRING(T.MonthYear,6,2) = SUBSTRING(s.Fiscal_Time,6,2)

The problem is I was using derived column to create/replace the stage column using the substring function , however I cannot do that twice because derived column cannot be used twice for the same column, any suggestions?

Thanks

|||

Where did you get that you can't use a derived column twice for the same column?

If you are having a problem with your lookup you can always use a Source / merge join transform, or you can go to the advanced tab of the Lookup transform and select enable memory restriction and type in your exact query with parameters and line it up that way (This is much slower than using it in the regular lookup mode)

If you were to give more info we could probably tell you the exact steps needed to complete this process.

(also, if your original question was answered, remember to mark it as such, along with all other applicable answers)

HELP Retrieving remote XML and returning ROWSET

Within a stored procedure, I need to retrieve XML over HTTP from an external
URL, then load some stuff into a recordset and return as such.
Like this, sort-of:
-- *******************************
create procudure queryremote (@.param)
declare @.xmldoc varchar(2000)
set @.xmldoc = (GET 'HTTP://someplaceservesXML.com/script.ext?param=' +
@.param)
sp_preparedocument (@.xmldoc etc.)
SELECT * FROM OPENXML(thexmlthing, "/xpath")
Return
-- *********************************
Just to be clear, this is exactly the opposite of what everyone is trying to
do; many articles on getting XML OUT of SQL server and putting XML data INTO
SQL server, over HTTP.
This requirement is to provide backward -compatibility to
SQL-recordset-aware-only applications. Making an HTTP XML query service
LOOK like an SQL recordset.
Email and post please, thanks a lot!
Richard Weerts
rweerts@.ndis.us
In SQL Server 2000 you have two options:
Do the get on the mid-tier and pass the XML as an argument to the stored
proc.
Write your own extended stored proc to GET the data.
In SQL Server 2005, you can write the function using CLR instead of writing
an extended stored proc.
HTH
Michael
"411XML Richard Weerts" <rweerts@.ndis.us> wrote in message
news:OLr5YmXfEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
> Within a stored procedure, I need to retrieve XML over HTTP from an
> external
> URL, then load some stuff into a recordset and return as such.
> Like this, sort-of:
> -- *******************************
> create procudure queryremote (@.param)
> declare @.xmldoc varchar(2000)
> set @.xmldoc = (GET 'HTTP://someplaceservesXML.com/script.ext?param=' +
> @.param)
> sp_preparedocument (@.xmldoc etc.)
> SELECT * FROM OPENXML(thexmlthing, "/xpath")
> Return
> -- *********************************
> Just to be clear, this is exactly the opposite of what everyone is trying
> to
> do; many articles on getting XML OUT of SQL server and putting XML data
> INTO
> SQL server, over HTTP.
> This requirement is to provide backward -compatibility to
> SQL-recordset-aware-only applications. Making an HTTP XML query service
> LOOK like an SQL recordset.
> Email and post please, thanks a lot!
> Richard Weerts
> rweerts@.ndis.us
>
>