asked on

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

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

Dirk Haest

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

AlHal2

ASKER

I did close the connection. Why did you have 2 equal signs for VB? Was it a typo?

Dirk Haest

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

Do you get any errormessage ?

AlHal2

ASKER

Here is the message.
A severe error occured on the current command. The results, if any should be discarded.

AlHal2

ASKER

The error occured on the executenon query line.

Dirk Haest

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

Dirk Haest

>> 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 !

AlHal2

ASKER

The procedure runs when I connect to the database the first time, but not the second. Therefore it's on there.

Dirk Haest

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

AlHal2

ASKER

Error message attached.
ErrMsg.doc

Dirk Haest

Can you post the stored procedure. Apparantly it goes wrong in your stored procedure and not in your connection to the database !

AlHal2

ASKER

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

Dirk Haest

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, ...)

AlHal2

ASKER

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.

Dirk Haest

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.

AlHal2

ASKER

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?

ASKER CERTIFIED SOLUTION

Dirk Haest

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AlHal2

ASKER

Thanks.