athinkr

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

2015-10-05T08:52:00.000-07:00

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

2015-07-07T07:21:00.002-07:00

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

2015-06-27T10:49:00.001-07:00

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

2015-06-02T07:16:00.002-07:00

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

2015-05-22T06:15:00.002-07:00

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

2015-05-22T06:14:00.004-07:00

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

2015-05-22T06:13:00.004-07:00

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

Windows service....not extremely intuitive

2013-12-05T08:48:00.000-08:00

I decided to try to create a windows service (that looks for rows every minute in a particular table in a database and does something when it finds a row in that table) today and when I first dove into the Visual Studio project I realized something. I didn't know where to put the things I wanted to happen while the service was running.

Yes, this is a very newbie thing for windows services, but it just didn't seem very easily understandable like other projects have been in the past.

This is what I seen when I first created the project after selecting the "View Code" part of the designer:

I guess this is typical. At least I know where to put the code when I'm starting and stopping the service.

Well, here I am. Lost. Where do I put my code to check the database.

Thank God for google. I did a little searching and found a great post at:

http://tutorials.csharp-online.net/Creating_a_.NET_Windows_Service%E2%80%94Alternative_1:_Use_a_Separate_Thread

Best part was this:

"When you start the service, Windows won't get feedback that the service has started, since the service blocks in the OnStart call. Windows will promptly report an appropriate error. Another catch is that you can't tell the service to stop, because it never leaves the OnStart event!"

I tried putting everything in the OnStart method in the past and could never figure out why my service wasn't working right.

This tutorial saved my life. Not really, but it did help me to figure out why my windows service would never start if I put everything in the OnStart method.

My answer was this: I need to put my "Working code" into it's own thread. After I got this revelation, things started flowing a little easier.

How to reseed a table via TSQL in SQL Server

2013-12-05T08:46:00.000-08:00

Sometimes it's useful to restart the identity of a table back to a certain number. DBCC CHECKIDENT ([TableName],reseed,0)

How to get the identity of a row that was last inserted into a specific table in a SQL Server database.

2013-12-05T08:45:00.000-08:00

Use the

IDENT_CURRENT

key word.
Here is the syntax: IDENT_CURRENT('stringTableName')

It could be used like this:

Declare @TestId int
set @TestId = IDENT_CURRENT('TestTable')

or it can be simply selected:
Select IDENT_CURRENT('TestTable')

Here is a link to Microsofts definition: IDENT_CURRENT

Setting up SSIS package to run automated.

2013-11-04T08:38:00.000-08:00

There may be a time when a load of data from another server of updating may need to be done automated via SQL server. If an SSIS package is needed for this type of a situation. A job on SQL server can be created and inside one of the steps of that job is where the SSIS would be put as shown below.

2013-09-26T07:56:00.002-07:00

Just found out how to generate insert statements in SQL Server Management Studio 2008. I had done it before, but this will be as a reminder.

Right click on the database and go to Tasks -> Generate Scripts
Select the tables (or object) that you want to generate the script against.
Go to Set scripting options and click on the Advanced button.
In the General category, go to Type of data to script
There are 3 options: Schema Only, Data Only, and Schema and Data. Select the appropriate option and click on OK.

Multiple columns into a pivot.

2013-07-26T13:15:00.000-07:00

Putting multiple columns into a pivot.
Took some time to figure out, but I got it to work.

1. Make data types of all columns the same.
Description: The idea is to make all columns of the same data type (Base query)

2. Unpivot the columns so they have a key, column name, and column value.
Description: Put all columns into one column with the "Column name and value" into a "ColName" field AND put the columns value into another field called "ColValue" (unpvt).

3. Finally, Pivot the values out by the "ColName" column
Description: The key and the values that were pivoted is what you want to select on this last step.

Example:
,Base AS (
SELECT theKey, Field3, Cast(Field1 as varchar) as Field1, Cast(Field2 as varchar) as Field2, cast(Field3 as varchar) as Field32
FROM theData
)
,unpvt AS (
SELECT theKey, ColName + CONVERT(varchar, Field3) AS ColName, ColValue
FROM Base
UNPIVOT (ColValue FOR ColName IN (Field1, Field2, Field32)) AS pvt
)
,repvt as (
SELECT theKey, Field1P, Field2P, Field32P, Field1R, Field2R, Field32R, Field1S, Field2S, Field32S, Field1C, Field2C, Field32C
FROM unpvt
PIVOT (MIN(ColValue) FOR ColName IN (Field1P, Field2P, Field32P, Field1R, Field2R, Field32R, Field1S, ModIdS, Field32S, Field1C, Field2C, Field32C)) AS pvt
)

Opening Remote SQL Server through VPN

2013-07-12T07:56:00.000-07:00

I wanted to be able to open sql server on my own machine and use the vpn as the connection. I had to create a shortcut on my desktop to login through the VPN.
1.Create shortcut on desktop and add the following extension:
C:\Windows\System32\runas.exe /netonly /user:remotedomain\remoteusername "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

2. Connect to VPN
3. Double click on ShortCut to open Sql Server.
NOTE: You will need to login with the remote password when you double click on the shortcut.
FYI: Also, I had to have the ip address on the box that sql server was being hosted on to make it work.
Hope this helps someone.

Here is a script that generates a script to check each column in a particular table whether they are completely NULL.

2013-07-05T13:08:00.003-07:00

A person would only need to take the UNION ALL off of the ending to run it with no problems.

Declare @NULL_COLUMNS TABLE(
table_name varchar(100),
[schema_name] varchar(130),
column_name varchar(150),
SelectStatement varchar(max)
)

Declare @Table_Name varchar(30)

set @Table_Name = 'TableName'

Insert Into @NULL_COLUMNS
Select
t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name,
'Select ''' + SCHEMA_NAME(schema_id) + '.' + t.name + '.' + c.name + ' - '' + Cast(isnull(AN.AmountNULL,0) as varchar(20)) + ''/'' + Cast(count(1) as varchar(20)) + '' is NULL '' as [Column], isnull(AN.AmountNULL,0) as AmountNULL, count(1) as Total from '+ SCHEMA_NAME(schema_id) + '.' + t.name + ' ai left join (Select ''' + c.name + ''' as ColumnName, count(1) as AmountNULL from '+ SCHEMA_NAME(schema_id) + '.' + t.name + ' where ' + c.name + ' is null ) AN on AN.ColumnName = ''' + c.name + ''' group by isnull(AN.AmountNULL,0) UNION ALL ' as SelectStatement
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name not in ('CreatedBy','CreatedDate','LastModified','LastModifiedBy')
and c.is_identity <> 1
and t.name = @Table_Name
ORDER BY schema_name, table_name;

Select * from @NULL_COLUMNS

2013-06-17T14:18:00.000-07:00

Reseeding (reseting) the identity of a table is really simple in SQL Server.

Check it out:

DBCC CHECKIDENT (TableName, reseed, 0)

This will reset the identity to 0 and any row inserted from this point on will start with 1. You can set the third parameter to any integer value.

Time span intersection check in SQL Server.

2013-06-13T07:38:00.000-07:00

While working in SQL server, a co-worker and I were trying to figure out how to tell if two time spans intersect in any way. We thought that the best way to do this would be to check for the opposite since there would be alot less check in those instances. Check it out!

if (@Beg1D <= @End1D and @Beg2D <= @End2D)
if (@Beg2D > @End1D or @Beg1D > @End2D)
Set @TimeSpansDoIntersect = 0
else
Set @TimeSpansDoIntersect = 1
else
Set @TimeSpansDoIntersect = null