Behavior Of SQL Server On Table Scans
Jun 29, 2007
Hi All,
if someone can point me to documentation on this I would appreciate it.....
If there isn't any....
I am wondering about the behavior of SQL Server for table scans. In other databases tables scans are not really table scans, they are scans of the underlying tablespace for all the rows that are in the table.....and if many tables are placed into the same tablespace then the obvious slowdown occurs as rows are scanned that are not in the table.
This used to be the case in server 7......but is it still the case in 2005 that if the explain says 'table scan' it will in fact scan the filegroup the table is in?
Some other databases also have a map of the row numbers and the table it is in and the optimiser decides whether to scan the data itself or to navigate through the map and fetch a row at a time depending on the stats....
It seems that the grahical explain does not tell me more than 'table scan'. Is there any way to see down to the physical level of what the optimiser is going to do?
Thank You in Advance
Peter
www.peternolan.com
View 2 Replies
ADVERTISEMENT
Jul 20, 2005
Hi all. Anyone know if sql server collects stats on how many fulltable scans a table gets and if so how do I get at those stats? Tryingto track down poorly indexed tables / processes and I am guessing thatsql server does have this data secreted away somewhere much like mydb2 and informix databases do.
View 1 Replies
View Related
Jul 20, 2005
I have a query which is quite complex. It's based on a set of data ina complex view which takes the data from several tables.In this complex query, if I allow the various parts of the query towork on the results of the view (MISView), it can take 15 minutes torun (eek !), however, if I create a temporary table with the data fromthe view and then use that for the remainder of the query, it runs inapprox 20 seconds.Now, I have examined the execution plan (my new favourite toy) andthere is a difference (as expected). However when looking at the partof the query that takes up most of the time, it shows that it bringsback 109,645,866 records from a table (Credit) that contains 13,002records. This table is one that is referenced in the view (MISView)which contains 13,653 records and does get some of it's data from thetable which is scanned (Credit).For the record, we don't have any tables with over 100,000 records in,so 109 million rows is going some for us. The part of the query thatruns slow does reference another copy of itself but this is necessaryfor the equation that is being run.Now I'm OK with why it's doing the table scan, but why does it bringback substantially more data than is in the table ? Is it somemultiple of the number of records that it's trying to work out. Iassume it tries to run a seperate plan for the view as part of it'sprocess.Ideally, I'm still going to go down the route of the temporary table,but I would like to understand more about what it does first as Idon't like leaving things unanswered.Any help would be appreciated.
View 6 Replies
View Related
Jul 20, 2005
How-doody all. Is there an easy way to identify tables that are notproperly indexed based on how often sessions are performing full tablescans on it?
View 1 Replies
View Related
Sep 21, 2001
Hi,
Full scans are bad, and I know this. I am seeing some on my database, and I know that some are unavoidable....but what I would like to do is find out which queries are causing them. I have over 500 users in my DB at any one time, and SQL Profiler doesn't seem to help me.....it shows me what objects are being scanned, but not if the scan is a full-scan or an index scan.
Does anyone have a way to find out what is causing my full scans?
View 1 Replies
View Related
Oct 21, 2003
hi, i'm monitoring a SQL server 2000 and every 5 minutues aprox. a dbcc scan is triggered on 2 databases only.
how can i deactivate this scans or how can i define a longer period of time between them?
thanks
View 8 Replies
View Related
Jan 18, 2007
Try a little experiment. Partly to humor me, and make me believe I am not quite insane.
Step 1: Install SQL server 2000, such that the data files are not in the default location, but in a location with a shorter path (i.e. install the data files to E:MSSQL8).
Step 2: Run the following queries, and comment on any oddities:
select filename
from master..sysaltfiles
where dbid = 2
go
select reverse(rtrim(filename)), filename
from sysaltfiles
where dbid = 2
go
select reverse(rtrim(filename))
from sysaltfiles
where dbid = 2
I am guessing that #2 is some sort of odd effect caused by the fixed length data field, but I just want to make sure that other people get this oddity, and not just me. I have no idea what could be causing the third output...or perhaps the lack of it.
View 2 Replies
View Related
Jul 19, 2005
Hi,anyone could explain this : I have installed the same asp.net application on 2 distinct hosts, each with its own SQL Server 2000 database.However, in some SQL queries in Stored Procedures, the results do not come out the same.For example, on one server the query "select @value = (select ....)-(select ...)" works and on the other one it returns a null valueAny hint ?Thanks for your helpJohann
View 2 Replies
View Related
Nov 2, 2007
We are having some problems with the SQL Server 2005 Keep Alive mechanism causing dropped connections. The Keep Alive time and interval appear to work as documented. However the number of retries made before dropping the connection seems to be variable. When running Network Monitor I have seen this vary between 3 and 9 retries. The value for TcpMaxDataRetransmissions in the registry is set to 5. Does anyone know what the correct number of retries should be for SQL Server 2005 Keep Alive? Is this dynamically determined or is this a bug?
View 2 Replies
View Related
Dec 2, 2007
Hi Friends,
I am struggling with creating a simple stored procedure, I want to take max(timekey) out in one variable @Val in a dynamic SQL statement as below in SP. When I create this SP, it creates successfully but when I run it I get error
Select @Val=MAX(TimeKey) FROM ABC
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@Val".
Can someone help me in understanding this wierd behavior? And how to get rid of this. Table and SP scripts are as below
CREATE TABLE ABC (TimeKey int, Data_Val int)
CREATE Procedure MAXVal(@Table_Name Varchar(30))
AS
BEGIN
DECLARE @Val INT,
@SQL Varchar(100)
SET @SQL='Select @Val=MAX(TimeKey) FROM '+''+@Table_Name+''
EXECUTE (@SQL)
END
Thanks,
Kul
View 6 Replies
View Related
Aug 2, 2006
I was able to successfully create a database maintenance plan for SQL Server 2000 Transaction Log Shipping for a few databases a few weeks ago. Yesterday, I created a few more but to my surprise, I can no longer do it. I can create a maintenance plan but the job it creates does not start even if I force the job to start. I did exactly the same thing as what I did (as I document everything I do) before but no luck.
Has anybody had this experience before?
View 5 Replies
View Related
Oct 1, 2004
I have two SQL Server Instances on two servers. One server is my webserver and database server and the other one is just a database server. i have an application that calls a stored procedure located on the webserver/database server that runs a query on the OTHER database server. I use linked tables in my first instance to make the call possible.
Everything was working just fine for months until the database server was restarted and the IP address was changed. The name of the database is the same however and my first SQL Server instance has no problems running queries on the other databases tables. However, when you try to run the application i get the following error:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection
I have mixed mode authentication selected and my security uses the security context with username=sa and password=sa.
So here's the weird part.
The application will only run correctly when i manually run a SQL command from my webserver's SQL Analyzer on the linked SQL Server. however, after a few minutes, the same error comes back!! so as a temporary fix, i scheduled a dts job to run a simple query on the linked server every two minutes, so the application keeps working! It's almost as if the webserver's sql server forgot that the linked server is there, and by running a simple query in query analyer, the connection gets refreshed and everythings normal again - for about 3 minutes!
I am completely stumped by whats happeneing and appreciate any help. Thank you.
View 3 Replies
View Related
Jan 21, 2008
I'm a wee bit of a newbie concerning DTS and have inherited a db with a DTS containing a Copy SQL Server Objects task set to run nightly. Essentially, it does an informal backup of some core data.
Recently, I was notified that one of the tables it copies over is now empty on the Destination db. The DTS shows that it runs successfully with no errors logged, the table in question IS selected to be copied from the Source database, there IS data in the Source database table, and every other table in the Destination database is populated appropriately.
Any ideas on what would cause this one table to be empty without generating any errors?
FYI, running SQL Server 2000.
View 1 Replies
View Related
Oct 11, 2014
I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'
Below is Query 1:
Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar= pub_name
FROM publishers
WHERE pub_id= '999'
Select @testvar
Out put of this query is 'Noname'
BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement
Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar=(Select pub_name
FROM publishers
WHERE pub_id= '999')
Select @testvar
View 4 Replies
View Related
Feb 6, 2007
This was originally posted on DBForums.com, so here is the link:
http://www.dbforums.com/showthread.php?t=1614086
Since some of the Microsoft staff come around here occasionally, I figured I should at least link to it here. This is the gist of the problem, though. I was asked to come up with a script to create all required data directories in case an emergency was declared, and someone had to rebuild one of our database servers. Most of you are probably thinking of hitting up the sysaltfiles table about now, but this will turn into a cautionary tale. Try it if you dare. The one requirement is that you install the data for SQL Server in a non-standard directory that has a short path (such as C:MSSQL8, instead of the whole C:Program files...).
What I am unclear on is whether this is a problem in the reverse function, the r(l)trim function, or the fixed-width datatype. I have confirmed that transferring the data to a temp table did not eliminate the...oddity.
select filename
from master..sysaltfiles
where dbid = 2
go
select reverse(rtrim(filename)), filename
from sysaltfiles
where dbid = 2
go
select reverse(rtrim(filename))
from sysaltfiles
where dbid = 2
I have also had two independent DBAs confirm this oddity exists, so this should be relatively easy to replicate.
View 10 Replies
View Related
Aug 29, 2007
I have three tables I am using, aspnet_Users, Stories,
CustomizedStory. Stories and
CustomizedStory are related via a foreign key StoryID. I’ve setup the tables so that when I delete a
Story row it cascade deletes the corresponding row from CustomizedStory.
Each CustomizedStory row has a reference to UserID from aspnet_Users.
Since, I didn’t want to mess with the table definition by adding a cascade
delete option on aspnet_Users, I decide to use a trigger, essentially delete all customized stories and associated stories if a user is deleted:ALTER
TRIGGER [dbo].[DeleteCustomizedStories] ON [dbo].[aspnet_Users] FOR DELETEAS
BEGIN DELETE FROM dbo.Story
WHERE StoryID
= (SELECT StoryID FROM dbo.CustomizedStory WHERE
UserID = (SELECT UserID FROM deleted))END
The problem I am having is that it deletes all of the
CustomizedStory rows as specified by the cascading option, but doesn’t delete
the Story rows. I can’t seem to understand
why this is happening, especially when I
explicitly told it to delete story rows.
View 1 Replies
View Related
Feb 1, 2008
I've done a new tabel that insert the UserId that in a uniqueidentifier get from Membership.GetUser().ProviderUserKeySo if I want to make a select statement threw storedprocedure in codebehind it runs as it shouldCode behindDim GetCustomersCars As CustomerCarByUserId = New CustomerCarByUserId MyCars.DataSource = GetCustomersCars.CarByUserId(Membership.GetUser().ProviderUserKey)MyCars.DataBind() But in when I use ObjectDataSource it fails<asp:ObjectDataSource id="ObjectDataSource1" runat="server" selectmethod="CarByUserId" typename="CustomerCarByUserId"> <SelectParameters> <asp:Parameter defaultvalue="Membership.GetUser().ProviderUserKey" name="UserId" type="Object" /> </SelectParameters> </asp:ObjectDataSource>I've tried with Membership.GetUser().ProviderUserKey.ToString(), but that doesnt work. Error message:InvalidCastExceptionI connect to the same source in both cases.Any one with an Idee ?
View 1 Replies
View Related
Jan 14, 2005
I get the error " 'commandBehavior' not declared". What does this mean? What is CommandBehavior exactly?
'
Sub Page_Load(Sender As Object, e As EventArgs)
' Obtain categoryId from QueryString
Dim connectionString As String = "server=(local); trusted_connection=true; database=SalesSide"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "Select tblIdent.fldStockNo, tblIdent.fldProgram, tblIdent.fldGenus" & _
"tblIdent.fldVariety, tblIdent.fldSize, tblAvailability.fldQuantity" & _
"FROM tblIdent INNER JOIN tblIdent On tblAvailability.fldStockNo = tblIdent.fldStockNo"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlConnection.Open
dgAvailability.DataSource = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
dgAvailability.DataBind()
sqlConnection.Close
End Sub
View 1 Replies
View Related
Dec 17, 1999
I'd like to understant the Percent Log Used behavior...
I monitored the Percent Log Used. For a specific database, it was 50 % used. I backup up the transaction log and the Percent Log Used was still 50 % used. For another database, the log was 60 % used. After the backup, it was 80 % used!!!!
Would you help me to understant this situation?
Thank you,
Fabio
View 2 Replies
View Related
May 28, 1999
Hi all,
I have a question regarding some very odd NT/SQL behavior that is exhibited when running a shell command through xp_cmdshell. If I do a 'net use z: servernamesharename' from the xp_cmdshell, it shows up in Explorer with the icon for a local drive! This would not be more than an ordinary M$ bug, except for the fact if go to a command line, I can't do a 'net use z: /delete' because it says that the network drive isn't there! Can someone shed some light on this?
Thanks,
Ed Molinari
Emerald Solutions
View 2 Replies
View Related
Jun 3, 2004
Hi
I'm using MSSQL 2000 (with SP3) on Win2000.
Now, before installing the service pack I encountered with several strange bugs in the MSSQL mostly in queries that used TOP, gladly they were all fixed when installing the service pack... or so I thought...
So yesterday while trying to optimize a heavy query (7 joins - 2 of them are left joins from different tables crossed to the same table) I encountered yet again and with the latest service pack with an even stranger bug.
First the returned records are just not always the same, meaning, for example when I use TOP 465 in the SELECT statement, the last record which is 465 contains some value, when using TOP 466 the record before the last which is record 465 contains different value!... of course both with the same ORDER BY clause.
Also when I view the execution plan it's also not same in both cases, with TOP 465 it's one way (and much much faster) and with TOP 466 the plan is completely different and much slower...
Does anyone encountered with this phenomenon? Any suggestions?
BTW, don't pay too much attention to the number 465, in my case this is the border of the problem, after trying this query on different tables I found that each has it's own border that after it the TOP starts to freak out.
Thanks for the help!
Inon.
View 14 Replies
View Related
Jun 30, 2004
I've scheduled a job to run on a certain schedule, but the Last Run Status date comes back very oddly, a couple years out of synch, the other jobs scheduled report back just fine.
Anyone seen this behavior?
Edward R Hunter, Data Application Designer
comScore Networks, Inc.
View 1 Replies
View Related
Nov 29, 2005
I have a SP that usually works fine (0-16 CPU time, 40 ms Duration), but from time to time the server hangs with apparently no reason. The SP has a lock timeout set to 500, so it should abort if a lock timeout error (1222) occurs but it doesn't. The Profiler reports very long execution time (over 30 sec), and because of that all other SP calls are blocked, 'cause the transaction opened by the first sp execution is not finished yet.
Any other attempts to identify other blocking queries did not show me anything suspect (sp_lock, dbcc opentran) other then the usual blocked chain. I'm starting to think about an IO bottleneck, or IO failure, that could block the disk access and cause the delay. The status of RAID 5 is healthy.
The server is used as storage system for a website (approx. 2000 concurrent users), and occasionally I noticed an ASP queue, but this strange behavior occurs even during the peak-off hours.
Any thoughts ?
-----
HP Server - 2 CPU @ 3,4 ; 4 GB RAM; SCSI - RAID 5
Windows 2000 Advanced Server - SQL Server 2000 SP4
View 1 Replies
View Related
Feb 18, 2008
I would like to ask for help. We had no problems with dynamic queries in SQL 2000, which were very fast. But when we ran the same queries in SQL 2005, it was many times slower lasting several seconds. I guess it has something to do with creating execution plan, because when I run it second time, it is suddenly extremely fast. But after just a little change (like adding space character), the speed is very slow again. If it is caused by execution plan in SQL 2005, is it somehow possible to change its settings so that it will behave like in sql 2000?
Thank you for answers!
View 2 Replies
View Related
Jul 23, 2005
Hi folksI have an C# app. connecting to a MS-ACCESS database with several tables.In a specific situations I have problems with a DateTime type in a table.The problemis when I want to select records from a table in a specific period the dayand monthseems to be swapped in the query, but it only happens when the swappinggives avalid date eg.12/10/2005 (12. Oct. 2005) returns records on 10/12/2005 (10. Dec. 2005)23/05/2005 (23. May 2005) returns records correctly since 05/23/2005 is nota valid date with danish regional settings.The query is:"SELECT [ID], [Activity], [BeginDate] FROM TimeReg WHERE [BeginDate] >= #" +_start + "# " AND [BeginDate] <= #" + _end + "#"_start and _end are of type DateTimeMy PC in running with danish regional settings and if I shift to en-USsettings in the control panel, thisfixes the problem, but that is not a solution for me.Any suggestions to solve this problemThanks in advance.Kim W.
View 4 Replies
View Related
Apr 22, 2007
SQL Server 2000 SP4.Running the script below prints 'Unexpected':-----------------------------DECLARE @String AS varchar(1)SELECT @String = 'z'IF @String LIKE '[' + CHAR(32) + '-' + CHAR(255) + ']'PRINT 'Expected'ELSEPRINT 'Unexpected'-----------------------------If the @String variable is set to 'y' (or in fact any ANSI character otherthan 'z'), the result is 'Expected'. The comparison also evaluates asexpected if CHAR(255) is replaced with CHAR(254). The server collation, ifthat matters, is SQL_Latin1_General_CP1_CI_AS.It would be helpful to find the explanatin of this behavior. Thanks.--(remove a 9 to reply by email)
View 2 Replies
View Related
Nov 1, 2007
I am have the following code below on a standalone computer and it worked perfectly. Suddenly, without any significant changes to the code there were no Servers instances found on my local computer. I know there are several server instance on the computer. Why is it acting so unpredictable? The same thing happened when I tried SQLDMO.
// Get a list of SQL servers available on the networks
DataTable dtSQLServers = SmoApplication.EnumAvailableSqlServers(false);
foreach (DataRow drServer in dtSQLServers.Rows)
{
String ServerName;
ServerName = drServer["Server"].ToString();
if (drServer["Instance;] != null && drServer["Instance"].ToString().Length > 0)
ServerName += " + drServer["Instance"].ToString();
if (cmbServer.Items.IndexOf(ServerName) < 0)
cmbServer.Items.Add(ServerName);
}
View 3 Replies
View Related
Jul 20, 2006
I am writing an upsert proc that should detect the change in state for a record. The change in state happens when a particular date field (default null) is populated. However, I can not get a record set that detects the changes properly.
Here is an example
set ANSI_NULLS on
go
create table #t1
(
ID int,
DateField datetime
)
create table #t2
(
ID int,
DateField datetime
)
insert into #t1 (ID, DateField) values (1, '7/20/2006')
insert into #t2 (ID, DateFIeld) values (1, null)
select * from #t1 join #t2 on #t1.ID = #t2.ID where #t1.DateField <> #t2.DateField
drop table #t1
drop table #t2
The select should return a record because NULL does not equal '7/20/2006' but it doesn't.
What am I missing?
Thanks in advance.
View 4 Replies
View Related
Dec 11, 2006
Hi All -
I am running sql2005 on windows 2003 all with uptodate SPs
Im sure this has been discussed somewhere, but I just dont understand why its behaving this way.
my issue is that if a 'db_owner' is a member of another db role then the is_member('some_other_role') always returns 0
example:
dbrole: testrole
ntuser: joeuser
ntgroup: testgroup
ntgroup: grpsysadmins
joeuser is a domain login.
testgroup and grpsysadmins are both nt groups.
joeuser is a member of both testgroup and grpsysadmins
grpsysadmins is a member of administrators grp on the sql server machine
testrole is a sql role
testgroup is a login into sql
testgroup is a member of testrole
1) when I do select is_member('db_owner') I get 1
2) when I do select is_member('testrole') I get 0 <== this is the wierd one
3) when I do select is_member('machinename estgroup') I get 1
I am expecting all 3 to return 1. now if I am logged as a user that is not sysadmin then select is_member('testrole') returns 1.
this behavior seems to be by design, but for the life of me i have no idea why?
View 7 Replies
View Related
Aug 29, 2007
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 -
@IsReconciled Bit = Null
View 1 Replies
View Related
Aug 8, 2007
I posted this at the asp.net forums but somone suggested I post it here. So:
Try this in sql server:
select COALESCE(a1, char(254)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
select COALESCE(a1, char(255)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
The only difference is that the first one uses 254 and the second one uses 255. The first sorts like this:
W
Y
Ya
Z
þ
The second one sorts like this:
W
Y
ÿ
Ya
Z
Is this expected behavior?
View 1 Replies
View Related
Aug 29, 2007
I'm seeing mixed results when I run a certain JOIN query...
Here's the original query:
Code Snippet
SELECT DISTINCT PY.PaymentId
, PY.PayeeId
, PY.PartyAddressId
, PY.DistributionId
, PY.EntitlementId
, PY.DeliveryTypeEnumItemId
, PY.AccountPaymentId
, PY.ParentPaymentId
, PY.PaymentAmount
, PY.PaymentDate
, PY.PaymentStatusEnumItemId
, PY.PaymentStatusDate
, PY.ReleaseRunId
, PY.ReleaseDate
, PY.AccountTransactionLogId
, PY.AccountStatusEnumItemId
, PY.AccountStatusDate
, PY.AccountPaidAmount
, PY.ReconciledInd
, PY.ReissuedInd -- Added 8/15/2007 AMR
, PY.UndeliverableInd
, PY.ReissueNote
, PY.CreateDate
, PY.CreateId
, PY.ModifiedDate
, PY.ModifiedId
, DS.Description
, AC.Description
--Removed 8/14/2007 AMR , AC.AccountProvider
, AC.AccountId
, PT.Name
, PA.AddressLine1
, PA.AddressLine2
, PA.City
, PA.State
, PA.Zip5
, PA.Zip4
, PE.clm_no
, CM.clmnt_idno FROM Payment PY (NOLOCK)
LEFT JOIN (SELECT PY.AccountPaymentId, PA.AddressLine1, ROW_NUMBER() OVER(ORDER BY PA.addressline1 ASC) AS RowNum
FROM PartyAddress AS PA INNER JOIN
Payee AS PE ON PA.PartyId = PE.PartyId INNER JOIN
Payment AS PY ON PE.PayeeId = PY.PayeeId
) 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 (((1*1000) - 1000) + 1) AND ((1*1000) - 1000) + 1000
This query numbers rows based on the PartyAddress. When I keep the WHERE clause in the query, it returns 810 rows. There exists a relationship between PARTY and PARTYADDRESS. There are 10001 rows in the PARTY table and 810 corresponding rows in the PARTYADDRESS table. Not every PARTY will have a corresponding PARTYADDRESS.
What the query SHOULD do is return all rows from PARTY and it's corresponding PARTADDRESS entry, if there is one.
When I take the WHERE clause out, I get back all 10001 rows - what I'm expecting.
Why would the "WHERE RowNum" clause affect the wuery? How can I get around that?
Thanks!!
View 1 Replies
View Related
Mar 5, 2008
I have 2 packages that for ease I'll call Parent & Child. The Parent package calls the Child package as the 4th step in the process. Once the Child has completed, the Parent has a few more imports that it does.
The Portfolio table is loaded in the Child package which is step 4 in the Parent package. Then in step 5 a few tasks utilize that Portfolio data for lookups.
The strange part is that there are probably 4 or 5 data tasks that do lookups against the Portfolio data in Step 5 (step 5 is a container). All but 2 of the data tasks retreive data from the Portfolio data. The other 2 don't find any data and just move on. Once the package stops, if I simply execute those tasks they run and load the data correctly.
It seems to me to be a caching or an isolation problem but I can't find a solution.
Any ideas?
View 7 Replies
View Related
