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

Question

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".

Answer 1

0

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.

answered Oct 20 '09 at 10:29 AM

Steve Jones - Editor ♦♦
5.1k ● 74 ● 78 ● 82

(comments are locked)

Answer 2

0

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?

answered Oct 20 '09 at 10:06 AM

Blackhawk-17
10.5k ● 23 ● 29 ● 34

What account is SQL running under? Is it a domain account or Local System?

Oct 20 '09 at 12:24 PM Blackhawk-17

Perhaps you can try setting up explicit Login mapping with sp_addlinkedsrvlogin.

Oct 20 '09 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 '09 at 12:48 PM Blackhawk-17

(comments are locked)

Answer 3

0

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

\\ldnroot

Rather than

\ldnroot

answered Oct 20 '09 at 10:28 AM

Melvyn Harbour 1 ♦♦
1.4k ● 17 ● 20 ● 22

The path does have \, not sure why when I've saved my edits above, it removed the first ''

Oct 20 '09 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 '09 at 10:58 AM Melvyn Harbour 1 ♦♦

Can't down vote as do not have the required 'Reputation' yet.

Oct 20 '09 at 11:03 AM Nick

Ah. You'll have to do some things on the site to build up some rep!

Oct 20 '09 at 11:41 AM Melvyn Harbour 1 ♦♦

(comments are locked)

Answer 4

0

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.

answered May 05 '12 at 01:55 PM

JD
104 ● 3 ● 6 ● 7

(comments are locked)

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

Follow this question

Related Questions