What is the significance of dropping connections (by chosing drop connection option) while detaching the database? Can it create problems if I don't select this option?
View Complete Post
I have a group of developers that are creating stored procedures, in them they are dropping a table and re-creating the table and then continuing with the processing. My question is if there is any difference between the effect of dropping a table
and truncating the table on database fragmentatiom.
I am trying to add a new database via the App_Data Folder and I receive an error described below.
I had attached an mdf file to try to use it in SQL Server 2008 Developer and Visual Studio 2010, which was is included in a book's lesson. Whenever I try to add a database within the App_Data Folder I get an error.
I can create a db by attaching an mdf via SQL Server Mgmt Studio, it connects fine, and I can see tables.
Mgmt Studio- Right Click Databases, Attach, Add, then once mdf is located:
The mdf file location, Database Name, Attach As, -- are all the same:
Do I need to change the Database Name?
Visual Studio- Server Explorer connects fine to mdf OR regular Database, I can see the tables.
However when I attempt to add ANY TYPE OF SQL SERVER DATABASE through the App_Data folder I receive this error "Connections to SQL Server Files (*.mdf) require SQL Server Express 2008 to function properly. Please verify the installation of the
component or download from the URl."
I tested this for both a normal dbo (non mdf) and the mdf.dbo I attached before, with the same error!
Im running XP SP3 w/ Visual Studio 2010 and SQL Server 2008 R2 Developer. I had previously removed SQL Server Express.
I have the following:
if exists (select name from sys.databases where name = @DatabaseName)
alter database @DatabaseName set single_user with rollback immediate
drop database @DatabaseName
I get an error on the second go:
Incorrect sintax near go.
Basically I am checking if the database exists. If yes then close all connections to it and drop it.
What am I missing?
Am I closing the connections correctly?
I have a secondary data connection with a lookup field into another list. To display the fields text value instead of the index, I am using a drop down list which gets its choices from the linked list. This information is read-only so I disabled the control
via formatting rule.
A calculated value control might be better suited, but my XPath skills are not very "developed". Has anyone done this before? Taken the index from the bound field and extracted the text value from a second data connection? Thx
Hi, we have a database restore script that runs an ALTER DATABASE [Database] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE command. The next statement is a WAITFOR DELAY with 30 seconds. This is to ensure any non compliant connections are
killed before continuing.
However, I'm wondering if the WAITFOR is actually necessary? Would the ALTER DATABASE command not block until all connections have been killed?
I can not delete user from a database in sql2005 beta 3.the message errror is :TITLE: SQL Server Management Studio----------------------------------------
Drop failed for User 'Amministratore'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.0981.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The database principal owns a schema and cannot be dropped. (Microsoft SQL Server, Error: 15138)
in sql 2000 I can delete the user very easy, but in sql 2005 I don't understant How to do it.
SQL 2005 Standard Edition X64 version 9.00.3042.00
I have an interesting problem here that I can not seem to figure out. I am trying to drop a user database and it keeps failing.
First, I started off using a simple DROP statement:
DROP DATABASE database_name
I received an error saying it was in use. No problem, I figured. So I then tried to execute this:
ALTER DATABASE database_name SET SINGLE_USER
No luck here either. I received an error message saying a lock could not be placed on the database. Try again later. Hmm. For kicks, I tried the detach database stored procedure:
exec sp_detach_db @dbname = 'database_name', @skipchecks = 'true', @keepfulltextindexfile = 'false'
No luck here either. I got curious and tried to see what process was using the database:
SELECT * FROM sysprocesses WHERE dbid = DB_ID('database_name')
I got no results. So apparently nothing is using it.
For added fun, I checked the database properties by going into SSMS and right clicking the database and selecting Properties. This is where it gets interesting. I received an error dialog that states:
Cannot show requested dialog. Th
Hi all. I need to programmatically remove the MS SQL database which used in my application. Currently I'm using the following code for this:
SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=<PATH_TO_MDF_FILE>;Integrated Security=True;Pooling = False;User Instance=True;MultipleActiveResultSets=True");<br/>
SqlCommand alterCommand = new SqlCommand("ALTER DATABASE \"<PATH_TO_MDF_FILE>\" SET SINGLE_USER WITH ROLLBACK IMMEDIATE", conn);<br/>
SqlCommand dropCommand = new SqlCommand("DROP DATABASE \"<PATH_TO_MDF_FILE>\";", conn);<br/>
//Not sure that pools clearing actually needed<br/>
//"Cannot drop database "<PATH_TO_MDF_FILE>" because it is currently in use." exception is raised here<br/>
I have an app that is reading and writing from databases that are structurally identical but are from different systems, primarily SQL Server and Access 2010. When using the respective .Net objects such as connections, commands, datareaders,
the code seems to be often the same (except for transactions - Access doesn't do transactions?). But the objects are different - sqlconnection versus oledbconnection, etc.
Is there a standard way of writing data access code that works regardless of the database type?
I am trying to set up the following.
I have configured the sql server side no problem. i am having issue getting the application that uses the database to connect to the mirrored server in the event of failure.
The application that utalises the database connects through the data sources (ODBC), System DSN, SQL driver. I see no option in the configuration for the addition of a 2nd IP. Many of the technical references i have read refer to the data source
connection string, i cannot find this in the registry however. Please help!
I am used to a 3rd party mirroring software where you connect to a logical IP address which then routes through to whichever database is running.
I am trying to save connections in management studio so I don't have to re-enter them every time. I have to connect to multiple servers and I just want to be able to open management studio and have them already in there. Is there anyway to do