Drop & Recreate the DB
-
I'm starting a new script. This is local on my machine and I'm the only user:
-- IF THE DATABASE EXISTS, DROP AND RE-CREATE IT
IF EXISTS (SELECT Name FROM master.dbo.sysdatabases WHERE name = N'Dashboard')
BEGIN
ALTER DATABASE Dashboard SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Dashboard
END
CREATE DATABASE Dashboard-- CREATE THE TABLES
GO
USE Dashboard
CREATE TABLE DashboardInfo
(Id INT PRIMARY KEY NOT NULL,
SiteId INT NOT NULL,
InstrumentId INT NOT NULL,
TowerLocation INT NOT NULL)The first time I run it all works fine. Any time I run it after that I get
Msg 3702, Level 16, State 3, Line 5
Cannot drop database "Dashboard" because it is currently in use.
Msg 1801, Level 16, State 3, Line 7
Database 'Dashboard' already exists. Choose a different database name.
Msg 2714, Level 16, State 6, Line 12
There is already an object named 'DashboardInfo' in the database.If I close and re-open SSMS then it works ok again. WTF is wrong here??????????
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
I'm starting a new script. This is local on my machine and I'm the only user:
-- IF THE DATABASE EXISTS, DROP AND RE-CREATE IT
IF EXISTS (SELECT Name FROM master.dbo.sysdatabases WHERE name = N'Dashboard')
BEGIN
ALTER DATABASE Dashboard SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Dashboard
END
CREATE DATABASE Dashboard-- CREATE THE TABLES
GO
USE Dashboard
CREATE TABLE DashboardInfo
(Id INT PRIMARY KEY NOT NULL,
SiteId INT NOT NULL,
InstrumentId INT NOT NULL,
TowerLocation INT NOT NULL)The first time I run it all works fine. Any time I run it after that I get
Msg 3702, Level 16, State 3, Line 5
Cannot drop database "Dashboard" because it is currently in use.
Msg 1801, Level 16, State 3, Line 7
Database 'Dashboard' already exists. Choose a different database name.
Msg 2714, Level 16, State 6, Line 12
There is already an object named 'DashboardInfo' in the database.If I close and re-open SSMS then it works ok again. WTF is wrong here??????????
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
Try putting USE [Master] at the top of the script
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
Try putting USE [Master] at the top of the script
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
I did. No change
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
I did. No change
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
What about it?
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
-
I'm starting a new script. This is local on my machine and I'm the only user:
-- IF THE DATABASE EXISTS, DROP AND RE-CREATE IT
IF EXISTS (SELECT Name FROM master.dbo.sysdatabases WHERE name = N'Dashboard')
BEGIN
ALTER DATABASE Dashboard SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Dashboard
END
CREATE DATABASE Dashboard-- CREATE THE TABLES
GO
USE Dashboard
CREATE TABLE DashboardInfo
(Id INT PRIMARY KEY NOT NULL,
SiteId INT NOT NULL,
InstrumentId INT NOT NULL,
TowerLocation INT NOT NULL)The first time I run it all works fine. Any time I run it after that I get
Msg 3702, Level 16, State 3, Line 5
Cannot drop database "Dashboard" because it is currently in use.
Msg 1801, Level 16, State 3, Line 7
Database 'Dashboard' already exists. Choose a different database name.
Msg 2714, Level 16, State 6, Line 12
There is already an object named 'DashboardInfo' in the database.If I close and re-open SSMS then it works ok again. WTF is wrong here??????????
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.
because of the
use Dashboardline, it creates a connection to the database in question.if exists (select name from sys.databases where name='YourDBName')
alter database YourDBName set single_user with rollback immediate
go
if exists (select name from sys.databases where name='YourDBName')
drop database YourDBNameDropping and recreating databases in Microsoft SQL Server - Stack Overflow[^]
Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON
-
because of the
use Dashboardline, it creates a connection to the database in question.if exists (select name from sys.databases where name='YourDBName')
alter database YourDBName set single_user with rollback immediate
go
if exists (select name from sys.databases where name='YourDBName')
drop database YourDBNameDropping and recreating databases in Microsoft SQL Server - Stack Overflow[^]
Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON
Thanks. That helped... Working fine now
If it's not broken, fix it until it is. Everything makes sense in someone's mind. Ya can't fix stupid.