configuration settings for a database

[reposted from comp.databases.ms-sqlserver] - sorry
Hi,
I would like to know how to get a list of configuration settings for a
database.
Is there a table that I can select this info from?
Or a file (perhaps an ini file?) of some kind?
Things like timeout settings, rollback space, table space, etc.
Thanks
Hi,
Server wide parameters will be in MASTER database , sysconfigures tables
You can either use
select * from master..sysconfigures
or use
sp_configure system procedure to view and change parameters.
Database informations:-
You can get the database informations like, number of file groups (Table
space in oracle) from sysfiles tables of each database.
use <dbname>
go
select * from sysfiles
Thanks
Hari
MCDBA
"Disco Octopus" <discooctopusN05PAM@.yahoo.com> wrote in message
news:h_MEc.118$qd4.9010@.news.optus.net.au...
> [reposted from comp.databases.ms-sqlserver] - sorry
> Hi,
> I would like to know how to get a list of configuration settings for a
> database.
> Is there a table that I can select this info from?
> Or a file (perhaps an ini file?) of some kind?
> Things like timeout settings, rollback space, table space, etc.
> Thanks
>
|||run sp_helpdb in Query Analyzer and look at the status field
can do individually and do sp_helpdb <dbname> . ie sp_helpdb pubs
Or in Enterprise Manager , select database and change the default view to
Taskpad as well as Right Click / Properties and Options.
cheers,
Andy.
"Disco Octopus" <discooctopusN05PAM@.yahoo.com> wrote in message
news:h_MEc.118$qd4.9010@.news.optus.net.au...
> [reposted from comp.databases.ms-sqlserver] - sorry
> Hi,
> I would like to know how to get a list of configuration settings for a
> database.
> Is there a table that I can select this info from?
> Or a file (perhaps an ini file?) of some kind?
> Things like timeout settings, rollback space, table space, etc.
> Thanks
>

configuration settings for a database

[reposted from comp.databases.ms-sqlserver] - sorry
Hi,
I would like to know how to get a list of configuration settings for a
database.
Is there a table that I can select this info from?
Or a file (perhaps an ini file?) of some kind?
Things like timeout settings, rollback space, table space, etc.
ThanksHi,
Server wide parameters will be in MASTER database , sysconfigures tables
You can either use
select * from master..sysconfigures
or use
sp_configure system procedure to view and change parameters.
Database informations:-
You can get the database informations like, number of file groups (Table
space in oracle) from sysfiles tables of each database.
use <dbname>
go
select * from sysfiles
Thanks
Hari
MCDBA
"Disco Octopus" <discooctopusN05PAM@.yahoo.com> wrote in message
news:h_MEc.118$qd4.9010@.news.optus.net.au...
> [reposted from comp.databases.ms-sqlserver] - sorry
> Hi,
> I would like to know how to get a list of configuration settings for a
> database.
> Is there a table that I can select this info from?
> Or a file (perhaps an ini file?) of some kind?
> Things like timeout settings, rollback space, table space, etc.
> Thanks
>|||run sp_helpdb in Query Analyzer and look at the status field
can do individually and do sp_helpdb <dbname> . ie sp_helpdb pubs
Or in Enterprise Manager , select database and change the default view to
Taskpad as well as Right Click / Properties and Options.
cheers,
Andy.
"Disco Octopus" <discooctopusN05PAM@.yahoo.com> wrote in message
news:h_MEc.118$qd4.9010@.news.optus.net.au...
> [reposted from comp.databases.ms-sqlserver] - sorry
> Hi,
> I would like to know how to get a list of configuration settings for a
> database.
> Is there a table that I can select this info from?
> Or a file (perhaps an ini file?) of some kind?
> Things like timeout settings, rollback space, table space, etc.
> Thanks
>

configuration settings for a database

[reposted from comp.databases.ms-sqlserver] - sorry
Hi,
I would like to know how to get a list of configuration settings for a
database.
Is there a table that I can select this info from?
Or a file (perhaps an ini file?) of some kind?
Things like timeout settings, rollback space, table space, etc.
ThanksHi,
Server wide parameters will be in MASTER database , sysconfigures tables
You can either use
select * from master..sysconfigures
or use
sp_configure system procedure to view and change parameters.
Database informations:-
You can get the database informations like, number of file groups (Table
space in oracle) from sysfiles tables of each database.
use <dbname>
go
select * from sysfiles
--
Thanks
Hari
MCDBA
"Disco Octopus" <discooctopusN05PAM@.yahoo.com> wrote in message
news:h_MEc.118$qd4.9010@.news.optus.net.au...
> [reposted from comp.databases.ms-sqlserver] - sorry
> Hi,
> I would like to know how to get a list of configuration settings for a
> database.
> Is there a table that I can select this info from?
> Or a file (perhaps an ini file?) of some kind?
> Things like timeout settings, rollback space, table space, etc.
> Thanks
>|||run sp_helpdb in Query Analyzer and look at the status field
can do individually and do sp_helpdb <dbname> . ie sp_helpdb pubs
Or in Enterprise Manager , select database and change the default view to
Taskpad as well as Right Click / Properties and Options.
cheers,
Andy.
"Disco Octopus" <discooctopusN05PAM@.yahoo.com> wrote in message
news:h_MEc.118$qd4.9010@.news.optus.net.au...
> [reposted from comp.databases.ms-sqlserver] - sorry
> Hi,
> I would like to know how to get a list of configuration settings for a
> database.
> Is there a table that I can select this info from?
> Or a file (perhaps an ini file?) of some kind?
> Things like timeout settings, rollback space, table space, etc.
> Thanks
>

Configuration management tools for SQL Server 2000

Is there a simple tool available that will analyse two SQL Server 2000
databases and report the structural differences between them? A bonus would
be its ability to generate change scripts to bring one of them up to the
state of the other.
Thanks,
AlanCheck out www.red-gate.com
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in message
news:ejAex3lTEHA.2580@.TK2MSFTNGP12.phx.gbl...
Is there a simple tool available that will analyse two SQL Server 2000
databases and report the structural differences between them? A bonus would
be its ability to generate change scripts to bring one of them up to the
state of the other.
Thanks,
Alan|||I like SQL Compare from red gate, but there are several alternatives.
http://www.aspfaq.com/2209
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in message
news:ejAex3lTEHA.2580@.TK2MSFTNGP12.phx.gbl...
> Is there a simple tool available that will analyse two SQL Server 2000
> databases and report the structural differences between them? A bonus
would
> be its ability to generate change scripts to bring one of them up to the
> state of the other.
> Thanks,
> Alan
>|||Tom - are you on the Red-gate pay roll?
>--Original Message--
>Check out www.red-gate.com
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in
message
>news:ejAex3lTEHA.2580@.TK2MSFTNGP12.phx.gbl...
>Is there a simple tool available that will analyse two
SQL Server 2000
>databases and report the structural differences between
them? A bonus would
>be its ability to generate change scripts to bring one
of them up to the
>state of the other.
>Thanks,
>Alan
>
>.
>|||Nope. I just used the product liked it, and wrote a review on it in SQL
Pro.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"sb" <anonymous@.discussions.microsoft.com> wrote in message
news:1a43801c44e65$67f9e3f0$a301280a@.phx.gbl...
Tom - are you on the Red-gate pay roll?
>--Original Message--
>Check out www.red-gate.com
>
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in
message
>news:ejAex3lTEHA.2580@.TK2MSFTNGP12.phx.gbl...
>Is there a simple tool available that will analyse two
SQL Server 2000
>databases and report the structural differences between
them? A bonus would
>be its ability to generate change scripts to bring one
of them up to the
>state of the other.
>Thanks,
>Alan
>
>.
>|||I use ErWin and/OR ER-Studio.
Greg Jackson
PDX, Oregon|||Have you also done reviews on all the other similar
products to validate your opinion on what to recommend?
>--Original Message--
>Nope. I just used the product liked it, and wrote a
review on it in SQL
>Pro.
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>..
>"sb" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1a43801c44e65$67f9e3f0$a301280a@.phx.gbl...
>Tom - are you on the Red-gate pay roll?
>>--Original Message--
>>Check out www.red-gate.com
>>
>>--
>>Tom
>>---
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in
>message
>>news:ejAex3lTEHA.2580@.TK2MSFTNGP12.phx.gbl...
>>Is there a simple tool available that will analyse two
>SQL Server 2000
>>databases and report the structural differences between
>them? A bonus would
>>be its ability to generate change scripts to bring one
>of them up to the
>>state of the other.
>>Thanks,
>>Alan
>>
>>.
>.
>|||> Tom - are you on the Red-gate pay roll?
He is not, but what difference does that make? He's not forcing anyone to
use the product, he's telling the original poster to check it out.|||alot - it doesn't appear to be impartial.
Isn't being impartial a responsibility for anyone
(especially MVPs) when giving advice? If accurate non-
partial analysis was made with no emotion, that would be
a contribution worthy of the title MVP.
MVPs are a source of information which in the most part
is highly regarded. I would like to see a little more
than a mere plug for a product.
>--Original Message--
>> Tom - are you on the Red-gate pay roll?
>He is not, but what difference does that make? He's not
forcing anyone to
>use the product, he's telling the original poster to
check it out.
>
>.
>|||> MVPs are a source of information which in the most part
> is highly regarded. I would like to see a little more
> than a mere plug for a product.
This is not as easy as it seem to be. I might have used a product and know it does the job. I might or might
not know about the other products, and I might now that they exists, but I might not know if they solve the
particular problem, or how well they do it.
We will never be totally impartial, not the MVP's nor the other posters. Some of us do in fact spend a
considerable amount of time to test and write about products tested. Most of us can't, as we need to feed
ourselves the out families.
So end result can quite simply be that we stop mentioning the product, just so we don't come out as being
partial. I think that the Op will miss out. If I would need more info about how well one product stand against
another product, I would just ask that in a follow-up. Or ask if people know about other products...
Also, is a tool vendor here find that we recommend one product, and that is not the product they he/she is
selling, I for one wouldn't mind at all a post from the tool vendor stating that he also develops a product
for this purpose... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sb" <anonymous@.discussions.microsoft.com> wrote in message news:1aa2701c44ed4$44e973c0$a101280a@.phx.gbl...
> alot - it doesn't appear to be impartial.
> Isn't being impartial a responsibility for anyone
> (especially MVPs) when giving advice? If accurate non-
> partial analysis was made with no emotion, that would be
> a contribution worthy of the title MVP.
> MVPs are a source of information which in the most part
> is highly regarded. I would like to see a little more
> than a mere plug for a product.
>
> >--Original Message--
> >> Tom - are you on the Red-gate pay roll?
> >
> >He is not, but what difference does that make? He's not
> forcing anyone to
> >use the product, he's telling the original poster to
> check it out.
> >
> >
> >.
> >|||thanks for the reply Tibor.
I do believe MVPs should not plug a single product but
point out the pros and cons of each. If they can't, they
don't know what they are talking about, so silence is the
better option.
They could of course earn their titles and get educated
as this is a large part of the job and which has earned
them their title. MVPs should be a source of facts. This
for obvious reasons cannot apply to the general public,
but you are in a position of which I for one hold in high
regard - you cannot afford to have this tainted. Your
information is your reputation - if it is below par - so
are you.
>--Original Message--
>> MVPs are a source of information which in the most part
>> is highly regarded. I would like to see a little more
>> than a mere plug for a product.
>This is not as easy as it seem to be. I might have used
a product and know it does the job. I might or might
>not know about the other products, and I might now that
they exists, but I might not know if they solve the
>particular problem, or how well they do it.
>We will never be totally impartial, not the MVP's nor
the other posters. Some of us do in fact spend a
>considerable amount of time to test and write about
products tested. Most of us can't, as we need to feed
>ourselves the out families.
>So end result can quite simply be that we stop
mentioning the product, just so we don't come out as being
>partial. I think that the Op will miss out. If I would
need more info about how well one product stand against
>another product, I would just ask that in a follow-up.
Or ask if people know about other products...
>Also, is a tool vendor here find that we recommend one
product, and that is not the product they he/she is
>selling, I for one wouldn't mind at all a post from the
tool vendor stating that he also develops a product
>for this purpose... :-)
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"sb" <anonymous@.discussions.microsoft.com> wrote in
message news:1aa2701c44ed4$44e973c0$a101280a@.phx.gbl...
>> alot - it doesn't appear to be impartial.
>> Isn't being impartial a responsibility for anyone
>> (especially MVPs) when giving advice? If accurate non-
>> partial analysis was made with no emotion, that would
be
>> a contribution worthy of the title MVP.
>> MVPs are a source of information which in the most part
>> is highly regarded. I would like to see a little more
>> than a mere plug for a product.
>>
>> >--Original Message--
>> >> Tom - are you on the Red-gate pay roll?
>> >
>> >He is not, but what difference does that make? He's
not
>> forcing anyone to
>> >use the product, he's telling the original poster to
>> check it out.
>> >
>> >
>> >.
>> >
>
>.
>|||I agree with you general standpoint of this, but please remember that MVP's, as well as newsgroups in general,
are about peer-to-peer sharing. I.e., it is all about volunteer and unpaid sharing/helping. We could have the
standpoint to "never mention a 3:rd party product unless you have evaluated and compared them all", but end
result of that is that very few such products will ever be mentioned...
(I wouldn't mind doing product comparison, but I need to earn an income...)
Personally, I'm always hesitant to mention 3:rd party products, partly because of your point, and partly
because I might only know that it exists (i.e., I might not even have used the product). What is best for the
OP? Not getting a reply at all, or having something to investigate? I've decided on some "middle of the road",
where I list 3:rd party products on my web-site, and of course all are welcome to inform me of the existence
of such a product.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sb" <anonymous@.discussions.microsoft.com> wrote in message news:1a5e801c44edd$b37143a0$a601280a@.phx.gbl...
> thanks for the reply Tibor.
> I do believe MVPs should not plug a single product but
> point out the pros and cons of each. If they can't, they
> don't know what they are talking about, so silence is the
> better option.
> They could of course earn their titles and get educated
> as this is a large part of the job and which has earned
> them their title. MVPs should be a source of facts. This
> for obvious reasons cannot apply to the general public,
> but you are in a position of which I for one hold in high
> regard - you cannot afford to have this tainted. Your
> information is your reputation - if it is below par - so
> are you.
> >--Original Message--
> >> MVPs are a source of information which in the most part
> >> is highly regarded. I would like to see a little more
> >> than a mere plug for a product.
> >
> >This is not as easy as it seem to be. I might have used
> a product and know it does the job. I might or might
> >not know about the other products, and I might now that
> they exists, but I might not know if they solve the
> >particular problem, or how well they do it.
> >
> >We will never be totally impartial, not the MVP's nor
> the other posters. Some of us do in fact spend a
> >considerable amount of time to test and write about
> products tested. Most of us can't, as we need to feed
> >ourselves the out families.
> >
> >So end result can quite simply be that we stop
> mentioning the product, just so we don't come out as being
> >partial. I think that the Op will miss out. If I would
> need more info about how well one product stand against
> >another product, I would just ask that in a follow-up.
> Or ask if people know about other products...
> >
> >Also, is a tool vendor here find that we recommend one
> product, and that is not the product they he/she is
> >selling, I for one wouldn't mind at all a post from the
> tool vendor stating that he also develops a product
> >for this purpose... :-)
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"sb" <anonymous@.discussions.microsoft.com> wrote in
> message news:1aa2701c44ed4$44e973c0$a101280a@.phx.gbl...
> >> alot - it doesn't appear to be impartial.
> >> Isn't being impartial a responsibility for anyone
> >> (especially MVPs) when giving advice? If accurate non-
> >> partial analysis was made with no emotion, that would
> be
> >> a contribution worthy of the title MVP.
> >>
> >> MVPs are a source of information which in the most part
> >> is highly regarded. I would like to see a little more
> >> than a mere plug for a product.
> >>
> >>
> >> >--Original Message--
> >> >> Tom - are you on the Red-gate pay roll?
> >> >
> >> >He is not, but what difference does that make? He's
> not
> >> forcing anyone to
> >> >use the product, he's telling the original poster to
> >> check it out.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||> I do believe MVPs should not plug a single product but
> point out the pros and cons of each.
That's fine, when we have the data. Are you suggesting that because I am an
MVP, before I suggest the only product I've ever used for this product, I
need to go and research every other available product?
You're out of your tree.
> If they can't, they
> don't know what they are talking about, so silence is the
> better option.
Bullsh*t. I've used a product, I know it works, therefore I'm going to
suggest it. Silence is NOT the better option... nobody is going to be
completely impartial and have a 100% unbiased and accurate comprehensive
analysis of every single tool available to perform a specific, and often
obscure, task.
> They could of course earn their titles and get educated
> as this is a large part of the job and which has earned
> them their title. MVPs should be a source of facts.
Being an MVP has nothing to do with having extensive knowledge of every
single 3rd party tool available. We're human too, remember.
> information is your reputation - if it is below par - so
> are you.
Cram it.|||hello Tibor,
thanks again for your reply.
I do think that MVPs should approach product
recommendations with the same professionalism they do
with the core product, where time does not seem to be an
issue.
>--Original Message--
>I agree with you general standpoint of this, but please
remember that MVP's, as well as newsgroups in general,
>are about peer-to-peer sharing. I.e., it is all about
volunteer and unpaid sharing/helping. We could have the
>standpoint to "never mention a 3:rd party product unless
you have evaluated and compared them all", but end
>result of that is that very few such products will ever
be mentioned...
>(I wouldn't mind doing product comparison, but I need to
earn an income...)
>Personally, I'm always hesitant to mention 3:rd party
products, partly because of your point, and partly
>because I might only know that it exists (i.e., I might
not even have used the product). What is best for the
>OP? Not getting a reply at all, or having something to
investigate? I've decided on some "middle of the road",
>where I list 3:rd party products on my web-site, and of
course all are welcome to inform me of the existence
>of such a product.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"sb" <anonymous@.discussions.microsoft.com> wrote in
message news:1a5e801c44edd$b37143a0$a601280a@.phx.gbl...
>> thanks for the reply Tibor.
>> I do believe MVPs should not plug a single product but
>> point out the pros and cons of each. If they can't,
they
>> don't know what they are talking about, so silence is
the
>> better option.
>> They could of course earn their titles and get educated
>> as this is a large part of the job and which has earned
>> them their title. MVPs should be a source of facts.
This
>> for obvious reasons cannot apply to the general public,
>> but you are in a position of which I for one hold in
high
>> regard - you cannot afford to have this tainted. Your
>> information is your reputation - if it is below par -
so
>> are you.
>> >--Original Message--
>> >> MVPs are a source of information which in the most
part
>> >> is highly regarded. I would like to see a little
more
>> >> than a mere plug for a product.
>> >
>> >This is not as easy as it seem to be. I might have
used
>> a product and know it does the job. I might or might
>> >not know about the other products, and I might now
that
>> they exists, but I might not know if they solve the
>> >particular problem, or how well they do it.
>> >
>> >We will never be totally impartial, not the MVP's nor
>> the other posters. Some of us do in fact spend a
>> >considerable amount of time to test and write about
>> products tested. Most of us can't, as we need to feed
>> >ourselves the out families.
>> >
>> >So end result can quite simply be that we stop
>> mentioning the product, just so we don't come out as
being
>> >partial. I think that the Op will miss out. If I would
>> need more info about how well one product stand against
>> >another product, I would just ask that in a follow-up.
>> Or ask if people know about other products...
>> >
>> >Also, is a tool vendor here find that we recommend one
>> product, and that is not the product they he/she is
>> >selling, I for one wouldn't mind at all a post from
the
>> tool vendor stating that he also develops a product
>> >for this purpose... :-)
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"sb" <anonymous@.discussions.microsoft.com> wrote in
>> message news:1aa2701c44ed4$44e973c0$a101280a@.phx.gbl...
>> >> alot - it doesn't appear to be impartial.
>> >> Isn't being impartial a responsibility for anyone
>> >> (especially MVPs) when giving advice? If accurate
non-
>> >> partial analysis was made with no emotion, that
would
>> be
>> >> a contribution worthy of the title MVP.
>> >>
>> >> MVPs are a source of information which in the most
part
>> >> is highly regarded. I would like to see a little
more
>> >> than a mere plug for a product.
>> >>
>> >>
>> >> >--Original Message--
>> >> >> Tom - are you on the Red-gate pay roll?
>> >> >
>> >> >He is not, but what difference does that make?
He's
>> not
>> >> forcing anyone to
>> >> >use the product, he's telling the original poster
to
>> >> check it out.
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||> I do think that MVPs should approach product
> recommendations with the same professionalism they do
> with the core product, where time does not seem to be an
> issue.
Well, gee, we work with the core product every day, and most of us earn our
living from it.
We don't, however, work with every single 3rd party product, for every
imaginable task, every day.
It's not like we go out and take a SQL Server course every time we attempt
to answer a question. If that's the equivalent of what you expect us to do
for product recommendations, well, don't hold your breath. Maybe you should
avoid such threads so you don't get so worked up about it, because what
you're asking simply isn't going to happen. As the others mentioned, we
have families to feed, just like you.
MVPs are not coming out and saying users must use this product. We're
offering a tool (or in my case, usually a relatively long list of tools) for
the user to investigate. If I have used a specific tool, I will mention
that, because I think it's useful information. If I have used a tool and it
sucks, I'm not afraid to mention that, either.
You might disagree. Tough. What you're proposing is some kind of
censorship that will leave users worse off than you perceive them to be now.
A|||hello Aaron,
So I take it if a question was asked to you about
replication and all you had ever used was transactional
replication, because it suited you you'd recommend it?
What I am pushing for here is better information, and
what is wrong with that?
By the way I love the colloquialisms.
>--Original Message--
>> I do believe MVPs should not plug a single product but
>> point out the pros and cons of each.
>That's fine, when we have the data. Are you suggesting
that because I am an
>MVP, before I suggest the only product I've ever used
for this product, I
>need to go and research every other available product?
>You're out of your tree.
>> If they can't, they
>> don't know what they are talking about, so silence is
the
>> better option.
>Bullsh*t. I've used a product, I know it works,
therefore I'm going to
>suggest it. Silence is NOT the better option... nobody
is going to be
>completely impartial and have a 100% unbiased and
accurate comprehensive
>analysis of every single tool available to perform a
specific, and often
>obscure, task.
>> They could of course earn their titles and get educated
>> as this is a large part of the job and which has earned
>> them their title. MVPs should be a source of facts.
>Being an MVP has nothing to do with having extensive
knowledge of every
>single 3rd party tool available. We're human too,
remember.
>> information is your reputation - if it is below par -
so
>> are you.
>Cram it.
>
>.
>|||> So I take it if a question was asked to you about
> replication and all you had ever used was transactional
> replication, because it suited you you'd recommend it?
If it fit the requirements, yes. If transactional replication wouldn't work
to meet the requirements, DUH, of course not.
Have I ever suggested something that didn't make sense, simply because it
was the only answer I knew?
You're not even taling apples vs. oranges here, you're talking apples vs.
car batteries.
> What I am pushing for here is better information, and
> what is wrong with that?
You're expecting MVPs to be completely unbiased and have gobs and gobs of
free time to research every single product before recommending one that they
have used, worked well, and that the user could investigate.
Once again, fat chance!
What is your interest in this? Are you a competitor to red-gate?|||> Have you also done reviews on all the other similar
> products to validate your opinion on what to recommend?
Mr. Anonymous, he said "check it out" not "this is the best product ever."|||> I do think that MVPs should approach product
> recommendations with the same professionalism they do
> with the core product, where time does not seem to be an
> issue.
Perhaps there's a misunderstanding about the MVP program. We are not paid by Microsoft. We earn our money from
"regular" work. I am a trainer and consultant, for instance.
I've decided to share what I've come to learn about the product from working. In hope that it will help
others. That's all.
If I get a request to earn money from working with a 3:rd party tool and I feel that I will do a good job with
that, then I would accept that job. During that period of time, I would get to know that tool, and if I like
it, I would probably comment on that tool here.
However, you can't expect us to take unpaid time to evaluate tools, so we can post unbiased comments about
them. You could post a request with MS that they pay someone to do that. But all of us here work for their
money and have house, rent, mortgages, insurances, food and all that stuff to pay.
MS could decide to censor the MVP's, in order to be more fair with the 3:rd party vendors. However, I don't
think it would gain the 3:rd party vendors, nor the community, and I know for sure that I wouldn't not
appreciate being censored.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sb" <anonymous@.discussions.microsoft.com> wrote in message news:1ab6501c44ef5$9ecf0d20$a401280a@.phx.gbl...
> hello Tibor,
> thanks again for your reply.
> I do think that MVPs should approach product
> recommendations with the same professionalism they do
> with the core product, where time does not seem to be an
> issue.
> >--Original Message--
> >I agree with you general standpoint of this, but please
> remember that MVP's, as well as newsgroups in general,
> >are about peer-to-peer sharing. I.e., it is all about
> volunteer and unpaid sharing/helping. We could have the
> >standpoint to "never mention a 3:rd party product unless
> you have evaluated and compared them all", but end
> >result of that is that very few such products will ever
> be mentioned...
> >(I wouldn't mind doing product comparison, but I need to
> earn an income...)
> >
> >Personally, I'm always hesitant to mention 3:rd party
> products, partly because of your point, and partly
> >because I might only know that it exists (i.e., I might
> not even have used the product). What is best for the
> >OP? Not getting a reply at all, or having something to
> investigate? I've decided on some "middle of the road",
> >where I list 3:rd party products on my web-site, and of
> course all are welcome to inform me of the existence
> >of such a product.
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"sb" <anonymous@.discussions.microsoft.com> wrote in
> message news:1a5e801c44edd$b37143a0$a601280a@.phx.gbl...
> >> thanks for the reply Tibor.
> >>
> >> I do believe MVPs should not plug a single product but
> >> point out the pros and cons of each. If they can't,
> they
> >> don't know what they are talking about, so silence is
> the
> >> better option.
> >> They could of course earn their titles and get educated
> >> as this is a large part of the job and which has earned
> >> them their title. MVPs should be a source of facts.
> This
> >> for obvious reasons cannot apply to the general public,
> >> but you are in a position of which I for one hold in
> high
> >> regard - you cannot afford to have this tainted. Your
> >> information is your reputation - if it is below par -
> so
> >> are you.
> >>
> >> >--Original Message--
> >> >> MVPs are a source of information which in the most
> part
> >> >> is highly regarded. I would like to see a little
> more
> >> >> than a mere plug for a product.
> >> >
> >> >This is not as easy as it seem to be. I might have
> used
> >> a product and know it does the job. I might or might
> >> >not know about the other products, and I might now
> that
> >> they exists, but I might not know if they solve the
> >> >particular problem, or how well they do it.
> >> >
> >> >We will never be totally impartial, not the MVP's nor
> >> the other posters. Some of us do in fact spend a
> >> >considerable amount of time to test and write about
> >> products tested. Most of us can't, as we need to feed
> >> >ourselves the out families.
> >> >
> >> >So end result can quite simply be that we stop
> >> mentioning the product, just so we don't come out as
> being
> >> >partial. I think that the Op will miss out. If I would
> >> need more info about how well one product stand against
> >> >another product, I would just ask that in a follow-up.
> >> Or ask if people know about other products...
> >> >
> >> >Also, is a tool vendor here find that we recommend one
> >> product, and that is not the product they he/she is
> >> >selling, I for one wouldn't mind at all a post from
> the
> >> tool vendor stating that he also develops a product
> >> >for this purpose... :-)
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >http://www.karaszi.com/sqlserver/default.asp
> >> >http://www.solidqualitylearning.com/
> >> >
> >> >
> >> >"sb" <anonymous@.discussions.microsoft.com> wrote in
> >> message news:1aa2701c44ed4$44e973c0$a101280a@.phx.gbl...
> >> >> alot - it doesn't appear to be impartial.
> >> >> Isn't being impartial a responsibility for anyone
> >> >> (especially MVPs) when giving advice? If accurate
> non-
> >> >> partial analysis was made with no emotion, that
> would
> >> be
> >> >> a contribution worthy of the title MVP.
> >> >>
> >> >> MVPs are a source of information which in the most
> part
> >> >> is highly regarded. I would like to see a little
> more
> >> >> than a mere plug for a product.
> >> >>
> >> >>
> >> >> >--Original Message--
> >> >> >> Tom - are you on the Red-gate pay roll?
> >> >> >
> >> >> >He is not, but what difference does that make?
> He's
> >> not
> >> >> forcing anyone to
> >> >> >use the product, he's telling the original poster
> to
> >> >> check it out.
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||I'll offer you a deal. If you don't like the advice of an MVP, we will
happily refund your community registration fee.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"sb" <anonymous@.discussions.microsoft.com> wrote in message
news:1abdb01c44ef8$5100f650$a101280a@.phx.gbl...
> hello Aaron,
> So I take it if a question was asked to you about
> replication and all you had ever used was transactional
> replication, because it suited you you'd recommend it?
> What I am pushing for here is better information, and
> what is wrong with that?
> By the way I love the colloquialisms.
> >--Original Message--
> >> I do believe MVPs should not plug a single product but
> >> point out the pros and cons of each.
> >
> >That's fine, when we have the data. Are you suggesting
> that because I am an
> >MVP, before I suggest the only product I've ever used
> for this product, I
> >need to go and research every other available product?
> >
> >You're out of your tree.
> >
> >> If they can't, they
> >> don't know what they are talking about, so silence is
> the
> >> better option.
> >
> >Bullsh*t. I've used a product, I know it works,
> therefore I'm going to
> >suggest it. Silence is NOT the better option... nobody
> is going to be
> >completely impartial and have a 100% unbiased and
> accurate comprehensive
> >analysis of every single tool available to perform a
> specific, and often
> >obscure, task.
> >
> >> They could of course earn their titles and get educated
> >> as this is a large part of the job and which has earned
> >> them their title. MVPs should be a source of facts.
> >
> >Being an MVP has nothing to do with having extensive
> knowledge of every
> >single 3rd party tool available. We're human too,
> remember.
> >
> >> information is your reputation - if it is below par -
> so
> >> are you.
> >
> >Cram it.
> >
> >
> >.
> >|||Hello Aaron,
It looks like we are going to have a difference of
opinion which I see nothing wrong with.
I have watched this topic come up on numerous occasions
and have not said anything up until now. I really wish
one of you MVPs would spend the time to research this
topic as all I have ever seen are plugs when this comes
up. It really doesn't look unbiased (I know you're
allowed to be) or professional from where I'm standing. I
also think there has been enough interest to warrant the
effort. All that I have written has had no affiliation to
any product only the topic.
I am entitled to my opinion and I do not appreciate the
slur as having fallen out of my tree, or being told
to "Cram it" as I have kept this thread professional.
>--Original Message--
>> So I take it if a question was asked to you about
>> replication and all you had ever used was transactional
>> replication, because it suited you you'd recommend it?
>If it fit the requirements, yes. If transactional
replication wouldn't work
>to meet the requirements, DUH, of course not.
>Have I ever suggested something that didn't make sense,
simply because it
>was the only answer I knew?
>You're not even taling apples vs. oranges here, you're
talking apples vs.
>car batteries.
>> What I am pushing for here is better information, and
>> what is wrong with that?
>You're expecting MVPs to be completely unbiased and have
gobs and gobs of
>free time to research every single product before
recommending one that they
>have used, worked well, and that the user could
investigate.
>Once again, fat chance!
>What is your interest in this? Are you a competitor to
red-gate?
>
>.
>|||> I'll offer you a deal. If you don't like the advice of an MVP, we will
> happily refund your community registration fee.
LOL!
Now, if I don't like the moaning from a community member about all of our
bad advice and biased recommendations, where do I get my refund? ;-)|||Probably have to talk to Stephen about that.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eu1logwTEHA.3332@.TK2MSFTNGP12.phx.gbl...
> > I'll offer you a deal. If you don't like the advice of an MVP, we will
> > happily refund your community registration fee.
> LOL!
> Now, if I don't like the moaning from a community member about all of our
> bad advice and biased recommendations, where do I get my refund? ;-)
>|||> I really wish
> one of you MVPs would spend the time to research this
> topic
I'm sure that many of us would be happy to do that. But the point is that no-one would be willing to pay for
that. Do you expect us to go unpaid during that evaluation?
Please consider that evaluating only one tool will be from half a day to a week worth of work. Imagine 5
tools, and then some comparison of them. That will easily be a man-month worth of work. Sure, I'm willing to
do that, if someone cough up the dough for that. As long as no-one is doing that, I need to earn my money
elsewhere.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/|||Your definition of "professional" is quite equivalent to my definitions of
"accusatory" and "stand-offish."
For starters, did you notice that my reply said:
"I like SQL Compare from red gate, but there are several alternatives.
http://www.aspfaq.com/2209"
If you look at the link I posted, you will see that I have compiled a list
of links to several capable products, including Red-Gate. I don't expect to
tell the user which product to use, because I simply don't have the time to
adequately test all of them. Do you? If so, please feel free to post a
review of each product, so the user might decide which one to use without
having to lift a finger! I'd be interested to see how you find the time to
do so; I guess you'll have to put aside this need to spend over 18 hours now
worried about getting your 2 cents in, telling the rest of us how we should
be quiet.
Do you expect every MVP to memorize the full list of available tools? Do
you not expect all people (MVP or not) to chime in when they know that a
tool they have used may meet the user's needs? Where, and in what charter,
does it say that MVPs have to, or are even casually expected to, provide
unilateral coverage of every 3rd party product known to man? We are not
talking about election campaign airtime here.
My company has a license for Red-Gate's tools, so I have no problem adding
the information (deemed helpful by most, yourself aside, I'm sure) that I
have had good luck with the product. Notice, again, that I did not say
"this is the only tool you need" or "this product kicks ass" or "this is the
best product out there." I said "I like it." In my understanding of the
English language, that does not translate to, and should not be interpreted
as, "you must like it too. Or else." The user is free to use that
information, or not use that information, as he/she evaluates a product that
will meet their needs.
If you don't like that, killfile me. Killfile all MVPs, for all I care.
But don't tell us how we should spend our non-paid time... unless you are
offering to pay me for the time I spend needlessly reviewing every single
3rd party product for SQL Server.
E-mail me if that's the case. Otherwise, adieu.
*PLONK*
--
http://www.aspfaq.com/
(Reverse address to reply.)
"sb" <anonymous@.discussions.microsoft.com> wrote in message
news:1afc601c44f07$9b922c70$a001280a@.phx.gbl...
> Hello Aaron,
> It looks like we are going to have a difference of
> opinion which I see nothing wrong with.
> I have watched this topic come up on numerous occasions
> and have not said anything up until now. I really wish
> one of you MVPs would spend the time to research this
> topic as all I have ever seen are plugs when this comes
> up. It really doesn't look unbiased (I know you're
> allowed to be) or professional from where I'm standing. I
> also think there has been enough interest to warrant the
> effort. All that I have written has had no affiliation to
> any product only the topic.
> I am entitled to my opinion and I do not appreciate the
> slur as having fallen out of my tree, or being told
> to "Cram it" as I have kept this thread professional.|||Aaron, I suspect that Stephen will just laugh at you.
--
Not so sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:elk2WiwTEHA.2408@.tk2msftngp13.phx.gbl...
> Probably have to talk to Stephen about that.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eu1logwTEHA.3332@.TK2MSFTNGP12.phx.gbl...
> > > I'll offer you a deal. If you don't like the advice of an MVP, we
will
> > > happily refund your community registration fee.
> >
> > LOL!
> >
> > Now, if I don't like the moaning from a community member about all of
our
> > bad advice and biased recommendations, where do I get my refund? ;-)
> >
> >
>|||> Aaron, I suspect that Stephen will just laugh at you.
How appropriate. ;-)|||Hi Alan,
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data.
You can find it here:
http://www.extreme.co.il
"Aaron [SQL Server MVP]" wrote:
> I like SQL Compare from red gate, but there are several alternatives.
> http://www.aspfaq.com/2209
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in message
> news:ejAex3lTEHA.2580@.TK2MSFTNGP12.phx.gbl...
> > Is there a simple tool available that will analyse two SQL Server 2000
> > databases and report the structural differences between them? A bonus
> would
> > be its ability to generate change scripts to bring one of them up to the
> > state of the other.
> >
> > Thanks,
> >
> > Alan
> >
> >
>
>|||Hi Alan,
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data.
You can find it here:
http://www.extreme.co.il
"Alan Howard" wrote:
> Is there a simple tool available that will analyse two SQL Server 2000
> databases and report the structural differences between them? A bonus would
> be its ability to generate change scripts to bring one of them up to the
> state of the other.
> Thanks,
> Alan
>
>|||Hi all,
Thanks for your suggestions and spirited debate. I tried the eval of SQL
Compare from Red Gate and would like to say I've had a look at the others
but I just haven't had the time yet. SQL Compare is a nifty tool.
Regards,
Alan
"Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in message
news:ejAex3lTEHA.2580@.TK2MSFTNGP12.phx.gbl...
> Is there a simple tool available that will analyse two SQL Server 2000
> databases and report the structural differences between them? A bonus
would
> be its ability to generate change scripts to bring one of them up to the
> state of the other.
> Thanks,
> Alan
>

Configuration fIle

I'm using stored procedures in a database that use data in other databases. I refer these data as DataBaseName.dbo.TableName. It works fine. But, i just thought, what if the name of the databases change?

Is there anyway to use some sort of variables to refer the databases? like a configuration file used in programming applications.

Thanks

You can do one of the below:

1. In SQL Server 2005, you can create synonyms for the tables in the other database. And when the name of the database changes you will have to drop & recreate the synonym

2. In older versions of SQL Server, you can create views that point to the tables in the other database and do the same.

Conditional Trigger

I'm hoping someone here can help me with this situation...I've looked everywhere and cannot find a solution.

I have two separate databases. Table structures are different. A record is inserted into Table 1 of Database A. Then, when Field 1 in Table 1 is updated, I want my trigger to fire and insert certain values (selected into "inserted") to be inserted into Database B, Table 1.

I have created a trigger that will do just that. Here's my problem:

I need to ensure the trigger only fires if two conditions are met. If the value in Field 1 is updated to one specific value, and if the type of transaction (housed in a separate field) is one of several specific types (linked to the query from a separate table).

Otherwise, I want the trigger to be ignored completely.

Below is my current code. Can anyone here assist?
Thanks in advance for any help,
Dirk

CREATE TRIGGER [ELOGXWALK] ON [dbo].[Appointments]
AFTER UPDATE
AS

SET XACT_ABORT ON

IF UPDATE(Status)
BEGIN

declare @.recnum int
declare @.dateofenc datetime
declare @.timein varchar (5)
declare @.Code varchar (6)
declare @.Descr varchar (25)
declare @.clinic varchar (6)
declare @.status varchar (5)
declare @.Appointment_ID int
declare @.Encounter_Number int
declare @.nextnum int

select @.recnum = fldrec_num, @.dateofEnc = left(appointment_datetime, 11), @.timein=right('0'+convert(varchar,time_in), 4),
@.Code = Code, @.Descr = Descr, @.clinic = 'ONSITE', @.Status = status, @.Appointment_ID = Appointment_ID,
@.Encounter_Number = Encounter_Number from inserted
left join Crosswalk.dbo.EncounterLink on TypeID = Appointment_Type_ID
left join patients on patients.patient_id = inserted.patient_id
left join contacts on contacts.contact_id = patients.contact_id
left join DBOCC.MCH_OHM.XWALK.EMPLOYEE on FLDSSN = contacts.ssn
where Appointment_Type_ID in (select TypeID from Crosswalk.dbo.EncounterLink)
and status = 'A'

set @.nextnum = (select fldnextnum from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG' )
update DBOCC.MCH_OHM.XWALK.tblrecnumseq
set fldnextnum = (select fldnextnum from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG') + 1
where fldtablename = 'ELOG'

insert into DBOCC.mch_ohm.XWALK.ELOG (FLDREC_NUM, FLDEMPLOYEE, FLDDATE, FLDTIME, FLDTYPE, FLDDESCR, FLDCLINIC, FLDRECNUM, FLDENCNUMBR)
values (@.nextnum, @.recnum, @.dateofenc, @.timein, @.code, @.Descr, @.clinic, @.appointment_id, @.Encounter_Number)

END

IF UPDATE (Encounter_Number)
BEGIN

select @.Appointment_ID = Appointment_ID, @.Encounter_Number = Encounter_Number from inserted
left join DBOCC.MCH_OHM.XWALK.ELOG on FLDRECNUM = inserted.Appointment_ID

update DBOCC.MCH_OHM.XWALK.ELOG
set FLDENCNUMBR = @.Encounter_Number
where FLDRECNUM = @.Appointment_ID

ENDcan you provide more specifics on the two conditions?|||First condition. The TypeID referenced in the script must be any one of about 10 different values. These are stored in a separate table (EncounterLink) along with the corresponding value I want to load into Database B. If the TypeID does not equal one of the values in the EncounterLink table, I want the trigger to disregard everything.

Second condition. The Status field should only fire the trigger if a certain value is entered. In this case, only the value "A" should fire the trigger. In the future I will probably want one or two different trigger functions based on different values.

Dirk|||great! your current design will work if one row is updated but will fail (in some fashion) if 2+ rows are updated. Do you want to handle multiple row updates? also, do you want the Encounter_number updated only if the TypeID and Status are okay or are these seperate events?|||I should not have to deal with multiple row updates, because the front end application will only do a single row at a time anyway (clicking a button in the app triggers the change).

I've actually made progress by specifically referencing "inserted" in the where statement of my select and adding an "If @.@.rowcount > 0" and "Begin" command just prior to the set statement. It seems to be working properly all around, but any streamlining suggestions will be more than welcome, as I am not really a DBA - my company justs asks that I play one during working hours!

Dirk|||it sounds like you are on your way...

I would have taken a diffrent approach so the trigger could handle multiple row updates but the bottom line is that you understand the trigger and it works. I would suggest two things though,

First
----------------------------
set @.nextnum = (select fldnextnum from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG' )
update DBOCC.MCH_OHM.XWALK.tblrecnumseq set fldnextnum = @.nextnum + 1 where fldtablename = 'ELOG'

or even

begin transaction
update DBOCC.MCH_OHM.XWALK.tblrecnumseq set fldnextnum = fldnextnum + 1 where fldtablename = 'ELOG'
select @.nextnum = (fldnextnum - 1) from DBOCC.MCH_OHM.XWALK.tblrecnumseq where fldtablename = 'ELOG'
commit transaction

And
----------------------------
IF UPDATE (Encounter_Number) BEGIN
update DBOCC.MCH_OHM.XWALK.ELOG
set FLDENCNUMBR = inserted.Encounter_Number
from inserted
join DBOCC.MCH_OHM.XWALK.ELOG on inserted.Appointment_ID = DBOCC.MCH_OHM.XWALK.ELOG.FLDRECNUM
end
----------------------------

these are minor things but (IMHO) you should save every step you can when writting triggers.|||Thanks for the tips! Both make sense to me, but the second one does not work...

Server: Msg 117, Level 15, State 2, Procedure ELOGXWALK, Line 49
The number name 'DBOCC.MCH_OHM.XWALK.ELOG' contains more than the maximum number of prefixes. The maximum is 3.

Dirk|||ARG!!! I do that sometimes.
I don't use liked servers that much but I think is should be something like

IF UPDATE (Encounter_Number) BEGIN
update DBOCC.MCH_OHM.XWALK.ELOG
set FLDENCNUMBR = inserted.Encounter_Number
from inserted
join OPENQUERY(DBOCC, select FLDRECNUM from MCH_OHM.XWALK.ELOG) elog on inserted.Appointment_ID = elog.FLDRECNUM
end

What you have is just fine though.

conditional split problem

I have been transfering data from text file to sql databases.

I have a conditional split where i check to if the address has changed for a particular person.If yes i direct to update else i direct to default output which means no change.

when i connect error output of conditional split to a database or union all couple of rows are directed to error output.But i dont understand the reason.How would i be able to know why they r directed to error.

Please let me know.

When the rows are sent to the error output they should have two new columns (ErrorCode and ErrorColumn) added to them. To determine the cause of the error, I would start by looking at these values, and then researching the code(s) that they contain.|||

This is a way of doing what Matthew suggested:

By default every error output in your dataflow will add a couple of columns: ErrorNumber and error column. The you can use script task to get the description of the error. Jamie has an explanation of that on his blog:

http://blogs.conchango.com/jamiethomson/archive/2005/08/08/1969.aspx

Conditional select in view

Greetings all,
I have three databases dmart, dmart_a and dmart_b. The first being a pointer database, has lookup table that one has to query to know which one of the two databases (dmart_a and dmart_b) is online. I want to create a view which can dynamically select the data from the database that is online . Following is the SQL which I was able to write however I am not able to precede any further b'cause of error Sub-query returns more than one row. All your suggestions are welcomed.

SELECT CASE
WHEN dbname = 'dmart_a'
THEN (select count(*) from dmart_a.upload.person_data)
ELSE (select count(*) from dmart_b.upload.person_data)
END --AS 'Database to point'
FROM dmart_db_pointerAs you have posted a question in the SQL server Article section it is being moved to SQL Server Forum.

MODERATOR.|||

Quote:

Originally Posted by VirDesi

Greetings all,
I have three databases dmart, dmart_a and dmart_b. The first being a pointer database, has lookup table that one has to query to know which one of the two databases (dmart_a and dmart_b) is online. I want to create a view which can dynamically select the data from the database that is online . Following is the SQL which I was able to write however I am not able to precede any further b'cause of error Sub-query returns more than one row. All your suggestions are welcomed.

SELECT CASE
WHEN dbname = 'dmart_a'
THEN (select count(*) from dmart_a.upload.person_data)
ELSE (select count(*) from dmart_b.upload.person_data)
END --AS 'Database to point'
FROM dmart_db_pointer

try:

select dmart_db_pointer.dbname, cnt_a, cnt_b from
dmart_db_pointer left join
(select 'dmart_a' as dbname, count(*) as cnt _afrom dmart_a.upload.person_data) dmart_a on dmart_a.dbname = dmart_db_pointer.dbname
left join (select 'dmart_b' as dbname, count(*) as cnt_b from dmart_b.upload.person_data) dmart_b on dmart_b.dbname = dmart_db_pointer.dbname

Conditional checks in SSIS.

I have two connections in a package pointing to two different databases
on the same server. I have to insert records from 'DB1' table 'Gender1'
to 'DB2' table 'Gender2'. Before I do that though, I have to make sure
the minimum value (of all the Gender Keys that are going to be
inserted) of 'DB1' 'GenderKey' (which is an identity field) is greater than the
maximum value of DB2-GenderKey (which is a primary key but not an
identity field). How can I do this simple check? I have to do this process for many different tables ....... Gender table is just an example. If someone can give an detailed explanation on which tasks to use and how to use them (as I am relatively new to SSIS) that'd be great.So you want to move the rows from DB1 that are newer than what you have in DB2.

You should use an Execute Task in the Control Flow to get the ISNULL(MAX(GenderKey) ,0) from DB2 and place that in a variable. Have another variable with EvaluateAsExpression=True that builds a SQL statement with your MaxGenderKey variable to get any rows where GenderKey in DB1 is greater than the MaxGenderKey from DB2. Set the data access mode of your OLE DB Source to "SQL command from variable" and specify this expression-based variable.
|||actually, i do not want to do insert at all. If the condition check is successfull, then do the insert otherwise abort the package? How do i do that?|||You could certainly do that. You'd get the MaxGenderKey from DB1 as well and compare them in a precedence constraint to prevent any further execution.

Unless you're changing your original purpose of inserting new records from DB1 to DB2, it seems simpler to just let it try. If there aren't any, then it won't find anything to do.
|||

Use one Execute SQL to get the Max value from DB2, and one to get the Max value from DB1. Store the values in variables, as Jay described. Then have a data flow to do the actual inserts. Have a precendence constraint between the 2 Execute SQL tasks, and one between the 2nd Execute SQL and the data flow. Right-click on the precedence constraint between the 2nd Execute SQL and the data flow task, and change the Evaluation Operation to Expression and Constraint. Leave the Value as Success, and change the Expression to compare the variables you created (something like MaxIDFromDB1 > MaxIDFromDB2). Execution will only flow to the data flow if the expression evaluates to TRUE.

Exec SQL > Exec SQL --(expression goes here)-> Data Flow

|||

I think I might be getting what you guys are trying to convey.

So here is what I did.

I added a Sequence Container.

1. I added a Execute Sql Task to it which brings the GenderKey from Prod and puts it in a Variable using the following query ..

SELECT [User::GenderKey_Prod] = ISNULL(MAX(GenderKey) ,0) FROM dbo.DIM_Gender

2. I added another Execute Sql Task which brings the GenderKey from Staging and puts it in another Variable using following query ..

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

I added the connector from Prod to Stg (1 -> 2).

3. Then added another data flow task (which will actually transfer the data).

4. And added a connector from STG to this data (2 -> 3) flow task and on this connector I defined the expression check @.

[User::GenderKey_Prod] < @. [User::GenderKey_Stg] which if true, will execute the data flow task.

All this makes sense, however, I am having troubles on step 2. Where condition task a parameter called TaskId, this parameter is a

Package Level parameter I have added. I did the parameter mapping, but it keeps giving error on this step says 'Parameter Name is

Unrecognized'. Can you see anything wrong that I have done in Step 2 while defining runtime parameters?

|||

Ok, I think I found the issue. The Parameter name in mapping section has to be 0.

But I still have an issue, now the package is running and executing step 1 and 2, after than its just not executing step 3 even if the expression is true. How can i check the values of the variables I assigned value to in step 1 and 2 ?

|||

ASOOD wrote:

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

On the Parameter Mapping page make sure you specify 0 for the Parameter Name to represent the first parameter.

I'm not too sure about your SELECT syntax. You know you have to set the ResultSet to Single Row and make an entry on the Result Set page? You'll specify the output column name (which in your query above is [User::GenderKey_Stg]) and select the variable.

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).
|||Right-click on step 3 and add a breakpoint to the OnPreexecute step. Then run (debug) the package. When processing stops, you can go to the Debug menu and show the locals window. From there, drill down until you find your variables. Hit the run button to continue.|||

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?|||I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?|||

Phil Brammer wrote:

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?

Sorry, I should have qualified the statement. It will only happen if your datatype in SQL is not int. I was testing with a table that had a smallint and thus had to supply a CAST.
|||

ASOOD wrote:

I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?

Did you set the ResultSet to "Single Row" and create a mapping between the result column and the variable?
|||You guys are the best!! Works like a charm now. Thank you all.|||Thanks for the example as I was trying to do the same thing for the first time.

Conditional checks in SSIS.

I have two connections in a package pointing to two different databases
on the same server. I have to insert records from 'DB1' table 'Gender1'
to 'DB2' table 'Gender2'. Before I do that though, I have to make sure
the minimum value (of all the Gender Keys that are going to be
inserted) of 'DB1' 'GenderKey' (which is an identity field) is greater than the
maximum value of DB2-GenderKey (which is a primary key but not an
identity field). How can I do this simple check? I have to do this process for many different tables ....... Gender table is just an example. If someone can give an detailed explanation on which tasks to use and how to use them (as I am relatively new to SSIS) that'd be great.So you want to move the rows from DB1 that are newer than what you have in DB2.

You should use an Execute Task in the Control Flow to get the ISNULL(MAX(GenderKey) ,0) from DB2 and place that in a variable. Have another variable with EvaluateAsExpression=True that builds a SQL statement with your MaxGenderKey variable to get any rows where GenderKey in DB1 is greater than the MaxGenderKey from DB2. Set the data access mode of your OLE DB Source to "SQL command from variable" and specify this expression-based variable.
|||actually, i do not want to do insert at all. If the condition check is successfull, then do the insert otherwise abort the package? How do i do that?|||You could certainly do that. You'd get the MaxGenderKey from DB1 as well and compare them in a precedence constraint to prevent any further execution.

Unless you're changing your original purpose of inserting new records from DB1 to DB2, it seems simpler to just let it try. If there aren't any, then it won't find anything to do.
|||

Use one Execute SQL to get the Max value from DB2, and one to get the Max value from DB1. Store the values in variables, as Jay described. Then have a data flow to do the actual inserts. Have a precendence constraint between the 2 Execute SQL tasks, and one between the 2nd Execute SQL and the data flow. Right-click on the precedence constraint between the 2nd Execute SQL and the data flow task, and change the Evaluation Operation to Expression and Constraint. Leave the Value as Success, and change the Expression to compare the variables you created (something like MaxIDFromDB1 > MaxIDFromDB2). Execution will only flow to the data flow if the expression evaluates to TRUE.

Exec SQL > Exec SQL --(expression goes here)-> Data Flow

|||

I think I might be getting what you guys are trying to convey.

So here is what I did.

I added a Sequence Container.

1. I added a Execute Sql Task to it which brings the GenderKey from Prod and puts it in a Variable using the following query ..

SELECT [User::GenderKey_Prod] = ISNULL(MAX(GenderKey) ,0) FROM dbo.DIM_Gender

2. I added another Execute Sql Task which brings the GenderKey from Staging and puts it in another Variable using following query ..

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

I added the connector from Prod to Stg (1 -> 2).

3. Then added another data flow task (which will actually transfer the data).

4. And added a connector from STG to this data (2 -> 3) flow task and on this connector I defined the expression check @.

[User::GenderKey_Prod] < @. [User::GenderKey_Stg] which if true, will execute the data flow task.

All this makes sense, however, I am having troubles on step 2. Where condition task a parameter called TaskId, this parameter is a

Package Level parameter I have added. I did the parameter mapping, but it keeps giving error on this step says 'Parameter Name is

Unrecognized'. Can you see anything wrong that I have done in Step 2 while defining runtime parameters?

|||

Ok, I think I found the issue. The Parameter name in mapping section has to be 0.

But I still have an issue, now the package is running and executing step 1 and 2, after than its just not executing step 3 even if the expression is true. How can i check the values of the variables I assigned value to in step 1 and 2 ?

|||

ASOOD wrote:

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

On the Parameter Mapping page make sure you specify 0 for the Parameter Name to represent the first parameter.

I'm not too sure about your SELECT syntax. You know you have to set the ResultSet to Single Row and make an entry on the Result Set page? You'll specify the output column name (which in your query above is [User::GenderKey_Stg]) and select the variable.

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).
|||Right-click on step 3 and add a breakpoint to the OnPreexecute step. Then run (debug) the package. When processing stops, you can go to the Debug menu and show the locals window. From there, drill down until you find your variables. Hit the run button to continue.|||

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?|||I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?|||

Phil Brammer wrote:

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?

Sorry, I should have qualified the statement. It will only happen if your datatype in SQL is not int. I was testing with a table that had a smallint and thus had to supply a CAST.
|||

ASOOD wrote:

I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?

Did you set the ResultSet to "Single Row" and create a mapping between the result column and the variable?
|||You guys are the best!! Works like a charm now. Thank you all.|||Thanks for the example as I was trying to do the same thing for the first time.

concurrent backups

If I have say 15 databases on one server and in order to
back them up when there is little activity, but to get
them backed up before the tape backups begin, will there
be a problem if some of them are begun at the same time,
say i start 5 of them at 10PM and 5 at 11PM, etc?
Please and Thank YouIf you backup to different tapes or different disk files, the concurrent
backups would be fine.
You cannot concurrently backup to the same tape device.
--
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rachel" <anonymous@.discussions.microsoft.com> wrote in message
news:270001c49cef$8baeebf0$a401280a@.phx.gbl...
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You|||Hi,
No problems, you can execute the backups for different databases in parellel.
Thanks
hari
MCDBA
"Rachel" wrote:
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You
>|||No, most backup software leaves open files alone and if a file is being
backed up to then its open. Even if the software does backup open files
then it will not cause a problem.
Adrian
Rachel wrote:
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You|||Technically this is not a problem if you are backing them all up to disk and
not directly to tape. But depending on the size of the db's, the disk
array's they are on and the disk array's they are being backed up to, the
number of processors, other jobs etc you may not have optimal results. You
may find they will run faster overall if you schedule them one after
another. You can also affect the users and other activities happening if
you attempt to backup too much at one time. Hardware plays a big factor in
this though.
Andrew J. Kelly SQL MVP
"Rachel" <anonymous@.discussions.microsoft.com> wrote in message
news:270001c49cef$8baeebf0$a401280a@.phx.gbl...
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You

concurrent backups

If I have say 15 databases on one server and in order to
back them up when there is little activity, but to get
them backed up before the tape backups begin, will there
be a problem if some of them are begun at the same time,
say i start 5 of them at 10PM and 5 at 11PM, etc?
Please and Thank You
If you backup to different tapes or different disk files, the concurrent
backups would be fine.
You cannot concurrently backup to the same tape device.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rachel" <anonymous@.discussions.microsoft.com> wrote in message
news:270001c49cef$8baeebf0$a401280a@.phx.gbl...
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You
|||Hi,
No problems, you can execute the backups for different databases in parellel.
Thanks
hari
MCDBA
"Rachel" wrote:

> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You
>
|||No, most backup software leaves open files alone and if a file is being
backed up to then its open. Even if the software does backup open files
then it will not cause a problem.
Adrian
Rachel wrote:

> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You
|||Technically this is not a problem if you are backing them all up to disk and
not directly to tape. But depending on the size of the db's, the disk
array's they are on and the disk array's they are being backed up to, the
number of processors, other jobs etc you may not have optimal results. You
may find they will run faster overall if you schedule them one after
another. You can also affect the users and other activities happening if
you attempt to backup too much at one time. Hardware plays a big factor in
this though.
Andrew J. Kelly SQL MVP
"Rachel" <anonymous@.discussions.microsoft.com> wrote in message
news:270001c49cef$8baeebf0$a401280a@.phx.gbl...
> If I have say 15 databases on one server and in order to
> back them up when there is little activity, but to get
> them backed up before the tape backups begin, will there
> be a problem if some of them are begun at the same time,
> say i start 5 of them at 10PM and 5 at 11PM, etc?
> Please and Thank You