Re: Application role on connection

From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 02/25/03 

From: "Dan Guzman" <danguzman@nospam-earthlink.net>
Date: Tue, 25 Feb 2003 08:02:21 -0600

Happygirl, as I mentioned in my previous response, SQL Server can
execute only one SQL statement on a connection at a time. Because you
haven't read the recordset from the first query, ADO acquires a separate
SQL Server connection for the second query. The application role hasn't
been activated on that connection so you get the permissions error.

A simple technique to address your issue is to specify CursorLocation
adUseClient (instead of the default adUserServer) so that ADO will
immediately return the query results to your application. This will
free the connection (with the activated application role) for reuse.

Also, you might consider using OLE DB instead of ODBC. This allows you
to turn off connection pooling via the connection string so that it
doesn't interfere with your application role security. The example
below uses OLE DB with connection pooling disabled and client-side
cursors.

    Set Conn = New Adodb.Connection

    'OLE DB connection with connection pooling disabled
    Conn.ConnectionString = "Provider=SQLOLEDB.1" & _
        ";Data Source=" & gsSQL70Server & _
        ";User ID=" & gsSQL70UID & _
        ";Password=" & Decrypt(gsSQL70PWD) & _
        ";Initial Catalog=" & gsSQL70DATABASE & _
        ";OLE DB Services=-2"

    Conn.Open

    'immediate retreive query results to the client
    Conn.CursorLocation = adUseClient

    Conn.Execute "sp_setapprole 'xxAppRole', 'xxAppRole'"

    Dim strSQL As String
    Dim Rst As Adodb.Recordset
    Dim strSQL1 As String
    Dim Rst1 As Adodb.Recordset

    strSQL = "SELECT * from Test"
    Set Rst = Conn.Execute(strSQL)

    StrSQL1 = "SELECT * from Test";
    Set Rst1 = Conn.Execute(strSQL1) 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Happygirl" <happygirl5354@hotmail.com> wrote in message
news:0b1d01c2dc8d$2a488390$a401280a@phx.gbl...
> The following are my sample code, i can successfully
> execute strsql, but not strsql1, but however if i add one
> more line in between "strsql.close", then both statement
> can execute successfully, but since the program create
> many of this recordset, should you provide any proper and
> simple ways.. Thanks.
>
>     Set Conn = New Adodb.Connection
>     Conn.ConnectionString = "Driver={SQL Server};SERVER="
> & gsSQL70Server & ";UID=" &   gsSQL70UID & ";PWD=" &
> Decrypt(gsSQL70PWD) & "" & ";DATABASE=" & gsSQL70DATABASE
>     Conn.Open
>     Conn.Execute "sp_setapprole 'xxAppRole', 'xxAppRole'"
>
>     Dim strSQL As String
>     Dim Rst As Adodb.Recordset
>     Dim strSQL1 As String
>     Dim Rst1 As Adodb.Recordset
>
>     strSQL = "SELECT * from Test"
>     Set Rst = Conn.Execute(strSQL)
>
>     StrSQL1 = "SELECT * from Test";
>     Set Rst1 = Conn.Execute(strSQL1)
>
>

Relevant Pages

  • Re: connection to sql server 2005 problem
    ... If one is not careful when installing SQL Server Express, it can result in one or more of the SQL Server related services being shut down when it is not in use. ... Dim _con = New SqlConnection("Initial Catalog=lion;Data ... This illustrates retrying with a delay until either, a connection is made or 3 attempts have been made, whichever occurs first. ... Public Function login(ByVal un As String, ...
    (microsoft.public.dotnet.languages.vb)
  • DAO ODBC connetion to sql server with access logon
    ... I have an access front end, and I got one sql server tabel that I ... connect to using a linked connection using ODBC. ... Dim SQLws As DAO.workspace ...
    (microsoft.public.access.security)
  • RE: Excel VBA & ODBC Data Sources
    ... The following is a routine I use to extract data from a SQL Server database ... Dim cn As New ADODB.Connection ... Dim CombinedArrayAs String, FieldNamesAs String ... Here is my connection string in VBA: ...
    (microsoft.public.excel.programming)
  • Re: Saving Blob Data To File
    ... This is the sample code from msdn on how to save image to database. ... ' The SQL Server Image datatype is a binary datatype. ... Dim ms As New MemoryStream ... ' This only needs to be done the very first time a connection ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Update sql server table with dataset from xls file
    ... Create another adapter for the sQL Server table. ... > ' Create connection string variable for excel. ... > Dim objConn As New OleDbConnection ...
    (microsoft.public.dotnet.framework.adonet)
Quantcast