"Timeout Expired" on large table change

I have a table containing several hundred thousand rows. Once of the
fields in the table is named EODFeedDate. The field is nullable. I've
decided that I do NOT want that field to be nullable. I've unchecked the
"allow nulls" checkbox for the field in Management Studio. I then tried to
save my schema change. I'm getting the following error from Management
Studio:
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."
I can't figure out what's causing the problem. It's worth nothing that:
- Of the current entries in this table, none of them have EODFeedDate
set to null so making the field non-nullable should cause a conflict.
- No one else is connected to the database. It's a private development
machine -- so it isn't a contention problem.
Any ideas?
David> "Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding."
> I can't figure out what's causing the problem.
I did a google search on this. You have to write a query to workaround this
bug. I've never written query code to modify a table before; I've always
used the UI, so I had some learning to do.
I learned about the ALTER TABLE statement but my execution of that statement
failed. The error message stated that the column could not be made
non-nullable because there is an index on the table that made use of the
column in question. That's when I learned about the DROP INDEX statement. By
using a DROP INDEX in tandem with an ALTER TABLE, I was able to make the
field non-nullable. I then re-created the index using the UI.
I've never spent so much time trying to do something so simple.
David|||You can have Management Studio generate the script by making the change,
then right clicking the window and selecting "Generate Change Script"
"The One We Call 'Dave'" <ghetto@.englewood.com> wrote in message
news:-MOdnXQku9doHYXZnZ2dnUVZ_t-dnZ2d@.giganews.com...
> I did a google search on this. You have to write a query to workaround
> this bug. I've never written query code to modify a table before; I've
> always used the UI, so I had some learning to do.
> I learned about the ALTER TABLE statement but my execution of that
> statement failed. The error message stated that the column could not be
> made non-nullable because there is an index on the table that made use of
> the column in question. That's when I learned about the DROP INDEX
> statement. By using a DROP INDEX in tandem with an ALTER TABLE, I was able
> to make the field non-nullable. I then re-created the index using the UI.
> I've never spent so much time trying to do something so simple.
> David
>|||"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uIYUXTHSGHA.4456@.TK2MSFTNGP14.phx.gbl...
> You can have Management Studio generate the script by making the change,
> then right clicking the window and selecting "Generate Change Script"
Doh! I didn't realize that!! Thanks for the tip. I'll be sure to make use of
that in the future.

"Detach Database" in SQL Server 2005 apparently dropped the database instead

Hello,
I am still trying to get my head around this one. I have been working
on a new version of a website and decided I wanted to make a backup of
the old database file before I tried running any scripts against it.
Since the production website is currently down, I decided to detach
the database, copy the file, and then reattach it. Instead of using
scripts, I used MS SQL Server Management Studio to handle the task.
After I ran the detach command on the database (with the default
options selected), I went to the directory where all of the database
files are stored on the server and the file doesn't exist!!
So, I tried to back up the database file because I didn't have a copy,
and now I don't even have the original. I did a complete Windows file
search on my entire network to try to locate the file, but it is
gone. Well, it isn't the end of the world in my case because I can go
to a prior backup and just move forward, but I really would like to
know what happened so it doesn't happen again.
For starters, is there any way I can query a system table to determine
what the file location was of the database I detached? Second, is
there some "memory only" mode that a database can be put in so when it
is detached it will disappear completely?
TIAHi
It is really strange . I did just at least ten testes to detach the database
via SSMS and it worked just fine
I have SQL Server Dev 2005 (SP2) Edition. Can you reproduce the problem for
another non-produiction database?
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188456828.420852.301950@.m37g2000prh.googlegroups.com...
> Hello,
> I am still trying to get my head around this one. I have been working
> on a new version of a website and decided I wanted to make a backup of
> the old database file before I tried running any scripts against it.
> Since the production website is currently down, I decided to detach
> the database, copy the file, and then reattach it. Instead of using
> scripts, I used MS SQL Server Management Studio to handle the task.
> After I ran the detach command on the database (with the default
> options selected), I went to the directory where all of the database
> files are stored on the server and the file doesn't exist!!
> So, I tried to back up the database file because I didn't have a copy,
> and now I don't even have the original. I did a complete Windows file
> search on my entire network to try to locate the file, but it is
> gone. Well, it isn't the end of the world in my case because I can go
> to a prior backup and just move forward, but I really would like to
> know what happened so it doesn't happen again.
> For starters, is there any way I can query a system table to determine
> what the file location was of the database I detached? Second, is
> there some "memory only" mode that a database can be put in so when it
> is detached it will disappear completely?
> TIA
>|||Sounds weird. No chance SSMS to drop a database when you detach it. There is
only "drop connection" option which contains "drop" action and it doesn't
drop the database.
You said you did a complete seach on your HDD(s) but I guess it must be
around somewhere on your HDD. Maybe you forgot it's name or something?
--
Ekrem Önsoy
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188456828.420852.301950@.m37g2000prh.googlegroups.com...
> Hello,
> I am still trying to get my head around this one. I have been working
> on a new version of a website and decided I wanted to make a backup of
> the old database file before I tried running any scripts against it.
> Since the production website is currently down, I decided to detach
> the database, copy the file, and then reattach it. Instead of using
> scripts, I used MS SQL Server Management Studio to handle the task.
> After I ran the detach command on the database (with the default
> options selected), I went to the directory where all of the database
> files are stored on the server and the file doesn't exist!!
> So, I tried to back up the database file because I didn't have a copy,
> and now I don't even have the original. I did a complete Windows file
> search on my entire network to try to locate the file, but it is
> gone. Well, it isn't the end of the world in my case because I can go
> to a prior backup and just move forward, but I really would like to
> know what happened so it doesn't happen again.
> For starters, is there any way I can query a system table to determine
> what the file location was of the database I detached? Second, is
> there some "memory only" mode that a database can be put in so when it
> is detached it will disappear completely?
> TIA
>|||On Aug 30, 12:12 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> It is really strange . I did just at least ten testes to detach the database
> via SSMS and it worked just fine
> I have SQL Server Dev 2005 (SP2) Edition. Can you reproduce the problem for
> another non-produiction database?
>
I tried creating a new sample database on the same server and
detaching it, but the files remained. So to your answer your
question, no I cannot repeat the problem.
I also know for sure I used the detach and not the delete command on
the previous database because the dialogs are different.
Is there any SQL command you know of that I can use to determine what
exact file location was detached?|||On Aug 30, 12:24 am, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> Sounds weird. No chance SSMS to drop a database when you detach it. There= is
> only "drop connection" option which contains "drop" action and it doesn't
> drop the database.
> You said you did a complete seach on your HDD(s) but I guess it must be
> around somewhere on your HDD. Maybe you forgot it's name or something?
> --
> Ekrem =D6nsoy
>
Believe me, I didn't forget the name. And I scanned every server (and
even every workstation) using "*.mdf". No dice. Since I set the
server up myself, I am pretty certain that I put the file on the local
server. I have never lost a database before and I have been working
with them for about 10 years.
About a week ago, I shut down the MSSQL service and copied all of the
database files from the server to another location. However, this one
database file wasn't included. This makes me wonder if there was just
a "ghost" database object with no actual file behind it or something.
That is why I would like to try to see what it was referencing if
possible.|||> Is there any SQL command you know of that I can use to determine what
> exact file location was detached?
Its probably where all your user databases are located isn't it?
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188458888.053113.228460@.x35g2000prf.googlegroups.com...
>
> On Aug 30, 12:12 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Hi
>> It is really strange . I did just at least ten testes to detach the
>> database
>> via SSMS and it worked just fine
>> I have SQL Server Dev 2005 (SP2) Edition. Can you reproduce the problem
>> for
>> another non-produiction database?
> I tried creating a new sample database on the same server and
> detaching it, but the files remained. So to your answer your
> question, no I cannot repeat the problem.
> I also know for sure I used the detach and not the delete command on
> the previous database because the dialogs are different.
> Is there any SQL command you know of that I can use to determine what
> exact file location was detached?
>|||Do you have any old SQL Server backup of the database? If so you can use RESTORE FILELISTONLY to
check the file location.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188459431.752325.247190@.x35g2000prf.googlegroups.com...
On Aug 30, 12:24 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Sounds weird. No chance SSMS to drop a database when you detach it. There is
> only "drop connection" option which contains "drop" action and it doesn't
> drop the database.
> You said you did a complete seach on your HDD(s) but I guess it must be
> around somewhere on your HDD. Maybe you forgot it's name or something?
> --
> Ekrem Önsoy
>
Believe me, I didn't forget the name. And I scanned every server (and
even every workstation) using "*.mdf". No dice. Since I set the
server up myself, I am pretty certain that I put the file on the local
server. I have never lost a database before and I have been working
with them for about 10 years.
About a week ago, I shut down the MSSQL service and copied all of the
database files from the server to another location. However, this one
database file wasn't included. This makes me wonder if there was just
a "ghost" database object with no actual file behind it or something.
That is why I would like to try to see what it was referencing if
possible.|||On Aug 30, 1:19 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Do you have any old SQL Server backup of the database? If so you can use RESTORE FILELISTONLY to
> check the file location.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
Actually all I have is a database that I created on another computer
and then used DTS to copy tables with data over to it. This was done
before I upgraded to SQL 2005. I don't technically have a "backup" in
the SQL Server sense of the word.|||Do you have an old backup of the master database on the computer where the database existed? If so,
you could restore that backup somewhere and check the sysaltfiles table. Will only give you the mdf
file, but that might be good enough...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188467622.686367.250240@.e9g2000prf.googlegroups.com...
> On Aug 30, 1:19 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Do you have any old SQL Server backup of the database? If so you can use RESTORE FILELISTONLY to
>> check the file location.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> Actually all I have is a database that I created on another computer
> and then used DTS to copy tables with data over to it. This was done
> before I upgraded to SQL 2005. I don't technically have a "backup" in
> the SQL Server sense of the word.
>|||No, I don't have any backups of the master database. This server has
been offline for almost a year. I was just in the process of backing
up the data in the primary database on the server by making a copy of
the file. I plan to set up backups going forward but I didn't have
any before (which is why I decided to detach the database in the first
place).
The file either disappeared when I ran the command to detach it or it
didn't exist before that. If it didn't exist that is another
inexplicable problem - I know I left it in place. Not to mention, I
didn't get any sql errors that the file was missing or anything like
that.
On Aug 30, 3:08 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Do you have an old backup of the master database on the computer where the database existed? If so,
> you could restore that backup somewhere and check the sysaltfiles table. Will only give you the mdf
> file, but that might be good enough...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "NightOwl888" <sstorh...@.webuniverse.net> wrote in message
> news:1188467622.686367.250240@.e9g2000prf.googlegroups.com...|||I figured out the problem. The production database file was prefixed
by the word "Test", the same as the test database. They were in 2
different directories, but they were all on the same server. I
thought that the production database was gone, but it was just named
"Test".
At some point I must have copied the Test database and attached it as
production. This makes sense because still now I am having issues
moving tables from one database to another.
Well, I am glad that I don't have to revert to an older copy and lose
some data. I will definitely back up master and set up some backup
jobs now, too.|||backup...backup..backup...;-)
Make it a best practice to always backup the master database anytime you do
something on the server even if the server is an old one. You'll never know
when it will become handy
"NightOwl888" <sstorhaug@.webuniverse.net> wrote in message
news:1188520731.793321.109640@.q3g2000prf.googlegroups.com...
>I figured out the problem. The production database file was prefixed
> by the word "Test", the same as the test database. They were in 2
> different directories, but they were all on the same server. I
> thought that the production database was gone, but it was just named
> "Test".
> At some point I must have copied the Test database and attached it as
> production. This makes sense because still now I am having issues
> moving tables from one database to another.
> Well, I am glad that I don't have to revert to an older copy and lose
> some data. I will definitely back up master and set up some backup
> jobs now, too.
>

"Cluster-aware" Applications

If a customer has a na application that accesses SQL
server running on a single machine (Windows 2003 Server)
and then decided to implement an active/passive server
cluster with SQL Server Enterprise, does the application
that accesses the database need to change? I am not sure
what is meant by "cluster-aware applications". Isn't
clustering supposed to be transparent to the application?
I read a line in "Server Clusters: Architectural Overview"
that said:
"Server clusters also provide the --> APPLICATION
INTERFACES <-- and tools needed to develop new cluster-
aware aaplications that can take advantage of the high
availability features of server clusters"
Thanks,
Todd
Hi
The biggest issue is when the cluster fails over from one node to another.
The client application must re-connect to the cluster again as the
connection will be broken when the failover occurs.
Any transaction that was running on behalf of the client would have been
rolled back and the application will have to re-submit it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
<anonymous@.discussions.microsoft.com> wrote in message
news:1db6401c4546b$89e3d460$a101280a@.phx.gbl...
> If a customer has a na application that accesses SQL
> server running on a single machine (Windows 2003 Server)
> and then decided to implement an active/passive server
> cluster with SQL Server Enterprise, does the application
> that accesses the database need to change? I am not sure
> what is meant by "cluster-aware applications". Isn't
> clustering supposed to be transparent to the application?
> I read a line in "Server Clusters: Architectural Overview"
> that said:
> "Server clusters also provide the --> APPLICATION
> INTERFACES <-- and tools needed to develop new cluster-
> aware aaplications that can take advantage of the high
> availability features of server clusters"
> Thanks,
> Todd
>
|||A cluster aware application is an application that will function on a
cluster and failover when necessary. As an example, a virtual SQL Server
2000 server is cluster aware iin that it will fail back and forth. By the
same token, Excel is not. If SQL Server 2000 and Excel are both running on
node 1 and node 1 crashes SQL Server will failover to node 2, but Excel
will not.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Cluster-aware Applications
===========================
An application is capable of being cluster-aware if it has the following characteristics:
* It uses TCP/IP as a network protocol.
* It maintains data in a configurable location.
* It supports transaction processing.
Most database applications, transaction processing applications, file and print server applications, and other groupware applications are capable of being made cluster-aware.
There are two types of cluster-aware applications:
* Applications that are managed as highly available cluster resources by a custom resource type. If you want your application to be highly available, to fail over, to benefit from customized failure detection
policies, or to be made available to clients on a virtual server, your application needs to be managed as a cluster resource. This means that in addition to any cluster-awareness you want to add to the application,
you will need to create a custom resource type to manage the application.
* Applications that interact with the cluster but are not cluster resources. Cluster Administrator is an example of such an application. This category also includes cluster-aware performance monitors, configuration
tools, setup applications, event handlers, and cluster management applications.
Cluster-Unaware Applications
===========================
The following features distinguish a cluster-unaware application:
* The application does not use the Server Cluster API. Therefore, it cannot discover information about the cluster environment, interact with cluster objects, detect that it is running in a cluster, or change its behavior
between clustered and non-clustered systems.
* If the application is managed as a cluster resource, it is managed as a Generic Application resource type or Generic Service resource type. These resource types provide very basic routines for failure
detection and application shutdown. Therefore, a cluster-unaware application might not be able to perform the initialization and cleanup tasks needed for it to be consistently available in the cluster.
Older applications are cluster-unaware. However, a cluster-unaware application can be made cluster-aware by creating resource types to manage the application. A custom resource type provides the
initialization, cleanup, and management routines specific to the needs of the application.
There is nothing inherently wrong with cluster-unaware applications. As long as they are functioning and highly available to cluster resources when managed as Generic Applications or Generic Services, there is
no need to make them cluster-aware. However, if an application does not start, stop, or failover consistently when managed by the generic types, it should be made cluster-aware.
Additional Information
======================
Platform SDK: Windows Clustering
http://msdn.microsoft.com/library/de...nformation.asp
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

"Best Practices" way to distribute MSDE

i have decided that when distributing MSDE2000 i want to use the Microsoft
MSI file (rather than using Merge Modules).
What is the (THE, the one) best way to:
1. Install MSDE?
- checking existing named instances
- use CreateProcess and wait? Launch msiexec directly?
2. Start MSDE engine?
- SQL DMO?
- install DMO?
3. Put my database into MSDE?
- restore empty database?
- attach empty database?
- scripts?
- what about creating logins?
- what about fixing users-logins mappings?
- using TSQL?
- using DMO?
4. Backup my database once it's in production?
- TSQL?
- DMO?
5. Restore a database once it's in production?
- TSQL?
- DMO?
6. If they have to re-install MSDE, and they then restore a database, how to
re-create the logins? How do i catch that it has happened and i need to
re-map logins to users?
A quick search of Microsoft reveals about 20 different methods for
accomplishing the same things.
i want to know which one is the right one.
Personally, I've always used DMO.
On Tue, 6 Jul 2004 13:21:25 -0400, Ian Boyd wrote:

> i have decided that when distributing MSDE2000 i want to use the Microsoft
> MSI file (rather than using Merge Modules).
> What is the (THE, the one) best way to:
> 1. Install MSDE?
> - checking existing named instances
> - use CreateProcess and wait? Launch msiexec directly?
> 2. Start MSDE engine?
> - SQL DMO?
> - install DMO?
> 3. Put my database into MSDE?
> - restore empty database?
> - attach empty database?
> - scripts?
> - what about creating logins?
> - what about fixing users-logins mappings?
> - using TSQL?
> - using DMO?
>
> 4. Backup my database once it's in production?
> - TSQL?
> - DMO?
> 5. Restore a database once it's in production?
> - TSQL?
> - DMO?
> 6. If they have to re-install MSDE, and they then restore a database, how to
> re-create the logins? How do i catch that it has happened and i need to
> re-map logins to users?
>
> A quick search of Microsoft reveals about 20 different methods for
> accomplishing the same things.
> i want to know which one is the right one.
|||hi Paul, Ian,
"Paul Buxton" <psb@.NOSPAMspireite.demon.co.uk> ha scritto nel messaggio
news:knuxcy7j42cd.f6k90yoseg6f.dlg@.40tude.net...
> Personally, I've always used DMO.
>
me too, but do not attach database(s)...
instead I do execute DDL sql scripts to create objects and pre-load them...
I actually use BCP too to preload heavy populated objects...
actually, running DDL sql scripts, as long as INSERT INTO scripts can be
done via Ado/Ado.Net too...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
All depends on what data you have to put in. In my instance, a BCP of all
the data we ship does comes up to a little under a gig - for me shipping
the mdf/ldf and attaching is far more preferable
|||How do you determine if an SQL Server is already installed?
If so, how do you detect if your database is already installed?
How do you detect if you login is already created?
If you login is not already created, but your database is, how do you relink
them?
If you login is already there, but the database isn't how do you handle it?
etc etc
i think i'm beginning why Microsoft didn't write an MSDE installer that
could just install MSDE...
"Paul Buxton" <psb@.NOSPAMspireite.demon.co.uk> wrote in message
news:knuxcy7j42cd.f6k90yoseg6f.dlg@.40tude.net...[vbcol=seagreen]
> Personally, I've always used DMO.
>
> On Tue, 6 Jul 2004 13:21:25 -0400, Ian Boyd wrote:
Microsoft[vbcol=seagreen]
how to[vbcol=seagreen]
|||hi Ian,
"Ian Boyd" <admin@.SWIFTPA.NET> ha scritto nel messaggio
news:%23fiy0DIZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> How do you determine if an SQL Server is already installed?
you can (locally) check the registry, instantiate SQL-DMO (which only tells
if SQL-DMO is locally present) and try a connection to the server... this
can be done via Ado/Ado.Net too...

> If so, how do you detect if your database is already installed?
query the database catalogue for it's name... SQL-DMO/Ado/Ado.Net

> How do you detect if you login is already created?
if you can't connect with that login, it's not there... or SQL-DMO or
sp_helplogins 'login2test' via Ado/Ado.Net

> If you login is not already created, but your database is, how do you
relink
> them?
strange situation, isn't it? =;-D
eventually, just re-add your login and grant db access, as long as all
object privileges... SQL-DMO/Ado/Ado.Net

> If you login is already there, but the database isn't how do you handle
it?
you just create the database... and then go on granting db access and object
privileges... SQL-DMO/Ado/Ado.Net
just use traditional common sense =;-)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Now is there any Microsoft document that describes this typical situation of
a client's computer was re-installed, and they need their data back...
What is the best practice to do this?
Ways to put a database into a server:
Attach my empty db
Restore my empty db
Run scripts to create my empty db
Restore a users's selected backup.
Attach a users db from a previous install.
Is there a concensus on an interface, API, and routines to perform these
things are automatically as possible?
Remember, if i'm supposed to be using MSDE now instead of Jet: Jet was very
easy for the client to backup and restore. They copied the file when they
wanted a backup, and they put it into the folder when they wanted to
restore.
What is the MSDE equivalent procedures to accomplish the same task as
seamlessly for the end user without any vendor intervention or hand-holding?
What are the "Best Practices" way to distribute MSDE?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2l3qjaF8auodU1@.uni-berlin.de...
> hi Ian,
> "Ian Boyd" <admin@.SWIFTPA.NET> ha scritto nel messaggio
> news:%23fiy0DIZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> you can (locally) check the registry, instantiate SQL-DMO (which only
tells
> if SQL-DMO is locally present) and try a connection to the server... this
> can be done via Ado/Ado.Net too...
>
> query the database catalogue for it's name... SQL-DMO/Ado/Ado.Net
>
> if you can't connect with that login, it's not there... or SQL-DMO or
> sp_helplogins 'login2test' via Ado/Ado.Net
> relink
> strange situation, isn't it? =;-D
> eventually, just re-add your login and grant db access, as long as all
> object privileges... SQL-DMO/Ado/Ado.Net
> it?
> you just create the database... and then go on granting db access and
object
> privileges... SQL-DMO/Ado/Ado.Net
> just use traditional common sense =;-)
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Ian,
"Ian Boyd" <admin@.SWIFTPA.NET> ha scritto nel messaggio
news:e1pXkyVZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> Now is there any Microsoft document that describes this typical situation
of
> a client's computer was re-installed, and they need their data back...
> What is the best practice to do this?
> Ways to put a database into a server:
> Attach my empty db
> Restore my empty db
> Run scripts to create my empty db
> Restore a users's selected backup.
> Attach a users db from a previous install.
as MSDE is distributed without management tools but oSql.Exe, the guidlines
I've found are in
http://support.microsoft.com/default...N-US;q325003..
in my understanding, you, as an ISV, are supposed to provide all the
functionnalities to support and manage your database(s) inside your
application(s) and/or with companion tools (thrid party or home built)
so you have to provide backup/restore functionalities, login/users
management and so on...
as regards database(s) creation, I do personally provides Transact-SQL
scripts, but attaching a shipped .mdf + .ldf solution is viable too, and is
often recommended as the simplest solution, but I do personally don't like
it, perhaps for a "purist" point of view...
anyway... for the sake of simplicity, you can use the method you better
like, as, for instance, Red-Gate new Packager tool
(http://www.red-gate.com/sql/sql_packager.htm), which provides a single file
installer for your database(s)...

> Is there a concensus on an interface, API, and routines to perform these
> things are automatically as possible?
for dayly house-keeping I do usually provide scripted job for database
backup, that rely on the SQL Server Agent, but my apps all feature (not
scheduled) backup functionalities, as long as login/user management..
database restore shoul'd usually not be a daly operation [=;-D ] , but is
provided as well... one shot operation...
again, personally I choose SQL-DMO API becouse I already provided it's
dependencies for my companion database general management tool, but
ADO/ADO.Net are good as well.. it all depends on your needs/API
skill/preferred method...

> Remember, if i'm supposed to be using MSDE now instead of Jet: Jet was
very
> easy for the client to backup and restore. They copied the file when they
> wanted a backup, and they put it into the folder when they wanted to
> restore.
>
actually you can even choose this solution, as long as your database all
have the 'Auto close' property set... this option will actually close (and
free) the physical database and transaction log files on the file system, so
that file copy operation (not SQL Server backup!) can be performed.. please
keep in mind connection pool time, which usually requires about 1 minute to
effectively close...
but, again... my personala advice is to perform dayly house-keeping actions
withour user's intervention.. that's to say: do what your users usually
skip... automatically backup their data ...
one draw back... SQLExpress, the replacement for MSDE in the SQL Server 2005
code base, will remove SQL Server Agent from the SKU, so keep in mind this
for the future.. personally I've still have to choose a scheduled
alternative, but I'll provide it for sure.
remember, you are supposed to provide all the manage/support
functionnalities

> What is the MSDE equivalent procedures to accomplish the same task as
> seamlessly for the end user without any vendor intervention or
hand-holding?
as MSDE is NOT Jet, you have a lot of extra features at a resonably price
[=;-D], but you have to take some new sagacity in your work...
nowdays, all our users do have some Access skill [potentially dangerous
=;-D ], so you shoul'd even train your users a little more, but this is
another story...

> What are the "Best Practices" way to distribute MSDE?
Microsoft provides ton of information as
http://msdn.microsoft.com/library/de...eddingmsde.asp
http://www.microsoft.com/sql/techinf...swithmsdes.asp
http://www.microsoft.com/sql/msde/te...ntegration.asp
http://msdn.microsoft.com/library/de...stsql_7b91.asp
....
personally I do provide a separate setup for MSDE, with a home built user
interface that takes care and handles all required parameter to then shell
to the setup.exe boostrap installer...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you all for you input and feedback;
and tolerating my agressive, insulting, condescending, patronizing writing
style - to everyone in every post i've ever done.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2l7qo3F9s4e1U1@.uni-berlin.de...[vbcol=seagreen]
> hi Ian,
> "Ian Boyd" <admin@.SWIFTPA.NET> ha scritto nel messaggio
> news:e1pXkyVZEHA.2216@.TK2MSFTNGP10.phx.gbl...
situation
> of
> as MSDE is distributed without management tools but oSql.Exe, the
guidlines
> I've found are in
> http://support.microsoft.com/default...N-US;q325003..
> in my understanding, you, as an ISV, are supposed to provide all the
> functionnalities to support and manage your database(s) inside your
> application(s) and/or with companion tools (thrid party or home built)
> so you have to provide backup/restore functionalities, login/users
> management and so on...
> as regards database(s) creation, I do personally provides Transact-SQL
> scripts, but attaching a shipped .mdf + .ldf solution is viable too, and
is
> often recommended as the simplest solution, but I do personally don't like
> it, perhaps for a "purist" point of view...
> anyway... for the sake of simplicity, you can use the method you better
> like, as, for instance, Red-Gate new Packager tool
> (http://www.red-gate.com/sql/sql_packager.htm), which provides a single
file[vbcol=seagreen]
> installer for your database(s)...
>
> for dayly house-keeping I do usually provide scripted job for database
> backup, that rely on the SQL Server Agent, but my apps all feature (not
> scheduled) backup functionalities, as long as login/user management..
> database restore shoul'd usually not be a daly operation [=;-D ] , but is
> provided as well... one shot operation...
> again, personally I choose SQL-DMO API becouse I already provided it's
> dependencies for my companion database general management tool, but
> ADO/ADO.Net are good as well.. it all depends on your needs/API
> skill/preferred method...
> very
they
> actually you can even choose this solution, as long as your database all
> have the 'Auto close' property set... this option will actually close (and
> free) the physical database and transaction log files on the file system,
so
> that file copy operation (not SQL Server backup!) can be performed..
please
> keep in mind connection pool time, which usually requires about 1 minute
to
> effectively close...
> but, again... my personala advice is to perform dayly house-keeping
actions
> withour user's intervention.. that's to say: do what your users usually
> skip... automatically backup their data ...
> one draw back... SQLExpress, the replacement for MSDE in the SQL Server
2005
> code base, will remove SQL Server Agent from the SKU, so keep in mind this
> for the future.. personally I've still have to choose a scheduled
> alternative, but I'll provide it for sure.
> remember, you are supposed to provide all the manage/support
> functionnalities
> hand-holding?
> as MSDE is NOT Jet, you have a lot of extra features at a resonably price
> [=;-D], but you have to take some new sagacity in your work...
> nowdays, all our users do have some Access skill [potentially dangerous
> =;-D ], so you shoul'd even train your users a little more, but this is
> another story...
>
> Microsoft provides ton of information as
>
http://msdn.microsoft.com/library/de...eddingmsde.asp
>
http://www.microsoft.com/sql/techinf...swithmsdes.asp
> http://www.microsoft.com/sql/msde/te...ntegration.asp
>
http://msdn.microsoft.com/library/de...stsql_7b91.asp
> ...
> personally I do provide a separate setup for MSDE, with a home built user
> interface that takes care and handles all required parameter to then shell
> to the setup.exe boostrap installer...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Let's just focus on how to determine if i should even install MSDE in the
first place.
1. How do i check if SQL Server 2000/MSDE2000 is already installed?
- i've read 3 about 3 different registry keys, as well as checking a
process list, and checking the services list, as well as using SQL DMO.
What if SQLDMO is not registered (or registered properly)? Do i install it
first?
i can't just look for a process, because SQL may be stopped
i can't just look at the services list, because it may be disabled. Also, i
probably can't use that instance, since it's server settings can be
different than what i require. Or it may be a trial version that will be
expiring tomorrow.
i could just always blindly install a new instance. But what if an existing
instance name conflicts with mine? What if the conflicting instance name is
actually an instance i installed last time? How do i know it's me or a
co-incidentally named instance from some other vendor? What if i'm the
client machine is out of instances, how do i handle it with a zero-user
interaction method (since the user will not have the client tools; and no
idea how to deal with it).
What if there is an existing instance, and it's mine, but noboby knows the
sa password? Do i install a fresh instance because an existing instance is
locked out?
All these questions that need to be dealt with when someone actually has to
integrate MSDE.
Compare that to Jet: put a file in my %ApplicationData% folder. And Jet is
guaranteed to be installed on any OS that Microsoft still supports.