I was running a DTS package which transfer a complete database, about 2.3GB in size, to another database on the same server. It was necessary for environment change from dev to QA. I baby sat the package till it was rebuilding indexes (90%) complete. This morning I have the following error message on my screen,
[Microsoft][ODBC SQL Server Driver][SQL Server]
[Microsoft][ODBC SQL Server Driver][SQL Server]Location: r:SPHINXNTDBMSqueryqeexecqsxchng.inl:749
Expression: (m_cbPageMac + ulPxvarSize) <=m_pxpktdesc->m_cbPageMax
SPID: 40
ProcessID: 347
I have no idea at this time what caused it. And as far as I can tell there in r drive mapped wither on the serve or my computer ( I was running the DTS package from my machine).Any help is appreciated
I was developing a data-driven website but I got this error An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
(I have Sql server 2005 also downloaded before) Please help me cause any website that has data can not be executed even the quick start tutorial that ships with the ASP.NET 2.0 What should I do?
Why does ado.net produce this error when I kow for a fact that there are no connections to any sql server 2005 database involved - this could and probbaly has had people off looking at the wrong connection strings in their web.configs. Note:- The connection string we have is to a sql 200 db I just wanted to make that clear. CheersGegor Error Message:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)Stack Trace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Web.SessionState.SqlSessionStateStore.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo)
i have a form which takes in a lot of input...one of them is a date field..am passing all the values to the stored proc and inserting into a table from there...pretty simple...however when the user does not enter any value in the field...its errors out as...
INSERT INTO Contact_Info (First,Last,Age) VALUES ('Alex','Strait',19)
Now it seems basic, but there in one more field that you're not supposed to specifiy a value in the SQL, its an int IDENTITY datatype field called num_id. Now this SQL works if it doesnt have the num_id field. But heres the error is gives me, I have never seen it before
Microsoft OLE DB Provider for ODBC Drivers error '80040e57'
[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.
hey all, i have a weird authentication error.i have two servers, both running sql server 2005. one is on my workstation the other is on my dev server. i backed up my workstation database and restored it on the dev server. my workstation is xp and my server win2k3. i'm developing an asp.net 2.0 app that talks to my sql server using windows auth. on my workstation my website runs under the ASPNET account, IUSER_COMPUTERNAME and i had that account setup in sql on my workstation in the main security folder, then again in the db security folder the name of it was [ASPNET LOGIN]. each sproc had EXEC permission for [ASPNET LOGIN].when i restored it over to my win2k3 box i deleted that login from the database and revoked it on all the sprocs. i then setup another account for the application and set my website to use that account. i couldn't use the original account because of the group it was in, blah blah blah, not important. the thing is, i got rid of all instances of that ASPNET LOGIN account and i'm running the website under a new account, and some of the stored procedures work, but some of them i get the error "Cannot find the user 'ASPNET LOGIN', because it does not exist or you do not have permission." which is crazy, because i've gotten rid of all references to that. it's not anywhere i see in the database, not in either security/users folder, it's not on any of the stored procedures. and it's NOWHERE in the website. not in IIS or code. furthermore some of the stored procedures work. as far as i can tell, the ones that are not called from my custom MemberShipProvider are working. so i went back to my database on my workstation, revoked exec permission on the sprocs, deleted the login from the db and then from the sql server instance, backed it up again, restored on my server and i'm still getting this issue.where can that ASPNET LOGIN be hiding? i restarted too thinking maybe it was cached somewhere. this is my connection string that the entire app uses: "Server=[serverName]; Database=[dbName]; Trusted_Connection=true;"my web.config is using <identity impersonate="true"/> and my website is running under the account i created.any ideas? i've been battling this for half a day.and why would it only happen with the membership provider? it's using the same connection string the rest of the app is.
Hello I have an issue with a data flow task that is reporting DBSTATUS_UNAVAILABLE. The data flow task consists of a Data Reader source which splits the rows off into 1 of 3 categories; update, insert, delete. Each destination is an OLEDB Command which respectively performs the needed operation.
The insert works fine as we just ran it yesterday and loaded 800,000 records into the table, the update is reporting the error, yet the data types match, both are nvarchar(25).
Does anyone have any idea why this might be occurring? We use this same method for all of our packages and yet this is the only one that seems to have this problem. Any help would be greatly appreciated. Thank you!
I'm trying out the nifty new way of writing SPROCs and UDFs in CLR (C#). I have a UDF that is a bit too complicated for T-SQL so I wrote it in C# but now I can't deploy it. I run the build and it compiles fine. Then I get this error when VS2005 tries to 'deploy': ------ Deploy started: Project: Kingsmere.SQL, Configuration: Debug Any CPU ------ Deploying file: Kingsmere.SQL.dll, Path: C:Documents and SettingsEmmanuelMy DocumentsVisual Studio 2005ProjectsKingsmere.SQLobjDebugKingsmere.SQL.dll ... Error: Incorrect syntax near '&'. I don't have a single ampersand character in my UDF's .cs file. The Test.sql file doesn't have one either. There is no file, line, column number associated with the error. If I hit F1, I get help for compile time error CS0266 which is for no implicit cast available (which was the last compile error I got before compiling successfully so I think this is a red herring. In other words, online help is just displaying the last know error number - nothing to do with the deployment error). I've noticed that if I change the signature of the function from: [Microsoft.SqlServer.Server.SqlFunction] public static int ComputeGoalNodeSpread(SqlInt64 Commence, SqlInt64 Finish, SqlByte TreeLevel, out SqlInt64 PreOrder, out SqlInt64 PostOrder) to: [Microsoft.SqlServer.Server.SqlFunction] public static int ComputeGoalNodeSpread(SqlInt64 Commence, SqlInt64 Finish, SqlByte TreeLevel) The error goes away. Seems to be related to those By Reference parameters. Help!
Steps I made: 1) Created Publications by script - using Manual Sync. (Transacat Repl) 2) Created Subscriptions by script 3) Started Distribution Agent THEN THIS ERROR MSG:
'Ello there. This proc has been giving me headaches for a week now, hopefully someone here knows whats wrong with it.
CREATE PROC [dbo].[search_querry_results]( @datefrom datetime = NULL, @datetodatetime = NULL, @querry_string varchar(60), @slice int ) AS
DECLARE @querry nvarchar(2000) SET @querry = 'SELECT TOP ' + CAST(@slice AS varchar(100)) + ' * FROM FREETEXTTABLE( Exponent, *, ''' + @querry_string + ''' ) as ct JOIN Exponent AS e ON ct.[KEY] = e.[Key] WHERE date > ' + CAST( @datefrom AS varchar(30)) + ' AND date < ' + CAST(@dateto AS varchar(30)) + ' ORDER BY Rank DESC'
BEGIN IF ( @datefrom IS NOT NULL ) AND ( @dateto IS NOT NULL ) EXEC sp_executesql @querry END GO
I am running it from Query Analyzer using the following line
File ID 4 of database ID 13 cannot be shrunk as it is either being shrunk by another process or is empty.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
*/
The commented lines is what I get in return. There's nothing being executed on this file. I dropped a few indexes. I need the space back from the file. Backups and everything are run on it normally. Is there something I'm missing or is there something wrong with it? I don't do 'AutoShrink'. Also, the file is not empty. Checkdb is working fine. Dbcc ShrinkDatabase also works fine but doesn't even recognize these files. It doesn't even show this files in the results pane when executing the command. Thank you for your help.
For some unknown reason, I'm suddenly experiencing the following error when I open a package in BIDS: Error 1 Error loading DimPromotionDemo.dtsx: The connection "" is not found. This error is thrown by Connections collection when the specific connection element is not found. c:mdwt_projectsaworders_simpleaworders_simpleaworders_simpleDimPromotionDemo.dtsx 1 1
I don't see any error icons anywhere in my package. How do I go about figuring out what's wrong?
I am trying to compile the sample program genwin.sqc, using nsqlprepwhich is used to precompile embedded sql in C. I am getting weirderrors and that is because windows.h is included in the genwin.sqcfile. I am usingSetting the lib and include path:set INCLUDE=C:Program FilesMicrosoft SQLServer80ToolsDevToolsinclude;C:Program FilesMicrosoft SQLServer80ToolsDevToolsSamplesesqlc;%include%set LIB=C:Program FilesMicrosoft SQLServer80ToolsDevToolslib;%lib%Precompiling:nsqlprep genwin /NOACCESSMicrosoft (R) Embedded SQL for C Pre-Compiler. Version 8.00.194.Copyright (c) Microsoft Corp 1993-1998. All rights reserved.Warning: genwin.sqc, line 7978: syntax error.}Warning: genwin.sqc, line 7987: syntax error.}Warning: genwin.sqc, line 71840: syntax error.__declspec void __stdcall DragAcceptFiles(HWND,BOOLWarning: genwin.sqc, line 71842: syntax error.__declspec HINSTANCE __stdcall ShellExecuteA(HWND hwndWarning: genwin.sqc, line 71843: syntax error.__declspec HINSTANCE __stdcall ShellExecuteW(HWND hwndWarning: genwin.sqc, line 71858: syntax error.__declspec INT __stdcall ShellAboutA(HWND hWndWarning: genwin.sqc, line 71859: syntax error.__declspec INT __stdcall ShellAboutW(HWND hWndWarning: genwin.sqc, line 71941: syntax error.HWNDWarning: genwin.sqc, line 71946: syntax error.As soon as i remove windows.h, the errors disappear. It seems nsqlprepdoesnt like windows.h. How do i get over this.Would really appreciate if u can help me out.versions:OS: windows 2000 server service pack 2VC++: version 6.0SQLserver 2000: 8.00.194, RTM, Standard editionThank youAmit
I have a package that has a FOREACHLOOP container. Inside the container is a SCRIPT task that runs a stored procedure and writes the output of the sp to a file.
That part works fine.
However, if I add a second script task inside the FOREACHLOOP container, I get error messages when running the second script task.
The second script task is identical to the first script task, except for the connection information. That is, it's doing the same thing, but running the sp on another server.
I tried removing the second script task from the FOREACHLOOP container, and putting it in it's own FOREACHLOOP container, but it still gives the same error.
This is the error I'm receiving:
Error 30009: Reference required to assembly 'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' containing the implemented interface 'System.Xml.Serialization.IXmlSerializable'. Add one to your project. Line 7 Column 22 through 30
I've been setting up subscriptions to a merge publication for the past 3 days. All of a sudden, I'm getting a pile of very strange errors. Replication is configured. I have 16 subscribers to an existing publication configured and synchronizing changes without any issues. The script that I'm using to create all of the subscriptions is as follows:
The last one that I added #17, gives the following errors after successfully creating the subscription.
Command attempted:
{call sys.sp_MSmergesubscribedb ('true', 0) }
Error messages:
The merge process could not initialize the subscription. Ensure that the subscription registration exists at the publisher, and reregister the subscription if necessary. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201011) Get help: http://help/MSSQL_REPL-2147201011
RegCreateKeyEx() returned error 5, 'Access is denied.' (Source: MSSQLServer, Error number: 22002) Get help: http://help/22002
Could not add article resolver 'Microsoft SQL Server Additive Conflict Resolver' information to the registry (Source: MSSQLServer, Error number: 21713) Get help: http://help/21713
Could not register article resolver: 'Microsoft SQL Server Additive Conflict Resolver'. (Source: MSSQLServer, Error number: 21715) Get help: http://help/21715
The system tables for merge replication could not be created successfully. (Source: MSSQLServer, Error number: 20008) Get help: http://help/20008
I've tried to manually create it using the GUI and get an even stranger error message as follows:
TITLE: New Subscription Wizard ------------------------------
Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. For information about installing replication, see the topic Installing Replication in SQL Server Books Online.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=ReplicationNotInstalled&LinkId=20476
Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication. (Microsoft SQL Server, Error: 21028)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21028&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------
I very obviously have replication installed by virtue of the fact that I have a publication and 16 subscriptions that are currently synchronizing. Any ideas?
We've had Reporting Services running in a production environ. for 6 months fine, but from Saturday every report now causes the following error (in both the Report Manager and Soap calls):
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
Specified argument was out of the range of valid values. Parameter name: date
Now, before you jump to conclusions - this error is occurring on reports with both parameters and no parameters (ie in reports that have no "date" parameter in the report).
The next bit of info is the weird bit...
It was working on Friday (25/March/2006) - so as a test, i switched the servers clock back to Friday - and BINGO... it worked. Then I changed it to Saturday (26th March) and it doesnt work. In fact for the next 7 days - the service will not work until April 2nd 2006 - (when I changed the systems date to the 2nd it worked again.) Moving forward, it looks like its working fine.
Does anyone have any suggestions? This is in a production environment, so obviously changing the sytsem date as a quick fix workaround wont suffice.
I have a dts package that is mysteriously changing back to a previously saved version. Package is saved, and re-opened with saved changes, then moments later opened again to have been reverted back to an older version. Has anyone ran into this before? The only thing that I can think of is that if a version of the package is left open on some other machine (as the older version0, it may be auto-saving, and reverting back.
I run a website, so all of this is done remotely, I do not have access to the servers directly.
On my prior host, everything on the DB worked just fine. Had it optimized and running pretty good for the site by adding indexes, etc.
Then I moved to my new faster server (new host) and at first everything seemed to be just fine. However, some users started complaining about speed in spots.
Sure enough when I checked a given page -- WHAM... the page took about 14 seconds to load. Not exactly fast. :)
So I checked to make sure the indexes copied over and sure enough they did. But it was still slow, even in Query Analyzer/MSE.
So I decided to rerun my view script... on a lark... just to see if that affected something. And sure enough it did. The page started running at 1 second or less. OK, I thought, the view just got corrupted or something.
All was fine until the next night when it happened again. I had added 12 new rows to the table (I do this nightly) and it seemed that adding the new rows slowed down the system. Until the view was rebuilt. With that done again, it's worked fine.
But each night now I have to rerun that view.
What gives??? Is there a DB setting or something causing views/indexes to not be maintained?
The prior server was 2000 or 2003. The new one is 2005.
Again, I cannot get access to the box itself except through Management Studio. The host's tech support group is less than useless, treating everyone as incompetent before they help you.
I'm trying to CAST an integer to a varchar and I keep getting e+006 in my result set. The reason I need to do this is so that I can combine 2 columns to return a string.
Hello and thanks for taking a moment to read this message.I have two colums from which I am taking values. One of which(a bit field), i am running a CASE statement against it to get SQL Server to return a string. The other is just a simple varchar column. My SELECT statement for the columns look like this: SELECT Case tblDisplayProfile.Approved When 0 then 'Not Approved' when 1 then 'Approved' else 'Not looked at' END AS Approved, tblDisplayProfile.DisplayProfileDesc These statements do return me something. Now what I want to do is combine (concantenate) the two fields. This is where I have problems. Any suggestions would be greatly appreciated.
Hi, I moved a SQL 2000 DB to a SQL 2005 server. When I run my .NET 1.1 or 3.5 website to call a stored procedure it returns the wrong results.The SQL stored proc runs fine when I excute it within SQL Manager but gives a different result when called via .NET The funny thing is I have 3 SQL servers 2000 , 2005 , and another 2005. The same data on each of the databases and the same website. Two of the databases work but the second 2005 database my production server decrements the dates some how. The funny thing is the stroed proc is not returning dates as such but a varchar of the name of the day that's getting decremented. My stored proc is SELECT
Case datepart(dw, DT_DATE) When 2 Then 'Mon' When 3 Then 'Tue' When 4 Then 'Wed' When 5 Then 'Thu' When 6 Then 'Fri' End AS [WeekDay], Sum(VL_HOURS) as Total
FROM ...
WHERE ....
GROUP BY datepart(dw, DT_DATE) and My c# .net code is "SqlHelper.ExecuteReader( connectionstring, storedproc, paramaeters);"using MS.ApplicationBlocksWhat happens that from sql, and 2 of my websites is when week day = 4 it returns "Wed" However on my Production server it returns "Tue" ???
Hello! MSSQL Server 7.0 SP1 When I open DTS designer for some unknown reason I'm getting error message "Could not create Component Categories manager". Then under Task I don't have any choices. Is there a quick way to fix it?
Last night our production server went crazy. The primary (production) database MDF file had size 15Gb and then for some reason the size became 0 (zero) K, So the SQL right away reported the error:
I/O error 38(Reached end of file.) detected during read of page buffer..
Error: 823, Severity: 24, State: 10
And the database became "suspected". Now we restoring it, but have anybody seen such a thing before? I'm breaking my head to figure out what could cause this problem!!!!!
Whenever I am creating a new database, I am not getting any system stored procedures created :o( the system tables & views are created though :o( what maybe the problem?
When I run the command: exec master..xp_cmdshell 'NET USE' from the analyzer the box responds there are no entries in the list.
After that, I run the command: exec master..xp_cmdshell 'NET USE Z: /DELETE' after which the box responds with a "network connection could not be found." and that's all okay.
The weird thing is: exec master..xp_cmdshell 'NET USE Z: \MACHINESHARENAME' results in a "The local device name is already in use.".
The machine in this particular case is the box itself. I have no problem accessing other disks on other systems. I can see the share using the view command. There's no maximum on the share itself and I can connect to the share using another sql box with the same user.
I don't know why it won't budge, worked before like a charm. After six months or so it just stopped. Anyone seen/solved this behaviour?
I have a datetime field with a value of '-28049-03-16 10:01:16.267' in SQl Server 2000. I haven't a clue how or why this value was entered as I did not design or write the database. However, I do have the task of writing a DTS package to export the data to Access. The problem is that the DTS fails when it tries to export the record with the above value into an Access table with a Date/Time field. I thought I could get round this by using a UDF to say if the date equals this value then make it null:
RETURNS DateTime
As BEGIN DECLARE @CheckDate varChar(300) SET @CheckDate = CONVERT(varChar(300),@Date)
IF (@CheckDate = '-28049-03-16 10:01:16.267') BEGIN SET @Date = Null END RETURN (@Date) END
However when I use the UDF it gives me the following error:
Server: Msg 542, Level 16, State 1, Procedure UDFn_CheckDate, Line 23 An invalid datetime value was encountered. Value exceeds the year 9999.
I have tried many variations of Convert, Cast etc. but still can't get it to work.
I'm now converting my queries from Access db to SQL Server db and I'm new with SP only.
I have a big query that is building from 1 or 2 sub-queries with "TOP x" (x is a variable in ASP). SP can't be used with an outside variable for "SELECT TOP x.." so I tried to send the sub-query as a variable to the SP but it refer it as a string and not a code so I can't run this sub-query in the SP.
Example:
CREATE PROCEDURE usp_test
@subSQL varchar(200)
AS
SELECT id FROM tbl WHERE id IN (@subSQL) ORDER BY id DESC
GO
----------------
exec usp_test 'SELECT TOP 15 id FROM tbl2'
What's wrong here? Am I working in a wrong method? :(
I have this stored procedure that works a little odd, some times it do what I want it to do which is....
- Insert orderinformation in tbl_Torderinfo table and then transfer all rows that are associated with a ordernumber from tbl_Ctemp to tbl_Torders
but sometimes it only insert data into the tbl_Orderinfo table and doesn't transfer the order details from tbl_Ctemp to tbl_TOrders even if there is rows to transfer. Any ideas of what might cause this odd behaivior?
I'm now converting my queries from Access db to SQL Server db and I'm new with SP only.
I have a big query that is building from 1 or 2 sub-queries with "TOP x" (x is a variable in ASP). SP can't be used with an outside variable for "SELECT TOP x.." so I tried to send the sub-query as a variable to the SP but it refer it as a string and not a code so I can't run this sub-query in the SP.
Example:
CREATE PROCEDURE usp_test
@subSQL varchar(200)
AS
SELECT id FROM tbl WHERE id IN (@subSQL) ORDER BY id DESC
GO
----------------
exec usp_test 'SELECT TOP 15 id FROM tbl2'
What's wrong here? Am I working in a wrong method?
have a dts package that is run by a scheduled job which was runningsuccessfully for several months and suddenly stopped working.The dts package does the following: Truncates the table, does a datapump from a specific excel file to a table on sql server, deletes theexcel file. The job does the following:Step 1: Look to see if a file exists and if so, run the dts package. Onsuccess of this step the job goes to step 2.Step2: Looks to see if any files still exist after the first stepStep3: Failure notification. To be triggered on failure of step 1 or2.The owner of the job is the Account that is used to run theSQLServiceAgent and in the SysAdmin server role.The owner of the job is also in the SysAdmin server role.Both the accounts are Windows Accounts.The job runs at schedule time, finds the file, and runs the dtspackage. The first part of the dts package runs successfully - Thetable is cleared. However it is not doing the data pump part. Thereare no failure notifications. The job then goes to step two and sendsa message that the file still exists. The dts task is set to output anerror file which is not being updated, which tells that the task is notexecuted. If the dts package is run manually, then the package runssuccessfully.Any help would be appreciated.ThanksKR
I have just installed my server with server2003 enterprise edition, everything is working fine. However, after I make it online for awhile, I found a weird thing on the tool bar just beside the clock. There are 2 hidden icons, when I move the pointer to these 2 icons, it shows the IP address of my server. Just wanna know if there is anything wrong with that.
I'm seeing some strange behavior from a stored procedure of mine. It essentially grabs a bunch of rows using a fairly simple JOIN....here's the from statement:
Code Snippet FROM Payment PY (NOLOCK) JOIN (SELECT DISTINCT PY.AccountPaymentId, ROW_NUMBER() OVER(ORDER BY PY.AccountPaymentId ASC) AS RowNum FROM Payment PY (NOLOCK)) AS SQ ON (SQ.AccountPaymentId = PY.AccountPaymentId) INNER JOIN Payee PE ON PE.PayeeId = PY.PayeeId INNER JOIN Party PT ON PE.PartyId = PT.PartyId INNER JOIN Distribution DS ON PY.DistributionId = DS.DistributionId LEFT OUTER JOIN Account AC ON DS.AccountId = AC.AccountId INNER JOIN clm CM ON PE.clm_no = cm.clm_no LEFT OUTER JOIN PartyAddress PA ON PY.PartyAddressId = PA.PartyAddressId AND PT.PartyId = PA.PartyId WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize and ((@PayeeName IS NULL) OR (PT.[Name] LIKE '%' + @PayeeName + '%')) AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId)) AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId)) AND ((@PaymentDate IS NULL) OR (DATEADD(day, DATEDIFF(day, 0, PY.PaymentDate), 0) = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber)) AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled)) AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued)) AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid)) AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus)) AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus)) AND ((@IsReissued IS NULL) OR (PY.ReissuedInd = @IsReissued)) ORDER BY AccountPaymentID ASC
When I pass a 1 for the @IsReconciled parameter, I get the right number of rows back - 9779. But when I pass a 0 (zero), i get no rows back, although there are 222 rows which satisfy the condition.
Is there somethig I'm overlooking (I don't think I am...)? I don't know whay 1 works and 0 wouldn't...
FYI - the @IsReconciled parameter is set to NULL at the outset of the procedure -
