Modified Stored Procedure Scripts Wrong ANSI Settings

Query Analyzer for 2000 was smart enough to realize that a stored procedure was created with the ANSI settings like such:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER OFF

go

However, when I use SSMS to modify a stored procedure from the context menu in the object explorer, instead get:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

Which is not even the default for my connection.

I'm working on a legacy code base with tons of double quoted strings, so I really need the ANSI settings to stay where they were without fighting the SQL editor about it.

Any suggestions? Is this a bug? I'm using SQL2005 RTM.

Matthew Martin

I would do this from your script in the query pane:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE <yourprocedure>
...

SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF

GO

Should be something like this at the end.

HTH, Jens Suessmeyer.

|||

True, I can revert to OSQL and generate the script correctly with my keyboard, the problem is that EM used to be able to script out a stored procedure with the correct settings (namely the settings that were in effect when the stored procedure was last altered), now with SSMS, right clicking on a stored procedure and selecting modify will script out a ALTER PROCEDURE script with the wrong settings.

Either I've hit a SSMS bug or I haven't found the 'Make-it-work-the-way-it-used-to' check box. I'm hoping it is the later.

Matthew Martin

|||

This is a known issue in SSMS. It will be fixed in SP1.

Modified Date For Stored Procedures

In SQL Server is there a way to know when a procedure was last
modified? I only see the "Create Date" column on the Enterprise
Manager.

Thanks Experts!jjone99 (jjone99@.hotmail.com) writes:
> In SQL Server is there a way to know when a procedure was last
> modified? I only see the "Create Date" column on the Enterprise
> Manager.

No, in SQL 2000 there is not.

This is addressed in the next version of SQL Server, currently in beta.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

moderate sql procedure question

In SQL Server 20:

Lets say I have a table for address. I create a stored procedure to update any value in the database, i.e.:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50),
@.new_V_Address1 varchar(50),
@.new_V_Address2 varchar(50),
@.new_V_Address3 varchar(50),
@.new_V_TownCity varchar(50),
@.new_I_RegionID bigint,
@.new_V_Postcode varchar(10)
)
AS
UPDATE T_Sites SET
[V_SiteName] = @.new_V_SiteName,
[V_Address1] = @.new_V_Address1,
[V_Address2] = @.new_V_Address2,
[V_Address3] = @.new_V_Address3,
[V_TownCity] = @.new_V_TownCity,
[I_RegionID] =@.new_I_RegionID ,
[V_PostCode] = @.new_V_Postcode
WHERE
I_SiteID= @.old_I_SiteID

GO

Now, lets say that the user only changes one value, e.g. Address1. Is is possible to only get this one value to update instead of passing all the values back and updating them all, i.e.:

EXEC SP_UPDATE_T_Site @.I_SiteID='2', @.Address1="...."

I know I can set default values and check these, but this would be too much work for the ammount of tables I have. Is there an easy way of doing this?

jagdipa wrote:

In SQL Server 20:

Lets say I have a table for address. I create a stored procedure to update any value in the database, i.e.:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50),
@.new_V_Address1 varchar(50),
@.new_V_Address2 varchar(50),
@.new_V_Address3 varchar(50),
@.new_V_TownCity varchar(50),
@.new_I_RegionID bigint,
@.new_V_Postcode varchar(10)
)
AS
UPDATE T_Sites SET
[V_SiteName] = @.new_V_SiteName,
[V_Address1] = @.new_V_Address1,
[V_Address2] = @.new_V_Address2,
[V_Address3] = @.new_V_Address3,
[V_TownCity] = @.new_V_TownCity,
[I_RegionID] =@.new_I_RegionID ,
[V_PostCode] = @.new_V_Postcode
WHERE
I_SiteID= @.old_I_SiteID

GO

Now, lets say that the user only changes one value, e.g. Address1. Is is possible to only get this one value to update instead of passing all the values back and updating them all, i.e.:

EXEC SP_UPDATE_T_Site @.I_SiteID='2', @.Address1="...."

I know I can set default values and check these, but this would be too much work for the ammount of tables I have. Is there an easy way of doing this?

hi jagdipa,
we're on the same situation on that one, I've decided since then to use ado.net to update optional data, and use only stored procs if the data to be update/inserted is consistent; meaning it updates all column and not a few...

|||Once approach you could try is this:

CREATE PROCEDURE [SP_UPDATE_T_Site]
(
@.old_I_SiteID bigint,
@.new_V_SiteName varchar(50) = NULL,
@.new_V_Address1 varchar(50) = NULL,
@.new_V_Address2 varchar(50) = NULL,
@.new_V_Address3 varchar(50) = NULL,
@.new_V_TownCity varchar(50) = NULL,
@.new_I_RegionID bigint = NULL,
@.new_V_Postcode varchar(10) = NULL
)
AS
UPDATE T_Sites SET
[V_SiteName] = ISNULL(@.new_V_SiteName,[V_SiteName]),
[V_Address1] = ISNULL(@.new_V_Address1,[V_Address1]),
[V_Address2] = ISNULL(@.new_V_Address2,[V_Address2]),
[V_Address3] = ISNULL(@.new_V_Address3,[V_Address3]),
[V_TownCity] = ISNULL(@.new_V_TownCity,[V_TownCity]),
[I_RegionID] = ISNULL(@.new_I_RegionID,[I_RegionID]),
[V_PostCode] = ISNULL(@.new_V_Postcode,[V_PostCode])
WHERE
I_SiteID= @.old_I_SiteID

GO

|||good one terry, i've used default values before but never thought on this implementation.. sweet..|||

Why is it not convenient to pass all the values in? How are you calling the Stored Proc?

If you use an Address object with Save method that calls the stored Proc, it should not be an issue to pass all the values to the Proc because all the values should be loaded into the instance of the Adress object that is calling Save.

|||

meantown2 wrote:

Why is it not convenient to pass all the values in? How are you calling the Stored Proc?

If you use an Address object with Save method that calls the stored Proc, it should not be an issue to pass all the values to the Proc because all the values should be loaded into the instance of the Adress object that is calling Save.

hi meantown, the poster wants to update only a selected column, if he/she is going to pass all values he might update columns he doesn't want to update. The reply of terry will prevent him/her from doing that, passing only selected parameters.

|||The reason I asked this was not really for this stored procedure (I just used this one for an easy example).

I have a B2B website where there are a lot of text fields on one webform. The data entered could grow. To try to improve effieciecy, I wanted to only pass the values that have changed.

This does mean a little extra work on the webform though. I will have to go through each value and check if it has changed.

So that leads me to my next question (this is a hard question to put down in writing, but I've tried my best to put my point across):

Lets say I have loaded the old values into a webform.
Then the user changes some of these and hits Save.
Now I need to check whether the values have changed. I could do this by comparing the values against the ones in the database - but this means an extra database access to retrieve the old values. Is there a way I can use the viewstate and somehow compare the values entered by the user against the ones stored in the viewstate?

Note: I dont want to put a load of invisible input fields everywhere and save the old values in that.

Thanks in advance for any help.

Jagdip

Model Database Stored procedures are gone.

SQL Server 2000 SP3a. Just noticed that the stored procedures that were in the model database are gone. So any new databases will not contain these stored procedures. Is this a problem?
Thanks,
Warren
We cannot answer that question for you. That is a question that you =
must answer. =20
If stored procedures were placed within the model database I would =
expect that something or someone expects those stored procedures to be =
available in the user databases that are created. =20
It is easy enough to create the stored procedures...simply grab the =
scripts and execute them in each database...then add them to model so =
that they will exist within new databases as they are created.
--=20
Keith
"Warren" <Warren@.discussions.microsoft.com> wrote in message =
news:C2DB2B96-5713-4EF3-A4B3-AC9F553ED07A@.microsoft.com...
> SQL Server 2000 SP3a. Just noticed that the stored procedures that =
were in the model database are gone. So any new databases will not =
contain these stored procedures. Is this a problem?
>=20
> Thanks,
>=20
> Warren
|||Dear Keith,
The only SP's in the model database are the ones that come with the SQL Server installation.. So your advice is to re-create them?
Thanks,
Chris Tyler
"Keith Kratochvil" wrote:

> We cannot answer that question for you. That is a question that you must answer.
> If stored procedures were placed within the model database I would expect that something or someone expects those stored procedures to be available in the user databases that are created.
> It is easy enough to create the stored procedures...simply grab the scripts and execute them in each database...then add them to model so that they will exist within new databases as they are created.
> --
> Keith
>
> "Warren" <Warren@.discussions.microsoft.com> wrote in message news:C2DB2B96-5713-4EF3-A4B3-AC9F553ED07A@.microsoft.com...
>
|||Hmmm...I did not think that any (Microsoft supplied) stored procedures =
were stored within model. Which stored procedures are missing? (what =
are their names?)
--=20
Keith
"Warren" <Warren@.discussions.microsoft.com> wrote in message =
news:C588718C-438E-4801-9A14-513120C5FBDE@.microsoft.com...
> Dear Keith,
>=20
> The only SP's in the model database are the ones that come with the =
SQL Server installation.. So your advice is to re-create them?[vbcol=seagreen]
>=20
> Thanks,
>=20
> Chris Tyler
>=20
>=20
>=20
> "Keith Kratochvil" wrote:
>=20
must answer. =20[vbcol=seagreen]
expect that something or someone expects those stored procedures to be =
available in the user databases that are created. =20[vbcol=seagreen]
scripts and execute them in each database...then add them to model so =
that they will exist within new databases as they are created.[vbcol=seagreen]
news:C2DB2B96-5713-4EF3-A4B3-AC9F553ED07A@.microsoft.com...[vbcol=seagreen]
that were in the model database are gone. So any new databases will not =
contain these stored procedures. Is this a problem?[vbcol=seagreen]
|||> The only SP's in the model database are the ones that come with the SQL
Server installation.
There are no such things, in any of my installations here. Can you name a
few of these "missing" stored procedures?
|||Hi Aaron,
There may not be any System SP's in the Model database.. What I find is most server have no SP's within Model, but some have dt_Stored procedures that say System. For example, dt_addsourcecontrol, dt_addsourcecontrol_u, dt_adduserobject...So this may hav
e come into Model by other means...
Thanks,
Warren
"Aaron [SQL Server MVP]" wrote:

> Server installation.
> There are no such things, in any of my installations here. Can you name a
> few of these "missing" stored procedures?
>
>
|||These are created automatically if you click on the diagram node in a
database. They don't need to exist, if they are missing, they will be
created by SQL Server. Not quite sure how they get into model (I think a few
other things trigger their creation as well) as there shouldn't be a diagram
node in EM for system databases.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Warren" <Warren@.discussions.microsoft.com> wrote in message
news:BC201722-6FE3-41BD-A22A-FAB84D3FEE37@.microsoft.com...
> Hi Aaron,
> There may not be any System SP's in the Model database.. What I find is
most server have no SP's within Model, but some have dt_Stored procedures
that say System. For example, dt_addsourcecontrol, dt_addsourcecontrol_u,
dt_adduserobject...So this may have come into Model by other means...[vbcol=seagreen]
> Thanks,
> Warren
> "Aaron [SQL Server MVP]" wrote:
SQL[vbcol=seagreen]
a[vbcol=seagreen]

Model Database Stored procedures are gone.

SQL Server 2000 SP3a. Just noticed that the stored procedures that were in the model database are gone. So any new databases will not contain these stored procedures. Is this a problem?
Thanks,
WarrenWe cannot answer that question for you. That is a question that you =must answer.
If stored procedures were placed within the model database I would =expect that something or someone expects those stored procedures to be =available in the user databases that are created.
It is easy enough to create the stored procedures...simply grab the =scripts and execute them in each database...then add them to model so =that they will exist within new databases as they are created.
-- Keith
"Warren" <Warren@.discussions.microsoft.com> wrote in message =news:C2DB2B96-5713-4EF3-A4B3-AC9F553ED07A@.microsoft.com...
> SQL Server 2000 SP3a. Just noticed that the stored procedures that =were in the model database are gone. So any new databases will not =contain these stored procedures. Is this a problem?
> > Thanks,
> > Warren|||Dear Keith,
The only SP's in the model database are the ones that come with the SQL Server installation.. So your advice is to re-create them?
Thanks,
Chris Tyler
"Keith Kratochvil" wrote:
> We cannot answer that question for you. That is a question that you must answer.
> If stored procedures were placed within the model database I would expect that something or someone expects those stored procedures to be available in the user databases that are created.
> It is easy enough to create the stored procedures...simply grab the scripts and execute them in each database...then add them to model so that they will exist within new databases as they are created.
> --
> Keith
>
> "Warren" <Warren@.discussions.microsoft.com> wrote in message news:C2DB2B96-5713-4EF3-A4B3-AC9F553ED07A@.microsoft.com...
> > SQL Server 2000 SP3a. Just noticed that the stored procedures that were in the model database are gone. So any new databases will not contain these stored procedures. Is this a problem?
> >
> > Thanks,
> >
> > Warren
>|||Hmmm...I did not think that any (Microsoft supplied) stored procedures =were stored within model. Which stored procedures are missing? (what =are their names?)
-- Keith
"Warren" <Warren@.discussions.microsoft.com> wrote in message =news:C588718C-438E-4801-9A14-513120C5FBDE@.microsoft.com...
> Dear Keith,
> > The only SP's in the model database are the ones that come with the =SQL Server installation.. So your advice is to re-create them?
> > Thanks,
> > Chris Tyler
> > > > "Keith Kratochvil" wrote:
> > > We cannot answer that question for you. That is a question that you =must answer. > > > > If stored procedures were placed within the model database I would =expect that something or someone expects those stored procedures to be =available in the user databases that are created. > > > > It is easy enough to create the stored procedures...simply grab the =scripts and execute them in each database...then add them to model so =that they will exist within new databases as they are created.
> > > > -- > > Keith
> > > > > > "Warren" <Warren@.discussions.microsoft.com> wrote in message =news:C2DB2B96-5713-4EF3-A4B3-AC9F553ED07A@.microsoft.com...
> > > SQL Server 2000 SP3a. Just noticed that the stored procedures =that were in the model database are gone. So any new databases will not =contain these stored procedures. Is this a problem?
> > > > > > Thanks,
> > > > > > Warren
> >|||> The only SP's in the model database are the ones that come with the SQL
Server installation.
There are no such things, in any of my installations here. Can you name a
few of these "missing" stored procedures?|||Hi Aaron,
There may not be any System SP's in the Model database.. What I find is most server have no SP's within Model, but some have dt_Stored procedures that say System. For example, dt_addsourcecontrol, dt_addsourcecontrol_u, dt_adduserobject...So this may have come into Model by other means...
Thanks,
Warren
"Aaron [SQL Server MVP]" wrote:
> > The only SP's in the model database are the ones that come with the SQL
> Server installation.
> There are no such things, in any of my installations here. Can you name a
> few of these "missing" stored procedures?
>
>|||These are created automatically if you click on the diagram node in a
database. They don't need to exist, if they are missing, they will be
created by SQL Server. Not quite sure how they get into model (I think a few
other things trigger their creation as well) as there shouldn't be a diagram
node in EM for system databases.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Warren" <Warren@.discussions.microsoft.com> wrote in message
news:BC201722-6FE3-41BD-A22A-FAB84D3FEE37@.microsoft.com...
> Hi Aaron,
> There may not be any System SP's in the Model database.. What I find is
most server have no SP's within Model, but some have dt_Stored procedures
that say System. For example, dt_addsourcecontrol, dt_addsourcecontrol_u,
dt_adduserobject...So this may have come into Model by other means...
> Thanks,
> Warren
> "Aaron [SQL Server MVP]" wrote:
> > > The only SP's in the model database are the ones that come with the
SQL
> > Server installation.
> >
> > There are no such things, in any of my installations here. Can you name
a
> > few of these "missing" stored procedures?
> >
> >
> >

Model Database Stored procedures are gone.

SQL Server 2000 SP3a. Just noticed that the stored procedures that were in
the model database are gone. So any new databases will not contain these st
ored procedures. Is this a problem?
Thanks,
WarrenWe cannot answer that question for you. That is a question that you =
must answer. =20
If stored procedures were placed within the model database I would =
expect that something or someone expects those stored procedures to be =
available in the user databases that are created. =20
It is easy enough to create the stored procedures...simply grab the =
scripts and execute them in each database...then add them to model so =
that they will exist within new databases as they are created.
--=20
Keith
"Warren" <Warren@.discussions.microsoft.com> wrote in message =
news:C2DB2B96-5713-4EF3-A4B3-AC9F553ED07A@.microsoft.com...
> SQL Server 2000 SP3a. Just noticed that the stored procedures that =
were in the model database are gone. So any new databases will not =
contain these stored procedures. Is this a problem?
>=20
> Thanks,
>=20
> Warren|||Dear Keith,
The only SP's in the model database are the ones that come with the SQL Serv
er installation.. So your advice is to re-create them?
Thanks,
Chris Tyler
"Keith Kratochvil" wrote:

> We cannot answer that question for you. That is a question that you must
answer.
> If stored procedures were placed within the model database I would expect
that something or someone expects those stored procedures to be available in
the user databases that are created.
> It is easy enough to create the stored procedures...simply grab the scrip
ts and execute them in each database...then add them to model so that they w
ill exist within new databases as they are created.
> --
> Keith
>
> "Warren" <Warren@.discussions.microsoft.com> wrote in message news:C2DB2B96
-5713-4EF3-A4B3-AC9F553ED07A@.microsoft.com...
>|||Hmmm...I did not think that any (Microsoft supplied) stored procedures =
were stored within model. Which stored procedures are missing? (what =
are their names?)
--=20
Keith
"Warren" <Warren@.discussions.microsoft.com> wrote in message =
news:C588718C-438E-4801-9A14-513120C5FBDE@.microsoft.com...
> Dear Keith,
>=20
> The only SP's in the model database are the ones that come with the =
SQL Server installation.. So your advice is to re-create them?[vbcol=seagreen]
>=20
> Thanks,
>=20
> Chris Tyler
>=20
>=20
>=20
> "Keith Kratochvil" wrote:
>=20
must answer. =20[vbcol=seagreen]
expect that something or someone expects those stored procedures to be =
available in the user databases that are created. =20[vbcol=seagreen]
scripts and execute them in each database...then add them to model so =
that they will exist within new databases as they are created.[vbcol=seagreen]
news:C2DB2B96-5713-4EF3-A4B3-AC9F553ED07A@.microsoft.com...[vbcol=seagreen]
that were in the model database are gone. So any new databases will not =
contain these stored procedures. Is this a problem?[vbcol=seagreen]|||> The only SP's in the model database are the ones that come with the SQL
Server installation.
There are no such things, in any of my installations here. Can you name a
few of these "missing" stored procedures?|||Hi Aaron,
There may not be any System SP's in the Model database.. What I find is most
server have no SP's within Model, but some have dt_Stored procedures that s
ay System. For example, dt_addsourcecontrol, dt_addsourcecontrol_u, dt_addu
serobject...So this may hav
e come into Model by other means...
Thanks,
Warren
"Aaron [SQL Server MVP]" wrote:

> Server installation.
> There are no such things, in any of my installations here. Can you name a
> few of these "missing" stored procedures?
>
>|||These are created automatically if you click on the diagram node in a
database. They don't need to exist, if they are missing, they will be
created by SQL Server. Not quite sure how they get into model (I think a few
other things trigger their creation as well) as there shouldn't be a diagram
node in EM for system databases.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Warren" <Warren@.discussions.microsoft.com> wrote in message
news:BC201722-6FE3-41BD-A22A-FAB84D3FEE37@.microsoft.com...
> Hi Aaron,
> There may not be any System SP's in the Model database.. What I find is
most server have no SP's within Model, but some have dt_Stored procedures
that say System. For example, dt_addsourcecontrol, dt_addsourcecontrol_u,
dt_adduserobject...So this may have come into Model by other means...[vbcol=seagreen]
> Thanks,
> Warren
> "Aaron [SQL Server MVP]" wrote:
>
SQL[vbcol=seagreen]
a[vbcol=seagreen]

Model database has unknown owner

I am trying to run the sp_helpdb stored procedure and am getting the
following results:
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails.
The statement has been terminated.
I think I have narrowed it down. The model database has an owner of
unknown. I have tried running this:
ALTER DATABASE model SET SINGLE_USER
DBCC CHECKDB ('model', Repair_Rebuild)
ALTER DATABASE model SET Multi_USER
But the server seems to hang trying to set the db to single user. I
can not detach the model database either. Any suggestions on how to
fix this without losing all of my other database info in the master?
TIA
when you run this
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
what's the schema_owner?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Have you tried just changing the owner to 'sa', which is what it should be?
USE model
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143221417.974191.196120@.i40g2000cwc.googlegr oups.com...
>I am trying to run the sp_helpdb stored procedure and am getting the
> following results:
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
> Cannot insert the value NULL into column '', table ''; column does not
> allow nulls. INSERT fails.
> The statement has been terminated.
> I think I have narrowed it down. The model database has an owner of
> unknown. I have tried running this:
> ALTER DATABASE model SET SINGLE_USER
> DBCC CHECKDB ('model', Repair_Rebuild)
> ALTER DATABASE model SET Multi_USER
> But the server seems to hang trying to set the db to single user. I
> can not detach the model database either. Any suggestions on how to
> fix this without losing all of my other database info in the master?
> TIA
>
|||When I tried this query:
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
I get:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.schemata'.
When I try changing the owner using:
use model
EXEC sp_changedbowner 'sa'
I get:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
I am logged in as sa.
|||The sp_changedbowner procedure affects the database you are currently in,
and the message indicates you did not USE model before running the stored
procedure.
First:
USE model
GO
Make sure you are in model:
SELECT db_name()
Once you are in model:
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143292556.116759.304750@.e56g2000cwe.googlegr oups.com...
> When I tried this query:
> select schema_owner,* from information_schema.schemata
> where catalog_name ='model'
> I get:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'information_schema.schemata'.
> When I try changing the owner using:
> use model
> EXEC sp_changedbowner 'sa'
> I get:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> I am logged in as sa.
>
|||I made sure I was in the model database. I set up the following query:
use model
go
SELECT db_name()
go
EXEC sp_changedbowner 'sa'
go
The responses I received was:
(1 row(s) affected)
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
Query Analyzer does show I am in the Model database.
|||What messages do you get if you do below?
EXEC model..sp_changedbowner 'sa'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhmosow@.gmail.com> wrote in message news:1143466613.203455.96190@.i40g2000cwc.googlegro ups.com...
>I made sure I was in the model database. I set up the following query:
> use model
> go
> SELECT db_name()
> go
> EXEC sp_changedbowner 'sa'
> go
> The responses I received was:
> (1 row(s) affected)
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> Query Analyzer does show I am in the Model database.
>
|||Running EXEC model..sp_changedbowner 'sa' returns:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
|||OK, it seems like SQL Server doesn't allow you to change the owner of the model database, and that
the error message is slightly misleading. Since sp_changedbowner doesn't allow you to change the
owner of model to anything else but "sa", you have to try to find out how and why this was changed
from sa in the first place. How to fix this is then up to you:
* Rebuild the system databases (rebuildm.exe). You will lose all information in the system
databases.
* Hack the system tables. If you don't know how, don't do it. And, it is not supported.- Warning,
warning!!!
* Open a case with MS Support and let them hand-hold you through the process.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jhmosow@.gmail.com> wrote in message news:1143471215.461886.94170@.v46g2000cwv.googlegro ups.com...
> Running EXEC model..sp_changedbowner 'sa' returns:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
>

Model database has unknown owner

I am trying to run the sp_helpdb stored procedure and am getting the
following results:
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails.
The statement has been terminated.
I think I have narrowed it down. The model database has an owner of
unknown. I have tried running this:
ALTER DATABASE model SET SINGLE_USER
DBCC CHECKDB ('model', Repair_Rebuild)
ALTER DATABASE model SET Multi_USER
But the server seems to hang trying to set the db to single user. I
can not detach the model database either. Any suggestions on how to
fix this without losing all of my other database info in the master?
TIAwhen you run this
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
what's the schema_owner?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Have you tried just changing the owner to 'sa', which is what it should be?
USE model
EXEC sp_changedbowner 'sa'
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143221417.974191.196120@.i40g2000cwc.googlegroups.com...
>I am trying to run the sp_helpdb stored procedure and am getting the
> following results:
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
> Cannot insert the value NULL into column '', table ''; column does not
> allow nulls. INSERT fails.
> The statement has been terminated.
> I think I have narrowed it down. The model database has an owner of
> unknown. I have tried running this:
> ALTER DATABASE model SET SINGLE_USER
> DBCC CHECKDB ('model', Repair_Rebuild)
> ALTER DATABASE model SET Multi_USER
> But the server seems to hang trying to set the db to single user. I
> can not detach the model database either. Any suggestions on how to
> fix this without losing all of my other database info in the master?
> TIA
>|||When I tried this query:
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
I get:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.schemata'.
When I try changing the owner using:
use model
EXEC sp_changedbowner 'sa'
I get:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
I am logged in as sa.|||The sp_changedbowner procedure affects the database you are currently in,
and the message indicates you did not USE model before running the stored
procedure.
First:
USE model
GO
Make sure you are in model:
SELECT db_name()
Once you are in model:
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143292556.116759.304750@.e56g2000cwe.googlegroups.com...
> When I tried this query:
> select schema_owner,* from information_schema.schemata
> where catalog_name ='model'
> I get:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'information_schema.schemata'.
> When I try changing the owner using:
> use model
> EXEC sp_changedbowner 'sa'
> I get:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> I am logged in as sa.
>|||I made sure I was in the model database. I set up the following query:
use model
go
SELECT db_name()
go
EXEC sp_changedbowner 'sa'
go
The responses I received was:
(1 row(s) affected)
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
Query Analyzer does show I am in the Model database.|||What messages do you get if you do below?
EXEC model..sp_changedbowner 'sa'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhmosow@.gmail.com> wrote in message news:1143466613.203455.96190@.i40g2000cwc.googlegroups.com...
>I made sure I was in the model database. I set up the following query:
> use model
> go
> SELECT db_name()
> go
> EXEC sp_changedbowner 'sa'
> go
> The responses I received was:
> (1 row(s) affected)
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> Query Analyzer does show I am in the Model database.
>|||Running EXEC model..sp_changedbowner 'sa' returns:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.|||OK, it seems like SQL Server doesn't allow you to change the owner of the model database, and that
the error message is slightly misleading. Since sp_changedbowner doesn't allow you to change the
owner of model to anything else but "sa", you have to try to find out how and why this was changed
from sa in the first place. How to fix this is then up to you:
* Rebuild the system databases (rebuildm.exe). You will lose all information in the system
databases.
* Hack the system tables. If you don't know how, don't do it. And, it is not supported.- Warning,
warning!!!
* Open a case with MS Support and let them hand-hold you through the process.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jhmosow@.gmail.com> wrote in message news:1143471215.461886.94170@.v46g2000cwv.googlegroups.com...
> Running EXEC model..sp_changedbowner 'sa' returns:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
>sql

Model database has unknown owner

I am trying to run the sp_helpdb stored procedure and am getting the
following results:
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column '', table ''; column does not
allow nulls. INSERT fails.
The statement has been terminated.
I think I have narrowed it down. The model database has an owner of
unknown. I have tried running this:
ALTER DATABASE model SET SINGLE_USER
DBCC CHECKDB ('model', Repair_Rebuild)
ALTER DATABASE model SET Multi_USER
But the server seems to hang trying to set the db to single user. I
can not detach the model database either. Any suggestions on how to
fix this without losing all of my other database info in the master?
TIAwhen you run this
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
what's the schema_owner?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Have you tried just changing the owner to 'sa', which is what it should be?
USE model
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143221417.974191.196120@.i40g2000cwc.googlegroups.com...
>I am trying to run the sp_helpdb stored procedure and am getting the
> following results:
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
> Cannot insert the value NULL into column '', table ''; column does not
> allow nulls. INSERT fails.
> The statement has been terminated.
> I think I have narrowed it down. The model database has an owner of
> unknown. I have tried running this:
> ALTER DATABASE model SET SINGLE_USER
> DBCC CHECKDB ('model', Repair_Rebuild)
> ALTER DATABASE model SET Multi_USER
> But the server seems to hang trying to set the db to single user. I
> can not detach the model database either. Any suggestions on how to
> fix this without losing all of my other database info in the master?
> TIA
>|||When I tried this query:
select schema_owner,* from information_schema.schemata
where catalog_name ='model'
I get:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.schemata'.
When I try changing the owner using:
use model
EXEC sp_changedbowner 'sa'
I get:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
I am logged in as sa.|||The sp_changedbowner procedure affects the database you are currently in,
and the message indicates you did not USE model before running the stored
procedure.
First:
USE model
GO
Make sure you are in model:
SELECT db_name()
Once you are in model:
EXEC sp_changedbowner 'sa'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<jhmosow@.gmail.com> wrote in message
news:1143292556.116759.304750@.e56g2000cwe.googlegroups.com...
> When I tried this query:
> select schema_owner,* from information_schema.schemata
> where catalog_name ='model'
> I get:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'information_schema.schemata'.
> When I try changing the owner using:
> use model
> EXEC sp_changedbowner 'sa'
> I get:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> I am logged in as sa.
>|||I made sure I was in the model database. I set up the following query:
use model
go
SELECT db_name()
go
EXEC sp_changedbowner 'sa'
go
The responses I received was:
(1 row(s) affected)
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.
Query Analyzer does show I am in the Model database.|||What messages do you get if you do below?
EXEC model..sp_changedbowner 'sa'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jhmosow@.gmail.com> wrote in message news:1143466613.203455.96190@.i40g2000cwc.googlegroups.c
om...
>I made sure I was in the model database. I set up the following query:
> use model
> go
> SELECT db_name()
> go
> EXEC sp_changedbowner 'sa'
> go
> The responses I received was:
> (1 row(s) affected)
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
> Query Analyzer does show I am in the Model database.
>|||Running EXEC model..sp_changedbowner 'sa' returns:
Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
22
Cannot change the owner of the master database.|||OK, it seems like SQL Server doesn't allow you to change the owner of the mo
del database, and that
the error message is slightly misleading. Since sp_changedbowner doesn't all
ow you to change the
owner of model to anything else but "sa", you have to try to find out how an
d why this was changed
from sa in the first place. How to fix this is then up to you:
* Rebuild the system databases (rebuildm.exe). You will lose all information
in the system
databases.
* Hack the system tables. If you don't know how, don't do it. And, it is not
supported.- Warning,
warning!!!
* Open a case with MS Support and let them hand-hold you through the process
.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jhmosow@.gmail.com> wrote in message news:1143471215.461886.94170@.v46g2000cwv.googlegroups.c
om...
> Running EXEC model..sp_changedbowner 'sa' returns:
> Server: Msg 15109, Level 16, State 1, Procedure sp_changedbowner, Line
> 22
> Cannot change the owner of the master database.
>

MOD 10 (Credit Card Validation)

Does anyone have any strings or links I can use to help with creating a mod-10 stored proc?

I am really stuck on step 1, which is flipping the numbers (ex 1234 -> 4321)..

Any information pretaining to MOD-10 and MSSQL would be MUCH appriciated..Does this help you to flip the number?

declare @.in bigint
declare @.out bigint

set @.in = 123456789012345678
set @.out = 0

while @.in > 0
begin
print @.in %10
set @.out = @.out*10 + (@.in % 10)
Set @.in = @.in /10

end

select @.out|||It looks like it should work.. I'm going to try it in the morning!

Thanks!sql

Misunderstanding of backup

I always assumed that the backup & restore procedure stored simply
data, not structure.

Now I find that if I add a new table to a database, then restore using
an old backup, the new table is gone.

Is there any way to restore JUST the data from a backup? If not, is
there any way to archive & import just data? The import/export wizard
only seems to send a single table to a flat file, but I need all the
tables.

Is there perhaps a command-line parameterthat isn't available in the
wizard that would accomplish this?You can restore to a different database or server and then copy over
the data.
http://msdn.microsoft.com/library/d...backpc_6ng9.asp

--
David Portas
SQL Server MVP
--|||You can restore to a different database or server and then copy over
the data.
http://msdn.microsoft.com/library/d...backpc_6ng9.asp

--
David Portas
SQL Server MVP
--|||
David Portas wrote:
> You can restore to a different database or server and then copy over
> the data.
> http://msdn.microsoft.com/library/d...backpc_6ng9.asp
> --
> David Portas
> SQL Server MVP
> --

Must the data be copied using the MOVE command, or can I simply
physically relocate the ldf & mdf files to the target server?|||
David Portas wrote:
> You can restore to a different database or server and then copy over
> the data.
> http://msdn.microsoft.com/library/d...backpc_6ng9.asp
> --
> David Portas
> SQL Server MVP
> --

Must the data be copied using the MOVE command, or can I simply
physically relocate the ldf & mdf files to the target server?|||You can always detach and reattach the files (sp_attach_db and sp_detach_db)
but as you'll have to restore them first you may as well restore them where
you want them to start with.

--
David Portas
SQL Server MVP
--|||You can always detach and reattach the files (sp_attach_db and sp_detach_db)
but as you'll have to restore them first you may as well restore them where
you want them to start with.

--
David Portas
SQL Server MVP
--|||
David Portas wrote:
> You can always detach and reattach the files (sp_attach_db and sp_detach_db)
> but as you'll have to restore them first you may as well restore them where
> you want them to start with.
> --
> David Portas
> SQL Server MVP
> --

Once the new temp database is created, what procedure would you
recommend for copying just the data between databases?|||Darryl (DarrylJ@.yahoo.com) writes:
> David Portas wrote:
>> You can always detach and reattach the files (sp_attach_db and
>> sp_detach_db) but as you'll have to restore them first you may as well
>> restore them where you want them to start with.
>>
> Once the new temp database is created, what procedure would you
> recommend for copying just the data between databases?

Depends on why, and how often etc. If you want to do it on a regular
basis, replication could be the way to go. For a one-off thing,
it depends on whether the target database already has data, and
what you want to do with it etc.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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 System Stored Procedures

I have a server that has a few system stored procedures missing, among them
sp_grantdbaccess, sp_droplogin, etc. I think this may be from a failed
installation of a service pack. This server is going away in a couple of
weeks, but in the meatime, I would like to get put these system stored
procedures back, but have been unsuccessful doing so.
Does any have a suggestion, besides restoring master?
Copy them from another SQL Server. Script them out and execute in the server
where they are missing.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"RogerT" <RogerT@.discussions.microsoft.com> wrote in message
news:2840B89F-C2FF-46D1-ACE3-F48CD2A9CF06@.microsoft.com...
>I have a server that has a few system stored procedures missing, among them
> sp_grantdbaccess, sp_droplogin, etc. I think this may be from a failed
> installation of a service pack. This server is going away in a couple of
> weeks, but in the meatime, I would like to get put these system stored
> procedures back, but have been unsuccessful doing so.
> Does any have a suggestion, besides restoring master?
|||What did you do to try to get the procedures back? Did you
try reapplying the latest Service Pack? If you had problems
with applying one of the service packs, you'd probably want
to make sure nothing else has been impacted.
-Sue
On Fri, 6 Oct 2006 15:40:02 -0700, RogerT
<RogerT@.discussions.microsoft.com> wrote:

>I have a server that has a few system stored procedures missing, among them
>sp_grantdbaccess, sp_droplogin, etc. I think this may be from a failed
>installation of a service pack. This server is going away in a couple of
>weeks, but in the meatime, I would like to get put these system stored
>procedures back, but have been unsuccessful doing so.
>Does any have a suggestion, besides restoring master?

Missing System Stored Procedures

I have a server that has a few system stored procedures missing, among them
sp_grantdbaccess, sp_droplogin, etc. I think this may be from a failed
installation of a service pack. This server is going away in a couple of
weeks, but in the meatime, I would like to get put these system stored
procedures back, but have been unsuccessful doing so.
Does any have a suggestion, besides restoring master?Copy them from another SQL Server. Script them out and execute in the server
where they are missing.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"RogerT" <RogerT@.discussions.microsoft.com> wrote in message
news:2840B89F-C2FF-46D1-ACE3-F48CD2A9CF06@.microsoft.com...
>I have a server that has a few system stored procedures missing, among them
> sp_grantdbaccess, sp_droplogin, etc. I think this may be from a failed
> installation of a service pack. This server is going away in a couple of
> weeks, but in the meatime, I would like to get put these system stored
> procedures back, but have been unsuccessful doing so.
> Does any have a suggestion, besides restoring master?|||What did you do to try to get the procedures back? Did you
try reapplying the latest Service Pack? If you had problems
with applying one of the service packs, you'd probably want
to make sure nothing else has been impacted.
-Sue
On Fri, 6 Oct 2006 15:40:02 -0700, RogerT
<RogerT@.discussions.microsoft.com> wrote:

>I have a server that has a few system stored procedures missing, among them
>sp_grantdbaccess, sp_droplogin, etc. I think this may be from a failed
>installation of a service pack. This server is going away in a couple of
>weeks, but in the meatime, I would like to get put these system stored
>procedures back, but have been unsuccessful doing so.
>Does any have a suggestion, besides restoring master?

Missing system stored procedures

When I create a new database, the system stored procedures are missing;
dt_addtosourcecontrol_u
dt_checkinobject_u
etc.
I'm logged in as sa.Is that the only 2?

I've never used them before...but the appear in all the databases I've created.

What version are you using?

And can you see them in master?|||Those procedures are magically created by Enterprise Manager, as far as I can tell. Even if you are logged into EM as someone with no create procedure rights. I would not worry over them, unless you have errors. You may need to apply a service pack to the client, at worst.|||Whatdya know...I gotta script that for often...it's the only thing I don't script

CREATE DATABASE [myDB99]
ON (NAME = N'myDB99'
, FILENAME = N'd:\database\njros1d151dev\MSSQL$NJROS1D151DEV\da ta\myDB99.mdf'
, SIZE = 209, FILEGROWTH = 10%)
LOG ON (NAME = N'myDB99_log', FILENAME = N'd:\database\njros1d151dev\MSSQL$NJROS1D151DEV\da ta\myDB99.ldf'
, SIZE = 61
, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO|||These procs are created automagically when you use the database diagrams feature in EM. These procedures have something to do with the creation of the diagrams (though i do not exactly know what).

I would not worry about them ... i have not ever seen anybody use them ... except the Enterprise Manager.|||I thought they had more to do with Visual Source Safe then with the DB Diagram feature...

Missing system stored procedures

I developed a Pocket PC app that uses merge replication in an isolated
development environment. It worked fine. The I moved to the operational
environment. Synchronization fails with error 2812, "Could not find stored
procedure 'sp_MSgetmakegenerationapplock'". A little investigation shows that
in the development environment, that stored procedure exists, but it does not
exist on the operational SQL Server. (The total sp count on the development
server is 969, on the operational server 930, so other sp's are also
missing). I disabled replication on the operational server and then
reconfigured it, but that didn't help.
The operational SQL Server is SQL Server 2000 Standard Edition running sp3a.
Any ideas why those sp's didn't get created, and how to fix this problem?
There's a message thread out there that suggests that xp_cmdshell is needed
to properly create some replication-related sp's. Is it possible that
xp_cmdshell is missing or disabled? However, when I configure the server for
replication, I don't get any error messages.
You need to reapply the sp. This problem has been observed before.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"PGallez" <PGallez@.discussions.microsoft.com> wrote in message
news:8A112E32-D0BB-4FB5-9A51-B62BBAFBAB01@.microsoft.com...
> I developed a Pocket PC app that uses merge replication in an isolated
> development environment. It worked fine. The I moved to the operational
> environment. Synchronization fails with error 2812, "Could not find stored
> procedure 'sp_MSgetmakegenerationapplock'". A little investigation shows
that
> in the development environment, that stored procedure exists, but it does
not
> exist on the operational SQL Server. (The total sp count on the
development
> server is 969, on the operational server 930, so other sp's are also
> missing). I disabled replication on the operational server and then
> reconfigured it, but that didn't help.
> The operational SQL Server is SQL Server 2000 Standard Edition running
sp3a.
> Any ideas why those sp's didn't get created, and how to fix this problem?
> There's a message thread out there that suggests that xp_cmdshell is
needed
> to properly create some replication-related sp's. Is it possible that
> xp_cmdshell is missing or disabled? However, when I configure the server
for
> replication, I don't get any error messages.
|||Thanks much, I'll give that a try.
"Hilary Cotter" wrote:

> You need to reapply the sp. This problem has been observed before.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "PGallez" <PGallez@.discussions.microsoft.com> wrote in message
> news:8A112E32-D0BB-4FB5-9A51-B62BBAFBAB01@.microsoft.com...
> that
> not
> development
> sp3a.
> needed
> for
>
>

Missing System Stored Procedures

I have a server that has a few system stored procedures missing, among them
sp_grantdbaccess, sp_droplogin, etc. I think this may be from a failed
installation of a service pack. This server is going away in a couple of
weeks, but in the meatime, I would like to get put these system stored
procedures back, but have been unsuccessful doing so.
Does any have a suggestion, besides restoring master?Copy them from another SQL Server. Script them out and execute in the server
where they are missing.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"RogerT" <RogerT@.discussions.microsoft.com> wrote in message
news:2840B89F-C2FF-46D1-ACE3-F48CD2A9CF06@.microsoft.com...
>I have a server that has a few system stored procedures missing, among them
> sp_grantdbaccess, sp_droplogin, etc. I think this may be from a failed
> installation of a service pack. This server is going away in a couple of
> weeks, but in the meatime, I would like to get put these system stored
> procedures back, but have been unsuccessful doing so.
> Does any have a suggestion, besides restoring master?|||What did you do to try to get the procedures back? Did you
try reapplying the latest Service Pack? If you had problems
with applying one of the service packs, you'd probably want
to make sure nothing else has been impacted.
-Sue
On Fri, 6 Oct 2006 15:40:02 -0700, RogerT
<RogerT@.discussions.microsoft.com> wrote:
>I have a server that has a few system stored procedures missing, among them
>sp_grantdbaccess, sp_droplogin, etc. I think this may be from a failed
>installation of a service pack. This server is going away in a couple of
>weeks, but in the meatime, I would like to get put these system stored
>procedures back, but have been unsuccessful doing so.
>Does any have a suggestion, besides restoring master?

missing stored procedures in model

Hello:
we just noticed that there are no stored procedures in model.
We restored from tape from many months ago and there were none then.
Question #1: I thought model had some stored procedures
if so
Question#2: since I cannot restore, how can I rebuild the model database
Thanks
T
The absence of SPs is ok for me, becasue the model db is only a
template for further created databases, such as tempdb at the server
start. If youdidn=B4t create any procedures in here, there won=B4t be
any.
HTH, Jens Suessmeyer.
|||So none come natively ?
Thanks
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1126698473.378221.321270@.f14g2000cwb.googlegr oups.com...
The absence of SPs is ok for me, becasue the model db is only a
template for further created databases, such as tempdb at the server
start. If youdidnt create any procedures in here, there wont be
any.
HTH, Jens Suessmeyer.
|||Correct, Model does not contain any stored procedures unless you put them
there.
"Support" wrote:

> So none come natively ?
> Thanks
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1126698473.378221.321270@.f14g2000cwb.googlegr oups.com...
> The absence of SPs is ok for me, becasue the model db is only a
> template for further created databases, such as tempdb at the server
> start. If youdidn′t create any procedures in here, there won′t be
> any.
> HTH, Jens Suessmeyer.
>
>
|||say this because in other databases, I have stored procedures like:
dt_addtosourcecontrol
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_whocheckedout
that are all system sprocs
T
"Support" <RemoveThis_Support@.mail.oci.state.ga.us> wrote in message
news:euPpNMSuFHA.3684@.TK2MSFTNGP09.phx.gbl...
> So none come natively ?
> Thanks
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1126698473.378221.321270@.f14g2000cwb.googlegr oups.com...
> The absence of SPs is ok for me, becasue the model db is only a
> template for further created databases, such as tempdb at the server
> start. If youdidnt create any procedures in here, there wont be
> any.
> HTH, Jens Suessmeyer.
>
|||I checked a number of our servers to verify.
"Support" wrote:

> say this because in other databases, I have stored procedures like:
> dt_addtosourcecontrol
> dt_adduserobject
> dt_adduserobject_vcs
> dt_checkinobject
> dt_whocheckedout
> that are all system sprocs
> T
>
>
> "Support" <RemoveThis_Support@.mail.oci.state.ga.us> wrote in message
> news:euPpNMSuFHA.3684@.TK2MSFTNGP09.phx.gbl...
>
>
|||By default the model database contains no stored procedures.
The dt_ procedures are added the first time you select ADD Diagram. The
same for the table dt_properties.
M