October 25, 2007 at 4:43 am
Hi,
I'm using a OPENDATASOURCE to select from a standard delimited text file. This has been working fine for days, but now it crashed as the file name contains a fullstop which seems to be confusing the OPENDATASOURCE/OLEDB driver.
The file name in question is jdm.001_aff.csv, so my dynamic SQL command is generated as follows:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=G:\;Properties="Text;HDR=YES;FMT=Delimited"')...jdm.001_aff#csv
I've tried enclosing the file name with [ ] but this doesn't make any difference.
E.g [jdm.001_aff#csv] and [jdm.001_aff]#[csv]
Can I use long file names with fullstops? Can anyone help?
Thanks!
October 25, 2007 at 4:55 am
Try out this!!!!!!!!!!!!
================
OPENDATASOURCE
Provides ad hoc connection information as part of a four-part object name without using a linked server name.
Syntax
OPENDATASOURCE ( provider_name, init_string )
Arguments
provider_name
Is the name registered as the PROGID of the OLE DB provider used to access the data source. provider_name is a char data type, with no default value.
init_string
Is the connection string passed to the IDataInitialize interface of the destination provider. The provider string syntax is based on keyword-value pairs separated by semicolons, that is, "keyword1=value; keyword2=value."
The basic syntax is defined in the Microsoft® Data Access SDK. Refer to the documentation on the provider for specific keyword-value pairs supported. This table lists the most commonly used keywords in the init_string argument.
Keyword OLE DB property Valid values and Description
Data Source DBPROP_INIT_DATASOURCE Name of the data source to connect to. Different providers interpret this in different ways. For SQL Server OLE DB provider, this indicates the name of the server. For Jet OLE DB provider, this indicates the full path of the .mdb file or .xls file.
Location DBPROP_INIT_LOCATION Location of the database to connect to.
Extended Properties DBPROP_INIT_PROVIDERSTRING The provider-specific connect-string.
Connect timeout DBPROP_INIT_TIMEOUT Time-out value after which the connection attempt fails.
User ID DBPROP_AUTH_USERID User ID to be used for the connection.
Password DBPROP_AUTH_PASSWORD Password to be used for the connection.
Catalog DBPROP_INIT_CATALOG The name of the initial or default catalog when connecting to the data source.
Remarks
The OPENDATASOURCE function can be used in the same Transact-SQL syntax locations as a linked server name. Thus, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or view name in a SELECT, INSERT, UPDATE, or DELETE statement, or to a remote stored procedure in an EXECUTE statement. When executing remote stored procedures, OPENDATASOURCE should refer to another SQL Server. OPENDATASOURCE does not accept variables for its arguments.
Like the OPENROWSET function, OPENDATASOURCE should only reference OLE DB data sources accessed infrequently. Define a linked server for any data sources accessed more than a few times. Neither OPENDATASOURCE, nor OPENROWSET provide all the functionality of linked server definitions, such as security management and the ability to query catalog information. All connection information, including passwords, must be provided each time OPENDATASOURCE is called.
Examples
This example accesses data from a table on another instance of SQL Server.
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
This is an example of a query against an Excel spreadsheet through the OLE DB provider for Jet.
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
October 25, 2007 at 5:04 am
Thanks, but this doesn't really help. The OPENDATASOURCE was working, so that isn't the problem. The problem is a file name which contains a fullstop.
October 25, 2007 at 5:15 am
Have you tried this one?
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=G:\;Properties="Text;HDR=YES;FMT=Delimited"')...[jdm.001_aff.csv]
--Ramesh
October 25, 2007 at 5:22 am
Nope - tried that, but thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply