Godaddy loses linefeeds (aka \n)

Topics: Database Publishing Wizard, Using SQL Server in Hosted Environments
May 10, 2007 at 11:02 AM
Edited May 10, 2007 at 11:05 AM
From my SQL 2005 I simply exported the table to CSV. I found the CSV import did not work. Each time it said 0 rows affected and reported errors. Then I found the SQL Server Database Publishing Toolkit which exports the DB to a .SQL file to create the tables and uses inserts to add the rows. Great!

From the query analyzer the load SQL file did not work either. I was forced to cut and paste the contents of the sql file to the query analyzer. Cutting and pasting the SQL file to the query analyzer lost the ENTER (newline/line feed/carriage returns/whatever) so much of the data looks like a blob. I tried cutting and pasting from several programs (notepad, notepad2, wordpad) in case the program I cut from lost the escape sequence. Nothing worked and the carriage return was always lost. I would very much like to be able to retain the ENTERs in my DB data, or else this is pointless and the data is ruined.

Any ideas on how to import my data and retain the <ENTER> escape sequences?
May 11, 2007 at 8:47 AM
Edited May 11, 2007 at 8:49 AM
Well, I found the solution. After having the FAQ quoted back to me by the idiots at support I gave up on their interface offering a solution. Instead I adapted the RunSql.aspx (http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=UploadAndExecute) to work on GoDaddy. The main trick there was permissions. In order to see who I was I outputed: System.Security.Principal.WindowsIdentity.GetCurrent().Name. I saw initialially I was running under the Network Service account. Under this account I didn't have file io permissions. Then I turned impersonation on in the web.config. Then I saw it was running under HOSTING\Aspnet. This account could open the file, but didn't have SQL permissions. Finally I impersonated myself (HOSTING\username) and it worked!

Trick was I couldn't impersonate myself from the application root (because my account didn't have access to the .net temp dir). So I had to create a sub-directory, with a minimal web.config which just did the impersonation, and put the aspx page there.

It would have been nice if GoDaddy's interface (and support) was up to stratch. But at least there is a way for the persistent and you can't beat their price. Get what you pay for... yada yada.

Cheers,
Chris
May 16, 2007 at 9:12 PM
Are you saying GoDaddy lost all the line feeds anywhere in teh script (e.g. so the script looked liek one big line of text) or that it lost line feeds within columns (e.g. you have somethign like a varchar(1000) that contained multiple lines of text and this was condensed to a single line)?

-Mike
May 17, 2007 at 8:45 AM
Their is a load .sql button, which didn't work. There is a load csv file, which didn't work. The only way left for most people is to cut and paste their create tables and inserts to the query analzer. The line feeds within varchar columns were lost when importing this way. Only uploading the file and using the code, mentioned above, was I able to get around this problem.

Just a nice change to the RunSql.aspx code is to use the file upload control and use the stream to then import vs. opening a local file which was problematic.