System SP for Linking Excel file
-
I'm trying to use a system SP for linking an Excel file to my Database... seems it bombs at the first DECLARE statement... I am very new to importing Excel into SQL Server and do not have access to SSIS unfortunately... any ideas? Thanks!
USE [TrackIT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [sys].[sp_addlinkedserver]
DECLARE @rc int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'C:\Users\DCAMPB\Desktop\Weekly Talent Management Report 10 22 12.xls'
SET @provstr = 'Excel 8.0'
EXEC @rc = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog
-
lots of things wrong here, let me point out a few, and then a script on how to create a linked server.
1. you cannot create objects in the sys. schema
2. you cannot modify system procedures or functions...create your own isntead.
3. a procedure needs an AS declaration to show where the code starts(CREATE PROC @PARAM AS {code})
4. If you are running SQL2008, the JET Driver doesn't work with 64 bit; you have to install theACE drivers and use those instead.
finally, here's a tested example:
--Required settings for the provider to work correctly as a linked server
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
--#################################################################################################
--Linked server Syntax for Excel with ACE 64 driver
--#################################################################################################
DECLARE @server sysname,
@srvproduct nvarchar(256),
@provider nvarchar(256),
@datasrc nvarchar(100),
@location nvarchar(100),
@provstr nvarchar(100),
@catalog sysname,
@sql varchar(1000)
--add an excel spreadsheet as a linked server.
SET @server = 'MyExcelACE'
SET @srvproduct = ''
SET @provider = 'Microsoft.ACE.OLEDB.12.0'
set @provstr = 'Excel 12.0'
SET @datasrc ='C:\Data\BlockGroups_2010\AKblockgroup.xls'
EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr
EXEC dbo.sp_AddLinkedSrvLogin @server, FALSE, NULL, Admin, NULL
--what spreadsheets (table equivilents are available?
EXEC sp_tables_ex 'MyExcelACE'
--you MUST know the name of the spreadsheet;
--spreadsheet name has a dollar sign at the end of it!
--I've personally never gor a spreadsheet that has a space in it
--for example "Activity Data" =ActivityData$ never got [Activity Data$] or [Activity Data]$ to work
--to work, so I end up editing the spreadsheet to remove spaces if that happens.
briancampbellmcad (10/29/2012)
I'm trying to use a system SP for linking an Excel file to my Database... seems it bombs at the first DECLARE statement... I am very new to importing Excel into SQL Server and do not have access to SSIS unfortunately... any ideas? Thanks!USE [TrackIT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [sys].[sp_addlinkedserver]
DECLARE @rc int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'C:\Users\DCAMPB\Desktop\Weekly Talent Management Report 10 22 12.xls'
SET @provstr = 'Excel 8.0'
EXEC @rc = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog
and here's teh code example, parameterized out as a procedure...all you can provide is the name of the linked server, and the path to the excel spreadsheet:
Create Procedure dbo.addMyExcelLinkedServer
@server sysname,
@datasrc nvarchar(100)
AS
DECLARE
@srvproduct nvarchar(256),
@provider nvarchar(256),
@location nvarchar(100),
@provstr nvarchar(100),
@catalog sysname,
@sql varchar(1000)
--add an excel spreadsheet as a linked server.
SET @server = 'MyExcelACE'
SET @srvproduct = ''
SET @provider = 'Microsoft.ACE.OLEDB.12.0'
set @provstr = 'Excel 12.0'
SET @datasrc ='C:\Data\BlockGroups_2010\AKblockgroup.xls'
EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr
EXEC dbo.sp_AddLinkedSrvLogin @server, FALSE, NULL, Admin, NULL
--what spreadsheets (table equivilents are available?
EXEC sp_tables_ex 'MyExcelACE'
--you MUST know the name of the spreadsheet;
--spreadsheet name has a dollar sign at the end of it!
--I've personally never gor a spreadsheet that has a space in it
--for example "Activity Data" =ActivityData$ never got [Activity Data$] or [Activity Data]$ to work
--to work, so I end up editing the spreadsheet to remove spaces if that happens.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
also, for the prerequisites:
install the AccessDatabaseEngine_x64.exe from microsoft:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive
command line flag;
this will force the install of the drivers, even if you have 32 bit office installed;
otherwise you get some error about 32 bit Office preventing the install.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
A little outside my experience... I open cmd and see "Administrator: C:\WINDOWS\system32\cmd.exe" as the header of my command prompt... not sure what and where to type in my command which is coming up as C:\users\DBCAMP>"
-
briancampbellmcad (10/29/2012)
A little outside my experience... I open cmd and see "Administrator: C:\WINDOWS\system32\cmd.exe" as the header of my command prompt... not sure what and where to type in my command which is coming up as C:\users\DBCAMP>"well, probably the easiest way is via a GUI shortcut, isntead of browsing to the "right" folder via command line.
1. try the default way:
Find wherever your web browser downloaded the file (it might be c:\users\DBCamp\Downloads for example)
2. double click it, you'll probably get prompted with run As Administrator...let it try to install.
3. If it raises an error, like i mentioned previously, you need to use the /passive switch...you can simply right click on the executable , choose create shortcut...
4. now right click on the newly creeated shortcut, go to properties...
5. On the first text box ("Target") simply add the flag /passive, save it,
6. click the newly edited shortcut.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply