Advertisement

03.31.2008 at 01:29PM PDT, ID: 23283873
[x]
Attachment Details

Accessing ODBC DSN Data in CLR Procedure using C#/SQL Server

Asked by omar_barlas in MS SQL Server, C# Programming Language, SQL Server 2005

Tags: CLR Stored Procedures, ODBC DSN, C#, MS SQL Server, TSQL

I need to a Select Data from tables accessable via ODBC DSN and bring the result set/DataReader in SQL Server Via CLR Stored Procedure. So I wrote C# Program to create CLR Procdure for that.
As i built and Deploy the Procedure it deployees sucessfully but , in SQL Server Management Studio it gives System.Data.Odbc.OdbcPermission error while Executing procedure.
Code I am using is (is also working in independant C# application) is attahed

The  code Builds and Executes as C# Application but when used in C# Database Project in StoredProcedure Class it builds and deployes but on execution of Stored Procedure from SQL Server Managemen Studio it returns Error

A .NET Framework error occurred during execution of user-defined routine or aggregate "spTestProcsAB":
System.Security.SecurityException: Request for the permission of type 'System.Data.Odbc.OdbcPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
   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.Odbc.OdbcConnection.PermissionDemand()
   at System.Data.Odbc.OdbcConnectionFactory.PermissionDemand(DbConnection outerConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.Odbc.OdbcConnection.Open()
   at StoredProcedures.spTestProcsAB()

Sample Project files are also attached you might need to setup ODBC USer DSN linking to MS Access file and Set Connection string of Database ProjectStart Free Trial
1:
2:
3:
4:
5:
6:
string connStr = "dsn=HRMS.PR.udd";
OdbcCommand theCommand = new OdbcCommand("Select * FROM HRLABSTD", new OdbcConnection(connStr));
theCommand.CommandType = CommandType.Text;
theCommand.Connection.Open();
SqlContext.Pipe.Send(theCommand.ExecuteReader(CommandBehavior.CloseConnection).ToString());
theCommand.Connection.Close();
[+][-]04.01.2008 at 05:13AM PDT, ID: 21253101

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.03.2008 at 01:33PM PDT, ID: 21276682

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.04.2008 at 01:28PM PDT, ID: 21285418

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.10.2008 at 10:29PM PDT, ID: 21331512

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.11.2008 at 12:16AM PDT, ID: 21331896

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.12.2008 at 10:22AM PDT, ID: 21341845

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]05.19.2008 at 08:49PM PDT, ID: 21603435

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: MS SQL Server, C# Programming Language, SQL Server 2005
Tags: CLR Stored Procedures, ODBC DSN, C#, MS SQL Server, TSQL
Sign Up Now!
Solution Provided By: omar_barlas
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_EXPERT_20070906