忘了跟楼主说了,第一次执行xp_cmdshell的时候,SqlServer会阻止对xp_cmdshell的访问,因为此组件已作为此服务器安全配置的一部分而被关闭,楼主运行下面的代码就OK了:-- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO
EXEC master..xp_cmdshell 'BCP "select × from db.dbo.tb" queryout c:\tb.txt -c -S -T'
bcp "select * from 表名 where 条件" queryout c:\a.xls -U用户名 -P密码 -SIP地址 -n -c --将某表中符合条件的记录导出到Excel文件中
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server 不存在或访问被拒绝
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL出现这个是什么问题呢
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword' /** 导出文本文件 */
EXEC master..xp_cmdshell 'bcp dbname..tablename out c:\DT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword' /**导出到TXT文本,用逗号分开*/
exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'
BULK INSERT 库名..表名
FROM 'c:\test.txt'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
EXEC master..xp_cmdshell 'bcp mydatabase1..mytable1 out e:\DT.txt -c -S 192.168.0.202 -U sa -P sa123'
同理,你的"password"?
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO