LOGINREC *login;
DBPROCESS *dbproc;
DBINT rowsread;// Install error-handler and message-handler.
dberrhandle(err_handler);
dbmsghandle(msg_handler);// Open a DBPROCESS structure.
login = dblogin();
DBSETLUSER(login, "user");
DBSETLPWD(login, "my_passwd");
DBSETLAPP(login, "example");
BCP_SETL(login, TRUE);
dbproc = dbopen(login, "my_server");// Initialize bulk copy.
if (bcp_init(dbproc, "pubs..authors", "authors.xls",
(BYTE *)NULL, DB_IN) == FAIL)
exit(ERREXIT);// Now, execute the bulk copy.
if (bcp_exec(dbproc, &rowsread) == FAIL)
printf("Incomplete bulk copy. Only %ld row%s copied.\n",
rowsread, (rowsread == 1) ? "": "s");
DBPROCESS *dbproc;
DBINT rowsread;// Install error-handler and message-handler.
dberrhandle(err_handler);
dbmsghandle(msg_handler);// Open a DBPROCESS structure.
login = dblogin();
DBSETLUSER(login, "user");
DBSETLPWD(login, "my_passwd");
DBSETLAPP(login, "example");
BCP_SETL(login, TRUE);
dbproc = dbopen(login, "my_server");// Initialize bulk copy.
if (bcp_init(dbproc, "pubs..authors", "authors.xls",
(BYTE *)NULL, DB_IN) == FAIL)
exit(ERREXIT);// Now, execute the bulk copy.
if (bcp_exec(dbproc, &rowsread) == FAIL)
printf("Incomplete bulk copy. Only %ld row%s copied.\n",
rowsread, (rowsread == 1) ? "": "s");
LOGINREC *login;
DBPROCESS *dbproc;
DBINT rowsread是预制的对象吗?还有你没有指定Excel中的Sheet的名称,也就是表的名称,
是不是有什么就拷贝什么?因为我的Excel中有30个表。
在EXCEL里写VBA,调用SQLDMO,把数据写入SQL数据库里.
IF EXISTS (SELECT * FROM master..sysobjects WHERE id = OBJECT_ID(N'sp_seiucc_importxlsfile'))
DROP PROCEDURE sp_seiucc_importxlsfile
GO
--Create the procedure
CREATE PROCEDURE sp_seiucc_importxlsfile
@campaignID char(3),
@fileName char(50),
@fileNumber tinyint
ASDECLARE @sql char(1000)SET @sql = 'EXEC master..sp_addlinkedserver @server=''EXCELFILE'',@srvproduct=''Jet 4'',@provider=''Microsoft.Jet.OLEDB.4.0'',@provstr=''Excel 8.0'',@datasrc=''C:\Campaigns\' + @campaignID + '\original\' + RTRIM(@fileName) + ''''
EXEC (@sql)
EXEC master..sp_addlinkedsrvlogin EXCELFILE, FALSE, NULL, Admin, NULLCREATE TABLE #table_name (
table_cat sysname null,
table_schem sysname null,
table_name sysname,
table_type varchar(32),
res varchar(254) null
)INSERT #table_name EXEC sp_tables_ex EXCELFILEDECLARE @j int
SET @j = 0
DECLARE @table_name sysname
DECLARE cur_table_name CURSOR FOR
SELECT table_name FROM #table_name WHERE table_type = 'table'FETCH NEXT FROM cur_table_name INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@j = 0)
BEGIN
SET @sql = 'SELECT * INTO [CAMPAIGN_' +@campaignID + '].dbo.[CAMPAIGN_' + @campaignID + '_landing_' + CONVERT(CHAR(1),@j) + '] FROM EXCELFILE...[' + RTRIM(@table_name) + '] WHERE 1=0'
EXEC (@sql)
END
SET @sql = 'INSERT INTO [CAMPAIGN_' +@campaignID + '].dbo.[CAMPAIGN_' + @campaignID + '_landing_' + CONVERT(CHAR(1),@j) + '] SELECT * FROM EXCELFILE...[' + RTRIM(@table_name) + ']'
EXEC (@sql)
SET @j = @j + 1
FETCH NEXT FROM cur_table_name INTO @table_name
ENDCLOSE cur_table_name
DEALLOCATE cur_table_nameDROP TABLE #table_name
EXEC master..sp_droplinkedsrvlogin EXCELFILE, NULL
EXEC master..sp_dropserver EXCELFILE
@srvproduct=''Jet 4'',@provider=''Microsoft.Jet.OLEDB.4.0''这个引擎,怎么办?
@srvproduct=''Jet 4'',@provider=''Microsoft.Jet.OLEDB.4.0''这个引擎,怎么办?
OLE DB 提供程序 'Microsoft.Jet.OLEDB.4.0' 报错。提供程序未给出有关错误的任何信息。
什么意思??
然后通过SQL的导入工具,在源数据列表中选择ODBC,再导入数据
EXEC (@sql)报错:
查询要求为连接设置 ANSI_NULLS 和 ANSI_WARNINGS 选项。这将确保一致的查询语义。请启用这些选项,然后重新发出查询。
我设置了
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
还是不行。请执教!!!