在 SELECT 之前在 SQL 中运行“SET"命令 [英] Running 'SET' command in SQL, prior to SELECT

问题描述

以下这个问题，我需要添加类似

Following this question, I need to add something like

SET LANGUAGE German;

在我的 SELECT 查询之前.我在 ASP/VBScript Web 环境中运行查询，据我所知，这基本上限制了我一次只能进行一个查询.如果我运行一个查询，例如 -

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.

I get a no results message, because the 'data' returned is from the SET query and not the SELECT that follows it.

有什么办法可以在 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 代码，如下所示.肯定有结果(因为 16 年 2 月 21 日 - 是在星期天，我有匹配的记录) - 但它们没有被显示.事实上，即使通过 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;

并不是真正的返回查询，但 Provider 会将其作为封闭的 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;

将禁止发送 DONE_IN_PROC 消息以表示执行行成功，ADODB 将其解释为关闭的 ADODB.Recordset 对象.

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 是使用 NextRecordSet() 方法遍历各种结果集，直到找到实际的查询结果.

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 对象

Assuming rs is our starting ADODB.Recordset object

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.

_{来自 MSDN - NextRecordset 方法 (ADO)}

只要有额外的结果并且包含复合语句的 Recordset 没有跨进程边界断开或编组，NextRecordset 方法将继续返回记录集 对象.如果行返回命令执行成功但没有返回记录，则返回的 Recordset 对象将打开但为空.通过验证 BOF 和 EOF 属性都是 是的.如果非行返回命令执行成功，返回的 Recordset 对象将被关闭，您可以通过测试 State 属性.当没有更多结果时，记录集将设置为 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.

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