Comparing DBs

is there any utility or program available that will compare two versions of
the same SQL Server database in terms of definition and objects. I don't
want to compare data. I need to see the differences in tables columns views
etc.
Thanks,
GaryThere are quite a few companies that make such products. Check out this
list: http://vyaskn.tripod.com/thirdparty.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Gary" <gb@.nospam.com> wrote in message
news:ewGG5cf2DHA.2308@.TK2MSFTNGP11.phx.gbl...
is there any utility or program available that will compare two versions of
the same SQL Server database in terms of definition and objects. I don't
want to compare data. I need to see the differences in tables columns views
etc.
Thanks,
Gary|||We use the following tool
http://www.adeptsql.com/
Its cheap, very accurate, very fast and will also update
your out of sync database if you wish. There is also a
trial version which you can download.
J
>--Original Message--
>is there any utility or program available that will
compare two versions of
>the same SQL Server database in terms of definition and
objects. I don't
>want to compare data. I need to see the differences in
tables columns views
>etc.
>Thanks,
>Gary
>
>.
>|||We use ERWin by Computer Associates.
a bit spendy but it does More then just compare Databases.
http://www3.ca.com/Solutions/Product.asp?ID=260
Gregory A Jackson
PDX, Oregon|||Hi Gary,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
For your question of compare the two versions of the same SQL Server
database in terms of definitions and objects but not data in them. Besides
the utiltities suggested by our MVPs and community members, if the compare
is quite simple, you can just generate a 'script' that define the objects
in 2 SQL Server respectively and compare the script. To generate the
script, they can use SQL Enterprise Manager and choose the "Object|Generate
SQL Script..." menu item. Another way is to use 'sp_help' upon each object
in the 2 database and compare them.
Hope this helps. If you still have questions on this issue, please feel
free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Gary,
As said in the answers, you can use tools to compare the databases.
I prefer to use the sysobjects table to do that, simply by doing
a
SELECT * from db1.sysobjects where not in (SELECT * from db2.sysobjects)
or
SELECT * from db2.sysobjects where not in (SELECT * from db1.sysobjects)
This works if you want to analyse the difference in term of tables
and fields.
To have a deeper analysis (fields of different types) it is more difficult
but possible with other system tables like syscolumns.
Gilles
"Gary" <gb@.nospam.com> wrote in message news:<ewGG5cf2DHA.2308@.TK2MSFTNGP11.phx.gbl>...
> is there any utility or program available that will compare two versions of
> the same SQL Server database in terms of definition and objects. I don't
> want to compare data. I need to see the differences in tables columns views
> etc.
> Thanks,
> Gary|||also check out DB Ghost @. www.dbghost.com
regards,
Mark Baekdal
>--Original Message--
>is there any utility or program available that will
compare two versions of
>the same SQL Server database in terms of definition and
objects. I don't
>want to compare data. I need to see the differences in
tables columns views
>etc.
>Thanks,
>Gary
>
>.
>|||I downloaded it and tried it out. Works great. Many thanks.
Gary
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0d0601c3d9fa$103b29b0$a601280a@.phx.gbl...
> We use the following tool
> http://www.adeptsql.com/
> Its cheap, very accurate, very fast and will also update
> your out of sync database if you wish. There is also a
> trial version which you can download.
> J
>
> >--Original Message--
> >is there any utility or program available that will
> compare two versions of
> >the same SQL Server database in terms of definition and
> objects. I don't
> >want to compare data. I need to see the differences in
> tables columns views
> >etc.
> >Thanks,
> >Gary
> >
> >
> >.
> >

Comparing DBs

is there any utility or program available that will compare two versions of
the same SQL Server database in terms of definition and objects. I don't
want to compare data. I need to see the differences in tables columns views
etc.
Thanks,
GaryThere are quite a few companies that make such products. Check out this
list: http://vyaskn.tripod.com/thirdparty.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Gary" <gb@.nospam.com> wrote in message
news:ewGG5cf2DHA.2308@.TK2MSFTNGP11.phx.gbl...
is there any utility or program available that will compare two versions of
the same SQL Server database in terms of definition and objects. I don't
want to compare data. I need to see the differences in tables columns views
etc.
Thanks,
Gary|||We use ERWin by Computer Associates.
a bit spendy but it does More then just compare Databases.
http://www3.ca.com/Solutions/Product.asp?ID=260
Gregory A Jackson
PDX, Oregon|||Hi Gary,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
For your question of compare the two versions of the same SQL Server
database in terms of definitions and objects but not data in them. Besides
the utiltities suggested by our MVPs and community members, if the compare
is quite simple, you can just generate a 'script' that define the objects
in 2 SQL Server respectively and compare the script. To generate the
script, they can use SQL Enterprise Manager and choose the "Object|Generate
SQL Script..." menu item. Another way is to use 'sp_help' upon each object
in the 2 database and compare them.
Hope this helps. If you still have questions on this issue, please feel
free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Gary,
As said in the answers, you can use tools to compare the databases.
I prefer to use the sysobjects table to do that, simply by doing
a
SELECT * from db1.sysobjects where not in (SELECT * from db2.sysobjects)
or
SELECT * from db2.sysobjects where not in (SELECT * from db1.sysobjects)
This works if you want to analyse the difference in term of tables
and fields.
To have a deeper analysis (fields of different types) it is more difficult
but possible with other system tables like syscolumns.
Gilles
"Gary" <gb@.nospam.com> wrote in message news:<ewGG5cf2DHA.2308@.TK2MSFTNGP11.phx.gbl>...

quote:

---

[col
or=darkred]
> is there any utility or program available that will compare two versions o
f
> the same SQL Server database in terms of definition and objects. I don't
> want to compare data. I need to see the differences in tables columns vie
ws
> etc.
> Thanks,
> Gary[/color]

---

sqlsql

Comparing Database Data

Can anyone tell me how a program like this might work:
http://www.red-gate.com/products/SQ...mpare/index.htm
I want to backup databases into a central repository but I only want
the records that have changed for that day. This program seems to do it
efficiently. Does Sql Server, Oracle, etc offer any sort of built in
way of doing this via metadata or a similar mechanism?

I want to be able to mirror databases in such a way that I dont need to
have the admin password for them. I don't want to alter the database
schema in any way.

The only way I can think off the top of my head is to assign MD5
checksum values to rows and then compare the checksums in the master
and copy, however, to do it efficiently, you'd have to save the MD5
values as you go, which would involve altering the schema on the master
table.

Quote:

Originally Posted by

I want to backup databases into a central repository but I only want
the records that have changed for that day. This program seems to do it
efficiently. Does Sql Server, Oracle, etc offer any sort of built in
way of doing this via metadata or a similar mechanism?
>

You could setup transactional replication in SQL Server. Modifications
would be propagated almost in real time.

Regards,
lucm

compare two text files

Dear all,

What software can compare two text files? They are contains about 100k data generated from Visual Basic 6.0 program and MS SQL 2000. I have many files to compare daily. Please give me some suggestions. Thanks.

Alex

I think VSS (source safe) is suitable for that|||

thanks Eisa. it is helpful.

Could you suggest some freewares?

|||Hi Alex,
I don't know if it is allowed to share free software here or not, howere you can serach google for compare text files free|||Thank your for your help|||Windows has a command line utility (fc) that compares two files. Type "fc /?" at a command prompt, and it will list the options/switches. It's a good solution if it has the features you need, because it's built into Windows, and you won't have to bother installing it everywhere you need it. If you want to use it in a job, you can invoke it with an operating system type step, or use xp_cmdshell if you need to call it after dynamically building the command at run-time. In a job, you would have to pipe the results to another text file, for review.|||

CompareIt! - cool

http://www.grigsoft.com/

|||thanks a lot

compare two text files

Dear all,

What software can compare two text files? They are contains about 100k data generated from Visual Basic 6.0 program and MS SQL 2000. I have many files to compare daily. Please give me some suggestions. Thanks.

Alex

I think VSS (source safe) is suitable for that|||

thanks Eisa. it is helpful.

Could you suggest some freewares?

|||Hi Alex,
I don't know if it is allowed to share free software here or not, howere you can serach google for compare text files free|||Thank your for your help|||Windows has a command line utility (fc) that compares two files. Type "fc /?" at a command prompt, and it will list the options/switches. It's a good solution if it has the features you need, because it's built into Windows, and you won't have to bother installing it everywhere you need it. If you want to use it in a job, you can invoke it with an operating system type step, or use xp_cmdshell if you need to call it after dynamically building the command at run-time. In a job, you would have to pipe the results to another text file, for review.|||

CompareIt! - cool

http://www.grigsoft.com/

|||thanks a lot

Compare sql (SQL DIFF)

hi,
I am looking for a program or utility that it could compare sql server table
schema, views, triggers and sps?
not sure which program is better. Suggestions please.
ThanksTry Red Gate SQL Compare (www.red-gate.com).
Ben Nevarez, MCDBA, OCP
Database Administrator
"mecn" wrote:

> hi,
> I am looking for a program or utility that it could compare sql server tab
le
> schema, views, triggers and sps?
> not sure which program is better. Suggestions please.
> Thanks
>
>|||Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u90BoMRpGHA.4032@.TK2MSFTNGP03.phx.gbl...
> hi,
> I am looking for a program or utility that it could compare sql server
> table schema, views, triggers and sps?
> not sure which program is better. Suggestions please.
> Thanks
>|||Red-gate rules!
"mecn" wrote:

> Thanks
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:u90BoMRpGHA.4032@.TK2MSFTNGP03.phx.gbl...
>
>|||Try ApexSQL's SQLDiff tool.
www.ApexSQL.com
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u90BoMRpGHA.4032@.TK2MSFTNGP03.phx.gbl...
> hi,
> I am looking for a program or utility that it could compare sql server
> table schema, views, triggers and sps?
> not sure which program is better. Suggestions please.
> Thanks
>|||mecn,
The free, open-source SchemaCrawler tool will do what you need.
SchemaCrawler outputs details of your schema (tables, views,
procedures, and more) in a diff-able plain-text format (text, CSV, or
XHTML). SchemaCrawler can also output data (including CLOBs and BLOBs)
in the same plain-text formats. You can use a standard diff program to
diff the current output with a reference version of the output.
SchemaCrawler can be run either from the command line, or as an ant
task. A lot of examples are available with the download to help you get
started.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required.
Sualeh Fatehi.

Compare sql (SQL DIFF)

hi,
I am looking for a program or utility that it could compare sql server table
schema, views, triggers and sps?
not sure which program is better. Suggestions please.
ThanksTry Red Gate SQL Compare (www.red-gate.com).
Ben Nevarez, MCDBA, OCP
Database Administrator
"mecn" wrote:
> hi,
> I am looking for a program or utility that it could compare sql server table
> schema, views, triggers and sps?
> not sure which program is better. Suggestions please.
> Thanks
>
>|||Thanks
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u90BoMRpGHA.4032@.TK2MSFTNGP03.phx.gbl...
> hi,
> I am looking for a program or utility that it could compare sql server
> table schema, views, triggers and sps?
> not sure which program is better. Suggestions please.
> Thanks
>|||Red-gate rules!
"mecn" wrote:
> Thanks
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:u90BoMRpGHA.4032@.TK2MSFTNGP03.phx.gbl...
> > hi,
> >
> > I am looking for a program or utility that it could compare sql server
> > table schema, views, triggers and sps?
> > not sure which program is better. Suggestions please.
> >
> > Thanks
> >
>
>|||Try ApexSQL's SQLDiff tool.
www.ApexSQL.com
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u90BoMRpGHA.4032@.TK2MSFTNGP03.phx.gbl...
> hi,
> I am looking for a program or utility that it could compare sql server
> table schema, views, triggers and sps?
> not sure which program is better. Suggestions please.
> Thanks
>|||mecn,
The free, open-source SchemaCrawler tool will do what you need.
SchemaCrawler outputs details of your schema (tables, views,
procedures, and more) in a diff-able plain-text format (text, CSV, or
XHTML). SchemaCrawler can also output data (including CLOBs and BLOBs)
in the same plain-text formats. You can use a standard diff program to
diff the current output with a reference version of the output.
SchemaCrawler can be run either from the command line, or as an ant
task. A lot of examples are available with the download to help you get
started.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required.
Sualeh Fatehi.

Compare SP 's content ?

Besides buy a third-party program, any way to compare the content of the SP
in two different SQL server '
Thanks a lotHi,
this will get you the Procedure text, comparing should be easy for you now,
if you need further help just raise a hand.
Select sc.* from syscomments sc
inner join sysobjects so ON
so.id = sc.id
Where XType = 'P' AND
so.name like 'YourProcedurename'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
news:eBuG6fGVFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Besides buy a third-party program, any way to compare the content of the
> SP in two different SQL server '
> Thanks a lot
>|||The way I do it is to generate a SQL script from each, and then just use 'fc
'
or 'diff'. I'm sure there are better ways of doing it though...|||Agnes
Try this one
if (select checksum_agg(checksum(ROUTINE_DEFINITION
))FROM
INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='spMyProc')
<> (select checksum_agg(checksum(ROUTINE_DEFINITION
))FROM
DatabaseName.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='spMyProc')
print 'different'
else
print 'probably the same'
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:eBuG6fGVFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Besides buy a third-party program, any way to compare the content of the
SP
> in two different SQL server '
> Thanks a lot
>|||Jens
I'd avoid querying system tables inseatd use
SELECT ROUTINE_DEFINITION FROM
INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='spMyProc'
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OL5ifkGVFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi,
> this will get you the Procedure text, comparing should be easy for you
now,
> if you need further help just raise a hand.
> Select sc.* from syscomments sc
> inner join sysobjects so ON
> so.id = sc.id
> Where XType = 'P' AND
> so.name like 'YourProcedurename'
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
> news:eBuG6fGVFHA.4056@.TK2MSFTNGP15.phx.gbl...
>|||Unless you have a good algorithmn, comparing SP's as already indicated will
not yield consistent results.
The code of an SP can be functionally identical but fail textual, checksum,
or line by line text / checksum comparisons.
At the end ofthe day it is pointless - SP's are easily recreated, so after
the schema is verified, just recompile all of the a-new.
I do have a tool that does this (and much more) and works reliably possibly
for the same reasons as you, but it is not a released product.
If you are interested in beta testing the product, post back with a munged
email while I think about it :)
- Tim
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OL5ifkGVFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi,
> this will get you the Procedure text, comparing should be easy for you
> now, if you need further help just raise a hand.
> Select sc.* from syscomments sc
> inner join sysobjects so ON
> so.id = sc.id
> Where XType = 'P' AND
> so.name like 'YourProcedurename'
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
> news:eBuG6fGVFHA.4056@.TK2MSFTNGP15.phx.gbl...
>|||edit both, save to text file and use windiff ?|||If you put a blank line or extra space in any of the sp's these methods will
fail.
If there are any structured comments with revision details in, again, these
methods will fail.
If the objective is to ensure that the SP's are not tampered with then a
checksum system with fc is a good starter. However a better starting point
would be security and traces.
"Mark" <nospam> wrote in message
news:%23gtuPJHVFHA.1404@.TK2MSFTNGP09.phx.gbl...
> edit both, save to text file and use windiff ?
>

Commit and Rollback?

Hi,

I'm using an SQL Express database over a network, using a C# Express program. So I had to use pure SQL connections and commands instead of using Data Sources (couldn't find a way for it to work). In the program / DB I've got a couple of Master - Detail situations. Something like:

Product:

--

productID

(...)

Acessories:

-

acessID

(...)

ProductAcess:

--

productID

acessID

So when inserting a new Product, I'll have to first insert the product (with product name, price, and so on) and once I get the product ID from the insert command, I'll insert the ProductAcess rows. I've found a problem in this though. If for some reason the insert of the product is successful, but the insert of ProductAcess

fails, I've got a big mess in hands because I'll have a row in Product with no rows in ProductAcess (which shouldn't happen in my program scenario). I could solve this by deleting all rows from the DB which connected in someway to the product that failed to insert, but would be far better and correct if I used a commit command at the end of the insert commands to make sure only the right data would be inserted (saving time and resources). I use this all the time in Oracle databases, but don't know if it is possible in SQL Express... Is it? How? Thanks

hi,

you can use transactions in Ado.Net as well, similarly to the trivial code snippet like

SqlConnection con = new SqlConnection(""); SqlCommand cmd = new SqlCommand(); SqlTransaction tx = con.BeginTransaction; cmd.Connection = con; cmd.Transaction = tx; int Res = cmd.ExecuteNonQuery(); cmd.Dispose; cmd = null; if (Res == 0) tx.Rollback; else tx.Commit; tx.Dispose; tx = null; con.Dispose; con = null;

regards

Commit and rollback

How are these placed exactly in the program. Do you run at 1 time or
different?
Example:
begin tran
delete
from enc
where epsid = '23232'
commit
I ran the above then ran the following to undo:
begin tran
rollback tran
That was it. I am not entirely sure how to put the transactions together to
enable the rollback if the data being changed it not correct.
Message posted via http://www.webservertalk.comTina,
Use the @.@.ERROR variable, like the following:
begin tran
delete from enc where epsid = '23232'
if @.@.ERROR = 0
commit tran
else
rollback tran
What this does is attempts to delete the record(s) from the enc table with
the given filter. If the query has a problem (fails, whatever), then the
@.@.ERROR variable will be set to some non-zero value. If the query completed
successfully, then the @.@.ERROR variable will be 0.
Hope that helps.
Richard
"tina miller via webservertalk.com" wrote:

> How are these placed exactly in the program. Do you run at 1 time or
> different?
> Example:
> begin tran
> delete
> from enc
> where epsid = '23232'
> commit
> I ran the above then ran the following to undo:
> begin tran
> rollback tran
> That was it. I am not entirely sure how to put the transactions together t
o
> enable the rollback if the data being changed it not correct.
> --
> Message posted via http://www.webservertalk.com
>|||I need something that I can do the rollback. This is in a training
environment and I need to be able to have it rollback.
Message posted via http://www.webservertalk.com

Command Line cmdexec in a job

I'm trying to zip my backup files using a zip program, scheduled in a
job. But I keep getting a error message stating.
"... (reason: The system cannot find the file specified). The step
failed."
Here is the step's text...
e:\WZZIP e:\backups\Mike.zip
If I use this one it works fine.
c:\winnt\wzzip.exe e:\backups\Mike.zip e:\backups\*.*
Any suggestions?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!So where is the wzzip.exe program?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"SQL Dude" <dude@.sql.com> wrote in message
news:eIMCZw8pDHA.2188@.TK2MSFTNGP11.phx.gbl...
> I'm trying to zip my backup files using a zip program, scheduled in a
> job. But I keep getting a error message stating.
> "... (reason: The system cannot find the file specified). The step
> failed."
>
> Here is the step's text...
> e:\WZZIP e:\backups\Mike.zip
>
> If I use this one it works fine.
> c:\winnt\wzzip.exe e:\backups\Mike.zip e:\backups\*.*
> Any suggestions?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!