如果实数据
bcp 数据库..表 out text数据文件 -U用户名 -P密码 -S服务器名 ....
也可以在图形界面中取出数据-->另存为如果是表结构
图形界面所有任务-->generate sql scripts...
bcp 数据库..表 out text数据文件 -U用户名 -P密码 -S服务器名 ....
也可以在图形界面中取出数据-->另存为如果是表结构
图形界面所有任务-->generate sql scripts...
EXEC master..xp_cmdshell 'bcp test.dbo.P_Aspect out c:\temp1.txt -c -q -S"servername" -U"sa" -P""'
2.use stored procedure auto bulk copy use defined table content to text files/*********************************************************************************
The following is an example of how to run this procedure in standard mode
(run under command prompt)
isql -E /dauchan -Q "sp_bcpTablesOut 'auchan', 'i:\temp\', 'SA','admin' "
-oi:\temp\bcpscript.bat /h-1 /n
isql -E starts a trusted connection in ISQL
/dauchan The datebase name where you want to store this procedure
-Q excutees the query and immediately exits ISQL
"sp_bcpTablesOut 'auchan', 'i:\temp\', 'SA','admin' " excuted by ISQL
-oi:\temp\bcpscript.bat The batch file generated by the stored procedure
/h-1 Suppresses headings
/n suppresses the prompt(>) and numbering
How to run the batch file and pipe result to a text file:
i:\temp\bcpscript,bat > i:\result.txt
***********************************************************************************/
if exists (select * from sysobjects where id =
object_id('dbo.sp_bcpTablesOut') and
sysstat & 0xf = 4)
drop procedure dbo.sp_bcpTablesOut
GO /* dbname = database name
dirname = destination directory for bcp output
username = optional
pwd = optional
*/ CREATE PROCEDURE sp_bcpTablesOut
@dbname varchar(40),
@dirname varchar(20),
@username varchar(30) = NULL,
@pwd varchar(30) = NULL
AS
set nocount off -- removes the rows affected count /* @Q1 represents single quote
@Q2 represents double quotes
@security represents security for BCP
@myquery represents file bcp batch output
*/ declare @Q1 char(1)
declare @Q2 char(1)
declare @security varchar(255)
declare @myquery varchar(255)
declare @bcpmode char(3)
declare @str varchar(400)
--select @Q1 = "'"
select @Q2 = '"'
select @bcpmode = '-c ' -- character type
--select @bcpmode = '-n ' -- native type /* checks for standard or integrated security */ IF @username IS NULL
select @security = '-T '
ELSE
select @security = '-U' + @username + ' -P' + @pwd /* formats final bcp output text that will be part of the script */ select @myquery = 'SELECT ' + @Q2 + 'bcp ' + @dbname + '..' + @Q2 + ' +
name + ' + @Q2 +
' out ' + @dirname + @Q2 + ' + name + ' + @Q2 + '.txt '+ @bcpmode +
@security +
@Q2 + ' from ' + @dbname + '..sysobjects where xtype = ' + @Q2 +
'U' + @Q2 +
' order by name '
select @str='set quoted_identifier off '+ @myquery + ' set quoted_identifier on'
-- print @str execute(@str)go