Hi
I have a problem. I have a database with way to many stored procedures. In
order delete some i made a script that finds stored procedures and their
dependencies. Those without any dependencies should be deleted because they
typically have been emptied for logic.
But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
uses a normal table or a table variable there is a dependency.
My example below turns out to have no dependecies as all even though the
table exists up front and the procedure has been executed succesfully.
Example: --
CREATE PROCEDURE Test3 AS
Create Table #no (
i int
)
Insert into #no (i) Select top 10 ID from table_1
Select * from #no
Drop table #no
--
This is a problem for me because a lot of my reports collect data from
several tables and insert them into temp tables. But effectively they will
show up with no dependencies and therefor be subject for deletion.
Remodelling all stored procedures to use table variables is no option.
This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error in
the product or whether there is a good reason for doing as it does.> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
One of the reasons dependency information may be missing is due to deferred
name resolution. Since the temp table does not exist when the proc is
created, no dependency info is recorded for any objects referenced by the
INSERT statement.
> Remodelling all stored procedures to use table variables is no option.
If you create the temp table before creating the proc, you should get the
dependency info.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
Although one could make the argument that this is a bug, it's unclear to me
what the correct behavior should be when a statement contains a mix of
existing and non-existing objects. You might consider submitting product
enhancement feedback via Connect (http://connect.microsoft.com/SQLServer) to
suggest that known dependency information be recorded when a statement
contains a mix of existing and non-existing objects. Perhaps this will make
it into the next SQL Server version or future service pack.
Hope this helps.
Dan Guzman
SQL Server MVP
"Karsten Feddersen, Denmark" <Karsten Feddersen,
Denmark@.discussions.microsoft.com> wrote in message
news:48D61C0B-5635-4137-A3E8-C7A7CC16796F@.microsoft.com...
> Hi
> I have a problem. I have a database with way to many stored procedures. In
> order delete some i made a script that finds stored procedures and their
> dependencies. Those without any dependencies should be deleted because
> they
> typically have been emptied for logic.
> But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
> FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
> uses a normal table or a table variable there is a dependency.
> My example below turns out to have no dependecies as all even though the
> table exists up front and the procedure has been executed succesfully.
> Example: --
> CREATE PROCEDURE Test3 AS
> Create Table #no (
> i int
> )
> Insert into #no (i) Select top 10 ID from table_1
> Select * from #no
> Drop table #no
> --
> This is a problem for me because a lot of my reports collect data from
> several tables and insert them into temp tables. But effectively they will
> show up with no dependencies and therefor be subject for deletion.
> Remodelling all stored procedures to use table variables is no option.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.sql
Showing posts with label procedures. Show all posts
Showing posts with label procedures. Show all posts
Monday, March 26, 2012
No dependencies when inserting data into a #temp table
Hi
I have a problem. I have a database with way to many stored procedures. In
order delete some i made a script that finds stored procedures and their
dependencies. Those without any dependencies should be deleted because they
typically have been emptied for logic.
But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
uses a normal table or a table variable there is a dependency.
My example below turns out to have no dependecies as all even though the
table exists up front and the procedure has been executed succesfully.
Example: --
CREATE PROCEDURE Test3 AS
Create Table #no (
i int
)
Insert into #no (i) Select top 10 ID from table_1
Select * from #no
Drop table #no
--
This is a problem for me because a lot of my reports collect data from
several tables and insert them into temp tables. But effectively they will
show up with no dependencies and therefor be subject for deletion.
Remodelling all stored procedures to use table variables is no option.
This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error in
the product or whether there is a good reason for doing as it does.> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
One of the reasons dependency information may be missing is due to deferred
name resolution. Since the temp table does not exist when the proc is
created, no dependency info is recorded for any objects referenced by the
INSERT statement.
> Remodelling all stored procedures to use table variables is no option.
If you create the temp table before creating the proc, you should get the
dependency info.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
Although one could make the argument that this is a bug, it's unclear to me
what the correct behavior should be when a statement contains a mix of
existing and non-existing objects. You might consider submitting product
enhancement feedback via Connect (http://connect.microsoft.com/SQLServer) to
suggest that known dependency information be recorded when a statement
contains a mix of existing and non-existing objects. Perhaps this will make
it into the next SQL Server version or future service pack.
Hope this helps.
Dan Guzman
SQL Server MVP
"Karsten Feddersen, Denmark" <Karsten Feddersen,
Denmark@.discussions.microsoft.com> wrote in message
news:48D61C0B-5635-4137-A3E8-C7A7CC16796F@.microsoft.com...
> Hi
> I have a problem. I have a database with way to many stored procedures. In
> order delete some i made a script that finds stored procedures and their
> dependencies. Those without any dependencies should be deleted because
> they
> typically have been emptied for logic.
> But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
> FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
> uses a normal table or a table variable there is a dependency.
> My example below turns out to have no dependecies as all even though the
> table exists up front and the procedure has been executed succesfully.
> Example: --
> CREATE PROCEDURE Test3 AS
> Create Table #no (
> i int
> )
> Insert into #no (i) Select top 10 ID from table_1
> Select * from #no
> Drop table #no
> --
> This is a problem for me because a lot of my reports collect data from
> several tables and insert them into temp tables. But effectively they will
> show up with no dependencies and therefor be subject for deletion.
> Remodelling all stored procedures to use table variables is no option.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
I have a problem. I have a database with way to many stored procedures. In
order delete some i made a script that finds stored procedures and their
dependencies. Those without any dependencies should be deleted because they
typically have been emptied for logic.
But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
uses a normal table or a table variable there is a dependency.
My example below turns out to have no dependecies as all even though the
table exists up front and the procedure has been executed succesfully.
Example: --
CREATE PROCEDURE Test3 AS
Create Table #no (
i int
)
Insert into #no (i) Select top 10 ID from table_1
Select * from #no
Drop table #no
--
This is a problem for me because a lot of my reports collect data from
several tables and insert them into temp tables. But effectively they will
show up with no dependencies and therefor be subject for deletion.
Remodelling all stored procedures to use table variables is no option.
This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error in
the product or whether there is a good reason for doing as it does.> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
One of the reasons dependency information may be missing is due to deferred
name resolution. Since the temp table does not exist when the proc is
created, no dependency info is recorded for any objects referenced by the
INSERT statement.
> Remodelling all stored procedures to use table variables is no option.
If you create the temp table before creating the proc, you should get the
dependency info.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
Although one could make the argument that this is a bug, it's unclear to me
what the correct behavior should be when a statement contains a mix of
existing and non-existing objects. You might consider submitting product
enhancement feedback via Connect (http://connect.microsoft.com/SQLServer) to
suggest that known dependency information be recorded when a statement
contains a mix of existing and non-existing objects. Perhaps this will make
it into the next SQL Server version or future service pack.
Hope this helps.
Dan Guzman
SQL Server MVP
"Karsten Feddersen, Denmark" <Karsten Feddersen,
Denmark@.discussions.microsoft.com> wrote in message
news:48D61C0B-5635-4137-A3E8-C7A7CC16796F@.microsoft.com...
> Hi
> I have a problem. I have a database with way to many stored procedures. In
> order delete some i made a script that finds stored procedures and their
> dependencies. Those without any dependencies should be deleted because
> they
> typically have been emptied for logic.
> But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
> FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
> uses a normal table or a table variable there is a dependency.
> My example below turns out to have no dependecies as all even though the
> table exists up front and the procedure has been executed succesfully.
> Example: --
> CREATE PROCEDURE Test3 AS
> Create Table #no (
> i int
> )
> Insert into #no (i) Select top 10 ID from table_1
> Select * from #no
> Drop table #no
> --
> This is a problem for me because a lot of my reports collect data from
> several tables and insert them into temp tables. But effectively they will
> show up with no dependencies and therefor be subject for deletion.
> Remodelling all stored procedures to use table variables is no option.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
No dependencies when inserting data into a #temp table
Hi
I have a problem. I have a database with way to many stored procedures. In
order delete some i made a script that finds stored procedures and their
dependencies. Those without any dependencies should be deleted because they
typically have been emptied for logic.
But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
uses a normal table or a table variable there is a dependency.
My example below turns out to have no dependecies as all even though the
table exists up front and the procedure has been executed succesfully.
Example: --
CREATE PROCEDURE Test3 AS
Create Table #no (
i int
)
Insert into #no (i)Select top 10 ID from table_1
Select * from #no
Drop table #no
This is a problem for me because a lot of my reports collect data from
several tables and insert them into temp tables. But effectively they will
show up with no dependencies and therefor be subject for deletion.
Remodelling all stored procedures to use table variables is no option.
This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error in
the product or whether there is a good reason for doing as it does.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
One of the reasons dependency information may be missing is due to deferred
name resolution. Since the temp table does not exist when the proc is
created, no dependency info is recorded for any objects referenced by the
INSERT statement.
> Remodelling all stored procedures to use table variables is no option.
If you create the temp table before creating the proc, you should get the
dependency info.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
Although one could make the argument that this is a bug, it's unclear to me
what the correct behavior should be when a statement contains a mix of
existing and non-existing objects. You might consider submitting product
enhancement feedback via Connect (http://connect.microsoft.com/SQLServer) to
suggest that known dependency information be recorded when a statement
contains a mix of existing and non-existing objects. Perhaps this will make
it into the next SQL Server version or future service pack.
Hope this helps.
Dan Guzman
SQL Server MVP
"Karsten Feddersen, Denmark" <Karsten Feddersen,
Denmark@.discussions.microsoft.com> wrote in message
news:48D61C0B-5635-4137-A3E8-C7A7CC16796F@.microsoft.com...
> Hi
> I have a problem. I have a database with way to many stored procedures. In
> order delete some i made a script that finds stored procedures and their
> dependencies. Those without any dependencies should be deleted because
> they
> typically have been emptied for logic.
> But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
> FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
> uses a normal table or a table variable there is a dependency.
> My example below turns out to have no dependecies as all even though the
> table exists up front and the procedure has been executed succesfully.
> Example: --
> CREATE PROCEDURE Test3 AS
> Create Table #no (
> i int
> )
> Insert into #no (i) Select top 10 ID from table_1
> Select * from #no
> Drop table #no
> --
> This is a problem for me because a lot of my reports collect data from
> several tables and insert them into temp tables. But effectively they will
> show up with no dependencies and therefor be subject for deletion.
> Remodelling all stored procedures to use table variables is no option.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
I have a problem. I have a database with way to many stored procedures. In
order delete some i made a script that finds stored procedures and their
dependencies. Those without any dependencies should be deleted because they
typically have been emptied for logic.
But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
uses a normal table or a table variable there is a dependency.
My example below turns out to have no dependecies as all even though the
table exists up front and the procedure has been executed succesfully.
Example: --
CREATE PROCEDURE Test3 AS
Create Table #no (
i int
)
Insert into #no (i)Select top 10 ID from table_1
Select * from #no
Drop table #no
This is a problem for me because a lot of my reports collect data from
several tables and insert them into temp tables. But effectively they will
show up with no dependencies and therefor be subject for deletion.
Remodelling all stored procedures to use table variables is no option.
This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error in
the product or whether there is a good reason for doing as it does.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
One of the reasons dependency information may be missing is due to deferred
name resolution. Since the temp table does not exist when the proc is
created, no dependency info is recorded for any objects referenced by the
INSERT statement.
> Remodelling all stored procedures to use table variables is no option.
If you create the temp table before creating the proc, you should get the
dependency info.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
Although one could make the argument that this is a bug, it's unclear to me
what the correct behavior should be when a statement contains a mix of
existing and non-existing objects. You might consider submitting product
enhancement feedback via Connect (http://connect.microsoft.com/SQLServer) to
suggest that known dependency information be recorded when a statement
contains a mix of existing and non-existing objects. Perhaps this will make
it into the next SQL Server version or future service pack.
Hope this helps.
Dan Guzman
SQL Server MVP
"Karsten Feddersen, Denmark" <Karsten Feddersen,
Denmark@.discussions.microsoft.com> wrote in message
news:48D61C0B-5635-4137-A3E8-C7A7CC16796F@.microsoft.com...
> Hi
> I have a problem. I have a database with way to many stored procedures. In
> order delete some i made a script that finds stored procedures and their
> dependencies. Those without any dependencies should be deleted because
> they
> typically have been emptied for logic.
> But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
> FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
> uses a normal table or a table variable there is a dependency.
> My example below turns out to have no dependecies as all even though the
> table exists up front and the procedure has been executed succesfully.
> Example: --
> CREATE PROCEDURE Test3 AS
> Create Table #no (
> i int
> )
> Insert into #no (i) Select top 10 ID from table_1
> Select * from #no
> Drop table #no
> --
> This is a problem for me because a lot of my reports collect data from
> several tables and insert them into temp tables. But effectively they will
> show up with no dependencies and therefor be subject for deletion.
> Remodelling all stored procedures to use table variables is no option.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
Tuesday, March 20, 2012
no "properties" option on stored procedures (cant set permissions)
SQL 2005 express.
I detached an SQL 2005 express database from server A and attached to SQL
2005 express on server B.
When i right click stored procedure from server A i see a PROPERITES
options.
When i do the same from server B ..... NO PROPERTIES option is available.
I view the SPs in both DBs using SQL Express Mgt studio. Both managers are
logged in as SA.
Any ideas ?
Thank
Scott
---
http://www.sage-eshop.comJust a wild guess:
Perhaps the owner of the database isn't a login on server B? See sp_changedbowner.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <http://www.sage-eshop.com> wrote in message news:uommvsq9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> SQL 2005 express.
> I detached an SQL 2005 express database from server A and attached to SQL
> 2005 express on server B.
> When i right click stored procedure from server A i see a PROPERITES
> options.
> When i do the same from server B ..... NO PROPERTIES option is available.
> I view the SPs in both DBs using SQL Express Mgt studio. Both managers are
> logged in as SA.
> Any ideas ?
> Thank
> Scott
> ---
> http://www.sage-eshop.com
>|||thanks for reply.
i can see sp_changedbowner, it executes ok.
im not exactly sure what i should do with it now.
scott.|||My guess is that the owner for the database (the SID) doesn't exist as a login in the master
database. Try setting the owner to sa, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <http://www.sage-eshop.com> wrote in message
news:uF%233%23Hr9GHA.4012@.TK2MSFTNGP04.phx.gbl...
> thanks for reply.
> i can see sp_changedbowner, it executes ok.
> im not exactly sure what i should do with it now.
> scott.
>|||i created a new DB and new SP and still dont get the properties.
if the user has the ability to create a DB and SPs in the db, they should
have the ability to view/amend properties.|||Might be a bug in the GUI you are using. If you are using the express version of management studio,
you might want to post to an express group. I didn't find such on this news server, but you can try
the HTTP groups:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <http://www.sage-eshop.com> wrote in message news:et40aRr9GHA.3352@.TK2MSFTNGP03.phx.gbl...
>i created a new DB and new SP and still dont get the properties.
> if the user has the ability to create a DB and SPs in the db, they should have the ability to
> view/amend properties.
>|||Hi ..
I'm getting exactly the same issue.. We've gone and created our
databases in SQL Express, but have a code generator that needs to look
at extended proeprties on the stored procedures. In SQL Express
databases, the Properties option is not available for SP's,
Personally I just think this is a bug in the Management Studio UI too,
as Properties is available for the other objects such as Tables,
Databases (do it's not that SQL Express doesn't supoprt Extended
Properties, which was my first guess).
If you get any luck, please post a message here.
Scott wrote:
> i created a new DB and new SP and still dont get the properties.
> if the user has the ability to create a DB and SPs in the db, they should
> have the ability to view/amend properties.
I detached an SQL 2005 express database from server A and attached to SQL
2005 express on server B.
When i right click stored procedure from server A i see a PROPERITES
options.
When i do the same from server B ..... NO PROPERTIES option is available.
I view the SPs in both DBs using SQL Express Mgt studio. Both managers are
logged in as SA.
Any ideas ?
Thank
Scott
---
http://www.sage-eshop.comJust a wild guess:
Perhaps the owner of the database isn't a login on server B? See sp_changedbowner.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <http://www.sage-eshop.com> wrote in message news:uommvsq9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> SQL 2005 express.
> I detached an SQL 2005 express database from server A and attached to SQL
> 2005 express on server B.
> When i right click stored procedure from server A i see a PROPERITES
> options.
> When i do the same from server B ..... NO PROPERTIES option is available.
> I view the SPs in both DBs using SQL Express Mgt studio. Both managers are
> logged in as SA.
> Any ideas ?
> Thank
> Scott
> ---
> http://www.sage-eshop.com
>|||thanks for reply.
i can see sp_changedbowner, it executes ok.
im not exactly sure what i should do with it now.
scott.|||My guess is that the owner for the database (the SID) doesn't exist as a login in the master
database. Try setting the owner to sa, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <http://www.sage-eshop.com> wrote in message
news:uF%233%23Hr9GHA.4012@.TK2MSFTNGP04.phx.gbl...
> thanks for reply.
> i can see sp_changedbowner, it executes ok.
> im not exactly sure what i should do with it now.
> scott.
>|||i created a new DB and new SP and still dont get the properties.
if the user has the ability to create a DB and SPs in the db, they should
have the ability to view/amend properties.|||Might be a bug in the GUI you are using. If you are using the express version of management studio,
you might want to post to an express group. I didn't find such on this news server, but you can try
the HTTP groups:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <http://www.sage-eshop.com> wrote in message news:et40aRr9GHA.3352@.TK2MSFTNGP03.phx.gbl...
>i created a new DB and new SP and still dont get the properties.
> if the user has the ability to create a DB and SPs in the db, they should have the ability to
> view/amend properties.
>|||Hi ..
I'm getting exactly the same issue.. We've gone and created our
databases in SQL Express, but have a code generator that needs to look
at extended proeprties on the stored procedures. In SQL Express
databases, the Properties option is not available for SP's,
Personally I just think this is a bug in the Management Studio UI too,
as Properties is available for the other objects such as Tables,
Databases (do it's not that SQL Express doesn't supoprt Extended
Properties, which was my first guess).
If you get any luck, please post a message here.
Scott wrote:
> i created a new DB and new SP and still dont get the properties.
> if the user has the ability to create a DB and SPs in the db, they should
> have the ability to view/amend properties.
Monday, March 19, 2012
Nightly Update / Fallback protection
I have become involved with an assessment of a client's nightly update
procedures, and I keep running into the following practice.
The client consistently copies databases off to separate locations and
performs updates upon the new copy. After successful update, the
updated database is then copied back to the origin location and
becomes the new version. All of this, obviously, to protect
themselves in case of error. I can't help but suspect that SQL Server
provides an easier way to do it.
In the mainframe universe, this used to be accomplished via
generational datasets which would be promoted as the baseline when the
update completes. I guess what I'm asking is if SQL Server provides
any kind of "checkpoint-fallback" capability that the client could use
instead of this monstrous time-consuming practice. When talking
thousands of updates in a batch, I'm not sure transactioning would be
the best way to do it. That would be one big honking rollback.
Thanks in advance to any of you architects out there!SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
(CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
the Copy databases' database files. This way, the copy database always has the data as it was when
the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
the Orig database' files and you are back where you began. Concept is called "Copy on write".
Same-same (concept) but different (implementation). :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||It is called a backup before you do any changes. Then if there is an issue
you simply restore.
--
Andrew J. Kelly SQL MVP
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||On Apr 23, 1:23 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
> empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
> modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
> the Copy databases' database files. This way, the copy database always has the data as it was when
> the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
> the Orig database' files and you are back where you began. Concept is called "Copy on write".
> Same-same (concept) but different (implementation). :-)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "BizEd" <EdMathia...@.gmail.com> wrote in message
> news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>
> >I have become involved with an assessment of a client's nightly update
> > procedures, and I keep running into the following practice.
> > The client consistently copies databases off to separate locations and
> > performs updates upon the new copy. After successful update, the
> > updated database is then copied back to the origin location and
> > becomes the new version. All of this, obviously, to protect
> > themselves in case of error. I can't help but suspect that SQL Server
> > provides an easier way to do it.
> > In the mainframe universe, this used to be accomplished via
> > generational datasets which would be promoted as the baseline when the
> > update completes. I guess what I'm asking is if SQL Server provides
> > any kind of "checkpoint-fallback" capability that the client could use
> > instead of this monstrous time-consuming practice. When talking
> > thousands of updates in a batch, I'm not sure transactioning would be
> > the best way to do it. That would be one big honking rollback.
> > Thanks in advance to any of you architects out there!- Hide quoted text -
> - Show quoted text -
Tibor,
Thanks. That's the type of functionality I was looking for. A simple
backup is not a sufficient solution when you are talking about a
multiple stage update process. If you wish to restart a step in the
process, the backup would necessitate going all the way back to the
beginning. Your solution is in essence "a delta file" which provides
functionality much more in line with the concept of mainframe
generational data sets!
Now to get the client on to SQL 2005....harrumph!|||Note that you can have several "delta-files" (several database snapshots). But if you want to
restore from any of them, you need for first DROP DATABASE for all those snapshots except the one to
restore from.
Another important thing is that when you restore from a database snapshot, you break the log backup
chain sequence for your database. So, if you are doing log backups for that database, you want to do
a BACKUP DATABASE after restoring from a database snapshot.
And, as always, make sure you read all about this in Books Online first. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177365104.403457.180780@.b58g2000hsg.googlegroups.com...
> On Apr 23, 1:23 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
>> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are
>> initially
>> empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take.
>> Then
>> modify your Orig database. As you modify Orig database, the pages to be modified are first copied
>> to
>> the Copy databases' database files. This way, the copy database always has the data as it was
>> when
>> the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
>> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back
>> to
>> the Orig database' files and you are back where you began. Concept is called "Copy on write".
>> Same-same (concept) but different (implementation). :-)
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "BizEd" <EdMathia...@.gmail.com> wrote in message
>> news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>>
>> >I have become involved with an assessment of a client's nightly update
>> > procedures, and I keep running into the following practice.
>> > The client consistently copies databases off to separate locations and
>> > performs updates upon the new copy. After successful update, the
>> > updated database is then copied back to the origin location and
>> > becomes the new version. All of this, obviously, to protect
>> > themselves in case of error. I can't help but suspect that SQL Server
>> > provides an easier way to do it.
>> > In the mainframe universe, this used to be accomplished via
>> > generational datasets which would be promoted as the baseline when the
>> > update completes. I guess what I'm asking is if SQL Server provides
>> > any kind of "checkpoint-fallback" capability that the client could use
>> > instead of this monstrous time-consuming practice. When talking
>> > thousands of updates in a batch, I'm not sure transactioning would be
>> > the best way to do it. That would be one big honking rollback.
>> > Thanks in advance to any of you architects out there!- Hide quoted text -
>> - Show quoted text -
> Tibor,
> Thanks. That's the type of functionality I was looking for. A simple
> backup is not a sufficient solution when you are talking about a
> multiple stage update process. If you wish to restart a step in the
> process, the backup would necessitate going all the way back to the
> beginning. Your solution is in essence "a delta file" which provides
> functionality much more in line with the concept of mainframe
> generational data sets!
> Now to get the client on to SQL 2005....harrumph!
>|||As a standard process, this seems excessive.
You are aware that within the processing, SQLServer (like most major
databases) allows you to define a transaction that holds off
commitment until all the steps have completed successfully, and can be
rolled back if any errors are detected?
Josh
On 23 Apr 2007 11:10:16 -0700, BizEd <EdMathias18@.gmail.com> wrote:
>I have become involved with an assessment of a client's nightly update
>procedures, and I keep running into the following practice.
>The client consistently copies databases off to separate locations and
>performs updates upon the new copy. After successful update, the
>updated database is then copied back to the origin location and
>becomes the new version. All of this, obviously, to protect
>themselves in case of error. I can't help but suspect that SQL Server
>provides an easier way to do it.
>In the mainframe universe, this used to be accomplished via
>generational datasets which would be promoted as the baseline when the
>update completes. I guess what I'm asking is if SQL Server provides
>any kind of "checkpoint-fallback" capability that the client could use
>instead of this monstrous time-consuming practice. When talking
>thousands of updates in a batch, I'm not sure transactioning would be
>the best way to do it. That would be one big honking rollback.
>Thanks in advance to any of you architects out there!
procedures, and I keep running into the following practice.
The client consistently copies databases off to separate locations and
performs updates upon the new copy. After successful update, the
updated database is then copied back to the origin location and
becomes the new version. All of this, obviously, to protect
themselves in case of error. I can't help but suspect that SQL Server
provides an easier way to do it.
In the mainframe universe, this used to be accomplished via
generational datasets which would be promoted as the baseline when the
update completes. I guess what I'm asking is if SQL Server provides
any kind of "checkpoint-fallback" capability that the client could use
instead of this monstrous time-consuming practice. When talking
thousands of updates in a batch, I'm not sure transactioning would be
the best way to do it. That would be one big honking rollback.
Thanks in advance to any of you architects out there!SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
(CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
the Copy databases' database files. This way, the copy database always has the data as it was when
the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
the Orig database' files and you are back where you began. Concept is called "Copy on write".
Same-same (concept) but different (implementation). :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||It is called a backup before you do any changes. Then if there is an issue
you simply restore.
--
Andrew J. Kelly SQL MVP
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>I have become involved with an assessment of a client's nightly update
> procedures, and I keep running into the following practice.
> The client consistently copies databases off to separate locations and
> performs updates upon the new copy. After successful update, the
> updated database is then copied back to the origin location and
> becomes the new version. All of this, obviously, to protect
> themselves in case of error. I can't help but suspect that SQL Server
> provides an easier way to do it.
> In the mainframe universe, this used to be accomplished via
> generational datasets which would be promoted as the baseline when the
> update completes. I guess what I'm asking is if SQL Server provides
> any kind of "checkpoint-fallback" capability that the client could use
> instead of this monstrous time-consuming practice. When talking
> thousands of updates in a batch, I'm not sure transactioning would be
> the best way to do it. That would be one big honking rollback.
> Thanks in advance to any of you architects out there!
>|||On Apr 23, 1:23 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are initially
> empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take. Then
> modify your Orig database. As you modify Orig database, the pages to be modified are first copied to
> the Copy databases' database files. This way, the copy database always has the data as it was when
> the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back to
> the Orig database' files and you are back where you began. Concept is called "Copy on write".
> Same-same (concept) but different (implementation). :-)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "BizEd" <EdMathia...@.gmail.com> wrote in message
> news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>
> >I have become involved with an assessment of a client's nightly update
> > procedures, and I keep running into the following practice.
> > The client consistently copies databases off to separate locations and
> > performs updates upon the new copy. After successful update, the
> > updated database is then copied back to the origin location and
> > becomes the new version. All of this, obviously, to protect
> > themselves in case of error. I can't help but suspect that SQL Server
> > provides an easier way to do it.
> > In the mainframe universe, this used to be accomplished via
> > generational datasets which would be promoted as the baseline when the
> > update completes. I guess what I'm asking is if SQL Server provides
> > any kind of "checkpoint-fallback" capability that the client could use
> > instead of this monstrous time-consuming practice. When talking
> > thousands of updates in a batch, I'm not sure transactioning would be
> > the best way to do it. That would be one big honking rollback.
> > Thanks in advance to any of you architects out there!- Hide quoted text -
> - Show quoted text -
Tibor,
Thanks. That's the type of functionality I was looking for. A simple
backup is not a sufficient solution when you are talking about a
multiple stage update process. If you wish to restart a step in the
process, the backup would necessitate going all the way back to the
beginning. Your solution is in essence "a delta file" which provides
functionality much more in line with the concept of mainframe
generational data sets!
Now to get the client on to SQL 2005....harrumph!|||Note that you can have several "delta-files" (several database snapshots). But if you want to
restore from any of them, you need for first DROP DATABASE for all those snapshots except the one to
restore from.
Another important thing is that when you restore from a database snapshot, you break the log backup
chain sequence for your database. So, if you are doing log backups for that database, you want to do
a BACKUP DATABASE after restoring from a database snapshot.
And, as always, make sure you read all about this in Books Online first. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BizEd" <EdMathias18@.gmail.com> wrote in message
news:1177365104.403457.180780@.b58g2000hsg.googlegroups.com...
> On Apr 23, 1:23 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> SQL Server 2005 (Enterprise Edition) has, sort of, this. You can create a "database snapshot"
>> (CREATE DATABASE Copy AS SNAPSHOT OF Orig). The database files for the Copy database are
>> initially
>> empty and consume close to 0 disk space, i.e., the snapshot takes only a few seconds to take.
>> Then
>> modify your Orig database. As you modify Orig database, the pages to be modified are first copied
>> to
>> the Copy databases' database files. This way, the copy database always has the data as it was
>> when
>> the snapshot was produced. If you need to "fall-back", then you can RESTORE DATABASE Orig FROM
>> DATABASE_SNAPSHOT 'Copy', and all pages (in the Copy databases' database files) are copied back
>> to
>> the Orig database' files and you are back where you began. Concept is called "Copy on write".
>> Same-same (concept) but different (implementation). :-)
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "BizEd" <EdMathia...@.gmail.com> wrote in message
>> news:1177351816.251254.221810@.b58g2000hsg.googlegroups.com...
>>
>> >I have become involved with an assessment of a client's nightly update
>> > procedures, and I keep running into the following practice.
>> > The client consistently copies databases off to separate locations and
>> > performs updates upon the new copy. After successful update, the
>> > updated database is then copied back to the origin location and
>> > becomes the new version. All of this, obviously, to protect
>> > themselves in case of error. I can't help but suspect that SQL Server
>> > provides an easier way to do it.
>> > In the mainframe universe, this used to be accomplished via
>> > generational datasets which would be promoted as the baseline when the
>> > update completes. I guess what I'm asking is if SQL Server provides
>> > any kind of "checkpoint-fallback" capability that the client could use
>> > instead of this monstrous time-consuming practice. When talking
>> > thousands of updates in a batch, I'm not sure transactioning would be
>> > the best way to do it. That would be one big honking rollback.
>> > Thanks in advance to any of you architects out there!- Hide quoted text -
>> - Show quoted text -
> Tibor,
> Thanks. That's the type of functionality I was looking for. A simple
> backup is not a sufficient solution when you are talking about a
> multiple stage update process. If you wish to restart a step in the
> process, the backup would necessitate going all the way back to the
> beginning. Your solution is in essence "a delta file" which provides
> functionality much more in line with the concept of mainframe
> generational data sets!
> Now to get the client on to SQL 2005....harrumph!
>|||As a standard process, this seems excessive.
You are aware that within the processing, SQLServer (like most major
databases) allows you to define a transaction that holds off
commitment until all the steps have completed successfully, and can be
rolled back if any errors are detected?
Josh
On 23 Apr 2007 11:10:16 -0700, BizEd <EdMathias18@.gmail.com> wrote:
>I have become involved with an assessment of a client's nightly update
>procedures, and I keep running into the following practice.
>The client consistently copies databases off to separate locations and
>performs updates upon the new copy. After successful update, the
>updated database is then copied back to the origin location and
>becomes the new version. All of this, obviously, to protect
>themselves in case of error. I can't help but suspect that SQL Server
>provides an easier way to do it.
>In the mainframe universe, this used to be accomplished via
>generational datasets which would be promoted as the baseline when the
>update completes. I guess what I'm asking is if SQL Server provides
>any kind of "checkpoint-fallback" capability that the client could use
>instead of this monstrous time-consuming practice. When talking
>thousands of updates in a batch, I'm not sure transactioning would be
>the best way to do it. That would be one big honking rollback.
>Thanks in advance to any of you architects out there!
Wednesday, March 7, 2012
Newly created stored procedure => master db
I have upgraded my SQLExpress to SP1 but since doing this I am not able to
create stored procedures under the database that I created. It seems as if
any new procs are created in the master database.
If I create a new proc, it is not displayed under the stored procedure
folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
Studio Express\Projects along with the other procs that I'd created prior to
the upgrade.
If I open the newly created proc then I'm asked to login to the SQL Express
again (I already have Management Studio Express open and logged in to my own
database). Once the proc is open , the combo box "available databases" on the
toolbar displayes, 'master'. So it seems I've logged in to the master
database where the new proc has been created. But it is not displayed in the
Stored Procs of the master either.
What has gone wrong? I should be able to create new stored procs in my
database and not in the master database.
Can someone help - thanksLisa
What is happenening if you issue?
USE mydb
GO
CREATE PROC mmmm
AS
....
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
>I have upgraded my SQLExpress to SP1 but since doing this I am not able to
> create stored procedures under the database that I created. It seems as if
> any new procs are created in the master database.
> If I create a new proc, it is not displayed under the stored procedure
> folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
> Studio Express\Projects along with the other procs that I'd created prior
> to
> the upgrade.
> If I open the newly created proc then I'm asked to login to the SQL
> Express
> again (I already have Management Studio Express open and logged in to my
> own
> database). Once the proc is open , the combo box "available databases" on
> the
> toolbar displayes, 'master'. So it seems I've logged in to the master
> database where the new proc has been created. But it is not displayed in
> the
> Stored Procs of the master either.
> What has gone wrong? I should be able to create new stored procs in my
> database and not in the master database.
> Can someone help - thanks|||Thanks for your response. Unfortunately it makes no difference. I created the
new proc and then opened it, but the Connect to Database Engine dialog box is
displayed and I connect to the master db.
"Uri Dimant" wrote:
> Lisa
> What is happenening if you issue?
> USE mydb
> GO
> CREATE PROC mmmm
> AS
> .....
>
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
> news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
> >I have upgraded my SQLExpress to SP1 but since doing this I am not able to
> > create stored procedures under the database that I created. It seems as if
> > any new procs are created in the master database.
> >
> > If I create a new proc, it is not displayed under the stored procedure
> > folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
> > Studio Express\Projects along with the other procs that I'd created prior
> > to
> > the upgrade.
> >
> > If I open the newly created proc then I'm asked to login to the SQL
> > Express
> > again (I already have Management Studio Express open and logged in to my
> > own
> > database). Once the proc is open , the combo box "available databases" on
> > the
> > toolbar displayes, 'master'. So it seems I've logged in to the master
> > database where the new proc has been created. But it is not displayed in
> > the
> > Stored Procs of the master either.
> >
> > What has gone wrong? I should be able to create new stored procs in my
> > database and not in the master database.
> > Can someone help - thanks
>
>|||Lisa
Ok, it is by default. You can change it by using
EXEC sp_defaultdb 'Lisa', 'AdventureWorks'
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:16DB04B0-8E9B-4C8C-88FD-F881A31AA781@.microsoft.com...
> Thanks for your response. Unfortunately it makes no difference. I created
> the
> new proc and then opened it, but the Connect to Database Engine dialog box
> is
> displayed and I connect to the master db.
>
> "Uri Dimant" wrote:
>> Lisa
>> What is happenening if you issue?
>> USE mydb
>> GO
>> CREATE PROC mmmm
>> AS
>> .....
>>
>> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in
>> message
>> news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
>> >I have upgraded my SQLExpress to SP1 but since doing this I am not able
>> >to
>> > create stored procedures under the database that I created. It seems as
>> > if
>> > any new procs are created in the master database.
>> >
>> > If I create a new proc, it is not displayed under the stored procedure
>> > folder. Iit is displayed in the folder c:\mydocuments\SQL
>> > ServerManagement
>> > Studio Express\Projects along with the other procs that I'd created
>> > prior
>> > to
>> > the upgrade.
>> >
>> > If I open the newly created proc then I'm asked to login to the SQL
>> > Express
>> > again (I already have Management Studio Express open and logged in to
>> > my
>> > own
>> > database). Once the proc is open , the combo box "available databases"
>> > on
>> > the
>> > toolbar displayes, 'master'. So it seems I've logged in to the master
>> > database where the new proc has been created. But it is not displayed
>> > in
>> > the
>> > Stored Procs of the master either.
>> >
>> > What has gone wrong? I should be able to create new stored procs in my
>> > database and not in the master database.
>> > Can someone help - thanks
>>|||Uri
I don't want to seem ignorant but where do I use this. I'm using Access as
my front end and SQLExpress as the backend. :(
Also - I'm not able to modify any of the previous stored procs. If I make a
modification and save by overwriting the proc, the changes are not there when
I open the proc again.
"Uri Dimant" wrote:
> Lisa
> Ok, it is by default. You can change it by using
> EXEC sp_defaultdb 'Lisa', 'AdventureWorks'
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
> news:16DB04B0-8E9B-4C8C-88FD-F881A31AA781@.microsoft.com...
> > Thanks for your response. Unfortunately it makes no difference. I created
> > the
> > new proc and then opened it, but the Connect to Database Engine dialog box
> > is
> > displayed and I connect to the master db.
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Lisa
> >> What is happenening if you issue?
> >> USE mydb
> >> GO
> >> CREATE PROC mmmm
> >> AS
> >> .....
> >>
> >>
> >> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in
> >> message
> >> news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
> >> >I have upgraded my SQLExpress to SP1 but since doing this I am not able
> >> >to
> >> > create stored procedures under the database that I created. It seems as
> >> > if
> >> > any new procs are created in the master database.
> >> >
> >> > If I create a new proc, it is not displayed under the stored procedure
> >> > folder. Iit is displayed in the folder c:\mydocuments\SQL
> >> > ServerManagement
> >> > Studio Express\Projects along with the other procs that I'd created
> >> > prior
> >> > to
> >> > the upgrade.
> >> >
> >> > If I open the newly created proc then I'm asked to login to the SQL
> >> > Express
> >> > again (I already have Management Studio Express open and logged in to
> >> > my
> >> > own
> >> > database). Once the proc is open , the combo box "available databases"
> >> > on
> >> > the
> >> > toolbar displayes, 'master'. So it seems I've logged in to the master
> >> > database where the new proc has been created. But it is not displayed
> >> > in
> >> > the
> >> > Stored Procs of the master either.
> >> >
> >> > What has gone wrong? I should be able to create new stored procs in my
> >> > database and not in the master database.
> >> > Can someone help - thanks
> >>
> >>
> >>
>
>
create stored procedures under the database that I created. It seems as if
any new procs are created in the master database.
If I create a new proc, it is not displayed under the stored procedure
folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
Studio Express\Projects along with the other procs that I'd created prior to
the upgrade.
If I open the newly created proc then I'm asked to login to the SQL Express
again (I already have Management Studio Express open and logged in to my own
database). Once the proc is open , the combo box "available databases" on the
toolbar displayes, 'master'. So it seems I've logged in to the master
database where the new proc has been created. But it is not displayed in the
Stored Procs of the master either.
What has gone wrong? I should be able to create new stored procs in my
database and not in the master database.
Can someone help - thanksLisa
What is happenening if you issue?
USE mydb
GO
CREATE PROC mmmm
AS
....
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
>I have upgraded my SQLExpress to SP1 but since doing this I am not able to
> create stored procedures under the database that I created. It seems as if
> any new procs are created in the master database.
> If I create a new proc, it is not displayed under the stored procedure
> folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
> Studio Express\Projects along with the other procs that I'd created prior
> to
> the upgrade.
> If I open the newly created proc then I'm asked to login to the SQL
> Express
> again (I already have Management Studio Express open and logged in to my
> own
> database). Once the proc is open , the combo box "available databases" on
> the
> toolbar displayes, 'master'. So it seems I've logged in to the master
> database where the new proc has been created. But it is not displayed in
> the
> Stored Procs of the master either.
> What has gone wrong? I should be able to create new stored procs in my
> database and not in the master database.
> Can someone help - thanks|||Thanks for your response. Unfortunately it makes no difference. I created the
new proc and then opened it, but the Connect to Database Engine dialog box is
displayed and I connect to the master db.
"Uri Dimant" wrote:
> Lisa
> What is happenening if you issue?
> USE mydb
> GO
> CREATE PROC mmmm
> AS
> .....
>
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
> news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
> >I have upgraded my SQLExpress to SP1 but since doing this I am not able to
> > create stored procedures under the database that I created. It seems as if
> > any new procs are created in the master database.
> >
> > If I create a new proc, it is not displayed under the stored procedure
> > folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
> > Studio Express\Projects along with the other procs that I'd created prior
> > to
> > the upgrade.
> >
> > If I open the newly created proc then I'm asked to login to the SQL
> > Express
> > again (I already have Management Studio Express open and logged in to my
> > own
> > database). Once the proc is open , the combo box "available databases" on
> > the
> > toolbar displayes, 'master'. So it seems I've logged in to the master
> > database where the new proc has been created. But it is not displayed in
> > the
> > Stored Procs of the master either.
> >
> > What has gone wrong? I should be able to create new stored procs in my
> > database and not in the master database.
> > Can someone help - thanks
>
>|||Lisa
Ok, it is by default. You can change it by using
EXEC sp_defaultdb 'Lisa', 'AdventureWorks'
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:16DB04B0-8E9B-4C8C-88FD-F881A31AA781@.microsoft.com...
> Thanks for your response. Unfortunately it makes no difference. I created
> the
> new proc and then opened it, but the Connect to Database Engine dialog box
> is
> displayed and I connect to the master db.
>
> "Uri Dimant" wrote:
>> Lisa
>> What is happenening if you issue?
>> USE mydb
>> GO
>> CREATE PROC mmmm
>> AS
>> .....
>>
>> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in
>> message
>> news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
>> >I have upgraded my SQLExpress to SP1 but since doing this I am not able
>> >to
>> > create stored procedures under the database that I created. It seems as
>> > if
>> > any new procs are created in the master database.
>> >
>> > If I create a new proc, it is not displayed under the stored procedure
>> > folder. Iit is displayed in the folder c:\mydocuments\SQL
>> > ServerManagement
>> > Studio Express\Projects along with the other procs that I'd created
>> > prior
>> > to
>> > the upgrade.
>> >
>> > If I open the newly created proc then I'm asked to login to the SQL
>> > Express
>> > again (I already have Management Studio Express open and logged in to
>> > my
>> > own
>> > database). Once the proc is open , the combo box "available databases"
>> > on
>> > the
>> > toolbar displayes, 'master'. So it seems I've logged in to the master
>> > database where the new proc has been created. But it is not displayed
>> > in
>> > the
>> > Stored Procs of the master either.
>> >
>> > What has gone wrong? I should be able to create new stored procs in my
>> > database and not in the master database.
>> > Can someone help - thanks
>>|||Uri
I don't want to seem ignorant but where do I use this. I'm using Access as
my front end and SQLExpress as the backend. :(
Also - I'm not able to modify any of the previous stored procs. If I make a
modification and save by overwriting the proc, the changes are not there when
I open the proc again.
"Uri Dimant" wrote:
> Lisa
> Ok, it is by default. You can change it by using
> EXEC sp_defaultdb 'Lisa', 'AdventureWorks'
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
> news:16DB04B0-8E9B-4C8C-88FD-F881A31AA781@.microsoft.com...
> > Thanks for your response. Unfortunately it makes no difference. I created
> > the
> > new proc and then opened it, but the Connect to Database Engine dialog box
> > is
> > displayed and I connect to the master db.
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Lisa
> >> What is happenening if you issue?
> >> USE mydb
> >> GO
> >> CREATE PROC mmmm
> >> AS
> >> .....
> >>
> >>
> >> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in
> >> message
> >> news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
> >> >I have upgraded my SQLExpress to SP1 but since doing this I am not able
> >> >to
> >> > create stored procedures under the database that I created. It seems as
> >> > if
> >> > any new procs are created in the master database.
> >> >
> >> > If I create a new proc, it is not displayed under the stored procedure
> >> > folder. Iit is displayed in the folder c:\mydocuments\SQL
> >> > ServerManagement
> >> > Studio Express\Projects along with the other procs that I'd created
> >> > prior
> >> > to
> >> > the upgrade.
> >> >
> >> > If I open the newly created proc then I'm asked to login to the SQL
> >> > Express
> >> > again (I already have Management Studio Express open and logged in to
> >> > my
> >> > own
> >> > database). Once the proc is open , the combo box "available databases"
> >> > on
> >> > the
> >> > toolbar displayes, 'master'. So it seems I've logged in to the master
> >> > database where the new proc has been created. But it is not displayed
> >> > in
> >> > the
> >> > Stored Procs of the master either.
> >> >
> >> > What has gone wrong? I should be able to create new stored procs in my
> >> > database and not in the master database.
> >> > Can someone help - thanks
> >>
> >>
> >>
>
>
Newly created stored procedure => master db
I have upgraded my SQLExpress to SP1 but since doing this I am not able to
create stored procedures under the database that I created. It seems as if
any new procs are created in the master database.
If I create a new proc, it is not displayed under the stored procedure
folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
Studio Express\Projects along with the other procs that I'd created prior to
the upgrade.
If I open the newly created proc then I'm asked to login to the SQL Express
again (I already have Management Studio Express open and logged in to my own
database). Once the proc is open , the combo box "available databases" on th
e
toolbar displayes, 'master'. So it seems I've logged in to the master
database where the new proc has been created. But it is not displayed in the
Stored Procs of the master either.
What has gone wrong? I should be able to create new stored procs in my
database and not in the master database.
Can someone help - thanksLisa
What is happenening if you issue?
USE mydb
GO
CREATE PROC mmmm
AS
....
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
>I have upgraded my SQLExpress to SP1 but since doing this I am not able to
> create stored procedures under the database that I created. It seems as if
> any new procs are created in the master database.
> If I create a new proc, it is not displayed under the stored procedure
> folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
> Studio Express\Projects along with the other procs that I'd created prior
> to
> the upgrade.
> If I open the newly created proc then I'm asked to login to the SQL
> Express
> again (I already have Management Studio Express open and logged in to my
> own
> database). Once the proc is open , the combo box "available databases" on
> the
> toolbar displayes, 'master'. So it seems I've logged in to the master
> database where the new proc has been created. But it is not displayed in
> the
> Stored Procs of the master either.
> What has gone wrong? I should be able to create new stored procs in my
> database and not in the master database.
> Can someone help - thanks|||Thanks for your response. Unfortunately it makes no difference. I created th
e
new proc and then opened it, but the Connect to Database Engine dialog box i
s
displayed and I connect to the master db.
"Uri Dimant" wrote:
> Lisa
> What is happenening if you issue?
> USE mydb
> GO
> CREATE PROC mmmm
> AS
> .....
>
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
>
>|||Lisa
Ok, it is by default. You can change it by using
EXEC sp_defaultdb 'Lisa', 'AdventureWorks'
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:16DB04B0-8E9B-4C8C-88FD-F881A31AA781@.microsoft.com...[vbcol=seagreen]
> Thanks for your response. Unfortunately it makes no difference. I created
> the
> new proc and then opened it, but the Connect to Database Engine dialog box
> is
> displayed and I connect to the master db.
>
> "Uri Dimant" wrote:
>|||Uri
I don't want to seem ignorant but where do I use this. I'm using Access as
my front end and SQLExpress as the backend.
Also - I'm not able to modify any of the previous stored procs. If I make a
modification and save by overwriting the proc, the changes are not there whe
n
I open the proc again.
"Uri Dimant" wrote:
> Lisa
> Ok, it is by default. You can change it by using
> EXEC sp_defaultdb 'Lisa', 'AdventureWorks'
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:16DB04B0-8E9B-4C8C-88FD-F881A31AA781@.microsoft.com...
>
>
create stored procedures under the database that I created. It seems as if
any new procs are created in the master database.
If I create a new proc, it is not displayed under the stored procedure
folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
Studio Express\Projects along with the other procs that I'd created prior to
the upgrade.
If I open the newly created proc then I'm asked to login to the SQL Express
again (I already have Management Studio Express open and logged in to my own
database). Once the proc is open , the combo box "available databases" on th
e
toolbar displayes, 'master'. So it seems I've logged in to the master
database where the new proc has been created. But it is not displayed in the
Stored Procs of the master either.
What has gone wrong? I should be able to create new stored procs in my
database and not in the master database.
Can someone help - thanksLisa
What is happenening if you issue?
USE mydb
GO
CREATE PROC mmmm
AS
....
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
>I have upgraded my SQLExpress to SP1 but since doing this I am not able to
> create stored procedures under the database that I created. It seems as if
> any new procs are created in the master database.
> If I create a new proc, it is not displayed under the stored procedure
> folder. Iit is displayed in the folder c:\mydocuments\SQL ServerManagement
> Studio Express\Projects along with the other procs that I'd created prior
> to
> the upgrade.
> If I open the newly created proc then I'm asked to login to the SQL
> Express
> again (I already have Management Studio Express open and logged in to my
> own
> database). Once the proc is open , the combo box "available databases" on
> the
> toolbar displayes, 'master'. So it seems I've logged in to the master
> database where the new proc has been created. But it is not displayed in
> the
> Stored Procs of the master either.
> What has gone wrong? I should be able to create new stored procs in my
> database and not in the master database.
> Can someone help - thanks|||Thanks for your response. Unfortunately it makes no difference. I created th
e
new proc and then opened it, but the Connect to Database Engine dialog box i
s
displayed and I connect to the master db.
"Uri Dimant" wrote:
> Lisa
> What is happenening if you issue?
> USE mydb
> GO
> CREATE PROC mmmm
> AS
> .....
>
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:430A9254-6539-4C84-8C57-DBC4E3C0AADA@.microsoft.com...
>
>|||Lisa
Ok, it is by default. You can change it by using
EXEC sp_defaultdb 'Lisa', 'AdventureWorks'
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:16DB04B0-8E9B-4C8C-88FD-F881A31AA781@.microsoft.com...[vbcol=seagreen]
> Thanks for your response. Unfortunately it makes no difference. I created
> the
> new proc and then opened it, but the Connect to Database Engine dialog box
> is
> displayed and I connect to the master db.
>
> "Uri Dimant" wrote:
>|||Uri
I don't want to seem ignorant but where do I use this. I'm using Access as
my front end and SQLExpress as the backend.
Also - I'm not able to modify any of the previous stored procs. If I make a
modification and save by overwriting the proc, the changes are not there whe
n
I open the proc again.
"Uri Dimant" wrote:
> Lisa
> Ok, it is by default. You can change it by using
> EXEC sp_defaultdb 'Lisa', 'AdventureWorks'
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:16DB04B0-8E9B-4C8C-88FD-F881A31AA781@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)