SQL Server Express - Detach & Shrink

[elziko]

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

 

[Miha Markic [MVP C#]]

Hi,

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.

I won't ask.

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?

Sure, the mixture of SqlCommand, ExecuteNonQuery and Sql Server's DDL
commands (check SQLServer's BOL) will do just fine.

 

[elziko]

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]

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.


--
____________________________________
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]

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]

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.

--
____________________________________
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]

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]

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

--
____________________________________
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]

Interesting approach but I might have taken another tact. Have you
investigated BCP?

Well its good to see that you didn't come up with any glaring faults with my
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