Windows service....not extremely intuitive

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

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.

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.

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.

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.

1. Right click on the database and go to Tasks -> Generate Scripts
2. Select the tables (or object) that you want to generate the script against.
3. Go to Set scripting options and click on the Advanced button.
4. In the General category, go to Type of data to script
5. 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.

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

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.

 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

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.

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