Comparing databases

Hi,
I need to compare 2 databases to check for missing objects, columns, etc.
Comparing objects was pretty easy. Just a pair of sql statements on the
sysobjects table and it worked fine.
Now I need to go a level deeper, by comparing missing & different columns in
tables. Is it possible to get the results from the system tables or do I
have to use DMO?
Thanks,
IvanIvan
Visit at http://www.red-gate.com
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OywqAZsiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I need to compare 2 databases to check for missing objects, columns, etc.
> Comparing objects was pretty easy. Just a pair of sql statements on the
> sysobjects table and it worked fine.
> Now I need to go a level deeper, by comparing missing & different columns
> in
> tables. Is it possible to get the results from the system tables or do I
> have to use DMO?
> Thanks,
> Ivan
>|||I know that there are quite a few tools that exist, but I need to develop my
own tool as this will be part of yet another bigger suite of tools.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> Ivan
> Visit at http://www.red-gate.com
>
>
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:OywqAZsiGHA.1508@.TK2MSFTNGP04.phx.gbl...
etc.
columns
>|||Well , then I'd use DMO objects library
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%231DU91siGHA.3780@.TK2MSFTNGP03.phx.gbl...
>I know that there are quite a few tools that exist, but I need to develop
>my
> own tool as this will be part of yet another bigger suite of tools.
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> etc.
> columns
>|||What to use depends on whether you prefer to work at the TSQL level or at th
e API level:
TSQL: For 2000, use syscolumns. For 2005, use sys.columns. Or (either versio
n) use the
information_schema views.
API: For 2000, use DMO. For 2005, use SMO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%231DU91siGHA.3780@.TK2MSFTNGP03.phx.gbl...
>I know that there are quite a few tools that exist, but I need to develop m
y
> own tool as this will be part of yet another bigger suite of tools.
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> etc.
> columns
>|||
> Now I need to go a level deeper, by comparing missing & different columns
in
> tables. Is it possible to get the results from the system tables or do I
> have to use DMO?
Try this
select name from <DB1>..syscolumns where
id=object_id('<DB1>..<TABLE_NAME>') and name not in
(select name from <DB2>..syscolumns where
id=object_id('<DB2>..<TABLE_NAME>'))
--This will return the additional columns in table in another database.
You can well modify it to meet your specefic requirement.

Compare data difference between two database in SQLServer 2005

Hi,
I have too databases, their structures are completely same, with minor data
difference, I want to generate a patch SQL statements, not complete backup
by comparing them, thus I can synchronzie the out-dated database with that
script. Does SQLServer2005 provide thus functionality? Or there is other
tool I can use. Thank you
zlf
You can do it yourself if you link the servers, but it may take a good
amount of work. Easier might be to use a third party tool. Red-Gate's SQL
Data Compare is probably the most obvious choice...
http://www.red-gate.com/products/SQL_Data_Compare/index.htm

Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"zlf" <zlfcn@.hotmail.com> wrote in message
news:uFrkmWPtHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have too databases, their structures are completely same, with minor
> data difference, I want to generate a patch SQL statements, not complete
> backup by comparing them, thus I can synchronzie the out-dated database
> with that script. Does SQLServer2005 provide thus functionality? Or there
> is other tool I can use. Thank you
> zlf
>
|||On Jun 22, 1:57 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> You can do it yourself if you link the servers, but it may take a good
> amount of work. Easier might be to use a third party tool. Red-Gate's SQL
> DataCompareis probably the most obvious choice...
> http://www.red-gate.com/products/SQL_Data_Compare/index.htm
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "zlf" <z...@.hotmail.com> wrote in message
> news:uFrkmWPtHHA.1184@.TK2MSFTNGP04.phx.gbl...
>
>
> - Show quoted text -
Hi Zif,
another great choice is xSQL Softwares xSQL Data Compare which you can
get from http://www.xsqlsoftware.com - you don't just get 2 weeks of
free trial but you also have it free forever for smaller size
databases (up to a certain number of objects) as well as free with no
limitations for SQL Server Express databases.
Furthermore, we have a well document api (xSQL SDK) that allows you to
integrate the functionality in your own application if you want.
Hope this helps.
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com
|||It works. Thank both of you
"Adam Machanic" <amachanic@.IHATESPAMgmail.com>
?:%23jF$gbPtHHA.2164@.TK2MSFTNGP02.phx.gbl...
> You can do it yourself if you link the servers, but it may take a good
> amount of work. Easier might be to use a third party tool. Red-Gate's
> SQL Data Compare is probably the most obvious choice...
> http://www.red-gate.com/products/SQL_Data_Compare/index.htm
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "zlf" <zlfcn@.hotmail.com> wrote in message
> news:uFrkmWPtHHA.1184@.TK2MSFTNGP04.phx.gbl...
>

Compare data difference between two database in SQLServer 2005

Hi,
I have too databases, their structures are completely same, with minor data
difference, I want to generate a patch SQL statements, not complete backup
by comparing them, thus I can synchronzie the out-dated database with that
script. Does SQLServer2005 provide thus functionality? Or there is other
tool I can use. Thank you
zlfYou can do it yourself if you link the servers, but it may take a good
amount of work. Easier might be to use a third party tool. Red-Gate's SQL
Data Compare is probably the most obvious choice...
http://www.red-gate.com/products/SQ...mpare/index.htm
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"zlf" <zlfcn@.hotmail.com> wrote in message
news:uFrkmWPtHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have too databases, their structures are completely same, with minor
> data difference, I want to generate a patch SQL statements, not complete
> backup by comparing them, thus I can synchronzie the out-dated database
> with that script. Does SQLServer2005 provide thus functionality? Or there
> is other tool I can use. Thank you
> zlf
>|||On Jun 22, 1:57 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> You can do it yourself if you link the servers, but it may take a good
> amount of work. Easier might be to use a third party tool. Red-Gate's SQ
L
> DataCompareis probably the most obvious choice...
> http://www.red-gate.com/products/SQ...mpare/index.htm
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/boo
kDisplay.html?bID=10220
> "zlf" <z...@.hotmail.com> wrote in message
> news:uFrkmWPtHHA.1184@.TK2MSFTNGP04.phx.gbl...
>
>
>
> - Show quoted text -
Hi Zif,
another great choice is xSQL Softwares xSQL Data Compare which you can
get from http://www.xsqlsoftware.com - you don't just get 2 weeks of
free trial but you also have it free forever for smaller size
databases (up to a certain number of objects) as well as free with no
limitations for SQL Server Express databases.
Furthermore, we have a well document api (xSQL SDK) that allows you to
integrate the functionality in your own application if you want.
Hope this helps.
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com|||It works. Thank both of you
"Adam Machanic" <amachanic@.IHATESPAMgmail.com>
':%23jF$gbPtHHA.2164@.TK2MSFTNGP02.phx.gbl...
> You can do it yourself if you link the servers, but it may take a good
> amount of work. Easier might be to use a third party tool. Red-Gate's
> SQL Data Compare is probably the most obvious choice...
> http://www.red-gate.com/products/SQ...mpare/index.htm
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "zlf" <zlfcn@.hotmail.com> wrote in message
> news:uFrkmWPtHHA.1184@.TK2MSFTNGP04.phx.gbl...
>

Compare data difference between two database in SQLServer 2005

Hi,
I have too databases, their structures are completely same, with minor data
difference, I want to generate a patch SQL statements, not complete backup
by comparing them, thus I can synchronzie the out-dated database with that
script. Does SQLServer2005 provide thus functionality? Or there is other
tool I can use. Thank you
zlfYou can do it yourself if you link the servers, but it may take a good
amount of work. Easier might be to use a third party tool. Red-Gate's SQL
Data Compare is probably the most obvious choice...
http://www.red-gate.com/products/SQL_Data_Compare/index.htm
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"zlf" <zlfcn@.hotmail.com> wrote in message
news:uFrkmWPtHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have too databases, their structures are completely same, with minor
> data difference, I want to generate a patch SQL statements, not complete
> backup by comparing them, thus I can synchronzie the out-dated database
> with that script. Does SQLServer2005 provide thus functionality? Or there
> is other tool I can use. Thank you
> zlf
>|||On Jun 22, 1:57 pm, "Adam Machanic" <amacha...@.IHATESPAMgmail.com>
wrote:
> You can do it yourself if you link the servers, but it may take a good
> amount of work. Easier might be to use a third party tool. Red-Gate's SQL
> DataCompareis probably the most obvious choice...
> http://www.red-gate.com/products/SQL_Data_Compare/index.htm
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> "zlf" <z...@.hotmail.com> wrote in message
> news:uFrkmWPtHHA.1184@.TK2MSFTNGP04.phx.gbl...
>
> > Hi,
> > I have toodatabases, their structures are completely same, with minor
> > data difference, I want to generate a patch SQL statements, not complete
> > backup by comparing them, thus I can synchronzie the out-dated database
> > with that script. Does SQLServer2005 provide thus functionality? Or there
> > is other tool I can use. Thank you
> > zlf- Hide quoted text -
> - Show quoted text -
Hi Zif,
another great choice is xSQL Softwares xSQL Data Compare which you can
get from http://www.xsqlsoftware.com - you don't just get 2 weeks of
free trial but you also have it free forever for smaller size
databases (up to a certain number of objects) as well as free with no
limitations for SQL Server Express databases.
Furthermore, we have a well document api (xSQL SDK) that allows you to
integrate the functionality in your own application if you want.
Hope this helps.
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com|||It works. Thank both of you:)
"Adam Machanic" <amachanic@.IHATESPAMgmail.com>
':%23jF$gbPtHHA.2164@.TK2MSFTNGP02.phx.gbl...
> You can do it yourself if you link the servers, but it may take a good
> amount of work. Easier might be to use a third party tool. Red-Gate's
> SQL Data Compare is probably the most obvious choice...
> http://www.red-gate.com/products/SQL_Data_Compare/index.htm
>
> --
> Adam Machanic
> SQL Server MVP
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "zlf" <zlfcn@.hotmail.com> wrote in message
> news:uFrkmWPtHHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have too databases, their structures are completely same, with minor
>> data difference, I want to generate a patch SQL statements, not complete
>> backup by comparing them, thus I can synchronzie the out-dated database
>> with that script. Does SQLServer2005 provide thus functionality? Or there
>> is other tool I can use. Thank you
>> zlf
>

Communication link failure

I have a VB6 application running on an SQLServer database. At first, the co
nnection to the database succeeds, a number of sql subsequent statements (bo
th select and update statements) succeed and then from some point on (after
about 80 sql statements), I
get the following error message : -2147467259 : [Microsoft][ODBD SQL
Server Driver]Communication link failure.
This happens on a number of P.C.'s, however not on all of them.
Thanks,
John DesmetThis sounds like it might share a few common features with a posting I made
here on March 5, under the heading:
Re: Occasional "Timeout expired" message - on SP that should take 1 second
What do you think? This syndrome sometimes also generates the
"Communication Link Failure" message.
- ITFred|||Thank you for your reply. At the end of the day (literally), we found out t
hat the Symantic Client Firewall is causing the problem. When it is turned
off, the problem no longer occurs. Now, we have to modify a number of firew
all settings.
Thanks,
John Desmet

Command to display SQL Statements

Does anyone know the command to display the whole SQL statement for a
connection after apply sp3?
Thanks,
LijunTake a look at the fn_get_sql topic in books online, assuming you updated...
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:#UPomeMnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Does anyone know the command to display the whole SQL statement for a
> connection after apply sp3?
> Thanks,
> Lijun
>|||Hi Lijun,
I agree with Aaron, you can use the fn_get_sql function to retrieve the SQL handle (sql_handle
column of the sysprocesses) and help you diagnose problematic processes.
You can also use "DBCC INPUTBUFFER" to easily display the LAST statement sent from a
client. The system process ID (SPID) for the user connection can be displayed in the output of
the sp_who system stored procedure.
Does that answer your question? Please feel free to post in the group if this solves your
problem or if you would like further assistance.
Best regards,
Billy Yao
Microsoft Online Partner Support