匯出 sqlsever 到access

SE [JjwDB]GO/****** Object:  StoredProcedure [dbo].[p_exporttb]    Script Date: 06/09/2013 14:12:07 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*--資料匯出Access匯出資料庫中的表到Access如果檔案不存在,將自動建立檔案如果表存在,將覆蓋現有的表支援匯出所有的資料類型--鄒建 2004.04(引用請保留此資訊)--*//*--調用樣本p_exporttb @s_tbname='sysobjects',@path='c:\',@fname='test.mdb'--*/ALTER proc [dbo].[p_exporttb]@s_tbname sysname,          --要匯出的表名@path nvarchar(1000),       --檔案存放目錄@fname nvarchar(250),       --檔案名稱@tbname varchar(250)='',    --要建立的表名,預設為匯出的表名@password varchar(20)='',   --ACCESS資料庫的密碼@s_user sysname='',         --如果提示串連錯誤,則需要提供登陸sql伺服器的使用者名稱及密碼@s_password varchar(50)=''as set nocount ondeclare @err int,@src nvarchar(255),@desc nvarchar(255),@out intdeclare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)--參數檢測if isnull(@fname,'')=''set @fname='temp.mdb'if isnull(@tbname,'')='' set @tbname=@s_tbname--檢查檔案是否已經存在if right(@path,1)<>'\' set @path=@path+'\'set @sql=@path+@fnameexec master..xp_fileexist @sql,@err out--資料庫建立select @sql=@path+@fname,@constr='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@sql+case isnull(@password,'') when '' then '' else ';Jet OLEDB:Database Password='+@password endif @err=0beginexec @err=sp_oacreate 'ADOX.Catalog',@obj outif @err<>0 goto lberrexec @err=sp_oamethod @obj,'Create',@out out,@constrif @err<>0 goto lberrexec @err=sp_oadestroy @objif @err<>0 goto lberrend--串連資料庫exec @err=sp_oacreate 'adodb.connection',@obj outif @err<>0 goto lberrexec @err=sp_oamethod @obj,'open',null,@constrif @err<>0 goto lberr--刪除現有的表set @sql='drop table ['+@tbname+']'exec @err=sp_oamethod @obj,'execute',@out out,@sql--if @err<>0 goto lberr--匯入表if isnull(@s_user,'')=''set @sql='SELECT * into ['+@tbname+'] FROM [ODBC;Driver=SQL Server;Server='+ @@servername+';database='+db_name()+'].['+@s_tbname+']'elseset @sql='SELECT * into ['+@tbname+'] FROM [ODBC;Driver=SQL Server;Server='+ @@servername+';Uid='+@s_user+';Pwd='+isnull(@s_password,'')+';database='+db_name()+'].['+@s_tbname+']'exec @err=sp_oamethod @obj,'execute',@out out,@sqlif @err<>0 goto lberrexec @err=sp_oamethod @obj,'close',nullif @err<>0 goto lberrexec @err=sp_oadestroy @objreturnlberr:exec sp_oageterrorinfo 0,@src out,@desc outif @obj<>0 exec @err=sp_oadestroy @objlbexit:select cast(@err as varbinary(4)) as 錯誤號碼,@src as 錯誤源,@desc as 錯誤描述select @sql,@constr,@fdlist