A difficult Combining Rows problem

Greetings,
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in one hour chunks, like below:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
This involves several things:
- Recognizing that there are variable # of rows (maybe we only have 3
15 minute entries instead of 4)
- Getting a min of those row's min column
- Getting a max of those row's max column
- Getting a total for those row's points column
- Input to any view or whatver would be based on the startTime and
endTime and would always be in whole hours.
I have a feeling that I am going to be doing this all in the C# .NET
end of things, but it's at least worth a shot asking all of you SQL
experts. What I am basically interested in knowing is, do you all
think that this is possible using views or stored procedures or
something else I don't know about. I didn't even know about views
until i started researching how to do this.
Any ideas? Is this possible? Should I just give up and do it on the
C# end of things? Seems to me that it might be possible to do in a
stored procedure, but possible not worth my time. I aprpeciate any
help or suggestions.
JasonTry this:
SELECT groupid,
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'20050101')),
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'2005-01-01T00:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(HH,'20050101',starttime) ;
David Portas
SQL Server MVP
--|||Hi
Check out the dateadd/datepart functions in Books Online for rounding times.
Try:
SELECT GROUPID, DATEADD(mi,-DATEPART(mi,Starttime),Starttime) AS StartTime,
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(mi,-DATEPART(mi,Starttime),Starttime),
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
John
"Factor" wrote:

> Greetings,
> I'm working to combine rows based on a time window and I am hoping to
> be able to write a stored procedure to do this for me, rather than have
> parse through all this data in my program. I'm not very well versed
> with T-SQL syntax.. just enough to get by selecting using inner joins,
> updating and inserting... thats about it. (Hence why I am here.)
> The raw data I have below looks like this:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
> 1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
> 1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
> 1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
> 1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
> 1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
> 1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
> 1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
> Pretty straight forward; you can see each entry is a 15 minute time
> interval. What I want to be able to do is to use a view or a stored
> procedure to view this in one hour chunks, like below:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
> 1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
> This involves several things:
> - Recognizing that there are variable # of rows (maybe we only have 3
> 15 minute entries instead of 4)
> - Getting a min of those row's min column
> - Getting a max of those row's max column
> - Getting a total for those row's points column
> - Input to any view or whatver would be based on the startTime and
> endTime and would always be in whole hours.
> I have a feeling that I am going to be doing this all in the C# .NET
> end of things, but it's at least worth a shot asking all of you SQL
> experts. What I am basically interested in knowing is, do you all
> think that this is possible using views or stored procedures or
> something else I don't know about. I didn't even know about views
> until i started researching how to do this.
> Any ideas? Is this possible? Should I just give up and do it on the
> C# end of things? Seems to me that it might be possible to do in a
> stored procedure, but possible not worth my time. I aprpeciate any
> help or suggestions.
> Jason
>|||John Bell and David Portas,
I will have to read up on these Dateadd/DatePart parameters an actually
interpret what is going on within these statements, but just from what
you gave me here it looks like this will work out very well, and I
really appreciate the insight. This will allow me to vary that time
window fairly easily I do believe, all on a SQL call (that's much
better than bringing back all the data and parsing through it all it.
Thanks again,
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still as to why the min / max /
total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still as to why the min / max /
total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||On 10 Nov 2005 11:41:54 -0800, Factor wrote:

>John
>I have read over those functions and I now understand what they do and
>how to use them, but I am still as to why the min / max /
>total fields actually work. I assume it has something to do with the
>GROUP BY statements, but again, I don't know why.
Hi Jason,
Correct. The GROUP BY tells SQL Server to combine the data from several
rows into one row. This is normally used to report totals, minimum,
maximum per project, per section, etc. But with the appropriate
expression, it cal also be used to combine rows that fit in the same
"period" into one group.
Though John's and David's versions both work, I suggest you go with
Davids version, as this is more flexible. (And, once you get your head
around it, easier to understand as well).
Basically, John's version works by taking each of the date parts you
want to disregard (milliseconds, seconds, minutes), then subtracting
that amount of time from the Starttime. The end result will of course be
the last full hour equal to or before Starttime.
David's version works the other way around - it calculates the number of
full hours that have elapsed since a chosen anchor date, then adds that
number to the chosen anchor date. The result will be the same as John's
expression.
(Note: David chose to just use the number of hours for the group by, and
add it back to the anchor date in the SELECT clause only)

>Assuming black magic happens and thats just how it works, I should just
>be able to change those hh,1 to hh,4 and get 4 hour increments instead.
No. I'll give you two examples how to modify David's query to report on
4-hour intervals and to report on 1/2-hour intervals.
For 4-hour intervals, again calculate the number of hours since an
anchor date. Divide by 4 and truncate, then multiply by 4 again. Add
this number of hours to the anchor date. There you have the start of the
last 4-hour interval
SELECT groupid,
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'20050101')),
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'2005-01-01T03:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
For 1/2-hour intervals, we can't divide the number of hours sice the
anchor date by 0.5, as that won't give us back the precision we already
lost. Instead, we'll have to calculate minutes and divide by 30:
SELECT groupid,
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'20050101')),
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'2005-01-01T00:29:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
In both cases, don't forget to change the shifted anchor value in the
expression for the end point of the interval. Instead of using the same
anchor date, then adding 30 minunte or 4 hours minus one second, the
anchor date is shifted by 30 minutes or 4 hours minus one second.
Now, the above code can still be simplified further. If your table
always has the complete data (as your sample roiws indicate), then you
could change the above queries to:
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
-- or: GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
Note that this might show "holes" in the periods if your real data is
not as complete as the sample you posted indicates. But the advantage is
that you get rid of the "shifted" anchor date for calculating end time.
Final step would be to put it in a stored procedure and use a parameter
for the interval length (in minutes):
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) / @.Interval;
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Jason,
What David Provided is an Excellent query .
Let us see if this query can help you.
Select GID , Min(STime) , Max(ETime) ,
Min(Minimum),Max(Maximum),Sum(Points)[co
lor=darkred]
>From yourTableName Group By[/color]
GID,Convert(varchar,STime,112),DatePart(
hh,STime)
Having same name as Functions/ Keyword sound confusing to me so I
changed them.
With Warm Regards
Jatinder Singh|||Hi
This is easier with David's method (see Hugo's reply for an explanation).
Dividing the number of hours by 4 and dropping the remainder will give you 4
hour chunks when they are multiplied back up. You also need to change the en
d
time to give a 4 hour gap.
SELECT groupid,
MIN(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'20050101')
) AS Starttime,
MAX(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'2005-01-01T03:59:59')
) AS Endtime,
MIN(min) AS [Min],
MAX(max) AS [Max],
SUM(points) AS [Total Points]
FROM Readings
GROUP BY groupid,
4*(DATEDIFF(HH,'20050101',starttime)/4)
John
"Factor" wrote:

> John
> I have read over those functions and I now understand what they do and
> how to use them, but I am still as to why the min / max /
> total fields actually work. I assume it has something to do with the
> GROUP BY statements, but again, I don't know why.
> Assuming black magic happens and thats just how it works, I should just
> be able to change those hh,1 to hh,4 and get 4 hour increments instead.
> When I do that, the Starttime and Endtime values do return correctly
> (although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
> the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
> that black magic that is limiting the MIN/MAX/SUM to one hour is still
> limiting them to one hour even with the altered start and end times.
> I'm unsure how to fix or get around this because I don't yet understand
> what is limiting that max to an hour in the first place. How does this
> work? I've been tripped up GROUP BY things before, it's my kryptonite
> for some reason.
> Hope that is not too confusing, I'm all jumbled in my head.
> I really apprecaite the help with this so far, you've all been
> wonderful.
> Jason
>|||Wondeful! Lots ot take in, I thank everyone for their help. I've made
a lot of progress and I've learned a TON about SQL int he past two
days.
I hope I can help you all in the future with something!
Thanks again,
Jason

A Common Query Question!

Hi Guys,

I have a query that gives me multiple rows for every customer, where as my requirement is to have one row for each customer. Below are the details:

Customers Table

Cust_ID int (PK)

Cust_Name varchar(255)

Subscription_Status int

Cust_Subscription_Audit Table

ID Identity(PK)

Cust_ID int

Old_Subscription_Status int

New_Subscription_Status int

Audit_Date DateTime

Logins Table (one customer may have multiple logins )

ID Identity (PK)

Cust_ID int

login name varchar(256)

email_ID varchar(256)

F_Name varchar(50)

L_name varchar(50)

role int

Last_Modified DateTime

INDEXES

Logins Table

PK__Clustered Ascending

No Index on Audit Table

Customers Table

PK__Clustered_ Ascending

Below is the query that is being used to extract the data

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID,MAX(CSA.Audit_Date)

FROM Customers C

INNERJOIN Cust_Subscription_Audit CSA ON C.Cust_ID= CSA.Cust_ID

INNERJOIN Logins L ON L.Cust_ID= C.Cust_ID

WHERE C.Subscription_Status >=1 AND L.Role= 1

GROUPBY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

This gives me multiple rows for a customer. What I am looking for is

1) One row for each customer.(Query 1)

2) If possible can pick the row of my choice Like log in that was added first or last based on login or Modified date in login table.(Query 2)

3) If possible can pick the row by row number i.e. if there are multiple logins for a customer then the 2nd login. (Query 3)

Your help will be appreciated.

Thanks

-Leo

Can you post some DDL, including constraints and indexes, sample data and expected result, please?

AMB

|||

Hi ,

Thanks for your prompt reply. Here is the Sample Data

Customers Table

Cust_ID Cust_Name SubsStatus

1 abc corp. 7

2 Adventure Works Inc 1

3 MicroCorn Inc 2

4 Fabrikam 7

5 Goleeeee Inc 5

Logins Table

ID Cust ID Login Email f_Name l_Name Role Date_Modified

1 1 rob1 rob1@.hotmail.com Rob Haany 1 2006-02-28 13:24
2 2 scot1 scot1@.yahoo.com Scot claudia 1 2006-05-24 12:30
3 3 scot2 scot2@.hotmail.com Scot Roy 1 2006-05-24 12:30
4 4 rob NULL RON HARTON 1 2006-08-22 15:26
5 4 qsir qsir@.hotmail.com Q Sir 1 2006-09-17 12:23
6 5 nzaar nzaar@.hotmail.com nad zaar 3 2006-09-17 12:23
7 5 jluk jluk@.hotmail.com Ron Richard 1 2006-09-17 12:23
8 2 drase draze@.hotmail.com Dino Mosoli 1 2006-08-22 15:26
9 2 dlins David Lin 3 2006-12-28 13:24
10 4 dmay NULL David May 1 2006-05-24 12:30

Role 1 = admin

Role 3 = Primary Contact

ID Cust_ID Old_Subscription_Status New_Subscription_Status Audit_Date

1 1 3 7 2006-09-24 12:30

2 2 0 1 2006-03-24 02:30

3 3 1 2 2006-08-24 10:30

4 4 1 7 2006-11-24 12:30

5 5 3 5 2006-09-24 12:30

6 4 3 1 2006-10-24 12:30

7 3 0 1 2006-06-24 12:30

8 1 0 1 2006-02-24 12:30

9 1 1 3 2006-07-24 12:30

10 5 1 3 2006-07-24 12:30

The required result is

Cust_ID Cust_Name F_name L_Name Email Audit Date

1 abc Corp Rob Haany rob1@.hotmail.com 2006-09-24 12:30

2 Adventure Works Inc Scot claudia scot1@.yahoo.com 2006-03-24 02:18

3 MicroCorn Inc Scot Roy scot2@.hotmail.com 2006-08-24 10:28

4 Fabrikam Q Sir qsir@.hotmail.com 2006-11-24 12:30

5 Goleeee Inc Ron Richard jluk@.hotmail.com 2006-09-24 12:30

Right now what is happening is threre are more than one records for Cust_ID = 2. The basic question is (Query 1) is how can we restrict the result set to only one row per Cust_ID. So my other questions (Query 2, Query3) were how can we choose one of these records based on some criteria e.g. since more than one records are there for Cust_ID = 2, and I wana choose the one that is with earliest Date_Modified or 2nd earliest Modified date.

Thanks,

-Leo.

|||

hi Leo,

If you are using SQL server 2005 Try this:

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)

FROM Customers C

INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID

CROSS APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID ) L

WHERE C.Subscription_Status >=1 AND L.Role= 1

GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

|||

Thank you very much for this nice hint. I tweeked the query little bit and it woked for me here is the modified query.

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)

FROM Customers C

INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID

OUTER APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID And [Role] = 1 ) L

WHERE C.Subscription_Status >=1

GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

|||do you need OUTER APPLU here? OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function (pretty much like OUTER JOIN in case of normal table). So if there is no match in table 'Logins' the value of L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID will be NULL.

A "not in"-lookup?

Hi,

This may be an easy one, but I can't seem to crack it:
What would be the best way to implement a "not in"-loopkup. I want all the rows in my dataflow, where the key is not found in a specific table, to proceed in the data flow.

I could modify my data source only selecting the rows I'm interested in. However I don't like this solution as it is not very transparent.

I could also just use a Loopup transformation and then just use the Error Output for the rest of the flow. I don't like this solution either as it is a reverse approach.

Is there a third, and better, solution or will I have to go with one of the two above?

Regards,
Sune

Sune Hansen wrote:

Hi,

This may be an easy one, but I can't seem to crack it:
What would be the best way to implement a "not in"-loopkup. I want all the rows in my dataflow, where the key is not found in a specific table, to proceed in the data flow.

I could modify my data source only selecting the rows I'm interested in. However I don't like this solution as it is not very transparent.

I could also just use a Loopup transformation and then just use the Error Output for the rest of the flow. I don't like this solution either as it is a reverse approach.

Is there a third, and better, solution or will I have to go with one of the two above?

Regards,
Sune

Sune,
Using the error output is a legitamate approach and doesn't violate accepted best practice. What is your issue with using it?

-Jamie|||Wow - that was quick Thanks.

My only issue was that I found it a little odd to use Error Output - there is no error.

Thanks again,
- Sune|||

Sune Hansen wrote:

Wow - that was quick Thanks.

My only issue was that I found it a little odd to use Error Output - there is no error.

Thanks again,
- Sune

Yes, I'll grant you that. But don't worry - this is considered best practice.

-Jamie|||You could also use a MergeJoin and then use a conditional split to filter out any rows where the value of the column from the "lookup" table is NULL to a not in output (on the split) and then use that output for the rest of the flow. I don't know that this is any more performant (or less) than using the lookup's error output but it does keep you from using the "error" output when as you say there is no error. Just another alternative.

Thanks,
Matt|||Yeah... it is quick.

But, use it only if you aredealing with few rows.
When I was working with rows in the order of few hundred thousands to couple of millions, using error output was really slow. Possible reason is that new buffers are assigned for holding error output and then data is copied over from input buffers to the error output buffers. So, a work around to the peformance problem was to break it into 2 steps. In first step, perform lookup as earlier but this time select the ID column for that table. Now take the regular output and feed it into a conditional split transform. Put an expression like ISNULL(<ID Column name>). Output of conditional split will give you the desired not-in lookup.

HTH,
Nitesh|||

Nitesh Ambastha wrote:

Yeah... it is quick.

But, use it only if you aredealing with few rows.
When I was working with rows in the order of few hundred thousands to couple of millions, using error output was really slow. Possible reason is that new buffers are assigned for holding error output and then data is copied over from input buffers to the error output buffers. So, a work around to the peformance problem was to break it into 2 steps. In first step, perform lookup as earlier but this time select the ID column for that table. Now take the regular output and feed it into a conditional split transform. Put an expression like ISNULL(<ID Column name>). Output of conditional split will give you the desired not-in lookup.

HTH,
Nitesh

And that worked quicker? By what order of magnitude?

Wow, that's really valuable stuff to know. Thanks Nitesh.

-Jamie|||

Jamie Thomson wrote:

And that worked quicker? By what order of magnitude?

Wow, that's really valuable stuff to know. Thanks Nitesh.

-Jamie

Jamie,

I did not do any measures/benchmarks (yet).

But, in my packages I have lots of data flow tasks. And within each data flow tasks, I have one or more Ole Db Destinations. Right before each of these destinations, I need to do this not-in lookup. With that in mind, I feel I saw approximately 1.5 to 2 times better timings for the entire package... specially when I was dealing with millions of rows. This was on a 4 processor, 4GB RAM Intel box running Win 2k3 and June CTP.

- Nitesh

9 million rows in msrepl_commands? I don't even have that many records!

Does more than one msrepl_commands record exist for each record updated?
How can I have so many rows in this table? Does anyone know how to
translate the command fields so I can see what the commands are... with so
many records sp_browsereplcmds isn't even responding.
Thanks
the msrepl_command will also contain the sync commands which are necessary
to deploy your snapshot on the subscriber. If the particular command is very
large it may wrap over one row.
However what probably has caused so many rows to fill up this table is the
fact that all transactions that occur on the publisher are converted into
singletons, so a single insert/update/delete statement that affects 100 rows
on the publisher will be converted into 100 insert/update/delete statements
on the publisher.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Ray Price" <ray.price@.gartner.com> wrote in message
news:OqL%23Fl$UEHA.3944@.tk2msftngp13.phx.gbl...
> Does more than one msrepl_commands record exist for each record updated?
> How can I have so many rows in this table? Does anyone know how to
> translate the command fields so I can see what the commands are... with so
> many records sp_browsereplcmds isn't even responding.
> Thanks
>

80k active rows when I run DBCC LOGINFO(XXX). Why?

Hello all,
I am having some trouble shrinking my logs which are currently approx. 21GB.
I did some research and found some pages that say the rows should have a
status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
approx 80k rows which all have a status of 2. What does this mean?
I already made a full backup of the database, and a separate backup of the
logs but the results remain the same.
I would appreciate if someone can explain what this means, what can I do
about, and why do I have 80k active rows. Where can I find an explanation of
this so I can learn about it? Thanks!
JohnnyYou probably created the file small and had bunch of autogrowths. Here's a r
eply to similar Q I
posted earlier today:
Log is emptied (2 to 0) then you backup log. Backup log, check if 2 at the e
nd. 2 at the end sets
limit of shrink. What you do is alternate backup log and shrink a few times.
Then you alter database
and pre-allocate a much as you probably need.
In addition, read about the virtual logfile concept in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:CC046CC3-867B-4CB7-AEF7-B81B3F622151@.microsoft.com...
> Hello all,
> I am having some trouble shrinking my logs which are currently approx. 21G
B.
> I did some research and found some pages that say the rows should have a
> status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get ba
ck
> approx 80k rows which all have a status of 2. What does this mean?
> I already made a full backup of the database, and a separate backup of the
> logs but the results remain the same.
> I would appreciate if someone can explain what this means, what can I do
> about, and why do I have 80k active rows. Where can I find an explanation
of
> this so I can learn about it? Thanks!
> Johnny|||Johnny,

> I am having some trouble shrinking my logs which are currently approx. 21G
B.
> I did some research and found some pages that say the rows should have a
> status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get ba
ck
> approx 80k rows which all have a status of 2. What does this mean?
The active part of the transaction log (uncommitted transactions). This can
be caused by long running transactions and can be a problem even if the
databse is using simple recovery model. See "Checkpoints and the Active
Portion of the Log" in BOL.
Try executing "dbcc opentran" to check all active transactions.
AMB
"Johnny" wrote:

> Hello all,
> I am having some trouble shrinking my logs which are currently approx. 21G
B.
> I did some research and found some pages that say the rows should have a
> status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get ba
ck
> approx 80k rows which all have a status of 2. What does this mean?
> I already made a full backup of the database, and a separate backup of the
> logs but the results remain the same.
> I would appreciate if someone can explain what this means, what can I do
> about, and why do I have 80k active rows. Where can I find an explanation
of
> this so I can learn about it? Thanks!
> Johnny|||Sorry, forgot to mention that the active part of the transaction log should
be at the begining, if not sql server can not shrink the transaction log.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Johnny,
>
> The active part of the transaction log (uncommitted transactions). This ca
n
> be caused by long running transactions and can be a problem even if the
> databse is using simple recovery model. See "Checkpoints and the Active
> Portion of the Log" in BOL.
> Try executing "dbcc opentran" to check all active transactions.
>
> AMB
> "Johnny" wrote:
>

80k active rows when I run DBCC LOGINFO(XXX). Why?

Hello all,
I am having some trouble shrinking my logs which are currently approx. 21GB.
I did some research and found some pages that say the rows should have a
status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
approx 80k rows which all have a status of 2. What does this mean?
I already made a full backup of the database, and a separate backup of the
logs but the results remain the same.
I would appreciate if someone can explain what this means, what can I do
about, and why do I have 80k active rows. Where can I find an explanation of
this so I can learn about it? Thanks!
JohnnyYou probably created the file small and had bunch of autogrowths. Here's a reply to similar Q I
posted earlier today:
Log is emptied (2 to 0) then you backup log. Backup log, check if 2 at the end. 2 at the end sets
limit of shrink. What you do is alternate backup log and shrink a few times. Then you alter database
and pre-allocate a much as you probably need.
In addition, read about the virtual logfile concept in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:CC046CC3-867B-4CB7-AEF7-B81B3F622151@.microsoft.com...
> Hello all,
> I am having some trouble shrinking my logs which are currently approx. 21GB.
> I did some research and found some pages that say the rows should have a
> status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
> approx 80k rows which all have a status of 2. What does this mean?
> I already made a full backup of the database, and a separate backup of the
> logs but the results remain the same.
> I would appreciate if someone can explain what this means, what can I do
> about, and why do I have 80k active rows. Where can I find an explanation of
> this so I can learn about it? Thanks!
> Johnny|||Johnny,
> I am having some trouble shrinking my logs which are currently approx. 21GB.
> I did some research and found some pages that say the rows should have a
> status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
> approx 80k rows which all have a status of 2. What does this mean?
The active part of the transaction log (uncommitted transactions). This can
be caused by long running transactions and can be a problem even if the
databse is using simple recovery model. See "Checkpoints and the Active
Portion of the Log" in BOL.
Try executing "dbcc opentran" to check all active transactions.
AMB
"Johnny" wrote:
> Hello all,
> I am having some trouble shrinking my logs which are currently approx. 21GB.
> I did some research and found some pages that say the rows should have a
> status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
> approx 80k rows which all have a status of 2. What does this mean?
> I already made a full backup of the database, and a separate backup of the
> logs but the results remain the same.
> I would appreciate if someone can explain what this means, what can I do
> about, and why do I have 80k active rows. Where can I find an explanation of
> this so I can learn about it? Thanks!
> Johnny|||Sorry, forgot to mention that the active part of the transaction log should
be at the begining, if not sql server can not shrink the transaction log.
AMB
"Alejandro Mesa" wrote:
> Johnny,
> > I am having some trouble shrinking my logs which are currently approx. 21GB.
> > I did some research and found some pages that say the rows should have a
> > status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
> > approx 80k rows which all have a status of 2. What does this mean?
> The active part of the transaction log (uncommitted transactions). This can
> be caused by long running transactions and can be a problem even if the
> databse is using simple recovery model. See "Checkpoints and the Active
> Portion of the Log" in BOL.
> Try executing "dbcc opentran" to check all active transactions.
>
> AMB
> "Johnny" wrote:
> > Hello all,
> >
> > I am having some trouble shrinking my logs which are currently approx. 21GB.
> > I did some research and found some pages that say the rows should have a
> > status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
> > approx 80k rows which all have a status of 2. What does this mean?
> >
> > I already made a full backup of the database, and a separate backup of the
> > logs but the results remain the same.
> >
> > I would appreciate if someone can explain what this means, what can I do
> > about, and why do I have 80k active rows. Where can I find an explanation of
> > this so I can learn about it? Thanks!
> >
> > Johnny

80k active rows when I run DBCC LOGINFO(XXX). Why?

Hello all,
I am having some trouble shrinking my logs which are currently approx. 21GB.
I did some research and found some pages that say the rows should have a
status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
approx 80k rows which all have a status of 2. What does this mean?
I already made a full backup of the database, and a separate backup of the
logs but the results remain the same.
I would appreciate if someone can explain what this means, what can I do
about, and why do I have 80k active rows. Where can I find an explanation of
this so I can learn about it? Thanks!
Johnny
You probably created the file small and had bunch of autogrowths. Here's a reply to similar Q I
posted earlier today:
Log is emptied (2 to 0) then you backup log. Backup log, check if 2 at the end. 2 at the end sets
limit of shrink. What you do is alternate backup log and shrink a few times. Then you alter database
and pre-allocate a much as you probably need.
In addition, read about the virtual logfile concept in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:CC046CC3-867B-4CB7-AEF7-B81B3F622151@.microsoft.com...
> Hello all,
> I am having some trouble shrinking my logs which are currently approx. 21GB.
> I did some research and found some pages that say the rows should have a
> status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
> approx 80k rows which all have a status of 2. What does this mean?
> I already made a full backup of the database, and a separate backup of the
> logs but the results remain the same.
> I would appreciate if someone can explain what this means, what can I do
> about, and why do I have 80k active rows. Where can I find an explanation of
> this so I can learn about it? Thanks!
> Johnny
|||Johnny,

> I am having some trouble shrinking my logs which are currently approx. 21GB.
> I did some research and found some pages that say the rows should have a
> status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
> approx 80k rows which all have a status of 2. What does this mean?
The active part of the transaction log (uncommitted transactions). This can
be caused by long running transactions and can be a problem even if the
databse is using simple recovery model. See "Checkpoints and the Active
Portion of the Log" in BOL.
Try executing "dbcc opentran" to check all active transactions.
AMB
"Johnny" wrote:

> Hello all,
> I am having some trouble shrinking my logs which are currently approx. 21GB.
> I did some research and found some pages that say the rows should have a
> status of 0, not 2. When I run the "DBCC LOGINFO(XXX)" statement, I get back
> approx 80k rows which all have a status of 2. What does this mean?
> I already made a full backup of the database, and a separate backup of the
> logs but the results remain the same.
> I would appreciate if someone can explain what this means, what can I do
> about, and why do I have 80k active rows. Where can I find an explanation of
> this so I can learn about it? Thanks!
> Johnny
|||Sorry, forgot to mention that the active part of the transaction log should
be at the begining, if not sql server can not shrink the transaction log.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Johnny,
>
> The active part of the transaction log (uncommitted transactions). This can
> be caused by long running transactions and can be a problem even if the
> databse is using simple recovery model. See "Checkpoints and the Active
> Portion of the Log" in BOL.
> Try executing "dbcc opentran" to check all active transactions.
>
> AMB
> "Johnny" wrote:

'8007007F' error

Hi,
I have a problem with my SQL Server 2000.
When I try to display the rows from a table with enterprise manager, I get a
'8007007F' error
I don't have this problem if I read the table from a Query using the Query
Anlyzer.
I use Windows 2003 Server and I installed the MDAC 8.x
This problem also occurs when I try to open databases from Active Server
pages
Any ideas ?
Stan.I'm experiencing the same issues after upgrading from Windows Server 2000 to
Windows Server 2003. I receive error '8007007F' when i try to query an ASP
page|||Hi Shaheen,
My problem was not a ASP problem but a DLL problem
I was not able to view my datatable with Enterprise Manager.
If you have the same problem in this case, check this url out.
http://groups.google.com/groups?q=%...0phx.gbl&rnum=1
Hope that helps.
Stan
"Shaheen" <anonymous@.discussions.microsoft.com> a crit dans le message de
news:AE4ADF75-0850-4D7D-9488-EF484AFC77A3@.microsoft.com...
> I'm experiencing the same issues after upgrading from Windows Server 2000
to Windows Server 2003. I receive error '8007007F' when i try to query an
ASP page|||I had the same problem after upgrading to Windows 2003 Server. I called Mic
rosoft and here is the fix:
Symptoms:
After upgrading from Windows 2000 to Windows 2003 attempting to access a dat
abase or data component will result in a '8007007f' or "The specified proced
ure could not be found" error.
Status:
This is a known issue with some installations of Windows 2003
Workaround:
Extract oledb32.dll from the zip file into these two directories. It's impo
rtant that it be done in this order:
1) C:\WINNT\system32\dllCache
2) C:\Program Files\Common Files\System\OLE DB
3) Reboot the server
Cause:
This issue is caused when the Windows 2003 installer did not update the oled
b32.dll file.
You can dowload the oledb32.dll file here: [url]http://www.promiseweb.com/oledb32
.zip[/
url]
This is per Malcolm Stewart at Microsoft Developer Support

'8007007F' error

Hi,
I have a problem with my SQL Server 2000.
When I try to display the rows from a table with enterprise manager, I get a
'8007007F' error
I don't have this problem if I read the table from a Query using the Query
Anlyzer.
I use Windows 2003 Server and I installed the MDAC 8.x
This problem also occurs when I try to open databases from Active Server
pages
Any ideas ?
Stan.I'm experiencing the same issues after upgrading from Windows Server 2000 to Windows Server 2003. I receive error '8007007F' when i try to query an ASP page|||Hi Shaheen,
My problem was not a ASP problem but a DLL problem
I was not able to view my datatable with Enterprise Manager.
If you have the same problem in this case, check this url out.
http://groups.google.com/groups?q=%228007007F+the+fix%22&hl=fr&lr=&ie=UTF-8&selm=081601c309fd%242ac4c3e0%24a001280a%40phx.gbl&rnum=1
Hope that helps.
Stan
"Shaheen" <anonymous@.discussions.microsoft.com> a écrit dans le message de
news:AE4ADF75-0850-4D7D-9488-EF484AFC77A3@.microsoft.com...
> I'm experiencing the same issues after upgrading from Windows Server 2000
to Windows Server 2003. I receive error '8007007F' when i try to query an
ASP page

6.5 reasons for slow inserts and blocking

Hallo!

I have a table in sql 6.5SP5a with 2`130`698 rows. The table has a primary
key on identity column and an insert trigger that updates the inserted row
according to some logic. Recently I've noticed that some amount of
intermittent blocking is occuring. It happens when the stored procedure that
inserts a new row in the table is receiving simultaneous calls. The
procedure looks something like this:

create procedure pr_insert_or_update
@.mode tinyint,
@.id int,
@.param1 int,
@.param2 datetime,
@.param3 varchar(3)

as

if @.mode=0
begin

insert into Table(column1, column2, column3, column4, column5)
values(param1, param2, param3, user_name(), host_name())

select @.@.identity as identity_column,
column_updated_by_the_trigger
from Table where identity_column=@.@.identity

end

else
begin

update table set column1=@.param1, column2=@.param2,
column3=@.param3, column4=user_name(), column5=host_name()
where identity_column=@.id

select @.id

end

The syprocesses waittype, status, and cmd entries for the suid at the head
of a blocking chain are usually 0x0000, runnable, and SELECT accordingly.
Although cmd is SELECT, DBCC OPENTRAN for the (user) database shows that the
oldest open transaction is "insert" and it belongs to the suid at the head
of a blocking chain. Also the exclusive blocking page locks are held.
I used the blocker script from Microsoft to gather this information. Still,
I'm stuck with finding out what is the cause of blocking since the waittype
is usually 0x0000 and DBCC PSS doesn't return any result in my case.

Any ideas on what could have caused/how to resolve blocking? Could it be
slow network when users are holding page locks for too long? Maybe I should
consider switching on "insert row lock" for the Table?

Many thanks!Two things come to mind.

1) It appears to me that you have a trigger on the table, column_updated_by_the_trigger, so the INSERT transaction is actually a couple of SQL statements linked together into 1 transaction. The INSERT plus any SQL statements within the trigger. So locks on the INSERT are being held until the trigger has been completed.

2) Once the trigger has completed, the statement will auto commit or auto rollback, I say this because I see no BEGIN TRAN, and I'm assuming the application that calls this has not issued it and you have the default settings, Implicit Transactions set to false.
So when the INSERT finishes and you have released the locks the next process that is in-line to execute the INSERT statement will happen, however the first process now will execute the SELECT, but will be locked out by the second process doing the INSERT.
Two options to solve this (a) Place a BEGIN TRAN prior to the INSERT and a COMMIT TRAN after the SELECT or (b) Place the hint (nolock) after the table name in the SELECT.

4G table's delete uses 20G log

I've a table with a size of ~4G and 6million rows in SQL server 2005.
Shen deleting all rows from it it takes closer to an hour and uses
~20G of transaction log. I wasn't able to truncate it due to FK's, but
should I not be able to truncat it by disabling FKs - how can I do
that? Or how can I expedite this delete because it;s annoying to let
server use 20G log for 4G table. My DB recovery model is set to SIMPLE
(for minimum logging)
TIA,
DataDealer
Do not delete with one big batch, instead , divide it into small batches
SET ROWCOUNT 1000
delete_more:
DELETE .....
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer
|||Recovery model do not affect the amount of logging for a DELETE operation.
You cannot TRUNCATE TABLE as long as a FK is referencing that table. It doesn't help if you disable
that constraint. How about dropping the constraint, TRUNCATE TABLE and then adding it back? That
will by far be the quickest way. If that doesn't suit you, follow Uri's advice to delete in batches
so not all is in one large transaction.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer

4G table's delete uses 20G log

I've a table with a size of ~4G and 6million rows in SQL server 2005.
Shen deleting all rows from it it takes closer to an hour and uses
~20G of transaction log. I wasn't able to truncate it due to FK's, but
should I not be able to truncat it by disabling FKs - how can I do
that? Or how can I expedite this delete because it;s annoying to let
server use 20G log for 4G table. My DB recovery model is set to SIMPLE
(for minimum logging)
TIA,
DataDealerDo not delete with one big batch, instead , divide it into small batches
SET ROWCOUNT 1000
delete_more:
DELETE .....
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer|||Recovery model do not affect the amount of logging for a DELETE operation.
You cannot TRUNCATE TABLE as long as a FK is referencing that table. It doesn't help if you disable
that constraint. How about dropping the constraint, TRUNCATE TABLE and then adding it back? That
will by far be the quickest way. If that doesn't suit you, follow Uri's advice to delete in batches
so not all is in one large transaction.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<Nasir111@.gmail.com> wrote in message
news:4285be79-87e3-4711-8fe6-344db8f3d77b@.s8g2000prg.googlegroups.com...
> I've a table with a size of ~4G and 6million rows in SQL server 2005.
> Shen deleting all rows from it it takes closer to an hour and uses
> ~20G of transaction log. I wasn't able to truncate it due to FK's, but
> should I not be able to truncat it by disabling FKs - how can I do
> that? Or how can I expedite this delete because it;s annoying to let
> server use 20G log for 4G table. My DB recovery model is set to SIMPLE
> (for minimum logging)
> TIA,
> DataDealer

4 million queries, or 4 million rows

Hello!
I need to verify that about 4 million rows were correctly inserted into
the database. Should i do this using one query returning 4 million rows,
or should i query the database 4 million times and returning one row each
query?
The result of the query/queries will be processed in a client application.
Does it matter?
I would prefer the latter, if it can finish in a reasonable amount of time.
Thanks!
Jon
"Jon" <jon@.noreply> wrote in message
news:xn0f10jy5j55qzl00t@.news.microsoft.com...
> Hello!
> I need to verify that about 4 million rows were correctly inserted into
> the database. Should i do this using one query returning 4 million rows,
> or should i query the database 4 million times and returning one row each
> query?
>
Neither. Surely it would make far more sense to write a query that verifies
the result server-side. That way you need only return a Yes/No answer or
some other aggregate or exception result.

> The result of the query/queries will be processed in a client application.
> Does it matter?
> I would prefer the latter, if it can finish in a reasonable amount of
> time.
>
It matters! But if client-side row-by-row processing is an absolute
requirement then you'd better test performance for yourself. I don't know
what you consider reasonable.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi Jon
"Jon" wrote:

> Hello!
> I need to verify that about 4 million rows were correctly inserted into
> the database. Should i do this using one query returning 4 million rows,
> or should i query the database 4 million times and returning one row each
> query?
What do you mean by this? If you inserted 4 million rows and no error status
was returned and the transaction was committed then they will be inserted! If
you need to make sure specific values are in a given column then make sure
that you have the correct column/table constraints in place.
>
John
|||The data is not imported from another database, it is file(s) that needs
to be processed before inserting into the database. I need to verify that
the application doing this, does the right thing (which it currently
doesn't). I am not really a pro on T-SQL (can i read from a file using
T-SQL?), so i feel more comfortable doing it in a "normal" application.
I have tried returning all rows in one query, and it takes about 20
minutes to verify the data. The problem is that when elements are missing
from the database, reading the next element from the file and next element
from the database gives a mis-match, because i am simply comparing wrong
items. I can of course "synchronize" this, but i want to keep down the
development time, so if it is just a small time difference, i may just do
it easy for me and not complicate it (the more complicated my code is, the
greater risk of me doing something wrong, which could mean that the
application verifying the insertion has a bug...).
Running time is not critical (it will just be used a few times) and i
would consider about 60 minutes to still be workable to verify the
complete database).
David Portas wrote:

>"Jon" <jon@.noreply> wrote in message
>news:xn0f10jy5j55qzl00t@.news.microsoft.com...
>Neither. Surely it would make far more sense to write a query that
>verifies the result server-side. That way you need only return a Yes/No
>answer or some other aggregate or exception result.
>
>It matters! But if client-side row-by-row processing is an absolute
>requirement then you'd better test performance for yourself. I don't know
>what you consider reasonable.
|||Hi,
The insertion is done by another application, reading text files,
processing each line in the text file, and inserting it into the database.
Unfortunately, it does not log errors, and there can also be a bug in the
application (so i need to verify not only that the element exists, but
also that the data is correct).
Verifying that the data is correct cannot be done using a constraint,
because it can possibly have inserted the data from another element into
the database (for example reading the wrong line in the file).
Jon
John Bell wrote:

>Hi Jon
>"Jon" wrote:
>
>What do you mean by this? If you inserted 4 million rows and no error
>status
>was returned and the transaction was committed then they will be inserted!
>If
>you need to make sure specific values are in a given column then make sure
>that you have the correct column/table constraints in place.
>John
|||Hi Jon
"Jon" wrote:

> Hi,
> The insertion is done by another application, reading text files,
> processing each line in the text file, and inserting it into the database.
> Unfortunately, it does not log errors, and there can also be a bug in the
> application (so i need to verify not only that the element exists, but
> also that the data is correct).
>
Have you considered BCP which will create a file of records that have
errored? Possibly a DTS/SSIS package could process this better?

> Verifying that the data is correct cannot be done using a constraint,
> because it can possibly have inserted the data from another element into
> the database (for example reading the wrong line in the file).
It sounds like you need to improve the program that does the inserts or the
quality of the data!
> --
> Jon
>
John
|||"Jon" <jon@.noreply> wrote in message
news:xn0f10m5sj87sy300v@.news.microsoft.com...
> Hi,
> The insertion is done by another application, reading text files,
> processing each line in the text file, and inserting it into the database.
> Unfortunately, it does not log errors, and there can also be a bug in the
> application (so i need to verify not only that the element exists, but
> also that the data is correct).
> Verifying that the data is correct cannot be done using a constraint,
> because it can possibly have inserted the data from another element into
> the database (for example reading the wrong line in the file).
>
Maybe you could load the file to the server independently and then compare
the two results with a query. Perhaps that seems an odd idea given that you
already have an application doing the same. But if it's possible to bulk
load the file using BCP then I would expect you to see some improvement on
the 20 minute running time you mentioned. Take a look at the BCP topic in
Books Online.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||In the first place, I wanted to bulk load the data as-is into the
database, but the db admin said no to this. I was not given any reason for
this. :-(
So I am looking into what other options i have to do this job.
You don't think it is reasonable to query the database a couple of million
times?
David Portas wrote:

>"Jon" <jon@.noreply> wrote in message
>news:xn0f10m5sj87sy300v@.news.microsoft.com...
>Maybe you could load the file to the server independently and then compare
>the two results with a query. Perhaps that seems an odd idea given that
>you already have an application doing the same. But if it's possible to
>bulk load the file using BCP then I would expect you to see some
>improvement on the 20 minute running time you mentioned. Take a look at
>the BCP topic in Books Online.
|||Yes, the program will be improved. But to do this, we need to figure out
what is wrong, hence why i am doing this.
I don't think they are open to loading the data using a complete new
process. I think they want to stick with the original application.

John Bell wrote:

>Hi Jon
>"Jon" wrote:
>Have you considered BCP which will create a file of records that have
>errored? Possibly a DTS/SSIS package could process this better?
>
>It sounds like you need to improve the program that does the inserts or the
>quality of the data!
>John
|||No, it is not on a live system. But the problem still remains, i need to
somehow compare the data from the files with the data that is loaded in
the database, and the question is what is best to do.
John Bell wrote:

>Hi Jon
>
>If that is the case then you will will probably want to take a copy of the
>database and not work on the live system!
>John

4 million queries, or 4 million rows

Hello!
I need to verify that about 4 million rows were correctly inserted into
the database. Should i do this using one query returning 4 million rows,
or should i query the database 4 million times and returning one row each
query?
The result of the query/queries will be processed in a client application.
Does it matter?
I would prefer the latter, if it can finish in a reasonable amount of time.
Thanks!
Jon"Jon" <jon@.noreply> wrote in message
news:xn0f10jy5j55qzl00t@.news.microsoft.com...
> Hello!
> I need to verify that about 4 million rows were correctly inserted into
> the database. Should i do this using one query returning 4 million rows,
> or should i query the database 4 million times and returning one row each
> query?
>
Neither. Surely it would make far more sense to write a query that verifies
the result server-side. That way you need only return a Yes/No answer or
some other aggregate or exception result.

> The result of the query/queries will be processed in a client application.
> Does it matter?
> I would prefer the latter, if it can finish in a reasonable amount of
> time.
>
It matters! But if client-side row-by-row processing is an absolute
requirement then you'd better test performance for yourself. I don't know
what you consider reasonable.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi Jon
"Jon" wrote:

> Hello!
> I need to verify that about 4 million rows were correctly inserted into
> the database. Should i do this using one query returning 4 million rows,
> or should i query the database 4 million times and returning one row each
> query?
What do you mean by this? If you inserted 4 million rows and no error status
was returned and the transaction was committed then they will be inserted! I
f
you need to make sure specific values are in a given column then make sure
that you have the correct column/table constraints in place.
>
John|||The data is not imported from another database, it is file(s) that needs
to be processed before inserting into the database. I need to verify that
the application doing this, does the right thing (which it currently
doesn't). I am not really a pro on T-SQL (can i read from a file using
T-SQL?), so i feel more comfortable doing it in a "normal" application.
I have tried returning all rows in one query, and it takes about 20
minutes to verify the data. The problem is that when elements are missing
from the database, reading the next element from the file and next element
from the database gives a mis-match, because i am simply comparing wrong
items. I can of course "synchronize" this, but i want to keep down the
development time, so if it is just a small time difference, i may just do
it easy for me and not complicate it (the more complicated my code is, the
greater risk of me doing something wrong, which could mean that the
application verifying the insertion has a bug...).
Running time is not critical (it will just be used a few times) and i
would consider about 60 minutes to still be workable to verify the
complete database).
David Portas wrote:

>"Jon" <jon@.noreply> wrote in message
>news:xn0f10jy5j55qzl00t@.news.microsoft.com...
>Neither. Surely it would make far more sense to write a query that
>verifies the result server-side. That way you need only return a Yes/No
>answer or some other aggregate or exception result.
>
>It matters! But if client-side row-by-row processing is an absolute
>requirement then you'd better test performance for yourself. I don't know
>what you consider reasonable.|||Hi,
The insertion is done by another application, reading text files,
processing each line in the text file, and inserting it into the database.
Unfortunately, it does not log errors, and there can also be a bug in the
application (so i need to verify not only that the element exists, but
also that the data is correct).
Verifying that the data is correct cannot be done using a constraint,
because it can possibly have inserted the data from another element into
the database (for example reading the wrong line in the file).
Jon
John Bell wrote:

>Hi Jon
>"Jon" wrote:
>
>What do you mean by this? If you inserted 4 million rows and no error
>status
>was returned and the transaction was committed then they will be inserted!
>If
>you need to make sure specific values are in a given column then make sure
>that you have the correct column/table constraints in place.
>John|||Hi Jon
"Jon" wrote:

> Hi,
> The insertion is done by another application, reading text files,
> processing each line in the text file, and inserting it into the database.
> Unfortunately, it does not log errors, and there can also be a bug in the
> application (so i need to verify not only that the element exists, but
> also that the data is correct).
>
Have you considered BCP which will create a file of records that have
errored? Possibly a DTS/SSIS package could process this better?

> Verifying that the data is correct cannot be done using a constraint,
> because it can possibly have inserted the data from another element into
> the database (for example reading the wrong line in the file).
It sounds like you need to improve the program that does the inserts or the
quality of the data!
> --
> Jon
>
John|||"Jon" <jon@.noreply> wrote in message
news:xn0f10m5sj87sy300v@.news.microsoft.com...
> Hi,
> The insertion is done by another application, reading text files,
> processing each line in the text file, and inserting it into the database.
> Unfortunately, it does not log errors, and there can also be a bug in the
> application (so i need to verify not only that the element exists, but
> also that the data is correct).
> Verifying that the data is correct cannot be done using a constraint,
> because it can possibly have inserted the data from another element into
> the database (for example reading the wrong line in the file).
>
Maybe you could load the file to the server independently and then compare
the two results with a query. Perhaps that seems an odd idea given that you
already have an application doing the same. But if it's possible to bulk
load the file using BCP then I would expect you to see some improvement on
the 20 minute running time you mentioned. Take a look at the BCP topic in
Books Online.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Yes, the program will be improved. But to do this, we need to figure out
what is wrong, hence why i am doing this.
I don't think they are open to loading the data using a complete new
process. I think they want to stick with the original application.
John Bell wrote:

>Hi Jon
>"Jon" wrote:
>
>Have you considered BCP which will create a file of records that have
>errored? Possibly a DTS/SSIS package could process this better?
>
>It sounds like you need to improve the program that does the inserts or the
>quality of the data!
>John|||Hi Jon

> Yes, the program will be improved. But to do this, we need to figure out
> what is wrong, hence why i am doing this.
If that is the case then you will will probably want to take a copy of the
database and not work on the live system!
John|||No, it is not on a live system. But the problem still remains, i need to
somehow compare the data from the files with the data that is loaded in
the database, and the question is what is best to do.
John Bell wrote:

>Hi Jon
>
>If that is the case then you will will probably want to take a copy of the
>database and not work on the live system!
>John

4 million queries, or 4 million rows

Hello!
I need to verify that about 4 million rows were correctly inserted into
the database. Should i do this using one query returning 4 million rows,
or should i query the database 4 million times and returning one row each
query?
The result of the query/queries will be processed in a client application.
Does it matter?
I would prefer the latter, if it can finish in a reasonable amount of time.
Thanks!
--
Jon"Jon" <jon@.noreply> wrote in message
news:xn0f10jy5j55qzl00t@.news.microsoft.com...
> Hello!
> I need to verify that about 4 million rows were correctly inserted into
> the database. Should i do this using one query returning 4 million rows,
> or should i query the database 4 million times and returning one row each
> query?
>
Neither. Surely it would make far more sense to write a query that verifies
the result server-side. That way you need only return a Yes/No answer or
some other aggregate or exception result.
> The result of the query/queries will be processed in a client application.
> Does it matter?
> I would prefer the latter, if it can finish in a reasonable amount of
> time.
>
It matters! But if client-side row-by-row processing is an absolute
requirement then you'd better test performance for yourself. I don't know
what you consider reasonable.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The data is not imported from another database, it is file(s) that needs
to be processed before inserting into the database. I need to verify that
the application doing this, does the right thing (which it currently
doesn't). I am not really a pro on T-SQL (can i read from a file using
T-SQL?), so i feel more comfortable doing it in a "normal" application.
I have tried returning all rows in one query, and it takes about 20
minutes to verify the data. The problem is that when elements are missing
from the database, reading the next element from the file and next element
from the database gives a mis-match, because i am simply comparing wrong
items. I can of course "synchronize" this, but i want to keep down the
development time, so if it is just a small time difference, i may just do
it easy for me and not complicate it (the more complicated my code is, the
greater risk of me doing something wrong, which could mean that the
application verifying the insertion has a bug...).
Running time is not critical (it will just be used a few times) and i
would consider about 60 minutes to still be workable to verify the
complete database).
--
David Portas wrote:
>"Jon" <jon@.noreply> wrote in message
>news:xn0f10jy5j55qzl00t@.news.microsoft.com...
>>Hello!
>>I need to verify that about 4 million rows were correctly inserted into
>>the database. Should i do this using one query returning 4 million rows,
>>or should i query the database 4 million times and returning one row each
>>query?
>Neither. Surely it would make far more sense to write a query that
>verifies the result server-side. That way you need only return a Yes/No
>answer or some other aggregate or exception result.
>>The result of the query/queries will be processed in a client application.
>>Does it matter?
>>I would prefer the latter, if it can finish in a reasonable amount of
>>time.
>It matters! But if client-side row-by-row processing is an absolute
>requirement then you'd better test performance for yourself. I don't know
>what you consider reasonable.|||Hi,
The insertion is done by another application, reading text files,
processing each line in the text file, and inserting it into the database.
Unfortunately, it does not log errors, and there can also be a bug in the
application (so i need to verify not only that the element exists, but
also that the data is correct).
Verifying that the data is correct cannot be done using a constraint,
because it can possibly have inserted the data from another element into
the database (for example reading the wrong line in the file).
--
Jon
John Bell wrote:
>Hi Jon
>"Jon" wrote:
>>Hello!
>>I need to verify that about 4 million rows were correctly inserted into
>>the database. Should i do this using one query returning 4 million rows,
>>or should i query the database 4 million times and returning one row each
>>query?
>What do you mean by this? If you inserted 4 million rows and no error
>status
>was returned and the transaction was committed then they will be inserted!
>If
>you need to make sure specific values are in a given column then make sure
>that you have the correct column/table constraints in place.
>John|||"Jon" <jon@.noreply> wrote in message
news:xn0f10m5sj87sy300v@.news.microsoft.com...
> Hi,
> The insertion is done by another application, reading text files,
> processing each line in the text file, and inserting it into the database.
> Unfortunately, it does not log errors, and there can also be a bug in the
> application (so i need to verify not only that the element exists, but
> also that the data is correct).
> Verifying that the data is correct cannot be done using a constraint,
> because it can possibly have inserted the data from another element into
> the database (for example reading the wrong line in the file).
>
Maybe you could load the file to the server independently and then compare
the two results with a query. Perhaps that seems an odd idea given that you
already have an application doing the same. But if it's possible to bulk
load the file using BCP then I would expect you to see some improvement on
the 20 minute running time you mentioned. Take a look at the BCP topic in
Books Online.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||In the first place, I wanted to bulk load the data as-is into the
database, but the db admin said no to this. I was not given any reason for
this. :-(
So I am looking into what other options i have to do this job.
You don't think it is reasonable to query the database a couple of million
times?
--
David Portas wrote:
>"Jon" <jon@.noreply> wrote in message
>news:xn0f10m5sj87sy300v@.news.microsoft.com...
>>Hi,
>>The insertion is done by another application, reading text files,
>>processing each line in the text file, and inserting it into the database.
>> Unfortunately, it does not log errors, and there can also be a bug in the application (so i need to verify not only that the element exists, but also that the data is correct).
>>Verifying that the data is correct cannot be done using a constraint,
>>because it can possibly have inserted the data from another element into
>>the database (for example reading the wrong line in the file).
>Maybe you could load the file to the server independently and then compare
>the two results with a query. Perhaps that seems an odd idea given that
>you already have an application doing the same. But if it's possible to
>bulk load the file using BCP then I would expect you to see some
>improvement on the 20 minute running time you mentioned. Take a look at
>the BCP topic in Books Online.|||Yes, the program will be improved. But to do this, we need to figure out
what is wrong, hence why i am doing this.
I don't think they are open to loading the data using a complete new
process. I think they want to stick with the original application.
John Bell wrote:
>Hi Jon
>"Jon" wrote:
>>Hi,
>>The insertion is done by another application, reading text files,
>>processing each line in the text file, and inserting it into the database.
>>Unfortunately, it does not log errors, and there can also be a bug in the
>>application (so i need to verify not only that the element exists, but
>>also that the data is correct).
>Have you considered BCP which will create a file of records that have
>errored? Possibly a DTS/SSIS package could process this better?
>>Verifying that the data is correct cannot be done using a constraint,
>>because it can possibly have inserted the data from another element into
>>the database (for example reading the wrong line in the file).
>It sounds like you need to improve the program that does the inserts or the
>quality of the data!
>>--
>>Jon
>John|||No, it is not on a live system. But the problem still remains, i need to
somehow compare the data from the files with the data that is loaded in
the database, and the question is what is best to do.
--
John Bell wrote:
>Hi Jon
>>Yes, the program will be improved. But to do this, we need to figure out
>>what is wrong, hence why i am doing this.
>If that is the case then you will will probably want to take a copy of the
>database and not work on the live system!
>John|||"Jon" <jon@.noreply> wrote in message
news:xn0f10o2ajatr8x00y@.news.microsoft.com...
> No, it is not on a live system. But the problem still remains, i need to
> somehow compare the data from the files with the data that is loaded in
> the database, and the question is what is best to do.
>
The DBA objects to you doing a bulk load on a non-production system? That
seems unhelpful to the say the least!
Since you are going through the file anyway in your code maybe you could
insert the data into a temporary table that way. Then compare the two tables
with a query.
Failing that, it's hard to be sure whether retrieving 1 x 4m rows or 4m x 1
row would be better. I suspect the single query approach is the best unless
your client-side process is particularly heavy duty in which case the
difference may be negligible. That's really just a hunch though because it
depends on too many factors only you can determine - the type of query,
indexing, hardware and network utilisation, etc.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Tue, 09 Jan 2007 12:32:00 -0800, Jon wrote:
> I am not really a pro on T-SQL (can i read from a file using
>T-SQL?), so i feel more comfortable doing it in a "normal" application.
(snip)
>Running time is not critical (it will just be used a few times)
Hi Jon,
For a throwaway program that doesn't need to be fast, I'd suggest that
you use the technique that you are most comfortable with. And in this
case, that appears to be reading the rows one by one.
> and i
>would consider about 60 minutes to still be workable to verify the
>complete database).
The only way to find out if you'll be able to process the data in 60
minutes is to test it. (Consider testing on smaller databases first and
then try to extrapolate the execution time - test with several sizes to
check if the increase in execution time will be linear of exponential).
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||"Jon" <jon@.noreply> wrote in message
news:xn0f10ndvj9udcu00w@.news.microsoft.com...
> In the first place, I wanted to bulk load the data as-is into the
> database, but the db admin said no to this. I was not given any reason for
> this. :-(
> So I am looking into what other options i have to do this job.
It might be worth re-visiting the Bulk Load. Ask the DBA's if you can bulk
load the data into a separate table, and then execute a query to move the
data from the new table into the actual table. Data is much easier to
manipulate once it's in the database, even in a different table.
> You don't think it is reasonable to query the database a couple of million
> times?
I don't think that's the best option, but if you're stuck with it...|||Thanks David, for your help!
I will spend some time this evening writing code. I think i will continue
with the one-query approach.
--
David Portas wrote:
>"Jon" <jon@.noreply> wrote in message
>news:xn0f10o2ajatr8x00y@.news.microsoft.com...
>>No, it is not on a live system. But the problem still remains, i need to
>>somehow compare the data from the files with the data that is loaded in
>>the database, and the question is what is best to do.
>The DBA objects to you doing a bulk load on a non-production system? That
>seems unhelpful to the say the least!
>Since you are going through the file anyway in your code maybe you could
>insert the data into a temporary table that way. Then compare the two
>tables with a query.
>Failing that, it's hard to be sure whether retrieving 1 x 4m rows or 4m x
>1 row would be better. I suspect the single query approach is the best
>unless your client-side process is particularly heavy duty in which case
>the difference may be negligible. That's really just a hunch though
>because it depends on too many factors only you can determine - the type
>of query, indexing, hardware and network utilisation, etc.|||The structure of the files is not close to the table definitions, that is
one of the reason we have developed an application to process the files
and insert the data into the database.
It is the verification i am working on. I have the original data (the
files), and i have the result of our application (the database), and i now
need to identify what data was not loaded correctly. With this
information, i hope we can identify what part(s) of our application is not
working properly, and fix the application.
John Bell wrote:
>Hi Jon
>"Jon" wrote:
>>No, it is not on a live system. But the problem still remains, i need to
>>somehow compare the data from the files with the data that is loaded in
>>the database, and the question is what is best to do.
>>--
>If you load the data into new tables using the same program it is not
>really
>going to prove anything apart from possibly the program makes the same
>mistakes consistently!! May be you need to export the data and compare what
>is exported with the original files? It is not clear how close the table
>definitions are to the flat file format.
>John|||Hi Hugo,
You have a very good point in that i should use what i feel most
comfortable with. I will see this evening in what direction i will go.
Thanks!
Hugo Kornelis wrote:
>On Tue, 09 Jan 2007 12:32:00 -0800, Jon wrote:
>>I am not really a pro on T-SQL (can i read from a file using
>>T-SQL?), so i feel more comfortable doing it in a "normal" application.
>(snip)
>>Running time is not critical (it will just be used a few times)
>Hi Jon,
>For a throwaway program that doesn't need to be fast, I'd suggest that
>you use the technique that you are most comfortable with. And in this
>case, that appears to be reading the rows one by one.
>>and i
>>would consider about 60 minutes to still be workable to verify the
>>complete database).
>The only way to find out if you'll be able to process the data in 60
>minutes is to test it. (Consider testing on smaller databases first and
>then try to extrapolate the execution time - test with several sizes to
>check if the increase in execution time will be linear of exponential).|||"Jon" <jon@.noreply> wrote in message
news:xn0f11t16kd8jng010@.news.microsoft.com...
> The structure of the files is not close to the table definitions, that is
> one of the reason we have developed an application to process the files
> and insert the data into the database.
> It is the verification i am working on. I have the original data (the
> files), and i have the result of our application (the database), and i now
> need to identify what data was not loaded correctly. With this
> information, i hope we can identify what part(s) of our application is not
> working properly, and fix the application.
If you could load all the data into a working table with a structure close
to what your flat file is, it is then a simple matter to export the data
again and compare to the original flat file. It is also easier to move and
manipulate the data once it is in the database than during loading.

4 lookups against single large table

I need to do a 4 column lookup against a large table (1 Million rows) that contains 4 different record types. The first lookup will match on colums A, B, C, and D. If no match is found, I try again with colums A, B, C, and '99' in column D. If no match, try again with column A, B, D, and '99' in Column C. Finally, if no match in any of the above, use column A, '99' in B, '99' in C, '99' in D. I will retreive 2 columns from the lookup table.

My thought is that breaking this sequence out into 4 different tables/ lookups would be most efficient. The other option would be to write a script that handled this logic in a single transform with an in-memory table. My concern is that the size of the table would be too large to load into memory.

Any ideas/suggestions would be appreciated.

You only need to pull in the 4 columns from the table that you will be looking up against. Assuming they are normal integers (i.e. 4 bytes) you would need 4 * 4 * 1000000 =16MB per lookup. That's not really all that much. My advice would be to give it a go and if you're havig problems - see where the bottlenecks are.

More on memory per package: http://blogs.conchango.com/jamiethomson/archive/2005/05/29/1486.aspx

-Jamie

4 chart per page

I am using sql reporting and need to place 4 chart per page : in 2
column and 2 rows.
I have one datasource for this report to show on chart.
Any help?On Jan 10, 8:12 pm, arkgroup <arkgr...@.hotmail.com> wrote:
> I am using sql reporting and need to place 4 chart per page : in 2
> column and 2 rows.
> I have one datasource for this report to show on chart.
> Any help?
If I'm understanding you correctly, you can place the 4 charts into 2
rectangle controls (either vertically or horizontally) and then set
the report to 2 columns (via: the Layout view >> Report drop-down >>
Report Properties... >> Layout tab >> Columns and possibly adjust the
Spacing and Margins). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant