Can I add a record number as data passes through

Hello.

In SSIS, is it possible to add a record number to each row of data as I copy it from the source to the destination.

An example of my source data is below, For each MemberID want to record the number of times it occurs in the table.

MemberID

2898

2899

2899

What I want it to look like when it gets to the destination is:

MemberID RecordNumber

2898 1

2899 1

2899 2

Like an Identity column I suppose, not for the whole table but for each MemberID.

Thanks

Looks possible to me using a custom script component to compare incoming fields to the last set coming in. Would work nicely if the data is sorted.|||

bobbins wrote:

Hello.

In SSIS, is it possible to add a record number to each row of data as I copy it from the source to the destination.

An example of my source data is below, For each MemberID want to record the number of times it occurs in the table.

MemberID

2898

2899

2899

What I want it to look like when it gets to the destination is:

MemberID RecordNumber

2898 1

2899 1

2899 2

Like an Identity column I suppose, not for the whole table but for each MemberID.

Thanks

The Rank transformation will do this for you:

Rank Transform
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx

-Jamie

|||

Jamie:

That's a very cool and useful component. Thanks! Great work.

- Mike

|||

mike.groh wrote:

Jamie:

That's a very cool and useful component. Thanks! Great work.

- Mike

Mike,

Its a pleasure. I've been after that sort of feedback for a long time :)

Again I have to reiterate Darren Green's contribution. It was my idea but he realised the idea.

-Jamie

|||

Thanks for the info, I haven't got round to doing this yet but I'm sure it'll work.

Thanks very much, top people!

|||

I've downloaded it and installed it but how do I use it in my packages? Should I now be able to see it in Visual Studio amongst all the other data flow transformation options.

Cheers

|||

bobbins wrote:

I've downloaded it and installed it but how do I use it in my packages? Should I now be able to see it in Visual Studio amongst all the other data flow transformation options.

Cheers

The instructions here: http://msdn2.microsoft.com/fr-fr/library/ms136125.aspx are for custom tasks but its intuitively similar for custom components. Look for the section entitled "How to use the Task in SSIS Designer"

-Jamie

|||If you just want the row number function, then use the Row Number Tx http://www.sqlis.com/default.aspx?93, as it does not require a sorted input.|||

I still can't do it because when I select 'Choose toolbox items' in the data flow designer it just list what's there already, I only have an option to browse on the COM and .NET components, is this where I should be putting it?

Thanks.

|||

You are looking at the Data Flow page in the Choose Toolbox Items?

You have scolled down to find the transform, in alphabetical order?

Double check this, and perhaps compare with some screenshots here-

Konesans - Frequently Asked Questions - How do I install a task or transform component?
(http://www.konesans.com/faq.aspx#installtask)

If this does not help, on the machine you are running Visual Studio, go to C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents, and look for the DLL file. I'm not sure which transform you are trying right now, so I cannot specify the filename for you here. If the file is not there, then it will never show up in Choose Toolbox Items, so did the install fail?

|||

Yes I am.

Yes I have, it's definately not there. I have checked against the link you provided and I cannot see the component in the list.

I am attempting to use the RankTransform component. I think it installed ok as there were no errors. I've searched for the dll ( it's full name is Conchango.SQLServer.SSIS.DataFlow.RankTransform.dll ) found that it is located in C:\Program Files\Microsof SQL Server\90\DTS\CompFldr so this looks like conformation that it has installed ok.

I've copied it to the directory that you specified and opened the 'choose items' again and it was listed, so I've added it.

I'd like to say a big thanks to yourself and Jamie for the help with this, as someone who doesn't know much about Visual Studio and dll's and stuff you've been an massive help.

I'll now try to use it (I'll have to go back to the instructions!) and let you know how it goes.

Thanks again

|||

Ok, the component works great but it is not putting in what I expected it to:

My source data is:

Member Number

2898

2899

2899

I added a sort task to sort it by Member Number ascending.

In the rank transform task I selected Member Number as the sort key and checked the partition box and selected row number as the output.

When the data reached the destination it looked like this:

Member Number Row Number

2898 1

2899 2

2899 3

I'd expected it to look like this:

Member Number Row Number

2898 1

2899 1

2899 2

because I assumed from what I'd selected in the rank transform task that it's essentially running this query:

SELECT [Member Number] , ROW_NUMBER() OVER (PARTITION BY [Member Number] ORDER BY [Member Number]) AS [Row Number]

FROM StatusHistory

which does give me the expected results.

Have I selected the wrong options in the rank transform task or I have I completely got the wrong end of the stick and am trying to use this task for something that it was not designed for?

It's a great thing to have anyway.

Thanks

|||

bobbins wrote:

Ok, the component works great but it is not putting in what I expected it to:

My source data is:

Member Number

2898

2899

2899

I added a sort task to sort it by Member Number ascending.

In the rank transform task I selected Member Number as the sort key and checked the partition box and selected row number as the output.

When the data reached the destination it looked like this:

Member Number Row Number

2898 1

2899 2

2899 3

I'd expected it to look like this:

Member Number Row Number

2898 1

2899 1

2899 2

because I assumed from what I'd selected in the rank transform task that it's essentially running this query:

SELECT [Member Number] , ROW_NUMBER() OVER (PARTITION BY [Member Number] ORDER BY [Member Number]) AS [Row Number]

FROM StatusHistory

which does give me the expected results.

Have I selected the wrong options in the rank transform task or I have I completely got the wrong end of the stick and am trying to use this task for something that it was not designed for?

It's a great thing to have anyway.

Thanks

Bobbins,

Thankyou for making me aware of this. it looks as though the partition functionality might not be working. I'll check it out.

-Jamie

|||

Had any luck with it?

Cheers

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 a stored procedure open excel and call a macro? or vice versa

I googled it but found nothing.
I've seen where excel can call a stored procedure to return a record set but
what if my stored procedure returns several record sets, how does excel
handle that?
Thanks1. Within SQL Server you can call xp_cmdshell, to OPEN excel file
2. Enabling xp_cmdshell has some drawbacks (SQL Injection,
Security...), watch out for that
3. Macro is a Part of Excel, which runs when you open the excel file so
I doubt SQL has anything to do with that
4. Opening excel file will happen @. server rather than client, might
need to look for that
5. Once Excel is OPEN SQL has no reference pointer to excel file, its
like I opened the file & I'm done
HTH
PP
Mike wrote:
> I googled it but found nothing.
> I've seen where excel can call a stored procedure to return a record set b
ut
> what if my stored procedure returns several record sets, how does excel
> handle that?
> Thanks

Can a stored procedure open excel and call a macro? or vice versa

I googled it but found nothing.
I've seen where excel can call a stored procedure to return a record set but
what if my stored procedure returns several record sets, how does excel
handle that?
Thanks1. Within SQL Server you can call xp_cmdshell, to OPEN excel file
2. Enabling xp_cmdshell has some drawbacks (SQL Injection,
Security...), watch out for that
3. Macro is a Part of Excel, which runs when you open the excel file so
I doubt SQL has anything to do with that
4. Opening excel file will happen @. server rather than client, might
need to look for that
5. Once Excel is OPEN SQL has no reference pointer to excel file, its
like I opened the file & I'm done :)
HTH
PP
Mike wrote:
> I googled it but found nothing.
> I've seen where excel can call a stored procedure to return a record set but
> what if my stored procedure returns several record sets, how does excel
> handle that?
> Thanks

Can a stored proc swallow an error

I have a table I insert a record into to give access to a user. It uses
primary keys so duplicates are not allowed, so trying to add a record
for a user more than once is not allowed.

In my .NET programs, sometimes it's easier to let the user select a
group of people to give access to eben if some of them may already have
it.

Of course this throws an exception an an error message. Now I could
catch and ignore the message in .NET for this operation but then I'm
stuck if something is genuinely wrong.

So is there a way to do this? :
In my stored procedure determine if an error occured because of a
duplicate key and somehow not cause an exception to be returned to
ADO.NET in that case?Can't you just change the INSERT statement so that it won't insert duplicate
rows? For Example:

INSERT INTO foo (user, ...)
SELECT 'Smith', ...
WHERE NOT EXISTS
(SELECT *
FROM foo
WHERE user = 'Smith') ;

or

INSERT INTO foo (user, ...)
SELECT user, ...
FROM bar
LEFT JOIN foo
ON foo.user = bar.user
WHERE foo.user IS NULL
AND ... ;

--
David Portas
SQL Server MVP
--|||Yes, I could check for the records existance before hand. But I wanted
to know if you can detect when an error occurs in T-SQL, and handle it
w/o causing an exception to be thrown in ADO.NET.|||<wackyphill@.yahoo.com> wrote in message
news:1104775581.290491.75140@.f14g2000cwb.googlegro ups.com...
>I have a table I insert a record into to give access to a user. It uses
> primary keys so duplicates are not allowed, so trying to add a record
> for a user more than once is not allowed.
> In my .NET programs, sometimes it's easier to let the user select a
> group of people to give access to eben if some of them may already have
> it.
> Of course this throws an exception an an error message. Now I could
> catch and ignore the message in .NET for this operation but then I'm
> stuck if something is genuinely wrong.
> So is there a way to do this? :
> In my stored procedure determine if an error occured because of a
> duplicate key and somehow not cause an exception to be returned to
> ADO.NET in that case?

Unfortunately, error handling in MSSQL (at least up to version 2000) is
somewhat limited - see these articles for more details:

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

These sections in particular may be useful for you:

http://www.sommarskog.se/error-hand...tml#client-code
http://www.sommarskog.se/error-handling-I.html#ADO.Net

Simon|||> Yes, I could check for the records existance before hand.

Not beforehand - in the INSERT statement itself.

> I wanted
> to know if you can detect when an error occurs in T-SQL, and handle it
> w/o causing an exception to be thrown in ADO.NET.

See the articles that Simon posted but IMO a stored procedure that requires
you to ignore an error for correct inputs is not a good stored procedure -
it won't fail safe and real problems may go undetected.

--
David Portas
SQL Server MVP
--|||(wackyphill@.yahoo.com) writes:
> I have a table I insert a record into to give access to a user. It uses
> primary keys so duplicates are not allowed, so trying to add a record
> for a user more than once is not allowed.
> In my .NET programs, sometimes it's easier to let the user select a
> group of people to give access to eben if some of them may already have
> it.
> Of course this throws an exception an an error message. Now I could
> catch and ignore the message in .NET for this operation but then I'm
> stuck if something is genuinely wrong.
> So is there a way to do this? :
> In my stored procedure determine if an error occured because of a
> duplicate key and somehow not cause an exception to be returned to
> ADO.NET in that case?

In SQL 2000, no. In the next version of SQL Server, SQL 2005 currently
in beta, yes.

But there is really not that big difference between catching the error in
SQL or in .Net. In the .Net excrption you ignore if the error number 2627
or else you rethrow. But admittedly, it's nicer to do this in the SQL
code, since you keep the error-handling logic closer to the test.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Can a SQL Server stored procedure do that ?

There is a stored procedure, namely sp_spaceused, to find out the space used
of a particular database.
But now, I also want to record the free hard disk space of each of my
server's logical drives, eg. C drive, D drive, ... into my SQL Server
database, is there any stored procedure just like sp_spaceused to find a
logical disk's free space ?
Try this undocumented xp:
master.dbo.xp_fixeddrives
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:ca9qn3$oec1@.imsp212.netvigator.com...
> There is a stored procedure, namely sp_spaceused, to find out the space
used
> of a particular database.
> But now, I also want to record the free hard disk space of each of my
> server's logical drives, eg. C drive, D drive, ... into my SQL Server
> database, is there any stored procedure just like sp_spaceused to find a
> logical disk's free space ?
>
|||you can take a look at following url.
http://www.databasejournal.com/scrip...le.php/1470811
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||More information about how xp_fixeddrives might be used.
http://databasejournal.com/features/...le.php/3080501
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:ca9qn3$oec1@.imsp212.netvigator.com...
> There is a stored procedure, namely sp_spaceused, to find out the space
used
> of a particular database.
> But now, I also want to record the free hard disk space of each of my
> server's logical drives, eg. C drive, D drive, ... into my SQL Server
> database, is there any stored procedure just like sp_spaceused to find a
> logical disk's free space ?
>

Can a SQL Server Stored Procedure do that ?

There is a stored procedure, namely sp_spaceused, to find out the space used
of a particular database.
But now, I also want to record the free hard disk space of each of my
server's logical drives, eg. C drive, D drive, ... into my SQL Server
database, is there any stored procedure just like sp_spaceused to find a
logical disk's free space ?
There's a proc called xp_fixeddrives. It is not documented, though, so all usual warnings applies.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message news:c9sjpn$afq3@.imsp212.netvigator.com...
> There is a stored procedure, namely sp_spaceused, to find out the space used
> of a particular database.
> But now, I also want to record the free hard disk space of each of my
> server's logical drives, eg. C drive, D drive, ... into my SQL Server
> database, is there any stored procedure just like sp_spaceused to find a
> logical disk's free space ?
>
>
|||it's not not officially supported but take a look at
xp_fixeddrives
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c9sjpn$afq3@.imsp212.netvigator.com...
> There is a stored procedure, namely sp_spaceused, to find out the space
used
> of a particular database.
> But now, I also want to record the free hard disk space of each of my
> server's logical drives, eg. C drive, D drive, ... into my SQL Server
> database, is there any stored procedure just like sp_spaceused to find a
> logical disk's free space ?
>
>

Can a SQL Server stored procedure do that ?

There is a stored procedure, namely sp_spaceused, to find out the space used
of a particular database.
But now, I also want to record the free hard disk space of each of my
server's logical drives, eg. C drive, D drive, ... into my SQL Server
database, is there any stored procedure just like sp_spaceused to find a
logical disk's free space ?Try this undocumented xp:
master.dbo.xp_fixeddrives
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:ca9qn3$oec1@.imsp212.netvigator.com...
> There is a stored procedure, namely sp_spaceused, to find out the space
used
> of a particular database.
> But now, I also want to record the free hard disk space of each of my
> server's logical drives, eg. C drive, D drive, ... into my SQL Server
> database, is there any stored procedure just like sp_spaceused to find a
> logical disk's free space ?
>|||you can take a look at following url.
http://www.databasejournal.com/scri...cle.php/1470811
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||More information about how xp_fixeddrives might be used.
http://databasejournal.com/features...cle.php/3080501
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:ca9qn3$oec1@.imsp212.netvigator.com...
> There is a stored procedure, namely sp_spaceused, to find out the space
used
> of a particular database.
> But now, I also want to record the free hard disk space of each of my
> server's logical drives, eg. C drive, D drive, ... into my SQL Server
> database, is there any stored procedure just like sp_spaceused to find a
> logical disk's free space ?
>

Can a SQL Server Stored Procedure do that ?

There is a stored procedure, namely sp_spaceused, to find out the space used
of a particular database.
But now, I also want to record the free hard disk space of each of my
server's logical drives, eg. C drive, D drive, ... into my SQL Server
database, is there any stored procedure just like sp_spaceused to find a
logical disk's free space ?There's a proc called xp_fixeddrives. It is not documented, though, so all u
sual warnings applies.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message news:c9sjpn$afq3@.imsp212.netvigator.com.
.
> There is a stored procedure, namely sp_spaceused, to find out the space us
ed
> of a particular database.
> But now, I also want to record the free hard disk space of each of my
> server's logical drives, eg. C drive, D drive, ... into my SQL Server
> database, is there any stored procedure just like sp_spaceused to find a
> logical disk's free space ?
>
>|||it's not not officially supported but take a look at
xp_fixeddrives
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c9sjpn$afq3@.imsp212.netvigator.com...
> There is a stored procedure, namely sp_spaceused, to find out the space
used
> of a particular database.
> But now, I also want to record the free hard disk space of each of my
> server's logical drives, eg. C drive, D drive, ... into my SQL Server
> database, is there any stored procedure just like sp_spaceused to find a
> logical disk's free space ?
>
>

Can a SQL Server stored procedure do that ?

There is a stored procedure, namely sp_spaceused, to find out the space used
of a particular database.
But now, I also want to record the free hard disk space of each of my
server's logical drives, eg. C drive, D drive, ... into my SQL Server
database, is there any stored procedure just like sp_spaceused to find a
logical disk's free space ?Try this undocumented xp:
master.dbo.xp_fixeddrives
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:ca9qn3$oec1@.imsp212.netvigator.com...
> There is a stored procedure, namely sp_spaceused, to find out the space
used
> of a particular database.
> But now, I also want to record the free hard disk space of each of my
> server's logical drives, eg. C drive, D drive, ... into my SQL Server
> database, is there any stored procedure just like sp_spaceused to find a
> logical disk's free space ?
>|||you can take a look at following url.
http://www.databasejournal.com/scripts/article.php/1470811
--
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||More information about how xp_fixeddrives might be used.
http://databasejournal.com/features/mssql/article.php/3080501
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:ca9qn3$oec1@.imsp212.netvigator.com...
> There is a stored procedure, namely sp_spaceused, to find out the space
used
> of a particular database.
> But now, I also want to record the free hard disk space of each of my
> server's logical drives, eg. C drive, D drive, ... into my SQL Server
> database, is there any stored procedure just like sp_spaceused to find a
> logical disk's free space ?
>

Can a SQL Server Stored Procedure do that ?

There is a stored procedure, namely sp_spaceused, to find out the space used
of a particular database.
But now, I also want to record the free hard disk space of each of my
server's logical drives, eg. C drive, D drive, ... into my SQL Server
database, is there any stored procedure just like sp_spaceused to find a
logical disk's free space ?There's a proc called xp_fixeddrives. It is not documented, though, so all usual warnings applies.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message news:c9sjpn$afq3@.imsp212.netvigator.com...
> There is a stored procedure, namely sp_spaceused, to find out the space used
> of a particular database.
> But now, I also want to record the free hard disk space of each of my
> server's logical drives, eg. C drive, D drive, ... into my SQL Server
> database, is there any stored procedure just like sp_spaceused to find a
> logical disk's free space ?
>
>|||it's not not officially supported but take a look at
xp_fixeddrives
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c9sjpn$afq3@.imsp212.netvigator.com...
> There is a stored procedure, namely sp_spaceused, to find out the space
used
> of a particular database.
> But now, I also want to record the free hard disk space of each of my
> server's logical drives, eg. C drive, D drive, ... into my SQL Server
> database, is there any stored procedure just like sp_spaceused to find a
> logical disk's free space ?
>
>

Can *datastore.xml be used to populate template.ini as a substitute for setup.iss?

Objective

Do a manual installation of SQL Server 2005 (which could be an upgrade from SQL Server 2000) and record the steps. Replay the steps for an unattended installation of SQL Server 2005.

Problem

There isn't a setup.ini file for SQL Server 2005. There is a template.ini for unattended installations of SQL Server 2005, but it doesn't obviously map to selections on manual installation dialog boxes.

Desired workaround

Capture the steps of a manual installation and use them to populate template.ini.

Relevant background for a potential solution

A manual installation creates log files. So does an unattended installation. But the unattended installation writes all of the log files into a single zip file.

What is important to know is that an unattended installation creates an additional file, a *datasource.xml file. This XML file is found in the zip file with all of the other log files. If you open this XML file, you'll see that the attribute names correspond to keywords in template.ini.

Potential solution

If a manual installation could be forced to generate a *datasource.xml file, then it should be possible to map the settings in the file to keywords in template.ini.

So, how can a *datasource.xml file be created? In post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=359678&SiteID=1, Jeffrey Baker says "add LOGNAME=<path to cab> and run setup again". Unfortunately, these instructions are too vague. Where is this added? To the command line, to a setup.ini (if so, in which directory, and in which section should he addition be made?). I did lots of searches and couldn't find the answers.

Hi John,

In SQL2K we supported the option of "recording" the installation options to a settings file which could be used in subsequent installations. This functionality was not carried forward to SQL2K5.

Using the datasource.xml file, as mentioned above, is not a supported mechanism for creating an input template.ini file. You'll need to manually author the template.ini to contain the installation settings you wish.

Calling web service onclick of report column

We have a web service that reads a PDF file into binary and inserts the
binary text in a database, then returns an ID to the record. Is there a way
to call this web service from a Reporting Service Report column? The column
will have an Adobe image in the column and when clicked with call the web
service and read the record from the Database streaming the binary to a
browser window.
Thanks in advance!
RickOn Nov 27, 7:55 am, "Rick" <rfem...@.newsgroups.nospam> wrote:
> We have a web service that reads a PDF file into binary and inserts the
> binary text in a database, then returns an ID to the record. Is there a way
> to call this web service from a Reporting Service Report column? The column
> will have an Adobe image in the column and when clicked with call the web
> service and read the record from the Database streaming the binary to a
> browser window.
> Thanks in advance!
> Rick
You might try looking into using the Custom Code section of the report
(via: Layout tab >> Report drop-down tab >> Report Properties... >>
Code tab). Another long shot might be to use Jump to URL as part of
the Navigation Properties and pass the Web Service parameters as part
of an expression that calls the web service via URL. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Ok, I created a class library that makes a call to the web service and
referenced it in the report.
The web service uses system.web.httpresponse to stream the file to a
browser, so in order to make the class library work it required me to pass
in the httpresponse from the calling web page. So I tested that with a test
web site and it works as expected. I tried adding the same code to call the
class library using Custom Code and Navigation URL. The problem I have is
how to get the Httpresponse from the report to pass into the class library,
I made a reference to System.Web.Httpresponse in the report, but when I call
the code or use the navigation url it does not recognize HTTPResponse.
Any suggestions?
Error:
The Hyperlink expression for the image 'image1' contains an error: [BC30691]
'HttpResponse' is a type in 'Web' and cannot be used as an expression.
Navigation URL:
=webservicecall.webservicecall.getpdffile("filepath\filename.pdf",System.Web.HTTPResponse)
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:b5c663d9-9c91-4b50-8b56-bae9ef9f4788@.w34g2000hsg.googlegroups.com...
> On Nov 27, 7:55 am, "Rick" <rfem...@.newsgroups.nospam> wrote:
>> We have a web service that reads a PDF file into binary and inserts the
>> binary text in a database, then returns an ID to the record. Is there a
>> way
>> to call this web service from a Reporting Service Report column? The
>> column
>> will have an Adobe image in the column and when clicked with call the web
>> service and read the record from the Database streaming the binary to a
>> browser window.
>> Thanks in advance!
>> Rick
>
> You might try looking into using the Custom Code section of the report
> (via: Layout tab >> Report drop-down tab >> Report Properties... >>
> Code tab). Another long shot might be to use Jump to URL as part of
> the Navigation Properties and pass the Web Service parameters as part
> of an expression that calls the web service via URL. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
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.|||I could use some help. Here is what I have, I created a Class Library that
makes a call to a web service and referenced the class library in the
report, I setup code in the report properties code, The Public function
calls CallWebService2, CallWebService2 makes a call to a the class library
with the web service passing in a pdffiname, an ID Parameter and the
HTTPResponse, the web service in return streams the PDF to the browser, I
put a column on the report that has an adobe icon image, in this column in
the Jump to URL, I put =code.CallWebService(): When I do this the Jump To
URL does nothing. I'm not sure if I'm getting the HTTPResponse correctly.
Report Properties Code:
Public Function CallWebService() as String
CallWebService2()
Return "True"
End Function
Private Function CallWebService2() as Boolean
Dim nservice As New WebServiceCall.WebServiceCall
Dim response1 As System.Web.HttpResponse
WebServiceCall.WebServiceCall.GetPDFFile("filepath\filename",
"parameter1", Response1)
Return True
End Function
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:Ks7X3AzMIHA.6940@.TK2MSFTNGHUB02.phx.gbl...
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> 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.
>|||Hello Rick,
You could not use the Code in the Jump to URL directly.
You may add a new report which is call the web service to show the
information. And you may use the Jump to URL to the report.
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.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
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.

Calling update and insert in one stored procedure

Hello,

Basically i want to have a stored procedute which will do an insert statement if the record is not in the table and update if it exists.

The Id is not autonumber, so if the record doesn't exists the sp should return the last id+1 to use it for the new record.

Is there any example i can see, or somebody can help me with that?

Thanks a lot.

IF EXISTS (SELECT * FROM table WHERE <condition>)

BEGIN

--the record exists. So just do the UPDATE

END

ELSE

BEGIN

--The record does not exist. So do an INSERT

END

|||

This is very helpful, thanks!

If it exists, then how can i get the max value of the id column?

Thanks again.

|||SELECT MAX(column) FROM table WHERE <Condition>|||

Thank you very much for your help!