blocks|key|3037014|text|我想你要找的命令是SET+NOEXEC+ON。如果您为您的连接设置了此项，查询将被解析，但不会被执行。另一个选择是SET+PARSEONLY+ON，但老实说，我不确定两者之间的真正区别是什么。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3037015|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms188394.aspx^0|9|D|1L|G|9|D|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]|$9|Q|A|R|B|C]]|D|@$9|S|A|T|1|U]]|E|$]]|$1|F|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

I think the command you are looking for is <a href="http://msdn.microsoft.com/en-us/library/ms188394.aspx" rel="noreferrer"><code>SET NOEXEC ON</code></a>. If you set this for your connection, the queries will be parsed but will not be executed. Another option would be <code>SET PARSEONLY ON</code>, but I'm honestly not sure what the difference between the two really is.

blocks|key|305091|text|SET+PARSEONLY：检查每个Transact-SQL语句的语法，并返回任何错误消息，而不编译或执行该语句。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|305092|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms178629.aspx^0|0|D|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

<a href="http://msdn.microsoft.com/en-us/library/ms178629.aspx" rel="noreferrer">SET PARSEONLY</a> : Examines the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement.

blocks|key|301970|text|使用以下查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|301971|SET+PARSEONLY+ON
--Your+query+here
SET+PARSEONLY+OFF|code-block|syntax|javascript|301972|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use the following query

<pre><code>SET PARSEONLY ON
--Your query here
SET PARSEONLY OFF
</code></pre>

blocks|key|301974|text|埃里克的答案是%2B1。但我发现SET+FMTONLY+ON也很有用，因为SET+NOEXEC+ON似乎不会抛出所有错误。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|301975|例如：|301976|SELECT+*+FROM+ATableThatDoesNotExist|code-block|syntax|javascript|301977|尽管数据库中不存在该表，但使用SET+NOEXEC+ON运行会表明它是成功的。改为使用SET+FMTONLY+ON运行它，将抛出"Invalid+object+name“错误。|301978|SET+FMTONLY+ON还返回关于将要返回的结果集的元数据，这非常方便|301979|entityMap|0|LINK|mutability|MUTABLE|url|http://technet.microsoft.com/en-us/library/ms173839.aspx^0|E|E|Z|D|0|0|0|F|D|17|E|0|0|E|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]|$9|11|A|12|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|13|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|14|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|15|8|@$9|16|A|17|B|C]|$9|18|A|19|B|C]]|D|@]|E|$]]|$1|O|3|P|5|6|7|1A|8|@]|D|@$9|1B|A|1C|1|1D]]|E|$]]|$1|Q|3|-4|5|6|7|1E|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|X]]]]

+1 to Eric's answer. But I've found <code>SET FMTONLY ON</code> to also be useful as <code>SET NOEXEC ON</code> doesn't appear to throw up all errors.

e.g.

<pre><code>SELECT * FROM ATableThatDoesNotExist
</code></pre>

Running that with <code>SET NOEXEC ON</code> says it was successful, despite the table not existing in the database. Running it with <code>SET FMTONLY ON</code> instead, will throw the "Invalid object name" error.

<a href="http://technet.microsoft.com/en-us/library/ms173839.aspx" rel="noreferrer">SET FMTONLY ON</a> also returns metadata about the resultset that would be returned, which can come in very handy

blocks|key|301981|text|实际上，这取决于测试的目的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|301982|最可靠的方法是在每次测试之后使用回滚，如果您的语句适合这样做的话(不是太重而无法使其可行)。|301983|我以前也这样做过，很高兴收到了运行时问题的通知，而这些问题是我用其他任何方式都无法捕捉到的。|301984|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Really it depends upon the purpose of the tests. 

The most reliable way would be to use the rollback after every test if your statements lend themselves to that (aren't too heavy weight to make it viable). 

I have done this in the past and have been glad to be notified of runtime issues that I wouldn't have caught any other way.

blocks|key|3251231|text|您可以通过编程方式访问VSTSDBPro的查询解析器：http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3251232|entityMap|0|LINK|mutability|MUTABLE|url|http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx^0|R|29|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

VSTSDBPro has a query parser you can access programmatically: <a href="http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx" rel="nofollow noreferrer">http://blogs.msdn.com/b/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx</a>

I'm trying to make my integration tests more idempotent. One idea was to execute rollback after every test, the other idea was to some how programatically parse the text, similar to the green check box in Query Analyzer or SSMS.

How do I get SQL Server to parse my command without running it using ADO.NET?

UPDATE: 
This is what finally worked as desired:

<pre><code>using (DbCommand executeOnly = Factory.DbCommand())
{
 executeOnly.Connection = command.Connection;
 executeOnly.CommandType = CommandType.Text;
 executeOnly.CommandText = "SET NOEXEC ON;" + sqlCommand;
 executeOnly.Connection.Open();
 executeOnly.ExecuteNonQuery();
}
//set more properties of command.
command.Execute();
</code></pre>

For inexplicable reasons, "<code>SET PARSEONLY ON</code>" only worked in Query Analyzer. I couldn't set this on an ADO.NET connection. It is just as well because <code>PARSEONLY</code> seems to catch only syntax errors, which isn't a common error. <code>SET NOEXEC ON</code> will catch a wider varieties of errors, such as a view that references a missing table or column or a missing parameter in a stored procedure.

How can I programmatically check (parse) the validity of a TSQL statement?

我正在尝试让我的集成测试更具幂等性。一个想法是在每次测试后执行回滚，另一个想法是如何以编程方式解析文本，类似于查询分析器或SSMS中的绿色复选框。如何让SQL Server在不使用ADO.NET运行命令的情况下对命令进行分析？更新：这就是最终想要的效果：using (DbCommand executeOnly = Fa...

问如何以编程方式检查(解析) TSQL语句的有效性？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何以编程方式检查(解析) TSQL语句的有效性？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何以编程方式检查(解析) TSQL语句的有效性？
EN