Print Control

Hi guys
The Print control - printing directly from the Report Manager - have a
default setting. Is there a way to change this default setting'
Our problem is that the default is 5*8 letter format whereas here we use A4
format. Preferable it would pickup the report format in regards to portrait
or landscape.
Thanks.
Regards
JonasIt should pick this up. Check and see what your page layout size is.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
news:CB2C2B3D-CAD3-4565-AB2F-8E134D8D0243@.microsoft.com...
> Hi guys
> The Print control - printing directly from the Report Manager - have a
> default setting. Is there a way to change this default setting'
> Our problem is that the default is 5*8 letter format whereas here we use
> A4
> format. Preferable it would pickup the report format in regards to
> portrait
> or landscape.
> Thanks.
> Regards
> Jonas|||I have a report that have this:
<PageHeight>29.7cm</PageHeight>
<PageWidth>42cm</PageWidth>
Which matches A3 landscape but ends up as 8,5 by 11". This report does have
page header and footer does that matter?
I must admit that it does pickup A3 & A4 formats on some other reports.
Thanks.
Regards
Jonas
"Bruce L-C [MVP]" wrote:
> It should pick this up. Check and see what your page layout size is.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
> news:CB2C2B3D-CAD3-4565-AB2F-8E134D8D0243@.microsoft.com...
> > Hi guys
> >
> > The Print control - printing directly from the Report Manager - have a
> > default setting. Is there a way to change this default setting'
> >
> > Our problem is that the default is 5*8 letter format whereas here we use
> > A4
> > format. Preferable it would pickup the report format in regards to
> > portrait
> > or landscape.
> >
> > Thanks.
> >
> > Regards
> > Jonas
>
>|||Weird, it picks it up for some but not all. All I can suggest is to see if
there is anything different between the reports that work and those that
doesn't.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
news:33FEE50C-2C84-41D9-A23E-998C30BCDB10@.microsoft.com...
>I have a report that have this:
> <PageHeight>29.7cm</PageHeight>
> <PageWidth>42cm</PageWidth>
> Which matches A3 landscape but ends up as 8,5 by 11". This report does
> have
> page header and footer does that matter?
> I must admit that it does pickup A3 & A4 formats on some other reports.
> Thanks.
> Regards
> Jonas
> "Bruce L-C [MVP]" wrote:
>> It should pick this up. Check and see what your page layout size is.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
>> news:CB2C2B3D-CAD3-4565-AB2F-8E134D8D0243@.microsoft.com...
>> > Hi guys
>> >
>> > The Print control - printing directly from the Report Manager - have a
>> > default setting. Is there a way to change this default setting'
>> >
>> > Our problem is that the default is 5*8 letter format whereas here we
>> > use
>> > A4
>> > format. Preferable it would pickup the report format in regards to
>> > portrait
>> > or landscape.
>> >
>> > Thanks.
>> >
>> > Regards
>> > Jonas
>>

print blank page

i export my report to pdf and its leaving one blank page with every alternative page

is there any setting?

thanks

Make sure your layout, pagesize and margins all agree. Also, search this forum. This topic has been addressed several times in the past.|||Look at the report body width. If, for example, you set your margins to .5" on a portrait report, in this situation your body may be 7.500001". I have found this from time to time. The amount is so slight that looking at it won't do you any good. Click on the body and look at the width, then try to find the object that is causing the width to exceed your margins. I would first check your table width.|||

I'm using landscape and in my body property

width=11in

height = 4.125in

what does it mean, i have to reduce the width or height?

thanks a lot

|||

i fixed myself

i have to reduce the width size

|||

The trick to landscape vs portrait reports is by default portrait is set to 8.5 x 11 in the report properties. All you have to do is flip these two values around.

Jon

Primary Keys with Transactional Replication

I am pretty new to replication and have been setting it up in a test
environment using the test databases delivered during the sql install
(northwind and pubs).
I noticed that when I would setup Transactional Replicational (NON –
updateable subscriber) that the primary keys would NOT come over with tables
to the subscriber. But, if I set up Transactional Replication with
Updateable Subscriber, the primary keys would come over with the tables on
the subscriber. Am I missing something here? Or, is this indeed how it
works?
Hi Janet,
As Paul mentioned, transactional replication typically (or traditionally)
replicates the primary key as just a unique index. Assuming that you are
using a SQL2000 publisher, you can enable the 0x8000 (PKUKAsContraints)
article schema option so primary key will be replicated as primary key. The
behavior that you saw for updateable subscriber was our attempt to
"out-smart" the user as updateable subscriptions requires primary key
constraint (not just the index) at the subscriber to work properly.
-Raymond
"Janet" <Janet@.discussions.microsoft.com> wrote in message
news:A81443F2-9BEF-40B2-9236-3D91DD44D9BC@.microsoft.com...
>I am pretty new to replication and have been setting it up in a test
> environment using the test databases delivered during the sql install
> (northwind and pubs).
> I noticed that when I would setup Transactional Replicational (NON -
> updateable subscriber) that the primary keys would NOT come over with
> tables
> to the subscriber. But, if I set up Transactional Replication with
> Updateable Subscriber, the primary keys would come over with the tables on
> the subscriber. Am I missing something here? Or, is this indeed how it
> works?
>
>

Primary keys ...

I was setting up replication and discovered that explicitly defined primary
keys are required on the tables being replicated. We currenty have defined
these on exactly zero of our tables (legacy thing - you get the picture). I
told the developers I needed them. They asked me if it would break anything
if we put them on. I'm thinking that as long as we don't define explicit
PK/FK relationshiops bewteen the tables we should be OK.
I would appreciate any insight on the matter.
Bob Castleman
DBA PoseurOne idea would be to add a UniqueIdentifier field and set it to be the
RowGuidCol and the PK (along with a default of NewId()). By doing this, you
avoid SQL adding this column for you anyway for replication.
Thomas
"Bob Castleman" <nomail@.here> wrote in message
news:uNvZICqRFHA.2528@.TK2MSFTNGP10.phx.gbl...
>I was setting up replication and discovered that explicitly defined primary
>keys are required on the tables being replicated. We currenty have defined
>these on exactly zero of our tables (legacy thing - you get the picture). I
>told the developers I needed them. They asked me if it would break anything
if
>we put them on. I'm thinking that as long as we don't define explicit PK/FK
>relationshiops bewteen the tables we should be OK.
> I would appreciate any insight on the matter.
> Bob Castleman
> DBA Poseur
>|||We already have unique IDs on the tables, they were just never explicitly
defined within SQL Server as PKs. We may at some point start defining PK/FK
relationships and referential integrity so wouldn't make sense to use our
current IDs? I am more concerned about unintended side effects. I can't see
how defining a PK on table would cause a problem, but I need to make sure.
Bob
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
> One idea would be to add a UniqueIdentifier field and set it to be the
> RowGuidCol and the PK (along with a default of NewId()). By doing this,
> you avoid SQL adding this column for you anyway for replication.
>
> Thomas
>
> "Bob Castleman" <nomail@.here> wrote in message
> news:uNvZICqRFHA.2528@.TK2MSFTNGP10.phx.gbl...
>|||As long as the data is actually unique, it shouldn't be a problem.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Bob Castleman" <nomail@.here> wrote in message
news:%23PkbQfqRFHA.648@.TK2MSFTNGP14.phx.gbl...
> We already have unique IDs on the tables, they were just never explicitly
> defined within SQL Server as PKs. We may at some point start defining
> PK/FK relationships and referential integrity so wouldn't make sense to
> use our current IDs? I am more concerned about unintended side effects. I
> can't see how defining a PK on table would cause a problem, but I need to
> make sure.
> Bob
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
>|||Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
use northwind
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a cross join sysobjects as b
go
declare @.sql nvarchar(4000)
declare @.s datetime
declare @.e datetime
declare @.i int
declare @.datepart varchar(15)
set @.s = '2005-01-01T12:00:00.000'
set @.e = '2005-01-01T13:00:00.000'
set @.i = 15
set @.datepart = 'minute'
set @.sql = N'
select
right(convert(varchar(35), dateadd(' + @.datepart + N', number, ''' +
convert(varchar(25), @.s, 126) + N'''), 100), 7) as colA
from
number as n
where
number % ' + ltrim(@.i) + N' = 0
and dateadd(' + @.datepart + N', number, ''' + convert(varchar(25), @.s, 126)
+ N''') < cast(''' + convert(varchar(25), @.e, 126) + N''' as datetime)'
print @.sql
exec sp_executesql @.sql
go
drop table number
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Bob Castleman" wrote:

> I was setting up replication and discovered that explicitly defined primar
y
> keys are required on the tables being replicated. We currenty have defined
> these on exactly zero of our tables (legacy thing - you get the picture).
I
> told the developers I needed them. They asked me if it would break anythin
g
> if we put them on. I'm thinking that as long as we don't define explicit
> PK/FK relationshiops bewteen the tables we should be OK.
> I would appreciate any insight on the matter.
> Bob Castleman
> DBA Poseur
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Why should I consider using an auxiliary numbers table?
> http://www.aspfaq.com/show.asp?id=2516
> use northwind
> go
> select
> identity(int, 1, 1) as number
> into
> number
> from
> sysobjects as a cross join sysobjects as b
> go
>
> declare @.sql nvarchar(4000)
> declare @.s datetime
> declare @.e datetime
> declare @.i int
> declare @.datepart varchar(15)
> set @.s = '2005-01-01T12:00:00.000'
> set @.e = '2005-01-01T13:00:00.000'
> set @.i = 15
> set @.datepart = 'minute'
> set @.sql = N'
> select
> right(convert(varchar(35), dateadd(' + @.datepart + N', number, ''' +
> convert(varchar(25), @.s, 126) + N'''), 100), 7) as colA
> from
> number as n
> where
> number % ' + ltrim(@.i) + N' = 0
> and dateadd(' + @.datepart + N', number, ''' + convert(varchar(25), @.s, 12
6)
> + N''') < cast(''' + convert(varchar(25), @.e, 126) + N''' as datetime)'
> print @.sql
> exec sp_executesql @.sql
> go
> drop table number
> go
>
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> AMB
>
> "Bob Castleman" wrote:
>|||If that's the case, then just declare those columns as the PK. You can decla
re a
PK without having to declare FK (although it helps with data integrity).
The only problems you might encounter by setting a given column(s) as the PK
are:
1. If the data is not unique or contains nulls
or
2. If the the app that writes the data expects that it can fill in duplicate
or
null data on one pass even if it changes it to be non-nullable and unique in
another pass. Obviously, this won't work as SQL will prevent any nulls or
duplciate values from ever being written.
Thomas
"Bob Castleman" <nomail@.here> wrote in message
news:%23PkbQfqRFHA.648@.TK2MSFTNGP14.phx.gbl...
> We already have unique IDs on the tables, they were just never explicitly
> defined within SQL Server as PKs. We may at some point start defining PK/F
K
> relationships and referential integrity so wouldn't make sense to use our
> current IDs? I am more concerned about unintended side effects. I can't se
e
> how defining a PK on table would cause a problem, but I need to make sure.
> Bob
> "Thomas" <replyingroup@.anywhere.com> wrote in message
> news:eMO7hZqRFHA.244@.TK2MSFTNGP12.phx.gbl...
>

primary keys

Hello,
I've a problem setting up a constraint on a table. There are two primary
keys in that table and one of them will be filled in through an
access application, wether it be nothing or some integer value.
What i want to do is to set a zero-value in a primary key column, how
can i do this? I've tried coalesce([columnname],'',0), but that gives me
an error. I think the syntax for the constraint is wrong, but i don't
know how to set it correctly.
Can anyone help me?Jason
You cannot do that . It seems you need create an UNIQUE CONTRAINT which will
accept a NULL value
Look at this example written by Steve Kass
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ekQO1ci2FHA.472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I've a problem setting up a constraint on a table. There are two primary
> keys in that table and one of them will be filled in through an access
> application, wether it be nothing or some integer value.
> What i want to do is to set a zero-value in a primary key column, how can
> i do this? I've tried coalesce([columnname],'',0), but that gives me an
> error. I think the syntax for the constraint is wrong, but i don't know
> how to set it correctly.
> Can anyone help me?|||There can only be one designated primary key for a table; any additional
unique keys are called candidate keys. Column(s) with primary key constraint
cannot have NULL value and all values must be unique.
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ekQO1ci2FHA.472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I've a problem setting up a constraint on a table. There are two primary
> keys in that table and one of them will be filled in through an access
> application, wether it be nothing or some integer value.
> What i want to do is to set a zero-value in a primary key column, how can
> i do this? I've tried coalesce([columnname],'',0), but that gives me an
> error. I think the syntax for the constraint is wrong, but i don't know
> how to set it correctly.
> Can anyone help me?sql

Primary Key

I am setting up some tables where I used to have an identity column as the primary key. I changed it so the primary key is not a char field length of 20.

Is there going to be a big performance hit for this? I didn't like the identity field because every time I referenced a table I had to do a join to get the name of object.

EG:

-- Old way
tbProductionLabour
ID (pk)| Descr | fkCostCode
-------
1 | REBAR | 1J

tbTemplateLabour
fkTemplateID | fkLabourID | Manpower | Hours
--------------
1 | 1 | 1 | 0.15

-- New way
tbProductionLabour
Labour | fkCostCode
-------
REBAR | 1J

tbTemplateLabour
fkTemplateID | fkLabour | Manpower | Hours
--------------
1 | REBAR | 1 | 0.15

This is a very basic example, but you get the idea of what I am referring to.

Any thoughts?

MikeI didn't like the identity field because every time I referenced a table I had to do a join to get the name of object.

The light! Don't look at the light!

I guess I'll add you to the "not preffering surrogates" group

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx

Excuse me while I climb back upon my barst...um desk chair...yeah that's right...|||EDIT: Didn't we have this conversation already?|||EDIT: Didn't we have this conversation already?
Probably, but as an in-experienced developer (wannabe) I was wondering if not using a identity field will really make that great of a performance difference.

I think the char(20) for a primary key is better mainly because when I open a table I am not seeing a number which I will have to look up. It also would reduce the number of joins I would have to make (which I guess would be better for performance). But, for looking up values or joining on that field, would the performance reduction be neglable or significant enough to want to use the identity field?

Mike|||Let me ask you this...

What do you think would be faster.

A). An umpteen table join on surrogate keys to get the data, or

B). A SELECT against 1 Table|||Let me ask you this...

What do you think would be faster.

A). An umpteen table join on surrogate keys to get the data, or

B). A SELECT against 1 Table
I would assume option B, but let me ask you this.

What do you think would be faster:
A) Looking up values based on an integer
or
B) Looking up values based on an umpteen char string

?

Mike B|||OK, I'll give you 80/1000 of a second

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 sysname)
CREATE TABLE myTable00(Col1 sysname, Col2 int IDENTITY(1,1))
GO

DECLARE @.x int
SELECT @.x = 1
WHILE @.x < 1000
BEGIN
INSERT INTO myTable99(Col1) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables
INSERT INTO myTable00(Col1) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables
SELECT @.x = @.x + 1
END

INSERT INTO myTable99(Col1) SELECT 'Brett'

CREATE INDEX myIndex99 ON myTable99(Col1)
CREATE INDEX myIndex00 ON myTable00(Col2)

SELECT COUNT(*) FROM myTable99

DECLARE @.x1 datetime, @.y1 datetime, @.x2 datetime, @.y2 datetime
SELECT @.x1 = GetDate()
SELECT @.x1 AS systime, 'Starting int look up'
SELECT * FROM myTable00 WHERE Col2 = 216784
SELECT @.y1 = GetDate()
SELECT @.y1 AS systime, 'Endinging int look up'

SELECT @.x2 = GetDate()
SELECT @.x2 AS systime, 'Starting sysname look up'
SELECT * FROM myTable00 WHERE Col1 = 'Brett'
SELECT @.y2 = GetDate()
SELECT @.y2 AS systime, 'Endinging int look up'

SELECT DATEDIFF(ms,@.x1, @.y1), DATEDIFF(ms,@.x2, @.y2)
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO|||For small tables with no updates, it doesn't matter much. If you start to update the char values you are using as keys, you'll lose hair very quickly. If you add rows (beyond about 100,000 or so), the numeric keys will be significantly faster, due to lower total physical IO.

The short answer boils down to you can use what you want. As you scale upward, the surrogate keys look better and better!

-PatP|||For small tables with no updates, it doesn't matter much. If you start to update the char values you are using as keys, you'll lose hair very quickly. If you add rows (beyond about 100,000 or so), the numeric keys will be significantly faster, due to lower total physical IO.

The short answer boils down to you can use what you want. As you scale upward, the surrogate keys look better and better!

-PatP
Yeah, I pretty much aggree with that. The tables I am refering to will not be that big and they are kind of complex so I thought it would be best to use as many natural keys as possible. Of course there are places in my DB where I use the identity because I don't think the natural keys are all that good to use.

How many people use CompanyName as a natural key in a companies table?

I understand there maybe more then one CompanyName in the table but should they be unique by appending a number or geographical location, or using the CompanyName / Address as the primary key.

My thought is that it is best to use a surrogate here because to carry a company name and address as a forein key to other tables is probably costly.

There is the argument that the address can change but is that really a problem since you can specify "Cascade update related fields" on the other tables?

I would personally love to see something other then a number when I am looking at these tables but....

Mike B|||At least in my opinion, company name stinks as a primary key. We aren't all that big, but we have several hundred companies scattered wildly about North America with the same name, and on a worldwide basis it gets even worse.

JOINs are cheap. SQL Server makes them nearly free IF you keep the FK value small (INT or smaller) and you've got enough RAM in your server.

-PatP|||JOINs are cheap.

That's gotta be the most open ended statement I've heard in a while...

Also... "Company's with the same name scattered around"?

Either they truly are a different company, which means they are separate legal entity, or they are a site for a company...

Are you essentially saying that accessing 1 table would be slower than accessing many?|||Many are franchises, some just reuse common names in different jurisdictions. The net result is that if you look for companies with names like Subway or McDonald's you find hundreds of hits, most (but not all) with separate EIN values.

If you have to haul a fifty byte VARCHAR off the disk versus a four byte integer for every row in a 34 million row table, versus a join to a table cached in RAM, then the JOIN is cheaper than the single table. While SQL Server is good at hiding physical details from the user, some things are still big enough tasks so that smart design beats brute force every time.

-PatP

prevent zero length entries

I have a couple critical fields where I am setting them to no nulls but just
realized that they can be set to zero length strings very easily. Other than
a trigger or handling the validation in the front end, is there an easy way
to handle this?
MS Access is my front end so interestingly enough, if you select an the
entire text in a field and hit the delete key it trys to set to NULL not ""
so it's ok. But in EM you can just hit the delete key and it goes to ""
instead of NULL. Obviously end users are goign to use the front end and not
EM so this isn't really a problem but I was just wondering about other front
ends that might not behave the same way. They might set to "" on hitting the
delete key.
THanks,
KeithKeith G Hicks wrote:
> I have a couple critical fields where I am setting them to no nulls but ju
st
> realized that they can be set to zero length strings very easily. Other th
an
> a trigger or handling the validation in the front end, is there an easy wa
y
> to handle this?
> MS Access is my front end so interestingly enough, if you select an the
> entire text in a field and hit the delete key it trys to set to NULL not "
"
> so it's ok. But in EM you can just hit the delete key and it goes to ""
> instead of NULL. Obviously end users are goign to use the front end and no
t
> EM so this isn't really a problem but I was just wondering about other fro
nt
> ends that might not behave the same way. They might set to "" on hitting t
he
> delete key.
> THanks,
> Keith
>
A check constraint of LEN(ColumnName) > 0 would do it, or you could work
in a more complex requirement. Honestly, if you're worried about people
just putting in an empty string, and you require at least one char,
they'll learn that if they put in 1 char they can save the data. If you
bump the requirement up to 2, they'll put in 2. :D
Zach|||keith,
create a check constraint on table, with expression: datalength(col)>0
hth,
dean
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23w4ZvbwCFHA.1932@.TK2MSFTNGP14.phx.gbl...
> I have a couple critical fields where I am setting them to no nulls but
just
> realized that they can be set to zero length strings very easily. Other
than
> a trigger or handling the validation in the front end, is there an easy
way
> to handle this?
> MS Access is my front end so interestingly enough, if you select an the
> entire text in a field and hit the delete key it trys to set to NULL not
""
> so it's ok. But in EM you can just hit the delete key and it goes to ""
> instead of NULL. Obviously end users are goign to use the front end and
not
> EM so this isn't really a problem but I was just wondering about other
front
> ends that might not behave the same way. They might set to "" on hitting
the
> delete key.
> THanks,
> Keith
>|||Thank you to both of you. That works just fine of course.
I feel sort of silly now. :)|||"Zach Wells" wrote:
<snip>

> Honestly, if you're worried about people just putting in
> an empty string, and you require at least one char, they'll
> learn that if they put in 1 char they can save the data. If
> you bump the requirement up to 2, they'll put in 2. :D
LOL! Never underestimate the ability of users to circumvent your data
validation routines. We had a situation just like that. You could even map
out the evolution of the validation routine and the users' innovation
(especially since the application had no way of exactly determining what a
correct string was for that particular column). The progression looked
something like:
.
NA
N/A
XXXX
DONTKNOW
:)
Craig