Best Practices Question - how do you execute multiple packages?

I have 200+ plus packages that need to be flexible in how they are run. For example, an end user may choose to run packages 1,2,3 and the next end user may choose to run packages 2,3,7, etc. Prior ro running a package, I set an "instance id" inside the group of packages so I can tie them all together in the logfile - I know that packages 1,2,3 were all run as group and that's distinct from packages 2,3,7 that were run in a differnt group.

Initially I embarked on a scenario where I had a queue table that loaded up the packages to be run and then had a little c# app that read the queue, generated the "instance id" and ran all the packages (either thru dtexec.exe or the Microsoft.SqlServer.DTS.Runtime). But now I wonder if using a master package that uses the Execute Package Task is the way to go. My 200+ packages are all independent and run based on a single config file and it seems as though going the parent package route will destroy some of that independence because I'll now be relying on parent package variables.

Any comments or suggestions?

Sounds like you have a great solution that works for you. If you decided to use a parent package to execute the child packages, it would be easy enough to drive with a ForEach loop and a simple file input or even a script task. A lot about whether this is the right choice for you depends on some things you haven't told us. For example, what is the long term plan for your current solution, do you plan on enhancing the current solution etc. Also, if you use a parent package, you're not forced to use parent package configurations. You can still use the same configuration scheme you're using currently.

From the information you've given, I'd say that it sounds like a good solution.

|||

Thanks for the response, Kirk. My 200+ packages use the config file in an indirect manner. When I design my packages, I don't step thru the Configuration Wizard and create a direct configuration, I just make sure to always name my objects the same and then I just apply my global config file with the /CONFIGFILE "c:\wherever\conf.dtsConfig". However, the Execute Package Task doesn't have any properties for specifying configurations. Ideally, I'd like a master package that read a queue table and that table would have the path to a package and a path to a config file and feed that to the Execute Package Task. Also, it would be nice if the Execute Package Task had a property like /SET from dtexec.exe so you wouldn't have to have child packages "pulling" variables/data from a master package because you have to design child packages with an awareness that they are executing in a larger context. Being able to apply a change from a master package to a child package would be preferrabe.

Best Practices Analyzer Modification

I work for a software development company and was wondering if it would be possible to create custom "Best Practices" for the Analyzer. For example if we were developing a database and wanted to check to make sure all new development followed the same pr
actices we could use our own custom "Best Practice" modules to check.
Is there anything like that that can be created for use with the Analyzer? An SDK or anything? Is that something that is planned for the future?
Thanks
The ability to create custom rules and extend BPA has certainly been talked
about and may be something that you can look forward to in a future version.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Earl G Elliott III" <Earl G Elliott III@.discussions.microsoft.com> wrote in
message news:19D1D94E-A1BA-42BE-ABAE-3ED0758F7544@.microsoft.com...
> I work for a software development company and was wondering if it would be
possible to create custom "Best Practices" for the Analyzer. For example if
we were developing a database and wanted to check to make sure all new
development followed the same practices we could use our own custom "Best
Practice" modules to check.
> Is there anything like that that can be created for use with the Analyzer?
An SDK or anything? Is that something that is planned for the future?
> Thanks
sql

best practice question: JOIN criteria vs. WHERE criteria

For example, consider the following queries:

DECLARE @.SomeParam INT
SET @.SomeParam = 44
SELECT *
 FROM TableA A
 JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID
 WHERE B.SomeParamColumn = @.SomeParam
SELECT *
 FROM TableA A
 JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @.SomeParam

Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?

Thanks.I checked the estimated execution plan and they were identical when i used a hard coded value instead of a parameter (@.someparam)

however,

when I used a parameter there was suddenly an execution difference.

The second query stored data in a temporary table. Thats something you'd definately want to avoid if you could. It also mis-guessed at the estimated row count.|||What tool did you use to check the execution plans? I checked it out with MS SQL Query Analyzer and saw no differences in execution plan when using a variable. Do you have example sql?

Thanks for the help!|||Always check the plan. And remember the plan *will* change as the stats for the table change. So make sure you've got a representative set of data.

Best match text search

Does the new text search method in SQL Server provide a “best match” text
search? Does it work similar to a Google search, for example, where the most
words that match gets the highest relevancy score, then these should appear
first. I wondered if the new text search provided a accuracy/relevancy score
on the results?
Help i appreciated.
Thanks
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-search/200702/1
Yes, the freetext predicate is most similar to the way searches are
conducted in Google.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ARPREET via droptable.com" <u31535@.uwe> wrote in message
news:6db8ff6e9d053@.uwe...
> Does the new text search method in SQL Server provide a "best match" text
> search? Does it work similar to a Google search, for example, where the
> most
> words that match gets the highest relevancy score, then these should
> appear
> first. I wondered if the new text search provided a accuracy/relevancy
> score
> on the results?
> Help i appreciated.
> Thanks
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-search/200702/1
>

Beginner with crosstab queries

I am having headaches trying to display some data in one table.
An example of some of the contents from the table are below:

ID ENTRY_ID SCHOOL
--------
1 10 Arizona
1 20 Arizona State
1 30 Texas
2 10 Baylor
2 20 Texas
3 10 Colorado

As you can see each ID has its own entry id, depending on how many school are assigned to that ID. The table has 1 to up to 10 entry_ids for each ID. The schools can be any of 70+ schools in the database, not just these that I listed.

I would like to list each school in its own column like below:

school1 school2 school3
----------
Arizona Arizona State Texas

school1 school2 school3
----------
Baylor Texas null

school1 school2 school3
----------
Colorado null null

I already know ahead of time that the highest count of entry_id per id is 10 because of a Having query I ran. Will I need to create 10 separate Select statements for each scenario??

This is what I have so far, but this is for ids with 5 entry_ids:
SELECT a.school, b.school, c.school, d.school, e.school
FROM table a,
table b,
table c,
table d,
table e
WHERE a.id = b.id
AND b.id = c.id
AND c.id = d.id
AND d.id = e.id
AND a.school > b.school
AND b.school > c.school
AND c.school > d.school
AND d.school > e.school

Unfortunately, it will not work for anything other than rows with 5 entry_ids per ID. I would hate to write out 10 different UNIONs if there is an easier way.

Your help is much appreciated and I hope I have given enough detail for an answer.Try this link (http://asktom.oracle.com/pls/ask/f?p=4950:8:7213782571237635933::NO::F4950_P8_DISPL AYID,F4950_P8_CRITERIA:7086279412131,)
:rolleyes:

Begin Date and End Date

Hi all
I want my users to be able to pick between the beginning date and the end date, for Example with the code I have below they can enter in a final suit date and then they get the results through query or report or whatever, what I want for them to be able to do is enter a beginning date and an end date Between [Enter the Start Date] And [Enter the End Date]
thats how access interprets it (Jet SQL) how would I do that in SQL Server?? Help Please

SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, FINALSUITDONE
FROM dbo.EmployeeGamingLicense
WHERE (FINALSUITDONE = @.Enter_FINALSUITDONE)I would have my stored procedure accept those dates as parameters. Presuming Access is still your front end, get the values from the user via a form, then pass them to the SP.|||Yes thats the front end, but can you elaborate because I'm am not getting anywhere at all..Oh GOOD GRAVY THE FRUSTRATION LEVELS IS CLIMBING..LOL|||What exactly are you trying to accomplish? How are you calling/executing the SP?|||I am trying to get it so that users can enter the beginning of the date like 12/1/2004 to 12/31/2004

I have a parameter in an ADP that allows them to enter the FinalSuit date 12/31/2005 and they see all the final suit for that date but I was wanting to give them a range, does that make sense|||To be honest, I don't use ADP's so I'm probably not your best source of info. I don't see the advantages of them, and I know of 3 Access MVP's that recommend against them.|||But when you work for a company that refuses to shell out money for a better front end then you use what you got|||Oh, Access is fine; I use it all the time. I simply stick with MDB/MDE front ends, not ADP. They seem to handle form references differently, so I figure someone with more ADP experience will be better able to help.|||Thanks for trying Its just frustrating cause Jet SQL and Sql server can be like apples and oranges|||SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]
GO

before delete triggers in MSSQL

Hi
I've got several tables that have foreign key relationships with a 'users'
table - for example tasks (assigned to user) and customers (liason of
customer). When I delete a user, I would like to set all the foreignkeyed
rows to have null as a user, rather than doing a cascading delete. This
could be done in a stored procedure, but the problem is that the application
has multiple modules that can be added and removed, so I don't know at
execution time what tables there are. The only way to do this I could think
of was with triggers. I know this is supposed to be a big no no, but
couldn't think of anything else. Not that it matters, because triggers won't
work here as the trigger is fired after the delete is done and hence bombs
out due to violated constraint checking. I can't use an 'INSTEAD OF' trigger
unfortunately as I need the facility to have multiple triggers. I see that
Oracle has a BEFORE trigger, imagining that this would solve the problem. Is
there similar functionality in SQL, or another way to do this?
I was hoping that this is a common task and that there is an easy way to do
it, but no luck so far with searches
Thanks
JoeOn Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:

> The only way to do this I could think
>of was with triggers. I know this is supposed to be a big no no, but
>couldn't think of anything else.
Hi Joe,
Why do you think triggers are a big no no? Of course, they shouldn't be
your first option and you should prefer DRI over triggers where possible,
but there are situations where triggers are an invaluable instrument.

> Not that it matters, because triggers won't
>work here as the trigger is fired after the delete is done and hence bombs
>out due to violated constraint checking.
That's correct. You either have to remove the foreign key constraint and
do the checking in the trigger as well, or you have to use INSTEAD OF
triggers.

> I can't use an 'INSTEAD OF' trigger
>unfortunately as I need the facility to have multiple triggers.
Maybe I'm missing something, but why don't you just combine the actions of
those various triggers into one trigger?

> I see that
>Oracle has a BEFORE trigger, imagining that this would solve the problem. I
s
>there similar functionality in SQL,
The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
has to offer.

> or another way to do this?
As I already indicated, you could move the constraint checking to the
trigger as well. But that's a bad idea, since that would force you to
write and maintain more trigger code, it would slow things down and it
would deny the query optimizer the knowledge of this constraint, so that
it can't use this knowledge to optimize query execution.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have to agree 100% with Hugo. Use instead of triggers, or drop your
relationships and implement them in triggers (which will be just as good,
but will be pretty painful to implement consiering you can just do it in the
instead of trigger.) You can have as many actions in the trigger as you
want.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:cva111hh4p6m600c28jagd6m348r6g2jii@.
4ax.com...
> On Mon, 14 Feb 2005 14:54:19 +0200, Mombers wrote:
>
> Hi Joe,
> Why do you think triggers are a big no no? Of course, they shouldn't be
> your first option and you should prefer DRI over triggers where possible,
> but there are situations where triggers are an invaluable instrument.
>
> That's correct. You either have to remove the foreign key constraint and
> do the checking in the trigger as well, or you have to use INSTEAD OF
> triggers.
>
> Maybe I'm missing something, but why don't you just combine the actions of
> those various triggers into one trigger?
>
> The INSTEAD OF trigger is the closest to a BEFORE trigger that SQL Server
> has to offer.
>
> As I already indicated, you could move the constraint checking to the
> trigger as well. But that's a bad idea, since that would force you to
> write and maintain more trigger code, it would slow things down and it
> would deny the query optimizer the knowledge of this constraint, so that
> it can't use this knowledge to optimize query execution.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)