下面已调试通过,可以参靠
create procedure datatoexcel
@sql varchar(500)
as
declare @cmd varchar(500)
set @cmd = 'bcp "' +rtrim(@sql)+'" queryout d:\temp.xls -c -S ALong_Yue -U sa -P 1234567'
exec master..xp_cmdshell @cmd
Private Sub Command1_Click()
Dim sql As String
Dim cnnstr As String
Dim cnn As New ADODB.connection
Dim rst As New ADODB.Recordset
On Error GoTo errlable1
sql = "select top 20 * from tfmsdb.dbo.leave_card"
cnnstr = " Provider = SQLOLEDB.1;Password=1234567;Persist Security Info=True;User ID=sa;Initial Catalog=zydb;Data Source=ALong_Yue "
cnn.open cnnstr
Set rst = cnn.execute("exec datatoexcel '" + sql + "'")
Exit Sub
errlable1:
MsgBox Err.Description
Err.Clear
End Sub
create procedure datatoexcel
@sql varchar(500)
as
declare @cmd varchar(500)
set @cmd = 'bcp "' +rtrim(@sql)+'" queryout d:\temp.xls -c -S you_servername -U sa -P you_password'
exec master..xp_cmdshell @cmd
调用:
dim sql as string
dim cnnstr as string
dim cnn as adodb.connection
dim rst as adodb.recordset
sql = "select top 10 * from you_databasename.dbo.you_tablename"
cnnstr = "..."
cnn.open cnnstr
st rst = cnn.execute("exec datatoexcel '"+sql+"'")
注:
bcp(批量copy)功能一览表