Openquery Error Handling
Contents |
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: TRY CATCH Around an OpenQuery Statement Doesn't Work SQL Server > Transact-SQL Question 0 Sign
Sp_testlinkedserver Example
in to vote I have a bunch of rows to move and I linked server error handling was hoping I could catch the errors all at once with a try catch instead of running the script over
Sp_testlinkedserver Return
and over. BEGIN TRY INSERT INTO [SLD].[dbo].[tblAnalyticalContext] SELECT * FROM OPENQUERY(MYSQL, 'SELECT * FROM MySQLDB.tblAnalyticalContext;') END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber linked server connection timeout ,ERROR_MESSAGE() AS ErrorMessage; END CATCH Table MySQLDB.tblAnalyticalContext doesn't exist in the MySQL DB. So an error should be generated and it is. But the try/catch doesn't work but SMS returns the error: Msg 7350, Level 16, State 2, Line 2 Cannot get the column information from OLE DB provider "MSDASQL" for linked server "MYSQL". If I replace the INSERT statement with a try catch sql server SELECT 1/0 the try/catch does work and the try/catch returns a 'division by zero error'. Any way to get the try/catch to work with the OpenQuery? If that's what's causing the problem. Tuesday, February 08, 2011 10:45 PM Reply | Quote Answers 0 Sign in to vote It has been a while since I worked with OPENQUERY, but I think this is a compile time error that occurs when the DB Engine tries to retrieve metadata from the destination. If that is the case, TRY CATCH does not capture compile errors: http://msdn.microsoft.com/en-us/library/ms179296.aspx You're SELECT 1/0 is a run-time error that TRY CATCH does handle. TRY CATCH should handle errors as the DB Engine retrieves the rows for your queries, since those would be run-time errors.Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights. Marked as answer by Ai-hua Qiu Wednesday, February 16, 2011 8:18 AM Wednesday, February 09, 2011 12:32 AM Reply | Quote 0 Sign in to vote If you add this after BEGIN TRY PRINT 'Hello!' You will see see that TRY block is not even entered. As Alan says, the e
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to handle an empty result set from an OpenQuery call to linked analysis server in dynamic SQL? up vote 2 down vote favorite I have a https://social.msdn.microsoft.com/Forums/sqlserver/en-US/69e61db5-63ec-4fe0-bfc0-704f10f6ab65/try-catch-around-an-openquery-statement-doesnt-work?forum=transactsql number of stored procedures structured similarly to this: DECLARE @sql NVARCHAR(MAX) DECLARE @mdx NVARCHAR(MAX) CREATE table #result ( [col1] NVARCHAR(50), [col2] INT, [col3] INT ) SET @mdx = '{some dynamic MDX}' SET @sql = 'SELECT a.* FROM OpenQuery(LinkedAnalysisServer, ''' + @mdx + ''') AS a' INSERT INTO #result EXEC sp_executesql @sql SELECT * FROM #result This works quite well when results exist in the cube. However, when the OpenQuery results are empty, the INSERT fails with http://stackoverflow.com/questions/2969734/how-to-handle-an-empty-result-set-from-an-openquery-call-to-linked-analysis-serv this error: Column name or number of supplied values does not match table definition. My question is, what is the best way to handle this scenario? I'm using the results in a static report file (.rdlc), so the explicit typing of the temp table is (I'm pretty sure) required. tsql stored-procedures ssas dynamic-sql openquery share|improve this question edited Jun 24 '10 at 11:00 marc_s 454k938711033 asked Jun 3 '10 at 20:59 Mr. Palomar 8018 add a comment| 2 Answers 2 active oldest votes up vote 4 down vote accepted Use TRY/CATCH in your stored procedure, you'll notice there is a specific error number for your problem, so check the error number and if it is that, return an empty result set. As you already have the table defined that'll be easier. PseudoCode looks something like this: SET @mdx = '{some dynamic MDX}' SET @sql = 'SELECT a.* FROM OpenQuery(LinkedAnalysisServer, ''' + @mdx + ''') AS a' BEGIN TRY INSERT INTO #result EXEC sp_executesql @sql END TRY BEGIN CATCH IF ERROR_NUMBER <> 'The error number you are seeing' BEGIN RAISERROR('Something happened that was not an empty result set') END END CATCH SELECT * FROM #result You'll want to check for that particular error, so that you don't just return empty result sets if your SSAS server crashes for example. share|improve this answer answered Jun 16 '10 at 17:36 M
visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. http://www.dbforums.com/showthread.php?1655983-Linked-Server-Error-Handling To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 2 of 2 Thread: Linked Server Error Handling Tweet Thread Tools Show Printable https://community.progress.com/community_groups/openedge_development/f/19/t/1943 Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 04-28-10,01:32 #1 DBA_Rahul View Profile View Forum Posts Visit Homepage Registered error handling User Join Date May 2004 Location bangalore Posts 270 Unanswered: Linked Server Error Handling Hi Guys, I am executing a DTS to transfer my SQL SERVER 2000 data to ORACLE 10g. I have created the destination Oracle 10g as an linked server in SQL SERVER 2000. In all stored procedures for data movement from SQL SERVER to ORACLE, we have used OPENQUERY. I am capturing openquery error handling the error through @@ERROR The issue is, when OPENQUERY thows ececption, the execution of my SP stops there only and it doesn't go to error handling section. Code: --Insert section INSERT INTO OPENQUERY(LNKSVRWUPOS, 'SELECT ID FROM TABLENAME') SELECT (SELECT * FROM OPENQUERY(LNKSVRWUPOS, 'SELECT ID_SEQ.nextval FROM DUAL')) AS ID --error handling section SET @ERROR = @@ERROR IF @ERROR <> 0 BEGIN ROLLBACK TRANSACTION INSERT INTO dtslog(FileName, FileTypeID, Message)VALUES(@FILENAME, 1, 'OPERATION failed form SQL SERVER TO ORACLE) RAISERROR('Error moving data from SQL SERVER TO ORACLE', 17, 1) RETURN END If the insert section fails, the control doesnt go to the error handling section. We need your guidance on the same at the earliest. Thanks, Rahul Jha Reply With Quote 05-01-10,09:42 #2 burkular View Profile View Forum Posts Visit Homepage Registered User Join Date Nov 2004 Location Canada Posts 58 It does not work when you are using sql2000. You are outside of SQL at that point and sql server can't interpret what is going on outside of the environment because it is not an sql error at that point. Using sql2005 is better because you can use the try and catch functions. But because
community Search Search Options Search Everything Search OpenEdge Development Member Options Share this RSS Thread Details 5 replies 1028 subscribers Postedover 6 years ago Related Tags .net 10.2 abl ABL highlighter ABLUnit appserver database eclipse openedge OpenEdge Development PDSOE progress REST webservice webspeed Overview Forum Wiki Documents User Help Ideas Blog  Forum Error Handling for Query-Open Posted by qcace on 19 Apr 2010 20:01 Hello,Is there a better way to return an OPEN-QUERY error ???Not sure is what I have below is the best way. This code is running on an app server.Without the IF error-status statement, if there is a problem with the vQuery it will continually post error log messages into our log file.With the IF error-status statement, it randomly returns an error even though there is no problem with the query.Thanks....CREATE QUERY hdlRadioQuery.hdlRadioQuery:SET-BUFFERS(BUFFER cl3gComm:HANDLE, BUFFER cl3gDevice:HANDLE).hdlRadioQuery:QUERY-PREPARE(vQuery).hdlRadioQuery:QUERY-OPEN.
IF ERROR-STATUS:ERROR THEN DO: RETURN ERROR 'Error opening query.'.END.... You have posted to a forum that requires a moderator to approve posts before they are publicly available. Posted by rbf on 19 Apr 2010 20:29 Append NO-ERROR to your statements. You need to do that anyway since ERROR-STATUS returns the status of your last NO-ERROR. Since you don't have any you are getting unexpected results in this code. In addition you have error conditions that are not caught.If you are on 10.1C+ you could also use structured error handling (CATCH). You have posted to a forum that requires a moderator to approve posts before they are publicly available. Posted by sarahm on 19 Apr 2010 20:29 ERROR-STATUS is only set for the last statement that was called with NO-ERROR. You don't have any NO-ERROR statements in your sample, so I imagine the the reason it appears random is because prior code has statements with NO-ERROR that may or may not be setting ERROR-STATUS:ERROR to true. A built-in method such as QUERY-OPEN will not set ERROR-STATUS:ERROR to true, but it will set messages, so