[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Connecting works, but Disconnecting and reconnecting fails on application role.

Posted on 2008-11-18
18
Medium Priority
?
774 Views
Last Modified: 2012-06-21
With the VB 2005 code below I can connect to one or more databases.  However if I connect to a database then connect to a different database, I can't reconnect to the original database.

            Con1.ConnectionString = ConnectionString
            cmd1.Connection = Con1
            Con1.Open()
            cmd1.Parameters.Clear()
            cmd1.CommandText = "sp_setapprole"
            cmd1.CommandType = CommandType.StoredProcedure
            cmd1.Parameters.AddWithValue("@rolename", "xx")
            cmd1.Parameters.AddWithValue("@password", "xx")
            cmd1.ExecuteNonQuery()

The connection string is like this.

Data source=IPAddress;Initial Catalog=DbName;Integrated Security=SSPI;Persist Security Info=FALSE;Network Library=DBMSSOCN
0
Comment
Question by:AlHal2
  • 9
  • 9
18 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 22984200
Are you sure that you closed your connection before trying to connect/reconnet to another database ?

con.State == ConnectionState.Open


if con.State == ConnectionState.Open then
   con.Close()
end if
 
Con1.ConnectionString = ConnectionString
cmd1.Connection = Con1
Con1.Open()
cmd1.Parameters.Clear()
cmd1.CommandText = "sp_setapprole"
cmd1.CommandType = CommandType.StoredProcedure
cmd1.Parameters.AddWithValue("@rolename", "xx")
cmd1.Parameters.AddWithValue("@password", "xx")
cmd1.ExecuteNonQuery()

Open in new window

0
 

Author Comment

by:AlHal2
ID: 22984771
I did close the connection.  Why did you have 2 equal signs for VB?  Was it a typo?
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 22984788
>> Was it a typo? -> yes, because I was thinking in c# while typing :)

Do you get any errormessage ?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:AlHal2
ID: 22985751
Here is the message.
A severe error occured on the current command.  The results, if any should be discarded.
0
 

Author Comment

by:AlHal2
ID: 22985776
The error occured on the executenon query line.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 22986775
Try to recreate your command-object. Perhaps that will solve the issue (new sqlcommand or oledbcommand, ...)
Con1 = New SqlConnection
cmd1 = New SqlCommand
Con1.ConnectionString = ConnectionString
cmd1.Connection = Con1
Con1.Open()
cmd1.Parameters.Clear()
cmd1.CommandText = "sp_setapprole"
cmd1.CommandType = CommandType.StoredProcedure
cmd1.Parameters.AddWithValue("@rolename", "xx")
cmd1.Parameters.AddWithValue("@password", "xx")
cmd1.ExecuteNonQuery()

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 22986792
>> A severe error occured on the current command.  The results, if any should be discarded.

Can you check if the stored procedure you want to execute (sp_setapprole) is on the database you open !
0
 

Author Comment

by:AlHal2
ID: 22992809
The procedure runs when I connect to the database the first time, but not the second.  Therefore it's on there.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 22993515
I think that your connection is successfull, but that there is a problem with your stored procedure.

Can you try this to see if your connection failed or that your stored procedure failed (for the second time).

Can you post the message you get when the error occured...
Con1.ConnectionString = ConnectionString
cmd1.Connection = Con1
try
  Con1.Open()
Catch ex as exception
  Messagebox.show ("Connection Failed with following exception: " & ex.tostring())
End Try
cmd1.Parameters.Clear()
cmd1.CommandText = "sp_setapprole"
cmd1.CommandType = CommandType.StoredProcedure
cmd1.Parameters.AddWithValue("@rolename", "xx")
cmd1.Parameters.AddWithValue("@password", "xx")
try
  cmd1.ExecuteNonQuery()
Catch ex as exception
  Messagebox.show ("Stored procedure Failed with following exception: " & ex.tostring())
End Try

Open in new window

0
 

Author Comment

by:AlHal2
ID: 22993799
Error message attached.
ErrMsg.doc
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 22993810
Can you post the stored procedure. Apparantly it goes wrong in your stored procedure and not in your connection to the database !
0
 

Author Comment

by:AlHal2
ID: 22994281
Attached.  By the way it is OK with this VB6 code.

    Set oConn = New Adodb.Connection
    With oConn
        .Provider = "SQLOLEDB"
        .Properties("Integrated Security").Value = "SSPI"
        .Properties("Persist Security Info") = False
        .Properties("Data Source").Value = DataSource
        .Properties("Network Library").Value = "DBMSSOCN" '  
        .Properties("Initial Catalog").Value = Dbase
        .Open
    End With
oConn.Execute "sp_setapprole 'tunaTrawler', 'tun4Tr4wler'"

sp.doc
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 22994334
If it works with vb6, then we can be sure that the procedure is also correct (although we already knew that because the first time it's executed it goes correctly).

If you run the stored procedure directly on your database, does it give an error if you execute it multiple time behind each other (same parameters, ...)
0
 

Author Comment

by:AlHal2
ID: 22994398
When I ran it the first time like this it was fine.
exec sys.sp_setapprole @rolename='xx',@Password='xx'

When I ran it the second time I got this message.

Msg 2762, Level 16, State 1, Procedure sp_setapprole, Line 46
sp_setapprole was not invoked correctly. Refer to the documentation for more information.

Looks like it's to do with encryption or cookies.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 22994436
It seems that way. When you tried to perform that in vb6, did you catch the error or did you have somewhere "on error resume next". That can explain why you didn't get any error in vb6.
In .net all the errors will popup.
You'll need to see what's wrong in your sp and alter it... Normally you should be possible to execute it twice on the database server. If that doesn't succeed, then it also won't work from .NET.
0
 

Author Comment

by:AlHal2
ID: 22994535
I found the answer on http://support.microsoft.com/kb/q229564/
I've added "Pooling=Fasle" to the connection string.

Could you explain in plain english what's going on?
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 22994559
Here is complete description of connection pooling
Connection Pooling for the .NET Framework Data Provider for SQL Server
http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.71).aspx

In short words: by setting the pooling=false, it will always recreate the connection and never use an existing connection. The connections aren't removed directly on the sqlserver
0
 

Author Closing Comment

by:AlHal2
ID: 31517822
Thanks.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month20 days, 2 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question