SQL Server Data-tier Application (DAC) or Sql Server Database Project - Visual Studio 2010

I am trying to figure out which one to use and when.

I found out via this post that:

• The SQL Server Data-tier Applciation (DAC) project type *only* works with SQL Server 2008 R2 databases

Although this instance of SQL Server is available, this seems like quite a restriction - i'm tempted to instantly dismiss this project type in favour of the Sql Server Database Project.

I found out via this post that:

• The Sql Server Database Project (*.dbproj)  "...will import all objects from your database as SQL scripts. This can be stuck into source control."

SQL Injection Attacks - EXEC() vs sp_executesql

Are Stored Procedures safe against SQL Injection Attacks...not necessarily.

If dynamic SQL is involved, they might not be.

This article explains the difference clearly:

Are Stored Procedures safe against SQL Injection Attacks

This extract summarizes why you should use sp_executesql instead of EXEC() in order to prevent Injection Attacks:

"The difference between the EXEC() and sp_executesql is that the former takes SQL statement in a string as parameter. But in case of the latter, it is a system procedure whose first parameter is a parameterized SQL statement. The second parameter is a parameter-list declaration, similar to the parameter list present in the declaration of a stored procedure. And the remaining are simply the parameters in that parameter-list. So the query built using this method is always same whenever it gets called but the user input changes which will be supplied at runtime. If the malicious user enters a value like ' or '1'='1, it will be passed as a parameter to the SQL statement. It won’t be part of the SQL statement any more as we have seen in vulnerable stored procedure above. It will search for the value ' or '1'='1 as product name in the database. Thus preventing SQL Injection attacks. "

Rename a SQL Server 2008 Database

-- ============

-- USAGE NOTES

-- Best to run each Block of GO statements individually so that if there are any errors, debugging is easy.

-- ============

-- Use the logical name returned for the database

SELECT *, name AS LogicalName, physical_name AS CurrentLocation, state_desc AS StateDesc

FROM sys.master_files

WHERE database_id = DB_ID(N'<OLD_DataBase_Name>');

USE MASTER

-- Set the RESTRICT ACCESS property to SINGLE_USER in order to obtain exclusive lock

ALTER DATABASE <OLD_DataBase_Name>

SET SINGLE_USER

GO

-- Set the DB OFFLINE so we can change the MDF and LDF files

ALTER DATABASE <OLD_DataBase_Name> SET OFFLINE

GO

-- Change the name of the 'pointer' to the MDF and LDF files...note that this doesn't actually physically rename the files at the Operating System level

ALTER DATABASE <OLD_DataBase_Name> MODIFY FILE ( NAME = <LogicalName>, FILENAME = 'path\<NEW_DataBase_Name>.mdf' )

GO

ALTER DATABASE <OLD_DataBase_Name> MODIFY FILE ( NAME = <LogicalName>, FILENAME = 'path\<NEW_DataBase_Name>.ldf' )

GO

-- !!! STOP !!!

-- GO PHYSICALLY RENAME THE FILES IN EXPLORER NOW !!! If you don't, setting the DB ONLINE will fail.

-- Set the DB back ONLINE

ALTER DATABASE <OLD_DataBase_Name> SET ONLINE

GO

-- Change the name of the database

ALTER DATABASE <OLD_DataBase_Name>

MODIFY NAME = <NEW_DataBase_Name>

GO

-- Set the RESTRICT ACCESS property back to the default MULTI_USER

ALTER DATABASE <NEW_DataBase_Name>

SET MULTI_USER

GO

-- Ensure that the changes have taken place correctly

SELECT *, name AS LogicalName, physical_name AS CurrentLocation, state_desc AS StateDesc

FROM sys.master_files

WHERE database_id = DB_ID(N'<NEW_DataBase_Name>');

Changing column COLLATION for Computed Columns

Cannot perform ALTER DATABASE within a TRANSACTION

I learnt today that you cannot perform an ALTER DATABASE operation within an 'implicit' or 'explicit' TRANSACTION.

I have a bunch of databases that i want to change the COLLATION of...ideally i would like them all to not have one set and just inherit from the server collation setting...but it appears you cannot do:

ALTER DATABASE MyDb COLLATE ''

As such...so the idea was to explicitly set the collation to the same value as the Server.

But when i tried to do them all in a TRANSACTION:

BEGIN TRANSACTION
-- TEST
ALTER DATABASE TMP_CHCR_DEV COLLATE SQL_Latin1_General_CP1_CI_AS

ALTER DATABASE database1 collate Latin1_General_CI_AS;
...
ALTER DATABASE databaseN collate Latin1_General_CI_AS;

WAITFOR DELAY '00:00:30'

ROLLBACK TRANSACTION

-- TEST the TRANSACTION
SELECT name, collation_name FROM sys.databases
where collation_name != 'Latin1_General_CI_AS'
-- This should return no results if the above has worked.

I got this error:

"ALTER DATABASE statement not allowed within multi-statement transaction."

Which is because ALTER DATABASE statements "must run in Autocommit" mode...

See this article for more info on Autocommit mode

The solution is to run each of the statements one by one...