No disk activity during database restore

I am trying to restore a 200 GB database on to a newly formatted SAN
volume. The restore has been running for hours but there doesn't seem
to be a lot of disk activity on the disks I'm restoring to and I'm
wondering if that's anything to worry about.
Using perfmon I'm monitoring the % Disk time for the disk I'm restoring
to. It will show around 99% for a minute or so and then drop to zero
for 3-4 minutes. Is this normal? I know that since I'm restoring to a
new volume that SQL Server needs to zero out the 200 GB before it
starts the actual restore but I would expect to see 100% disk time
while it does this.
Any insight appreciated.
Thanks!
Hi , Can you watch i/o in sysprocesses table for the SPID running the backup
task , this should be changing ...
Vishal
"pshroads@.gmail.com" wrote:

> I am trying to restore a 200 GB database on to a newly formatted SAN
> volume. The restore has been running for hours but there doesn't seem
> to be a lot of disk activity on the disks I'm restoring to and I'm
> wondering if that's anything to worry about.
> Using perfmon I'm monitoring the % Disk time for the disk I'm restoring
> to. It will show around 99% for a minute or so and then drop to zero
> for 3-4 minutes. Is this normal? I know that since I'm restoring to a
> new volume that SQL Server needs to zero out the 200 GB before it
> starts the actual restore but I would expect to see 100% disk time
> while it does this.
> Any insight appreciated.
> Thanks!
>
|||Hi
I see this all the time. Even the IO stats and SPID stats show that nothing
is happening, but it is.
The issue is that before the actual restore can take place, the DB needs to
be created and initialized. This is an operation that SQL Server calls the
OS to do via an API. As the OS just indicates that the operation is still
running, the IO and SPID counter do not change.
The SAN has the instruction, and is doing the hard work. The OS and in turn
SQL Server are waiting for the SAN to finish.
If you were to have 200GB cache on the SAN, the SAN would return almost
immediately and then flush the pages to disk, but you probably don't have so
much, the disk IO is still happening at SAN level.
Deepening on the SAN configuration, 200GB could take anywhere from a few
seconds to a few hours.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vishal Gandhi" <VishalGandhi@.discussions.microsoft.com> wrote in message
news:007A4393-EE3D-4360-98B6-23A5C949FE17@.microsoft.com...[vbcol=seagreen]
> Hi , Can you watch i/o in sysprocesses table for the SPID running the
> backup
> task , this should be changing ...
> Vishal
>
> "pshroads@.gmail.com" wrote:
|||Thanks for your reply. Even though SQL Server passes the task off to
the OS I'm still not sure why I don't see continual disk activity. Why
would I see a minute or two of activity followed by a few minutes of no
activity?
Thanks
|||Hi
Have a look at the SAN management that your SAN vendor supplies. It will
give a you a good idea what is really happening in it.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<pshroads@.gmail.com> wrote in message
news:1119646132.436946.57570@.g14g2000cwa.googlegro ups.com...
> Thanks for your reply. Even though SQL Server passes the task off to
> the OS I'm still not sure why I don't see continual disk activity. Why
> would I see a minute or two of activity followed by a few minutes of no
> activity?
> Thanks
>

No disk activity during database restore

I am trying to restore a 200 GB database on to a newly formatted SAN
volume. The restore has been running for hours but there doesn't seem
to be a lot of disk activity on the disks I'm restoring to and I'm
wondering if that's anything to worry about.
Using perfmon I'm monitoring the % Disk time for the disk I'm restoring
to. It will show around 99% for a minute or so and then drop to zero
for 3-4 minutes. Is this normal? I know that since I'm restoring to a
new volume that SQL Server needs to zero out the 200 GB before it
starts the actual restore but I would expect to see 100% disk time
while it does this.
Any insight appreciated.
Thanks!Hi , Can you watch i/o in sysprocesses table for the SPID running the backup
task , this should be changing ...
Vishal
"pshroads@.gmail.com" wrote:
> I am trying to restore a 200 GB database on to a newly formatted SAN
> volume. The restore has been running for hours but there doesn't seem
> to be a lot of disk activity on the disks I'm restoring to and I'm
> wondering if that's anything to worry about.
> Using perfmon I'm monitoring the % Disk time for the disk I'm restoring
> to. It will show around 99% for a minute or so and then drop to zero
> for 3-4 minutes. Is this normal? I know that since I'm restoring to a
> new volume that SQL Server needs to zero out the 200 GB before it
> starts the actual restore but I would expect to see 100% disk time
> while it does this.
> Any insight appreciated.
> Thanks!
>|||Hi
I see this all the time. Even the IO stats and SPID stats show that nothing
is happening, but it is.
The issue is that before the actual restore can take place, the DB needs to
be created and initialized. This is an operation that SQL Server calls the
OS to do via an API. As the OS just indicates that the operation is still
running, the IO and SPID counter do not change.
The SAN has the instruction, and is doing the hard work. The OS and in turn
SQL Server are waiting for the SAN to finish.
If you were to have 200GB cache on the SAN, the SAN would return almost
immediately and then flush the pages to disk, but you probably don't have so
much, the disk IO is still happening at SAN level.
Deepening on the SAN configuration, 200GB could take anywhere from a few
seconds to a few hours.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vishal Gandhi" <VishalGandhi@.discussions.microsoft.com> wrote in message
news:007A4393-EE3D-4360-98B6-23A5C949FE17@.microsoft.com...
> Hi , Can you watch i/o in sysprocesses table for the SPID running the
> backup
> task , this should be changing ...
> Vishal
>
> "pshroads@.gmail.com" wrote:
>> I am trying to restore a 200 GB database on to a newly formatted SAN
>> volume. The restore has been running for hours but there doesn't seem
>> to be a lot of disk activity on the disks I'm restoring to and I'm
>> wondering if that's anything to worry about.
>> Using perfmon I'm monitoring the % Disk time for the disk I'm restoring
>> to. It will show around 99% for a minute or so and then drop to zero
>> for 3-4 minutes. Is this normal? I know that since I'm restoring to a
>> new volume that SQL Server needs to zero out the 200 GB before it
>> starts the actual restore but I would expect to see 100% disk time
>> while it does this.
>> Any insight appreciated.
>> Thanks!
>>|||Thanks for your reply. Even though SQL Server passes the task off to
the OS I'm still not sure why I don't see continual disk activity. Why
would I see a minute or two of activity followed by a few minutes of no
activity?
Thanks|||Hi
Have a look at the SAN management that your SAN vendor supplies. It will
give a you a good idea what is really happening in it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<pshroads@.gmail.com> wrote in message
news:1119646132.436946.57570@.g14g2000cwa.googlegroups.com...
> Thanks for your reply. Even though SQL Server passes the task off to
> the OS I'm still not sure why I don't see continual disk activity. Why
> would I see a minute or two of activity followed by a few minutes of no
> activity?
> Thanks
>

No disk activity during database restore

I am trying to restore a 200 GB database on to a newly formatted SAN
volume. The restore has been running for hours but there doesn't seem
to be a lot of disk activity on the disks I'm restoring to and I'm
wondering if that's anything to worry about.
Using perfmon I'm monitoring the % Disk time for the disk I'm restoring
to. It will show around 99% for a minute or so and then drop to zero
for 3-4 minutes. Is this normal? I know that since I'm restoring to a
new volume that SQL Server needs to zero out the 200 GB before it
starts the actual restore but I would expect to see 100% disk time
while it does this.
Any insight appreciated.
Thanks!Hi , Can you watch i/o in sysprocesses table for the SPID running the backup
task , this should be changing ...
Vishal
"pshroads@.gmail.com" wrote:

> I am trying to restore a 200 GB database on to a newly formatted SAN
> volume. The restore has been running for hours but there doesn't seem
> to be a lot of disk activity on the disks I'm restoring to and I'm
> wondering if that's anything to worry about.
> Using perfmon I'm monitoring the % Disk time for the disk I'm restoring
> to. It will show around 99% for a minute or so and then drop to zero
> for 3-4 minutes. Is this normal? I know that since I'm restoring to a
> new volume that SQL Server needs to zero out the 200 GB before it
> starts the actual restore but I would expect to see 100% disk time
> while it does this.
> Any insight appreciated.
> Thanks!
>|||Hi
I see this all the time. Even the IO stats and SPID stats show that nothing
is happening, but it is.
The issue is that before the actual restore can take place, the DB needs to
be created and initialized. This is an operation that SQL Server calls the
OS to do via an API. As the OS just indicates that the operation is still
running, the IO and SPID counter do not change.
The SAN has the instruction, and is doing the hard work. The OS and in turn
SQL Server are waiting for the SAN to finish.
If you were to have 200GB cache on the SAN, the SAN would return almost
immediately and then flush the pages to disk, but you probably don't have so
much, the disk IO is still happening at SAN level.
Deepening on the SAN configuration, 200GB could take anywhere from a few
seconds to a few hours.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vishal Gandhi" <VishalGandhi@.discussions.microsoft.com> wrote in message
news:007A4393-EE3D-4360-98B6-23A5C949FE17@.microsoft.com...[vbcol=seagreen]
> Hi , Can you watch i/o in sysprocesses table for the SPID running the
> backup
> task , this should be changing ...
> Vishal
>
> "pshroads@.gmail.com" wrote:
>|||Thanks for your reply. Even though SQL Server passes the task off to
the OS I'm still not sure why I don't see continual disk activity. Why
would I see a minute or two of activity followed by a few minutes of no
activity?
Thanks|||Hi
Have a look at the SAN management that your SAN vendor supplies. It will
give a you a good idea what is really happening in it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<pshroads@.gmail.com> wrote in message
news:1119646132.436946.57570@.g14g2000cwa.googlegroups.com...
> Thanks for your reply. Even though SQL Server passes the task off to
> the OS I'm still not sure why I don't see continual disk activity. Why
> would I see a minute or two of activity followed by a few minutes of no
> activity?
> Thanks
>

Newly created witness server only connects to one of the partners?

We've been running a mirrored database (using certificates since we don't have a domain) and it's all working well. Last week we decided to add a witness for automatic failovers, but for some reason I just can not get the witness to connect to the Partner2 server.

See screenshot here

Please help me troubleshoot this - I re-created the endpoints / users / certificates but it's still not working. Where can I get more information on what exactly the problem is? Can I test the endpoints somehow?

Hello. I started from scratch on a test environment and I get the exact same problem. On the mirror I get that Witness Connection status as Disconnected.

I don't use certificates and also no encrypton as in this tutorial: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

The direct result is that automatic failover does not work.

Some help will be great!

|||

I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

I get status disconnected and the automatic failover does not work.

|||

I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

I get status disconnected and the automatic failover does not work.

|||

I managed to make it work, but my solution is really convoluted and made me feel really uneasy:

- Setup a HOSTS file entry on Partner1 and Partner2 to point the witness IP address to the name WITNESS:

10.1.0.123 WITNESS

- Add the witness to the DB using the HOSTS entry (in TSQL)

alter database MyDatabase set witness = 'tcp://WITNESS:5022'

- Check mirroring monitor. Monitor confirms that the winess is only connected to Partner1

- Change the HOSTS entry on Partner1 to the wrong IP address for WITNESS:

0.0.0.0 WITNESS

- Restart the SQL Service on the Witness server. After the restart, mirroring monitor shows that the witness is now connected to Partner2, and not to Partner1

- Fix the HOSTS entry on Partner1 to the correct IP address again

- Wait 10 mins

- Check mirroring monitor - it now shows the witness connected to both servers! (if it's still not connected to Partner1, restart the witness again)

What? This makes no sense, but it worked for me.

|||

You know what. I followed your instructions step by step and indeed it works. But this is not a way I declare myself satisfied either...

It makes no sense... I wonder how anyone was able to make this work as it seems that this is a clear bug.

|||If someone wants the step by step to reproduce this I can tell it. It's a fairly basic process, following the instructions and still it doesn't work...|||

Anyway just to let you know I downloaded from Microsoft their SQL2005 in VHD (virtual hard drive) format and ran it under virtual server. And I tried step by step and it's giving the exact problem... there is something wrong there for sure.

No one willing to comment on this so far?

|||

I found that simply restarting SQL Server on the witness server solves this issue.

-dta

|||how exactlly do you do that? add entry to the hosts file? be exact please, i'm new and not very good with t-sql

Newly created witness server only connects to one of the partners?

We've been running a mirrored database (using certificates since we don't have a domain) and it's all working well. Last week we decided to add a witness for automatic failovers, but for some reason I just can not get the witness to connect to the Partner2 server.

See screenshot here

Please help me troubleshoot this - I re-created the endpoints / users / certificates but it's still not working. Where can I get more information on what exactly the problem is? Can I test the endpoints somehow?

Hello. I started from scratch on a test environment and I get the exact same problem. On the mirror I get that Witness Connection status as Disconnected.

I don't use certificates and also no encrypton as in this tutorial: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

The direct result is that automatic failover does not work.

Some help will be great!

|||

I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

I get status disconnected and the automatic failover does not work.

|||

I have the exact problem. I don't use certificates and encryption is off as suggested in this article: http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005

I get status disconnected and the automatic failover does not work.

|||

I managed to make it work, but my solution is really convoluted and made me feel really uneasy:

- Setup a HOSTS file entry on Partner1 and Partner2 to point the witness IP address to the name WITNESS:

10.1.0.123 WITNESS

- Add the witness to the DB using the HOSTS entry (in TSQL)

alter database MyDatabase set witness = 'tcp://WITNESS:5022'

- Check mirroring monitor. Monitor confirms that the winess is only connected to Partner1

- Change the HOSTS entry on Partner1 to the wrong IP address for WITNESS:

0.0.0.0 WITNESS

- Restart the SQL Service on the Witness server. After the restart, mirroring monitor shows that the witness is now connected to Partner2, and not to Partner1

- Fix the HOSTS entry on Partner1 to the correct IP address again

- Wait 10 mins

- Check mirroring monitor - it now shows the witness connected to both servers! (if it's still not connected to Partner1, restart the witness again)

What? This makes no sense, but it worked for me.

|||

You know what. I followed your instructions step by step and indeed it works. But this is not a way I declare myself satisfied either...

It makes no sense... I wonder how anyone was able to make this work as it seems that this is a clear bug.

|||If someone wants the step by step to reproduce this I can tell it. It's a fairly basic process, following the instructions and still it doesn't work...|||

Anyway just to let you know I downloaded from Microsoft their SQL2005 in VHD (virtual hard drive) format and ran it under virtual server. And I tried step by step and it's giving the exact problem... there is something wrong there for sure.

No one willing to comment on this so far?

|||

I found that simply restarting SQL Server on the witness server solves this issue.

-dta

|||how exactlly do you do that? add entry to the hosts file? be exact please, i'm new and not very good with t-sql

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

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

newid() question

when you use newid() is that a new uniqueidentifier for the database or for the table?

I have a changelog that captures newly created users first and gives them a newid, then i want to approve that change and copy the newid generated for that user into the user table. Will I run into duplicate id's that way?The NEWID() function creates aGUID. This number should be, statistically-speaking, unique.