1

I have a web application built in ASP and initially hosted on my own local server - Win 2008, IIS 7.0, SQL Server 2008.

I then bought a discountASP.Net hosting account for website + database.

The web application takes a file from a folder under the website root and bulk inserts into records in the database. Everything worked fine on the local development box.

When the database + website was moved to the DiscountASP.Net hosted account, the BULK INSERT would not worked and DiscountASP.Net told us that BULK INSERT is not allowed since the hosted database server is a shared server.

What is the work around for this scenario? They have indicated that we could use individual INSERTs.

Is there are better solutions out there? Has someone faced a similar problem and have some solution in place that we can re-use? (rather than reinventing the wheel).

Thanks for looking up my problem.

Moiz Tankiwala
  • 6,070
  • 7
  • 38
  • 51

2 Answers2

0

You'd have to use SQLBulkCopy from .net or send in XML to be parsed in SQL code

gbn
  • 422,506
  • 82
  • 585
  • 676
  • The source file that I get is a flat file, with column and line separators and the web code is Classic ASP. – Moiz Tankiwala Dec 09 '11 at 17:52
  • On a side note DiscountASP.Net does allow .Net code in SQL Server. Any thoughts? – Moiz Tankiwala Dec 09 '11 at 18:20
  • @Moiz Tankiwala: you'd have a similar problem in accessing resources outside of SQL Server unless you can call a web service or such. But I'd expect limitations of what CLR they allow otherwise you can interfere with other client databases – gbn Dec 13 '11 at 07:51
0

After some trial, I was able to get a solution myself. Here are the solution steps (Due to client confidentiality, I cannot share the code):

  1. I read all the text from the file on the Web Server from the ASP code using the Scripting.FileSystemObject server object.

  2. I am passing the entire text of the file as a variable to a stored procedure (SQL Server 2008). The datatype of the parameter has to be "adLongVarChar" Refer the data type table here (If the enum is not recognized, as was my case, I used the value 201 instead of the enum).

  3. Wrote SQL code in stored procedure to parse the string into rows and then further parse each line into column values.

Hope the pseudo code of my solutions is helpful to someone.

Moiz Tankiwala
  • 6,070
  • 7
  • 38
  • 51