July 28, 2009 at 5:55 pm
I have a bunch of Excel sheets which I've setup as linked servers on my SQL Server. I've written a query which copies data from those Excel sheets (one by one) into corresponding SQL tables. It started as something like this:
-------
INSERT INTO table (columns)
SELECT (columns) FROM ExcelLinkedServerA
INSERT INTO table (columns)
SELECT (columns) FROM ExcelLinkedServerB
-------
Then I found that the Excel file for ExcelLinkedServerB is sometimes not available. I have 8 Excel files (and linked servers) and it's tedious to comment out those which aren't available each time I run the script. So I declared a couple of parameters:
DECLARE @p_LoadExcelB BIT, @p_LoadExcelA BIT
Then set these parameters depending on whether the files are available:
SET @p_LoadExcelA = 1
SET @p_LoadExcelB = 0
I then put an IF clause around each INSERT statement:
-------
IF @p_LoadExcelA = 1
BEGIN
INSERT INTO table (columns)
SELECT (columns) FROM ExcelLinkedServerA
END
IF @p_LoadExcelB = 1
BEGIN
INSERT INTO table (columns)
SELECT (columns) FROM ExcelLinkedServerB
END
-------
When I go to run this, SQL Server tells me there is an error with the Excel sheet for Server B. I know this, it's not available. That's why I put that condition in there.
Why is SQL looking for the data source when it's outside the logic of the code to do so? Can I turn this off or how can I program this better so it works the way I want?
Sam
P.S. I know there are other ways to do this, including SSIS (which I'm building on the side, by the way). I'd like to know how to solve my issue above though without changing tools.
July 28, 2009 at 8:10 pm
You could try wrapping a try/catch around them. But I REALLY have to recommend the SSIS solution instead.. Also keep in mind a connection in SSIS or as a linked server will not be available if someone has it open.. Keep in mind there are also security considerations for this when they are setup as linked servers. Anyone with enough access can read them..
CEWII
July 28, 2009 at 8:27 pm
Hmm even throwing a try/catch around the INSERT (in place of the IF) has the same issue. The query falls over when it can't find the Excel sheet.
Any more ideas?
July 28, 2009 at 9:17 pm
how about wrap it with try catch, and use dynamic SQL instead; that way it won't test for the connection until the EXEC fires:
BEGIN TRY
Begin Transaction
IF @p_LoadExcelB = 1
BEGIN
SET @sql='
INSERT INTO table (columns)
SELECT (columns) FROM ExcelLinkedServerB'
exec(@sql)
END
Commit transaction
END TRY
BEGIN CATCH
--ignore the error, the link was not up
END CATCH
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply