September 21, 2010

Synchronize data between MS Access and SQL Server database using Linked Server

I used linked server to export data from an access database to Sql server database at regular interval. I feel it is much easier rather than creating a windows service/scheduled job.

  1. Add a linked server
    EXEC sp_addlinkedserver
    @server = N'AccessDB',
    @provider = N'Microsoft.Jet.OLEDB.4.0',
    @srvproduct = N'OLE DB Provider for Jet',
    @datasrc = N'C:\SourceData.mdb'
  2. Set up login
    EXEC sp_addlinkedsrvlogin

    @rmtsrvname = N'AccessDB',
    @useself = N'False',
    @rmtuser = N'Admin',
    @rmtpassword = ''
  3. Now you can exceute sql statement with this access database.
    Eg: simple select statement:- SELECT * FROM OPENQUERY(AccessDB,  'SELECT * FROM Order')In my case I wrote an insert statement in side sql job with scheduled time interval.

September 20, 2010

Web survey

WebSurvey is an ASP.NET server control that helps developers quickly and easily generated web-based surveys from an XML file

Creating a Windows Service Application

Just thought of sharing a very nice article, explaining how to create a windows service using visual studio
http://msdn.microsoft.com/en-us/library/zt39148a.aspx

Admin privilege is required to follow all the steps in the article.