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.