Hi,
There was the most awful case. During a update of a DB there was a
switching-off of power supplies. After switch on of a server the base has
passed in mode 'Suspect'. To restore base by standard way it was not
possible. Substitution of files and had turned out to open changes of the
status of base data.
Has executed substitution of files of a database. Has successfully opened a
database, structure - all is accessible.
Check of a file of a database on integrity is executed successfully, errors
are not present (DBCC CHECKDB).
I can not create backup, gives out the message that are opened deferred
transactions.
Also, at change of some objects there is a lag of a server that leads to
change of the status of database 'In Recovery'. In 2 and more hours the
status has not changed.
Whether it is necessary to wait for restoration? How to commit deferred tran?
Thank you for any help.
p.s. Backups very old :(Not sure if this is going to work ... a little bit risky but worth a try (I
guess)...
It seemed that some transaction are not clearing so try to rollback the
transaction and then try to backup the database and then try to turn the
database back online, so the command would look like:
USE master
GO
-- First set it to read only and rollback any active transaction
ALTER DATABASE [DatabaseName]
SET READ_ONLY WITH ROLLBACK IMMEDIATE
GO
-- Backup the database, hopefully it should work since there should not be
any active transaction
BACKUP DATABASE [DatabaseName]
TO DISK = '[FilePathAndName]'
WITH INIT
GO
-- Turn the database back online
ALTER DATABASE [DatabaseName]
SET ONLINE WITH ROLLBACK IMMEDIATE
GO
Hopefully that would work (but no guarantee...)
Lucas
"int64" wrote:
> Hi,
> There was the most awful case. During a update of a DB there was a
> switching-off of power supplies. After switch on of a server the base has
> passed in mode 'Suspect'. To restore base by standard way it was not
> possible. Substitution of files and had turned out to open changes of the
> status of base data.
> Has executed substitution of files of a database. Has successfully opened a
> database, structure - all is accessible.
> Check of a file of a database on integrity is executed successfully, errors
> are not present (DBCC CHECKDB).
> I can not create backup, gives out the message that are opened deferred
> transactions.
> Also, at change of some objects there is a lag of a server that leads to
> change of the status of database 'In Recovery'. In 2 and more hours the
> status has not changed.
> Whether it is necessary to wait for restoration? How to commit deferred tran?
> Thank you for any help.
> p.s. Backups very old :(
>
Showing posts with label case. Show all posts
Showing posts with label case. Show all posts
Thursday, March 29, 2012
Monday, March 19, 2012
database encryption
Hi,
i am trying to encrypt my database using dbEncrypt software.The encryption provides role based access ot the database.In that case developer can not call any stored procedure of that database. These two are conflicting things. Can anyone suggest me a way so that i can access the stored procedure through my code by checking the roles from the front end. is it possible??
Please reply soon.
Application Security, INC. The manufacturer of that product, will most likely have specific methods and methodologies for connecting and querying a DB that has been encrypted using their software. I would recomend you ask them how to accomplish this.
http://www.appsecinc.com/support/index.shtml
Sunday, March 11, 2012
Database Diagrams
Do you need to be a member of the dbo role or an sa on a particular sql server to create a database diagram? I just want to confirm this is the case.
Any assistance will be helpful.
MariaYou have to logged as dbo or system administrator (sa or member of server group system administrators).|||Great, that's what I thought.
Thanks,
Maria|||Even if you are not a dbo or sysadmin, you can still create the diagrams and save them. But you will not be able to make changes to the tables as you don't have DML permissions.|||Okay, that is the problem. My developers can't seem to create a database diagram because the message is that they don't have sufficient permissions. Is there a particular role that they should be assigned to in order to do this? Or a level of permissions that is associated?
Maria|||Make sure the following stored procedures exist in the database:
dbo.dt_getobjwithprop_u
dbo.dt_adduserobject
dbo.dt_setpropertybyid_u
dbo.dt_getpropertiesbyid_u|||Great, I'll try that tomorrow and let you know.
Maria|||Hi,
The procs exist in the database. The problem is that my users are still unable to create the diagrams on the db. It claims they don't have enough permissions.
That's where the initial inquiry came into play. If this is wrong and they should be able to create a diagram regardless of the role they are grouped into on the db, then does anyone know what the problem is?
Maria
Any assistance will be helpful.
MariaYou have to logged as dbo or system administrator (sa or member of server group system administrators).|||Great, that's what I thought.
Thanks,
Maria|||Even if you are not a dbo or sysadmin, you can still create the diagrams and save them. But you will not be able to make changes to the tables as you don't have DML permissions.|||Okay, that is the problem. My developers can't seem to create a database diagram because the message is that they don't have sufficient permissions. Is there a particular role that they should be assigned to in order to do this? Or a level of permissions that is associated?
Maria|||Make sure the following stored procedures exist in the database:
dbo.dt_getobjwithprop_u
dbo.dt_adduserobject
dbo.dt_setpropertybyid_u
dbo.dt_getpropertiesbyid_u|||Great, I'll try that tomorrow and let you know.
Maria|||Hi,
The procs exist in the database. The problem is that my users are still unable to create the diagrams on the db. It claims they don't have enough permissions.
That's where the initial inquiry came into play. If this is wrong and they should be able to create a diagram regardless of the role they are grouped into on the db, then does anyone know what the problem is?
Maria
Wednesday, March 7, 2012
Database Design- Referencing multiple database
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.why multiple databases? It makes it harder to enforce referential integrity and it can cause other headaches. i just recently ran into an issue where i swapped out a database instead of doinng a restore and the application broke because database objects that reference another database actually store the database id instead of the name. it was easy to fix but I still had an hour of down time. thank god it was not a production server.|||Hi All,
Hi.
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server.
Really...
Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
How do i design my database and sql queries?
In a word: "poorly".
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Well, that plan certainly is......"creative"...
Any thoughts on above approach?? or any better/standard way for such scenarios ?
I'd say, practically anything else would be better.
Thanks in Advance. Your inputs will be of great help.
No problem.|||Ahh, the much-vaunted (but almost certainly mythical and definitely undocumented) 'unreplication' functionality of SQL Server! ;)
On a more serious note, if you really want to proceed down this route you will need to set up Linked Servers and enable the MSDTC service.
I would implore you to think again.
Lempster|||That's certainly a novel approach - doomed but novel. Out of curiosity what advantages were you expecting from spreading your system across different servers? and what was your database(s) supposed to hold?|||How versatile
but, um, how much experience do you have?
Oh, I get it, you're in upper manglement|||Ouch Brett...that'll likely leave a scar. ;)
I assumed from the description that between the lines there is a plain motivation. Seems to me that the distant (aka remote) databases are already in existance, and the O.P. has been given a task to pull some data in from other servers, and followed the logical path to the current planned implementation!
Sheesh. How silly is it to have databases spread all 'round when you can just copy them all to one place and just keep them in synch. ?|||Hi All,
I am designing database
I didn't read that all
I designing a housebuilding business
I plan to have all of my nails located in DC, all of my wood in Oregon, all the fixtures in NY, and all of my roofing supplies in N'orleans (There all over the place and free)|||sometimes i go to other sql boards after following a link and i am always surprised at the difference in the content from this place.|||how so?
too short|||i think this board has more of a tendency to slam the OP.|||Nothing like the real world, eh|||No soup for you, poster!|||There are scenarios where distributed databases are a good idea - but they don't come up very often (at least I've never met one). Must admit it would be quite interesting to work on one though. I think it's important to hear the reasoning behind his system and find out why he thinks it's so versatile.|||I agree there, Mike. Sometimes you need to know the rationale behind the current design before dashing said design upon the jagged and bloody rocks of despair. ;)
If nothing else, it also allows one to more pointedly help (and/or taunt) the original poster's assumptions and reasoning.
As I am sure you are aware, sometimes stuff just comes across initially so far off the wall it's hard to resist a good-natured jab in the ribs and perhaps a well-intentioned kick in the groin.|||Hi all,
Thanks for all kinds of replies.
Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department. Application which we will be designing depends on these data. We cannot certainly have all required data in one single database.
Option of creating creating identical tables and syncing regularly is as i feel the better than writing down the distributed queries.
Any thought on it ??
Thanks,|||Hi all,
Thanks for all kinds of replies.
Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department. Application which we will be designing depends on these data. We cannot certainly have all required data in one single database.
Option of creating creating identical tables and syncing regularly is as i feel the better than writing down the distributed queries.
Any thought on it ??
Thanks,|||What's wrong with just downloading the data from the other data sources via feeds - bcp each file into a transfer table - check the data - put the changed data into your database. How many feeds could you expect this way, what size (roughly) for each feed?
Your method may save you having to write any feeds but the people writing the app are going to hate you for the rest of their lives :)
Mike|||Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department.This sounds like a small data warehousing initiative to me. Perhaps that is the approach you should be taking.|||We do stuff like this on a daily basis. Consolidating data from different departments on a single machine for historical and modeling purposes. I use a combination of distributed queries and web services that allow us to yank data in flat file format from some machines, and yank data directly from others (via linked servers).
Seems like downloading and synching databases is alot more work than you need to be doing to do what (i understand to be...) you need to do. Data feeds and distributed queries are much better with respect to basic database design conventions, and synching databases just causes a spontaneous sphincter constriction whenever I hear it.
Duplication of data = bad. Direct/indirect (and on-demand) access to remote data = good.
Sounds like you've already decided though, so g'luck to you :)|||Well the things getting more complex. We will be having four external database to depends on for our application.
Reason of staying away from distributed query was that our application requires this data (external database table serves as master table for our application) in the transactions. And of course in reports as well.
I liked the suggestion of getting feed from external database into temporary staging area and then updating only updating the changesets in our tables.|||Originally posted by versatile_me
I liked the suggestion of getting feed from external database into temporary staging area and then updating only updating the changesets in our tables.I'd like to claim first dibs on the concept but I suspect it's been around for 40 years.
I know it's fun comming up with new systems but after you spend 6 months sweating blood trying to get it to work there are only a few possible outcomes :
it fails and you have to call in a professional who is then faced with working with a flawed design or loosing the client by throwing everything away and starting afresh.
it works, but only just, and after 3 months limping along the system gets abandonded.
it works and is a fantastic success - I'm afraid this is the least likely outcome :)
If the application is at all important to your company then it may well be worth getting an experienced profesional in to do the work and you provide the business requirements. This would be a good way to learn about database/system design and also might ensure any good ideas you may have will appear in the finished product.
Mike|||We do stuff like this on a daily basis. Consolidating data from different departments on a single machine for historical and modeling purposes. I use a combination of distributed queries and web services that allow us to yank data in flat file format from some machines, and yank data directly from others (via linked servers).
Seems like downloading and synching databases is alot more work than you need to be doing to do what (i understand to be...) you need to do. Data feeds and distributed queries are much better with respect to basic database design conventions, and synching databases just causes a spontaneous sphincter constriction whenever I hear it.
Duplication of data = bad. Direct/indirect (and on-demand) access to remote data = good.
Sounds like you've already decided though, so g'luck to you :)
...
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
I'm struggling to see much difference between TallCowboy's "daily consolidation" and the OP's idea of equiv. Master tables that are regularly refreshed.
Although duplicate data = bad, isn't it common practice to have read-only replicated tables on a different server for reporting (or other read-only) purposes? Obviously; the data is as old as the last replication so the designer has to consider the ramification of slightly old data, but this localizes remote-connection / design breakages into the DTS / Copy programs, not the report.
Not so much "versitile" as "stable". (at least - until the source system changes and you have to fix the DTS - or retest it each and every time it changes ... so I'm not saying it's a great idea, just maybe unavoidable).|||I'm struggling to see much difference between TallCowboy's "daily consolidation" and the OP's idea of equiv. Master tables that are regularly refreshed.The difference is that in my system the data is not imported on a daily basis "as-is", but rather the existing tables are updated with NEW data that we did not previously have. However, I see your point in that the difference may be perception (and/or semantics) and my understanding of what the O.P. is contemplating as an implementation.
In my world, the data we import (via web service or via remote access) is not a "synching" of databases. It's the importation of data from external providers, and placed into our own "consolidated" and normalized database tables (and used for our own devious purposes completely unrelated to the original source(s) of the data). At least in my eyes, a completely different process than importing actual tables from a remote database and keeping them in synch.
Although duplicate data = bad, isn't it common practice to have read-only replicated tables on a different server for reporting (or other read-only) purposes? Obviously; the data is as old as the last replication so the designer has to consider the ramification of slightly old data, but this localizes remote-connection / design breakages into the DTS / Copy programs, not the report. I can't speak for this one, because we don't do it. We either provide access to our data via controlled (read-only) linked servers and associated userid's, or via a web service that is a front-end to our database.
We absolutely don't allow uncontrolled access to our tables, even remote access via linked servers/userid's is typically controlled with a view. If there is time and/or the longevity of the projects dictates, we write a web service wrapper to the external world and make them keep their nasty little grubbies off our databases.
We expect (and generally require) external/remote databases to have (or support) the same indirect access. We don't want our applications failing because they changed a table on the external system.
Not so much "versatile" as "stable". (at least - until the source system changes and you have to fix the DTS - or retest it each and every time it changes ... so I'm not saying it's a great idea, just maybe unavoidable).ed-zachary :)|||i think this board has more of a tendency to slam the OP.
Really?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67782&whichpage=8|||ROTFLMAO ... like Mastercard ... that was priceless :D
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.why multiple databases? It makes it harder to enforce referential integrity and it can cause other headaches. i just recently ran into an issue where i swapped out a database instead of doinng a restore and the application broke because database objects that reference another database actually store the database id instead of the name. it was easy to fix but I still had an hour of down time. thank god it was not a production server.|||Hi All,
Hi.
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server.
Really...
Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
How do i design my database and sql queries?
In a word: "poorly".
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Well, that plan certainly is......"creative"...
Any thoughts on above approach?? or any better/standard way for such scenarios ?
I'd say, practically anything else would be better.
Thanks in Advance. Your inputs will be of great help.
No problem.|||Ahh, the much-vaunted (but almost certainly mythical and definitely undocumented) 'unreplication' functionality of SQL Server! ;)
On a more serious note, if you really want to proceed down this route you will need to set up Linked Servers and enable the MSDTC service.
I would implore you to think again.
Lempster|||That's certainly a novel approach - doomed but novel. Out of curiosity what advantages were you expecting from spreading your system across different servers? and what was your database(s) supposed to hold?|||How versatile
but, um, how much experience do you have?
Oh, I get it, you're in upper manglement|||Ouch Brett...that'll likely leave a scar. ;)
I assumed from the description that between the lines there is a plain motivation. Seems to me that the distant (aka remote) databases are already in existance, and the O.P. has been given a task to pull some data in from other servers, and followed the logical path to the current planned implementation!
Sheesh. How silly is it to have databases spread all 'round when you can just copy them all to one place and just keep them in synch. ?|||Hi All,
I am designing database
I didn't read that all
I designing a housebuilding business
I plan to have all of my nails located in DC, all of my wood in Oregon, all the fixtures in NY, and all of my roofing supplies in N'orleans (There all over the place and free)|||sometimes i go to other sql boards after following a link and i am always surprised at the difference in the content from this place.|||how so?
too short|||i think this board has more of a tendency to slam the OP.|||Nothing like the real world, eh|||No soup for you, poster!|||There are scenarios where distributed databases are a good idea - but they don't come up very often (at least I've never met one). Must admit it would be quite interesting to work on one though. I think it's important to hear the reasoning behind his system and find out why he thinks it's so versatile.|||I agree there, Mike. Sometimes you need to know the rationale behind the current design before dashing said design upon the jagged and bloody rocks of despair. ;)
If nothing else, it also allows one to more pointedly help (and/or taunt) the original poster's assumptions and reasoning.
As I am sure you are aware, sometimes stuff just comes across initially so far off the wall it's hard to resist a good-natured jab in the ribs and perhaps a well-intentioned kick in the groin.|||Hi all,
Thanks for all kinds of replies.
Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department. Application which we will be designing depends on these data. We cannot certainly have all required data in one single database.
Option of creating creating identical tables and syncing regularly is as i feel the better than writing down the distributed queries.
Any thought on it ??
Thanks,|||Hi all,
Thanks for all kinds of replies.
Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department. Application which we will be designing depends on these data. We cannot certainly have all required data in one single database.
Option of creating creating identical tables and syncing regularly is as i feel the better than writing down the distributed queries.
Any thought on it ??
Thanks,|||What's wrong with just downloading the data from the other data sources via feeds - bcp each file into a transfer table - check the data - put the changed data into your database. How many feeds could you expect this way, what size (roughly) for each feed?
Your method may save you having to write any feeds but the people writing the app are going to hate you for the rest of their lives :)
Mike|||Well first let me clear (which i shud have done in my first post.) , that database are hosted on diff machine coz they are mastered by diff groups of ppl or the department.This sounds like a small data warehousing initiative to me. Perhaps that is the approach you should be taking.|||We do stuff like this on a daily basis. Consolidating data from different departments on a single machine for historical and modeling purposes. I use a combination of distributed queries and web services that allow us to yank data in flat file format from some machines, and yank data directly from others (via linked servers).
Seems like downloading and synching databases is alot more work than you need to be doing to do what (i understand to be...) you need to do. Data feeds and distributed queries are much better with respect to basic database design conventions, and synching databases just causes a spontaneous sphincter constriction whenever I hear it.
Duplication of data = bad. Direct/indirect (and on-demand) access to remote data = good.
Sounds like you've already decided though, so g'luck to you :)|||Well the things getting more complex. We will be having four external database to depends on for our application.
Reason of staying away from distributed query was that our application requires this data (external database table serves as master table for our application) in the transactions. And of course in reports as well.
I liked the suggestion of getting feed from external database into temporary staging area and then updating only updating the changesets in our tables.|||Originally posted by versatile_me
I liked the suggestion of getting feed from external database into temporary staging area and then updating only updating the changesets in our tables.I'd like to claim first dibs on the concept but I suspect it's been around for 40 years.
I know it's fun comming up with new systems but after you spend 6 months sweating blood trying to get it to work there are only a few possible outcomes :
it fails and you have to call in a professional who is then faced with working with a flawed design or loosing the client by throwing everything away and starting afresh.
it works, but only just, and after 3 months limping along the system gets abandonded.
it works and is a fantastic success - I'm afraid this is the least likely outcome :)
If the application is at all important to your company then it may well be worth getting an experienced profesional in to do the work and you provide the business requirements. This would be a good way to learn about database/system design and also might ensure any good ideas you may have will appear in the finished product.
Mike|||We do stuff like this on a daily basis. Consolidating data from different departments on a single machine for historical and modeling purposes. I use a combination of distributed queries and web services that allow us to yank data in flat file format from some machines, and yank data directly from others (via linked servers).
Seems like downloading and synching databases is alot more work than you need to be doing to do what (i understand to be...) you need to do. Data feeds and distributed queries are much better with respect to basic database design conventions, and synching databases just causes a spontaneous sphincter constriction whenever I hear it.
Duplication of data = bad. Direct/indirect (and on-demand) access to remote data = good.
Sounds like you've already decided though, so g'luck to you :)
...
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
I'm struggling to see much difference between TallCowboy's "daily consolidation" and the OP's idea of equiv. Master tables that are regularly refreshed.
Although duplicate data = bad, isn't it common practice to have read-only replicated tables on a different server for reporting (or other read-only) purposes? Obviously; the data is as old as the last replication so the designer has to consider the ramification of slightly old data, but this localizes remote-connection / design breakages into the DTS / Copy programs, not the report.
Not so much "versitile" as "stable". (at least - until the source system changes and you have to fix the DTS - or retest it each and every time it changes ... so I'm not saying it's a great idea, just maybe unavoidable).|||I'm struggling to see much difference between TallCowboy's "daily consolidation" and the OP's idea of equiv. Master tables that are regularly refreshed.The difference is that in my system the data is not imported on a daily basis "as-is", but rather the existing tables are updated with NEW data that we did not previously have. However, I see your point in that the difference may be perception (and/or semantics) and my understanding of what the O.P. is contemplating as an implementation.
In my world, the data we import (via web service or via remote access) is not a "synching" of databases. It's the importation of data from external providers, and placed into our own "consolidated" and normalized database tables (and used for our own devious purposes completely unrelated to the original source(s) of the data). At least in my eyes, a completely different process than importing actual tables from a remote database and keeping them in synch.
Although duplicate data = bad, isn't it common practice to have read-only replicated tables on a different server for reporting (or other read-only) purposes? Obviously; the data is as old as the last replication so the designer has to consider the ramification of slightly old data, but this localizes remote-connection / design breakages into the DTS / Copy programs, not the report. I can't speak for this one, because we don't do it. We either provide access to our data via controlled (read-only) linked servers and associated userid's, or via a web service that is a front-end to our database.
We absolutely don't allow uncontrolled access to our tables, even remote access via linked servers/userid's is typically controlled with a view. If there is time and/or the longevity of the projects dictates, we write a web service wrapper to the external world and make them keep their nasty little grubbies off our databases.
We expect (and generally require) external/remote databases to have (or support) the same indirect access. We don't want our applications failing because they changed a table on the external system.
Not so much "versatile" as "stable". (at least - until the source system changes and you have to fix the DTS - or retest it each and every time it changes ... so I'm not saying it's a great idea, just maybe unavoidable).ed-zachary :)|||i think this board has more of a tendency to slam the OP.
Really?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67782&whichpage=8|||ROTFLMAO ... like Mastercard ... that was priceless :D
Friday, February 24, 2012
database design issue
Here's a situation I'm trying to replicate in a database. A CASE can be tied to multiple EVENTS and an EVENT can have multiple CONTACTS. Each CASE or a CONTACT can have a SURVEY.
I am unable to figure a way to accomplish the last task.
Any help is greatly appreciated!
-Jay
Note: Words in capitals represent tables.Create a table of surveys and include the primary key as a foreign key in the CASE and CONTACTS tables.
Not sure what is so difficult about this...|||Not sure what is so difficult about this...Losing a table made it easier I guess :)|||Thank you Blindman...I guess i was thinking out loud :D|||Glad to help. Welcome to the forums.
I am unable to figure a way to accomplish the last task.
Any help is greatly appreciated!
-Jay
Note: Words in capitals represent tables.Create a table of surveys and include the primary key as a foreign key in the CASE and CONTACTS tables.
Not sure what is so difficult about this...|||Not sure what is so difficult about this...Losing a table made it easier I guess :)|||Thank you Blindman...I guess i was thinking out loud :D|||Glad to help. Welcome to the forums.
Tuesday, February 14, 2012
Database could not be removed
We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now we
are facing a strange case where when we tried to restore and overwrite a
database, it always say database is in use and cannot be overwritten. I have
tried to detach and even delete it but everytime it just said the database is
in use and refused to give up. I have checked and make sure no users and
connections are make to the said database. Eventually I have to resort to
restarting the SQL service to remove this database.
Is there a way to remove or overwrite a database without restarting the SQL
service?Are you sure YOU are not in the database when you are trying to restore or
detach?
If you run the sp_lock procedure, look for DB locks in the database you are
trying to remove. There will then be a spid, which can let you determine who
is in the database.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mpWong" <mpWong@.discussions.microsoft.com> wrote in message
news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> we
> are facing a strange case where when we tried to restore and overwrite a
> database, it always say database is in use and cannot be overwritten. I
> have
> tried to detach and even delete it but everytime it just said the database
> is
> in use and refused to give up. I have checked and make sure no users and
> connections are make to the said database. Eventually I have to resort to
> restarting the SQL service to remove this database.
> Is there a way to remove or overwrite a database without restarting the
> SQL
> service?
>|||Thanks for your advice. I will check next time this happens again. I am
absolutely sure nobody or myself was using the database when I tried the
restore.
"Kalen Delaney" wrote:
> Are you sure YOU are not in the database when you are trying to restore or
> detach?
> If you run the sp_lock procedure, look for DB locks in the database you are
> trying to remove. There will then be a spid, which can let you determine who
> is in the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mpWong" <mpWong@.discussions.microsoft.com> wrote in message
> news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> > We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> > we
> > are facing a strange case where when we tried to restore and overwrite a
> > database, it always say database is in use and cannot be overwritten. I
> > have
> > tried to detach and even delete it but everytime it just said the database
> > is
> > in use and refused to give up. I have checked and make sure no users and
> > connections are make to the said database. Eventually I have to resort to
> > restarting the SQL service to remove this database.
> > Is there a way to remove or overwrite a database without restarting the
> > SQL
> > service?
> >
>
>
are facing a strange case where when we tried to restore and overwrite a
database, it always say database is in use and cannot be overwritten. I have
tried to detach and even delete it but everytime it just said the database is
in use and refused to give up. I have checked and make sure no users and
connections are make to the said database. Eventually I have to resort to
restarting the SQL service to remove this database.
Is there a way to remove or overwrite a database without restarting the SQL
service?Are you sure YOU are not in the database when you are trying to restore or
detach?
If you run the sp_lock procedure, look for DB locks in the database you are
trying to remove. There will then be a spid, which can let you determine who
is in the database.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mpWong" <mpWong@.discussions.microsoft.com> wrote in message
news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> we
> are facing a strange case where when we tried to restore and overwrite a
> database, it always say database is in use and cannot be overwritten. I
> have
> tried to detach and even delete it but everytime it just said the database
> is
> in use and refused to give up. I have checked and make sure no users and
> connections are make to the said database. Eventually I have to resort to
> restarting the SQL service to remove this database.
> Is there a way to remove or overwrite a database without restarting the
> SQL
> service?
>|||Thanks for your advice. I will check next time this happens again. I am
absolutely sure nobody or myself was using the database when I tried the
restore.
"Kalen Delaney" wrote:
> Are you sure YOU are not in the database when you are trying to restore or
> detach?
> If you run the sp_lock procedure, look for DB locks in the database you are
> trying to remove. There will then be a spid, which can let you determine who
> is in the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mpWong" <mpWong@.discussions.microsoft.com> wrote in message
> news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> > We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> > we
> > are facing a strange case where when we tried to restore and overwrite a
> > database, it always say database is in use and cannot be overwritten. I
> > have
> > tried to detach and even delete it but everytime it just said the database
> > is
> > in use and refused to give up. I have checked and make sure no users and
> > connections are make to the said database. Eventually I have to resort to
> > restarting the SQL service to remove this database.
> > Is there a way to remove or overwrite a database without restarting the
> > SQL
> > service?
> >
>
>
Database could not be removed
We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now we
are facing a strange case where when we tried to restore and overwrite a
database, it always say database is in use and cannot be overwritten. I have
tried to detach and even delete it but everytime it just said the database is
in use and refused to give up. I have checked and make sure no users and
connections are make to the said database. Eventually I have to resort to
restarting the SQL service to remove this database.
Is there a way to remove or overwrite a database without restarting the SQL
service?
Are you sure YOU are not in the database when you are trying to restore or
detach?
If you run the sp_lock procedure, look for DB locks in the database you are
trying to remove. There will then be a spid, which can let you determine who
is in the database.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mpWong" <mpWong@.discussions.microsoft.com> wrote in message
news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> we
> are facing a strange case where when we tried to restore and overwrite a
> database, it always say database is in use and cannot be overwritten. I
> have
> tried to detach and even delete it but everytime it just said the database
> is
> in use and refused to give up. I have checked and make sure no users and
> connections are make to the said database. Eventually I have to resort to
> restarting the SQL service to remove this database.
> Is there a way to remove or overwrite a database without restarting the
> SQL
> service?
>
|||Thanks for your advice. I will check next time this happens again. I am
absolutely sure nobody or myself was using the database when I tried the
restore.
"Kalen Delaney" wrote:
> Are you sure YOU are not in the database when you are trying to restore or
> detach?
> If you run the sp_lock procedure, look for DB locks in the database you are
> trying to remove. There will then be a spid, which can let you determine who
> is in the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mpWong" <mpWong@.discussions.microsoft.com> wrote in message
> news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
>
>
are facing a strange case where when we tried to restore and overwrite a
database, it always say database is in use and cannot be overwritten. I have
tried to detach and even delete it but everytime it just said the database is
in use and refused to give up. I have checked and make sure no users and
connections are make to the said database. Eventually I have to resort to
restarting the SQL service to remove this database.
Is there a way to remove or overwrite a database without restarting the SQL
service?
Are you sure YOU are not in the database when you are trying to restore or
detach?
If you run the sp_lock procedure, look for DB locks in the database you are
trying to remove. There will then be a spid, which can let you determine who
is in the database.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mpWong" <mpWong@.discussions.microsoft.com> wrote in message
news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> we
> are facing a strange case where when we tried to restore and overwrite a
> database, it always say database is in use and cannot be overwritten. I
> have
> tried to detach and even delete it but everytime it just said the database
> is
> in use and refused to give up. I have checked and make sure no users and
> connections are make to the said database. Eventually I have to resort to
> restarting the SQL service to remove this database.
> Is there a way to remove or overwrite a database without restarting the
> SQL
> service?
>
|||Thanks for your advice. I will check next time this happens again. I am
absolutely sure nobody or myself was using the database when I tried the
restore.
"Kalen Delaney" wrote:
> Are you sure YOU are not in the database when you are trying to restore or
> detach?
> If you run the sp_lock procedure, look for DB locks in the database you are
> trying to remove. There will then be a spid, which can let you determine who
> is in the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mpWong" <mpWong@.discussions.microsoft.com> wrote in message
> news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
>
>
Database could not be removed
We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now w
e
are facing a strange case where when we tried to restore and overwrite a
database, it always say database is in use and cannot be overwritten. I have
tried to detach and even delete it but everytime it just said the database i
s
in use and refused to give up. I have checked and make sure no users and
connections are make to the said database. Eventually I have to resort to
restarting the SQL service to remove this database.
Is there a way to remove or overwrite a database without restarting the SQL
service?Are you sure YOU are not in the database when you are trying to restore or
detach?
If you run the sp_lock procedure, look for DB locks in the database you are
trying to remove. There will then be a spid, which can let you determine who
is in the database.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mpWong" <mpWong@.discussions.microsoft.com> wrote in message
news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> we
> are facing a strange case where when we tried to restore and overwrite a
> database, it always say database is in use and cannot be overwritten. I
> have
> tried to detach and even delete it but everytime it just said the database
> is
> in use and refused to give up. I have checked and make sure no users and
> connections are make to the said database. Eventually I have to resort to
> restarting the SQL service to remove this database.
> Is there a way to remove or overwrite a database without restarting the
> SQL
> service?
>|||Thanks for your advice. I will check next time this happens again. I am
absolutely sure nobody or myself was using the database when I tried the
restore.
"Kalen Delaney" wrote:
> Are you sure YOU are not in the database when you are trying to restore or
> detach?
> If you run the sp_lock procedure, look for DB locks in the database you ar
e
> trying to remove. There will then be a spid, which can let you determine w
ho
> is in the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mpWong" <mpWong@.discussions.microsoft.com> wrote in message
> news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
>
>
e
are facing a strange case where when we tried to restore and overwrite a
database, it always say database is in use and cannot be overwritten. I have
tried to detach and even delete it but everytime it just said the database i
s
in use and refused to give up. I have checked and make sure no users and
connections are make to the said database. Eventually I have to resort to
restarting the SQL service to remove this database.
Is there a way to remove or overwrite a database without restarting the SQL
service?Are you sure YOU are not in the database when you are trying to restore or
detach?
If you run the sp_lock procedure, look for DB locks in the database you are
trying to remove. There will then be a spid, which can let you determine who
is in the database.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"mpWong" <mpWong@.discussions.microsoft.com> wrote in message
news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
> We are running SQL Server 2000 Ent SP4 on Windows Server 2003 Ent SP1. Now
> we
> are facing a strange case where when we tried to restore and overwrite a
> database, it always say database is in use and cannot be overwritten. I
> have
> tried to detach and even delete it but everytime it just said the database
> is
> in use and refused to give up. I have checked and make sure no users and
> connections are make to the said database. Eventually I have to resort to
> restarting the SQL service to remove this database.
> Is there a way to remove or overwrite a database without restarting the
> SQL
> service?
>|||Thanks for your advice. I will check next time this happens again. I am
absolutely sure nobody or myself was using the database when I tried the
restore.
"Kalen Delaney" wrote:
> Are you sure YOU are not in the database when you are trying to restore or
> detach?
> If you run the sp_lock procedure, look for DB locks in the database you ar
e
> trying to remove. There will then be a spid, which can let you determine w
ho
> is in the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "mpWong" <mpWong@.discussions.microsoft.com> wrote in message
> news:C1F40A29-F5E0-4A8A-A993-4167AB6A76F2@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)