Post reply

Use DSN in SQLCLR

  • September 4, 2009 at 7:42 pm

    #214722

    I am trying to create a stored procedure using .NET in SQL 2005 (SQLCLR) to refer the customer table in a DSN (local) Database in another server.

    The following is the error:

    Msg 6522, Level 16, State 1, Procedure uspGetCustomerInformation, Line 0

    A .NET Framework error occurred during execution of user-defined routine or aggregate "uspGetCustomerInformation":

    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 uspCustomerInfo.StoredProcedures.uspGetCustomerInformation(String sCode)

    .

    I have set the DATABASE TRUSWORTHY level ON and running this query in SA mode while testing.

    I dunno how to set this permission exactly. If any one could share a piece of working code, i would be much grateful.

    Thank you

  • September 4, 2009 at 9:21 pm

    #1049658

    Questions:

    1) What permission set is your assembly installed with?

    2) Why are you using ODBC instead of ADO.NET?

    3) Why can't you use a linked server for this?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • September 4, 2009 at 11:38 pm

    #1049672

    Hi

    Questions:

    1) What permission set is your assembly installed with?

    I used EXTERNAL permission in the project. VS 2008

    2) Why are you using ODBC instead of ADO.NET?

    The reason is I am very new to this area. also the enternal database is Microsoft Dynamics NAV native DB which has a ODBC driver installed on the local machine.

    3) Why can't you use a linked server for this?

    Dunno much about this.

    Thank you

  • September 5, 2009 at 12:22 am

    #1049677

    A better couple of questions would be.... What does the CLR do and why do you believe you need a CLR to do it?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 5, 2009 at 12:35 am

    #1049680

    Okey, great.

    This is the scenario. I need to pull only selected information from a NAV Customer table. So, I bult a stored procedure in CLR to accept a customer code and connect to NAV Native DB through ODBC and pull the [Name], [Address], [otherfields] and pass to user as a table.

    My CLR code is as below

    Imports System

    Imports System.Data

    Imports System.Data.SqlClient

    Imports System.Data.SqlTypes

    Imports System.Data.Odbc.OdbcPermission

    Imports System.Data.Odbc

    Imports Microsoft.SqlServer.Server

    Partial Public Class StoredProcedures

    _

    Public Shared Sub uspGetCustomerInformation(ByVal sCode As String)

    Dim cn As New OdbcConnection("Data Source=NAV_DB")

    cn.Open()

    Dim cmd As New OdbcCommand("SELECT [No_],[Name],[Address 1].[Address 2],[Address 3] FROM Customer WHERE [No_] = '" & sCode & "'", cn)

    cmd.CommandType = CommandType.Text

    SqlContext.Pipe.Send(cmd.ExecuteReader(CommandBehavior.CloseConnection).ToString())

    cmd = Nothing

    cn.Close()

    End Sub

    End Class

    My idea is to run the SP like this

    EXECUTE [Database].[dbo].[uspGetCustomerInformation] 'CUST001'

    Hope this helps to understand the entire process i expected.

    Thank you again

  • September 5, 2009 at 5:34 pm

    #1049796

    meelan (9/4/2009)

    1) What permission set is your assembly installed with?

    I used EXTERNAL permission in the project. VS 2008

    You may need to use the UNSAFE permission set.

    2) Why are you using ODBC instead of ADO.NET?

    The reason is I am very new to this area. also the enternal database is Microsoft Dynamics NAV native DB which has a ODBC driver installed on the local machine.

    See if you can get it to work with ADO.NET. If you can you may be able to keep it at EXTERNAL_ACCESS.

    3) Why can't you use a linked server for this?

    Dunno much about this.

    Its OK, you probably cannot do it to an Navison DB anyway.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • September 5, 2009 at 7:55 pm

    #1049798

    meelan (9/5/2009)

    Okey, great.

    This is the scenario. I need to pull only selected information from a NAV Customer table. So, I bult a stored procedure in CLR to accept a customer code and connect to NAV Native DB through ODBC and pull the [Name], [Address], [otherfields] and pass to user as a table.

    If that's the case, why not just setup an ODBC (or, maybe, an OLEDB) provider on the server and create a linked server to the remote data?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 6, 2009 at 5:09 am

    #1049809

    There's nothing special about a Navision database (except maybe in the 'special needs' sense) so a linked server would be by far the better option. Trying to use .NET code to access a remote DSN to read a table is perverse.

    Linked servers are very easy to set up and work with (NAV uses a standard SQL Server database, in case that's not clear) - see the following link to a Books Online entry as a good starting point:

    http://msdn.microsoft.com/en-us/library/ms188279.aspx

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 6, 2009 at 7:35 am

    #1049848

    Thank you very much for your valuable comments and help.

    BY using the guidelines provided here and little bit of syntax correction (and small modification in NAV database), the code now works. But, it returns a MESSAGE which contains "System.Data.Odbc.OdbcDataReader" only.

    Anyway, I will learn more in this area while looking at the linked server option also.

    Tks again

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply