import excel

need some help with getting this stored procedure working.
I'm trying to import excel spreadsheet into an existing table
getting error message of syntax near "*"
===========================================
CREATE PROCEDURE [dbo].importExcel
AS
--Create linked server
EXEC sp_addlinkedserver 'ExcelSource', 'Jet
4.0','Microsoft.Jet.OLEDB.4.0','C:\temp\tables.xls ', NULL,'Excel 5.0'
GO
--login to linked server
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
-- import spreadsheet data into database table...
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
DECLARE @.SQLString1 VARCHAR(100)
SET @.SQLString1 = 'SELECT * FROM ExcelSource...[table$]'
DECLARE @.SQLString2 VARCHAR(100)
SET @.SQLString2 = 'INSERT INTO table ' + @.SQLString1
-- convert variable from VARCHAR to NVARCHAR
DECLARE @.S2 NVARCHAR(1000)
SET @.S2 = CAST(@.SQLString2 as NVarchar(1000))
EXECUTE sp_executesql @.S2
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
GO
hi,
"TJS" <nospam@.here.com> ha scritto nel messaggio
news:10qlms38norp6e3@.corp.supernews.com
> need some help with getting this stored procedure working.
> I'm trying to import excel spreadsheet into an existing table
> getting error message of syntax near "*"
> SET @.SQLString1 = 'SELECT * FROM ExcelSource...[table$]'
what is the name of the Excel sheet?
usually it defaults to Sheet1$, so the correct syntax is
SET @.SQLString1 = 'SELECT * FROM ExcelSource...[Sheet1$]'
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||the name is correct , I am only testing at this time.
I did remove the extra GO statements and that seems to have resolved the
error messages.
I now have to find an ASP file to dynamically generate the sql statements
for each spreadsheet table and pass them into the stored procedure because
the live database tables have primary keys. That unfortunately requires
using column lists...
I can't believe somebody hasn't already done all this ?
sql