Comparing DB's columns and create script

Hi,
I am looking for a solution that can generate a script on column difference
between two databases and its tables.
Basically, I have a one core db and one development db. The development db
has been revised several time and now I need to generate a script that just
adds all new table columns with their defaults to the old core db.
Any knowledge about this or some methods to use.
Thanks in advance
ChristianCheck out SQLCompare, from Red-GAte Software... It will compare the two
databases, and generate a script to do exactly what you need...
http://www.red-gate.com
"Christian Perthen" wrote:

> Hi,
> I am looking for a solution that can generate a script on column differenc
e
> between two databases and its tables.
> Basically, I have a one core db and one development db. The development db
> has been revised several time and now I need to generate a script that jus
t
> adds all new table columns with their defaults to the old core db.
> Any knowledge about this or some methods to use.
> Thanks in advance
> Christian
>
>

Compare value

Hi,

I just start ti study SQL, Pls advice the solution for the case below :

I have 2 table : order_detail and ship_detail

Order_id Ship_id
Designation Designation
qty qty


I'd like to write the SQL command that show me the status of each order and the item is complete or non complete shipped

Thanks & Best regards
CitronWhat field ties the order detail to the ship detail (is ship_id the same as order_id ) ? What does the designation field store - how do you know when an item has been shipped or not ?

Compare Dates and fail job if no match

This is probably a problem with a pretty simple solution but i can't find the right control/data flow item to handle it

Scenario.

I determine the database date for my source data for a set of ETL jobs via a piece of SQL - this gets passed to a master package variable which is subsequently to be used as the "Load date" of the resulting child package ETL routines. However I only want the packages to run if the LoadDate has either not been run before or is the next one in the DW sequence.

To check for this, In my data warehouse I also have a table called Import_Registry where the date of each upload is stored at the end of the daily ETL routines. So I can obtain the potential NextUpload date via this bit of SQL script.

SELECT DATEADD(day, 1, MAX(Upload_Date)) AS NextUpload FROM Import_Registry

Problem. I need to compare these two dates (the source db date & the DW next upload date) to see if they match. If they do match, then I run all the ETL packages using the date. If they do not match, say for example if the source database date is less than the "NextUpload" date I want to exit the routines "gracefully" and log the failure.

How do I get this working - can't seem to get my head around how I can compare the 2 dates ?

Derived column transformation has DateDiff function.

or

Script Component, you can write your own function in vb.net

|||Use the precedence constraints in the control flow. Use Execute SQL tasks to get your dates, and then hook them up to a sequence container which houses your data flow(s). The precedence constraint can perform your date comparison.|||

Hey Phil,

Thanks for that... you just confirmed what I had gradually worked out for myself!!

My solution was.....

I created 2 Execute SQL tasks. One to get the source database date, the other to get the "next date" due for loading into the DW and joined them to the first execute package task. But, between the final Execut SQL task & the package task I created a precedence "expression" constraint as follows

@.[User::MasterPackage_vLoaddate]== @.[User::NextLoadDate]

which were the 2 variables assigned from the Execute SQL tasks. On success, the remaining packages run but on failure the job fails.

It works great, so thanks for confirming that....

|||I appreciate that you tried this on your own and got it to work. I also appreciate that you posted your solution for others to use.

Glad it worked out for you!

Thanks,
Phil

Commercial Audit Software

I'm looking for a good commercial, 3rd party solution, not a Trace solution.
Can anyone provide comments on something they have in production? So
far, my limited research has turned up Lumigent Entegra, and RippleTech
LogCaster.
My goal is to track DML changes by login with minimal impact on system
performance. Further, I need the audit data to be as secure as possible from
malicious updates and deletes. This is to implement audit tracking for SOX
compliance.
ThanksLumigent Entegra is probably the most mature product on the market for
server-level auditing. It isn't perfect, but it can go a long way towards
filling the audit requirements.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Garth Wells" <nobody@.ishome.com> wrote in message
news:uoCCTMXKFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I'm looking for a good commercial, 3rd party solution, not a Trace
> solution.
> Can anyone provide comments on something they have in production? So
> far, my limited research has turned up Lumigent Entegra, and RippleTech
> LogCaster.
> My goal is to track DML changes by login with minimal impact on system
> performance. Further, I need the audit data to be as secure as possible
> from
> malicious updates and deletes. This is to implement audit tracking for SOX
> compliance.
> Thanks
>|||Manakoa Services Corporation is releasing a System Controls MP for MOM2005
which provides basic collection and reporting for windows security events.
You can review the features at http://www.manakoa.com/products/scmp/
The RTM is scheduled for May 31st. If your interested in particpating in the
beta you can contact me at jbeckett@.manakoa.com for more info.
"Garth Wells" wrote:

> I'm looking for a good commercial, 3rd party solution, not a Trace solutio
n.
> Can anyone provide comments on something they have in production? So
> far, my limited research has turned up Lumigent Entegra, and RippleTech
> LogCaster.
> My goal is to track DML changes by login with minimal impact on system
> performance. Further, I need the audit data to be as secure as possible fr
om
> malicious updates and deletes. This is to implement audit tracking for SOX
> compliance.
> Thanks
>
>|||You can try LiveAudit from www.datamirror.com. It captures inserts updates
and deletes from the ms_replcmds table in the distribution database. You
need to publish the tables that you need to track. In addition to the actual
change in a row, you can also add journal information such as who made the
change, when the change was made, and what type of change accured among
other journal control information the changes are then reported as table
entries in most databases available. From there, you can use something like
Crystal reports to look at the history or trail of the changes made to the
database.
Hope this helps,
RdR
"Jeremiah Beckett" <Jeremiah Beckett@.discussions.microsoft.com> wrote in
message news:B08EC974-72DB-461F-AAEB-98CE1687852A@.microsoft.com...[vbcol=seagreen]
> Manakoa Services Corporation is releasing a System Controls MP for MOM2005
> which provides basic collection and reporting for windows security events.
> You can review the features at http://www.manakoa.com/products/scmp/
> The RTM is scheduled for May 31st. If your interested in particpating in
> the
> beta you can contact me at jbeckett@.manakoa.com for more info.
>
> "Garth Wells" wrote:
>

Commercial Audit Software

I'm looking for a good commercial, 3rd party solution, not a Trace solution.
Can anyone provide comments on something they have in production? So
far, my limited research has turned up Lumigent Entegra, and RippleTech
LogCaster.
My goal is to track DML changes by login with minimal impact on system
performance. Further, I need the audit data to be as secure as possible from
malicious updates and deletes. This is to implement audit tracking for SOX
compliance.
Thanks
Lumigent Entegra is probably the most mature product on the market for
server-level auditing. It isn't perfect, but it can go a long way towards
filling the audit requirements.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Garth Wells" <nobody@.ishome.com> wrote in message
news:uoCCTMXKFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I'm looking for a good commercial, 3rd party solution, not a Trace
> solution.
> Can anyone provide comments on something they have in production? So
> far, my limited research has turned up Lumigent Entegra, and RippleTech
> LogCaster.
> My goal is to track DML changes by login with minimal impact on system
> performance. Further, I need the audit data to be as secure as possible
> from
> malicious updates and deletes. This is to implement audit tracking for SOX
> compliance.
> Thanks
>
|||Manakoa Services Corporation is releasing a System Controls MP for MOM2005
which provides basic collection and reporting for windows security events.
You can review the features at http://www.manakoa.com/products/scmp/
The RTM is scheduled for May 31st. If your interested in particpating in the
beta you can contact me at jbeckett@.manakoa.com for more info.
"Garth Wells" wrote:

> I'm looking for a good commercial, 3rd party solution, not a Trace solution.
> Can anyone provide comments on something they have in production? So
> far, my limited research has turned up Lumigent Entegra, and RippleTech
> LogCaster.
> My goal is to track DML changes by login with minimal impact on system
> performance. Further, I need the audit data to be as secure as possible from
> malicious updates and deletes. This is to implement audit tracking for SOX
> compliance.
> Thanks
>
>
|||You can try LiveAudit from www.datamirror.com. It captures inserts updates
and deletes from the ms_replcmds table in the distribution database. You
need to publish the tables that you need to track. In addition to the actual
change in a row, you can also add journal information such as who made the
change, when the change was made, and what type of change accured among
other journal control information the changes are then reported as table
entries in most databases available. From there, you can use something like
Crystal reports to look at the history or trail of the changes made to the
database.
Hope this helps,
RdR
"Jeremiah Beckett" <Jeremiah Beckett@.discussions.microsoft.com> wrote in
message news:B08EC974-72DB-461F-AAEB-98CE1687852A@.microsoft.com...[vbcol=seagreen]
> Manakoa Services Corporation is releasing a System Controls MP for MOM2005
> which provides basic collection and reporting for windows security events.
> You can review the features at http://www.manakoa.com/products/scmp/
> The RTM is scheduled for May 31st. If your interested in particpating in
> the
> beta you can contact me at jbeckett@.manakoa.com for more info.
>
> "Garth Wells" wrote:

Commercial Audit Software

I'm looking for a good commercial, 3rd party solution, not a Trace solution.
Can anyone provide comments on something they have in production? So
far, my limited research has turned up Lumigent Entegra, and RippleTech
LogCaster.
My goal is to track DML changes by login with minimal impact on system
performance. Further, I need the audit data to be as secure as possible from
malicious updates and deletes. This is to implement audit tracking for SOX
compliance.
ThanksLumigent Entegra is probably the most mature product on the market for
server-level auditing. It isn't perfect, but it can go a long way towards
filling the audit requirements.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Garth Wells" <nobody@.ishome.com> wrote in message
news:uoCCTMXKFHA.2852@.TK2MSFTNGP14.phx.gbl...
> I'm looking for a good commercial, 3rd party solution, not a Trace
> solution.
> Can anyone provide comments on something they have in production? So
> far, my limited research has turned up Lumigent Entegra, and RippleTech
> LogCaster.
> My goal is to track DML changes by login with minimal impact on system
> performance. Further, I need the audit data to be as secure as possible
> from
> malicious updates and deletes. This is to implement audit tracking for SOX
> compliance.
> Thanks
>|||Manakoa Services Corporation is releasing a System Controls MP for MOM2005
which provides basic collection and reporting for windows security events.
You can review the features at http://www.manakoa.com/products/scmp/
The RTM is scheduled for May 31st. If your interested in particpating in the
beta you can contact me at jbeckett@.manakoa.com for more info.
"Garth Wells" wrote:
> I'm looking for a good commercial, 3rd party solution, not a Trace solution.
> Can anyone provide comments on something they have in production? So
> far, my limited research has turned up Lumigent Entegra, and RippleTech
> LogCaster.
> My goal is to track DML changes by login with minimal impact on system
> performance. Further, I need the audit data to be as secure as possible from
> malicious updates and deletes. This is to implement audit tracking for SOX
> compliance.
> Thanks
>
>|||You can try LiveAudit from www.datamirror.com. It captures inserts updates
and deletes from the ms_replcmds table in the distribution database. You
need to publish the tables that you need to track. In addition to the actual
change in a row, you can also add journal information such as who made the
change, when the change was made, and what type of change accured among
other journal control information the changes are then reported as table
entries in most databases available. From there, you can use something like
Crystal reports to look at the history or trail of the changes made to the
database.
Hope this helps,
RdR
"Jeremiah Beckett" <Jeremiah Beckett@.discussions.microsoft.com> wrote in
message news:B08EC974-72DB-461F-AAEB-98CE1687852A@.microsoft.com...
> Manakoa Services Corporation is releasing a System Controls MP for MOM2005
> which provides basic collection and reporting for windows security events.
> You can review the features at http://www.manakoa.com/products/scmp/
> The RTM is scheduled for May 31st. If your interested in particpating in
> the
> beta you can contact me at jbeckett@.manakoa.com for more info.
>
> "Garth Wells" wrote:
>> I'm looking for a good commercial, 3rd party solution, not a Trace
>> solution.
>> Can anyone provide comments on something they have in production? So
>> far, my limited research has turned up Lumigent Entegra, and RippleTech
>> LogCaster.
>> My goal is to track DML changes by login with minimal impact on system
>> performance. Further, I need the audit data to be as secure as possible
>> from
>> malicious updates and deletes. This is to implement audit tracking for
>> SOX
>> compliance.
>> Thanks
>>

Comitting statemnts

Hi!
I have a lot of sql scripts in *.sql files.
I'm looking for a solution how to execute them in "one statement" (for
example in Query Analizer) .
Thanks.Query analyzer does not have the ability to read .sql files and execute the SQL code inside those
files. OSQL.EXE does, check out the :r option. But I don't think that you can have GO in those .sql
files.
Another option is to use xp_cmdshell and from there execute OSQL.EXE with appropriate command line
parameters (including name of the .sql file).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dmitry Karneyev" <karneyev@.msn.com> wrote in message news:ONXUBrUnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I have a lot of sql scripts in *.sql files.
> I'm looking for a solution how to execute them in "one statement" (for
> example in Query Analizer) .
> Thanks.
>|||Thanks for advice!
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:%23XrYmsUnDHA.2536@.tk2msftngp13.phx.gbl...
> Query analyzer does not have the ability to read .sql files and execute
the SQL code inside those
> files. OSQL.EXE does, check out the :r option. But I don't think that you
can have GO in those .sql
> files.
> Another option is to use xp_cmdshell and from there execute OSQL.EXE with
appropriate command line
> parameters (including name of the .sql file).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Dmitry Karneyev" <karneyev@.msn.com> wrote in message
news:ONXUBrUnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > Hi!
> >
> > I have a lot of sql scripts in *.sql files.
> > I'm looking for a solution how to execute them in "one statement" (for
> > example in Query Analizer) .
> >
> > Thanks.
> >
> >
>|||Hi Tibor ,
We can have "go" in .SQL files and execute this in OSQL / ISQL.
Hi Dmitry Karneyev ,
From Query analyzer u cant do that, instead create a .BAT file
inside the .BAT file , u can call the .SQL files using OSQL / ISQL
eg:
OSQL -Usa -Ppass -Sservername -ic:\a1.sql >out.txt
OSQL -Usa -Ppass -Sservername -ic:\a2.sql >>out.txt
OSQL -Usa -Ppass -Sservername -ic:\a3.sql >>out.txt
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:#XrYmsUnDHA.2536@.tk2msftngp13.phx.gbl...
> Query analyzer does not have the ability to read .sql files and execute
the SQL code inside those
> files. OSQL.EXE does, check out the :r option. But I don't think that you
can have GO in those .sql
> files.
> Another option is to use xp_cmdshell and from there execute OSQL.EXE with
appropriate command line
> parameters (including name of the .sql file).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Dmitry Karneyev" <karneyev@.msn.com> wrote in message
news:ONXUBrUnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > Hi!
> >
> > I have a lot of sql scripts in *.sql files.
> > I'm looking for a solution how to execute them in "one statement" (for
> > example in Query Analizer) .
> >
> > Thanks.
> >
> >
>|||What Tibor refers to is using the :r options in a input file for osql. :r
allows you pass a list of files to run to osql, for example:
file all.sql
:ra1.sql
:ra2.sql
:ra3.sql
If you now pass the file all.sql as the input file to osql
(OSQL -Usa -Ppass -Sservername -ic:\all.sql), the files a1.sql, a2.sql and
a3.sql will be executed. The drawback of this is that you can't use GO
within the files a1.sql, a2.sql and a3.sql, which is a bummer.
If you use the input .sql files directly with osql you can indeed include GO
in the sql file.
:r is an undocumented option, so we have to take it as it comes I guess.
Jacco Schalkwijk
SQL Server MVP
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ug%23PoAVnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> Hi Tibor ,
> We can have "go" in .SQL files and execute this in OSQL / ISQL.
>
> Hi Dmitry Karneyev ,
> From Query analyzer u cant do that, instead create a .BAT file
> inside the .BAT file , u can call the .SQL files using OSQL / ISQL
> eg:
> OSQL -Usa -Ppass -Sservername -ic:\a1.sql >out.txt
> OSQL -Usa -Ppass -Sservername -ic:\a2.sql >>out.txt
> OSQL -Usa -Ppass -Sservername -ic:\a3.sql >>out.txt
> Thanks
> Hari
> MCDBA
>
>
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:#XrYmsUnDHA.2536@.tk2msftngp13.phx.gbl...
> > Query analyzer does not have the ability to read .sql files and execute
> the SQL code inside those
> > files. OSQL.EXE does, check out the :r option. But I don't think that
you
> can have GO in those .sql
> > files.
> >
> > Another option is to use xp_cmdshell and from there execute OSQL.EXE
with
> appropriate command line
> > parameters (including name of the .sql file).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Dmitry Karneyev" <karneyev@.msn.com> wrote in message
> news:ONXUBrUnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > > Hi!
> > >
> > > I have a lot of sql scripts in *.sql files.
> > > I'm looking for a solution how to execute them in "one statement" (for
> > > example in Query Analizer) .
> > >
> > > Thanks.
> > >
> > >
> >
> >
>|||> We can have "go" in .SQL files and execute this in OSQL / ISQL.
I never said we can't. I was referring to the :r option of OSQL, See Jacco's explanation (thanks
Jacco!).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:ug%23PoAVnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> Hi Tibor ,
> We can have "go" in .SQL files and execute this in OSQL / ISQL.
>|||My pleasure :)
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eW7gJhVnDHA.2456@.TK2MSFTNGP09.phx.gbl...
> > We can have "go" in .SQL files and execute this in OSQL / ISQL.
> I never said we can't. I was referring to the :r option of OSQL, See
Jacco's explanation (thanks
> Jacco!).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ug%23PoAVnDHA.2304@.TK2MSFTNGP11.phx.gbl...
> > Hi Tibor ,
> >
> > We can have "go" in .SQL files and execute this in OSQL / ISQL.
> >
> >
>|||Thanks guys for explanation!
Since I have "go" in all of my files I decided to do this:
create a "bat" file with following strings
osql -E -S "servername" -d "databasename" -i "sqlfilename"
it works perfect!
"Dmitry Karneyev" <karneyev@.msn.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:ONXUBrUnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I have a lot of sql scripts in *.sql files.
> I'm looking for a solution how to execute them in "one statement" (for
> example in Query Analizer) .
> Thanks.
>