How to check if a linked server exist on an usb drive?

[stevelear]

I have seen numerous answer to the question "how to check if a linked server exist", but my problem is a bit different.
In my application I'm certain that the linked server exist, but this server is an access mdb file stored on a pen drive.
The customer is free to disconnect the pen drive to keep is data secure but my stored procedures fail with an oledb error message if the file is not there!
Anyone can find a way to check the existence of the mdb file?

Submitted via EggHeadCafe - Software Developer Portal of Choice
Integrate Membership, Roles and Profile Providers into your ASP.NET 2.0 App
http://www.eggheadcafe.com/tutorials/aspnet/1a040e19-0301-403a-81d2-b4d1a17b879a/integrate-membership-rol.aspx

[William Vaughn (MVP)]

I might try to create a CLR executable to test for existence of the file.
The CLR function would have to run with full rights for this to work. I
describe how to build CLR functions in the 7th Edition.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

"Steve Lear" wrote in message news:201011210...@email.it...

[Erland Sommarskog]

(Steve Lear) writes:
> I have seen numerous answer to the question "how to check if a linked
> server exist", but my problem is a bit different. In my application I'm
> certain that the linked server exist, but this server is an access mdb
> file stored on a pen drive. The customer is free to disconnect the pen
> drive to keep is data secure but my stored procedures fail with an oledb
> error message if the file is not there!
> Anyone can find a way to check the existence of the mdb file?

If you are on SQL 2005, have you tried sp_testlinkedserver?

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[stevelear]

Thank you Mr Vaughn and Mr Sommarskog and excuse me for my absence in these days. However I can't use your solutions because I need to use this check also in SQLServer 2000.

I have reached a compromise with this stored procedure

CREATE PROCEDURE [LinkedServerExist]
AS
BEGIN
DECLARE @optLinkedServer nvarchar(255)
DECLARE @cmd nvarchar(300)
DECLARE @cnt int

-- Read the linked server name from an internal options table
-- It's necessary on the production server because there we have too many linked server
-- SELECT @optLinkedServer = Convert(nvarchar, valore) from GEST_tabSystem where codice = 9950
-- if @optLinkedServer IS NULL or @optLinkedServer = '' return 0

-- In test we can fix the name like this...
select @optLinkedServer = 'H:\SECURE\working.mdb'

DECLARE @tabLinkedServer TABLE
(
nomeFile nvarchar(1024)
)

-- Prepare the dir command to look for the linked server filename
SELECT @cmd = 'dir ' + @optLinkedServer + ' /b'
-- Run the command and capture its output in var TABLE
INSERT @tabLinkedServer EXEC master..xp_cmdshell @cmd
-- Remove that null line
DELETE FROM @tabLinkedServer WHERE nomeFile IS NULL
-- Find out if dir has found the filename
SELECT @cnt = COUNT(*) FROM @tabLinkedServer WHERE nomeFile like right(@optLinkedServer, len(nomeFile));
return @cnt
END

thanks again
Steve Lear

Erland Sommarskog wrote:

(Steve Lear) writes:If you are on SQL 2005, have you tried sp_testlinkedserver?
12-Jan-10

(Steve Lear) writes:

If you are on SQL 2005, have you tried sp_testlinkedserver?

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Previous Posts In This Thread:

Submitted via EggHeadCafe - Software Developer Portal of Choice

SQL Server - Detach / Attach / Full Text Catalog
http://www.eggheadcafe.com/tutorials/aspnet/241bf00d-8e75-4f70-8676-881f3eefed9b/sql-server--detach--att.aspx

[stevelear]

Oh, well, for SQLServer 2000 I need to change the TABLE declare with a create table #tabLinkedServer and change the subsequent lines where @tabLinkedServer is used

Steve Lear wrote:

How to check if a linked server exist on an usb drive?
18-Jan-10

thanks again
Steve Lear

Previous Posts In This Thread:

Submitted via EggHeadCafe - Software Developer Portal of Choice

Store Recordsets in Application State Dictionary
http://www.eggheadcafe.com/tutorials/aspnet/f80a0cf2-1524-4eca-a5d5-ab162f67e2b2/store-recordsets-in-appli.aspx