Sql Server 2008 Management Studio: Prevent saving changes that require table re-creation

There are times when a database developer may want to save changes to a table without dropping and re-creating the table.
However, sql server 2008 management studio doesn't always allow this to happen. Luckily, there is something you can do about it.

When inside of SQL Server Management studio:
1. Go to Tools menu -> Click on Options
You will see the Options window appear.
It will look like this:

2. Click on the triangle next to "Designers" and then click on the "Table and Database Designers" section.
It should then look like this:

3. Uncheck the "Prevent saving changes that require table re-creation".
4. Click OK

You should now be able to save changes as long as the save doesn't take too long.
NOTE: If the table you are trying to save changes too has alot of data in it, you may want to change the "Command Timeout" to not timeout after 30 seconds.

Closing stream after error

Sometimes there are cases where it is appropriate to create one streamReader to be used with multiple files like the code below. But what if an error were to occur before the stream closed. Uh oh, theres a problem. Our catch would catch the exception, but we would get an error when an attept is made to move the file to the Error directory. What do we do?
Easy, wrap your stream in a using statement and remove the "stream.Close()" because it's no longer needed. This will make sure the stream is closed even if there is an exception during the Parsing of the XML.
The file can be moved successfully and you will not have to worry about memory leaks.

StreamReader stream;
DirectoryInfo ImportDir = new DirectoryInfo(importFileDirectory);
var Files = Directory.GetFiles(importFileDirectory, "*.dat").Select(fn => new FileInfo(fn)).OrderBy(f => f.Name);
foreach (FileInfo file in Files)
{
if (checkFileAvailable(file.FullName))
{
try
{
BatchID = file.Name.Remove(file.Name.Length - 4);
xmldoc.Load(stream);
ParseXML(xmldoc);

stream.Close();

//move file to archive folder
DateTime dt = DateTime.Now;
string s = dt.ToString("yyMMddHHmmss");
System.IO.File.Move(file.FullName, archiveFileDirectory + file.Name.Remove(file.Name.Length - 4) + s + file.Extension);

}
catch (Exception ex)
{
//move file to Error folder
DateTime dt = DateTime.Now;
string s = dt.ToString("yyMMddHHmmss");
System.IO.File.Move(file.FullName, errorFileDirectory + file.Name.Remove(file.Name.Length - 4) + s + file.Extension);

LogDescription = string.Format("Import ERROR!");
Logger.Log_Err(LogDescription, ex);
Logger.LogDistributionResult("0", "0", "0", "C4", "F", "IN", BatchID, "File Failed to Load: Exception: " + ex);
continue;
}
}
}

This is the way the new code will look inside the try.

//Use a using to make sure a stream is closed even if there is an exception.
using (stream = new StreamReader(file.FullName))
{
BatchID = file.Name.Remove(file.Name.Length - 4);
xmldoc.Load(stream);
ParseXML(xmldoc);
}
//move file to archive folder
DateTime dt = DateTime.Now;
string s = dt.ToString("yyMMddHHmmss");
System.IO.File.Move(file.FullName, archiveFileDirectory + file.Name.Remove(file.Name.Length - 4) + s + file.Extension);

God crushes rebellion

Numbers 16:1-5,19,32-33 NLT

[1] One day Korah son of Izhar, a descendant of Kohath son of Levi, conspired with Dathan and Abiram, the sons of Eliab, and On son of Peleth, from the tribe of Reuben. [2] They incited a rebellion against Moses, along with 250 other leaders of the community, all prominent members of the assembly. [3] They united against Moses and Aaron and said, "You have gone too far! The whole community of Israel has been set apart by the LORD, and he is with all of us. What right do you have to act as though you are greater than the rest of the LORD's people?" [4] When Moses heard what they were saying, he fell face down on the ground. [5] Then he said to Korah and his followers, "Tomorrow morning the LORD will show us who belongs to him and who is holy. The LORD will allow only those whom he selects to enter his own presence. [19] Meanwhile, Korah had stirred up the entire community against Moses and Aaron, and they all gathered at the Tabernacle entrance. Then the glorious presence of the LORD appeared to the whole community, [32] The earth opened its mouth and swallowed the men, along with their households and all their followers who were standing with them, and everything they owned. [33] So they went down alive into the grave, along with all their belongings. The earth closed over them, and they all vanished from among the people of Israel.

Korah and his follower tried to bring down Moses and Aaron by saying that him and his people were just as Holy as the chosen servants of God (Moses and Aaron). It was rebellion and God dealt with them swiftly. Never underestimate the power and favor God has given a leader in your church because if you go against Gods chosen, you could be dealt with in a way that is not so wanted. Also, recognize that if you are a leader in your church, there will most always be someone that doesn't like or appreciate you. 

Using the table value constructor to specify multiple values in the FROM clause of a SELECT statement

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);
When I first saw this syntax, I wasn't sure if it would work. However, after testing, this worked perfectly.
This tsql statement could save alot of time for the people that don't need to create a permanent object in the database, but is looking for a quick fix.
This statement could be used as a function or it could be used to select multiple values to insert into a table.

Here is a situation where it could be used in the place of a function.
EX:
Select @dCollected = (SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b))

Here is a situation where it could be used in the place filling a temporary table and selecting from it.
EX:
SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b)
Union
Select a, b From PermanentOrTempTable

This is a good use of this syntax.
However, I would not recommend using this often as it could lead to spagetti code and cannot be reused unless pasted over and over again which is bad development.

Reference: https://msdn.microsoft.com/en-us/library/dd776382.aspx

SSRS Dynamic Grouping

At some point, you will want your users to be able to choose what to group their reports by. This article written by Chris Hays shows perfectly how to create a dynamic grouping:

http://blogs.msdn.com/b/chrishays/archive/2004/07/15/dynamicgrouping.aspx?Redirected=true

This is definately worth checking out.

FoxPro shorthand syntax for searching a string

The dollar sign in between two strings is a search for symbol like the following:
cSearchFor $ cSearchIn

How to perform a database backup with tsql

Most people (that have used sql server) know how to backup and restore a database as a new name from Sql Server Management Studio, but it‘s not always quite so apparent how to backup and restore a database as a different name in tsql. Here is just a reminder as to how to backup and restore into a different name. This can also be used for seperating “Development” and “Test”.

— Perform database backups

print ‘Backup Development database’

BACKUP DATABASE [Development]

TO DISK = N’G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Development\Development_full_backup.bak’

WITH NOFORMAT, INIT, NAME = N’Development-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

print ‘Backup Test database’

BACKUP DATABASE [Test]

TO DISK = N’G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Test\Test_full_backup.bak’

WITH NOFORMAT, INIT, NAME = N’Test-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10


— Perform database restores from SeconddaryDev

use master

go

Alter Database [Test] set Auto_Update_Statistics_Async OFF

go

Alter Database [Test] set Single_User With Rollback Immediate

go

print ‘Restore Development to Test’

RESTORE DATABASE [Test]

FROM DISK = N’G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Development\Development_full_backup.bak’

WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

GO

Alter Database [Test] set Multi_User



go