仅当复制到服务器时才可以跳过主机文件列

Question

我想使用 bcp 从我的数据库创建一个 xml 文件。

以下代码在 SQL Server 2008 中有效，但在 SQl Express 2005 中无效。

这是代码错误： SQLState = S1000，NativeError = 0 错误 = [Microsoft][SQL Native Client] 仅当复制到服务器时才可以跳过主机文件列

这些是 Sql Express 信息： Microsoft SQL Server Management Studio Express：9.00.4035.00

Microsoft 数据访问组件 (MDAC)：2000.085.1132.00 (xpsp.080413-0852)

Microsoft MSXML：2.6 3.0 4.0 5.0 6.0

Microsoft Internet Explorer 8.0.6001.18702

Microsoft .NET Framework： 615

SO：5.1.2600

这是我的代码：

DECLARE @FileName VARCHAR(150)
DECLARE @dataExport AS DATETIME
DECLARE @param VARCHAR(8)

SET @FileName = 'c:\BackupSql\XMLOutput.xml'
SET @dataExport = '20110122'
SET @param = 'XXX'

DECLARE @SQLCmd VARCHAR(1800)

SELECT @SQLCmd = 'bcp ' + 
'"DECLARE @xml as xml; ' +
'DECLARE @text varchar(MAX); ' +             
'SET @xml = (select ' +
'(select ''' + convert(varchar(10), @dateExport, 112) + ''' as DateExport ' +
'FOR XML PATH (''DATA''), TYPE), ' + 
'(select ' +
    '(select Item as ITEM, cast(Quantity as int) as QTY, 
convert(decimal(10,6), Price) as PRICE, ''0.0'' as TOTAL, convert(varchar(10), DateInv, 112) as DATE from '+ DB_NAME() +'.dbo.ITEMS where (ITEM not like ''X-%'' and ITEM not like ''Y-%'') and DateInv = ''' + convert(varchar(10), @dateExport, 112) + ''' ' +
        'FOR XML PATH(''LINE''), TYPE) ' +
    'FOR XML PATH(''BODY''), TYPE) ' +
    'FOR XML PATH (''''), ' +
    'ROOT(''MYROOT''), TYPE); ' +
'SET @text = REPLACE(CAST(@xml AS varchar(max)), ''><'', ''>'' + CHAR(13) + CHAR(10) + ''<''); ' +
'SELECT @text" ' + 
' queryout ' +
@FileName + ' -c -T -S' + convert(varchar(128), serverproperty('servername')) --Trustedonnection e ANSI

EXECUTE master..xp_cmdshell @SQLCmd

Answer 1

我找到了一个解决方法：

1）我创建了一个返回 xml 内容的存储过程
2）我在bcp代码中执行了前面的存储过程。

示例代码：
我找到了一个解决方法：

首先创建一个返回 xml 内容的存储过程，然后在 bcp 代码中调用它。

示例代码：

-- 1) --------------------------------------------

    CREATE PROCEDURE [dbo].[XXX_TransformItemInXML]

    (     @dateExport as datetime     )
    AS
    BEGIN

    SET NOCOUNT ON

    DECLARE @xml xml;
    DECLARE @text varchar(MAX);

    SET @xml = (select
            (select convert(varchar(10), GETDATE(), 112) as DATAESPORTAZIONE
            FOR XML PATH ('DATA'), TYPE),
                (select
                    (select Item as ITEM,
                         cast(Quantity as int) as QTY,
                     convert(decimal(10,6), Price) as PRICE,
                     '0.0' as TOTAL,
                    convert(varchar(10), DateInv, 112) as DATE
                from MYDB.dbo.Items
                where (Item not like 'x-%'
                    and Item not like 'Y-%')
                and DtaInv = @dateExport
                FOR XML PATH('LINE'), TYPE)
            FOR XML PATH('BODY'), TYPE)
            FOR XML PATH (''),
            ROOT('MYROOT'), TYPE)

    SET @text = '<?xml version="1.0" encoding="ISO-8859-1"?>' + CHAR(13) + CHAR(10);

    SET @text = @text + REPLACE(CAST(@xml AS varchar(MAX)), '><', '>' + CHAR(13) + CHAR(10) + '<');

    SELECT @text

    END


-- END STORED PROCEDURE ----------------------------------------------------

-- BCP CODE ----------------------------------------------------------------

    DECLARE @fileName VARCHAR(50)

    DECLARE @sqlCmd VARCHAR(1500)

    DECLARE @dataExport

    SET @dataExport = GETDATE()

    SET @fileName = 'C:\BackupSql\Output.xml'

    select @sqlCmd = 'bcp "exec ' + DB_NAME() + '.dbo.XXX_TransformItemInXML ''' + convert(varchar(10),@dateExport, 112) + ''' " queryout ' +
        @fileName +
        ' -c -T -S' + convert(nvarchar(128), serverproperty('servername')) -- Trusted connection e ANSI

    EXECUTE master..xp_cmdshell @SQLCmd

-- END BCP CODE ------------------------------------------------------------