|
I'm glad you liked it.
|
|
|
|
|
Hello,
I'am developing an App that needs to access a SqlDataBase named "Work", but this may be located in any drive of my system (C:,D:,U:,...). The database structure is always the same, so I use a unique name and only change the files.
When the application start, I use a OpenFileDialog to locate the database and execute an storedprocedure to attach the database to SqlServer (EXEC sp_attach_db @dbname ...).
This works fine, and I can work with the database.
When the user ends to work with database, I want to detach ("EXEC sp_detach_db) this from my App to allow to choose another location one.
At this point I get an error reporting that the database is currently in use, ans so, it can't be detached.
StoredProcedures form attach and detach owns to Master database, so when I execute it I'am not using my database.
Do you know any procedure to disconnect my App from database using system.data, and then I could execute the detach procedure ?
(If I only execute Attach and Detach, without accessing to database, is runs fine).
Here the Detach function:
Public Function DetachDB(ByVal DbName As String) As Boolean
Dim Cmd As New SqlClient.SqlCommand
Dim AffectedRows As Int32
Dim TmpDbName As String = _DbName
_DbName = "Master"
Cmd.Connection = GetConnection
Cmd.CommandText = "EXEC sp_detach_db @dbname = '" & DbName & "'"
Cmd.CommandType = CommandType.Text
Cmd.Connection.Open()
Try
AffectedRows = Cmd.ExecuteNonQuery()
Cmd.Connection.Close()
_DbName = TmpDbName
Return (True)
Catch e As Exception
If PopupErrors Then
MessageBox.Show("[DB]Error separando la base de datos " + e.Message)
End If
Cmd.Connection.Close()
_DbName = TmpDbName
Return False
Exit Function
End Try
End Function
|
|
|
|
|
How does your connection-string look? Do you connect to the "master" database on the server, or to your named database?I are Troll 
|
|
|
|
|
The connection to the app database is:
"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=work;Data Source=pcjordi2009\sqlexpress"
And when I connect to detach/attach I use:
"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Master;Data Source=pcjordi2009\sqlexpress"
|
|
|
|
|
Looks good. Just tried from SQL Management Studio, one can simulate an open connection by simply opening a table in edit-mode.
ALTER DATABASE [AdventureWorks] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE master;
GO
sp_detach_db 'AdventureWorks'; The first command sets the database to single-user mode, effectively disconnecting everyone else I are Troll
|
|
|
|
|
I've tested from Management Studio and it works properly.
From my application, the problem continues.
When I execute ALTER DATABASE... the DB enters in Single User Mode (An icon appears on Db List in Mabagement Studio, but when execute detach it fails reporting that Database is in use 
|
|
|
|
|
edmonson wrote: When I execute ALTER DATABASE... the DB enters in Single User Mode (An icon appears on Db List in Mabagement Studio, but when execute detach it fails reporting that Database is in use
Strange.. Can you post the offending code here?
I've tried it here this way, just to verify that it's actually possible;
string cs = "Server=.\\SQLEXPRESS;Database=master;Trusted_Connection=True;";
using (var con = new SqlConnection(cs))
using (var cmd = new SqlCommand())
{
con.Open();
cmd.Connection = con;
cmd.CommandText = @"ALTER DATABASE [AdventureWorks] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;";
cmd.ExecuteNonQuery();
cmd.CommandText = @"sp_detach_db 'AdventureWorks';";
cmd.ExecuteNonQuery();
}I are Troll
|
|
|
|
|
Sure, this code works and the database is deatached succesful!
but when I execute an attach again:
- Database is attached successful (I can see on Management Studio)
- I open a connection an it opens succesful.
- But when I call an cmd.ExecuteReader to get data, then next error message appears:
"Error at level transport when send the query to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe)"
I've reviewed SQL configuration, and all seems good.
If I restart the App all work succesful.
Any idea, why the server process stopped ??
|
|
|
|
|
edmonson wrote: Any idea, why the server process stopped ??
Alas, no. Is it replicatable?I are Troll
|
|
|
|
|
Sorry, the message was:
Sure, your code works and the database is deatached succesful!
but when I execute an attach again(the same database):
- Database is attached successful (I can see on Management Studio)
- I open a connection an it opens succesful.
- But when I call an cmd.ExecuteReader to get data, then next error message appears:
"Error at level transport when send the query to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe)"
I've reviewed SQL configuration, and all seems good.
If I restart the App all work succesful.
Any idea about it ?
|
|
|
|
|
I don't know; does it happen every time after a reattach?I are Troll 
|
|
|
|
|
Yes, every time I attached again (without exiting from application) and try to get data, the problem appears.
But if I restart application and reattach then works fine !
(Strange behavior )
If I find the solution I'll report it.
|
|
|
|
|
edmonson wrote: Yes, every time I attached again (without exiting from application) and try to get data, the problem appears.
Without exiting from the application; so something gets left behind, in either the application or SQL Server. When the application starts to fetch data, then the application will create a new Connection object, right?
Thinking out loud; the database is re-attached, from within the application. Could it be that we disconnected an active DataReader during the detaching?I are Troll
|
|
|
|
|
|
They also got a webservice[^], wouldn't that be easier to interface with?I are Troll
|
|
|
|
|
Sorry Eddie,
but I'm trying to program some interaction with webpages, not trying to buy an excel datasheet.
My program needs now and then some distance calculations, and I'm not using it professionally.
Further more, I cannot find an option to program interaction with the webservices. They just offer some data for sale.
Still need help to communicate with e.g. the forementioned website, but others as well.
I think I can adapt the eventuel solution to other sites probably, so I'll stick to my help request as it is for now.
Kind regards,
Onno
|
|
|
|
|
Google for "vb.net web page scraping".
|
|
|
|
|
Thx.. I'll go and give it a try. Might just do the trick.
|
|
|
|
|
orvries wrote: Still need help to communicate with e.g. the forementioned website, but others as well.
Once you push the button on the page, it'll load a new page, based on the URL. You'd have to build this URL from code, transferring the correct parameters. As an example, by changing "gouda" to "amsterdam[^]" in the URL, you'll get a different result.
Once you have that, you can fetch[^] the results. Last, you'd need to parse the results, cutting out those parts of the data that you're interested in.
You might want to search for "web scraper vb.net" to get some examples.I are Troll
|
|
|
|
|
Thx.. I'll go and give it a try. Might just do the trick.
BTW the change you suggested did give other text-output on the screen, but the calculated route was not affected. That seems to depend on the previously determined coordinates by the first button press. But I might filter those out of a stream that I get as you indicated.
Thanx a lot for helping me going again!
|
|
|
|
|
Hi all
Can someone plz me about writing codes for asymmetric encryption
I must provide 2 keys in a text box the the program will display the encrypted text in a text box
thnaks
it very urgetn for an assignment
|
|
|
|
|
faizalfz wrote: it very urgetn for an assignment
But not urgent enough to do this[^]?txtspeak is the realm of 9 year old children, not developers. Christian Graus
|
|
|
|
|
I am trying to design a pop-up notification in VB. For example i have an expiring date for some items and wants to notify the user about item that are about expiring and the number of days left. I am using VB and Microsoft SQL Server. ANy help?????
|
|
|
|
|
I can't see the problem here. You either need to design your own form and populate it, or you need to use a Message Box like this MsgBox(String.Format("{0} will expire on th {1}",Product.Name,Product.ExpiryDate). I am not sure what you really need to do.
|
|
|
|
|
What have you tried so far?
Sounds an easy enough task, but if you are stuck getting the code to work, post a relevant code snippet describing the problem and I'm sure we can help you out.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
