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

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 ?

hi,
"TJS" <nospam@here.com> ha scritto nel messaggio
news:10qlms38norp6e3@corp.supernews.com
[quoted text, click to view]

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.shtm http://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