存储过程和c# SqlDataReader返回的行比预期的要少

I have been asked to migrate a website application from one server to another. The code was written by previous developer.

我被要求将一个网站应用程序从一个服务器迁移到另一个服务器。代码是由以前的开发人员编写的。

He created a stored procedure with the following: [Parameters]

他创建了一个存储过程，其中包含以下内容:

            @SurveyPeriodConfigID int = -1,
            @StartDate DATETIME = NULL,
            @EndDate DATETIME = NULL,
            @StartTime DATETIME = [9:00:00],
            @EndTime DATETIME = [19:00:00],
            @Category INT = -1,
            @SubcategoryID INT = -1,
            @ReportType VARCHAR(50) = null,
            @DepartmentID INT = -1,
            @FloorID INT = -1,
            @RoomID INT = -1,
            @IsLazyLoad BIT = 0,
            @Day INT = -1

and [WHERE caluse]:

和[caluse]:

            SELECT [fields]
            FROM [tables]
            WHERE 
            (DATEPART(dw, rsdm.DateCreated)) != 7 AND (DATEPART(dw, rsdm.DateCreated)) !=1
                    AND rsdm.DateCreated BETWEEN @StartDate AND @EndDate
                    AND (CONVERT(VARCHAR(8),rsdm.DateCreated,108) BETWEEN @StartTime AND @EndTime)
                    AND (@Category = -1 OR rsdm.CategoryCodeID = @Category)
                    AND (@SubcategoryID = -1 OR rsdm.SubcategoryID = @SubcategoryID)
                    AND (@DepartmentID =-1 OR rsdm.DepartmentID = @DepartmentID)
                    AND (@FloorID =-1 OR rsdm.FloorID = @FloorID)
                    AND (@RoomID =-1 OR rsdm.RoomID = @RoomID)
                    AND (@Day =-1  OR  DATEPART(dw, rsdm.DateCreated) = @Day)

C# Code:

c#代码:

            SqlDataReader sqlDataReader = thisCommand.ExecuteReader(CommandBehavior.CloseConnection);

...calls the stored procedure and returns SqlDataReader.

…调用存储过程并返回SqlDataReader。

So far so good.

目前为止一切都很顺利。

Now the issue... previously the old site returned 8200 rows but now the new website returns 6500 rows!!!

现在这个问题…以前老站点返回8200行，现在新站点返回6500行!!

I setup the code and website on my PC on my copy of sql database... it returns correct value of 8200. I then changed web.config to point to new website sql server... 6500 rows!!!

我在我的PC上安装了sql数据库的拷贝的代码和网站……它返回正确的值8200。然后我改变了网络。配置指向新的网站sql server…6500行! ! !

I backed up live site sql database and restored said same database locally on my PC and tested it... 8200 rows!!!

我备份了实时站点sql数据库，并在我的PC上恢复了相同的数据库并进行了测试……8200行! ! !

Old site had Sql server 2005, my PC has developer SQL 2012 whilst live server has SQL 2012.

旧网站有Sql server 2005，我的PC有开发人员Sql 2012，而live server有Sql 2012。

I then ran sql profiler on new site Sql Server, extracted script and ran script on same sql database 8200 records!!!

然后我在新的站点sql Server上运行sql分析器，提取脚本并在同一个sql数据库8200记录上运行脚本!!

I've spent the past 1-2 days on this and still no idea why live Sql Server return 6500 rows via sqldatareader.

在过去的1-2天里，我一直不知道为什么live Sql Server通过sqldatareader返回6500行。

Any suggestions would be greatly appreciated.

如有任何建议，将不胜感激。

4 个解决方案

#1

When you connect to the database, there is always a login account associated with it. Are you using a different login for your app than you use when connecting with SQL Server Management Studio? Date formats are based on the default language for your logins.

当您连接到数据库时，总是有一个与之关联的登录帐户。您的应用程序使用的登录名是否与连接SQL Server Management Studio时使用的登录名不同?日期格式基于您登录的默认语言。

Please run the following query:

请运行以下查询:

Select  syslogins.name, syslogins.language, syslanguages.dateformat
From    sys.syslogins
        Inner Join sys.syslanguages
            On syslogins.language = syslanguages.name

Check the dateformat column for all the logins returned. If the dateformat is different, then this probably explains why you are getting different results when running from your app vs. running from SSMS.

检查dateformat列中返回的所有登录。如果dateformat是不同的，那么这可能解释了为什么从应用程序运行与从SSMS运行时得到的结果不同。

If this truly is the cause of your problems, then you can change the default language for your logins by following the directions in this blog: http://blogs.lessthandot.com/index.php/datamgmt/datadesign/setting-a-standard-dateformat-for-sql-se/

如果这确实是导致问题的原因，那么您可以按照本文中的说明更改登录的默认语言:http://blogs.lessthandot.com/index.php/datamgmt/datadesign/setting-astandard -dateformat-for sql-se/

#2

I would check if options are configured differently on the different servers, in particular ANSI_NULLS (see script below). Note that when you run a script in SSMS, the default options are likely different to those in effect when using ADO.NET (see SSMS Tools / Options / Query Execution / SQL Server / ANSI). This often explains differences in behavior between SSMS and ADO.NET.

我将检查不同服务器上的选项是否配置不同，特别是ANSI_NULLS(参见下面的脚本)。注意，当您在ssm中运行一个脚本时，默认选项可能与使用ADO时不同。NET(参见SSMS工具/选项/查询执行/ SQL Server / ANSI)。这常常解释了ssm和ADO.NET之间的行为差异。

DECLARE @options INT 
SELECT @options = @@OPTIONS 

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK' 
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS' 
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT' 
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS' 
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING' 
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS' 
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT' 
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER' 
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT' 
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON' 
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF' 
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL' 
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT' 
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

#3

Since you mention the results are different between SSMS and SqlClient, there are two options I can think of

既然您提到了SSMS和SqlClient的结果不同，我可以考虑两个选项。

there are actually 2 different sprocs in different schemas, and SSMS is choosing one and SqlClient the other (presumably based on identity)
实际上，在不同的模式中有两个不同的sproc, SSMS选择一个，SqlClient另一个(可能基于标识)
(much more likely) the SET options are different, causing different behavior
(更可能的是)设置选项不同，导致不同的行为。

To check the latter, run select @@OPTIONS in both SSMS and via SqlClient, and compare. The flags are listed here: http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/

要检查后者，请在SSMS和通过SqlClient运行select @@OPTIONS并进行比较。这里列出了这些标志:http://www.mssqltips.com/sqlservertip/1415/行列式-set- optionsfor a-current-session-in-sql-server/

#4

OK the problem was with (DATEPART(dw, rsdm.DateCreated)) != 7 AND (DATEPART(dw, rsdm.DateCreated)) !=1

问题是(DATEPART(dw, rsdm.DateCreated)) != 7和(DATEPART(dw, rsdm.DateCreated)) !=1

SqlClient user had DATEFIRST set as 1, where as my SSMS was 7. All other same user connections to other sql server instances had DATEFIRST set as 7. (Thanks also to @"G Mastros" for final confirmation of issue).

SqlClient用户的DATEFIRST设置为1，其中我的SSMS为7。所有其他与其他sql server实例的用户连接都将DATEFIRST设置为7。(也感谢@“G Mastros”最终确认问题)。

All I did was add SQL Script SET DATEFIRST 7; for now and will deal with why users are setup with 1 later!

我所做的就是添加SQL脚本集DATEFIRST 7;现在和以后会处理为什么用户设置为1 !

#1