BCP 生成execl表

今天有人问在ASP中，实现了一个功能，可以把SQL数据库中的某张表导出为EXCEL格式的文件，在本地调试成功了，但在有些虚拟主机空间中不能实现？他在asp 中调用SQL中的BCP命令来导出数据库中的表的。语句为：

[code:5de057c891]EXEC master..xp_cmdshell 'bcp user010_db(数据库名)..autobackdate(表名) out f:\webusr\user010\webmasterdev\backup\2004_04_15_66bk.xls(服务器上的虚拟路径）-c -q -S"XXX"(服务器名) -U"XXX"(用户名) -P"XXX"(密码)'
[/code:5de057c891]

由于BCP需要涉及一些系统资源应用，处于系统安全考虑，有些虚拟主机提供商不提供该功能的。因此建议使用者采用DTS方式传输数据或编写函数来实现生成execl表。

下面有一些BCP的介绍，有兴趣可以看看 8)

一些资料中提到BCP工具在SQL Server7的版本中不推荐使用，Bcp有局限性，首先它的界面不是图形化的，其次它只是在SQL Server的表（视图）与文本文件之间进行复制，但它的优点是性能好，开销小，占用内存少，速度快。
  
批通信程序(BCP)的输入和输出数据介绍：[/b:5de057c891]

在一般情况，源SQL Server向目的SQL Server转换时，它们的字符集和排序次序是相同的。但是，源SQL Server和目的SQL Server字符集和排序次序不一样时，采用数据转换服务系统将出错，这时可通过批通信程序(BCP)输入和输出数据。如：在SQLServer 6.5向SQLServer7.0升级的过程中， SQLServer7.0为双字节版本，可安装于Windows NT Server4.0中文版上。但是其默认的安装字符集和排序次序是：1252/ISO Character Set(Default)和Dictionary order，case-insensitive。跟中文版的SQL 6.5是不一样的。在升级的时候，没有改变这个默认的设置，结果中文数据在转化后，变成怪字符。

假设数据库为：NEWBJTEL，源SQL Server为英文，目的SQL Server为中文，CZYTAB为数据库NEWBJTEL的数据表。操作步骤如下：

1. 在源SQL Server机器上，DOS状态，运行如下命令：
c:\mssql\binn\bcp NEWBJTEL..CZYTAB OUT [/b:5de057c891]

C:\shareJxzdf\NEWBJTELDATA\CZYTAB.TXT -c -C1252 -Usa -Pas[/b:5de057c891]

对于该命令的解释：bcp.exe是一个用来将SQLServer数据拷入或拷出操作系统文件的命令行实用程序。

为了提高速度，必须在数据库中设置为：select into/bulk copy方式。该命令可以选择众多的参数，其中-c表示拷贝后的文件将以字符串的形式记录，C后面跟字符代码，英文版的代号为：1252。简体中文的代号是：936。-U后面是帐号名，P后面是密码。如果帐号名是sa，密码为空，那么格式为：-Usa -P。

另外可选的一些参数为：“-t ,”意思是在每一个字段之后用“,”来分隔，“-r \n”，意思是在每一行的末尾用“\ n”（即换行符）来分隔；其他的参数可以在在线帮助中找到。CZYTAB为数据库NEWBJTEL中的一个表格名，C:\shareJxzdf\NEWBJTELDATA\CZYTAB.TXT指定导出文件的位置。如果有多个表格，必须用多个bcp语句，建议做成一个批处理，然后一并执行。

在目的SQL Server机器上，将BCP输出的文本文件拷贝到目的SQL Server机器，在DOS状态，运行如下命令：

C:\mssql\binn\bcp NEWBJTEL..CZYTAB IN [/b:5de057c891]

C:\shareJxzdf\NEWBJTELDATA\CZYTAB.TXT -c -C936 -Usa -Pas[/b:5de057c891]

或：在Query Analyzer中打开一个新的Query窗口，然后运行下面的命令：
BULK INSERT CZYTAB FROM ' C:\shareJxzdf\NEWBJTELDATA\CZYTAB.TXT '
WITH (
CODEPAGE = 936
)
可以同时用多个命令来对多个表格进行数据恢复，只要改变表格名和相应的数据文件名即可。其中CODEPAGE = 936是指数据以“中文简体”的形式被恢复。
运行成功后的反馈信息类似：“(20 row(s) affected)“，表明20行数据被插入。


附注：
如果在对SQL Server的升级时把字符集和排序次序安装成不一样时，应按以下步骤操作：
1．先从SQL Server 7.0中备份所有数据库的SQL脚本，因为数据库中有indexes、stored procedures、triggers等需进行备份。
2．从SQL Server 7.0中备份所有数据库数据，即利用BCP.exe。
3．重建主数据库（利用Rebuildm.exe），改变字符集及排序次序等。
4．重建所有数据库，利用备份的SQL脚本。
5．再把数据导回去。





生成execl表函数

[code:5de057c891]/****************************************
' SqlServer数据库倒入Excel文件函数
'参数说明：
'XslName：Excel文件名；
'XslTable：Excel文件中的名称（表）；
'TableName：SQL数据库中的表名；
'ReportId：报表流水号
****************************************/

Function SqlToExcel(XslName, XslTable, TableName, ReportId)
’Excel文件地址
    Dim sXLS
sXLS = Server.MapPath(".") & "\excel\" & XslName & ".xls"

    ’打开一个Excel的ADO连接
    Dim oConn
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & sXLS & ";" & _
               "Extended Properties=""Excel 8.0;HDR=NO;"""
’打开Excel记录集
Dim oRs, oRsCount
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open "Select * from " & XslTable & "col", oConn, 1, 1
oRsCount = oRS.Fields.Count
oRs.Close
set oRs = Nothing

’打开Sql记录集
Dim Rs
Set Rs = Server.CreateObject("ADODB.Recordset")
    Rs.Open "Select * from " & TableName & " Where reportId = ’" & ReportId & "’ And XslTable = ’" & XslTable & "’ Order By row asc", Conn, 1, 1

Dim Addsql
Do While Not Rs.EOF
  If IsNull(Rs("col0")) Then
   Addsql = "Insert Into " & XslTable & "col" & " values (NULL"
  Else
   Addsql = "Insert Into " & XslTable & "col" & " values (" & Rs("col0") & ""
  End If
  For i = 1 To oRsCount - 1
   If IsNull(Rs("col"&i)) Then
    Addsql = Addsql & ", NULL"
   Else
    Addsql = Addsql & ", " & Rs("col"&i) & ""
   End If
  Next
  Addsql = Addsql & ")"
  response.write Addsql
  oConn.Execute Addsql
  Rs.MoveNext
Loop
’关闭记录集
Rs.Close
Set Rs = Nothing
’关闭连接
    oConn.Close
    Set oConn = Nothing
End Function
[/code:5de057c891]