Re: Difference between openrowset and opendatasource

From: oj (nospam_ojngo_at_home.com)
Date: 05/23/04 

Date: Sat, 22 May 2004 17:46:13 -0700

No. Openrowset() does not allow you to use 4-part-name.
e.g.
--not valid
select * from openrowset('conn').db.dbo.obj 

-- 
-oj
http://www.rac4sql.net
"TomTom" <no_spam@nospamfordiscussion.com> wrote in message
news:%23J0LekFQEHA.3456@TK2MSFTNGP11.phx.gbl...
> 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