I need your HELP! I having problem with link to a text file and refer to the contents by using a dot '.' instead of '#' in the file name. Everthing work fine if I use the '#' for extension seperator. The problem is the existing stored procedures require the text file to contain a dot instead of a '#'.

We have 4 servers and 2 were working fine with the text files containing a dot '.', but the other two will always failed doesn't matter if I rebuild the whole server from scratch!

There must be some server setting that allow internal conversion of '#' to a dot '.' or similar. What is that setting?

Has anyone been through what I have prbolem with? Below are the actual codes that describe the prblem:


EXEC sp_droplinkedsrvlogin @rmtsrvname = 'TEST3', -- name of a linked server that the login mapping applies to
            @locallogin = NULL -- login on the local server
EXEC sp_DropServer @server = 'TEST3'


EXEC sp_addlinkedserver @server = 'TEST3', -- name of the linked server to create
                    @srvproduct = 'JET', -- product name of the OLE DB data source to add as a linked server
                    @provider = 'Microsoft.Jet.OLEDB.4.0', -- unique programmatic identifier (PROGID) of the OLE DB provider corresponding to this data source, ie 'Microsoft.Jet.OLEDB.4.0'
                        @datasrc = '\\Jekyll\CWPImport\PPDirect\AdelaideBank\TD', -- name of the data source as interpreted by the OLE DB provider, ie 'C:\Data'
-- @location = @location, -- location of the database as interpreted by the OLE DB provider
                    @provstr = 'Text' -- OLE DB provider-specific connection string that identifies a unique data source, ie 'Text'


/* create a mapping between logins on the local server running Microsoftc SQL Server and
remote logins on the linked server */
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'TEST3', -- name of a linked server that the login mapping applies to
                @useself = FALSE, -- name of the login used to connect to the remote server
                @locallogin = NULL, -- login on the local server
                @rmtuser = NULL, -- username used to connect to rmtsrvname when useself is false
                @rmtpassword = NULL -- password associated with rmtuser

sp_helpserver 'TEST3'

------------------------------------------------------
SELECT * FROM [TEST3]...[Trans#csv]
It's return all rows fine.

-----------------------------------------------------
SELECT * FROM [TEST3]...[Trans.csv] (But this script work fine on the other server)

Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'TEST3' does not contain table 'Trans.csv'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='TEST3', TableName='Trans.csv'].

Please Help.

Best Regards
Tim

Hi, I´m having the same problem. Did you solve it? can you tell me what to do. Thanks

Alberto

Ummm... the linked server requires # for the extension symbol and I'm pretty sure you cannot change that. If you have a list of file names with the dot symbol, just do a REPLACE before you use it with the linked server. This will probably require a bit of dynamic SQL because you can't use a variable in the FROM clause.

--Jeff Moden