[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

TSQL to check if linked ODBC server is working

Hi Experts

I want a SQL function that returns a bit if an linked SQL server is running, my syntax is not working and is missing something, any help would be great
Create Function sfCheck_ODBC_Link
RETURNS bit 
AS
BEGIN
	DECLARE @IntODBC bit
	TRY
		select * FROM mylinkedserver...servertable
		SET @IntODBC = 1
		RETURN (@IntODBC)
	END TRY
	BEGIN CATCH
		SET @IntODBC = 0
		RETURN (@IntODBC)
	END CATCH

END

Open in new window

0
MrDavidThorn
Asked:
MrDavidThorn
  • 4
  • 3
1 Solution
 
MrDavidThornAuthor Commented:
Still developing now have the following code but still getting an error
Create Function sfCheck_ODBC_Link(@ODBCup bit)
RETURNS bit 
BEGIN
	DECLARE @IntODBC 
	TRY
		select * FROM RIMS_TRS...BUSINESS_ORGANISATION
		SET @IntODBC = 1
	END TRY

	BEGIN CATCH
		SET @IntODBC = 0
	END CATCH
	RETURN @IntODBC
END

Open in new window

0
 
JestersGrindCommented:
I think you are over thinking this.  You could something simpler like this.


Create Function sfCheck_ODBC_Link
RETURNS bit 
AS
BEGIN
	DECLARE @IntODBC bit
	IF EXISTS(select * FROM mylinkedserver...servertable)
		SET @IntODBC = 1
	ELSE
		SET @IntODBC = 0

	RETURN (@IntODBC)

END

Open in new window

0
 
MrDavidThornAuthor Commented:
still get an error

Msg 102, Level 15, State 1, Procedure sfCheck_ODBC_link, Line 2
Incorrect syntax near 'RETURNS'.
Msg 178, Level 15, State 1, Procedure sfCheck_ODBC_link, Line 10
A RETURN statement with a return value cannot be used in this context.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
JestersGrindCommented:
Sorry, parenthesis are required after the name even if no parameters are being passed.

Greg


Create Function sfCheck_ODBC_Link()
RETURNS bit 
AS
BEGIN
        DECLARE @IntODBC bit
        IF EXISTS(select * FROM mylinkedserver...servertable)
                SET @IntODBC = 1
        ELSE
                SET @IntODBC = 0

        RETURN (@IntODBC)

END

Open in new window

0
 
MrDavidThornAuthor Commented:
errr how do I actually use the function!

I have
select dbo.sfCheck_ODBC_link

and I get the error message
The multi-part identifier "dbo.sfCheck_ODBC_link" could not be bound.
0
 
JestersGrindCommented:
You're missing the parenthesis.  Try this SELECT dbo.sfCheck_ODBC_Link().

Greg

0
 
MrDavidThornAuthor Commented:
you the man
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now