Click to See Complete Forum and Search --> : MSSQL Bulk Insert Alternative
Hello,
I have been using a Bulk Insert command called from an ASP page to append delimited text files to various tables in my MSSQL database. It works fine on my local computer, but the hosting company that my boss has selected (1and1.com) does not support Bulk Inserts. My question is if there is a alternative way of appending text files that can be called from an ASP page, or if I should look for another hosting company that supports my way of doing things.
Thanks,
Harry
dotancohen
10-12-2006, 09:29 PM
Look for a different host.
I'm currently in the process of switching to dailyrazor.com. They support bulk inserts and ad-hoc queries. (or so they say).
dotancohen
10-14-2006, 06:22 AM
You should create a test page that performs all the wizardry that you expect that you maybe, maybe, maybe will need someday, and try that page on their servers first. Many hosts will let you do that before commiting to an account.
You should create a test page that performs all the wizardry that you expect that you maybe, maybe, maybe will need someday, and try that page on their servers first. Many hosts will let you do that before commiting to an account.
Thats what I am planning on doing first thing next week. They have a 30 day money back guarantee. Hopefully things will work out.
dotancohen
10-14-2006, 11:02 AM
Let us know how itt goes in any case. People will google this thread, you know.
Thanks.
Well, after hours of beating my head against a wall, I finally gave up trying to use the bulk insert command to append text data to my mssql database. I’m now using the ASP textstream Object. It’s a lot slower, take more coding and will require more coding maintenance in the future, but at least it works.
Thanks,
Harry
dotancohen
10-24-2006, 08:00 AM
Give us a code example. Lets see why it didn't work.
It really wasn't a problem with the coding. Apparently the vast majority of hosting firms don’t allow you the security rights to run the bulk insert command. It ran fine on my local server. I switched to DailyRazor.com because they said they supported bulk inserts. Of course when I tried it, it didn’t work. I got the same ‘permission denied’ message. I also had the same issue with using the following type of query:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;Database="&UploadFile&"\;','SELECT company,customernumber,userlogin,date,time,amount,authcode FROM "&FileName&"') SELECT Company,customernumber,userlogin,date,time,amount,authcode FROM Transactions where company ="&"'"&companyid&"'"&" and posted<>'T'"
So, I gave up and switched to using the filesystem object to import and export data. If works fine, but it is a lot more coding, and I takes much longer to import large amounts of data.
This is my first attempt and web development. Maybe I got off track by trying to use the bulk insert command, but it seemed like a really easy quick way of getting data into your web database.
Thanks,
Harry