Professional Web Applications Themes

problem while connecting to sqlserver using application role with asp - Microsoft SQL / MS SQL Server

<% set conn=server.CreateObject ("ADODB.Connection") conn.Open Application("ConnStr") '此连接为guest权限 conn.Open Application("AdvConn_ConnectionString") set cmd=server.createObject("ADODB.Command") with cmd .ActiveConnection =conn .CommandText ="sp_setAppRole" .CommandType =adCmdStoredProc end with set param0=cmd.CreateParameter ("RETURN_VALUE",adInteger ,adParamReturnValue ) set param1=cmd.CreateParameter("rolename",adVarChar ,adParamInput ,50,Application("RuntimeAppRoleName")) set param2=cmd.CreateParameter("password",adVarChar ,adParamInput ,50,Application("RuntimeAppRolePassword")) cmd.Parameters.append param0 cmd.Parameters.append param1 cmd.Parameters.append param2 cmd.execute ret=cmd.Parameters("RETURN_VALUE") if ret=1 then response.write "Connect to database failure,contact the administrator." response.flush response.end end if '----sqlstatements conn.close set conn=nothing %>...

  1. #1

    Default problem while connecting to sqlserver using application role with asp

    <%
    set conn=server.CreateObject ("ADODB.Connection")
    conn.Open Application("ConnStr") '此连接为guest权限
    conn.Open Application("AdvConn_ConnectionString")

    set cmd=server.createObject("ADODB.Command")
    with cmd
    .ActiveConnection =conn
    .CommandText ="sp_setAppRole"
    .CommandType =adCmdStoredProc
    end with
    set param0=cmd.CreateParameter ("RETURN_VALUE",adInteger
    ,adParamReturnValue )
    set param1=cmd.CreateParameter("rolename",adVarChar ,adParamInput
    ,50,Application("RuntimeAppRoleName"))
    set param2=cmd.CreateParameter("password",adVarChar ,adParamInput
    ,50,Application("RuntimeAppRolePassword"))
    cmd.Parameters.append param0
    cmd.Parameters.append param1
    cmd.Parameters.append param2
    cmd.execute
    ret=cmd.Parameters("RETURN_VALUE")
    if ret=1 then
    response.write "Connect to database failure,contact the administrator."
    response.flush
    response.end
    end if

    '----sqlstatements

    conn.close

    set conn=nothing
    %>


    Tommy Guest

  2. #2

    Default Re: problem while connecting to sqlserver using application role with asp

    Tommy (com) writes: 

    Now that was a nasty error message! I was expecting to see something
    like "you cannot exec sp_setapprole twice". This looks like something
    is crashing on the SQL Server side, and then it goes downhill from there.

    Anyway, you cannot call sp_setapprole more than once for the same
    connection. Since ADO uses connection pooling, and clings to a connection
    after you have closed it for some 60 seconds, these accidents can happen.

    The remedy is to include "OLE DB Services = -2" in the connection string
    to turn off connection pooling.


    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  3. #3

    Default Re: problem while connecting to sqlserver using application role with asp

    "Anyway, you cannot call sp_setapprole more than once for the same
    connection. Since ADO uses connection pooling, and clings to a connection
    after you have closed it for some 60 seconds, these accidents can happen.
    "

    Thanks for ur help. The connection structure I implemented in the
    development is include a initConnection.inc file which initialize a
    connection object and close it in the specific file which using the
    connection. I am wondering how I use it for two times within each
    connection.
    So from my understanding, the problem is like belows:
    the connection pool pools the connection and thus within each connection,
    there happens sp_setapprole executed multiple times, is that right?
    With turned off the connection pooling, the problem may be solved. But I am
    afraid that it will finally greatly damage the efficiency of the database
    operations.

    "Erland Sommarskog" <se> 写入邮件
    news:0.0.1... 
    >
    > Now that was a nasty error message! I was expecting to see something
    > like "you cannot exec sp_setapprole twice". This looks like something
    > is crashing on the SQL Server side, and then it goes downhill from there.
    >
    > Anyway, you cannot call sp_setapprole more than once for the same
    > connection. Since ADO uses connection pooling, and clings to a connection
    > after you have closed it for some 60 seconds, these accidents can happen.
    >
    > The remedy is to include "OLE DB Services = -2" in the connection string
    > to turn off connection pooling.
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, se
    >
    > Books Online for SQL Server SP3 at
    > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/ref]


    Tommy Guest

  4. #4

    Default Re: problem while connecting to sqlserver using application role with asp

    Tommy (com) writes: 

    Well, depends on you how you code your application. If you have an
    application where users are constantly connected it is not an issue.
    But these days it is very common to use connection pooling, and this
    case there is a price, yes.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. [Macromedia][SQLServer JDBC Driver][SQLServer]Internal
    By gsmadman in forum Coldfusion Database Access
    Replies: 1
    Last Post: February 9th, 04:53 AM
  2. Problem with SQLServer connections
    By David Aden in forum Coldfusion Database Access
    Replies: 3
    Last Post: August 31st, 05:10 PM
  3. Connecting ASP.NET to SQLServer,Somebody,Please!!
    By Mohammad-Reza in forum ASP Database
    Replies: 2
    Last Post: April 20th, 01:28 PM
  4. Connecting via SQLServer?
    By Blink in forum ASP.NET General
    Replies: 0
    Last Post: July 18th, 02:18 PM
  5. Replies: 0
    Last Post: January 11th, 07:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139