detached database disappears?

Hi All:
Running SQL2005 sp1 on Windows Server 2003 R2.
I have Management Studio installed locally on the server. I connected to the
local instance, backed up a database with no problems, and then detached the
database, and now the physical files are gone? not in the "data" folder, not
anywhere...I still have the backup file, but I'm a little freaked out by
this.
Has anyone experience this? I'm logged in as the administrator so I don't
think the files are hidden from view (all the other databases are in the
data folder)
> I have Management Studio installed locally on the server. I connected to
> the local instance, backed up a database with no problems, and then
> detached the database, and now the physical files are gone? not in the
> "data" folder, not anywhere...I still have the backup file, but I'm a
> little freaked out by this.
> Has anyone experience this?
No, I've never seen this. Are you sure you searched all of the drives,
since often we don't place user databases under the program files structure,
but rather on a separate disk altogether.
|||>> I have Management Studio installed locally on the server. I connected to
> No, I've never seen this. Are you sure you searched all of the drives,
> since often we don't place user databases under the program files
> structure, but rather on a separate disk altogether.
Yeah, there's only one drive/partition on the system, and I've searched
everywhere...it's g-o-n-e.
I performed a full dB backup as well as TLog backup before detaching it. I'm
having trouble restoring from the backup now.
First, I tried to restore by typing in the name of the dB in the "to
database" and attaching the backup file. The backup shows the name of the dB
in the backup set, but when I select the "full database backup" and click
OK, I get a strange error:
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: File "EMS_Metro_Data" cannot be restored
over the existing "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\EMS_Metro_Data_new.MDF". Reissue the RESTORE
statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE
to identify an alternate location. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&LinkId=20476[/url]
BUT the database name referenced in the error is NOT the database I'm trying
to restore!
So, I then created a new database with the same name as the one I detached,
and selected "restore" and went through the same process, and I then get a
different error:
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The backup set holds a backup of a database other than the existing
'EMS_Ultimate' database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error:
3154)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476[/url]
EMS_Ultimate is the dB I'm trying to restore! And that's the name of the dB
in the backup file...now I'm really nervous!
|||Sounds to me like the MDF file is still in the Data folder.
And the error message references the name of the *file* no the name of the
database. They do not have to be the same.
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:ekhyVxeKHHA.320@.TK2MSFTNGP06.phx.gbl...
> Yeah, there's only one drive/partition on the system, and I've searched
> everywhere...it's g-o-n-e.
> I performed a full dB backup as well as TLog backup before detaching it.
> I'm having trouble restoring from the backup now.
> First, I tried to restore by typing in the name of the dB in the "to
> database" and attaching the backup file. The backup shows the name of the
> dB in the backup set, but when I select the "full database backup" and
> click OK, I get a strange error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: File "EMS_Metro_Data" cannot be restored
> over the existing "C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\EMS_Metro_Data_new.MDF". Reissue the RESTORE
> statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE
> to identify an alternate location. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&LinkId=20476[/url]
> --
> BUT the database name referenced in the error is NOT the database I'm
> trying to restore!
> So, I then created a new database with the same name as the one I
> detached, and selected "restore" and went through the same process, and I
> then get a different error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The backup set holds a backup of a database other than the existing
> 'EMS_Ultimate' database.
> RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error:
> 3154)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476[/url]
> --
> EMS_Ultimate is the dB I'm trying to restore! And that's the name of the
> dB in the backup file...now I'm really nervous!
>
|||Sounds to me like the MDF file is still in the Data folder.
And the error message references the name of the *file* no the name of the
database. They do not have to be the same.
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:ekhyVxeKHHA.320@.TK2MSFTNGP06.phx.gbl...
> Yeah, there's only one drive/partition on the system, and I've searched
> everywhere...it's g-o-n-e.
> I performed a full dB backup as well as TLog backup before detaching it.
> I'm having trouble restoring from the backup now.
> First, I tried to restore by typing in the name of the dB in the "to
> database" and attaching the backup file. The backup shows the name of the
> dB in the backup set, but when I select the "full database backup" and
> click OK, I get a strange error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: File "EMS_Metro_Data" cannot be restored
> over the existing "C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\EMS_Metro_Data_new.MDF". Reissue the RESTORE
> statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE
> to identify an alternate location. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&LinkId=20476[/url]
> --
> BUT the database name referenced in the error is NOT the database I'm
> trying to restore!
> So, I then created a new database with the same name as the one I
> detached, and selected "restore" and went through the same process, and I
> then get a different error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The backup set holds a backup of a database other than the existing
> 'EMS_Ultimate' database.
> RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error:
> 3154)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476[/url]
> --
> EMS_Ultimate is the dB I'm trying to restore! And that's the name of the
> dB in the backup file...now I'm really nervous!
>
|||
> Sounds to me like the MDF file is still in the Data folder.
> And the error message references the name of the *file* no the name of the
> database. They do not have to be the same.
>
OK, this is very bizarre...but when I clicked on the "options" in the
"restore database" window, all the settings seemed to be from a previous
restore I did months and months ago on a different database...I don't
understand why those settings would be preserved, when on the first panel
("general") I'm inputting completely different information and selecting a
completely different backup file, but I guess that's one of the mysteries of
the SQL2005.
The errors below were occuring because the MDF and LDF files existed for the
database specified in the options pane...not for the one I was trying to
restore. When I modified the settings in the "options" pane it worked
properly...oh well...at least I have the data back...I was worried the wrong
database was in the backup file.
Thanks for bearing with me!

>
> "geek-y-guy" <noone@.nowhere.org> wrote in message
> news:ekhyVxeKHHA.320@.TK2MSFTNGP06.phx.gbl...
>

Detach/Attach Via Management Studio

I really can't believe how difficult this is.
Open SQL Server Management Studio, browse to the databases, right-click the
one to detach, select Tasks>Detach... Check the Drop Connections checkbox,
click OK... Bang! Failed.
"The Database is not accessable..." pops up during the process. Click on the
OK.
"Cannot detach the database 'test' because it is currently in use.
(Microsoft SQL Server, Error: 3703)"
Hold on... Didn't I ask it to drop the connections? So why is it still in
use?
Look at the database, it's in Single User mode. OK. Let's get it back to
normal... Right-click, select properties... Bang! Error.
"Cannot show requested dialog."
"Database 'test' is already open and can only have one user at a time.
(Microsoft SQL Server, Error: 924)"
Who the heck has it open? Check the Activity Monitor:
A suspended delete command through the web application db user...
"(@.p2 int)BEGIN CONVERSATION TIMER ('37238b35-6439-db11-934c-00137260bfc2')
TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from
[SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]), TIMEOUT
@.p2;"
Unfortunately, I can't seem to kill the damn process. It just refuses to go
away. And the developers have no idea what it's for, so it must be some .NET
2.0 assembly thing.
Anyone know how to deal with this apart from shutting down the web
application server?Hi,
Open the query window and try this script...
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH Rollback Immediate
GO
SP_Detach_db <dbname>
Thanks
Hari
SQL Server MVP
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||The process is the server side of the Dependency client event that they use
to get a notification when something changes the data for a specified query.
It should be killable unless the client is restarting it. You may need to
stop the client app to get it to go away.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||There was a Service Broker for the client running on the database server
that wouldn't let the process finish. Rebooting the web application server
allowed me to detach/attach, but I was also able to do the same by removing
and adding the broker.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OL%23bT2XzGHA.1536@.TK2MSFTNGP02.phx.gbl...
> The process is the server side of the Dependency client event that they
> use to get a notification when something changes the data for a specified
> query. It should be killable unless the client is restarting it. You may
> need to stop the client app to get it to go away.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>>I really can't believe how difficult this is.
>> Open SQL Server Management Studio, browse to the databases, right-click
>> the one to detach, select Tasks>Detach... Check the Drop Connections
>> checkbox, click OK... Bang! Failed.
>> "The Database is not accessable..." pops up during the process. Click on
>> the OK.
>> "Cannot detach the database 'test' because it is currently in use.
>> (Microsoft SQL Server, Error: 3703)"
>> Hold on... Didn't I ask it to drop the connections? So why is it still in
>> use?
>> Look at the database, it's in Single User mode. OK. Let's get it back to
>> normal... Right-click, select properties... Bang! Error.
>> "Cannot show requested dialog."
>> "Database 'test' is already open and can only have one user at a time.
>> (Microsoft SQL Server, Error: 924)"
>> Who the heck has it open? Check the Activity Monitor:
>> A suspended delete command through the web application db user...
>> "(@.p2 int)BEGIN CONVERSATION TIMER
>> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
>> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
>> as message_body from
>> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
>> TIMEOUT @.p2;"
>> Unfortunately, I can't seem to kill the damn process. It just refuses to
>> go away. And the developers have no idea what it's for, so it must be
>> some .NET 2.0 assembly thing.
>> Anyone know how to deal with this apart from shutting down the web
>> application server?
>>
>

Detach/Attach Via Management Studio

I really can't believe how difficult this is.
Open SQL Server Management Studio, browse to the databases, right-click the
one to detach, select Tasks>Detach... Check the Drop Connections checkbox,
click OK... Bang! Failed.
"The Database is not accessable..." pops up during the process. Click on the
OK.
"Cannot detach the database 'test' because it is currently in use.
(Microsoft SQL Server, Error: 3703)"
Hold on... Didn't I ask it to drop the connections? So why is it still in
use?
Look at the database, it's in Single User mode. OK. Let's get it back to
normal... Right-click, select properties... Bang! Error.
"Cannot show requested dialog."
"Database 'test' is already open and can only have one user at a time.
(Microsoft SQL Server, Error: 924)"
Who the heck has it open? Check the Activity Monitor:
A suspended delete command through the web application db user...
"(@.p2 int)BEGIN CONVERSATION TIMER ('37238b35-6439-db11-934c-00137260bfc2')
TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from
[SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]), TIM
EOUT
@.p2;"
Unfortunately, I can't seem to kill the damn process. It just refuses to go
away. And the developers have no idea what it's for, so it must be some .NET
2.0 assembly thing.
Anyone know how to deal with this apart from shutting down the web
application server?Hi,
Open the query window and try this script...
ALTER DATABASE <DBNAME> SET SINGLE_USER WITH Rollback Immediate
GO
SP_Detach_db <dbname>
Thanks
Hari
SQL Server MVP
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||The process is the server side of the Dependency client event that they use
to get a notification when something changes the data for a specified query.
It should be killable unless the client is restarting it. You may need to
stop the client app to get it to go away.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I really can't believe how difficult this is.
> Open SQL Server Management Studio, browse to the databases, right-click
> the one to detach, select Tasks>Detach... Check the Drop Connections
> checkbox, click OK... Bang! Failed.
> "The Database is not accessable..." pops up during the process. Click on
> the OK.
> "Cannot detach the database 'test' because it is currently in use.
> (Microsoft SQL Server, Error: 3703)"
> Hold on... Didn't I ask it to drop the connections? So why is it still in
> use?
> Look at the database, it's in Single User mode. OK. Let's get it back to
> normal... Right-click, select properties... Bang! Error.
> "Cannot show requested dialog."
> "Database 'test' is already open and can only have one user at a time.
> (Microsoft SQL Server, Error: 924)"
> Who the heck has it open? Check the Activity Monitor:
> A suspended delete command through the web application db user...
> "(@.p2 int)BEGIN CONVERSATION TIMER
> ('37238b35-6439-db11-934c-00137260bfc2') TIMEOUT = 120; WAITFOR(RECEIVE
> TOP (1) message_type_name, conversation_handle, cast(message_body AS XML)
> as message_body from
> [SqlQueryNotificationService-5710e78f-2bab-4e58-8567-edb949981446]),
> TIMEOUT @.p2;"
> Unfortunately, I can't seem to kill the damn process. It just refuses to
> go away. And the developers have no idea what it's for, so it must be some
> .NET 2.0 assembly thing.
> Anyone know how to deal with this apart from shutting down the web
> application server?
>|||There was a Service Broker for the client running on the database server
that wouldn't let the process finish. Rebooting the web application server
allowed me to detach/attach, but I was also able to do the same by removing
and adding the broker.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:OL%23bT2XzGHA.1536@.TK2MSFTNGP02.phx.gbl...
> The process is the server side of the Dependency client event that they
> use to get a notification when something changes the data for a specified
> query. It should be killable unless the client is restarting it. You may
> need to stop the client app to get it to go away.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OGNd4KXzGHA.3464@.TK2MSFTNGP03.phx.gbl...
>

Designing category system

Hi,

I am trying to design tables for managing category system.

In my inventory management system, I have a receive form, where I have to enter a product from a subcategory of a category.

I can't figuire out how should i design my tables. Am I correct with these tables -

Products Table - ProdID, ProdName, CatID, SCatID
Category Table - CatID, CatName
SubCategory Table - SCatID, SCatName

I can't show only the subCategories associated with a Category. What will be the design?

Also in the Receive form, which one will be logical? Should I select a category first, and related subCategories appears, and then product list form that subcategory appear?

Or should I select the product first, then categories and subCategories should appear?

Thanx a lot in advance for your help.

Regards
Kapalic

It depends how your hierarchy is,
suppose if your hierarchy follows ,

Category
|- Sub-Category
|- Product

Then you can go for following design,

Category -> CatId, CatName, ParentCatId
Where
CatId Primary key
ParentCatId foreign Key of CatId

(if ParentCatid is null then Category otherwise Sub Category)

Product -> ProductId, CatId
Where
CatId is Foregitn key of Category(CatId)

Regarding selection,
Product -> Sub-Category is ONE ON ONE relation
Sub-Cateogry -> Category is ONE ON ONE Relation

But,
Category -> Sub-Category is ONE To MANY relation
Sub-Cateogry -> Product is ONE ON MANY relation

Again its depends how your business rules defines.

|||

Hi ManiD,

Thnx for your reply! I will use the table structure u provided. This is much more logical.

And regarding to choosing fields, i do not have any binding other than existance of category and subcategory id. I just want an expert openion on which approach will be better for the users and developers perspective.

Regards
Kapalic

Designing Aggregations

Does anyone know of a way to copy aggregations in the BI Studio?

I know you can copy aggregations in management studio after the cube is deployed...

Trying to do it at design time.

Thanks

Mark

You can switch to the "Code View" in BI dev studio and copy following property

<AggregationDesignID>YourAggregationDesignHere</AggregationDesignID>

From one partition to another.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Design Question

Hi all,

I am currently building a project management application.
The users wants to save existing projects as templates.

My question is, what would be the better solution.
Create seperate tables to hold template information.
Or use the same table as the existing project but, flagged as Templates?My feeling is that having one table would be the better solution. Having 2 separate tables with the same file structure is asking for trouble maintenance-wise. Any schema changes would have to be done twice.

Terri|||Yes thats what the IT Manager said and I agree.
But the template data is only varchar name and description type stuff.
Where as the other table has datetime, bit, int etc type fields.
So if I combine the two table I will have to allow for null values.

Is this a problem?|||Well, now it is sounding like the template schema and the project schema are not the same. If you post the schema perhaps better advice can be offered.

And I don't see a problem with allowing for NULL values, as long as you account for them in your coding.

Terri|||Use the same table as your manager said. Fewer tables = simpler design = better program. Also you can easily change templates into project or vice versa if you ever see the need to. (Think easy template creation from existing campaigns?)|||Thanks guys|||Or you can normalize your project table into ProjectHeader and ProjectDetails. Include in the ProjectHeader those fields which are common to templates and projects, and add a ProjectType field to identify it as a project or template. The additional fields that only apply to projects can then be placed in your new ProjectDetails table, along with the foreign key reference to the ProjectHeader table.

This is generally how you would handle object generalizations (or inheritance, whatever terminology you want to use) like this. Shoving it all into one single table can cause problems later - what if you decide you now have more "special cases" of project records?

DESIGN FLAWS IN MANAGEMENT STUDIO ??

Greetings,
Please tell me we don't have to live with this annoying thing where if
you try to open multiple .sql files it opens a new instance of
Management Studio and prompts you to connect to a server'
I JUST want to modify some sql scripts dangitt.
If so, MS please get on the ball to get this corrected. I am switching
my .sql files back to opening in Visual Studio and refuse to use this
tool until corrected. In addition it seems very bloated and load times
for the application are annoying. Am I the only one seeing this?
Thanks,
MikeI agree Mike. I like the way that Enterprise Manager and Query Analyzer are
integrated into one tool, but Management Studio is clunky and much slower
than the old tools. It drives me nuts when I open a SQL file, and MS creates
a new tabbed window and connection instead of opening the file in my existing
window.
Also, when generating the script for a table, you no longer gives you the
ability to leave out the junk you don't want, like collation, unless you go
through the script wizard, which is slow. I just want to show the script for
a single table for crying out loud.
I just installed SP1 hoping that they changed some of these things, but it
looks like Microsoft ignored Management Studio.
Tom
"mike.mcneer@.gmail.com" wrote:
> Greetings,
> Please tell me we don't have to live with this annoying thing where if
> you try to open multiple .sql files it opens a new instance of
> Management Studio and prompts you to connect to a server'
> I JUST want to modify some sql scripts dangitt.
> If so, MS please get on the ball to get this corrected. I am switching
> my .sql files back to opening in Visual Studio and refuse to use this
> tool until corrected. In addition it seems very bloated and load times
> for the application are annoying. Am I the only one seeing this?
> Thanks,
> Mike
>|||http://lab.msdn.microsoft.com/productfeedback/Default.aspx
You can file bugs as well as suggestions out here. Filing a suggestion that
basically says "it sucks, fix it" is a quick way to be completely ignored.
Detail exactly what you are seeing, what you expect it to do, and what it
actually does.
Then the nice thing about this site? The entire world has the ability to
vote on suggestions as well as post comments also. If enough people agree
that something should be done, it will get done. Having messages scattered
across a newsgroup related to product improvements isn't going to help
determine what is important to all of us and what isn't.
I personally don't find an issue with the performance of Management Studio.
BUT, I use Query Analyzer for my day to day SQL Server 2005 tasks. It's
like any managed code application I've ever come across, it is VERY slow the
first time it is launched, because all kinds of assemblies and other stuff
have to be loaded. Everytime I start it after that, it takes very little
time to launch. So, I launch SSMS when I start my machine up and since I
don't reboot it unless something forces me to, it get decent performance out
of SSMS for weeks at a time.
As for the connection dialog, well, it didn't work that way initially. Back
in the early betas, SSMS would launch without ever requiring a connection
and scripts would also open up without requiring a connection. However, a
bunch of people screamed about it not being connected and those voices were
a lot louder than the ones who didn't want to always connect to an instance,
so SSMS now pops up a dialog to connect to an instance when you launch it.
If enough people want it the other way, it isn't that hard to make the
change.
Also, you are taking about functionality changes. There isn't an actual bug
in anything that you pointed out. So, don't expect anything to be done
about it until the next version. They are very careful to not introduce new
functionality, which is what you are asking for, in a service pack.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:E68D3248-4B78-4BC7-9B40-319A660C29C2@.microsoft.com...
>I agree Mike. I like the way that Enterprise Manager and Query Analyzer
>are
> integrated into one tool, but Management Studio is clunky and much slower
> than the old tools. It drives me nuts when I open a SQL file, and MS
> creates
> a new tabbed window and connection instead of opening the file in my
> existing
> window.
> Also, when generating the script for a table, you no longer gives you the
> ability to leave out the junk you don't want, like collation, unless you
> go
> through the script wizard, which is slow. I just want to show the script
> for
> a single table for crying out loud.
> I just installed SP1 hoping that they changed some of these things, but it
> looks like Microsoft ignored Management Studio.
> Tom
> "mike.mcneer@.gmail.com" wrote:
>> Greetings,
>> Please tell me we don't have to live with this annoying thing where if
>> you try to open multiple .sql files it opens a new instance of
>> Management Studio and prompts you to connect to a server'
>> I JUST want to modify some sql scripts dangitt.
>> If so, MS please get on the ball to get this corrected. I am switching
>> my .sql files back to opening in Visual Studio and refuse to use this
>> tool until corrected. In addition it seems very bloated and load times
>> for the application are annoying. Am I the only one seeing this?
>> Thanks,
>> Mike
>>|||On Fri, 28 Apr 2006 09:07:02 -0700, Tom wrote:
> It drives me nuts when I open a SQL file, and MS creates
>a new tabbed window and connection instead of opening the file in my existing
>window.
Hi Tom,
What would yu prefer, then? Would you want MS to discard whatever is
currently in the active query window and replace it with the contents of
the file you double-clicked? What would your reaction be if you lost two
hours worth of query-writing because you accidentally doouble-clicked a
SQL file?
BTW, I have set notepad to be the default application for the .sql
suffix. Maybe an idea for you too?
>Also, when generating the script for a table, you no longer gives you the
>ability to leave out the junk you don't want, like collation, unless you go
>through the script wizard, which is slow. I just want to show the script for
>a single table for crying out loud.
Agreed - this is a major pain.
--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:60u4521197dgmlfjkvhn0uo88h7tdhsm5c@.4ax.com...
> On Fri, 28 Apr 2006 09:07:02 -0700, Tom wrote:
> > It drives me nuts when I open a SQL file, and MS creates
> >a new tabbed window and connection instead of opening the file in my
existing
> >window.
> Hi Tom,
> What would yu prefer, then? Would you want MS to discard whatever is
> currently in the active query window and replace it with the contents of
> the file you double-clicked? What would your reaction be if you lost two
> hours worth of query-writing because you accidentally doouble-clicked a
> SQL file?
>
Not sure what exactly he's referring to, but I know what annoys me.
With QA with SQL 2000, if I had say 5 .SQL files in a directory and opened
each one (double clicking) it would open a new window in the existing copy
of QA (I believe using the current connection info).
Now, with SMS, it opens a new COPY of SMS each time. Which is much slower
and a royal pain.
I've also noticed that for some strange reason I seem to "mistype" the
password in SMS when I try to connect a LOT more than in QA. It's very
weird, it's almost like it doesn't see me type a character, or is missing it
when I hit the shift key. I know at least 1-2 others that have said they
have a similar experience.
> BTW, I have set notepad to be the default application for the .sql
> suffix. Maybe an idea for you too?
That's probably what I'm going to do.
> >Also, when generating the script for a table, you no longer gives you the
> >ability to leave out the junk you don't want, like collation, unless you
go
> >through the script wizard, which is slow. I just want to show the script
for
> >a single table for crying out loud.
> Agreed - this is a major pain.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 28 Apr 2006 21:57:42 -0400, Greg D. Moore (Strider) wrote:
(snip)
>With QA with SQL 2000, if I had say 5 .SQL files in a directory and opened
>each one (double clicking) it would open a new window in the existing copy
>of QA (I believe using the current connection info).
>Now, with SMS, it opens a new COPY of SMS each time. Which is much slower
>and a royal pain.
Hi Greg,
Strange. If I restore the original default action for .SQL files and
doubleclick one, it loads in a new tab in my open SSMS (though it does
bug me for connection details).
If I drag across a bunch of .SQL files to select them alll and hit
enter, than I do get several instances of SSMS. One of the query loads
in the existing SSMS, the others all load a new instance. Not good.
>I've also noticed that for some strange reason I seem to "mistype" the
>password in SMS when I try to connect a LOT more than in QA.
I use Windows Authentication, so that's not a problem for me. :-)
--
Hugo Kornelis, SQL Server MVP|||I agree totally :-( Something went terribly wrong in the year 2005. Not only
this, but also Visausl Studio 2005 has serious problems :-(
PS: I still use Query analyzer: much much faster :-)|||thanks guys, I agree I shouldn't have probably griped too much but, I
just wanted to make sure I wasn't the only one dealing with this and
maybe there was a way around other than default my sql files back to
something else. When you have 40 Storedp rocedures, 10 triggers and
table change scripts for a build every other week you need something
speedy and quick to get those opened and compiled.

DESIGN FLAWS IN MANAGEMENT STUDIO ??

Greetings,
Please tell me we don't have to live with this annoying thing where if
you try to open multiple .sql files it opens a new instance of
Management Studio and prompts you to connect to a server'
I JUST want to modify some sql scripts dangitt.
If so, MS please get on the ball to get this corrected. I am switching
my .sql files back to opening in Visual Studio and refuse to use this
tool until corrected. In addition it seems very bloated and load times
for the application are annoying. Am I the only one seeing this?
Thanks,
MikeI agree Mike. I like the way that Enterprise Manager and Query Analyzer are
integrated into one tool, but Management Studio is clunky and much slower
than the old tools. It drives me nuts when I open a SQL file, and MS create
s
a new tabbed window and connection instead of opening the file in my existin
g
window.
Also, when generating the script for a table, you no longer gives you the
ability to leave out the junk you don't want, like collation, unless you go
through the script wizard, which is slow. I just want to show the script fo
r
a single table for crying out loud.
I just installed SP1 hoping that they changed some of these things, but it
looks like Microsoft ignored Management Studio.
Tom
"mike.mcneer@.gmail.com" wrote:

> Greetings,
> Please tell me we don't have to live with this annoying thing where if
> you try to open multiple .sql files it opens a new instance of
> Management Studio and prompts you to connect to a server'
> I JUST want to modify some sql scripts dangitt.
> If so, MS please get on the ball to get this corrected. I am switching
> my .sql files back to opening in Visual Studio and refuse to use this
> tool until corrected. In addition it seems very bloated and load times
> for the application are annoying. Am I the only one seeing this?
> Thanks,
> Mike
>|||http://lab.msdn.microsoft.com/produ...ck/Default.aspx
You can file bugs as well as suggestions out here. Filing a suggestion that
basically says "it sucks, fix it" is a quick way to be completely ignored.
Detail exactly what you are seeing, what you expect it to do, and what it
actually does.
Then the nice thing about this site? The entire world has the ability to
vote on suggestions as well as post comments also. If enough people agree
that something should be done, it will get done. Having messages scattered
across a newsgroup related to product improvements isn't going to help
determine what is important to all of us and what isn't.
I personally don't find an issue with the performance of Management Studio.
BUT, I use Query Analyzer for my day to day SQL Server 2005 tasks. It's
like any managed code application I've ever come across, it is VERY slow the
first time it is launched, because all kinds of assemblies and other stuff
have to be loaded. Everytime I start it after that, it takes very little
time to launch. So, I launch SSMS when I start my machine up and since I
don't reboot it unless something forces me to, it get decent performance out
of SSMS for weeks at a time.
As for the connection dialog, well, it didn't work that way initially. Back
in the early betas, SSMS would launch without ever requiring a connection
and scripts would also open up without requiring a connection. However, a
bunch of people screamed about it not being connected and those voices were
a lot louder than the ones who didn't want to always connect to an instance,
so SSMS now pops up a dialog to connect to an instance when you launch it.
If enough people want it the other way, it isn't that hard to make the
change.
Also, you are taking about functionality changes. There isn't an actual bug
in anything that you pointed out. So, don't expect anything to be done
about it until the next version. They are very careful to not introduce new
functionality, which is what you are asking for, in a service pack.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:E68D3248-4B78-4BC7-9B40-319A660C29C2@.microsoft.com...[vbcol=seagreen]
>I agree Mike. I like the way that Enterprise Manager and Query Analyzer
>are
> integrated into one tool, but Management Studio is clunky and much slower
> than the old tools. It drives me nuts when I open a SQL file, and MS
> creates
> a new tabbed window and connection instead of opening the file in my
> existing
> window.
> Also, when generating the script for a table, you no longer gives you the
> ability to leave out the junk you don't want, like collation, unless you
> go
> through the script wizard, which is slow. I just want to show the script
> for
> a single table for crying out loud.
> I just installed SP1 hoping that they changed some of these things, but it
> looks like Microsoft ignored Management Studio.
> Tom
> "mike.mcneer@.gmail.com" wrote:
>|||On Fri, 28 Apr 2006 09:07:02 -0700, Tom wrote:

> It drives me nuts when I open a SQL file, and MS creates
>a new tabbed window and connection instead of opening the file in my existi
ng
>window.
Hi Tom,
What would yu prefer, then? Would you want MS to discard whatever is
currently in the active query window and replace it with the contents of
the file you double-clicked? What would your reaction be if you lost two
hours worth of query-writing because you accidentally doouble-clicked a
SQL file?
BTW, I have set notepad to be the default application for the .sql
suffix. Maybe an idea for you too?

>Also, when generating the script for a table, you no longer gives you the
>ability to leave out the junk you don't want, like collation, unless you go
>through the script wizard, which is slow. I just want to show the script f
or
>a single table for crying out loud.
Agreed - this is a major pain.
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:60u4521197dgmlfjkvhn0uo88h7tdhsm5c@.
4ax.com...
> On Fri, 28 Apr 2006 09:07:02 -0700, Tom wrote:
>
existing[vbcol=seagreen]
> Hi Tom,
> What would yu prefer, then? Would you want MS to discard whatever is
> currently in the active query window and replace it with the contents of
> the file you double-clicked? What would your reaction be if you lost two
> hours worth of query-writing because you accidentally doouble-clicked a
> SQL file?
>
Not sure what exactly he's referring to, but I know what annoys me.
With QA with SQL 2000, if I had say 5 .SQL files in a directory and opened
each one (double clicking) it would open a new window in the existing copy
of QA (I believe using the current connection info).
Now, with SMS, it opens a new COPY of SMS each time. Which is much slower
and a royal pain.
I've also noticed that for some strange reason I seem to "mistype" the
password in SMS when I try to connect a LOT more than in QA. It's very
weird, it's almost like it doesn't see me type a character, or is missing it
when I hit the shift key. I know at least 1-2 others that have said they
have a similar experience.

> BTW, I have set notepad to be the default application for the .sql
> suffix. Maybe an idea for you too?
That's probably what I'm going to do.

>
go[vbcol=seagreen]
for[vbcol=seagreen]
> Agreed - this is a major pain.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 28 Apr 2006 21:57:42 -0400, Greg D. Moore (Strider) wrote:
(snip)
>With QA with SQL 2000, if I had say 5 .SQL files in a directory and opened
>each one (double clicking) it would open a new window in the existing copy
>of QA (I believe using the current connection info).
>Now, with SMS, it opens a new COPY of SMS each time. Which is much slower
>and a royal pain.
Hi Greg,
Strange. If I restore the original default action for .SQL files and
doubleclick one, it loads in a new tab in my open SSMS (though it does
bug me for connection details).
If I drag across a bunch of .SQL files to select them alll and hit
enter, than I do get several instances of SSMS. One of the query loads
in the existing SSMS, the others all load a new instance. Not good.

>I've also noticed that for some strange reason I seem to "mistype" the
>password in SMS when I try to connect a LOT more than in QA.
I use Windows Authentication, so that's not a problem for me. :-)
Hugo Kornelis, SQL Server MVP|||I agree totally :-( Something went terribly wrong in the year 2005. Not only
this, but also Visausl Studio 2005 has serious problems :-(
PS: I still use Query analyzer: much much faster :-)|||thanks guys, I agree I shouldn't have probably griped too much but, I
just wanted to make sure I wasn't the only one dealing with this and
maybe there was a way around other than default my sql files back to
something else. When you have 40 Storedp rocedures, 10 triggers and
table change scripts for a build every other week you need something
speedy and quick to get those opened and compiled.

Design advice?

I have an construction estimation system, and I want to develop a project management system. I will be using the same database because there are shared tables. My question is this, critical data tables are considered tables with dollar values and these tables should not be shared across the whole company. I do however need information from these tables, such as product and quantity of the product for a given project. When an estimate becomes a project it is assigned a project number. At this point I thought of Copying the required data from the estimate side to the project side. This would result in duplicate data in a sence but the tables will be referenced from two standalone front end applications. Should I copy the data from one table to another, or create new "views" to the estimate tables for the project management portion.

What would be the best solution to this problem? I find in some circumstances, a new table is required because additional data will be saved on the "Project Management" side, but not in all cases.

Mike Bi'd make a new table and transfer contents of estimates table into it once the estimate matures into a project. at least one reason behind it: you can have multiple estimates and all but the final one will stay estimates. i'd also designate a grouping field to identify what estimates may relate to what project (i am talking about estimates that never became a project, but are related to the one that did)|||i'd also designate a grouping field to identify what estimates may relate to what project (i am talking about estimates that never became a project, but are related to the one that did)

I have already done that, it is a "Project Reference ID". Thanks for the input. It is pretty much already what I was thinking, but.....I am very under confident in my decisions (inexperience I guess).

Mike B