Hi, I am running into an issue, while I import an excel file into sql server 2005 using OpenRowSet, it works fine when excel file is closed, If excel file is open it gives an error message. I have an excel file which is being updated 8 to 10 times in a minute by a third party software, I have to import this excel file into sql server 2005 very 10 seconds.
Any help would be higly appriciated......... Thanks, Yogi
How do you know which rows you want to import if the file is constantly being updated? Do you have some kind of sequence number so that you can detect gaps?
If you really want to run with something like you have suggested then why not set up a process to copy the Excel spreadsheet file periodically and then get your OPENROWSET code to read from this copy.
I think it's a general principle that a file cannot, or should not, be accessed while it's open and being written to by another program.
How is the conflict resolved if a part of the file is simultaneously written and read?
That being said, I've come across a situation on one of the computers I use (not Excel, not Windows, not even a PC, so it's hardly relevant) where I could download open files by FTP.
But I use another practically identical computer where this is not possible. I don't know why, but it seems to me to be the normal situation.
This idea of copying a file, and operating on the copy fits what pjp has said. If you can get away with it ... The frequency of access you require, Yogi, seems to invite conflict between reading and writing.
Is it bad if you just stop the import when you get the error and wait 10 seconds until the next import?