CLR Stored procedure to access Ole 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:
MyComputer

Hello Va1era,

The exception you encountered indicates that the C# assembly containing the stored procedure that you registered has insufficient permissions to create an OleDbConnection object, with respect to the .Net framework Code Access Security (CAS) policies. This probably means that your assembly was registered WITH PERMISSION_SET = SAFE, which is also the default if PERMISSION_SET is unspecified in the CREATE ASSEMBLY DDL statement.

To fix this, you can re-register your assembly WITH PERMISSION_SET = EXTERNAL_ACCESS to gain access to the ADO.Net classes (including OleDbConnection). If you are using Visual Studio, you will have to make this change from SAFE to EXTERNAL_ACCESS in the properties page of your C# database project.

To understand more about the CAS security permission buckets in SQL Server's hosted CLR (SAFE, EXTERNAL_ACCESS and UNSAFE), you can look at this document: http://bordecal.mvps.org/Nicole/SqlClrCas/SqlClrCasSpeculations.htm. Also, here is the MSDN page on CAS and ADO.Net 2.0: http://msdn2.microsoft.com/en-us/library/0x4t63kb.aspx

Hope this helps.

Thanks,
Ravi

Client timeout expired, settings on 10hrs on server, how change cl

A client has to insert many files with bulk insert. I start it using windows
scripting host with VB Script and ole db provider for SQL Server. works good
and fast. Only really big files will take several minutes to terminate.
How can I set the client timeout?
I'm using windows server2003 SP1, SQL Server2000 SP4, MDAC 2.8 (SP1 cannot
be installed on windows 2003 server) all on same server. Because scripts are
interacting with sql-server (I'm looking foreward to sql server 2005!!!)
I have seen, some have the same problem, but I have not seen solutions.
Do I have to use ODBC instead of OLE DE?
Thanks for any help!
See if this helps:
http://www.aspfaq.com/show.asp?id=2066
Andrew J. Kelly SQL MVP
"Urban" <urban@.nospamplease> wrote in message
news:AA015201-3F8A-465D-9A45-317870105454@.microsoft.com...
>A client has to insert many files with bulk insert. I start it using
>windows
> scripting host with VB Script and ole db provider for SQL Server. works
> good
> and fast. Only really big files will take several minutes to terminate.
> How can I set the client timeout?
> I'm using windows server2003 SP1, SQL Server2000 SP4, MDAC 2.8 (SP1 cannot
> be installed on windows 2003 server) all on same server. Because scripts
> are
> interacting with sql-server (I'm looking foreward to sql server 2005!!!)
> I have seen, some have the same problem, but I have not seen solutions.
> Do I have to use ODBC instead of OLE DE?
> Thanks for any help!

Client connection error while trying to create maintenane plans

Error: Ole db error 0x80004005 Client unable to establish connection

This happens when trying to create a maintenance plan in sql 2005 sp 1

It has now happend with 3 installs - one fresh install and two upgrades from 2000 to 2005. The startup account for sql server is a domain user account and has not been changed. This happens on both windows 2000 and windows 2003.

This however does not happen if the sql startup account is in the local admin group for the machine

Any help is greatly appreciated

Thanks

KR

Refer this link , i hope it will solve u r problem.

http://support.microsoft.com/kb/253500/

also upgrade to SP2 , there are lot of enhancement done Maintenance plan wizards in SP2

Refer : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1266171&SiteID=1

Madhu

|||

This happens when the client is on the same machine, so the first link would probably not apply in this case as it deals with remote connections.

Thanks

KR