Linked Server on SQL Server 2005 to Flat file stor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Linked Server on SQL Server 2005 to Flat file stor

Author

Topic

kontact00
Starting Member

3 Posts

Posted - 2009-10-20 : 09:23:01

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

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

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-20 : 11:57:26

try this UNC path
\\ldnroot\nick\prod_f~1\proces~1 . map a path to \\ldnroot then go to command window and go to this newl create map letter on your command window. drill down to nick then do dir /x. what does the short name of prod_files show as the same for processing. mayb that could be the problem, it does not see the long file name so you have to give it the short file name?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources