Can I export a report as PDF files within a DOS batch file?

Hi,
Is there any command line tool that helps me to export a report into a PDF
file?
I need to do that within a batch file. I am trying to avoid C# coding to do
that.
Any help would be appreciated,
Maxyes, rs.exe should do what you are trying to accomplish.
--
Shaun Beane, MCT, MCDST, MCDBA
dbageek.blogspot.com
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:eU8dFLcXGHA.3724@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Is there any command line tool that helps me to export a report into a PDF
> file?
> I need to do that within a batch file. I am trying to avoid C# coding to
> do that.
> Any help would be appreciated,
> Max
>|||Great! Do you know any link to a sample that shows me how to export and save
the report into a PDF file?
"Shaun Beane" <shaun.beane@.gmail.nojunk.com> wrote in message
news:OhIWIZcXGHA.4148@.TK2MSFTNGP03.phx.gbl...
> yes, rs.exe should do what you are trying to accomplish.
> --
> Shaun Beane, MCT, MCDST, MCDBA
> dbageek.blogspot.com
> "Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
> news:eU8dFLcXGHA.3724@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> Is there any command line tool that helps me to export a report into a
>> PDF file?
>> I need to do that within a batch file. I am trying to avoid C# coding to
>> do that.
>> Any help would be appreciated,
>> Max
>>
>|||Hi Maxwell,
You can use the RS.exe Utility to export the report to the PDF file.
RS.exe will read a script file which should be written by VB.NET.
Here is a article posted some sample code, you may try to refer.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
se_frm/thread/e608c8fecc95d08/8a2dcc6ca82bf3fc?tvc=1&q=RS+utility+export+pdf
&hl=zh-CN#8a2dcc6ca82bf3fc
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Can I avoid temp tables, etc.

I need help on two questions:
1. Is temp table the only way to pass recordsets from a nested stored
procedure to a calling stored procedure? Can we avoid temp tables in
this case?
2. Are operations in a stored procedure are treated as a transaction?

Any help will be greatly appreciated.

Background: We need to use temp table to pass recordsets from a nested
stored procedure to a calling stored procedure. Our understanding is
that in this case, we have no choice but to use temp tables. So, we
need to optimize the performance as much as possible. To do this, we
wanted to find out whether operations in a stored procedure are treated
as a transaction. We are using SQL 2000 SP4. I could not find any
answers so I did the following experiment.

Experiment 1:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[Wiz_SP_Transaction_Test]
GO

CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
AS

Update
Articles
SET
IsUpdate = 20
where
ArticlesId < 80000

SELECT * from Articles

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

"SELECT * from Articles" takes a long time (about 40 seconds) to
complete

Before executing the SP, the IsUpdate attribute for all articles is 30.
Then I executed this SP. Before the SP is finished, I end the SP
manually. I checked the IsUpdate attribute again, and found that all
Articles's (ArticlesId < 80000) Isupdate attribute is now 20. The
operations did not rollback. I interpret this to mean that the whole SP
is not treated as a transaction.

Then, I did experiment 2 below. This time, I explicitly declared the
transaction.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[Wiz_SP_Transaction_Test]
GO

CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
AS
BEGIN TRANSACTION
Update
Articles
SET
IsUpdate = 50
where
ArticlesId < 80000

SELECT * from Articles

IF @.@.ERROR <0 ROLLBACK TRANSACTION
COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Before this second SP, the IsUpdate attribute is 20 (set in the first
experiment). I run this second SP and ended it manually before it
finished. I checked the IsUpdate attributes for all Articles's
(ArticlesId < 80000), but their Isupdate attribute is 50. So the
operation did not rollback either. But we have declared the transaction
explicitly. Does this mean that the SP is still not treated as a
transaction?(betbubble@.gmail.com) writes:

Quote:

Originally Posted by

I need help on two questions:
1. Is temp table the only way to pass recordsets from a nested stored
procedure to a calling stored procedure? Can we avoid temp tables in
this case?

No, there are more alternative: use a process-keyed table. As long
as the access is from T-SQL only, @.@.spid works fine. We use this
technique a lot in our shop.

There is also INSERT-EXEC, but I like this less.

I discuss these options in more detail in an article on my web site:
http://www.sommarskog.se/share_data.html

Quote:

Originally Posted by

2. Are operations in a stored procedure are treated as a transaction?

A procedure as such does not define any transaction scope. However,
each INSERT, UPDATE and DELETE statement defines a transaction if
there is no other transaction active. This transaction includs any
trigger that is fired the statement. And in case of INSERT EXEC, the
called procedure will operate in the context of the transaction
defined by the INSERT statement.

Note that this applies, regardless of the INSERT, UPDATE or DELETE
statement appears in a stored procedure or not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What are you using SET ANSI_NULLS OFF for?|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

What are you using SET ANSI_NULLS OFF for?

I would guess that betbubble uses Enterprise Manager to create his
procedures. Which is a very bad idea, for the precise reason Alexander
points out (thanks for catching it!): Enterprise Manager has incorrect
defaults for ANSI_NULLS and QUTOED_IDENTIFIERS. You have rarely reason
to have these options off (least of all ANSI_NULLS), but there are
features in SQL Server that requires these settings to be ON, so by
all means run with them.

If you use Query Analyzer to edit stored procedures, you get the
correct defaults.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland and Alexander,
Thanks a lot. I will experiment with them and report back.|||These SPs were generated using the Query Analyzer, ANSI_NULLS and
QUTOED_IDENTIFIERS are OFF. I turned them ON manually. Is there
something wrong with my Query Analyzer settings?

I read the article by Erland. Great information! Thanks. I am
experimenting with the Process-Keyed tables, which are very big tables.
I have many querys concurrentlly, they will need to use the same
Process-Keyed tables. Any advice on reducing locks will be appreciated.|||(betbubble@.gmail.com) writes:

Quote:

Originally Posted by

These SPs were generated using the Query Analyzer, ANSI_NULLS and
QUTOED_IDENTIFIERS are OFF. I turned them ON manually. Is there
something wrong with my Query Analyzer settings?

You can change the connection settings under Tools->Options->Cononection
Properties. The default settings is that all settings for indexed views
are on, but you might have changed that at some point.

Also, if the SP was originally created by EM, and you scripted it from
QA, QA will include the original settings in the script, so you will
actively have to change them - or just remove them-

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Can I avoid slammer attack without install sp3 for sql server 2k

Hi,

I guess there is some problem setting up transactional replication
after i install sp3 for sql server 2k. I get primary key violation in sp_MSget_repl_commands. So I need to revert back to sp2. But what should i do to avoid slammer attack with sp2 installed on my server

Thanks
Nikhil.This is a known issue. You should contact PSS and ask them to look up
SRX030113606317.

Can FK be nullable/optional by design?

Hi All!

General statement: FK should not be nullabe to avoid orphans in DB.

Real life:
Business rule says that not every record will have a parent. It is
implemented as a child record has FK that is null.

It works, and it is simpler.
The design that satisfy business rule and FK not null can be
implemented but it will be more complicated.

Example: There are clients. A client might belong to only one group.

Case A.
Group(GroupID PK, Name,Code)
Client(ClientID PK, Name, GroupID FK NULL)

Case B(more cleaner)
Group(GroupID PK, Name, GroupCode)

Client (ClientID PK, Name, .)
Subtype:
GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

There is one more entity in Case B and it will require an additional
join in compare with caseA
Example: Select all clients that belongs to any group

Summary Q: Is it worth to go with CaseB?

Thank you in advance"Andy" <net__space@.hotmail.com> wrote in message <news:edb90340.0311301114.19718061@.posting.google.c om>...

> Hi All!
> General statement: FK should not be nullabe to avoid orphans in DB.
> Real life:
> Business rule says that not every record will have a parent. It is
> implemented as a child record has FK that is null.

Nulls suck. Dealing with Null is ugly any way you look at it.

> It works, and it is simpler.
> The design that satisfy business rule and FK not null can be
> implemented but it will be more complicated.
> Example: There are clients. A client might belong to only one group.
> Case A.
> Group(GroupID PK, Name,Code.)
> Client(ClientID PK, Name, GroupID FK NULL)

In this scheme, a client may belong to no group or one group but
cannot belong to more than one group. Is this the business rule?

> Case B(more cleaner)
> Group(GroupID PK, Name, GroupCode.)
> Client (ClientID PK, Name, ..)
> Subtype:
> GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)
> There is one more entity in Case B and it will require an additional
> join in compare with caseA
> Example: Select all clients that belongs to any group

With one tweak, GroupedClient can be a many<->many link between
Client and Group. Otherwise, you can always use a view to turn
Case B into Case A for the convenience of a particular program.

> Summary Q: Is it worth to go with CaseB?

Case C. Use one or more "special" groups to "contain" otherwise
"groupless" clients. However, you now have the "special" groups
to deal with.

--
Joe Foster <mailto:jlfoster%40znet.com> Sign the Check! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!|||net__space@.hotmail.com (Andy) writes:

> General statement: FK should not be nullabe to avoid orphans in DB.

I don't see the reasoning behind this statement. Any column that
references keys to another table should be explicitly specified as such
to avoid orphans.

If that column may sometimes be unknown/unspecified for perfectly valid
records, I see no reason not to make it nullable.

--
"Notwithstanding fervent argument that patent protection is essential
for the growth of the software industry, commentators have noted
that `this industry is growing by leaps and bounds without it.'"
-- US Supreme Court Justice John Paul Stevens, March 3, 1981.|||depends on what a Group is and how it is used...

e.g.,
is a Group a Super-Client? -- individual Clients may be subsidiaries of a
Super-Client?
is a Group in internal designation, like a Sales territory?

How many Clients are there likely to be w/o a group?
When you need to act on the clients that are grouped, do you also need to
act on the clients that are not grouped?

[ps. in Case B, where did PersonID come from? Is that the Client?]

> Example: There are clients. A client might belong to only one group.

> Case A.
> Group(GroupID PK, Name,Code.)
> Client(ClientID PK, Name, GroupID FK NULL)
>
> Case B(more cleaner)
> Group(GroupID PK, Name, GroupCode.)
> Client (ClientID PK, Name, ..)
> Subtype:
> GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)
> There is one more entity in Case B and it will require an additional
> join in compare with caseA
> Example: Select all clients that belongs to any group
>
> Summary Q: Is it worth to go with CaseB?
> Thank you in advance|||"Trey Walpole" <treyNOpole@.SPcomcastAM.net> wrote in message news:<u3p24vCuDHA.3144@.tk2msftngp13.phx.gbl>...
> depends on what a Group is and how it is used...
> e.g.,
> is a Group a Super-Client? -- individual Clients may be subsidiaries of a
> Super-Client?
> is a Group in internal designation, like a Sales territory?
> How many Clients are there likely to be w/o a group?
> When you need to act on the clients that are grouped, do you also need to
> act on the clients that are not grouped?
> [ps. in Case B, where did PersonID come from? Is that the Client?]

Yes, it does.
It should be this way

[ps. in Case B, where did PersonID come from? Is that the Client?]

Case B
Group(GroupID PK, Name, GroupCode.)
Client (ClientID PK, Name, ..)
Subtype:
GroupedClient (ClientID PK/FK, GroupID FK NOT NULL)|||net__space@.hotmail.com (Andy) wrote in message news:<edb90340.0311301114.19718061@.posting.google.com>...
> Hi All!
> General statement: FK should not be nullabe to avoid orphans in DB.

Where did this statement come from? The idea of an orphan belongs to
network and hierarchical databases (old fashioned) or to
object-oriented databases (allegedly new), where the only way to get
to a record might be through its parent record. In a relational
database there is no such thing as an orphan.

You can find your "orphans" by some equivalent of (client where
groupcode not present) (worded that way to keep away from arguments
about NULLS).

In your example, what you have is

A client may be a member of at most one group.

If you meant to have

A client must be a member of exactly one group.

then (in your example) you would have to use NOT NULL.

Regards,

Eric|||"Andy" <net__space@.hotmail.com> wrote in message
news:edb90340.0311301114.19718061@.posting.google.c om...
> Hi All!
> General statement: FK should not be nullabe to avoid orphans in DB.
> Real life:
> Business rule says that not every record will have a parent. It is
> implemented as a child record has FK that is null.

I'm not too hot on all this, but here is what I was lead to believe: If
Client *must* belong to at least one group, then the client is dependent on
the group - it cannot exist without it. Therefore, it's primary key would
(at least logically) be a composite, where the group pk forms part of the
clients composite primary key. This would ensure that a client cannot exist
without a group!?

This might look like:
Client(GroupID PK, ClientID PK, Name )

Otherwise, if the Client could optionally belong to one Group, the
relationship would be captured in a link table, as you suggested in B?

GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

Just my 2 pennies worth 8-)

Tobes|||"Tobin Harris" <tobin_dont_you_spam_me@.breathemail.net> wrote in message <news:braub1$1cceh$1@.ID-135366.news.uni-berlin.de>...

> "Andy" <net__space@.hotmail.com> wrote in message
> news:edb90340.0311301114.19718061@.posting.google.c om...
> > Hi All!
> > General statement: FK should not be nullabe to avoid orphans in DB.
> > Real life:
> > Business rule says that not every record will have a parent. It is
> > implemented as a child record has FK that is null.

> I'm not too hot on all this, but here is what I was lead to believe: If
> Client *must* belong to at least one group, then the client is dependent on
> the group - it cannot exist without it. Therefore, it's primary key would
> (at least logically) be a composite, where the group pk forms part of the
> clients composite primary key. This would ensure that a client cannot exist
> without a group!?
> This might look like:
> Client(GroupID PK, ClientID PK, Name )

Did you really mean to claim that ALL non-nullable attributes MUST
'logically' be included as part of the primary key?!

> Otherwise, if the Client could optionally belong to one Group, the
> relationship would be captured in a link table, as you suggested in B?
> GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

This would avoid the null nonsense until someone does an outer join.

--
Joe Foster <mailto:jlfoster%40znet.com> L. Ron Dullard <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!|||"Joe "Nuke Me Xemu" Foster" <joe@.bftsi0.UUCP> wrote in message
news:1071189386.456990@.news-1.nethere.net...
> Did you really mean to claim that ALL non-nullable attributes MUST
> 'logically' be included as part of the primary key?!

Well, not really! I was just throwing in another option - where if the
existance of one entity is dependent on another, then you can make the PK of
that entity part of a composite key in the dependent entity. It's an
alternative to just non nullable foreign keys, where the related column(s)
become part of a primary key, rather than just a foreign key. Sorry, I think
I need to take my anti-waffle pill, can't seem to put a good explanation
together 8-)

> > Otherwise, if the Client could optionally belong to one Group, the
> > relationship would be captured in a link table, as you suggested in B?
> > GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)
> This would avoid the null nonsense until someone does an outer join.

That's true. So which option would you go for?

Tobes

> --
> Joe Foster <mailto:jlfoster%40znet.com> L. Ron Dullard
<http://www.xenu.net/>
> WARNING: I cannot be held responsible for the above They're
coming to
> because my cats have apparently learned to type. take me away,
ha ha!|||"Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message <news:brck8d$1t2ru$1@.ID-131901.news.uni-berlin.de>...

> "Joe "Nuke Me Xemu" Foster" <joe@.bftsi0.UUCP> wrote in message
> news:1071189386.456990@.news-1.nethere.net...
> > Did you really mean to claim that ALL non-nullable attributes MUST
> > 'logically' be included as part of the primary key?!
> Well, not really! I was just throwing in another option - where if the
> existance of one entity is dependent on another, then you can make the PK of
> that entity part of a composite key in the dependent entity. It's an
> alternative to just non nullable foreign keys, where the related column(s)
> become part of a primary key, rather than just a foreign key. Sorry, I think
> I need to take my anti-waffle pill, can't seem to put a good explanation
> together 8-)

The ClientID by itself should probably be the primary key, though
the GroupID could be made part of an alternate candidate key.

> > > Otherwise, if the Client could optionally belong to one Group, the
> > > relationship would be captured in a link table, as you suggested in B?
> > > > GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)
> > This would avoid the null nonsense until someone does an outer join.
> That's true. So which option would you go for?

Maybe have a special "Loners" group? =) It's hard to say given
the information at hand. Yeah, I know, the usual cop-out...

--
Joe Foster <mailto:jlfoster%40znet.com> Sacrament R2-45 <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!|||"Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
news:brck8d$1t2ru$1@.ID-131901.news.uni-berlin.de...
> "Joe "Nuke Me Xemu" Foster" <joe@.bftsi0.UUCP> wrote in message
> news:1071189386.456990@.news-1.nethere.net...
> > Did you really mean to claim that ALL non-nullable attributes MUST
> > 'logically' be included as part of the primary key?!
> Well, not really! I was just throwing in another option - where if the
> existance of one entity is dependent on another, then you can make the PK
of
> that entity part of a composite key in the dependent entity. It's an
> alternative to just non nullable foreign keys, where the related column(s)
> become part of a primary key, rather than just a foreign key. Sorry, I
think
> I need to take my anti-waffle pill, can't seem to put a good explanation
> together 8-)

Please allow me to hang an important point off of your post. The bind you
find yourself in above is certainly not unique to you so there is no need to
take this personally.

Your bind above demonstrates a very real pitfall of confusing knowledge of a
specific tool with knowledge of fundamentals. I have seen numerous people
fall into this specific pit throughout my career. I figure at least a 90%
chance the tool you know is Erwin, and you are describing their
"identifying" vs. "non-identifying" relationships.

I have seen people using this tool create schemas with ridiculous six and
seven part compound primary keys and call it "normalization".

Your bind above also demonstrates the dangers of using a graphical crutch in
place of real thought and analysis.

I respectfully suggest you will find yourself much more effective if you
learn the fundamentals before the tools.|||Just a couple of things:

> Your bind above demonstrates a very real pitfall of confusing knowledge of
a
> specific tool with knowledge of fundamentals. I have seen numerous people
> fall into this specific pit throughout my career. I figure at least a 90%
> chance the tool you know is Erwin, and you are describing their
> "identifying" vs. "non-identifying" relationships.

Identifying and non-identifying relationships are not an Erwin thing. They
are an idef1x thing. Check FIPS publication 184:
http://www.itl.nist.gov/fipspubs/idef1x.doc.

> I have seen people using this tool create schemas with ridiculous six and
> seven part compound primary keys and call it "normalization".

Just because you have six and seven part compound keys does not mean that
you are not normalized. It may take that many different atomic bits to
uniquely identify something. If these compound keys are built from six
relationships, the chances of it being normalized are about as good as the
San Diego Chargers winning last years Super Bowl, but it is possible.

> Your bind above also demonstrates the dangers of using a graphical crutch
in
> place of real thought and analysis.

So you don't use data models? The graphical "crutch" as you call it is
pretty standard stuff. I have never considered data models controversial in
the least. Cannot question the need for thought and analysis though :)

> I respectfully suggest you will find yourself much more effective if you
> learn the fundamentals before the tools.

You are correct (cannot believe I am agreeing with you :) about just having
tool knowledge. Erwin is a great tool, but they do have some
terminology/practices that are not standard, and frankly the tool will let
you get away with murder. It's job is to let you draw pictures of your
data, not to give you a hard time. That is your job Bob :)

--
-----------------------
----
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

"Bob Badour" <bbadour@.golden.net> wrote in message
news:Vf6dnepaArIqnkeiRVn-tw@.golden.net...
> "Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
> news:brck8d$1t2ru$1@.ID-131901.news.uni-berlin.de...
> > "Joe "Nuke Me Xemu" Foster" <joe@.bftsi0.UUCP> wrote in message
> > news:1071189386.456990@.news-1.nethere.net...
> > > Did you really mean to claim that ALL non-nullable attributes MUST
> > > 'logically' be included as part of the primary key?!
> > Well, not really! I was just throwing in another option - where if the
> > existance of one entity is dependent on another, then you can make the
PK
> of
> > that entity part of a composite key in the dependent entity. It's an
> > alternative to just non nullable foreign keys, where the related
column(s)
> > become part of a primary key, rather than just a foreign key. Sorry, I
> think
> > I need to take my anti-waffle pill, can't seem to put a good explanation
> > together 8-)
> Please allow me to hang an important point off of your post. The bind you
> find yourself in above is certainly not unique to you so there is no need
to
> take this personally.
> Your bind above demonstrates a very real pitfall of confusing knowledge of
a
> specific tool with knowledge of fundamentals. I have seen numerous people
> fall into this specific pit throughout my career. I figure at least a 90%
> chance the tool you know is Erwin, and you are describing their
> "identifying" vs. "non-identifying" relationships.
> I have seen people using this tool create schemas with ridiculous six and
> seven part compound primary keys and call it "normalization".
> Your bind above also demonstrates the dangers of using a graphical crutch
in
> place of real thought and analysis.
> I respectfully suggest you will find yourself much more effective if you
> learn the fundamentals before the tools.|||"Bob Badour" <bbadour@.golden.net> wrote in message
news:Vf6dnepaArIqnkeiRVn-tw@.golden.net...
> "Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
> news:brck8d$1t2ru$1@.ID-131901.news.uni-berlin.de...
> > "Joe "Nuke Me Xemu" Foster" <joe@.bftsi0.UUCP> wrote in message
> > news:1071189386.456990@.news-1.nethere.net...
> > > Did you really mean to claim that ALL non-nullable attributes MUST
> > > 'logically' be included as part of the primary key?!
> > Well, not really! I was just throwing in another option - where if the
> > existance of one entity is dependent on another, then you can make the
PK
> of
> > that entity part of a composite key in the dependent entity. It's an
> > alternative to just non nullable foreign keys, where the related
column(s)
> > become part of a primary key, rather than just a foreign key. Sorry, I
> think
> > I need to take my anti-waffle pill, can't seem to put a good explanation
> > together 8-)
> Please allow me to hang an important point off of your post. The bind you
> find yourself in above is certainly not unique to you so there is no need
to
> take this personally.
> Your bind above demonstrates a very real pitfall of confusing knowledge of
a
> specific tool with knowledge of fundamentals. I have seen numerous people
> fall into this specific pit throughout my career. I figure at least a 90%
> chance the tool you know is Erwin, and you are describing their
> "identifying" vs. "non-identifying" relationships.

Interestingly, I have used Erwin, but only briefly! My knowledge of this
technique came from something tought in relational theory during my degree.
Basically, we were being shown how to transition from conceptual ER diagrams
to a physical model, and this specific technique was to be used if one
entity's existance was dependent on another. I even recall the classroom
example! This was along the lines of if you had the entities Cinema and
CinemaScreen, then the existance of the screen might be dependent on the
cinema (no screen without a cinema kinda thing). Therefore, the PK of the
cinema would 'propogage' down to form part of the CinemaScreens PK. I'm not
really bothered about the context, this just did seem like a logical thing
to do.

Don't worry, I haven't taken this personally! However, having learnt this
approach well before sitting down and trying to use a RDBMS, I found that
when using any RDBMS, they seemed to support the concept of a column that is
part of a primary key, and a foreign key also. So, way back then I never
questioned it.

> I have seen people using this tool create schemas with ridiculous six and
> seven part compound primary keys and call it "normalization".

Yeah, I've fallen into this trap once or twice (although not quite so far!)

> Your bind above also demonstrates the dangers of using a graphical crutch
in
> place of real thought and analysis.
> I respectfully suggest you will find yourself much more effective if you
> learn the fundamentals before the tools.

A fair suggestion, although I thought I knew at least most of the
fundamentals! I've always put learning this before learnign the tools. That
way, when you come to learn the tools, it os interesting to see if/how they
supported the things you want to achieve, rather than pushing buttons seeing
what the tool could do, and then trying to understand it!

Just out of interest, what would you describe as the fundamentals?

Tobes|||"Tobin Harris" <tobin_dont_you_spam_me@.breathemail.net> wrote in message
news:brddal$26unq$1@.ID-135366.news.uni-berlin.de...
> "Bob Badour" <bbadour@.golden.net> wrote in message
> news:Vf6dnepaArIqnkeiRVn-tw@.golden.net...
> > "Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
> > news:brck8d$1t2ru$1@.ID-131901.news.uni-berlin.de...
> > > > "Joe "Nuke Me Xemu" Foster" <joe@.bftsi0.UUCP> wrote in message
> > > news:1071189386.456990@.news-1.nethere.net...
> > > > Did you really mean to claim that ALL non-nullable attributes MUST
> > > > 'logically' be included as part of the primary key?!
> > > > Well, not really! I was just throwing in another option - where if the
> > > existance of one entity is dependent on another, then you can make the
> PK
> > of
> > > that entity part of a composite key in the dependent entity. It's an
> > > alternative to just non nullable foreign keys, where the related
> column(s)
> > > become part of a primary key, rather than just a foreign key. Sorry, I
> > think
> > > I need to take my anti-waffle pill, can't seem to put a good
explanation
> > > together 8-)
> > Please allow me to hang an important point off of your post. The bind
you
> > find yourself in above is certainly not unique to you so there is no
need
> to
> > take this personally.
> > Your bind above demonstrates a very real pitfall of confusing knowledge
of
> a
> > specific tool with knowledge of fundamentals. I have seen numerous
people
> > fall into this specific pit throughout my career. I figure at least a
90%
> > chance the tool you know is Erwin, and you are describing their
> > "identifying" vs. "non-identifying" relationships.
> Interestingly, I have used Erwin, but only briefly! My knowledge of this
> technique came from something tought in relational theory during my
degree.
> Basically, we were being shown how to transition from conceptual ER
diagrams
> to a physical model, and this specific technique was to be used if one
> entity's existance was dependent on another. I even recall the classroom
> example!

I doubt, then, you were actually taught any relational theory. With the
current state of the education, I do not find that surprising.

> Don't worry, I haven't taken this personally! However, having learnt this
> approach well before sitting down and trying to use a RDBMS, I found that
> when using any RDBMS, they seemed to support the concept of a column that
is
> part of a primary key, and a foreign key also. So, way back then I never
> questioned it.

The candidate keys and foreign keys within a relation are generally
independent of one another and can overlap. Of course, a correspondence
exists between a foreign key in a referencing relation and a candidate key
in the referenced relation. I said "generally independent" above because in
the case that a relation refers to itself, the foreign key and candidate key
are in the same relation.

Whether some or all of a foreign key forms some or all of a candidate key
has no particular importance to me.

> > I have seen people using this tool create schemas with ridiculous six
and
> > seven part compound primary keys and call it "normalization".
> Yeah, I've fallen into this trap once or twice (although not quite so
far!)
> > Your bind above also demonstrates the dangers of using a graphical
crutch
> in
> > place of real thought and analysis.
> > I respectfully suggest you will find yourself much more effective if you
> > learn the fundamentals before the tools.
> A fair suggestion, although I thought I knew at least most of the
> fundamentals! I've always put learning this before learnign the tools.
That
> way, when you come to learn the tools, it os interesting to see if/how
they
> supported the things you want to achieve, rather than pushing buttons
seeing
> what the tool could do, and then trying to understand it!
> Just out of interest, what would you describe as the fundamentals?

Chris Date's _Introduction to Database Management Systems_ makes a good
start at them. I would seem foolish to try to teach them in an email
message.

One would start with "What is data?" and "What does it mean to manage data?"
From there, one would move to: "What principles facilitate or guide
effective data management?" And onward...

Since you apparently think one can easily enumerate them in an email, what
would you describe as the fundamentals?|||"Bob Badour" <bbadour@.golden.net> wrote in message
news:tPGdndKS74g91Eei4p2dnA@.golden.net...

> I doubt, then, you were actually taught any relational theory. With the
> current state of the education, I do not find that surprising.

> One would start with "What is data?"

If I add this data to that data do I have 2 datas?|||"Bob Badour" <bbadour@.golden.net> wrote in message <news:tPGdndKS74g91Eei4p2dnA@.golden.net>...

> I doubt, then, you were actually taught any relational theory. With the
> current state of the education, I do not find that surprising.

At my alma mater, UCSB, relational theory was an elective, but
at least it was available at all. =/

> Chris Date's _Introduction to Database Management Systems_ makes a good
> start at them. I would seem foolish to try to teach them in an email
> message.

I have the seventh edition. Is there a definitive list of the
changes made to the eighth, perhaps at http://dbdebunk.com/ ?

--
Joe Foster <mailto:jlfoster%40znet.com> "Regged" again? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!|||"Bob Badour" <bbadour@.golden.net> wrote in message
news:tPGdndKS74g91Eei4p2dnA@.golden.net...
> Chris Date's _Introduction to Database Management Systems_ makes a good
> start at them. I would seem foolish to try to teach them in an email
> message.

Don't worry Bob, I wasn't expecting you to seem foolish, or give a full
tutorial.

> One would start with "What is data?" and "What does it mean to manage
data?"
> From there, one would move to: "What principles facilitate or guide
> effective data management?" And onward...

Ok, this makes sense.

> Since you apparently think one can easily enumerate them in an email, what
> would you describe as the fundamentals?

I hadn't even considered whether it was difficult or not. I was simply
interested in what your perceived "fundamentals" entailed, mainly so I could
go and learn more... I kind of expected you to mention some general topics,
which may or may not have included:

Normalization - learning how to extrapolate to 1st, 2nd and 3rd normal form
schemas
Integrety - learning that integrety applies at various levels - Domain,
Column, Table, Database (Referential)
Data Types - seen as sets of permissable values that enforce business rules
by constraining the data that is stored.
Top-Down Analysis - learning to identify entities and business rules by
reading existing documentation, verbal communication etc
Bottom Up Analysis - learning to derive and normalise attribute listings
Keys and Identity - different types and why|||"Tobin Harris" <tobin_dont_you_spam_me@.breathemail.net> wrote in message
news:brnvmp$5gqn4$1@.ID-135366.news.uni-berlin.de...
> "Bob Badour" <bbadour@.golden.net> wrote in message
> news:tPGdndKS74g91Eei4p2dnA@.golden.net...
> > Chris Date's _Introduction to Database Management Systems_ makes a good
> > start at them. I would seem foolish to try to teach them in an email
> > message.
> Don't worry Bob, I wasn't expecting you to seem foolish, or give a full
> tutorial.
> > One would start with "What is data?" and "What does it mean to manage
> data?"
> > From there, one would move to: "What principles facilitate or guide
> > effective data management?" And onward...
> Ok, this makes sense.
> > Since you apparently think one can easily enumerate them in an email,
what
> > would you describe as the fundamentals?
> I hadn't even considered whether it was difficult or not. I was simply
> interested in what your perceived "fundamentals" entailed, mainly so I
could
> go and learn more... I kind of expected you to mention some general
topics,
> which may or may not have included:
> Normalization - learning how to extrapolate to 1st, 2nd and 3rd normal
form
> schemas
> Integrety - learning that integrety applies at various levels - Domain,
> Column, Table, Database (Referential)
> Data Types - seen as sets of permissable values that enforce business
rules
> by constraining the data that is stored.
> Top-Down Analysis - learning to identify entities and business rules by
> reading existing documentation, verbal communication etc
> Bottom Up Analysis - learning to derive and normalise attribute listings
> Keys and Identity - different types and why

Your list of "fundamentals" does not answer any of the questions "What is
data?", "What does it mean to manage data?" or "What principles facilitate
or guide effective data management?"

Of the items in your list above, integrity and data types are fundamental,
but your elaborations above are anything but fundamental.

One can come up with any number of taxonomies for integrity
constraints--Chris Date has published enough of them in his career. The
taxonomy I find most enlightening is: All integrity constraints constrain
variables. Integrity is fundamental because it is fundamental to the
manipulation function when managing data.

A data type does not enforce business rules--the integrity function of the
dbms does this. Data type is fundamental to computing and not only to data
management. A data type comprises both a set of values and a set of
operations on those values. With respect to the relational model, Date and
Darwen have observed that data types define what we can make statements
about, and relations make statements about them.|||"Bob Badour" <bbadour@.golden.net> wrote in message
news:aoCdnbmkVbe0SUKiRVn-tA@.golden.net...
> Your list of "fundamentals" does not answer any of the questions "What is
> data?", "What does it mean to manage data?" or "What principles facilitate
> or guide effective data management?"

In that case I'd be interested in learning some of these fundamentals. I may
have to take myself to the library...

> Of the items in your list above, integrity and data types are fundamental,
> but your elaborations above are anything but fundamental.
> One can come up with any number of taxonomies for integrity
> constraints--Chris Date has published enough of them in his career. The
> taxonomy I find most enlightening is: All integrity constraints constrain
> variables. Integrity is fundamental because it is fundamental to the
> manipulation function when managing data.
> A data type does not enforce business rules--the integrity function of the
> dbms does this. Data type is fundamental to computing and not only to data
> management. A data type comprises both a set of values and a set of
> operations on those values. With respect to the relational model, Date and
> Darwen have observed that data types define what we can make statements
> about, and relations make statements about them.

Hmmm, I thought Data Types (including UDTs) did enforce business rules, by
constraining the set of possible values that can be stored in a column
constrained to that type. If a business rule dictates that data of a certain
type must fall within a spefic range, for example, then by defining a type
that imposes this constraint, the business rule could be enforced by the
Data Type?

Thanks for your reply

Tobes|||"Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
news:brq3iu$5nfbc$1@.ID-131901.news.uni-berlin.de...

> Hmmm, I thought Data Types (including UDTs) did enforce business rules, by
> constraining the set of possible values that can be stored in a column
> constrained to that type. If a business rule dictates that data of a
certain
> type must fall within a spefic range, for example, then by defining a type
> that imposes this constraint, the business rule could be enforced by the
> Data Type?

The type of data type chosen is the first step in enforcing business rules.
Clearly if the business rule states this will be an integer between -100 and
100, then you first choose a datatype. In this case, you might go with a
smallint, or just an integer. Then you apply a check constraint. A proper
Domain or a User Defined Type will include the datatype and some of the
checking needed. If you chose a varchar for instance, the user would be
able to insert whatever into the column, unless you built more elaborate
checking into your column.

--
-----------------------
----
Louis Davidson (drsql@.hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)|||"Tobes (Breath)" <tobin_dont_spam_me@.breathemail.net> wrote in message
news:brq3iu$5nfbc$1@.ID-131901.news.uni-berlin.de...
> "Bob Badour" <bbadour@.golden.net> wrote in message
> news:aoCdnbmkVbe0SUKiRVn-tA@.golden.net...
> > Your list of "fundamentals" does not answer any of the questions "What
is
> > data?", "What does it mean to manage data?" or "What principles
facilitate
> > or guide effective data management?"
> In that case I'd be interested in learning some of these fundamentals. I
may
> have to take myself to the library...

Try to find a library with a copy of the ISO/IEC Standard Vocabularies for
Information Technology. A friend drew my attention to an article in IEEE
Compute called _The Great Term Robbery_ a few years ago; I found both that
article and the standard vocabularies very informative with respect to "What
is data?".

I have never found a succinct list of principles, and if anyone knows of
one, I would love to see it. Codd's 12 Rules embody a lot of principles he
did not name explicitly; although, logical identity, guaranteed access,
physical and logical independence are all principles. Certainly, the
principle of separating concerns applies to data management in several ways.
As a general principle, one prefers to minimize, centralize and automate any
need for highly specialized or arcane knowledge. One prefers to maximize the
portability of one's data. One prefers to make easy things easy and to make
likely errors difficult. One prefers to minimize the learning curve for
casual users. etc.

> > Of the items in your list above, integrity and data types are
fundamental,
> > but your elaborations above are anything but fundamental.
> > One can come up with any number of taxonomies for integrity
> > constraints--Chris Date has published enough of them in his career. The
> > taxonomy I find most enlightening is: All integrity constraints
constrain
> > variables. Integrity is fundamental because it is fundamental to the
> > manipulation function when managing data.
> > A data type does not enforce business rules--the integrity function of
the
> > dbms does this. Data type is fundamental to computing and not only to
data
> > management. A data type comprises both a set of values and a set of
> > operations on those values. With respect to the relational model, Date
and
> > Darwen have observed that data types define what we can make statements
> > about, and relations make statements about them.
> Hmmm, I thought Data Types (including UDTs) did enforce business rules, by
> constraining the set of possible values that can be stored in a column
> constrained to that type.

Data types form part of the definition of some constraints, but the
integrity function of the dbms enforces constraints. What you suggest above
is similar to suggesting that legislation and street signs enforce traffic
laws. Police officers and the judiciary enforce traffic laws.

> If a business rule dictates that data of a certain
> type must fall within a spefic range, for example, then by defining a type
> that imposes this constraint, the business rule could be enforced by the
> Data Type?

The type does not impose the constraint; the integrity function of the dbms
imposes the constraint. The type merely describes the constraint. For a very
long time, almost all constraints in commerical SQL dbmses were nothing more
than comments. One was allowed to express them, but the integrity function
of the dbms ignored them (if one can really claim an integrity function even
exists in that situation).

can avoid this rollback ??

Hi,
We're testing Transactional replication with updatable subscriber. We have
kept a machine @.branch office which is the publisher and the subscriber is at
the head office. Publisher is win 2003 server with win 2000 sp4 and
subscriber is a cluster with win 2003 and sql 2000 sp3. For small commands
replication is quick but for some specific processes invloving many
transactions, the replication backlog is huge.
We used profiler and found that there is a table whci is deleted completely
during the process and populated again with current data. The table has
almost 80 thousand rows. The profiler shows that sp_MSdel is run for every
row and then sp_MSins is run to insert data. this process takes lot of time
and creates backlog.
After the table got repopulated at subscriber and replication procs started
inserting data in another table, the publisher machine got switched off
accidently by a person at branch. When we started the machine next time, the
second tables at subscriber was empty and the profiler showed that again the
sp_MSdel was being executed at subscriber.
Does this mean that , during the process, if ne of the participating machine
is not available, the whole process will be restarted?
Why it strts with the table it had already replicated?can I stop this?
Ne suggestions?
regards,
have a look at the transaction on the publisher which caused this process.
It is probably a large update statement which is not being done as an update
in place on the subscriber, but rather being decomposed into a series of
delete and then update statements. See if you can identify if this action
was initiated by a stored procedure on the publisher and if so consider
replicating the execution of this stored procedure. You might also want to
look at using the trace flag mentioned in the following kb article which
will force an update in place.
http://support.microsoft.com/kb/302341/EN-US/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"k_s" <ks@.discussions.microsoft.com> wrote in message
news:40CEB69C-0E43-4A93-A89D-60A9BBFD08DE@.microsoft.com...
> Hi,
> We're testing Transactional replication with updatable subscriber. We have
> kept a machine @.branch office which is the publisher and the subscriber is
> at
> the head office. Publisher is win 2003 server with win 2000 sp4 and
> subscriber is a cluster with win 2003 and sql 2000 sp3. For small commands
> replication is quick but for some specific processes invloving many
> transactions, the replication backlog is huge.
> We used profiler and found that there is a table whci is deleted
> completely
> during the process and populated again with current data. The table has
> almost 80 thousand rows. The profiler shows that sp_MSdel is run for every
> row and then sp_MSins is run to insert data. this process takes lot of
> time
> and creates backlog.
> After the table got repopulated at subscriber and replication procs
> started
> inserting data in another table, the publisher machine got switched off
> accidently by a person at branch. When we started the machine next time,
> the
> second tables at subscriber was empty and the profiler showed that again
> the
> sp_MSdel was being executed at subscriber.
> Does this mean that , during the process, if ne of the participating
> machine
> is not available, the whole process will be restarted?
> Why it strts with the table it had already replicated?can I stop this?
> Ne suggestions?
> regards,
|||Thanks for the prompt reply Hilary.
The process is actually deleting a table completely and then inserting rows
into it. This is done by a stored procedure.
Could u give me ne link to get more idea about the 'replicatin execution of
sp'.
We told the application maintenance guys this issue, anmd asked them to
change the logic to update instead of competely emptying the table.
"Hilary Cotter" wrote:

> have a look at the transaction on the publisher which caused this process.
> It is probably a large update statement which is not being done as an update
> in place on the subscriber, but rather being decomposed into a series of
> delete and then update statements. See if you can identify if this action
> was initiated by a stored procedure on the publisher and if so consider
> replicating the execution of this stored procedure. You might also want to
> look at using the trace flag mentioned in the following kb article which
> will force an update in place.
> http://support.microsoft.com/kb/302341/EN-US/
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "k_s" <ks@.discussions.microsoft.com> wrote in message
> news:40CEB69C-0E43-4A93-A89D-60A9BBFD08DE@.microsoft.com...
>
>
|||Is it possible that you are doing a cascading update of the entire table or
a large portion of it?
Regarding replicating the execution of stored procedures have a look at
Publishing Stored Procedure Execution in BOL.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"k_s" <ks@.discussions.microsoft.com> wrote in message
news:35203F89-5688-497D-B46B-F8D5B85728A1@.microsoft.com...[vbcol=seagreen]
> Thanks for the prompt reply Hilary.
> The process is actually deleting a table completely and then inserting
> rows
> into it. This is done by a stored procedure.
> Could u give me ne link to get more idea about the 'replicatin execution
> of
> sp'.
> We told the application maintenance guys this issue, anmd asked them to
> change the logic to update instead of competely emptying the table.
> "Hilary Cotter" wrote:
|||Couldn't get u. Presently it's not update. It's a history table which
maintens one day history. So everyday, it is cleared and working table's data
is shifted ti it
I saw and tried the sp execution , works well. But in my case it won't fit,
as I have my procedure calling another procedure and BOL says that won't b
supported.
: (
"Hilary Cotter" wrote:

> Is it possible that you are doing a cascading update of the entire table or
> a large portion of it?
> Regarding replicating the execution of stored procedures have a look at
> Publishing Stored Procedure Execution in BOL.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "k_s" <ks@.discussions.microsoft.com> wrote in message
> news:35203F89-5688-497D-B46B-F8D5B85728A1@.microsoft.com...
>
>