在运行SQL“SET”命令来选择前 [英] Running 'SET' command in SQL, prior to SELECT

问题描述

随着<一个href=\"http://stackoverflow.com/questions/35720172/sql-select-works-in-management-studio-but-not-from-website/35744691#35744691\">this问题，我需要添加类似

SET LANGUAGE German;

在我的 SELECT 查询。
我正在运行的ASP / VBScript中的网络环境下，这基本上限制了我，据我所知，在单个查询，一次查询。
如果我运行一个查询，如 -

before my SELECT query. I am running the query in an ASP/VBScript web environment, which basically limits me, as far as I know, to a single query at a time. If I run a query such as -

SET LANGUAGE German; SELECT.....

我得到一个没有结果的消息，是因为数据返回从 SET 查询，而不是 SELECT 它后面。

有没有什么可以做的ASP / VBScript的环境中共同运行 SET 和 SELECT ？

Is there anything that can be done to run the SET and the SELECT together in the ASP/VBScript environment?

更新：
按Lankymarts建议：

UPDATE: As per Lankymarts suggestion:

set rs = SERVER.CreateObject("ADODB.recordset")
rs.open sql, conn, 1, 2

Do While (rs.State = 0 Or rs Is Not Nothing)  // also tried: Do While (rs.State = 0 AND rs Is Not Nothing) 
 Set rs = rs.NextRecordset
Loop

do while not rs.eof
  response.write ...

更新2：结果
既然封闭式记录的问题解决了，我还没有从主记录让行。
这是我的VBScript code，下文。
有肯定的结果（因为21 /月/ 16 - 已于上周日，我有匹配的记录本） - 但他们没有被显示。事实上，即使通过SSMS显示有时我不明白的结果 - 也许它让所有的困惑与语言的变化

UPDATE 2:
Now that the closed recordset issue is solved, I am still not getting rows from the main recordset. This is my VBScript code, below. There are definitely results (because 21/feb/16 - was on Sunday, and i have matching records for this) - but they are not being displayed. In fact even displaying via SSMS sometimes i dont get the results - maybe its getting all confused with the language changes?

    sql = " SET LANGUAGE German; "
    sql = sql & " SELECT [tblstudentrakazot].studentid, firstname, lastname, tblRakezetSchedule.* FROM tblRakezetSchedule"
    sql = sql & " INNER join [tblstudentrakazot] on [tblstudentrakazot].scheduleID = tblRakezetSchedule.scheduleid "
    sql = sql & " INNER join tblstudents on [tblstudentrakazot].studentid = tblstudents.studentid"
    sql = sql & " WHERE CONVERT(int,scheduleday) = datepart(d,convert(datetime,'" & cleanSQL(planneddate) & "',103)) AND "
    sql = sql & " tblRakezetSchedule.rakezetID = " & CleanSQL(x_rakezetID)
    sql = sql & " ORDER BY replace(scheduletimefrom, ':', '')"

    response.Write("### " & sql)
    set rs = SERVER.CreateObject("ADODB.recordset")
    rs.open sql, conn, 1, 2

    Do While rs.State = 0 And Not rs Is Nothing
     Set rs = rs.NextRecordset
    loop 


    do while not rs.eof 
       ' we now proceed to loop through the actual result recordset : studentid, firstname etc... 

顺便说一句 - 它的语言留在德国的查询运行后呢，还是恢复到默认语言

By the way - does the language remain in German after the query has run, or does it return to its default language?

我想我需要在这里是一个语言设置缺省为DD / MM / YYYY（因为系统中的其他标准的要求），另一个在 DATEFIRST 是周日（1）。

I guess what i need here is a language setting whose default is dd/mm/yyyy (because of other legacy requirements in the system) and one that the DATEFIRST is Sunday (1).

同时
我试图让一个存储过程，因为这样的：

ALSO: I tried to make a stored procedure, as such:

    ALTER PROCEDURE [dbo].[procListRakezetSlotsByDay] @planneddate nvarchar(10),  @rakezetID int

    AS
    BEGIN

        SET NOCOUNT ON;
        SET LANGUAGE German;

    SELECT [tblstudentrakazot].studentid, firstname, lastname, tblRakezetSchedule.* FROM tblRakezetSchedule
    INNER join [tblstudentrakazot] on [tblstudentrakazot].scheduleID = tblRakezetSchedule.scheduleid 
    INNER join tblstudents on [tblstudentrakazot].studentid = tblstudents.studentid
    WHERE CONVERT(int,scheduleday) = datepart(d,convert(datetime,@planneddate,103)) AND tblRakezetSchedule.rakezetID = @rakezetID
    ORDER BY replace(scheduletimefrom, ':', '')


    END

，然后运行它：

    DECLARE @return_value int

    EXEC    @return_value = [dbo].[procListRakezetSlotsByDay]
            @planneddate = N'28/2/2016',
            @rakezetID = 182

    SELECT  'Return Value' = @return_value

    GO

和在这里，没有返回任何结果 - 即使在SSMS ...
我很迷茫。感谢所有到目前为止谁都有帮助。

and here too, it returns no results - even within SSMS... I am VERY confused. thanks to all who have helped so far.

推荐答案

这是一种误解。无论是ASP / VBScript中限制了你，极限是由ADODB用来执行该命令的供应商强加的。在SQL Server而言，虽然没有限制的（我知道）的执行包含多个查询命令时。

This is a misconception. Neither ASP/VBScript limits you, the limit is imposed by the provider ADODB uses to perform the command. In terms of SQL Server though there is no limit (I know of) when executing a command that contains multiple queries.

首先

SET LANGUAGE German;

是不是一个真正的返回的查询，但该供应商将返回它作为一个封闭 ADODB.Recordset 对象，这是不理想，但有一个简单的解决。

isn't really a returning query but the Provider will return it as a closed ADODB.Recordset object, which isn't ideal but there is a simple fix.

SET NOCOUNT ON;

会抑制被发送到说正在执行的路线是成功的这是由ADODB PTED作为一个封闭的 ADODB间$ P $ DONE_IN_PROC 消息。记录对象。

Will inhibit DONE_IN_PROC messages from being sent to say the executing line was successful which is interpreted by ADODB as a closed ADODB.Recordset object.

另一种方式来处理这个但不能作为直接的作为的 SET NOCOUNT ON 是使用<一个href=\"https://msdn.microsoft.com/en-us/library/ms677539%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396\"相对=nofollow> NextRecordSet（） 的 ADODB.Recordset 对象以逐步的方法不同的结果集，直到找到实际的查询结果。

Another way to deal with this but not as straight-forward as SET NOCOUNT ON is to use the NextRecordSet() method of the ADODB.Recordset object to step through the various resultsets until you find the actual query result.

假设 RS 是我们的出发 ADODB.Recordset 对象

Do While (rs.State = adStateClosed And Not rs Is Nothing)
 Set rs = rs.NextRecordset
Loop

将返回第一个 ADODB.Recordset 对象不是处于关闭状态。

will return the first ADODB.Recordset object that isn't in the closed state.

<子>从<一个href=\"https://msdn.microsoft.com/en-us/library/ms677539%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396\"相对=nofollow> MSDN - NextRecordset方法（ADO）

的只要有其他的结果，并在记录包含复合语句未断开或跨进程边界封送，在 NextRecordset 方式将继续返回记录的对象。如果行返回命令成功执行，但不返回任何记录，则返回的记录的对象将是开放的，但空。试验对于这种情况通过验证转炉和 EOF 这两种属性，真。如果非行返回命令成功执行，则返回的记录对象将被关闭，您可以通过测试的国家财产上的记录即可。当没有更多结果，记录将被设置为Nothing。的

As long as there are additional results and the Recordset containing the compound statements is not disconnected or marshaled across process boundaries, the NextRecordset method will continue to return Recordset objects. If a row-returning command executes successfully but returns no records, the returned Recordset object will be open but empty. Test for this case by verifying that the BOF and EOF properties are both True. If a non–row-returning command executes successfully, the returned Recordset object will be closed, which you can verify by testing the State property on the Recordset. When there are no more results, recordset will be set to Nothing.

这篇关于在运行SQL“SET”命令来选择前的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！