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?
To add some more details if I try the following from my SQL Server Management Studio 2005 on my XP machine
I get 'Command(s) completed successfully.'
When I then
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 ♦♦
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?
Is it not as simple as just needing a double backslash at the start of the path, so:
answered Oct 20 '09 at 10:28 AM
Melvyn Harbour 1 ♦♦
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