|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Server Express - Detach & ShrinkWhen a user of my application has finished with the database it must be
ready to be moved to any other machine for use by another user. For this I'd like to detach and shrink the database (its in the default simple recovery mode) and so I'm using SSEUtil from within my code to accomplish this. However, I think it would be better if I did this from ADO.NET since I'd have more control and I'd be able to get more information if an error was to occur. What's the best way to accomplish an detach followed by a database shrink in ADO.NET? TIA Hi,
"elziko" <elz***@yahoo.co.uk> wrote in message I won't ask.news:eZXMvrFPGHA.1312@TK2MSFTNGP09.phx.gbl... > When a user of my application has finished with the database it must be > ready to be moved to any other machine for use by another user. For this I'd > like to detach and shrink the database (its in the default simple recovery Sure, the mixture of SqlCommand, ExecuteNonQuery and Sql Server's DDL > mode) and so I'm using SSEUtil from within my code to accomplish this. > > However, I think it would be better if I did this from ADO.NET since I'd > have more control and I'd be able to get more information if an error was > to occur. What's the best way to accomplish an detach followed by a > database shrink in ADO.NET? commands (check SQLServer's BOL) will do just fine. -- Miha Markic [MVP C#] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ Miha Markic [MVP C#] wrote:
> I won't ask. Do you think what I'm doing is some how flawed? I'd be interested to know why! I was under the understanding that this whole XCopy scenario was one of SQL Server Express's strengths. > Sure, the mixture of SqlCommand, ExecuteNonQuery and Sql Server's DDL Thanks.> commands (check SQLServer's BOL) will do just fine. Does person A somehow alter the database that person B uses? Consider that
the database installed with User Instance = True (the recommended method) makes a copy of the original database and saves it to the user's private memory space on disk. -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ "elziko" <elz***@yahoo.co.uk> wrote in message news:%23VacpMHPGHA.3732@TK2MSFTNGP10.phx.gbl... > Miha Markic [MVP C#] wrote: >> I won't ask. > > Do you think what I'm doing is some how flawed? I'd be interested to know > why! I was under the understanding that this whole XCopy scenario was one > of SQL Server Express's strengths. > >> Sure, the mixture of SqlCommand, ExecuteNonQuery and Sql Server's DDL >> commands (check SQLServer's BOL) will do just fine. > > Thanks. > William (Bill) Vaughn wrote:
> Does person A somehow alter the database that person B uses? Consider I'm not sure exactly what you're trying to explain but they way my > that the database installed with User Instance = True (the > recommended method) makes a copy of the original database and saves > it to the user's private memory space on disk. application is designed then once person A opens the database to update it there is no way person B can also open the same database until person A is finished with it. Is that what you mean? Thanks for replying. Ah, now _I_ am confused.
SQL Server (even the Express edition) can serve as a standalone local database that a single user can access on a system. It can also be setup to be visible on the network so other users can access it. This way one, two or twenty-two hundred people can access the same database at the same time. We're not sure why you're copying the database from place to place to share the data. Unless you don't have a network, this does not really make sense. Even if you don't have a network, there are ways to extract data from the database (replication or DTS) that can be used to move the data from place to place or keep several databases in sync. -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ "elziko" <elz***@yahoo.co.uk> wrote in message news:e%231p0eIPGHA.2924@TK2MSFTNGP11.phx.gbl... > William (Bill) Vaughn wrote: >> Does person A somehow alter the database that person B uses? Consider >> that the database installed with User Instance = True (the >> recommended method) makes a copy of the original database and saves >> it to the user's private memory space on disk. > > I'm not sure exactly what you're trying to explain but they way my > application is designed then once person A opens the database to update it > there is no way person B can also open the same database until person A is > finished with it. Is that what you mean? > > Thanks for replying. > William (Bill) Vaughn wrote:
> Ah, now _I_ am confused. OK, what I need is some sort of single file to store numerical data. In the past we have used Access databases for this sort of thing but now our client is storing up to millions of rows of data in a single file and Access just can't cope with this. During each session he may want to remove some of the data and add some more. After a few tests I found that SQL Server Express can handle this amount of data quite well and as you said it can be used for a single user locally on a system. Our client requires that these files be portable for e-mailing, copying to CD or sending via FTP (depending on how big they are) etc. The only trouble is that an SQL Server Databases (mdf) also require that the log file (ldf) is moved around with it. So when a user has finished with a database, I stream both the mdf and ldf files into a single file that the user can treat like any other file. When that file is opened again, my application extracts the mdf and ldf and puts it into a temporary folder for access again. This way I get the practical, scalability and speed benefits of teh SSE database along with the requirement that the uesr has of having a single project file. The extraction and combining of the mdf & ldf are pretty quick and the whole process seems to work very well. Creating our own system for storing the data in a way that gives me as much functionality just seems to be re-inventing the wheel! I hope this makes sense and although it seems to work very well I'd be interested in any flaws that you can think of. Interesting approach but I might have taken another tact. Have you
investigated BCP? This can be called to load data into and out of SQL Server and it does so very quickly. This way you could save a delimited or custom format file that could be easily transported and reload it quickly at the destination. No, I'm not endorsing XML here but a custom format might be best. These files contain only data--no schema so they are light and tight and can be easily compressed. BCP can be invoked from SQL Server itself (TSQL) to export the file. The 2.0 Framework supports compression (see this month's SQL Server Magazine) so you could compress programmatically. hth -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ "elziko" <elz***@yahoo.co.uk> wrote in message news:egoTkMRPGHA.740@TK2MSFTNGP12.phx.gbl... > William (Bill) Vaughn wrote: >> Ah, now _I_ am confused. > > OK, what I need is some sort of single file to store numerical data. In > the past we have used Access databases for this sort of thing but now our > client is storing up to millions of rows of data in a single file and > Access just can't cope with this. During each session he may want to > remove some of the data and add some more. > > After a few tests I found that SQL Server Express can handle this amount > of data quite well and as you said it can be used for a single user > locally on a system. Our client requires that these files be portable for > e-mailing, copying to CD or sending via FTP (depending on how big they > are) etc. The only trouble is that an SQL Server Databases (mdf) also > require that the log file (ldf) is moved around with it. > > So when a user has finished with a database, I stream both the mdf and ldf > files into a single file that the user can treat like any other file. When > that file is opened again, my application extracts the mdf and ldf and > puts it into a temporary folder for access again. > > This way I get the practical, scalability and speed benefits of teh SSE > database along with the requirement that the uesr has of having a single > project file. The extraction and combining of the mdf & ldf are pretty > quick and the whole process seems to work very well. Creating our own > system for storing the data in a way that gives me as much functionality > just seems to be re-inventing the wheel! > > I hope this makes sense and although it seems to work very well I'd be > interested in any flaws that you can think of. > William (Bill) Vaughn wrote:
> Interesting approach but I might have taken another tact. Have you Well its good to see that you didn't come up with any glaring faults with my > investigated BCP? method :) Thanks for your sugesstion but since I have put time in already and what I have works very well I think I'll leave it as it is. I'll look ito BCP from future reference though. Thanks  
Other interesting topics
|
|||||||||||||||||||||||
