Re: Difference between openrowset and opendatasource

From: TomTom (no_spam_at_nospamfordiscussion.com)
Date: 05/23/04 

Date: Sat, 22 May 2004 17:01:31 -0700

According to the documentation, OpenRowSet supports three-part name. The
missing one-part is the server name, which is specified as an argument of
OpenRowSet. I understand the different, but it looks very similar.

Thanks for your help!

"oj" <nospam_ojngo@home.com> wrote in message
news:ec8URoEQEHA.808@tk2msftngp13.phx.gbl...
> Opendatasource was implemented in sql2k to address some problems that
> openrowset has with Jet provider string.
>
> The major differences are listed here
>
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_12_02p1.asp?frame=true
>
> And here is a blurp that is missing from that link...
> Important For each instance of SQL Server 2000, members of the sysadmin
> fixed server role can enable or disable the use of ad hoc connector names
> for an OLE DB provider using the SQL Server DisallowAdhocAccess property.
> When ad hoc access is enabled, any user logged on to that instance can
> execute SQL statements containing ad hoc connector names referencing any
> data source on the network that can be accessed using that OLE DB
provider.
> To control access to data sources, members of the sysadmin role can
disable
> ad hoc access for that OLE DB provider, thereby limiting users to only
those
> data sources referenced by linked server names defined by the
> administrators. By default, ad hoc access is enabled for the SQL Server
OLE
> DB provider, and disabled for all other OLE DB providers.
>
> To sum up, openrowset() allows you to create an ad-hoc connection to oledb
> provider and execute a query.
> e.g.
> select * from openrowset('conn','query')
>
> Opendatasource() also allows you to create an ad-hoc connection but it
allows
> you to reference the provider in a 4-part-name.
> e.g.
> select * from opendatasource('conn').db.owner.obj
>
> --
> -oj
> http://www.rac4sql.net
>
>
> "TomTom" <no_spam@nospamfordiscussion.com> wrote in message
> news:uu7t5CEQEHA.3476@tk2msftngp13.phx.gbl...
> > It looks like OpenDataSource was introduced in SQL 2000, but I wonder
what's
> > the difference from OpenRowSet. Looking at the construct, they look
very
> > similar. BOL also does not explain the differences. What is the major
> > difference? Thanks!
> >
> >
>
>

Relevant Pages

  • adsi linked servers
    ... I'm following the directions for the OLE DB Directory ... I add the linked server with this TSQL ... from OLE DB provider 'ADSDSOObject'". ... If I execute an openquery command from query analyzer I ...
    (microsoft.public.sqlserver.security)
  • Re: Facing problem in accessing analysis service 2005 database from other machine
    ... You need to install the 9.0 OLE DB provider on the client machine. ... Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider ...
    (microsoft.public.sqlserver.olap)
  • Re: Difference between openrowset and opendatasource
    ... No. Openrowset() does not allow you to use 4-part-name. ... >> for an OLE DB provider using the SQL Server DisallowAdhocAccess property. ... >> data source on the network that can be accessed using that OLE DB> provider. ...
    (microsoft.public.sqlserver.programming)
  • Re: non-sa users get access denied in OLE DB query
    ... Ad hoc access to OLE DB provider 'IBMDADB2' has been denied. ... access this provider through a linked server. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: miising oracle odbc data provider
    ... available 64bit Microsoft OLE DB Provider. ... this provider will be available to a Service Pack 3 to its Windows ... Windows Server 2008. ...
    (microsoft.public.sqlserver.odbc)
Quantcast