I've inherited a horrible stored proc to load an XSL file from a text file into one TEXT column in one row. It BULK INSERTs into a temporary table and then cursors through that somehow appending the lines together into a TEXT variable using TEXTPTR and UPDATETEXT. The whole thing is in a transaction. What could go wrong?
Well, it's suddenly started occasionally not loading some of the start of the file. No errors, it just sometimes misses off the first n bytes (always the same number of bytes).
I have a recipe like this that works:
Is there a cleaner way? Maybe one that doesn't use xp_cmdshell?
asked Jul 26 '10 at 08:33 AM in Default
Here is the sample showing how to insert the guts of the file to the table without using the xp_cmdshell:
The results display (because this what was in the file):
Of course it goes without saying that the file path is relative to the server.
If you can create a CLR assembly with EXTERNAL_ACCESS, then I would go that route. Given that you have a nvarchar(4000) column, you will want to split larger text files into smaller chunks.
You will also want it not to epic fail on Unicode.
A CLR table function could be a neat way to achieve the above two without causing the massive hole that xp_cmdshell does.
answered Jul 26 '10 at 08:44 AM
Matt Whitfield ♦♦