x

Linked Server on SQL Server 2005 to Flat file stored on Network Drive

Hi,

My Environment setup is as follows:

Running SQL Server Management Studio 2005 on XP Connecting to SQL Server 2005 on Windows Server 2003 SP2 Flat Files are located on a network drive which the Windows Server has access via Fully Qualified Path

I have no problem creating Linked Server if I RDC onto the Windows Server, but

How do I setup a Linked Server for the Windows Server 2003/SQL Server 2005 to connect to the Flat file on the Network Drive using my XP machine?

Thanks

Nick

To add some more details if I try the following from my SQL Server Management Studio 2005 on my XP machine

EXEC sp_addlinkedserver EXT_TV_DATA, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'\\ldnroot\nick\prod_files\processing\', NULL, 'Text'

I get 'Command(s) completed successfully.'

When I then

EXEC sp_tables_ex EXT_TV_DATA

I get

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXT_TV_DATA" returned message "'\ldnroot\nick\prod_files\processing' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.". Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXT_TV_DATA".

more ▼

asked Oct 20, 2009 at 09:56 AM in Default

Nick gravatar image

Nick
21 1 1 1

I have verified that I can access the network path when I log onto the Server and in Run execute the path.
Oct 20, 2009 at 10:45 AM Nick
Ah, but although you may have access, does the user that SQL Server is running under have access?
Oct 20, 2009 at 10:56 AM Melvyn Harbour 1 ♦♦
I log in with my Windows credentials which are the same for both my XP account and Server 2003 account. SQL Server is configured to use Windows Authentication.
Oct 20, 2009 at 11:01 AM Nick
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

you could log onto XP as the account running SQL Server and set up the drive mapping, but I wouldn't recommend that. My guess is that you have the pathing wrong. Type the path you need into the explorer window and be sure it's valid. If it is, then you put that in your linked server, but the SQL Server database engine service account needs to have rights to get to that path.

more ▼

answered Oct 20, 2009 at 10:29 AM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

(comments are locked)
10|1200 characters needed characters left

I'm not totally clear on what you are asking but I would guess that you need to use the form \\SERVER\Share\File

Rather than trying to utilize a drive letter path such as F:\FileName

Chances are that the account SQL is running under will not have drive mappings assigned and you will have to explicitly define the network path.

From the server can you map the network drive as the account SQL runs under as opposed to yours?

more ▼

answered Oct 20, 2009 at 10:06 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

What account is SQL running under? Is it a domain account or Local System?
Oct 20, 2009 at 12:24 PM Blackhawk-17
Perhaps you can try setting up explicit Login mapping with sp_addlinkedsrvlogin.
Oct 20, 2009 at 12:34 PM Blackhawk-17

As another troubleshooting exercise you can try accessing the files via an OPENDATASOURCE query.

Has the SQL account registered an SPN? Is it trusted for delegation?
Oct 20, 2009 at 12:48 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

Is it not as simple as just needing a double backslash at the start of the path, so:

\\ldnroot

Rather than

\ldnroot 
more ▼

answered Oct 20, 2009 at 10:28 AM

Melvyn Harbour 1 gravatar image

Melvyn Harbour 1 ♦♦
1.4k 18 20 22

The path does have \, not sure why when I've saved my edits above, it removed the first ''
Oct 20, 2009 at 10:42 AM Nick
Escaping. I've fixed it now. I suggest you downvote my answer, as it wasn't actually helpful!
Oct 20, 2009 at 10:58 AM Melvyn Harbour 1 ♦♦
Can't down vote as do not have the required 'Reputation' yet.
Oct 20, 2009 at 11:03 AM Nick
Ah. You'll have to do some things on the site to build up some rep!
Oct 20, 2009 at 11:41 AM Melvyn Harbour 1 ♦♦
(comments are locked)
10|1200 characters needed characters left

Hi,

You must check if the account under SQL Server is running have access to the path, check in Security Properties after you create the linked server and specify another account to access the resource.
more ▼

answered May 05, 2012 at 01:55 PM

JD gravatar image

JD
104 6 7 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1943
x106

asked: Oct 20, 2009 at 09:56 AM

Seen: 3336 times

Last Updated: May 05, 2012 at 01:55 PM