How-To Synchronize Data Between Excel 2007 and SQL Server 2008

Linking Excel with SQL Server with sp_addlinkedserver


You can use the sp_addlinkedserver stored procedure to create the same Linked Server Object. Here is some sample code that you can use:

CREATE PROCEDURE dbo.CreateXlsMusicLink

AS
BEGIN
DECLARE @rc int
DECLARE @servername nvarchar(128)
DECLARE @serverproduct nvarchar(128)
DECLARE @serviceprovider nvarchar(128)
DECLARE @datasource nvarchar(4000)
DECLARE @providerstring nvarchar(4000)


-- Set parameter values
SET @servername = 'xlsMusicLink'
SET @serverproduct = 'Excel'
SET @serviceprovider = 'Microsoft.Ace.OLEDB.12.0'
SET @datasource = 'c:\temp\music.xlsx'
SET @providerstring = 'Excel 12.0'

EXEC @RC = [master].[dbo].[sp_addlinkedserver] @servername, @serverproduct, @serviceprovider, @datasource, @providerstring
END
GO


I am using the “Microsoft.Ace.OLEDB.12.0” Service Provider for Excel 2007 however if you are using Excel 2003, 2002 or 95 then you should the “'Microsoft.Jet.OLEDB.4.0' “ Service Provider. Also when using Excel 2007 you must use the “Excel 12.0” Provider string otherwise use the “Excel 8.0” String for the older versions of Excel.

Using the Linked Server Object to insert Excel data from SQL Server


The first example will use the Linked Server Object “Excel_Lined” that was created with SSMS wizard. This example will fiest export a list of music titles and artist from SQL Server to Excel using the Linked Server Object. To make this example work we will need to create a table for our music list. This is a simple with these columns: Music Title, Artist. You copy and paste to following script into a SSMS Query page and execute it if you want to follow along and test the code.

Here is the code for the table that I am naming music:

USE [BISolutions]
GO

/****** Object: Table [dbo].[Music] Script Date: 09/26/2010 07:26:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Music](
[Song Title] [varchar](250) NULL,
[Artist] [varchar](150) NULL,
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Next I used the DTS to populate the table with a list of music titles from the 80s. The Excel file can be downloaded from the resources section

The Stored Procedure to Insert data into Excel from SQL Server is outlined below and can be copied and compiled in SSMS.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Kevin Languedoc
-- Create date: 9/24/2010
-- Description: Create procedure to insert data into Excel
-- =============================================
CREATE PROCEDURE dbo.InsertExcel

AS
BEGIN
insert into xlsMusicLink...[music$]([Song Title], Artist)
select [Song Title], Artist from dbo.Music

END
GO

Using this technique that your Microsoft Excel be closed and that the user have the appropriate permissions on the folder where the file is located.

More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working