将 sqlcmd 与 FOR XML 一起使用会返回无效的对象名称“dbo.Table"，但查询在 SSMS 中运行 [英] Using sqlcmd with FOR XML returns Invalid Object name 'dbo.Table' yet Query runs in SSMS

问题描述

这是我的查询:

SELECT title1, 
    [precinct percent] AS [PrecinctPercent], 
    leader, 
    [leader percent] AS [LeaderPercent], 
    Winner, 
    WinningVotes, 
    leader2, 
    [leader2 percent] AS [Leader2Percent], 
    Loser, 
    LosingVotes 
    FROM [leader].dbo.[RACE]  r inner join
     (select rc.[race number],
             max(case when seqnum = 1 then [candidate num] end) as Winner,
             max(case when seqnum = 1 then Votes end) as WinningVotes,
             max(case when seqnum = 2 then [candidate num] end) as Loser,
             max(case when seqnum = 2 then Votes end) as LosingVotes
      from (select rc.*,
                   row_number() over (partition by rc.[race number] order by votes desc) as seqnum
            from [leader].dbo.[RACE CANDIDATES] rc
           ) rc
      group by rc.[race number]
     ) rc
     on r.[race number] = rc.[race number]
     FOR XML PATH ('WQAD'), ROOT('root')

这个查询运行并产生一些我想要的结果.我正在尝试将 XML 文件作为文件输出.我已经能够通过在 Server Management Studio 中打开 xml，然后运行 ​​VB 脚本来重新调整临时 xml 文件，然后将该 xml 移动到它的目的地来完成此操作.嘿...我必须做我必须做的事情才能在 AIR 上获得这些结果.

This query runs and produces some results I desire. I am trying to get the XML file to output as a file. I have been able to accomplish this by opening the xml in Server Management Studio, then running a VB Script to remane the temporary xml file, then move that xml to it's destination. Hey...I gotta do what I gotta do to get these results ON AIR.

通过我的旅行，我尝试使用 sqlcmd 完成此操作.这是我想要运行的:

Through my travels I have tried to accomlish this using sqlcmd. here is what I am trying to run:

sqlcmd -S WQAD-SEVE\SQLEXPRESS -i C:\Users\localtv\Desktop\QUERIES\THISONE22 .sql -o C:\Users\localtv\Desktop\RESULTS236.xml

它会执行，但是当我打开/编辑它输出的 XML 文件时，我得到:

It executes, but when I open/edit the XML file it outputs, I get:

消息 208，级别 16，状态 1，服务器 WQAD-SEVE\SQLEXPRESS，第 1 行无效的对象名称dbo.RACE".

Msg 208, Level 16, State 1, Server WQAD-SEVE\SQLEXPRESS, Line 1 Invalid object name 'dbo.RACE'.

我尝试插入 [database].dbo.RACE，但仍然遇到同样的问题.我已经为此工作数周了，并且正在努力想办法让它发挥作用.

I have tried inserting [database].dbo.RACE, but still run into the same issue. I have been working on this for weeks now and am trying to figure out a way to get this to work.

此外，当我尝试在没有 -o 输出目标的情况下运行命令时，命令行会显示相同的错误消息.从我的查询来看，它在遇到 dbo.RACE 之前通过了相当多的时间.

Also, when I try to run the command without the -o output destination, the command line echos the same error message. From the look of my query, it makes it through quite a bit before it encounter dbo.RACE.

有什么建议吗?

预先感谢您提供宝贵的帮助.

Thank you in advance for your valued assistance.

***我已经删除了 [databasename] 和 .dbo.同样，仍然将 RACE 显示为无效对象.

***I have removed the [databasename] and .dbo. as well, still Shows RACE as an invalid object.

推荐答案

正如您所发现且我确认的那样，您需要指定 -d MyDatabaseName 选项.

As you've discovered and I confirmed, that you need to specify the -d MyDatabaseName option.

否则，一旦您使用 FOR XML PATH，您就会收到 invalid object name "Table" 错误，即使您在脚本中添加了数据库名称FROM 子句(即，即使 FROM Database.dbo.Table 也不会绕过 -d 与 FOR XML AUTO).奇怪.

Otherwise, you'll get the invalid object name "Table" error as soon as you use FOR XML PATH, even if you add the database name in your script FROM clause (viz. even FROM Database.dbo.Table won't circumvent the need for -d with FOR XML AUTO). Bizarre.

回复:它不会踢出格式正确的 xml 文件:

您需要添加 :XML ON 作为 sqlcmd 的输入 (-i) 文件中的第一行:

You need to add the :XML ON as the first line in your input (-i) file to sqlcmd:

:XML ON
SELECT title1,
 ...

这篇关于将 sqlcmd 与 FOR XML 一起使用会返回无效的对象名称“dbo.Table"，但查询在 SSMS 中运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！