Comparing SQL Databases

I've written an Access application that uses SQL Server 2005 for the backend.
I currently have about 10 clients using the application. When I have an
update for my clients I send them a script containing all the updates for the
server. The script contains ALTER, CREATE, etc procedures.
Unfortunately, one package had some problems and subsequently did not append
or process the entire script. The problem is, I don't know what has failed,
and therefore I don't know what procedures, views or tables may be incorrect.
My server is 100% accurate.
Is there a utility on the market that would compare two servers and report
what is missing or not the same in a report. Or better yet, one that creates
a script to bring them in snyc with each other. This would help me solve this
problem plus I would then not have to keep track of every change I make to
the server as I'm working.
Or, does SQL Server have a utility that does this?
Thanks.
www.red-gate.com
www.apexsql.com
Andrew J. Kelly SQL MVP
"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
news:E2C743BE-339D-477A-BF56-3C520F3FB611@.microsoft.com...
> I've written an Access application that uses SQL Server 2005 for the
> backend.
> I currently have about 10 clients using the application. When I have an
> update for my clients I send them a script containing all the updates for
> the
> server. The script contains ALTER, CREATE, etc procedures.
> Unfortunately, one package had some problems and subsequently did not
> append
> or process the entire script. The problem is, I don't know what has
> failed,
> and therefore I don't know what procedures, views or tables may be
> incorrect.
> My server is 100% accurate.
> Is there a utility on the market that would compare two servers and report
> what is missing or not the same in a report. Or better yet, one that
> creates
> a script to bring them in snyc with each other. This would help me solve
> this
> problem plus I would then not have to keep track of every change I make to
> the server as I'm working.
> Or, does SQL Server have a utility that does this?
> Thanks.
|||Hi,
I understand that one of your packages failed to execute and you would like
to know if there is a tool to compare two servers and report what is
missing or not the same in a report.
If I have misunderstood, please let me know.
I do not know if there is a third party tool for your concern, however I
think that you can try using SQL Profiler to monitor your SQL Server 2005
and look at the trace logs for manually checking the differences.
You may refer to:
Introducing SQL Server Profiler
http://msdn2.microsoft.com/en-us/library/ms181091.aspx
Using SQL Server Profiler
http://msdn2.microsoft.com/en-us/library/ms187929.aspx
Please feel free to let me know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Also look at xsql.
http://www.xsqlsoftware.com/Main.aspx
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
"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
news:E2C743BE-339D-477A-BF56-3C520F3FB611@.microsoft.com...
> I've written an Access application that uses SQL Server 2005 for the
> backend.
> I currently have about 10 clients using the application. When I have an
> update for my clients I send them a script containing all the updates for
> the
> server. The script contains ALTER, CREATE, etc procedures.
> Unfortunately, one package had some problems and subsequently did not
> append
> or process the entire script. The problem is, I don't know what has
> failed,
> and therefore I don't know what procedures, views or tables may be
> incorrect.
> My server is 100% accurate.
> Is there a utility on the market that would compare two servers and report
> what is missing or not the same in a report. Or better yet, one that
> creates
> a script to bring them in snyc with each other. This would help me solve
> this
> problem plus I would then not have to keep track of every change I make to
> the server as I'm working.
> Or, does SQL Server have a utility that does this?
> Thanks.
|||On Jul 27, 7:56 pm, Greg <AccessVBA...@.newsgroups.nospam> wrote:
> I've written an Access application that usesSQLServer2005 for the backend.
> I currently have about 10 clients using the application. When I have an
> update for my clients I send them a script containing all the updates for theserver. The script contains ALTER, CREATE, etc procedures.
> Unfortunately, one package had some problems and subsequently did not append
> or process the entire script. The problem is, I don't know what has failed,
> and therefore I don't know what procedures, views or tables may be incorrect.
> Myserveris 100% accurate.
> Is there a utility on the market that wouldcomparetwo servers and report
> what is missing or not the same in a report. Or better yet, one that creates
> a script to bring them in snyc with each other. This would help me solve this
> problem plus I would then not have to keep track of every change I make to
> theserveras I'm working.
> Or, doesSQLServerhave a utility that does this?
> Thanks.
Hi Greg,
check out xSQL Software's products, xSQL Object and xSQL Data Compare.
Those two utilities are completely free for SQL Server Express and
there is also a free lite edition that supports any SQL Server
Edition. You can download the products from http://www.xsqlsoftware.com
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com
|||xSQL Compare comes in a Lite edition that works without restrictions if you
are using SQL Server Express. I downloaded the full professional product and
tested it against my two databases and it reported all the differences
between the two of them. And, on top of that it automatially generated teh
scripts to update the differences as well.
This is an excellent product and meets my needs 100%.
"Hilary Cotter" wrote:

> Also look at xsql.
> http://www.xsqlsoftware.com/Main.aspx
> --
> 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
> "Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
> news:E2C743BE-339D-477A-BF56-3C520F3FB611@.microsoft.com...
>
>
|||www.dbconstructor.com
Robert Allen Schambach
"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
news:E2C743BE-339D-477A-BF56-3C520F3FB611@.microsoft.com...
> I've written an Access application that uses SQL Server 2005 for the
> backend.
> I currently have about 10 clients using the application. When I have an
> update for my clients I send them a script containing all the updates for
> the
> server. The script contains ALTER, CREATE, etc procedures.
> Unfortunately, one package had some problems and subsequently did not
> append
> or process the entire script. The problem is, I don't know what has
> failed,
> and therefore I don't know what procedures, views or tables may be
> incorrect.
> My server is 100% accurate.
> Is there a utility on the market that would compare two servers and report
> what is missing or not the same in a report. Or better yet, one that
> creates
> a script to bring them in snyc with each other. This would help me solve
> this
> problem plus I would then not have to keep track of every change I make to
> the server as I'm working.
> Or, does SQL Server have a utility that does this?
> Thanks.

Comparing SQL Databases

I've written an Access application that uses SQL Server 2005 for the backend.
I currently have about 10 clients using the application. When I have an
update for my clients I send them a script containing all the updates for the
server. The script contains ALTER, CREATE, etc procedures.
Unfortunately, one package had some problems and subsequently did not append
or process the entire script. The problem is, I don't know what has failed,
and therefore I don't know what procedures, views or tables may be incorrect.
My server is 100% accurate.
Is there a utility on the market that would compare two servers and report
what is missing or not the same in a report. Or better yet, one that creates
a script to bring them in snyc with each other. This would help me solve this
problem plus I would then not have to keep track of every change I make to
the server as I'm working.
Or, does SQL Server have a utility that does this?
Thanks.www.red-gate.com
www.apexsql.com
Andrew J. Kelly SQL MVP
"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
news:E2C743BE-339D-477A-BF56-3C520F3FB611@.microsoft.com...
> I've written an Access application that uses SQL Server 2005 for the
> backend.
> I currently have about 10 clients using the application. When I have an
> update for my clients I send them a script containing all the updates for
> the
> server. The script contains ALTER, CREATE, etc procedures.
> Unfortunately, one package had some problems and subsequently did not
> append
> or process the entire script. The problem is, I don't know what has
> failed,
> and therefore I don't know what procedures, views or tables may be
> incorrect.
> My server is 100% accurate.
> Is there a utility on the market that would compare two servers and report
> what is missing or not the same in a report. Or better yet, one that
> creates
> a script to bring them in snyc with each other. This would help me solve
> this
> problem plus I would then not have to keep track of every change I make to
> the server as I'm working.
> Or, does SQL Server have a utility that does this?
> Thanks.|||Hi,
I understand that one of your packages failed to execute and you would like
to know if there is a tool to compare two servers and report what is
missing or not the same in a report.
If I have misunderstood, please let me know.
I do not know if there is a third party tool for your concern, however I
think that you can try using SQL Profiler to monitor your SQL Server 2005
and look at the trace logs for manually checking the differences.
You may refer to:
Introducing SQL Server Profiler
http://msdn2.microsoft.com/en-us/library/ms181091.aspx
Using SQL Server Profiler
http://msdn2.microsoft.com/en-us/library/ms187929.aspx
Please feel free to let me know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Also look at xsql.
http://www.xsqlsoftware.com/Main.aspx
--
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
"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
news:E2C743BE-339D-477A-BF56-3C520F3FB611@.microsoft.com...
> I've written an Access application that uses SQL Server 2005 for the
> backend.
> I currently have about 10 clients using the application. When I have an
> update for my clients I send them a script containing all the updates for
> the
> server. The script contains ALTER, CREATE, etc procedures.
> Unfortunately, one package had some problems and subsequently did not
> append
> or process the entire script. The problem is, I don't know what has
> failed,
> and therefore I don't know what procedures, views or tables may be
> incorrect.
> My server is 100% accurate.
> Is there a utility on the market that would compare two servers and report
> what is missing or not the same in a report. Or better yet, one that
> creates
> a script to bring them in snyc with each other. This would help me solve
> this
> problem plus I would then not have to keep track of every change I make to
> the server as I'm working.
> Or, does SQL Server have a utility that does this?
> Thanks.|||On Jul 27, 7:56 pm, Greg <AccessVBA...@.newsgroups.nospam> wrote:
> I've written an Access application that usesSQLServer2005 for the backend.
> I currently have about 10 clients using the application. When I have an
> update for my clients I send them a script containing all the updates for theserver. The script contains ALTER, CREATE, etc procedures.
> Unfortunately, one package had some problems and subsequently did not append
> or process the entire script. The problem is, I don't know what has failed,
> and therefore I don't know what procedures, views or tables may be incorrect.
> Myserveris 100% accurate.
> Is there a utility on the market that wouldcomparetwo servers and report
> what is missing or not the same in a report. Or better yet, one that creates
> a script to bring them in snyc with each other. This would help me solve this
> problem plus I would then not have to keep track of every change I make to
> theserveras I'm working.
> Or, doesSQLServerhave a utility that does this?
> Thanks.
Hi Greg,
check out xSQL Software's products, xSQL Object and xSQL Data Compare.
Those two utilities are completely free for SQL Server Express and
there is also a free lite edition that supports any SQL Server
Edition. You can download the products from http://www.xsqlsoftware.com
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com|||xSQL Compare comes in a Lite edition that works without restrictions if you
are using SQL Server Express. I downloaded the full professional product and
tested it against my two databases and it reported all the differences
between the two of them. And, on top of that it automatially generated teh
scripts to update the differences as well.
This is an excellent product and meets my needs 100%.
"Hilary Cotter" wrote:
> Also look at xsql.
> http://www.xsqlsoftware.com/Main.aspx
> --
> 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
> "Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
> news:E2C743BE-339D-477A-BF56-3C520F3FB611@.microsoft.com...
> > I've written an Access application that uses SQL Server 2005 for the
> > backend.
> > I currently have about 10 clients using the application. When I have an
> > update for my clients I send them a script containing all the updates for
> > the
> > server. The script contains ALTER, CREATE, etc procedures.
> >
> > Unfortunately, one package had some problems and subsequently did not
> > append
> > or process the entire script. The problem is, I don't know what has
> > failed,
> > and therefore I don't know what procedures, views or tables may be
> > incorrect.
> > My server is 100% accurate.
> >
> > Is there a utility on the market that would compare two servers and report
> > what is missing or not the same in a report. Or better yet, one that
> > creates
> > a script to bring them in snyc with each other. This would help me solve
> > this
> > problem plus I would then not have to keep track of every change I make to
> > the server as I'm working.
> >
> > Or, does SQL Server have a utility that does this?
> >
> > Thanks.
>
>|||www.dbconstructor.com
Robert Allen Schambach
"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
news:E2C743BE-339D-477A-BF56-3C520F3FB611@.microsoft.com...
> I've written an Access application that uses SQL Server 2005 for the
> backend.
> I currently have about 10 clients using the application. When I have an
> update for my clients I send them a script containing all the updates for
> the
> server. The script contains ALTER, CREATE, etc procedures.
> Unfortunately, one package had some problems and subsequently did not
> append
> or process the entire script. The problem is, I don't know what has
> failed,
> and therefore I don't know what procedures, views or tables may be
> incorrect.
> My server is 100% accurate.
> Is there a utility on the market that would compare two servers and report
> what is missing or not the same in a report. Or better yet, one that
> creates
> a script to bring them in snyc with each other. This would help me solve
> this
> problem plus I would then not have to keep track of every change I make to
> the server as I'm working.
> Or, does SQL Server have a utility that does this?
> Thanks.sqlsql

Comparing SQL Databases

I've written an Access application that uses SQL Server 2005 for the backend
.
I currently have about 10 clients using the application. When I have an
update for my clients I send them a script containing all the updates for th
e
server. The script contains ALTER, CREATE, etc procedures.
Unfortunately, one package had some problems and subsequently did not append
or process the entire script. The problem is, I don't know what has failed,
and therefore I don't know what procedures, views or tables may be incorrect
.
My server is 100% accurate.
Is there a utility on the market that would compare two servers and report
what is missing or not the same in a report. Or better yet, one that creates
a script to bring them in snyc with each other. This would help me solve thi
s
problem plus I would then not have to keep track of every change I make to
the server as I'm working.
Or, does SQL Server have a utility that does this?
Thanks.www.red-gate.com
www.apexsql.com
Andrew J. Kelly SQL MVP
"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
news:E2C743BE-339D-477A-BF56-3C520F3FB611@.microsoft.com...
> I've written an Access application that uses SQL Server 2005 for the
> backend.
> I currently have about 10 clients using the application. When I have an
> update for my clients I send them a script containing all the updates for
> the
> server. The script contains ALTER, CREATE, etc procedures.
> Unfortunately, one package had some problems and subsequently did not
> append
> or process the entire script. The problem is, I don't know what has
> failed,
> and therefore I don't know what procedures, views or tables may be
> incorrect.
> My server is 100% accurate.
> Is there a utility on the market that would compare two servers and report
> what is missing or not the same in a report. Or better yet, one that
> creates
> a script to bring them in snyc with each other. This would help me solve
> this
> problem plus I would then not have to keep track of every change I make to
> the server as I'm working.
> Or, does SQL Server have a utility that does this?
> Thanks.|||Hi,
I understand that one of your packages failed to execute and you would like
to know if there is a tool to compare two servers and report what is
missing or not the same in a report.
If I have misunderstood, please let me know.
I do not know if there is a third party tool for your concern, however I
think that you can try using SQL Profiler to monitor your SQL Server 2005
and look at the trace logs for manually checking the differences.
You may refer to:
Introducing SQL Server Profiler
http://msdn2.microsoft.com/en-us/library/ms181091.aspx
Using SQL Server Profiler
http://msdn2.microsoft.com/en-us/library/ms187929.aspx
Please feel free to let me know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Also look at xsql.
http://www.xsqlsoftware.com/Main.aspx
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
"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
news:E2C743BE-339D-477A-BF56-3C520F3FB611@.microsoft.com...
> I've written an Access application that uses SQL Server 2005 for the
> backend.
> I currently have about 10 clients using the application. When I have an
> update for my clients I send them a script containing all the updates for
> the
> server. The script contains ALTER, CREATE, etc procedures.
> Unfortunately, one package had some problems and subsequently did not
> append
> or process the entire script. The problem is, I don't know what has
> failed,
> and therefore I don't know what procedures, views or tables may be
> incorrect.
> My server is 100% accurate.
> Is there a utility on the market that would compare two servers and report
> what is missing or not the same in a report. Or better yet, one that
> creates
> a script to bring them in snyc with each other. This would help me solve
> this
> problem plus I would then not have to keep track of every change I make to
> the server as I'm working.
> Or, does SQL Server have a utility that does this?
> Thanks.|||On Jul 27, 7:56 pm, Greg <AccessVBA...@.newsgroups.nospam> wrote:
> I've written an Access application that usesSQLServer2005 for the backend.
> I currently have about 10 clients using the application. When I have an
> update for my clients I send them a script containing all the updates for
theserver. The script contains ALTER, CREATE, etc procedures.
> Unfortunately, one package had some problems and subsequently did not appe
nd
> or process the entire script. The problem is, I don't know what has failed
,
> and therefore I don't know what procedures, views or tables may be incorre
ct.
> Myserveris 100% accurate.
> Is there a utility on the market that wouldcomparetwo servers and report
> what is missing or not the same in a report. Or better yet, one that creat
es
> a script to bring them in snyc with each other. This would help me solve t
his
> problem plus I would then not have to keep track of every change I make to
> theserveras I'm working.
> Or, doesSQLServerhave a utility that does this?
> Thanks.
Hi Greg,
check out xSQL Software's products, xSQL Object and xSQL Data Compare.
Those two utilities are completely free for SQL Server Express and
there is also a free lite edition that supports any SQL Server
Edition. You can download the products from http://www.xsqlsoftware.com
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com|||xSQL Compare comes in a Lite edition that works without restrictions if you
are using SQL Server Express. I downloaded the full professional product and
tested it against my two databases and it reported all the differences
between the two of them. And, on top of that it automatially generated teh
scripts to update the differences as well.
This is an excellent product and meets my needs 100%.
"Hilary Cotter" wrote:

> Also look at xsql.
> http://www.xsqlsoftware.com/Main.aspx
> --
> 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
> "Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
> news:E2C743BE-339D-477A-BF56-3C520F3FB611@.microsoft.com...
>
>|||www.dbconstructor.com
Robert Allen Schambach
"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
news:E2C743BE-339D-477A-BF56-3C520F3FB611@.microsoft.com...
> I've written an Access application that uses SQL Server 2005 for the
> backend.
> I currently have about 10 clients using the application. When I have an
> update for my clients I send them a script containing all the updates for
> the
> server. The script contains ALTER, CREATE, etc procedures.
> Unfortunately, one package had some problems and subsequently did not
> append
> or process the entire script. The problem is, I don't know what has
> failed,
> and therefore I don't know what procedures, views or tables may be
> incorrect.
> My server is 100% accurate.
> Is there a utility on the market that would compare two servers and report
> what is missing or not the same in a report. Or better yet, one that
> creates
> a script to bring them in snyc with each other. This would help me solve
> this
> problem plus I would then not have to keep track of every change I make to
> the server as I'm working.
> Or, does SQL Server have a utility that does this?
> Thanks.

Comparing Reporting Programs

I am developing an ASP.Net 2.0 application that will make heavy use of reports. The VisualStudio.Net 2005 Professional edition contains two reporting programs -- Crystal Reports and SQL Server 2005 Reporting Services. Can anyone refer me to a white paper, technical article, book chapter, blog, etc. that compares the strengths and weaknesses of these two programs? Thanks for any suggestions.

Ken McLean

In my opinion SSRS, though far from perfect beats Crystal Reports. I will look up some references for you.

|||

Have a look at:

SQL Server Reporting Services 2005 XML extensions http://www.codeproject.com/KB/reporting-services/SSRS2005XML.aspx|||

Thanks a lot. That's exactly what I was looking for.

Ken McLean

Comparing MSDE to Access - Import Wizard?

I need to rewrite an Access 2000 application to use MSDE and .NET. The one
feature I'm worried about is the ability to import tables and/or data from
Access, Excel, txt, even Outlook.
The Access Import Wizard is used a lot here and the app in question has a
lot of VBA code that works with imported tables for getting data into the
database. Does MSDE have an Import Wizard like Access? Will I have to code
all this functionality from scratch?
Thanks in advance.
hi,
deko wrote:
> I need to rewrite an Access 2000 application to use MSDE and .NET. The one
> feature I'm worried about is the ability to import tables
> and/or data from Access, Excel, txt, even Outlook.
> The Access Import Wizard is used a lot here and the app in question
> has a lot of VBA code that works with imported tables for getting
> data into the database. Does MSDE have an Import Wizard like Access?
> Will I have to code all this functionality from scratch?
> Thanks in advance.
MSDE comes with no tools but oSql.exe, BCP.exe and the DTS runtime... you
can perhaps modify your import schema to sue such tools... or, youn can even
rely on Access based database as linked server to use the JET OLE DB
provider to a access dbf, txt, excel, mdb .... and the like...
http://tinyurl.com/9vdrz
http://tinyurl.com/anvbn
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Comparing all fields of two rows in few tables.

The problem scenario is this:
I have an application which uses few tables.
user can post data and can submit the form multiple times.
each posting is saved as each version.
Now that I have all data, I need to find the difference between 2
versions of the saved data
and report that he has modified these fields in the current submission
to the older.
what is the best way to compare all the fields(except for Primary key)
of 2 rows in a table.
I am thinking of doing this -
select
case when oldversion.Field1 <> newversion.Field1 then 'changed' as
Field1 end,
case when oldversion.Field2 <> newversion.Field2 then 'changed' as
Field2 end
from
(select * from Table1 where TablePKField = oldPKID ) oldversion
inner join
(select * from Table1 where TablePKField = NewPKID ) newversion
where newversion.commonField = newversion.CommonField
Please suggest me the best way of doing this without much performance
loss...
Thanks for your time.
G.Gees
if (select checksum_agg(checksum(*)) from t1)
<> (select checksum_agg(checksum(*)) from t2)
print 'different'
else
print 'probably the same'
"Gees" <gayathri.s@.gmail.com> wrote in message
news:1141032918.046365.85170@.j33g2000cwa.googlegroups.com...
> The problem scenario is this:
> I have an application which uses few tables.
> user can post data and can submit the form multiple times.
> each posting is saved as each version.
> Now that I have all data, I need to find the difference between 2
> versions of the saved data
> and report that he has modified these fields in the current submission
> to the older.
> what is the best way to compare all the fields(except for Primary key)
> of 2 rows in a table.
> I am thinking of doing this -
> select
> case when oldversion.Field1 <> newversion.Field1 then 'changed' as
> Field1 end,
> case when oldversion.Field2 <> newversion.Field2 then 'changed' as
> Field2 end
> from
> (select * from Table1 where TablePKField = oldPKID ) oldversion
> inner join
> (select * from Table1 where TablePKField = NewPKID ) newversion
> where newversion.commonField = newversion.CommonField
> Please suggest me the best way of doing this without much performance
> loss...
> Thanks for your time.
> G.
>|||Thanks Uri Dimant.
My problem also includes, quering those fields where the data is
changed and show only the changes.
something like In Table1 ,
Field1- Field 2 - Field3
Row1 A - B - C
Row2 A - X - Y
I need to show that,
>From Row1 to Row2
Values of Field2 , B to X
and Values of Field3 C to Y
are the changes.
any help ?
Thanks again!
G
Uri Dimant wrote:
> Gees
> if (select checksum_agg(checksum(*)) from t1)
> <> (select checksum_agg(checksum(*)) from t2)
> print 'different'
> else
> print 'probably the same'
>
>
> "Gees" <gayathri.s@.gmail.com> wrote in message
> news:1141032918.046365.85170@.j33g2000cwa.googlegroups.com...|||Gees
CREATE TABLE [dbo].Audit (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [varchar] (50) NOT NULL ,
[Col2] [int] NOT NULL ,
[Col3] [varchar] (255) NOT NULL ,
[Col4] [int] NOT NULL
) ON [PRIMARY]
And it has the following records:
INSERT INTO Audit VALUES ('Andy', 3, 'Oxford', 21)
INSERT INTO Audit VALUES ('Andy', 4, 'Oxford', 21)
INSERT INTO Audit VALUES ('Andy', 4, 'Cambridge', 21)
INSERT INTO Audit VALUES ('Andy', 6, 'Cambridge', 29)
INSERT INTO Audit VALUES ('Andy', 4, 'Manchester', 21)
ID ChangedColumn NewVal
2 Col2 4
3 Col3 Cambridge
4 Col2 6
4 Col4 29
5 Col3 Manchester
select a2.id,'col2' as colchng, cast(a2.col2 as varchar(255)) as newvalue
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col2<>a2.col2
union all
select a2.id,'col3', a2.col3
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col3<>a2.col3
union all
select a2.id,'col4', cast(a2.col4 as varchar(255))
from audit a1 join audit a2 on a1.id=a2.id-1
where a1.col4<>a2.col4
order by 1,2
"Gees" <gayathri.s@.gmail.com> wrote in message
news:1141042195.525248.161020@.i39g2000cwa.googlegroups.com...
> Thanks Uri Dimant.
> My problem also includes, quering those fields where the data is
> changed and show only the changes.
> something like In Table1 ,
> Field1- Field 2 - Field3
> Row1 A - B - C
> Row2 A - X - Y
> I need to show that,
> Values of Field2 , B to X
> and Values of Field3 C to Y
> are the changes.
> any help ?
> Thanks again!
> G
> Uri Dimant wrote:
>
>|||Hi Uri Dimant,
Thanks for the quick and nice reply.
Thats very useful.
Thanks a ton!
Best,
G

Compare data in Tables

I am trying to determine the changes an application makes to a database.
The plan is to copy the existing schema (active) to a reference schema, run
the application and then diff the table data between the reference and the
a active schema. I have found one software vendor who has a tool to do
this, but it will only do one table at a time (interactively); I have more
then 300 and will run this a few times.

One other way of determining the changes, I guess, would be to log all sql
statements (in order), but I don't know how to do this (either).

Any pointers would be greatly appreciated.

LeoIf its changes you are looking for try running SQL Profiler against it.
Filter for where writes > 0. Another solution would to write a script to
doing the all tables comparison. Something like

Create a table with tablename, checksumbefore, checksumafter, rowsbefore,
rowsafter, numberofrowsdiff
Write a cursor of all user tables
For each table
Get count of rows with select count(*)
calc the CHECKSUM of each row and write to individual temp tables
select count(*) from checksumafter where checksum not in checksumbefore
insert/update the table

By the end of the script you should have indentified which tables change and
by how much.

"Leo" <leolist@.optushome.com.au> wrote in message
news:Xns9639579AFB3Bleolistoptushomecoma@.211.29.13 3.50...
>I am trying to determine the changes an application makes to a database.
> The plan is to copy the existing schema (active) to a reference schema,
> run
> the application and then diff the table data between the reference and the
> a active schema. I have found one software vendor who has a tool to do
> this, but it will only do one table at a time (interactively); I have more
> then 300 and will run this a few times.
> One other way of determining the changes, I guess, would be to log all sql
> statements (in order), but I don't know how to do this (either).
> Any pointers would be greatly appreciated.
> Leo|||There is a software tool that can do this for you called DB Ghost
(www.dbghost.com). Its very fast at comparing data and can be run from
the command line for a fully automated process. A single command will
do any number of tables that you desire.

It's also the cornerstone of a full change management solution for SQL
Server databases i.e. it can build, compare and synchronize the schema
AND data directly from drop/create scripts held in a source control
system.

I highly recommend you check it out.|||"Malcolm" <malcolm.leach@.innovartis.co.uk> wrote in
news:1113551521.457015.133450@.l41g2000cwc.googlegr oups.com:

> DB Ghost

DB Ghost did exactly what I needed.

Thanks for your advice

Leo

compare

I am new SQL
I have XML files (jobs) that are generated from a third party app on a
weekly basis.
My SQL application loads the the xml files for production.

>From time to time changes will be made to the original xml files, which
requires a user to reload the job via the app.
However, the reporting process on these changes is somewhat anticquated
and sometimes these changes are missed and the job is not reloaded.
I am looking for a process to compare specfic dates values in the database
against either the file date on the xml files or the date that is
contained in the xml file and generate a report when the date is
in the DB is less then the file date
Any help is greatly appreciated.
Thank you
If you are using SQL Server 2005, look into the XQuery support that should
give you the comparison semantics...
Best regards
Michael
"steve" <steve@.discussions.microsoft.com> wrote in message
news:19713BEF-C2BD-46B8-A518-47656A51BA2C@.microsoft.com...
>I am new SQL
> I have XML files (jobs) that are generated from a third party app on a
> weekly basis.
> My SQL application loads the the xml files for production.
> requires a user to reload the job via the app.
> However, the reporting process on these changes is somewhat anticquated
> and sometimes these changes are missed and the job is not reloaded.
> I am looking for a process to compare specfic dates values in the database
> against either the file date on the xml files or the date that is
> contained in the xml file and generate a report when the date is
> in the DB is less then the file date
> Any help is greatly appreciated.
> Thank you
>

compare

I am new SQL
I have XML files (jobs) that are generated from a third party app on a
wly basis.
My SQL application loads the the xml files for production.

>From time to time changes will be made to the original xml files, which
requires a user to reload the job via the app.
However, the reporting process on these changes is somewhat anticquated
and sometimes these changes are missed and the job is not reloaded.
I am looking for a process to compare specfic dates values in the database
against either the file date on the xml files or the date that is
contained in the xml file and generate a report when the date is
in the DB is less then the file date
Any help is greatly appreciated.
Thank youIf you are using SQL Server 2005, look into the XQuery support that should
give you the comparison semantics...
Best regards
Michael
"steve" <steve@.discussions.microsoft.com> wrote in message
news:19713BEF-C2BD-46B8-A518-47656A51BA2C@.microsoft.com...
>I am new SQL
> I have XML files (jobs) that are generated from a third party app on a
> wly basis.
> My SQL application loads the the xml files for production.
>
> requires a user to reload the job via the app.
> However, the reporting process on these changes is somewhat anticquated
> and sometimes these changes are missed and the job is not reloaded.
> I am looking for a process to compare specfic dates values in the database
> against either the file date on the xml files or the date that is
> contained in the xml file and generate a report when the date is
> in the DB is less then the file date
> Any help is greatly appreciated.
> Thank you
>

Compact Sql Mobile 3.0 DataBase in C++/OLE DB ?

Hello,

I'm porting my C++ (EVC3) application from Sql CE 2.0 to Sql Mobile 3.0

(with OLE DB). To compact a 2.0 database, i used ISSCECompact interface.

But it doesn't exists with the new version. I've only found C#

and VB.Net samples in MSDN ...

Thanks in advance.

Have you had a chance to look at: http://msdn2.microsoft.com/en-us/library/ms174034(en-US,SQL.90).aspx

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

#include "ca_merge30.h"
#include <stdio.h>

void ErrorPrint() ;

#define VerifyResult(x) hr=x;\
if(FAILED(hr)) { \
printf("Failed : HR : %x\n",hr); \
printf("at : %d \n",__LINE__); \
ErrorPrint(); \
goto Exit; \
}
#define RELEASE(x) if(NULL!=x)x->Release();

extern "C" int __cdecl wmain(){

HRESULT hr = NOERROR;
ISSCEEngine *pISSCEEngine = NULL;
VerifyResult(CoInitialize(NULL));
VerifyResult(CoCreateInstance(CLSID_Engine, NULL, CLSCTX_INPROC_SERVER,
IID_ISSCEEngine, (void**)&pISSCEEngine));

WCHAR *wszSrc=L"Data Source = src.sdf";
WCHAR *wszDst=L"Data Source = dst.sdf";
VerifyResult(pISSCEEngine->CompactDatabase(wszSrc, wszDst));

Exit:
RELEASE(pISSCEEngine);
CoUninitialize();
return hr;
}

void ErrorPrint()
{
LPVOID lpMsgBuf;
DWORD dw = GetLastError();

FormatMessage(
FORMAT_MESSAGE_ALLOCATE_BUFFER |
FORMAT_MESSAGE_FROM_SYSTEM,
NULL,
dw,
MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT),
(LPTSTR) &lpMsgBuf,
0, NULL );

printf( "Failed with error %d: %ls",
dw, lpMsgBuf);

LocalFree(lpMsgBuf);
}

Hope this helps! Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

(Sample By: BalaDutt, MSFT, SQL Mobile, Microsoft Corporation)

|||Many thanks ! It's exactly what i needed !|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

This helped me as well, thanks. But I also cannot find any reference material for how to perform a "Verify" DB as advertised in the Documentation for SQL Server Mobile Edition.

The docs have a sample in C#, but nothing for OLD DB in C++.

Does anyone know how to do this?

Compact Sql Mobile 3.0 DataBase in C++/OLE DB ?

Hello,

I'm porting my C++ (EVC3) application from Sql CE 2.0 to Sql Mobile 3.0 (with OLE DB). To compact a 2.0 database, i used ISSCECompact interface. But it doesn't exists with the new version. I've only found C# and VB.Net samples in MSDN ...

Thanks in advance.

Have you had a chance to look at: http://msdn2.microsoft.com/en-us/library/ms174034(en-US,SQL.90).aspx

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

#include "ca_merge30.h"
#include <stdio.h>

void ErrorPrint() ;

#define VerifyResult(x) hr=x;\
if(FAILED(hr)) { \
printf("Failed : HR : %x\n",hr); \
printf("at : %d \n",__LINE__); \
ErrorPrint(); \
goto Exit; \
}
#define RELEASE(x) if(NULL!=x)x->Release();

extern "C" int __cdecl wmain(){

HRESULT hr = NOERROR;
ISSCEEngine *pISSCEEngine = NULL;
VerifyResult(CoInitialize(NULL));
VerifyResult(CoCreateInstance(CLSID_Engine, NULL, CLSCTX_INPROC_SERVER,
IID_ISSCEEngine, (void**)&pISSCEEngine));

WCHAR *wszSrc=L"Data Source = src.sdf";
WCHAR *wszDst=L"Data Source = dst.sdf";
VerifyResult(pISSCEEngine->CompactDatabase(wszSrc, wszDst));

Exit:
RELEASE(pISSCEEngine);
CoUninitialize();
return hr;
}

void ErrorPrint()
{
LPVOID lpMsgBuf;
DWORD dw = GetLastError();

FormatMessage(
FORMAT_MESSAGE_ALLOCATE_BUFFER |
FORMAT_MESSAGE_FROM_SYSTEM,
NULL,
dw,
MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT),
(LPTSTR) &lpMsgBuf,
0, NULL );

printf( "Failed with error %d: %ls",
dw, lpMsgBuf);

LocalFree(lpMsgBuf);
}

Hope this helps! Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

(Sample By: BalaDutt, MSFT, SQL Mobile, Microsoft Corporation)

|||Many thanks ! It's exactly what i needed !
|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

This helped me as well, thanks. But I also cannot find any reference material for how to perform a "Verify" DB as advertised in the Documentation for SQL Server Mobile Edition.

The docs have a sample in C#, but nothing for OLD DB in C++.

Does anyone know how to do this?

Compact Sql Mobile 3.0 DataBase in C++/OLE DB ?

Hello,

I'm porting my C++ (EVC3) application from Sql CE 2.0 to Sql Mobile 3.0 (with OLE DB). To compact a 2.0 database, i used ISSCECompact interface. But it doesn't exists with the new version. I've only found C# and VB.Net samples in MSDN ...

Thanks in advance.

Have you had a chance to look at: http://msdn2.microsoft.com/en-us/library/ms174034(en-US,SQL.90).aspx

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

#include "ca_merge30.h"
#include <stdio.h>

void ErrorPrint() ;

#define VerifyResult(x) hr=x;\
if(FAILED(hr)) { \
printf("Failed : HR : %x\n",hr); \
printf("at : %d \n",__LINE__); \
ErrorPrint(); \
goto Exit; \
}
#define RELEASE(x) if(NULL!=x)x->Release();

extern "C" int __cdecl wmain(){

HRESULT hr = NOERROR;
ISSCEEngine *pISSCEEngine = NULL;
VerifyResult(CoInitialize(NULL));
VerifyResult(CoCreateInstance(CLSID_Engine, NULL, CLSCTX_INPROC_SERVER,
IID_ISSCEEngine, (void**)&pISSCEEngine));

WCHAR *wszSrc=L"Data Source = src.sdf";
WCHAR *wszDst=L"Data Source = dst.sdf";
VerifyResult(pISSCEEngine->CompactDatabase(wszSrc, wszDst));

Exit:
RELEASE(pISSCEEngine);
CoUninitialize();
return hr;
}

void ErrorPrint()
{
LPVOID lpMsgBuf;
DWORD dw = GetLastError();

FormatMessage(
FORMAT_MESSAGE_ALLOCATE_BUFFER |
FORMAT_MESSAGE_FROM_SYSTEM,
NULL,
dw,
MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT),
(LPTSTR) &lpMsgBuf,
0, NULL );

printf( "Failed with error %d: %ls",
dw, lpMsgBuf);

LocalFree(lpMsgBuf);
}

Hope this helps! Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

(Sample By: BalaDutt, MSFT, SQL Mobile, Microsoft Corporation)

|||Many thanks ! It's exactly what i needed !
|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

This helped me as well, thanks. But I also cannot find any reference material for how to perform a "Verify" DB as advertised in the Documentation for SQL Server Mobile Edition.

The docs have a sample in C#, but nothing for OLD DB in C++.

Does anyone know how to do this?

Compact Edition DVD Standalone

Is it possible to run SQL Server Compact Edition as a standalone application from a DVD?

i.e. it would not leave anything installed on the users computer after the DVD was ejected.

Hi:

For what it's worth, I am attempting the same thing: developing a product catalog that will enable users to browse products and configure order for printing. As my product is using Framework 2, I will simply have an autorun install the program since I have to check for the existence of the framework anyway.

Please post back your findings.

|||If both .Net CF2 and SQL Server 2005 are installed on the target system, you should be able to deploy the DLLs for SQL CE on the DVD.|||I do not believe there is a dependancy on SQL Server/.NET CF2 for a runtime SQL Server CE application - believe that there is only a dependency on VB6 runtime/.Net 1/1.1 runtime of .Net2.0 runtime (according the language the app has been written in. Plus of course the SQL CE Dlls on the DVD|||Make connection string like this:

"Data Source=".\MyDatabase.sdf";Password="MyPassword";mode="Read Only";SSCE:Temp File Directory = "C:\";"

This will use temporary database on c: disk if there's need to write temp data for queries.

On DVD/CD include these files in root:
Microsoft.SqlServerCe.Client.dll
sqlceca30.dll
sqlcecompact30.dll
sqlceer30EN.dll
sqlceme30.dll
sqlceoledb30.dll
sqlceqp30.dll
sqlcese30.dll
System.Data.SqlServerCe.dll

Now, this works for me, but I have requirement, not to write to C:, but to users temp folder.
Does somebody know how to change connection string to write into temp folder?

Communication Link Failure?

Hai,

Iam using VC++ application to connect remote database(MSSQL) through network.

Everything works fine, if the network connection is active. If the network breaks and resumes, the application throws "Communication Link Failure" and do not get connected even after the network is resumed.

Client :

* Windows 2000 Professional

* MDAC 2.5

* VC++, using framework- CRecordset to access database

* PEER to PEER network configuration

* DSN through ODBC

Server :

* Windows 2000 Professional

* MDAC 2.8

* MSSQL Server

how to resume the database connection ?

Any one help me?

Thanks in advance,

If an existing connection fails due to broken network connectivity the client application must open a new connection to the server.

Does the new connection fail as well? If so, what is the full error message text?

|||

No..if the application is restarted the connection resumes..

Do i have to make a new connection after the broken network connectivity? Is there any other solution without reconnecting?

|||

Yes, you have to make a new connection after the broken network connectivity.

|||Thank you..

Communication link failure...

Hello,
We are the authors of an application written in PowerBuilder (type
Client-Server, database MSSQLServer2000).
After some time (1 hour) of client inactivity happens disconnect from server
and aplication reports error 'Communication link failure ...'

Please, would you help me, if exists some way how to set up an interval of
the connectivity between Client and Server. I wasnt able to find it out in
any help or manual.

Thank you very much in advance!
PavelOn Fri, 02 Apr 2004 16:31:41 +0200, Pavel Vetesnik wrote:

> Hello,
> We are the authors of an application written in PowerBuilder (type
> Client-Server, database MSSQLServer2000). After some time (1 hour) of
> client inactivity happens disconnect from server and aplication reports
> error 'Communication link failure ...'
> Please, would you help me, if exists some way how to set up an interval of
> the connectivity between Client and Server. I wasnt able to find it out
> in any help or manual.
> Thank you very much in advance!
> Pavel

You should add code to your application to capture the login dialogue
and log back on to the server in the event the server has dropped the
session and the SAME client session wishes to communicate with the server.

I suggest this because the timeout value used by the server could change
from release to release or be changed by the network/system administrators
if resource usage gets too high.

Communication Link Failure Error

Hi,

I am using ADO in my application to connect and retrieve data from Microsoft SQL Server 2000 SP4. When I start my application, it establishes connection to the server successfully and I am able to retrieve data from the server. here is my test scenario:

1. unplug the network cable from the client machine and put the cable back in after some time. I disconnect from the database in my code when the client machine disconnects from the network

2. Once the client machine comes back on the network, I try to reconnect and retrieve data from the sql server but I get the following error message when I try to execute a sql command:

[Microsoft][ODBC SQL Server Driver]Communication Link failure

This problem occurs only with Window XP SP2. With XP SP1 it works fine. It seems to be problem on the client side as with Windows XP SP1 I do not get this error message at all.

Here are the various details:

1. The firewall is off on the client workstation.

2. the connection string is:

driver={SQL Server};server=AA;uid=BB;pwd=CC;database=DD

where AA = DNS name of server machine where sql server is running.

BB = user name

CC = password

DD= database name

3. The client is remote to the server.

4. I can ping the server machine when the client machine reconnects to the network.

5. I can still telnet the server when the client machine reconnects to the network.

6. client database provider is MDAC odbc.

7. the client and server machines are in the same domain.

8. Shared memory and TCPIP protocols are enabled and TCP is at the top of the list.

9. No alias is being used.

10. Viris scan is not installed.

11. force encriptioned is NOT checked.

Please reply.

It appears a connection pooling issue. The closed connections by the client app are returned to the pool and become invalid after you unplug the cable. Next time you try to connect, odbc reuse the invalid connectin and throw the error. Retry your connection or disable pooling should resolve your issue.|||

I am already doing this:

1. When the machine disconnects from the network, I close the database connection. when the machine reconnects to the network, I try to establish a new connection to the database.

2. The connection pooling is also disabled for SQL server odbc driver under Connection Pooling tab of ODBC Data Source Administrator.

Please reply.

|||

Good day,

Tahir posted this request in Oct this year, has there been any response yet?

We are experiencing exactely the same problem in completely different ODBC client and are desperately looking for a way around this.

regards

|||

I eventually managed to find a way around it. Here are the changes which I made to my code:

1. Changed the connection steing to use native OLE DB as follows:

Provider=sqloledb;Data Source=AAA;Initial Catalog=BBB;User Id=CCC;Password=DDD

where AAA = SQL Server name

BBB = database name

CCC = login user name

DDD= password

2. I declare a list which holds pending SQL request.

3. hook up ADO connection object's Connectioncomplete and Disconnection events.

4. when execution of a SQL query failes, an exception is raisedcatch this exception. check the list of pending requests. if it is empty then add this failed request into the list and disconnect from the database. (this will force to disconnect from an invalid connection if there is any). Do not attempt to disconnect from the database if there is already a pending request in the list.

5. I have a separate thread which attempts to connect to the database. once I have got connection to the database, i suspend that thread.

6. in disconnection event handler, I check if there is an error while I tried to disconnect from the database. If there is NO error then I resume the thread which attempts to connect to the database.

7. in connection event handler, I check if there is an error while I tried to connect to the database. If there is NO error then I start a timer which checks pending request list and if there is any pending request, send it to the SQL server and then empty the list.

Regards,

Tahir Sultan

|||

Tahir thanks for the response.

We still have a problem because we can only use ODBC?

regards

Amanda

|||

Can catch the error and retry the connection be one of your option?

Otherwise, please post your code here and we can try to repro it.

HTH

|||

At the moment we are diconneting and then re-connecting, but even this is not solving the problem. We need to stop the application to get rid of the error. This is only the case for the communication link failure error with all other errors diconnecting and re-connecting works fine.

regards

|||

I had the same problem when I was using ODBC. Even disconnecting and reconnecting did not work at all. I tried all possible ODBC driver configuration but none of them seemed to make any difference. But moving to native OLE DB driver solved the problem.

Tahir Sultan

|||We have a situation common to laptops. I'm convinced that it is the result of some power management setting (hibernate, hard drive shutdown, or standby), but can never log it to pinpoint it exactly. Any suggestions?

Communication Link Failure Error

Hi,

I am using ADO in my application to connect and retrieve data from Microsoft SQL Server 2000 SP4. When I start my application, it establishes connection to the server successfully and I am able to retrieve data from the server. here is my test scenario:

1. unplug the network cable from the client machine and put the cable back in after some time. I disconnect from the database in my code when the client machine disconnects from the network

2. Once the client machine comes back on the network, I try to reconnect and retrieve data from the sql server but I get the following error message when I try to execute a sql command:

[Microsoft][ODBC SQL Server Driver]Communication Link failure

This problem occurs only with Window XP SP2. With XP SP1 it works fine. It seems to be problem on the client side as with Windows XP SP1 I do not get this error message at all.

Here are the various details:

1. The firewall is off on the client workstation.

2. the connection string is:

driver={SQL Server};server=AA;uid=BB;pwd=CC;database=DD

where AA = DNS name of server machine where sql server is running.

BB = user name

CC = password

DD= database name

3. The client is remote to the server.

4. I can ping the server machine when the client machine reconnects to the network.

5. I can still telnet the server when the client machine reconnects to the network.

6. client database provider is MDAC odbc.

7. the client and server machines are in the same domain.

8. Shared memory and TCPIP protocols are enabled and TCP is at the top of the list.

9. No alias is being used.

10. Viris scan is not installed.

11. force encriptioned is NOT checked.

Please reply.

It appears a connection pooling issue. The closed connections by the client app are returned to the pool and become invalid after you unplug the cable. Next time you try to connect, odbc reuse the invalid connectin and throw the error. Retry your connection or disable pooling should resolve your issue.|||

I am already doing this:

1. When the machine disconnects from the network, I close the database connection. when the machine reconnects to the network, I try to establish a new connection to the database.

2. The connection pooling is also disabled for SQL server odbc driver under Connection Pooling tab of ODBC Data Source Administrator.

Please reply.

|||

Good day,

Tahir posted this request in Oct this year, has there been any response yet?

We are experiencing exactely the same problem in completely different ODBC client and are desperately looking for a way around this.

regards

|||

I eventually managed to find a way around it. Here are the changes which I made to my code:

1. Changed the connection steing to use native OLE DB as follows:

Provider=sqloledb;Data Source=AAA;Initial Catalog=BBB;User Id=CCC;Password=DDD

where AAA = SQL Server name

BBB = database name

CCC = login user name

DDD= password

2. I declare a list which holds pending SQL request.

3. hook up ADO connection object's Connectioncomplete and Disconnection events.

4. when execution of a SQL query failes, an exception is raisedcatch this exception. check the list of pending requests. if it is empty then add this failed request into the list and disconnect from the database. (this will force to disconnect from an invalid connection if there is any). Do not attempt to disconnect from the database if there is already a pending request in the list.

5. I have a separate thread which attempts to connect to the database. once I have got connection to the database, i suspend that thread.

6. in disconnection event handler, I check if there is an error while I tried to disconnect from the database. If there is NO error then I resume the thread which attempts to connect to the database.

7. in connection event handler, I check if there is an error while I tried to connect to the database. If there is NO error then I start a timer which checks pending request list and if there is any pending request, send it to the SQL server and then empty the list.

Regards,

Tahir Sultan

|||

Tahir thanks for the response.

We still have a problem because we can only use ODBC?

regards

Amanda

|||

Can catch the error and retry the connection be one of your option?

Otherwise, please post your code here and we can try to repro it.

HTH

|||

At the moment we are diconneting and then re-connecting, but even this is not solving the problem. We need to stop the application to get rid of the error. This is only the case for the communication link failure error with all other errors diconnecting and re-connecting works fine.

regards

|||

I had the same problem when I was using ODBC. Even disconnecting and reconnecting did not work at all. I tried all possible ODBC driver configuration but none of them seemed to make any difference. But moving to native OLE DB driver solved the problem.

Tahir Sultan

|||We have a situation common to laptops. I'm convinced that it is the result of some power management setting (hibernate, hard drive shutdown, or standby), but can never log it to pinpoint it exactly. Any suggestions?

Communication Link Failure Error

Hi,

I am using ADO in my application to connect and retrieve data from Microsoft SQL Server 2000 SP4. When I start my application, it establishes connection to the server successfully and I am able to retrieve data from the server. here is my test scenario:

1. unplug the network cable from the client machine and put the cable back in after some time. I disconnect from the database in my code when the client machine disconnects from the network

2. Once the client machine comes back on the network, I try to reconnect and retrieve data from the sql server but I get the following error message when I try to execute a sql command:

[Microsoft][ODBC SQL Server Driver]Communication Link failure

This problem occurs only with Window XP SP2. With XP SP1 it works fine. It seems to be problem on the client side as with Windows XP SP1 I do not get this error message at all.

Here are the various details:

1. The firewall is off on the client workstation.

2. the connection string is:

driver={SQL Server};server=AA;uid=BB;pwd=CC;database=DD

where AA = DNS name of server machine where sql server is running.

BB = user name

CC = password

DD= database name

3. The client is remote to the server.

4. I can ping the server machine when the client machine reconnects to the network.

5. I can still telnet the server when the client machine reconnects to the network.

6. client database provider is MDAC odbc.

7. the client and server machines are in the same domain.

8. Shared memory and TCPIP protocols are enabled and TCP is at the top of the list.

9. No alias is being used.

10. Viris scan is not installed.

11. force encriptioned is NOT checked.

Please reply.

It appears a connection pooling issue. The closed connections by the client app are returned to the pool and become invalid after you unplug the cable. Next time you try to connect, odbc reuse the invalid connectin and throw the error. Retry your connection or disable pooling should resolve your issue.|||

I am already doing this:

1. When the machine disconnects from the network, I close the database connection. when the machine reconnects to the network, I try to establish a new connection to the database.

2. The connection pooling is also disabled for SQL server odbc driver under Connection Pooling tab of ODBC Data Source Administrator.

Please reply.

|||

Good day,

Tahir posted this request in Oct this year, has there been any response yet?

We are experiencing exactely the same problem in completely different ODBC client and are desperately looking for a way around this.

regards

|||

I eventually managed to find a way around it. Here are the changes which I made to my code:

1. Changed the connection steing to use native OLE DB as follows:

Provider=sqloledb;Data Source=AAA;Initial Catalog=BBB;User Id=CCC;Password=DDD

where AAA = SQL Server name

BBB = database name

CCC = login user name

DDD= password

2. I declare a list which holds pending SQL request.

3. hook up ADO connection object's Connectioncomplete and Disconnection events.

4. when execution of a SQL query failes, an exception is raisedcatch this exception. check the list of pending requests. if it is empty then add this failed request into the list and disconnect from the database. (this will force to disconnect from an invalid connection if there is any). Do not attempt to disconnect from the database if there is already a pending request in the list.

5. I have a separate thread which attempts to connect to the database. once I have got connection to the database, i suspend that thread.

6. in disconnection event handler, I check if there is an error while I tried to disconnect from the database. If there is NO error then I resume the thread which attempts to connect to the database.

7. in connection event handler, I check if there is an error while I tried to connect to the database. If there is NO error then I start a timer which checks pending request list and if there is any pending request, send it to the SQL server and then empty the list.

Regards,

Tahir Sultan

|||

Tahir thanks for the response.

We still have a problem because we can only use ODBC?

regards

Amanda

|||

Can catch the error and retry the connection be one of your option?

Otherwise, please post your code here and we can try to repro it.

HTH

|||

At the moment we are diconneting and then re-connecting, but even this is not solving the problem. We need to stop the application to get rid of the error. This is only the case for the communication link failure error with all other errors diconnecting and re-connecting works fine.

regards

|||

I had the same problem when I was using ODBC. Even disconnecting and reconnecting did not work at all. I tried all possible ODBC driver configuration but none of them seemed to make any difference. But moving to native OLE DB driver solved the problem.

Tahir Sultan

|||We have a situation common to laptops. I'm convinced that it is the result of some power management setting (hibernate, hard drive shutdown, or standby), but can never log it to pinpoint it exactly. Any suggestions?

Communication Link failure

Hello,
Sometimes my application accessing SQL Server via ODBC is giving an error message "Communication link failure".
The network and server looks fine, but I'am still getting it time to time.
Please recommend possible reasons and solutions.
Thanks indvance.We has some unnoticed unstable connections very difficult to find because NT retried to connect without warning (maybe just some lattency when you tried to access again a lan resource).
We checked with "ping -w5000 -t"
The problem was bad coupling male/female RJ45 connectors in some stations.

Hope this helps,
Csar.

Communication Link Failure

Hello everyone,
We have upgraded our database server from SQL Server
7.0/sp4 to SQL Server 2000/sp2. Since then the client
application accessing the database has consistent 08S01
errors with "Communication Link Failure" messages.
There is a pop up dialog box that provides instant
information as part of the application. Most of the time
those 08S01 problems start occurring when this window
pops up. By default, SQL Server 2000 set 'Query Timeout'
to 600. I have changed it to 0 (or unlimited) just to
avoid timing out of queries (just in case the query is
kept open in the dialog box). I found from users that the
errors have reduced (to some extent) but they are not
eliminated.
Has someone experienced such problems in the past? Your
comments and help is welcome.
Thanks in advance,
mzeeshanThe latest SQL2k Service Pack is 3a, start by applying this. It could be a
problem on the client rather than the server, ensure the clients have the
current version of MDAC installed.
--
HTH
Ryan Waight, MCDBA, MCSE
"mzeeshan" <mzeeshan@.yahoo.com> wrote in message
news:0f1301c38a83$0ac83e30$a401280a@.phx.gbl...
> Hello everyone,
> We have upgraded our database server from SQL Server
> 7.0/sp4 to SQL Server 2000/sp2. Since then the client
> application accessing the database has consistent 08S01
> errors with "Communication Link Failure" messages.
> There is a pop up dialog box that provides instant
> information as part of the application. Most of the time
> those 08S01 problems start occurring when this window
> pops up. By default, SQL Server 2000 set 'Query Timeout'
> to 600. I have changed it to 0 (or unlimited) just to
> avoid timing out of queries (just in case the query is
> kept open in the dialog box). I found from users that the
> errors have reduced (to some extent) but they are not
> eliminated.
> Has someone experienced such problems in the past? Your
> comments and help is welcome.
> Thanks in advance,
> mzeeshan