Mixing SQL Server and MSDE and authentication

We install MSDE2000 under a named instance and with SQL Server
authentication, and specify a password for 'sa'.
I recently had someone install it on a system that already had SQL Server
installed.
On that system, we could not connect to the named instance using the "sa"
user and password specified in the installer. Using ODBC, we kept getting
the error 18452 - Login failed for user 'sa'.
How does the presence of SQL Server affect this? Does the 'sa' password for
the SQL Server default instance take precedence?
hi,
"JJ" <jjjj@.nospam.com> ha scritto nel messaggio
news:uAPJ05LGEHA.2732@.tk2msftngp13.phx.gbl...
> We install MSDE2000 under a named instance and with SQL Server
> authentication, and specify a password for 'sa'.
> I recently had someone install it on a system that already had SQL Server
> installed.
> On that system, we could not connect to the named instance using the "sa"
> user and password specified in the installer. Using ODBC, we kept getting
> the error 18452 - Login failed for user 'sa'.
> How does the presence of SQL Server affect this? Does the 'sa' password
for
> the SQL Server default instance take precedence?
>
it shoul'd really not...
all specified settings are instance specific, the only difference is that
the 1st instance (default) will listen to default TCP/IP port 1433, while
all other ubsequent instances will pass throught UDP port 1434 if they have
been set to dinamically retrieve the port number... but this another
story...
if you can reproduce the problem and are "sure" [ =;-) ] that the MSDE
instance has been installed specifying the SECURITYMODE=SQL parameter, you
are wellcome to open a case with Microsoft PSS...
thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi again,
anyway, try checking the HKLM\Software\Microsoft\Microsoft SQL
Server\Instance Name\MSSQLServer\LoginMode registry key... it shoul'd be 2
or 0 for mixed mode authentication...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||That's the strange part - I did check that in the registry and it was set to
2, so I tried setting it to 0 (then stopped and restarted the MSDE instance)
and got the same error.
Unfortunately, I can't check it again because we ended up just using SQL
Server and uninstalling MSDE for the user with the problem. (It was
actually a mistake for the user to have done the install with MSDE because
usually if they have SQL Server already, we want them to just use that. I
can't think of a good reason for an end user to have both SQL Server and
MSDE installed on the same system. But I was curious to find out whether or
not it should have worked.)
It may indeed have been the dynamic port issue - I did not check the client
configuration to see if it was trying to go to a specific port (i.e. 1433).
But I would have thought it just wouldn't have connected since the server
name contained the instance name.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:c4ju2l$2h7scp$1@.ID-207518.news.uni-berlin.de...
> hi again,
> anyway, try checking the HKLM\Software\Microsoft\Microsoft SQL
> Server\Instance Name\MSSQLServer\LoginMode registry key... it shoul'd be 2
> or 0 for mixed mode authentication...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Mixed Security Mode for MSDE?

Hi all... MSDE installs with pure Windows Security. Can it be
reconfigured to use mixed security? Does somebody know how this can be
done?
I have tried it using Enterprise Manager in a scenario where that tool
was availeable due to presence of SQL 2K Standard, however, MSDE did
not accept a configuration change to mixed security mode.
Somebody's got a clue?
Tim
hi Tim,
"Tim" <TimWalter@.hotmail.com> ha scritto nel messaggio
news:ca5b4dee.0410200712.4615c0a7@.posting.google.c om
> Hi all... MSDE installs with pure Windows Security. Can it be
> reconfigured to use mixed security? Does somebody know how this can be
> done?
> I have tried it using Enterprise Manager in a scenario where that tool
> was availeable due to presence of SQL 2K Standard, however, MSDE did
> not accept a configuration change to mixed security mode.
> Somebody's got a clue?
after modifying the Security mode via EM, you have to restart the server...
anyway, you can have a look at
http://support.microsoft.com/default...en-us;Q285097, in the part
regarding the window registry...
for your convenience, at the link following my sign., you can find a user
interface similar to Entrprise Manager where you can graphically perform
this task too..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Missing Tools after Setup

In the MSDE BOOKs there is a list of utilities and there directorys that i
should have:
x:\Program Files\Microsoft SQL Server\MSSQL\Binn1
-bcp2
-console
-isql2
-sqlagent
-sqldiag
-sqlmaint
-sqlservr
-vswitch
x:\Program Files\Microsoft SQL Server\80\Tools\Binn
-bcp2
-dtsrun
-dtswiz
-isql2
-isqlw
-itwiz
-odbccmpt
-osql
-rebuildm
-sqlftwiz
x:\Program Files\Microsoft SQL Server\80\Com
-distrib3
-logread3
-replmerg3
-snapshot3
x:\Program Files\Common Files\Microsoft Shared\Service Manager
-scm
x:\Program Files\Common Files\Microsoft Shared\?
-regxmlss
That is the list that i should have, here is the list that i do have:
x:\Program Files\Microsoft SQL Server\MSSQL\Binn1
-cmdwrap.exe
-sqlagent.exe
-sqlmaint.exe
-sqlservr.exe
x:\Program Files\Microsoft SQL Server\80\Tools\Binn
-bcp.exe
-cnfgsvr.exe
-dcomscm.exe
-DTSRUN.exe
-OSQL.exe
-scm.exe
-sqladhlp.exe
-sqlmangr.exe
-SVRNETCN.exe
x:\Program Files\Microsoft SQL Server\80\Com
-DISTRIB.exe
-replmerg.exe
-snapshot.exe
x:\Program Files\Common Files\Microsoft Shared\Service Manager
NO Service Manager Directory Exists
x:\Program Files\Common Files\Microsoft Shared\?
It says that this all should have been installed on the install by default
here is how installed
i moved my command prompt to the directory containing the setup folder
(C:\MSDERela)
then i ran
setup SAPWD="??"
this installed fine
what did i do wrong?
ALso i am very new to databases
the only thing that i have ever used is access which is pretty cut and dry.
I have had a lot of trouble finding simple tutorials for a beginner if any
one knows of one please let me know.
In the mean time a couple of quick questions:
1) does a database file have to be in the directory: C:\Program
Files\Microsoft SQL Server\MSSQL\Data
in order for it to work?
2) other than osql how can i access my data? The Books say that thre is a
SQL Server Query analyzer but could not find it (see beginning of post)
thanks Again for the help
WStoreyII
Hi,
I'm not sure which MSDE book you are referring to but the tools you mention
are those that are installed with SQL Server, not with the MSDE. The MSDE
ships with limited tools. The intention is that your application will do the
work. We have some tools you could try at our site. Others are at:
http://www.microsoft.com/sql/msde/pa.../default.aspx.
The data files do not have to be in a specific folder but you will find life
much easier if you have them all in a particular folder.
If you are going to be doing development for the MSDE, I'd strongly suggest
you purchase a copy of the SQL Server Developer Edition. I gather it's
currently only around $49 USD and provides a wealth of things you'll find
useful during development.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"WStoreyII" <WStoreyII@.discussions.microsoft.com> wrote in message
news:9109A07D-4093-4473-AB0A-CFDFD9611C06@.microsoft.com...
> In the MSDE BOOKs there is a list of utilities and there directorys that i
> should have:
> x:\Program Files\Microsoft SQL Server\MSSQL\Binn1
> -bcp2
> -console
> -isql2
> -sqlagent
> -sqldiag
> -sqlmaint
> -sqlservr
> -vswitch
> x:\Program Files\Microsoft SQL Server\80\Tools\Binn
> -bcp2
> -dtsrun
> -dtswiz
> -isql2
> -isqlw
> -itwiz
> -odbccmpt
> -osql
> -rebuildm
> -sqlftwiz
> x:\Program Files\Microsoft SQL Server\80\Com
> -distrib3
> -logread3
> -replmerg3
> -snapshot3
> x:\Program Files\Common Files\Microsoft Shared\Service Manager
> -scm
> x:\Program Files\Common Files\Microsoft Shared\?
> -regxmlss
> That is the list that i should have, here is the list that i do have:
> x:\Program Files\Microsoft SQL Server\MSSQL\Binn1
> -cmdwrap.exe
> -sqlagent.exe
> -sqlmaint.exe
> -sqlservr.exe
> x:\Program Files\Microsoft SQL Server\80\Tools\Binn
> -bcp.exe
> -cnfgsvr.exe
> -dcomscm.exe
> -DTSRUN.exe
> -OSQL.exe
> -scm.exe
> -sqladhlp.exe
> -sqlmangr.exe
> -SVRNETCN.exe
> x:\Program Files\Microsoft SQL Server\80\Com
> -DISTRIB.exe
> -replmerg.exe
> -snapshot.exe
> x:\Program Files\Common Files\Microsoft Shared\Service Manager
> NO Service Manager Directory Exists
> x:\Program Files\Common Files\Microsoft Shared\?
> It says that this all should have been installed on the install by default
> here is how installed
> i moved my command prompt to the directory containing the setup folder
> (C:\MSDERela)
> then i ran
> setup SAPWD="??"
> this installed fine
> what did i do wrong?
> ALso i am very new to databases
> the only thing that i have ever used is access which is pretty cut and
> dry.
> I have had a lot of trouble finding simple tutorials for a beginner if any
> one knows of one please let me know.
> In the mean time a couple of quick questions:
> 1) does a database file have to be in the directory: C:\Program
> Files\Microsoft SQL Server\MSSQL\Data
> in order for it to work?
> 2) other than osql how can i access my data? The Books say that thre is a
> SQL Server Query analyzer but could not find it (see beginning of post)
> thanks Again for the help
> WStoreyII
>
|||Greg's correct. MSDE was not intended to ship
with the full compliment of (management) tools
as SQL Server is.
If you're want to get up and running as quick as
possible then Microsoft Access (2000 and above)
is an excellent interface to MSDE for your own
internal development purposes. Micrsoft press has
a decent book on Access Data Projects also.
If you're wanting to use MSDE to develop a
distributable application, then you'll need to
be coding everything yourself.
Good luck.

missing tables and stored procedures after restoring database

I have been running a desktop PC with MSDE 1.0 installed. I had made a
backup of the data using a t-sql command backup database ... Since this PC
had crashed and has subsequently been rebuilt, I have restored the database
but found that vital tables and stored procedures are missing. The missing
tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
into the database as user sa.
Any help in recovering those missing tables and stored procedures would be
greatly appreciated.
use restore headeronly command on the backup file To check when the
backup was taken looks like you haven't backed up the database when
you created the tables whcih you said you were missing or you have
restored
an earlier backup not the latest once since you are the Sysadmin u can
view all the tables created with any login and if you want to have
information
about the latest backup taken check out the one with
Use Msdb
select *from backupset
order by backup_finish_date desc
|||If they're missing after a restore then they were not there at the time
of the back and are now gone forever (presumedly they were created after
the backup you used to restore the DB). Do you have any later backups?
BTW, every table & proc in a DB has an owner. To be sure you're just
not missing it in whatever client-side tool you're using execute this T-SQL:
select o.[name] as tablename, u.[name] as ownername, type
from dbo.sysobjects as o
inner join dbo.sysusers as u on o.uid = o.uid
order by type, u.[name], o.[name]
Have a look to see if you can see the proc (type P) or the table (type
U) somewhere in the resultset. Also are you sure these "vital" tables &
procs were in the same database and weren't in another database that was
being referenced from your database? For example,
use A
go
select * from B.dbo.MyVitalTable
exec B.dbo.MyVitalProc
If that was true then they wouldn't be included in the backup (and
therefore subsequent restore) of your database.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Old Paulie wrote:

>I have been running a desktop PC with MSDE 1.0 installed. I had made a
>backup of the data using a t-sql command backup database ... Since this PC
>had crashed and has subsequently been rebuilt, I have restored the database
>but found that vital tables and stored procedures are missing. The missing
>tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
>into the database as user sa.
>Any help in recovering those missing tables and stored procedures would be
>greatly appreciated.
>
|||saradhi,
Thanks for the advice, I have checked the backupset table in the Msdb
database and that only describes one backup taken last year. The backup at
that time probably would not have included these missing tables. I had
however made several backups and their is no proof of these in this table. I
need to obviously review the way I back up since it appears I am doing
something fundamentally wrong. Any advice on backing up for future reference
would be greatly appreciated. Should I be backing up the other databases
that are created upon install of msde, ie Master, Msdb etc ...?
"saradhi" wrote:

> use restore headeronly command on the backup file To check when the
> backup was taken looks like you haven't backed up the database when
> you created the tables whcih you said you were missing or you have
> restored
> an earlier backup not the latest once since you are the Sysadmin u can
> view all the tables created with any login and if you want to have
> information
> about the latest backup taken check out the one with
> Use Msdb
> select *from backupset
> order by backup_finish_date desc
>
|||Hi Mike,
I am quite sure that the vital tables belonged to my database as I had
created no others! I did run your query and did not find and records
relating to the missing tables and stored procedures. From your advice and
'saradhi's', it is obvious now that my backup was flawed and I am doing
something wrong Thank you for your assistance.
"Mike Hodgson" wrote:
[vbcol=seagreen]
> If they're missing after a restore then they were not there at the time
> of the back and are now gone forever (presumedly they were created after
> the backup you used to restore the DB). Do you have any later backups?
> BTW, every table & proc in a DB has an owner. To be sure you're just
> not missing it in whatever client-side tool you're using execute this T-SQL:
> select o.[name] as tablename, u.[name] as ownername, type
> from dbo.sysobjects as o
> inner join dbo.sysusers as u on o.uid = o.uid
> order by type, u.[name], o.[name]
> Have a look to see if you can see the proc (type P) or the table (type
> U) somewhere in the resultset. Also are you sure these "vital" tables &
> procs were in the same database and weren't in another database that was
> being referenced from your database? For example,
> use A
> go
> select * from B.dbo.MyVitalTable
> exec B.dbo.MyVitalProc
> If that was true then they wouldn't be included in the backup (and
> therefore subsequent restore) of your database.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Old Paulie wrote:
|||One last chance. Perhaps your backup file has several backups on it, and you restore the first one?
Check using RESTORE HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...[vbcol=seagreen]
> Hi Mike,
> I am quite sure that the vital tables belonged to my database as I had
> created no others! I did run your query and did not find and records
> relating to the missing tables and stored procedures. From your advice and
> 'saradhi's', it is obvious now that my backup was flawed and I am doing
> something wrong Thank you for your assistance.
> "Mike Hodgson" wrote:
|||Tibor,
It turns out that my backup DID include several backup sets and I was
therefore able to restore the most recent backup set to get back all missing
tables and procedures.
THANK YOU VERY MUCH!
"Tibor Karaszi" wrote:

> One last chance. Perhaps your backup file has several backups on it, and you restore the first one?
> Check using RESTORE HEADERONLY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
> news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...
>

missing tables and stored procedures after restoring database

I have been running a desktop PC with MSDE 1.0 installed. I had made a
backup of the data using a t-sql command backup database ... Since this PC
had crashed and has subsequently been rebuilt, I have restored the database
but found that vital tables and stored procedures are missing. The missing
tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
into the database as user sa.
Any help in recovering those missing tables and stored procedures would be
greatly appreciated.use restore headeronly command on the backup file To check when the
backup was taken looks like you haven't backed up the database when
you created the tables whcih you said you were missing or you have
restored
an earlier backup not the latest once since you are the Sysadmin u can
view all the tables created with any login and if you want to have
information
about the latest backup taken check out the one with
Use Msdb
select *from backupset
order by backup_finish_date desc|||If they're missing after a restore then they were not there at the time
of the back and are now gone forever (presumedly they were created after
the backup you used to restore the DB). Do you have any later backups?
BTW, every table & proc in a DB has an owner. To be sure you're just
not missing it in whatever client-side tool you're using execute this T-SQL:
select o.[name] as tablename, u.[name] as ownername, type
from dbo.sysobjects as o
inner join dbo.sysusers as u on o.uid = o.uid
order by type, u.[name], o.[name]
Have a look to see if you can see the proc (type P) or the table (type
U) somewhere in the resultset. Also are you sure these "vital" tables &
procs were in the same database and weren't in another database that was
being referenced from your database? For example,
use A
go
select * from B.dbo.MyVitalTable
exec B.dbo.MyVitalProc
If that was true then they wouldn't be included in the backup (and
therefore subsequent restore) of your database.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Old Paulie wrote:

>I have been running a desktop PC with MSDE 1.0 installed. I had made a
>backup of the data using a t-sql command backup database ... Since this PC
>had crashed and has subsequently been rebuilt, I have restored the database
>but found that vital tables and stored procedures are missing. The missing
>tables were not allegedly owned by dbo but perhaps had no owner(?) PS I lo
g
>into the database as user sa.
>Any help in recovering those missing tables and stored procedures would be
>greatly appreciated.
>|||saradhi,
Thanks for the advice, I have checked the backupset table in the Msdb
database and that only describes one backup taken last year. The backup at
that time probably would not have included these missing tables. I had
however made several backups and their is no proof of these in this table.
I
need to obviously review the way I back up since it appears I am doing
something fundamentally wrong. Any advice on backing up for future referenc
e
would be greatly appreciated. Should I be backing up the other databases
that are created upon install of msde, ie Master, Msdb etc ...?
"saradhi" wrote:

> use restore headeronly command on the backup file To check when the
> backup was taken looks like you haven't backed up the database when
> you created the tables whcih you said you were missing or you have
> restored
> an earlier backup not the latest once since you are the Sysadmin u can
> view all the tables created with any login and if you want to have
> information
> about the latest backup taken check out the one with
> Use Msdb
> select *from backupset
> order by backup_finish_date desc
>|||Hi Mike,
I am quite sure that the vital tables belonged to my database as I had
created no others! I did run your query and did not find and records
relating to the missing tables and stored procedures. From your advice and
'saradhi's', it is obvious now that my backup was flawed and I am doing
something wrong Thank you for your assistance.
"Mike Hodgson" wrote:
[vbcol=seagreen]
> If they're missing after a restore then they were not there at the time
> of the back and are now gone forever (presumedly they were created after
> the backup you used to restore the DB). Do you have any later backups?
> BTW, every table & proc in a DB has an owner. To be sure you're just
> not missing it in whatever client-side tool you're using execute this T-SQ
L:
> select o.[name] as tablename, u.[name] as ownername, type
> from dbo.sysobjects as o
> inner join dbo.sysusers as u on o.uid = o.uid
> order by type, u.[name], o.[name]
> Have a look to see if you can see the proc (type P) or the table (type
> U) somewhere in the resultset. Also are you sure these "vital" tables &
> procs were in the same database and weren't in another database that was
> being referenced from your database? For example,
> use A
> go
> select * from B.dbo.MyVitalTable
> exec B.dbo.MyVitalProc
> If that was true then they wouldn't be included in the backup (and
> therefore subsequent restore) of your database.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Old Paulie wrote:
>|||One last chance. Perhaps your backup file has several backups on it, and you
restore the first one?
Check using RESTORE HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...[vbcol=seagreen]
> Hi Mike,
> I am quite sure that the vital tables belonged to my database as I had
> created no others! I did run your query and did not find and records
> relating to the missing tables and stored procedures. From your advice an
d
> 'saradhi's', it is obvious now that my backup was flawed and I am doing
> something wrong Thank you for your assistance.
> "Mike Hodgson" wrote:
>|||Tibor,
It turns out that my backup DID include several backup sets and I was
therefore able to restore the most recent backup set to get back all missing
tables and procedures.
THANK YOU VERY MUCH!
"Tibor Karaszi" wrote:

> One last chance. Perhaps your backup file has several backups on it, and y
ou restore the first one?
> Check using RESTORE HEADERONLY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
> news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...
>

missing tables and stored procedures after restoring database

I have been running a desktop PC with MSDE 1.0 installed. I had made a
backup of the data using a t-sql command backup database ... Since this PC
had crashed and has subsequently been rebuilt, I have restored the database
but found that vital tables and stored procedures are missing. The missing
tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
into the database as user sa.
Any help in recovering those missing tables and stored procedures would be
greatly appreciated.use restore headeronly command on the backup file To check when the
backup was taken looks like you haven't backed up the database when
you created the tables whcih you said you were missing or you have
restored
an earlier backup not the latest once since you are the Sysadmin u can
view all the tables created with any login and if you want to have
information
about the latest backup taken check out the one with
Use Msdb
select *from backupset
order by backup_finish_date desc|||This is a multi-part message in MIME format.
--080807080807040609090709
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
If they're missing after a restore then they were not there at the time
of the back and are now gone forever (presumedly they were created after
the backup you used to restore the DB). Do you have any later backups?
BTW, every table & proc in a DB has an owner. To be sure you're just
not missing it in whatever client-side tool you're using execute this T-SQL:
select o.[name] as tablename, u.[name] as ownername, type
from dbo.sysobjects as o
inner join dbo.sysusers as u on o.uid = o.uid
order by type, u.[name], o.[name]
Have a look to see if you can see the proc (type P) or the table (type
U) somewhere in the resultset. Also are you sure these "vital" tables &
procs were in the same database and weren't in another database that was
being referenced from your database? For example,
use A
go
select * from B.dbo.MyVitalTable
exec B.dbo.MyVitalProc
If that was true then they wouldn't be included in the backup (and
therefore subsequent restore) of your database.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Old Paulie wrote:
>I have been running a desktop PC with MSDE 1.0 installed. I had made a
>backup of the data using a t-sql command backup database ... Since this PC
>had crashed and has subsequently been rebuilt, I have restored the database
>but found that vital tables and stored procedures are missing. The missing
>tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
>into the database as user sa.
>Any help in recovering those missing tables and stored procedures would be
>greatly appreciated.
>
--080807080807040609090709
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>If they're missing after a restore then they were not there at the
time of the back and are now gone forever (presumedly they were created
after the backup you used to restore the DB). Do you have any later
backups?<br>
<br>
BTW, every table & proc in a DB has an owner. To be sure you're
just not missing it in whatever client-side tool you're using execute
this T-SQL:<br>
</tt>
<blockquote><tt>select o.[name] as tablename, u.[name] as ownername,
type<br>
from dbo.sysobjects as o<br>
   inner join dbo.sysusers as u on o.uid = o.uid<br>
order by type, u.[name], o.[name]<br>
</tt></blockquote>
<tt>Have a look to see if you can see the proc (type P) or the table
(type U) somewhere in the resultset. Also are you sure these "vital"
tables & procs were in the same database and weren't in another
database that was being referenced from your database? For example,<br>
</tt>
<blockquote><tt>use A<br>
go<br>
<br>
select * from B.dbo.MyVitalTable<br>
exec B.dbo.MyVitalProc<br>
</tt></blockquote>
<tt>If that was true then they wouldn't be included in the backup (and
therefore subsequent restore) of your database.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Old Paulie wrote:
<blockquote cite="mid99FCEEAA-D592-4D73-AD2D-946AD7C71952@.microsoft.com"
type="cite">
<pre wrap="">I have been running a desktop PC with MSDE 1.0 installed. I had made a
backup of the data using a t-sql command backup database ... Since this PC
had crashed and has subsequently been rebuilt, I have restored the database
but found that vital tables and stored procedures are missing. The missing
tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
into the database as user sa.
Any help in recovering those missing tables and stored procedures would be
greatly appreciated.
</pre>
</blockquote>
</body>
</html>
--080807080807040609090709--|||saradhi,
Thanks for the advice, I have checked the backupset table in the Msdb
database and that only describes one backup taken last year. The backup at
that time probably would not have included these missing tables. I had
however made several backups and their is no proof of these in this table. I
need to obviously review the way I back up since it appears I am doing
something fundamentally wrong. Any advice on backing up for future reference
would be greatly appreciated. Should I be backing up the other databases
that are created upon install of msde, ie Master, Msdb etc ...?
"saradhi" wrote:
> use restore headeronly command on the backup file To check when the
> backup was taken looks like you haven't backed up the database when
> you created the tables whcih you said you were missing or you have
> restored
> an earlier backup not the latest once since you are the Sysadmin u can
> view all the tables created with any login and if you want to have
> information
> about the latest backup taken check out the one with
> Use Msdb
> select *from backupset
> order by backup_finish_date desc
>|||Hi Mike,
I am quite sure that the vital tables belonged to my database as I had
created no others! I did run your query and did not find and records
relating to the missing tables and stored procedures. From your advice and
'saradhi's', it is obvious now that my backup was flawed and I am doing
something wrong:( Thank you for your assistance.
"Mike Hodgson" wrote:
> If they're missing after a restore then they were not there at the time
> of the back and are now gone forever (presumedly they were created after
> the backup you used to restore the DB). Do you have any later backups?
> BTW, every table & proc in a DB has an owner. To be sure you're just
> not missing it in whatever client-side tool you're using execute this T-SQL:
> select o.[name] as tablename, u.[name] as ownername, type
> from dbo.sysobjects as o
> inner join dbo.sysusers as u on o.uid = o.uid
> order by type, u.[name], o.[name]
> Have a look to see if you can see the proc (type P) or the table (type
> U) somewhere in the resultset. Also are you sure these "vital" tables &
> procs were in the same database and weren't in another database that was
> being referenced from your database? For example,
> use A
> go
> select * from B.dbo.MyVitalTable
> exec B.dbo.MyVitalProc
> If that was true then they wouldn't be included in the backup (and
> therefore subsequent restore) of your database.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Old Paulie wrote:
> >I have been running a desktop PC with MSDE 1.0 installed. I had made a
> >backup of the data using a t-sql command backup database ... Since this PC
> >had crashed and has subsequently been rebuilt, I have restored the database
> >but found that vital tables and stored procedures are missing. The missing
> >tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
> >into the database as user sa.
> >
> >Any help in recovering those missing tables and stored procedures would be
> >greatly appreciated|||One last chance. Perhaps your backup file has several backups on it, and you restore the first one?
Check using RESTORE HEADERONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...
> Hi Mike,
> I am quite sure that the vital tables belonged to my database as I had
> created no others! I did run your query and did not find and records
> relating to the missing tables and stored procedures. From your advice and
> 'saradhi's', it is obvious now that my backup was flawed and I am doing
> something wrong:( Thank you for your assistance.
> "Mike Hodgson" wrote:
>> If they're missing after a restore then they were not there at the time
>> of the back and are now gone forever (presumedly they were created after
>> the backup you used to restore the DB). Do you have any later backups?
>> BTW, every table & proc in a DB has an owner. To be sure you're just
>> not missing it in whatever client-side tool you're using execute this T-SQL:
>> select o.[name] as tablename, u.[name] as ownername, type
>> from dbo.sysobjects as o
>> inner join dbo.sysusers as u on o.uid = o.uid
>> order by type, u.[name], o.[name]
>> Have a look to see if you can see the proc (type P) or the table (type
>> U) somewhere in the resultset. Also are you sure these "vital" tables &
>> procs were in the same database and weren't in another database that was
>> being referenced from your database? For example,
>> use A
>> go
>> select * from B.dbo.MyVitalTable
>> exec B.dbo.MyVitalProc
>> If that was true then they wouldn't be included in the backup (and
>> therefore subsequent restore) of your database.
>> --
>> *mike hodgson*
>> blog: http://sqlnerd.blogspot.com
>>
>> Old Paulie wrote:
>> >I have been running a desktop PC with MSDE 1.0 installed. I had made a
>> >backup of the data using a t-sql command backup database ... Since this PC
>> >had crashed and has subsequently been rebuilt, I have restored the database
>> >but found that vital tables and stored procedures are missing. The missing
>> >tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
>> >into the database as user sa.
>> >
>> >Any help in recovering those missing tables and stored procedures would be
>> >greatly appreciated|||Tibor,
It turns out that my backup DID include several backup sets and I was
therefore able to restore the most recent backup set to get back all missing
tables and procedures.
THANK YOU VERY MUCH!
"Tibor Karaszi" wrote:
> One last chance. Perhaps your backup file has several backups on it, and you restore the first one?
> Check using RESTORE HEADERONLY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Old Paulie" <OldPaulie@.discussions.microsoft.com> wrote in message
> news:BADA21C1-5F74-4E9A-83FD-779132C78585@.microsoft.com...
> > Hi Mike,
> >
> > I am quite sure that the vital tables belonged to my database as I had
> > created no others! I did run your query and did not find and records
> > relating to the missing tables and stored procedures. From your advice and
> > 'saradhi's', it is obvious now that my backup was flawed and I am doing
> > something wrong:( Thank you for your assistance.
> >
> > "Mike Hodgson" wrote:
> >
> >> If they're missing after a restore then they were not there at the time
> >> of the back and are now gone forever (presumedly they were created after
> >> the backup you used to restore the DB). Do you have any later backups?
> >>
> >> BTW, every table & proc in a DB has an owner. To be sure you're just
> >> not missing it in whatever client-side tool you're using execute this T-SQL:
> >>
> >> select o.[name] as tablename, u.[name] as ownername, type
> >> from dbo.sysobjects as o
> >> inner join dbo.sysusers as u on o.uid = o.uid
> >> order by type, u.[name], o.[name]
> >>
> >> Have a look to see if you can see the proc (type P) or the table (type
> >> U) somewhere in the resultset. Also are you sure these "vital" tables &
> >> procs were in the same database and weren't in another database that was
> >> being referenced from your database? For example,
> >>
> >> use A
> >> go
> >>
> >> select * from B.dbo.MyVitalTable
> >> exec B.dbo.MyVitalProc
> >>
> >> If that was true then they wouldn't be included in the backup (and
> >> therefore subsequent restore) of your database.
> >>
> >> --
> >> *mike hodgson*
> >> blog: http://sqlnerd.blogspot.com
> >>
> >>
> >>
> >> Old Paulie wrote:
> >>
> >> >I have been running a desktop PC with MSDE 1.0 installed. I had made a
> >> >backup of the data using a t-sql command backup database ... Since this PC
> >> >had crashed and has subsequently been rebuilt, I have restored the database
> >> >but found that vital tables and stored procedures are missing. The missing
> >> >tables were not allegedly owned by dbo but perhaps had no owner(?) PS I log
> >> >into the database as user sa.
> >> >
> >> >Any help in recovering those missing tables and stored procedures would be
> >> >greatly appreciated
>

missing password

Hi there,
I have run up a WSUS server and used msde for the sql database and
sods law I thought I'd be clever and use a different password to
normal which at the time I thought I'd remember and I've completely
forgot it.
By any chance does anyone know of a way I can now retrieve this as I
would like to link up the server in enterprise manager.
Any help would be appreciated, even if its too say I've got no chance.
Many thanks.Login to MSDE using windows authentication and change the password.
Steps:-
1. Login into MSDE machine using a Admin account
2. go to command prompt
3. Type OSQL -SSQL_Servername -E (enter - This will got to a SQL prompt)
4. Execute SP_Password NULL,'New_sa_password','SA'
This will change the SA password to the password you provide in step 4.
Thanks
Hari
"CJC" wrote:

> Hi there,
> I have run up a WSUS server and used msde for the sql database and
> sods law I thought I'd be clever and use a different password to
> normal which at the time I thought I'd remember and I've completely
> forgot it.
> By any chance does anyone know of a way I can now retrieve this as I
> would like to link up the server in enterprise manager.
> Any help would be appreciated, even if its too say I've got no chance.
> Many thanks.
>|||Thanks for your reply, I have only had chance to look at it.
This command for changing the password, will it matter what username I
initially used when setting it up, as I probably didnt use SA? Or is
this command saying I am creating this password for this username, so
in theory could I put 'joe.bloggs' instead of 'sa'
Also I'm terrible for being over cautious, is this likely to screw
anything else up, as currently the server is running fine, its just I
cannot link WSUS into enterprise manager.
Thanks again.
On 2 Feb, 20:54, Hari Prasad <HariPra...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Login to MSDE using windows authentication and change the password.
> Steps:-
> 1. Login into MSDE machine using a Admin account
> 2. go to command prompt
> 3. Type OSQL -SSQL_Servername -E (enter - This will got to a SQL prompt)
> 4. Execute SP_Password NULL,'New_sa_password','SA'
> This will change the SA password to the password you provide in step 4.
> Thanks
> Hari
> "CJC" wrote:
>
>
>
>

missing password

Hi there,
I have run up a WSUS server and used msde for the sql database and
sods law I thought I'd be clever and use a different password to
normal which at the time I thought I'd remember and I've completely
forgot it.
By any chance does anyone know of a way I can now retrieve this as I
would like to link up the server in enterprise manager.
Any help would be appreciated, even if its too say I've got no chance.
Many thanks.
Thanks for your reply, I have only had chance to look at it.
This command for changing the password, will it matter what username I
initially used when setting it up, as I probably didnt use SA? Or is
this command saying I am creating this password for this username, so
in theory could I put 'joe.bloggs' instead of 'sa'
Also I'm terrible for being over cautious, is this likely to screw
anything else up, as currently the server is running fine, its just I
cannot link WSUS into enterprise manager.
Thanks again.
On 2 Feb, 20:54, Hari Prasad <HariPra...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Login to MSDE using windows authentication and change the password.
> Steps:-
> 1. Login into MSDE machine using a Admin account
> 2. go to command prompt
> 3. Type OSQL -SSQL_Servername -E (enter - This will got to a SQL prompt)
> 4. Execute SP_Password NULL,'New_sa_password','SA'
> This will change the SA password to the password you provide in step 4.
> Thanks
> Hari
> "CJC" wrote:
>
>

missing password

Hi there,
I have run up a WSUS server and used msde for the sql database and
sods law I thought I'd be clever and use a different password to
normal which at the time I thought I'd remember and I've completely
forgot it.
By any chance does anyone know of a way I can now retrieve this as I
would like to link up the server in enterprise manager.
Any help would be appreciated, even if its too say I've got no chance.
Many thanks.Login to MSDE using windows authentication and change the password.
Steps:-
1. Login into MSDE machine using a Admin account
2. go to command prompt
3. Type OSQL -SSQL_Servername -E (enter - This will got to a SQL prompt)
4. Execute SP_Password NULL,'New_sa_password','SA'
This will change the SA password to the password you provide in step 4.
Thanks
Hari
"CJC" wrote:
> Hi there,
> I have run up a WSUS server and used msde for the sql database and
> sods law I thought I'd be clever and use a different password to
> normal which at the time I thought I'd remember and I've completely
> forgot it.
> By any chance does anyone know of a way I can now retrieve this as I
> would like to link up the server in enterprise manager.
> Any help would be appreciated, even if its too say I've got no chance.
> Many thanks.
>|||Thanks for your reply, I have only had chance to look at it.
This command for changing the password, will it matter what username I
initially used when setting it up, as I probably didnt use SA? Or is
this command saying I am creating this password for this username, so
in theory could I put 'joe.bloggs' instead of 'sa'
Also I'm terrible for being over cautious, is this likely to screw
anything else up, as currently the server is running fine, its just I
cannot link WSUS into enterprise manager.
Thanks again.
On 2 Feb, 20:54, Hari Prasad <HariPra...@.discussions.microsoft.com>
wrote:
> Login to MSDE using windows authentication and change the password.
> Steps:-
> 1. Login into MSDE machine using a Admin account
> 2. go to command prompt
> 3. Type OSQL -SSQL_Servername -E (enter - This will got to a SQL prompt)
> 4. Execute SP_Password NULL,'New_sa_password','SA'
> This will change the SA password to the password you provide in step 4.
> Thanks
> Hari
> "CJC" wrote:
> > Hi there,
> > I have run up a WSUS server and used msde for the sql database and
> > sods law I thought I'd be clever and use a different password to
> > normal which at the time I thought I'd remember and I've completely
> > forgot it.
> > By any chance does anyone know of a way I can now retrieve this as I
> > would like to link up the server in enterprise manager.
> > Any help would be appreciated, even if its too say I've got no chance.
> > Many thanks.

missing Enterprise Manager after installation of MSDE

My MSAccess application uses MSDE. For creating the setup, I use
InstallShield Developer 8, which installs the MSDE. Among the features for
MSDE, included in the redistributables is the Enterprise Manager.
But after executing the setup I cannot find the Enterprise Manager.
I think, I need the Enterprise Manager to connect to the Database to which
my Access application must be connected.
Or does there exist another method, to set up the connection?
Thank for help,
Rudolf
I didnt think MSDE came with Enterprise Manager at all. Including it in release files generated by InstallShield Developer presupposes that it comes with MSDE. Can someone confirm that it Enterprise Manager does come with MSDE (2000 Release A)?
Mark Ferguson
"Rudolf Hennemuth" wrote:

> My MSAccess application uses MSDE. For creating the setup, I use
> InstallShield Developer 8, which installs the MSDE. Among the features for
> MSDE, included in the redistributables is the Enterprise Manager.
> But after executing the setup I cannot find the Enterprise Manager.
> I think, I need the Enterprise Manager to connect to the Database to which
> my Access application must be connected.
> Or does there exist another method, to set up the connection?
> Thank for help,
> Rudolf
>
>
|||EM does *not* come with MSDE. You get a database without much interface.
jan
"Mark Ferguson" <MarkFerguson@.discussions.microsoft.com> wrote in message
news:C71FA1B3-2E82-4DF2-9DCB-D9278C18AA63@.microsoft.com...
> I didnt think MSDE came with Enterprise Manager at all. Including it in
release files generated by InstallShield Developer presupposes that it comes
with MSDE. Can someone confirm that it Enterprise Manager does come with
MSDE (2000 Release A)?[vbcol=seagreen]
> --
> Mark Ferguson
>
> "Rudolf Hennemuth" wrote:
for[vbcol=seagreen]
which[vbcol=seagreen]
|||hi,
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> ha scritto nel messaggio
news:u%23sJmh6UEHA.2408@.tk2msftngp13.phx.gbl...
> EM does *not* come with MSDE. You get a database without much interface.
and in addition to Jan answer, including Enterprise Manager component and
related MMC snap-in dependencies in a personal deployment package violates
Microsoft EULA... at least the standard one
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
|||Hello,
There is no Entreprise manager for MSDE (even if a SQL Manager from SQL2000
can work with MSDE). It is one of the differences between SQL Server and
MSDE (no graphical tool).
You can use SQL DMO to make a lot of things (create database, restore a
backup...)
Marc Allard
Allcomp
"Rudolf Hennemuth" <Rudolf.Hennemuth@.diese-gmbh.de> a crit dans le message
de news:ufwZBX4UEHA.2816@.TK2MSFTNGP11.phx.gbl...
> My MSAccess application uses MSDE. For creating the setup, I use
> InstallShield Developer 8, which installs the MSDE. Among the features for
> MSDE, included in the redistributables is the Enterprise Manager.
> But after executing the setup I cannot find the Enterprise Manager.
> I think, I need the Enterprise Manager to connect to the Database to which
> my Access application must be connected.
> Or does there exist another method, to set up the connection?
> Thank for help,
> Rudolf
>
|||There is another way...Microsoft freely distributes a SQl Server Web Data Administrator. See:
http://www.microsoft.com/downloads/d...displaylang=en
Mark Ferguson
"Rudolf Hennemuth" wrote:

> My MSAccess application uses MSDE. For creating the setup, I use
> InstallShield Developer 8, which installs the MSDE. Among the features for
> MSDE, included in the redistributables is the Enterprise Manager.
> But after executing the setup I cannot find the Enterprise Manager.
> I think, I need the Enterprise Manager to connect to the Database to which
> my Access application must be connected.
> Or does there exist another method, to set up the connection?
> Thank for help,
> Rudolf
>
>
|||Thanks,
this was the best hint, which solve my problem.
Rudolf
"Mark Ferguson" <MarkFerguson@.discussions.microsoft.com> schrieb im
Newsbeitrag news:20B68A31-4DA9-4281-AE16-40D8DCFFF9CE@.microsoft.com...
> There is another way...Microsoft freely distributes a SQl Server Web Data
Administrator. See:
>
http://www.microsoft.com/downloads/d...displaylang=en[vbcol=seagreen]
> --
> Mark Ferguson
>
> "Rudolf Hennemuth" wrote:
for[vbcol=seagreen]
which[vbcol=seagreen]