November 3, 2021 at 2:32 am
I have a Stored Procedure that uses OPENROWSET to an Excel spreadsheet and sends the SELECT results back to an application. Works fine last a long time! Now I want to throw some error checking into the equation, specifically (If the Excel file is open, If the file doesn't exist, If the file TAB is misspelled) using "Try...Catch". The "Open File" error works correctly sending the Raised Error Number back to the application, but the other two does not. Am I correct in assuming the Raised Error is forcefully terminating the Transact-SQL user session, and if so What are my options for solving this problem?
November 3, 2021 at 2:03 pm
You say you are using an application - is there a reason you are not just pulling the Excel data in through that rather than using SQL Server as a "middle man"?
Now, my understanding is that SQL Server when it goes to use OPENROWSET with Excel, it will either succeed or fail. I don't think there is much room to capture different errors there. My understanding is that the error you are getting is from OPENROWSET, not from Excel.
My approach to this IF you need SQL Server to work with the Excel file would be to have the application handle the sanity checking on the file prior to asking SQL to open it. What I mean is have the application attempt to open the file and check if the file exists, if it is already open somewhere else, and for the worksheet spellings. And for that matter, I would have the application actually populate the worksheet name for the end user to pick from a dropdown to reduce the chance it is spelled wrong and have the user need to look the name up. Then once you have all the information you need from the application side, close the excel file, wait for excel to terminate, and after that call the stored procedure with the appropriate arguments. This way SQL doesn't need to do the sanity logic; it just needs to pull the data in and do something with it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 27, 2021 at 10:20 am
This was removed by the editor as SPAM
April 30, 2022 at 5:31 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply