Error Handling: TRY CATCH 1

[kjv1611]

I must be missing something, so figure I'll ask here. I thought the basic premise of a TRY/CATCH block is the TRY is the first event, and if it errors out, instead of just erroring out, you can then do the CATCH piece. But I am going nowhere with getting anything useful from that setup.

Here's what I'm trying to do:

BEGIN TRY
   SELECT TOP 1 1 ,'LongName' FROM CrossServer01.[MYDOMAIN\myusername].MyTable
END TRY
BEGIN CATCH
	SELECT TOP 1 1 ,'ShortName' FROM CrossServer01.myusername.MyTable
END CATCH

I tried it without the cross server portion by using the EXEC command as well, and that gets same results.

Am I missing something? is there a keyword I need to use to get this to work? Or do I need to go another route altogether?

The issue is that the schema name will work differently on different servers, at least with the particular server we're linking to in this instance. So my end goal is that I want to be able to figure out what the schema is, or at least be able to send the SELECT query to the correct schema when the table runs.

Thanks in advance for any suggestions.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[gmmastros]

I would suggest that you use a synonym for this.

https://msdn.microsoft.com/en-us/library/ms187552.aspx

The benefit of synonyms is that you can configure it just once and then reuse it in many different queries.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kjv1611]

Sorry, just now looking at this. Yes it does look like that's the ideal way to go. If time permits, I'll try to dig more into this piece of the project, and get it updated this week or next at latest. Thanks for the suggestion!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Alright, while waiting on a separate script to run, I looked a little more at the use of synonyms, and that got me to thinking about how I could simply pull in the correct schema to a variable (what I was trying to do all along), so I could be sure to use the correct schema.

This is what I came up with, much shorter than what I saw online:

DECLARE @Schema as varchar(100) = (
	SELECT	s.name
	FROM	LinkedServer.Sandbox.sys.schemas s WITH (NOLOCK)
	WHERE	REPLACE(s.name,'CORPDOMAIN\','') = REPLACE(USER,'CORPDOMAIN\','')
)

SELECT @Schema

And that seems to work perfectly so far. I'll be sure to test it with some other folks.

I do have one issue with it so far, but I don't think it'll hurt my current usage. It just drives me bonkers. In trying to setup a method for testing, I wanted to grab one table name from that schema, and use it to select the top record in that table. I'm able to select the table name if I setup a dynamic SQL varchar variable, and use EXEC, but if I try to do a normal SQL SELECT where I feed the @Schema variable to the WHERE clause as a string/varchar value, it comes back with no results.

Can anyone point me in the right direction? What am I missing?

DECLARE @Schema as varchar(100) = (
	SELECT	s.name
	FROM	LinkedServer.Sandbox.sys.schemas s WITH (NOLOCK)
	WHERE	REPLACE(s.name,'CORPDOMAIN\','') = REPLACE(USER,'CORPDOMAIN\','')
)
DECLARE @Table AS varchar(100)
DECLARE @SQL AS varchar(max)

SELECT @Schema

SET @SQL = 'SELECT TOP 1 t.name AS TableName
		FROM	LinkedServer.Sandbox.sys.tables t WITH (NOLOCK)
		JOIN	LinkedServer.Sandbox.sys.schemas s WITH (NOLOCK) ON t.schema_id = s.schema_id
		WHERE	s.name = ' + CHAR(39) + @Schema + CHAR(39) + '
			AND	t.name IS NOT NULL'

EXEC (@SQL)

SET @Table = (
		SELECT TOP 1 t.name
		FROM	LinkedServer.Sandbox.sys.tables t WITH (NOLOCK)
		JOIN	LinkedServer.Sandbox.sys.schemas s WITH (NOLOCK) ON t.schema_id = s.schema_id
		WHERE	s.name = CHAR(39) + @Schema + CHAR(39)
)

SELECT @Table

Thanks for any help on this one.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57