Creating Needed Installer for Windows Services

This is not the actual "installer". This code is needed for the service to operate correctly. We will get to installing and deployment latter..

Add The Installer Code

From Service1 designer, right click in the gray area and choose Add Installer.

Another file will be added to your Solution Explorer, called ProjectInstaller. On this class will be two components in the designer, ServiceProccessInstaller1 and ServiceInstaller1.

Let's configure the Installer: Click on the listed component and alter the following properties:

ServiceProccessInstaller1:

• Account = LocalSystem (will cause service to run under local service account)

ServiceInstaller1:

• ServiceName = NewService
• StartType = Automatic (wether your service will auto start after reboot)
• DisplayName = NewService

Now we Build and Install the service to test it out.

Well, that's it for today. We'll continue with next lesson in another time.

Reference:
dotheweb.net

Creating Services with Visual Studio 2008

A Windows service is an aplication that runs in the background. Here's an example on how to make one. It's for Visual Studio 2008, but it would work with Visual Studio 2005 as well.

Create the Service


From Visual Studio Start Page, choose create project... Select Windows Service and give a name of NewService.


First we will do is add a timer to the Service. DO NOT USE THE TIMER IN THE TOOLBOX. The Timer for Windows Forms won't work for Services. We will have to the System.Timers.Timer component by right clicking on the Toolbox, and Choose Items.

Find the one that matches below:

Click OK. This will put the Timer into the Printing section of the Toolbar for some reason.

From The Service1 designer. Click on Timer1, and set the interval to 10000 (10 seconds). Then Double-click to open Code View for Timer1_Elapsed.

Add the Following Code to the Timer1_Elapsed:

Dim MyLog As New EventLog()
' Create a new event log
' Check if the Event Log Exists
If Not MyLog.SourceExists("NewService") Then
MyLog.CreateEventSource("NewService", "NewService_Log") ' Create Log
End If

MyLog.Source = "NewService" ' Write to the Log
MyLog.WriteEntry("NewService_Log", "Service is running", EventLogEntryType.Information)

End Sub

And for OnStart():

Protected Overrides Sub OnStart(ByVal args() As String)
' Add code here to start your service. This method should set things
' in motion so your service can do its work.

Timer1.Enabled = True

End Sub

Now is normally where you would hit Run to start and debug your App. This is not the case with Windows Services. We have to add a few thing called Installers to make them work correctly.

We will discuss it in the next part of this tutorial.

Linked Server SQL Syntax for SQL Server

Here they are some examples how to use linked server to execute SELECT, INSERT, UPDATE and DELETE statements. Run these statements in either Query Analyzer for SQL Server 7 / 2000 or in New Query field for SQL Server 2005 / 2008. These examples assume following conditions:

1. "MYLINKEDSERVER" as the name for a linked server that connect to PostgresSQL Database using pgsqlODBC.
2. BOOKS is a table in a schema named LIBRARY, and AUTHOR is a column in BOOKS table.

SELECT Statement

SELECT AUTHOR FROM MYLINKEDSERVER.LIBRARY.BOOKS

OR

SELECT * FROM OPENQUERY
(MYLINKEDSERVER, 'SELECT AUTHOR FROM LIBRARY.BOOKS')

INSERT Statement

INSERT INTO MYLINKEDSERVER.LIBRARY.BOOKS (AUTHOR) VALUES ('Shaun Walker')

OR

INSERT INTO OPENQUERY (MYLINKEDSERVER, 'SELECT AUTHOR FROM LIBRARY.BOOKS')
VALUES ('Shaun Walker')

UPDATE Statement

UPDATE MYLINKEDSERVER.LIBRARY.BOOKS SET AUTHOR='Bill Gates' WHERE AUTHOR='Shaun
Walker'

OR

UPDATE OPENQUERY (MYLINKEDSERVER, 'SELECT AUTHOR FROM LIBRARY.BOOKS') SET
AUTHOR='Bill Gates' WHERE AUTHOR='Shaun Walker'

DELETE Statement

DELETE MYLINKEDSERVER.LIBRARY.BOOKS WHERE AUTHOR='Shaun Walker'

OR

DELETE OPENQUERY (MYLINKEDSERVER, 'SELECT AUTHOR FROM LIBRARY.BOOKS')
WHERE AUTHOR='Shaun Walker'

NOTE:

To perform DELETE or UPDATE operations, the target table must have a unique index. For more information, please refer to the section titled UPDATE and DELETE Requirements for OLE DB Providers in the SQL Server Books Online.

How to make Linked Server between MS SQL Server 2008 64 bit and PostgreSQL 8.3

If however you had a SQL Server 2008 64-bit server and wished to create a linked server to a PostgreSQL server. You ran into 2 very annoying obstacles.

Obstacle 1:
There for a long-time was no 64-bit ODBC driver nor native driver for PostgreSQL. This obstacle was somewhat alleviated when Fuurin Kazanbai made experimental compiled 64-bit PostgreSQL ODBC drivers available which work for AMD and Intel based processors.

Obstacle 2:
All looked good in the world until you tried this in SQL Server 2005 64-bit and low and behold - you needed a 64-bit OLEDB provider for ODBC to use it in SQL Server 2005 64-bit. Yes we waited patiently for years for this piece to be available. We still love you Microsoft. Then as Jeff Crumbley pointed out - Microsoft released an OLEDB 64-bit provider for ODBC in early April 2008.

Below are the steps to get a PostgreSQL linked server working in SQL Server 2008 64-bit:

1. Run WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe - (Available as of 4/4/2008 from: http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en).
2. Make the folder C:\Program Files\PostgreSQL\8.1\AMD64bin (you can try the 8.3 if you are running that) and place the dlls from psqlodbc_AMD64 available from http://www.geocities.jp/inocchichichi/psqlodbc/index.html.
3. Run the psqlodbcwAMD64.reg file.
4. Create a System DSN in the 64-bit Data Source (ODBC).
5. Create a Linked Server in SQL Server to PostgreSQL (I'll explain it in detail below).

OK, now here are the steps to configure a Linked Server in SQL Server 2008:

1. Launch the SQL Server 2008 Management Studio.
2. Under the Server Objects folder, right click on Linked Servers and select New Linked Server.
3. Enter a name for the Linked Server and choose "Other Data Source". Do not use any spaces or special characters in the Linked Server's name.
4. Select "Microsoft OLE DB Provider for ODBC Drivers" from the Provider dropdown list. Enter MSDASQL for the Product Name.
5. In the "Data Source" field, enter the name of the ODBC System data source you wish to use. Note that you have to use exact same name as it shown in the ODBC Data Source Administrator.
6. In the Security page, select the "Be made using this security context". Enter a valid remote user/login (user ID) and password for the remote host.
7. Leave the remaining fields blank and click OK to save the linked server configuration.

Now it's time for testing our configuration. If an error occures, review the error and make changes to the linked server configuration as necessary.

In the Management Studio, expand the linked server node and then expand the Catalogs node. Click on the node for the linked server and expand the Tables to view a table list.

That's it, and you've make a Linked Server between MS SQL Server 2008 64 bit and PostgreSQL 8.3. Congratulation. In the next posting, I'll explain how to use the linked server to manipulate the data in the remote server.

Create MySQL User Account from Command Line

Follow these steps to create new user for MySQL in command line. Note this instruction assume that you have already setup MySQL.

1. Open a terminal and login to MySQL as root

mysql --user="root" --password="your_root_password"

2. Create new user and password with following command

CREATE USER 'new_username'@'localhost'
IDENTIFIED BY 'password_for_new_user';

3. Assign privileges to the new user

GRANT ALL ON *.* TO 'new_username'@'localhost';

4. Exit MySQL interface

exit

Notes:

The above instructions will create a new user account on "localhost" and grant the user all privileges. While this is safe for my development environment, you would obviously want to grant only the necessary permissions in a production environment.

For more information including the MySQL manual references, visit here.