upload to database error from ASP
am 08.06.2006 22:19:45 von Rahul Chatterjee
Hello All
I have a web page which reads in a csv/fixed width file and builds an XML
DOM which gets parsed and uploaded into a database.
Below is the stored proc which does the upload into the database. The file
is read in by the asp page and the string is built. This program works fine
for small files but for larger files it breaks. Example:
When the user created a text file fixed width with trailing spaces in the
data, I could upload only 360 records (even though my database or the asp
page has no limit on it). Every time I tried to go past 360, the page would
display an error (internal server error). When I stripped out the trailing
and leading spaces, I was able to load past the 360th record or if I broke
down the file into small pieces (with the trailing and leading spaces) I
could upload it.
Is there a limitation imposed by either ASP or XML where in we cannot create
an XML upload record past a certain size
Please advise
Thanks
DECLARE @idoc int
EXEC sp_XML_preparedocument @idoc OUTPUT, @doc
INSERT INTO TBLDATAUPLOADtemp (GROUPID, CONTRNUM, SECID, SSN,LASTNAME,
FIRSTNAME,MI,HRSWK, HRSPD , GROSS , MISC1, MISC2, MISC3, MISC4, WRKDT,
INSDATE,DISPORD)
SELECT GROUPID, CONTRNUM, SECID, SSN, LASTNAME, FIRSTNAME,MI,HRSWK, HRSPD ,
GROSS , MISC1, MISC2, MISC3, MISC4, WRKDT, CONVERT(VARCHAR,getDate()
,101),DISPORD
FROM OPENXML(@idoc,'CONTRACTOR/LINEITEM',1)
WITH (GROUPID int,
CONTRNUM char(9),
SECID char(9),
SSN char(9),
LASTNAME char(30),
FIRSTNAME char(20),
MI char(1),
HRSWK decimal(9,2),
HRSPD decimal(9,2),
GROSS decimal(9,2),
MISC1 decimal(9,2),
MISC2 decimal(9,2),
MISC3 decimal(9,2),
MISC4 decimal(9,2),
WRKDT datetime,
INSDATE datetime,
DISPORD int)
Re: upload to database error from ASP
am 09.06.2006 16:41:16 von reb01501
Rahul Chatterjee wrote:
> Thank you for the direction. Its a stack overflow error. I believe I
> am overreaching the form posting limit that ASP has (from what I
> read on the internet. I like your suggestion about using a DTS
> package to do it.
Do a thorough read of the articles in the FAQ at www.sqldts.com.
> Can you give me a few pointers how to do it. I can
> set up the dts package to read in a CSV file.
>
> Here is what happens typically on this site. The user uploads a file
> (UploadFormContri.asp) - the file is processed in UploadFile.asp. In
> ValidateUpload16.asp the data is validated and converted to XML DOM.
> If there are any errors in format or data types, this page flags the
> error and displays it on the screen. Next in InsertContribution.asp
> the data from the DOM is inserted into the database table and the
> DisplayContri_option.asp loads it and displays the next data set.
>
> The problem with my page arises after the data has been read and the
> XML DOM has been created. It tries to do a request on the XML string
> in the subsequent page and I guess that is too much for it to handle.
> The file size I am uploading is 40Kb.
>
> Is there any way to do this using a DTS package - say the user
> uploads the file to the designated server folder (names can vary),
> the page executes a DTS package which reads and validates the data
> and spits out appropriate error messages for each line of data and if
> valid inserts the info into the table
>
This is where I would use a staging table. Simply import all the data into
the staging table, then run the appropriate INSERT...SELECT statements that
move the data into the actual destination tables. This can be done entirely
in the DTS package, using SQL Tasks
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"