It provides ad hoc connection information as part of four – part object name without using a linked server name.
Syntax:
OPENDATASOURCE( provider_name, init_string )
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 following lists the most commonly used keywords in the init_string argument.
Keyword : Data Source
OLE DB property : DBPROP_INIT_DATASOURCE
Valid values and Description :
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.
Keyword : Location
OLE DB property : DBPROP_INIT_LOCATION
Valid values and Description : Location of the database to connect to.
Keyword : Extended Properties
OLE DB property : DBPROP_INIT_PROVIDERSTRING
Valid values and Description : The provider-specific connect-string.
Keyword : Connect timeout
OLE DB property : DBPROP_INIT_TIMEOUT
Valid values and Description : Time-out value after which the connection attempt fails.
Keyword : User ID
OLE DB property : DBPROP_AUTH_USERID
Valid values and Description : User ID to be used for the connection.
Keyword : Password
OLE DB property : DBPROP_AUTH_PASSWORD
Valid values and Description : Password to be used for the connection.
Keyword : Catalog
OLE DB property : DBPROP_INIT_CATALOG
Valid values and Description : The name of the initial or default catalog when connecting to the data source.
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
...S.VinothkumaR.
Microsoft Certified Technology Specialist (MCTS)
Microsoft Certified Professional Developer(MCPD)
About Me
- Vinothkumar S
- Chennai, Tamilnadu, India
- Hi..Viewers..I wish to all. This is Vinoth. This is where I try to condense everything that you need to know about me into a single page. I posted about me in my first post "Introduction".
2 comments:
Hello,
I may be asking silly question!
I am using SQL Server 2005 Standard Edition (SP2).
I ran query using OPENDATASOURCE(). I am using tab delimited text file as "Data Source". I got following SQL exception when I ran query with "ParthaMishra_1172_Sov_ITMSUpload_UnitedWay_NewEngland_2009Final.txt" file name.
------Exception-----
Exception in Thread com.microsoft.sqlserver.jdbc.SQLServerException: The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "ParthaMishra_1172_Sov_ITMSUpload_UnitedWay_NewEngland_2009Final#txt". The table either does not exist or the current user does not have permissions on that table.
---------------------
Interestingly, I ran the query again after changing name of the file to "PPM_ITMSUpload_1172.txt" and it worked.
So my question is: Is there anything with length of file name when using OPENDATASOURCE()?
The physical paths of the file are "E:\PMS\UploadedFiles\23055\2008\emp_CBD6042EEB20D1B68E1A51F194B024BD_1221156673057\ParthaMishra_1172_Sov_ITMSUpload_UnitedWay_NewEngland_2009Final.txt" and "E:\PMS\UploadedFiles\23055\2008\emp_CBD6042EEB20D1B68E1A51F194B024BD_1221157087084\PPM_ITMSUpload_1172.txt"
Please clarify me.
Thank you!
Hi Jigu888,
May I know your string.. Meant how you pass the opensource() as per his information.
Post a Comment