CLR Stored procedure to access OleDb datasource, how?

I tried to write a CLR stored procedure using C# in SQL 2005 to access an
Access
database.
When I use the OleDbConnection class in System.Data, the procedure throws
SecurityException at runtime.
Output as following:
System.Security.SecurityException: Request for the permission of type
'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand,
StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.OleDb.OleDbConnection.PermissionDemand()
at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection
outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Dbbest.Data.BulkStuff.bulkcopy(String source_oledb_connection_string,
String source_table, String destination_table, Int32 batchSize, Int32
notifyAfter)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.OleDb.OleDbPermission
The Zone of the assembly that failed was:
MyComputerWhen you created the assembly did you set the permission set? If not,
then it is SAFE by default and you will not be able ot get to your
access database. You MAY have to go all the way to UNSAFE to get
there, I have not checked.
CREATE ASSEMBLY SQLCLRTest
FROM 'C:\MyDBApp\SQLCLRTest.dll'
WITH PERMISSION_SET = SAFE
SAFE, EXTERNAL_ACCESS, UNSAFE are the options that you have for this.
Steve

client-side SQLXML

I assume from http://technet.microsoft.com/en-us/...y/ms171948.aspx
that it is possible to have the server-side provider be an OLEDB provider
for Oracle
for instance. But I have failed to find any code samples where SQLXML is
used
client-side to query Oracle or any other RDBMS. Does anyone have an example
of this?
Thanks,
Chris
Chris Harrington
Active Interface, LLC.
http://www.activeinterface.comThe client side SQLXML is only built into the SQL Server OLEDB provider so
it won't work against anything except SQL Server.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:erR04RBwHHA.736@.TK2MSFTNGP06.phx.gbl...
>I assume from http://technet.microsoft.com/en-us/...y/ms171948.aspx
> that it is possible to have the server-side provider be an OLEDB provider
> for Oracle
> for instance. But I have failed to find any code samples where SQLXML is
> used
> client-side to query Oracle or any other RDBMS. Does anyone have an
> example of this?
> Thanks,
> Chris
>
> Chris Harrington
> Active Interface, LLC.
> http://www.activeinterface.com
>|||But the diagram clearly shows SQLXML being used with "other dbms" systems -
did MSFT make an untrue statement with that diagram?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:D40B1CAA-9270-4E94-A151-E14027811B8E@.microsoft.com...
> The client side SQLXML is only built into the SQL Server OLEDB provider so
> it won't work against anything except SQL Server.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
> news:erR04RBwHHA.736@.TK2MSFTNGP06.phx.gbl...
>|||Sorry, my bad. We used to block that. I assume you just have to specify
the right OLEDB connection string for your provided. Other than that there
shouldn't be any difference.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:u6MdKJewHHA.4916@.TK2MSFTNGP04.phx.gbl...
> But the diagram clearly shows SQLXML being used with "other dbms"
> systems - did MSFT make an untrue statement with that diagram?
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:D40B1CAA-9270-4E94-A151-E14027811B8E@.microsoft.com...
>

client-side SQLXML

I assume from http://technet.microsoft.com/en-us/library/ms171948.aspx
that it is possible to have the server-side provider be an OLEDB provider
for Oracle
for instance. But I have failed to find any code samples where SQLXML is
used
client-side to query Oracle or any other RDBMS. Does anyone have an example
of this?
Thanks,
Chris
Chris Harrington
Active Interface, LLC.
http://www.activeinterface.com
The client side SQLXML is only built into the SQL Server OLEDB provider so
it won't work against anything except SQL Server.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:erR04RBwHHA.736@.TK2MSFTNGP06.phx.gbl...
>I assume from http://technet.microsoft.com/en-us/library/ms171948.aspx
> that it is possible to have the server-side provider be an OLEDB provider
> for Oracle
> for instance. But I have failed to find any code samples where SQLXML is
> used
> client-side to query Oracle or any other RDBMS. Does anyone have an
> example of this?
> Thanks,
> Chris
>
> Chris Harrington
> Active Interface, LLC.
> http://www.activeinterface.com
>
|||But the diagram clearly shows SQLXML being used with "other dbms" systems -
did MSFT make an untrue statement with that diagram?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:D40B1CAA-9270-4E94-A151-E14027811B8E@.microsoft.com...
> The client side SQLXML is only built into the SQL Server OLEDB provider so
> it won't work against anything except SQL Server.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
> news:erR04RBwHHA.736@.TK2MSFTNGP06.phx.gbl...
>
|||Sorry, my bad. We used to block that. I assume you just have to specify
the right OLEDB connection string for your provided. Other than that there
shouldn't be any difference.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"ChrisHarrington" <charrington-at-activeinterface.com> wrote in message
news:u6MdKJewHHA.4916@.TK2MSFTNGP04.phx.gbl...
> But the diagram clearly shows SQLXML being used with "other dbms"
> systems - did MSFT make an untrue statement with that diagram?
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:D40B1CAA-9270-4E94-A151-E14027811B8E@.microsoft.com...
>

Client Access

Hi Experts,
I have an custom-built client/server application which
uses SQL Server 2000 as its database server. I'm using
OLEDB as the data access mechanism to access the database.
Instead of installing SQL Server clients on my application
client PCs, is there a way to enable my application
clients to access SQL Server DB?
Anything like installing MDAC / DLL alone?
TIA,
HariHi
MDAC will give you everything you need to connect to your server and on most
windows PCs it should already be installed, you just need to make sure it is
version supported by your application.
John
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:294b01c48e71$958de6d0$a301280a@.phx.gbl...
> Hi Experts,
> I have an custom-built client/server application which
> uses SQL Server 2000 as its database server. I'm using
> OLEDB as the data access mechanism to access the database.
> Instead of installing SQL Server clients on my application
> client PCs, is there a way to enable my application
> clients to access SQL Server DB?
> Anything like installing MDAC / DLL alone?
> TIA,
> Hari|||Just install MDAC, which includes OLEDB. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:294b01c48e71$958de6d0$a301280a@.phx.gbl...
> Hi Experts,
> I have an custom-built client/server application which
> uses SQL Server 2000 as its database server. I'm using
> OLEDB as the data access mechanism to access the database.
> Instead of installing SQL Server clients on my application
> client PCs, is there a way to enable my application
> clients to access SQL Server DB?
> Anything like installing MDAC / DLL alone?
> TIA,
> Hari