HOW: Auotincrement without using autoincrement

I have a master table which I have been updating in multiple queries to
create a new row. It has a primary key, call it WorkID. I can't make it
autoincrement. And it is more complicated because...
WorkID can also appear in two other tables, as references to the master
record, but I need to provide for the master record being gone, and as
such the records in the other two tables are orphaned. That is fine.
To create a new master record I select MAX(WorkID) unioned on all 3
tables, and increment the result, and this is my new WorkID. However,
even if in a transaction, this query work can result in duplicate WorkID
numbers when new masters are created in extremely rapid succession. I
see the flaw here. Don't think this approach can be salvaged when the
MAX and INSERT queries are done separately.
I have been racking my brain to find a way to deal with this WITHOUT
resorting to auto-increment on the master table. I can think of no
expression for an INSERT value which will take the MAX+1 of WorkID on
the three tables, but it seems there must be a way to do this in a
single statement. Something like:
INSERT WORK (WorkID) VALUES (SELECT MAX(WorkID)+1 FROM ... UNION ... )
Maybe I need a new single row, single column table that arbitrates new
WorkID values? Or maybe someone has a favorite trick?
Thx - Lee>> I have a master table .. <<
I have not heard that term since I moved from navigational DBMS to
RDBMS in thw 1970's. Let's get back to the basics of an RDBMS. Rows
are not records; fields are not columns; tables are not files. The
differences are VITAL!!!
Of course not! An exposed physical locator created inside the hardware
cannot be a relational key by definition.
Well, you need a new brain :) What is the natrual, relational key in
the SPECS YOU NEVER POSTED -- repeated SPECS YOU NEVER POSTED? I
assume that you are not soooooo ignorant that you want to have a
"magical one-size-fits-all" auto numbering.|||Wow.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123641244.396767.117310@.g14g2000cwa.googlegroups.com...
> I have not heard that term since I moved from navigational DBMS to
> RDBMS in thw 1970's. Let's get back to the basics of an RDBMS. Rows
> are not records; fields are not columns; tables are not files. The
> differences are VITAL!!!
>
> Of course not! An exposed physical locator created inside the hardware
> cannot be a relational key by definition.
>
> Well, you need a new brain :) What is the natrual, relational key in
> the SPECS YOU NEVER POSTED -- repeated SPECS YOU NEVER POSTED? I
> assume that you are not soooooo ignorant that you want to have a
> "magical one-size-fits-all" auto numbering.
>|||On Tue, 09 Aug 2005 12:18:38 -0700, Lee Gillie wrote:

> I have been racking my brain to find a way to deal with this WITHOUT
> resorting to auto-increment on the master table. I can think of no
> expression for an INSERT value which will take the MAX+1 of WorkID on
> the three tables, but it seems there must be a way to do this in a
> single statement. Something like:
> INSERT WORK (WorkID) VALUES (SELECT MAX(WorkID)+1 FROM ... UNION ... )
> Maybe I need a new single row, single column table that arbitrates new
> WorkID values? Or maybe someone has a favorite trick?
> Thx - Lee
As Celko said, you could probably do well with a full redesign that uses a
natural key. Failing that, how about this:
INSERT WORK (WorkID)
SELECT 1+MAX(WorkID)
FROM (
SELECT MAX(WorkID) WorkID FROM WORK
UNION ALL
SELECT MAX(WorkID) WorkID FROM OtherTable1
UNION ALL
SELECT MAX(WorkID) WorkID FROM OtherTable2
)
A better way might be to keep your available unused workIDs in yet another
table. First gather all used WorkIDs in the table:
CREATE TABLE WorkIDs (WorkID int, Used bit)
INSERT INTO WorkIDs (WorkID, Used)
SELECT WorkID, 1 FROM WORK
UNION
SELECT WorkID, 1 FROM OtherTable1
UNION
SELECT WorkID, 1 FROM OtherTable2
Now add some new ones for future use:
DECLARE @.maxworkID int
DECLARE @.x int
SELECT @.maxworkID = MAX(WorkID) FROM WorkIDs
SET @.x = @.maxworkID
WHILE @.x < @.maxworkID + 10000
BEGIN
INSERT INTO WorkIDs (WorkID, Used) VALUES (@.x, 0)
SET @.x = @.x + 1
END
Now here's your procedure for creating a new workID:
BEGIN TRANSACTION
DECLARE @.newWorkID int
SELECT @.newWorkID = MIN(WorkID) FROM WorkIDs WHERE Used=0
INSERT INTO WORK (WorkID) VALUES (@.newWorkID)
UPDATE WorkIDs SET Used=1 WHERE WorkID=@.newWorkID
COMMIT TRANSACTION|||> BEGIN TRANSACTION
> DECLARE @.newWorkID int
> SELECT @.newWorkID = MIN(WorkID) FROM WorkIDs WHERE Used=0
> INSERT INTO WORK (WorkID) VALUES (@.newWorkID)
> UPDATE WorkIDs SET Used=1 WHERE WorkID=@.newWorkID
> COMMIT TRANSACTION
Actually this is the worst case scenario for a busy system unless you use
exclusive locks. Say two users start the select at the same time. Boom,
deadlock. They both have a shared read lock on the workId's, the both
insert into work and then the UPDATE tries to WorkIDs to the same value
requiring exclusive locks. (unless the Unique Key fails them in the Work
table.)

> INSERT WORK (WorkID)
> SELECT 1+MAX(WorkID)
> FROM (
> SELECT MAX(WorkID) WorkID FROM WORK
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable1
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable2
> )
This would have to single thread over ALL tables! The SELECT by default
would only take shared locks, so any two users could be getting the max
values at the same time.
This gets even worse if this transaction gets called in another transaction.
There is not a good way to do this without single threading.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Ross Presser" <rpresser@.NOSPAMgmail.com.invalid> wrote in message
news:85kyr6seugm6$.dlg@.rosspresser.dyndns.org...
> On Tue, 09 Aug 2005 12:18:38 -0700, Lee Gillie wrote:
>
> As Celko said, you could probably do well with a full redesign that uses a
> natural key. Failing that, how about this:
> INSERT WORK (WorkID)
> SELECT 1+MAX(WorkID)
> FROM (
> SELECT MAX(WorkID) WorkID FROM WORK
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable1
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable2
> )
> A better way might be to keep your available unused workIDs in yet another
> table. First gather all used WorkIDs in the table:
> CREATE TABLE WorkIDs (WorkID int, Used bit)
> INSERT INTO WorkIDs (WorkID, Used)
> SELECT WorkID, 1 FROM WORK
> UNION
> SELECT WorkID, 1 FROM OtherTable1
> UNION
> SELECT WorkID, 1 FROM OtherTable2
> Now add some new ones for future use:
> DECLARE @.maxworkID int
> DECLARE @.x int
> SELECT @.maxworkID = MAX(WorkID) FROM WorkIDs
> SET @.x = @.maxworkID
> WHILE @.x < @.maxworkID + 10000
> BEGIN
> INSERT INTO WorkIDs (WorkID, Used) VALUES (@.x, 0)
> SET @.x = @.x + 1
> END
> Now here's your procedure for creating a new workID:
> BEGIN TRANSACTION
> DECLARE @.newWorkID int
> SELECT @.newWorkID = MIN(WorkID) FROM WorkIDs WHERE Used=0
> INSERT INTO WORK (WorkID) VALUES (@.newWorkID)
> UPDATE WorkIDs SET Used=1 WHERE WorkID=@.newWorkID
> COMMIT TRANSACTION|||On Thu, 11 Aug 2005 21:39:02 -0500, Louis Davidson wrote:
[snip]

> This gets even worse if this transaction gets called in another transactio
n.
> There is not a good way to do this without single threading.
Thanks for your comments. So, is there any decent answer to the problem of
generating new WorkIDs without using IDENTITY?

Aggregation of records with the same combination of granularity attributes when processing data

If there are multiple records with the same combination of granularity attributes when processing data ,
how these records are aggregated for the specific coordinate in the physical space?

e.g.

[date]
(it is the dimension talbe , which has only one column called date):

date

2007-1-1 0:00:00

2007-1-2 0:00:00

2007-1-3 0:00:00

2007-1-4 0:00:00

2007-1-5 0:00:00

[sales]

(it is the fact table , which has tow columns:time , amount.)

time

amout

2007-1-1 1:00:00

1

2007-1-1 2:00:00

2

2007-1-1 3:00:00

3

2007-1-2 0:00:00

8

In the DSV,I add a named calculation called [date] , whose definition is as follow, to the table [sales].

[date]: dateadd(day , 0 , datediff(day , 0 , time))

Than I establish a relationship between these two table:
[sales].[date]à[date].[date]

So there are three records corresponding to [2007-1-1 00:00:00]:

Amount

Date

1

2007-01-01

2

2007-01-01

3

2007-01-01

What is the value for [2007-1-1 00:00:00] when the AggregateFunction of [amount] is sum , count , max , min , lastchild etc. respectively?

Try this!

For myself , the value for [2007-1-1 00:00:00] is 6 when the AggregateFunction is LastChild.

For myself , the value for [2007-1-1 00:00:00] is 6 when the AggregateFunction is None.

|||

For myself ,
the value for [2007-1-1 00:00:00] is 6 when the AggregateFunction is AverageOfChildren.
the value for [2007-1-1 00:00:00] is 3 when the AggregateFunction is Max.
the value for [2007-1-1 00:00:00] is 1 when the AggregateFunction is Min.
the value for [2007-1-1 00:00:00] is 6 when the AggregateFunction is FirstChild.
the value for [2007-1-1 00:00:00] is 6 when the AggregateFunction is LastNonEmpty.
the value for [2007-1-1 00:00:00] is 6 when the AggregateFunction is Sum.

Note the first three results,please.

|||I have the same question.

/3G & multiple instances

Hi,
I am a little confused.
lest assume that I have a server with 8GB RAM
and I have installed 2 SQL instances on this server.
and I added the /3G parameter .
can both instances use 3GB RAM?
thanks,
Amit EshetAssuming Enterprise Edition of SQL Server: Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<anonymous@.discussions.microsoft.com> wrote in message
news:03c301c53395$c56a2a20$a501280a@.phx.gbl...
> Hi,
> I am a little confused.
> lest assume that I have a server with 8GB RAM
> and I have installed 2 SQL instances on this server.
> and I added the /3G parameter .
> can both instances use 3GB RAM?
> thanks,
>
> Amit Eshet
>

/3G & multiple instances

Hi,
I am a little confused.
lest assume that I have a server with 8GB RAM
and I have installed 2 SQL instances on this server.
and I added the /3G parameter .
can both instances use 3GB RAM?
thanks,
Amit EshetAssuming Enterprise Edition of SQL Server: Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<anonymous@.discussions.microsoft.com> wrote in message
news:03c301c53395$c56a2a20$a501280a@.phx.gbl...
> Hi,
> I am a little confused.
> lest assume that I have a server with 8GB RAM
> and I have installed 2 SQL instances on this server.
> and I added the /3G parameter .
> can both instances use 3GB RAM?
> thanks,
>
> Amit Eshet
>

/3G & multiple instances

Hi,
I am a little confused.
lest assume that I have a server with 8GB RAM
and I have installed 2 SQL instances on this server.
and I added the /3G parameter .
can both instances use 3GB RAM?
thanks,
Amit Eshet
Assuming Enterprise Edition of SQL Server: Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
<anonymous@.discussions.microsoft.com> wrote in message
news:03c301c53395$c56a2a20$a501280a@.phx.gbl...
> Hi,
> I am a little confused.
> lest assume that I have a server with 8GB RAM
> and I have installed 2 SQL instances on this server.
> and I added the /3G parameter .
> can both instances use 3GB RAM?
> thanks,
>
> Amit Eshet
>

.pdf rendering incorrectly

I have a report that is deployed at multiple sites without any problems, except one. At one site, when I render it to pdf, it doesn't look at all right, and the alignment on a table goes from center to right aligned for no obvious reason. It then stretches a textbox horizontally to force it onto another page. I've doublechecked and made sure that all the expand options on every single thing on the report is off, and I really don't understand why it would happen at only one site, even though the data is almost the exact same. Not only that, table cells don't stretch horizontally, they stretch vertically, so again, I'm stumped about why this one site would render incorrectly to a .pdf. Even in the report viewer, everything looks fine.

I am wondering if anyone has had a problem with rendering to pdf incorrectly when the rdl and data are correct, and what your solution was.

P.S. IT isn't a problem with margining, so please, don't give the generic "CHECK YOUR MARGINS" response.

Have you checked your margins?|||

Yes, I have... if it was a margining issue, it would be seen elsewhere I imagine. It's only at one site out of 10. The problem is there regardless of whether or not I leave the data fields in there or remove them. I've tried splitting the table in to two separate tables, no luck I can't duplicate the problem anywhere else.

This is the table in question... basic, nothing fancy.... adding up the column widths gives 7.75 in, its an 8.5x11 in with .25" margins... plenty of room to work with.

<Table Name="table1">
<Height>1.6in</Height>
<Style />
<KeepTogether>true</KeepTogether>
<Top>1.25in</Top>
<Width>7.75in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox175">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>36</ZIndex>
<Value>=FIRST(Fields!SortName.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox242">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>35</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox243">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>d</Format>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>34</ZIndex>
<Value>=FIRST(Fields!BirthDate.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox244">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>33</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox245">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>32</ZIndex>
<Value>=First(Fields!Gender_Code.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.2in</Height>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox246">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>31</ZIndex>
<Value>Student Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox250">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>30</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox251">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>29</ZIndex>
<Value>Date of Birth (MM/dd/yyyy)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox260">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>28</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox269">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>27</ZIndex>
<Value>Sex</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox270">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>26</ZIndex>
<Value>=First(Fields!SSN.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox273">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>25</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ColSpan>5</ColSpan>
<ReportItems>
<Textbox Name="textbox274">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>24</ZIndex>
<Value>=FIRST(Fields!PlaceOfBirth.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.2in</Height>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox275">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>23</ZIndex>
<Value>Social Security Number</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox278">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>22</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ColSpan>5</ColSpan>
<ReportItems>
<Textbox Name="textbox279">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>21</ZIndex>
<Value>Birth Place</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox284">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>20</ZIndex>
<Value>=Fields!PhoneNumber.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox285">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>19</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox286">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>18</ZIndex>
<Value>=Fields!LanguageType_CodeName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox287">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>17</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox288">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>16</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox289">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>15</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox290">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Verdana</FontFamily>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>14</ZIndex>
<Value>=Fields!Ethnicity_Code.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.2in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox291">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<Value>Home Phone</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox292">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox293">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<Value>Home Language</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox294">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox295">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<Value>Other Language</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox296">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox297">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<Value>Ethnic Group</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox298">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox299">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox300">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox301">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<VerticalAlign>Bottom</VerticalAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox302">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox303">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox304">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
<FontSize>7pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<Value>(African American / Asian / Caucasian / Hispanic / Native American / Other)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="StudentId">
<GroupExpressions>
<GroupExpression>=Fields!LocalId.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!FamilyRefId.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Left>0.125in</Left>
<TableColumns>
<TableColumn>
<Width>1.875in</Width>
</TableColumn>
<TableColumn>
<Width>0.125in</Width>
</TableColumn>
<TableColumn>
<Width>1.75in</Width>
</TableColumn>
<TableColumn>
<Width>0.25in</Width>
</TableColumn>
<TableColumn>
<Width>1.375in</Width>
</TableColumn>
<TableColumn>
<Width>0.125in</Width>
</TableColumn>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
<TableColumn>
<Width>0.125in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
</TableColumns>
</Table>

|||

Maybe you have to check some config values. Go to the following folder on that server which is rendering incorrectly:

Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer and open the rsreportserver.config in a notepad.

Navigate to <Render> tag and then to <Extention name="PDF"> tag under that. Check if the Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsoft.ReportingServices.ImageRendering"

If it's something else, probably that is the source of the problem.

Shyam

.NET Performance - Native vs OLE DB

Using a local instance of SQL Server 2000 and .NET I have managed to insert 4000 rows across multiple tables using the OLE DB provider in 11 seconds but when using the native provider it takes 13 seconds.
I was under the impression the native provider should enhance performance not decrease it? Why is this?
Thanks, Robby
By native provider, do you mean System.Data.SqlClient? In general SqlClient
is a good deal faster than using the combination of System.Data.OleDb and
the native oledb provider for sql server. To comment further, I'd need to
know more about your scenario.
Thanks,
Dave
"Robby White" <Robby White@.discussions.microsoft.com> wrote in message
news:04E120F8-5A6E-42F9-80E9-8BBD32B887C3@.microsoft.com...
> Using a local instance of SQL Server 2000 and .NET I have managed to
insert 4000 rows across multiple tables using the OLE DB provider in 11
seconds but when using the native provider it takes 13 seconds.
> I was under the impression the native provider should enhance performance
not decrease it? Why is this?
> Thanks, Robby
|||Yes I mean the System.Data.SqlClient provider.
Inserting 4000 records into SQL Server 2000
using the System.Data.SqlClient took 14 seconds
using the System.Data.Odbc took 13 seconds
using the System.Data.OleDb took 12 seconds
I have tried running these tests in different orders also and get the same results. I realise the times are close but I am concerned about scalibility.
Robby White
"David Schleifer [MSFT]" wrote:

> By native provider, do you mean System.Data.SqlClient? In general SqlClient
> is a good deal faster than using the combination of System.Data.OleDb and
> the native oledb provider for sql server. To comment further, I'd need to
> know more about your scenario.
> Thanks,
> Dave
> "Robby White" <Robby White@.discussions.microsoft.com> wrote in message
> news:04E120F8-5A6E-42F9-80E9-8BBD32B887C3@.microsoft.com...
> insert 4000 rows across multiple tables using the OLE DB provider in 11
> seconds but when using the native provider it takes 13 seconds.
> not decrease it? Why is this?
>
>
|||The insert operation is a fairly simple one, compared to other provider
operations such as reading data, so it's hard for any one provider to exceed
another once it's been reasonbly optimized. If you want to compare overall
provider performance, whatever benchmark you choose would have to include a
fair measure of read operations, which is one of the areas where SqlClient
really outperforms the oledb managed provider.
That said, when I tried a simple test with 16000 distinct insert operations
(i.e. seperate round trip for each), I got results for SqlClient that were
1-2% better than the OleDb provider. I think it's fair to say that the two
providers are basically equivalent for many types of insert operations, but
overall for best performance you will be better off with SqlClient.
If you are only interested in insert performance, you will probably want to
check out the Whidbey release of .NET, where SqlClient supports a bulk load
api.
"Robby White" <RobbyWhite@.discussions.microsoft.com> wrote in message
news:8A9CA873-B3B5-44B7-B2D9-296FFE988FC5@.microsoft.com...
> Yes I mean the System.Data.SqlClient provider.
> Inserting 4000 records into SQL Server 2000
> using the System.Data.SqlClient took 14 seconds
> using the System.Data.Odbc took 13 seconds
> using the System.Data.OleDb took 12 seconds
> I have tried running these tests in different orders also and get the same
results. I realise the times are close but I am concerned about
scalibility.[vbcol=seagreen]
> Robby White
> "David Schleifer [MSFT]" wrote:
SqlClient[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
performance[vbcol=seagreen]
|||Thanks for your help...
"David Schleifer [MSFT]" wrote:

> The insert operation is a fairly simple one, compared to other provider
> operations such as reading data, so it's hard for any one provider to exceed
> another once it's been reasonbly optimized. If you want to compare overall
> provider performance, whatever benchmark you choose would have to include a
> fair measure of read operations, which is one of the areas where SqlClient
> really outperforms the oledb managed provider.
> That said, when I tried a simple test with 16000 distinct insert operations
> (i.e. seperate round trip for each), I got results for SqlClient that were
> 1-2% better than the OleDb provider. I think it's fair to say that the two
> providers are basically equivalent for many types of insert operations, but
> overall for best performance you will be better off with SqlClient.
> If you are only interested in insert performance, you will probably want to
> check out the Whidbey release of .NET, where SqlClient supports a bulk load
> api.
>
> "Robby White" <RobbyWhite@.discussions.microsoft.com> wrote in message
> news:8A9CA873-B3B5-44B7-B2D9-296FFE988FC5@.microsoft.com...
> results. I realise the times are close but I am concerned about
> scalibility.
> SqlClient
> and
> to
> performance
>
>

.NET Performance - Native vs OLE DB

Using a local instance of SQL Server 2000 and .NET I have managed to insert
4000 rows across multiple tables using the OLE DB provider in 11 seconds but
when using the native provider it takes 13 seconds.
I was under the impression the native provider should enhance performance no
t decrease it? Why is this?
Thanks, RobbyBy native provider, do you mean System.Data.SqlClient? In general SqlClient
is a good deal faster than using the combination of System.Data.OleDb and
the native oledb provider for sql server. To comment further, I'd need to
know more about your scenario.
Thanks,
Dave
"Robby White" <Robby White@.discussions.microsoft.com> wrote in message
news:04E120F8-5A6E-42F9-80E9-8BBD32B887C3@.microsoft.com...
> Using a local instance of SQL Server 2000 and .NET I have managed to
insert 4000 rows across multiple tables using the OLE DB provider in 11
seconds but when using the native provider it takes 13 seconds.
> I was under the impression the native provider should enhance performance
not decrease it? Why is this?
> Thanks, Robby|||Yes I mean the System.Data.SqlClient provider.
Inserting 4000 records into SQL Server 2000
using the System.Data.SqlClient took 14 seconds
using the System.Data.Odbc took 13 seconds
using the System.Data.OleDb took 12 seconds
I have tried running these tests in different orders also and get the same r
esults. I realise the times are close but I am concerned about scalibility.
Robby White
"David Schleifer [MSFT]" wrote:

> By native provider, do you mean System.Data.SqlClient? In general SqlClien
t
> is a good deal faster than using the combination of System.Data.OleDb and
> the native oledb provider for sql server. To comment further, I'd need to
> know more about your scenario.
> Thanks,
> Dave
> "Robby White" <Robby White@.discussions.microsoft.com> wrote in message
> news:04E120F8-5A6E-42F9-80E9-8BBD32B887C3@.microsoft.com...
> insert 4000 rows across multiple tables using the OLE DB provider in 11
> seconds but when using the native provider it takes 13 seconds.
> not decrease it? Why is this?
>
>|||The insert operation is a fairly simple one, compared to other provider
operations such as reading data, so it's hard for any one provider to exceed
another once it's been reasonbly optimized. If you want to compare overall
provider performance, whatever benchmark you choose would have to include a
fair measure of read operations, which is one of the areas where SqlClient
really outperforms the oledb managed provider.
That said, when I tried a simple test with 16000 distinct insert operations
(i.e. seperate round trip for each), I got results for SqlClient that were
1-2% better than the OleDb provider. I think it's fair to say that the two
providers are basically equivalent for many types of insert operations, but
overall for best performance you will be better off with SqlClient.
If you are only interested in insert performance, you will probably want to
check out the Whidbey release of .NET, where SqlClient supports a bulk load
api.
"Robby White" <RobbyWhite@.discussions.microsoft.com> wrote in message
news:8A9CA873-B3B5-44B7-B2D9-296FFE988FC5@.microsoft.com...
> Yes I mean the System.Data.SqlClient provider.
> Inserting 4000 records into SQL Server 2000
> using the System.Data.SqlClient took 14 seconds
> using the System.Data.Odbc took 13 seconds
> using the System.Data.OleDb took 12 seconds
> I have tried running these tests in different orders also and get the same
results. I realise the times are close but I am concerned about
scalibility.[vbcol=seagreen]
> Robby White
> "David Schleifer [MSFT]" wrote:
>
SqlClient[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
performance[vbcol=seagreen]|||Thanks for your help...
"David Schleifer [MSFT]" wrote:

> The insert operation is a fairly simple one, compared to other provider
> operations such as reading data, so it's hard for any one provider to exce
ed
> another once it's been reasonbly optimized. If you want to compare overall
> provider performance, whatever benchmark you choose would have to include
a
> fair measure of read operations, which is one of the areas where SqlClient
> really outperforms the oledb managed provider.
> That said, when I tried a simple test with 16000 distinct insert operation
s
> (i.e. seperate round trip for each), I got results for SqlClient that were
> 1-2% better than the OleDb provider. I think it's fair to say that the two
> providers are basically equivalent for many types of insert operations, bu
t
> overall for best performance you will be better off with SqlClient.
> If you are only interested in insert performance, you will probably want t
o
> check out the Whidbey release of .NET, where SqlClient supports a bulk loa
d
> api.
>
> "Robby White" <RobbyWhite@.discussions.microsoft.com> wrote in message
> news:8A9CA873-B3B5-44B7-B2D9-296FFE988FC5@.microsoft.com...
> results. I realise the times are close but I am concerned about
> scalibility.
> SqlClient
> and
> to
> performance
>
>