bcp输出问题 [英] Problems with bcp output
本文介绍了bcp输出问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
以下代码有一些问题.此代码的主要原因是将SQL语句导出到文件中.但这是行不通的,而且我也看不到自己的错误.
I have some problems with the following code. Main reason for this code is to export the SQL statement into a file. But it doesn't work and I don't see my mistake.
DECLARE @DBName VARCHAR(5000);
DECLARE @period VARCHAR(5000);
DECLARE @SQLEXE VARCHAR(5000);
DECLARE @SearchSchema VARCHAR(5000);
SET @period = '''2017-01-01 00:00:00'' AND ''2017-12-31 23:59:59'''
SET @DBName = (SELECT name FROM master.dbo.sysdatabases where name LIKE '%NAV%');
EXECUTE ('USE [' + @DBName+']');
SET @SearchSchema = REPLACE((SELECT name FROM sys.tables where name LIKE '%$Change Log Setup'), 'Change Log Setup', 'Change Log Entry');
SET @SQLEXE = 'bcp "SELECT [Entry No_]
,[Date and Time]
,[User ID]
,[Table No_]
,[Field No_]
,[Type of Change]
,[Old Value]
,[New Value]
,[Primary Key]
,[Primary Key Field 1 No_]
,[Primary Key Field 1 Value]
,[Primary Key Field 2 Value]
,[Primary Key Field 3 No_]
,[Primary Key Field 3 Value]
,[Record ID]
FROM [dbo].[' + @SearchSchema + ']
WHERE [Date and Time] BETWEEN '+@period+'" out "C:\Users\Public\Documents\1a_EY_change_log_entry.txt" -o "C:\Users\Public\Documents\1b_EYlog_change_log_entry.log" -c -T';
Exec master..xp_cmdshell @SQLEXE;
错误消息是:
您看到我的错误了吗?
推荐答案
这可能很奇怪,但是可以使用..
This could be weird but it will work..
将整个sql放在一行而不是换行
Put the entire sql in single line instead of new lines
SET @SQLEXE = 'bcp "SELECT [Entry No_],[Date and Time],[User ID],[Table No_],[Field No_],[Type of Change],[Old Value],[New Value],[Primary Key],[Primary Key Field 1 No_],[Primary Key Field 1 Value],[Primary Key Field 2 Value],[Primary Key Field 3 No_],[Primary Key Field 3 Value],[Record ID] FROM [dbo].[' + @SearchSchema + '] WHERE [Date and Time] BETWEEN '+@period+'" out "C:\Users\Public\Documents\1a_EY_change_log_entry.txt" -o "C:\Users\Public\Documents\1b_EYlog_change_log_entry.log" -d '+quotename(@dname)+' -c -T';
此外,这将无法正常工作
Also this will not work as you are expecting
EXECUTE ('USE [' + @DBName+']');
使用bcp
-d databasename
这篇关于bcp输出问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文