Today we got a client ask us to upload data from excel sheet into sql server, update data to the sql server database.
Well, my collegues usings a simple script that copy the values from the excel file. If there are 1,000 records, there will be 1,000 rows of sql update statements.
I decided to do it using another method and a good friend of our - DTS.
Actually, I got another method which uses openrowset which also works but the database server doesn't allow MSDTC to start so bo bian. The DBA scared to corrupt the production db server simply by starting the MSDTC service on the server, which I suspected was installed incorrectly, thats why it refuse to restart. Anyway, i think its ridiculous that a simple MSDTC service can corrupt a database server.
So, early in the morning i use DTS to upload to a temp table and I wrote a simple sql script to update the required table using the same Primary key on both tables as a join. Following that, the client call again, and she said the excel data is wrong, had to resend and ask us to re-upload again.
Thinking back, although writing 1,000 lines of sql script is simple but due to unforeseen cirumstances using DTS and a simple update statement can save the day and increase productivity and reduce the amount of effort wasted.
Before
you start, make sure that you don't have an open instance of Acrobat
Reader, or else the program will not allow you to move the necessary
folders.
1. Go to the installation folder of acrobat reader
(C:\program files\adobe\acrobat\reader\.. whatever)
2.
Move all the files and folders from the "Plug-ins" directory to the
"Optional" directory. Cut and paste the files NOT copy & paste.
That's all there is to it! No side-effects, whatsoever !
Will work for v7, and v8, not sure about earlier versions.