Comparing to Resultsets

I have a situation where I have two tables, one in Access and one in SQL
Server. There are several hundred thousand rows and the tables should
contain the exact same number of rows. They may not though and the count
could be off by one row or a hundred rows. I need to be able to identify
which rows are not in both resultsets so i can update the second table with
the missing rows.
My problem is that I have built a VB app that SELECT * FROM table and then I
query the second table using the current results from table 1... Select *
From table where companyName = 'value from table1'
This works but it has taken 5 hours so far and isn't even half way done.
The IDs in both table can be (and are) different in both tables so i don't
really have a solid way to compare records on a record by record basis. To
make matters worse Access and SQL server appear to have different sort
engines so when I sort the records by a column, the fields are not in
exactly the same order.
What options do i have to compare these two tables and identify the rows
that are different'
Thanks,
RonYou'd have to post DDL to help us help you.
You don't really need a single common column to compare result sets, they
must, however, contain comparable data.
ML
http://milambda.blogspot.com/

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 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

Compare like data

Hello all,

I am new to sql and have some Access experience.

In sql, how do I: compare 2 identical tables, (except for data); then update
table 1 with new data from table 2

TIA

JakeGitarJake (gitarjake@.spammersuntied.com) writes:
> I am new to sql and have some Access experience.
> In sql, how do I: compare 2 identical tables, (except for data); then
> update table 1 with new data from table 2

To find all rows that are different, assuming that the key is keycol:

SELECT *
FROM a
FULL JOIN b ON a.keycol = b.keycol
WHERE a.keycol IS NULL OR
b.keycol IS NULL OR
(a.col1 <> b.col1 OR a.col1 IS NOT NULL AND b.col1 IS NULL OR
a.col1 IS NULL AND b.col1 IS NOT NULL) OR
(a.col2 <> b.col2 OR a.col2 IS NOT NULL AND b.col2 IS NULL OR
a.col2 IS NULL AND b.col2 IS NOT NULL) OR
...

For columns that does not permit NULL, you can skip the checks for NULL.

To update:

UPDATE a
SET col1 = b.col1,
col2 = b.col2,
..
FROM a
JOIN b ON a.keycol = b.keycol
WHERE (a.col1 <> b.col1 OR a.col1 IS NOT NULL AND b.col1 IS NULL OR
a.col1 IS NULL AND b.col1 IS NOT NULL) OR
(a.col2 <> b.col2 OR a.col2 IS NOT NULL AND b.col2 IS NULL OR
a.col2 IS NULL AND b.col2 IS NOT NULL) OR

DELETE a
WHERE NOT EXISTS (SELECT *
FROM b
WHERE b.keycol = a.keycol)

INSERT a (keycol, col1, col2, ...)
SELECT keycol, col1, col2, ...)
FROM b
WHERE NOT EXISTS (SELECT * FROM a WHERE a.keycol = b.keycol)

You can take some shortcuts here. The simplest way is to say "DELETE a"
and then insert all from b. The long where condition on the UPDATE
statement can be excluded, you only update a few extra rows with the
values they already have.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

compare Data in 2 databases?

Hi All
I am relatively new to coding so please bare with me...
I have a number of SQL 2005 databases I access using an ODBC connection.
What I would like to do is create a code of some kind that will look at the
Client List in one db <<server1>> and compare them to like fields in another
<<server2>>. I would like this to then be a View that I can connect to with
Crystal and build a report around.
This is probably brain dead stuff to you but I am not finding what I need in
the NG's, but I am probably not calling it the correct term.
TIA
ApexSQL has a Diff product that can compare both data and structures. I
believe you can output the results of a compare for use elsewhere.
TheSQLGuru
President
Indicium Resources, Inc.
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:0E38DB96-8599-4021-A7BA-7C325B273F2A@.microsoft.com...
> Hi All
> I am relatively new to coding so please bare with me...
> I have a number of SQL 2005 databases I access using an ODBC connection.
> What I would like to do is create a code of some kind that will look at
> the
> Client List in one db <<server1>> and compare them to like fields in
> another
> <<server2>>. I would like this to then be a View that I can connect to
> with
> Crystal and build a report around.
> This is probably brain dead stuff to you but I am not finding what I need
> in
> the NG's, but I am probably not calling it the correct term.
> TIA
>
|||There is not statement that I can write that will do this? I am in a
non-profit and cannot buy all of these add-ins.
Thanks
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:0E38DB96-8599-4021-A7BA-7C325B273F2A@.microsoft.com...
> Hi All
> I am relatively new to coding so please bare with me...
> I have a number of SQL 2005 databases I access using an ODBC connection.
> What I would like to do is create a code of some kind that will look at
> the
> Client List in one db <<server1>> and compare them to like fields in
> another
> <<server2>>. I would like this to then be a View that I can connect to
> with
> Crystal and build a report around.
> This is probably brain dead stuff to you but I am not finding what I need
> in
> the NG's, but I am probably not calling it the correct term.
> TIA
>
|||John,
If your database is not too big, the free Lite Edition:
http://www.xsqlsoftware.com/LiteEdition.aspx
Advertised as free for SQLExpress:
http://www.teratrax.com/tdc/?gclid=CLGX5YD4-o0CFUtyOAodnRSlOw
Red Gate tools also come with a 14-day trial as do doubtless others.
RLF
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:368D850F-0CC0-4919-8D99-BE1936935556@.microsoft.com...
> There is not statement that I can write that will do this? I am in a
> non-profit and cannot buy all of these add-ins.
> Thanks
> "JOHN HARRIS" <harris1113@.fake.com> wrote in message
> news:0E38DB96-8599-4021-A7BA-7C325B273F2A@.microsoft.com...
>

compare Data in 2 databases?

Hi All
I am relatively new to coding so please bare with me...
I have a number of SQL 2005 databases I access using an ODBC connection.
What I would like to do is create a code of some kind that will look at the
Client List in one db <<server1>> and compare them to like fields in another
<<server2>>. I would like this to then be a View that I can connect to with
Crystal and build a report around.
This is probably brain dead stuff to you but I am not finding what I need in
the NG's, but I am probably not calling it the correct term.
TIAApexSQL has a Diff product that can compare both data and structures. I
believe you can output the results of a compare for use elsewhere.
--
TheSQLGuru
President
Indicium Resources, Inc.
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:0E38DB96-8599-4021-A7BA-7C325B273F2A@.microsoft.com...
> Hi All
> I am relatively new to coding so please bare with me...
> I have a number of SQL 2005 databases I access using an ODBC connection.
> What I would like to do is create a code of some kind that will look at
> the
> Client List in one db <<server1>> and compare them to like fields in
> another
> <<server2>>. I would like this to then be a View that I can connect to
> with
> Crystal and build a report around.
> This is probably brain dead stuff to you but I am not finding what I need
> in
> the NG's, but I am probably not calling it the correct term.
> TIA
>|||There is not statement that I can write that will do this? I am in a
non-profit and cannot buy all of these add-ins.
Thanks
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:0E38DB96-8599-4021-A7BA-7C325B273F2A@.microsoft.com...
> Hi All
> I am relatively new to coding so please bare with me...
> I have a number of SQL 2005 databases I access using an ODBC connection.
> What I would like to do is create a code of some kind that will look at
> the
> Client List in one db <<server1>> and compare them to like fields in
> another
> <<server2>>. I would like this to then be a View that I can connect to
> with
> Crystal and build a report around.
> This is probably brain dead stuff to you but I am not finding what I need
> in
> the NG's, but I am probably not calling it the correct term.
> TIA
>|||John,
If your database is not too big, the free Lite Edition:
http://www.xsqlsoftware.com/LiteEdition.aspx
Advertised as free for SQLExpress:
http://www.teratrax.com/tdc/?gclid=CLGX5YD4-o0CFUtyOAodnRSlOw
Red Gate tools also come with a 14-day trial as do doubtless others.
RLF
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:368D850F-0CC0-4919-8D99-BE1936935556@.microsoft.com...
> There is not statement that I can write that will do this? I am in a
> non-profit and cannot buy all of these add-ins.
> Thanks
> "JOHN HARRIS" <harris1113@.fake.com> wrote in message
> news:0E38DB96-8599-4021-A7BA-7C325B273F2A@.microsoft.com...
>> Hi All
>> I am relatively new to coding so please bare with me...
>> I have a number of SQL 2005 databases I access using an ODBC connection.
>> What I would like to do is create a code of some kind that will look at
>> the
>> Client List in one db <<server1>> and compare them to like fields in
>> another
>> <<server2>>. I would like this to then be a View that I can connect to
>> with
>> Crystal and build a report around.
>> This is probably brain dead stuff to you but I am not finding what I need
>> in
>> the NG's, but I am probably not calling it the correct term.
>> TIA
>

compare Data in 2 databases?

Hi All
I am relatively new to coding so please bare with me...
I have a number of SQL 2005 databases I access using an ODBC connection.
What I would like to do is create a code of some kind that will look at the
Client List in one db <<server1>> and compare them to like fields in another
<<server2>>. I would like this to then be a View that I can connect to with
Crystal and build a report around.
This is probably brain dead stuff to you but I am not finding what I need in
the NG's, but I am probably not calling it the correct term.
TIAApexSQL has a Diff product that can compare both data and structures. I
believe you can output the results of a compare for use elsewhere.
TheSQLGuru
President
Indicium Resources, Inc.
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:0E38DB96-8599-4021-A7BA-7C325B273F2A@.microsoft.com...
> Hi All
> I am relatively new to coding so please bare with me...
> I have a number of SQL 2005 databases I access using an ODBC connection.
> What I would like to do is create a code of some kind that will look at
> the
> Client List in one db <<server1>> and compare them to like fields in
> another
> <<server2>>. I would like this to then be a View that I can connect to
> with
> Crystal and build a report around.
> This is probably brain dead stuff to you but I am not finding what I need
> in
> the NG's, but I am probably not calling it the correct term.
> TIA
>|||There is not statement that I can write that will do this? I am in a
non-profit and cannot buy all of these add-ins.
Thanks
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:0E38DB96-8599-4021-A7BA-7C325B273F2A@.microsoft.com...
> Hi All
> I am relatively new to coding so please bare with me...
> I have a number of SQL 2005 databases I access using an ODBC connection.
> What I would like to do is create a code of some kind that will look at
> the
> Client List in one db <<server1>> and compare them to like fields in
> another
> <<server2>>. I would like this to then be a View that I can connect to
> with
> Crystal and build a report around.
> This is probably brain dead stuff to you but I am not finding what I need
> in
> the NG's, but I am probably not calling it the correct term.
> TIA
>|||John,
If your database is not too big, the free Lite Edition:
http://www.xsqlsoftware.com/LiteEdition.aspx
Advertised as free for SQLExpress:
http://www.teratrax.com/tdc/?gclid=...CFUtyOAodnRSlOw
Red Gate tools also come with a 14-day trial as do doubtless others.
RLF
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:368D850F-0CC0-4919-8D99-BE1936935556@.microsoft.com...
> There is not statement that I can write that will do this? I am in a
> non-profit and cannot buy all of these add-ins.
> Thanks
> "JOHN HARRIS" <harris1113@.fake.com> wrote in message
> news:0E38DB96-8599-4021-A7BA-7C325B273F2A@.microsoft.com...
>

Company logo in Report Manager - Top Frame

We are trying to work out if it is possible to access the source code for the
Report Manager interface, so that we can configure the design.
For example, we would like to put the company logo in as a watermark in the
space at the top section of Report Manager (behind 'SQL Server Reporting
Services Home')
We have been able to set the background colour etc using the style sheet,
but would like to know if it is possible to have greater control over the
look and feel at the base level.Yes you can. The images for instance can be changed... I have forgotten the
details, but they are discussed in The Hitchikers Guide to Reporting
Services... The issue is that you must place your images in a RS directory,
and change some RS files. This puts you in a position where you might have
to re-do this work at each Service Pack or upgrade...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:17084FC0-0C20-41B4-A7EC-9CA49D3A0F99@.microsoft.com...
> We are trying to work out if it is possible to access the source code for
> the
> Report Manager interface, so that we can configure the design.
> For example, we would like to put the company logo in as a watermark in
> the
> space at the top section of Report Manager (behind 'SQL Server Reporting
> Services Home')
> We have been able to set the background colour etc using the style sheet,
> but would like to know if it is possible to have greater control over the
> look and feel at the base level.|||Thanks Wayne, much appreciated.
Darren
"Wayne Snyder" wrote:
> Yes you can. The images for instance can be changed... I have forgotten the
> details, but they are discussed in The Hitchikers Guide to Reporting
> Services... The issue is that you must place your images in a RS directory,
> and change some RS files. This puts you in a position where you might have
> to re-do this work at each Service Pack or upgrade...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:17084FC0-0C20-41B4-A7EC-9CA49D3A0F99@.microsoft.com...
> > We are trying to work out if it is possible to access the source code for
> > the
> > Report Manager interface, so that we can configure the design.
> >
> > For example, we would like to put the company logo in as a watermark in
> > the
> > space at the top section of Report Manager (behind 'SQL Server Reporting
> > Services Home')
> >
> > We have been able to set the background colour etc using the style sheet,
> > but would like to know if it is possible to have greater control over the
> > look and feel at the base level.
>
>|||This doesnt work in 2005 Report Manager.
The Report Manager in 2005 is little differern from 2000. Im not able to do
the customization as explined in Hitchikers Guide. any help'
have you tried any customization for Report Manager in 2005.
any feedback appricated..
Thanks
Bava
"Wayne Snyder" wrote:
> Yes you can. The images for instance can be changed... I have forgotten the
> details, but they are discussed in The Hitchikers Guide to Reporting
> Services... The issue is that you must place your images in a RS directory,
> and change some RS files. This puts you in a position where you might have
> to re-do this work at each Service Pack or upgrade...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:17084FC0-0C20-41B4-A7EC-9CA49D3A0F99@.microsoft.com...
> > We are trying to work out if it is possible to access the source code for
> > the
> > Report Manager interface, so that we can configure the design.
> >
> > For example, we would like to put the company logo in as a watermark in
> > the
> > space at the top section of Report Manager (behind 'SQL Server Reporting
> > Services Home')
> >
> > We have been able to set the background colour etc using the style sheet,
> > but would like to know if it is possible to have greater control over the
> > look and feel at the base level.
>
>

compacting / to repair base of data sql, using the msde

I am developing a project of the access for a friend, more I am with the
following doubts:
Does some exist it sorts things out of compacting / to repair base of data
sql, using the msde or the adp.?
MSDE databases do not require compacting. When updates or other changes are
made, the space freed when a row is deleted is reused automatically. For the
most part, MSDE/SQL Server databases are not subject to the file corruption
problems that are associated (from time-to-time ) with JET databases. There
are utilities (DBCC for one) that can be used to perform maintenance on the
database, but it's unusual to have to resort to these for smaller databases
as typically implemented with MSDE.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:%23ZUa0OKMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> I am developing a project of the access for a friend, more I am with the
> following doubts:
> Does some exist it sorts things out of compacting / to repair base of data
> sql, using the msde or the adp.?
>

Compact and Repair in an adp

Hi,

I just deleted about 1 million records from a SQL Server 2000 database. I
access it using Access 2000.

I have Compact and Repair available in the adp under Tools. Should I do that
from Access? Will it not mess anything up?

Thanks!

J~JA wrote:

Quote:

Originally Posted by

I just deleted about 1 million records from a SQL Server 2000 database. I
access it using Access 2000.
>
I have Compact and Repair available in the adp under Tools. Should I do that
from Access? Will it not mess anything up?

No idea. You can definitely use Query Analyzer to run a
DBCC SHRINKDATABASE command.

If you expect the database to grow back to its previous size and
stay there, then you shouldn't shrink it. If you expect it to
stay at its reduced size, then you should.|||JA (jarmour@.kc.rr.com) writes:

Quote:

Originally Posted by

I just deleted about 1 million records from a SQL Server 2000 database. I
access it using Access 2000.
>
I have Compact and Repair available in the adp under Tools. Should I do
that from Access? Will it not mess anything up?

To me that sounds like tools for the local Access database.

In any case, one million rows is not that much, unless the rows are
extremely wide.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

compact and repair database

I'm an Access man moving towards SQL Server and I'm interested in knowing if there is a utility for my SQL Server version 7 database that is similar to Access' Compact and Repair utility? I'd like a recommendation from a guru on what Prevenatative Maintenance measures I should be running daily/weekly/monthly etc...

Thanks in advance.
dogSQL Server used to need a lot of "care and feeding" on a regular basis. In version 2000, it comes with maintenance plan wizards that will provide a wizard interface to create and schedule maintenance jobs that handle 99% of the needs of any database, and all of the needs of 99% of the databases that I've seen.

After you run up your box and have Windoze and SQL running happily, you should visit the maintenance wizard soon.

At your convenience, you should read up on the whole suite of DBCC commands in SQL Books Online. You'll probably know which ones you need long before you need them, if you ever need them!

As an observation, you write like you are fairly comfortable with programming, databases, etc. If so, you probably want to find a place like this to post questions, since you'll get answers that make sense to you relatively quickly from folks that have done all of this a time or two!

-PatP|||SQL Server used to need a lot of "care and feeding" on a regular basis. In version 2000, it comes with maintenance plan wizards that will provide a wizard interface to create and schedule maintenance jobs that handle 99% of the needs of any database, and all of the needs of 99% of the databases that I've seen.

A note about the maintenance plan wizard: I've had a maintenance plan created for about 2 months now but I don't think its doing anything. I've been in to look at the settings 3 or 4 times now and all looks well but I notice that its not backing up to the directory I specified and I suspect its not doing any of the integrity and reindexing procedures either. After defining it, is there another step I have to take to get the maintenance plan running? I'm finishing up development and users are starting to input data, so its moving to the front burner.|||? Did you schedule it? That's an area you have to fill out in there. Also, look at your jobs in Enterprise Manager. When you create a Maintenance plan, it creates a job to run the on a scheduled basis. Make sure the jobs are enable. Then, look at the properties of them to make sure they have a valid schedule.

Compact & Repair

How do you repair and get rid of dead files for this
server versus Access database.
> How do you repair and get rid of dead files for this
> server versus Access database.
In SQL Server you can use DBCC SHRINKDATABASE and DBCC SHRINKFILE commands.
Is this what you are asking?
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com

compact

No one answered in another group, please help.

Using access 2000 /msde
I know how to compact the access project file (.adp), there's a menu choice
for that.
How do I compact the tables and stored procedures on the server? ( The .MDF
file)
I only have the DOS promp OSQL utility to work with.
thanksHi

There is not a compact command as such in SQL Server. If you want to shrink
the log file check out

Log file filling up
http://www.support.microsoft.com/?id=110139

Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512

http://www.mssqlserver.com/faq/logs-shrinklog.asp

Log File Grows too big
http://www.support.microsoft.com/?id=317375

For log file shrinking etc.. check out :

http://msdn.microsoft.com/library/d...ar_da2_1uzr.asp

and the following KB articles:

INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650

INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318

Books online will give you all the T-SQL commands

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

or online: such as for DBCC
http://msdn.microsoft.com/library/d...asp?frame=true

You should also look at defragmenting the indexes using either DBCC
DBREINDEX or DBCC INDEXDEFRAG

If you want to read more about SQL Server internals then check out
"Inside SQL Server 2000" by Kalen Delany ISBN 0-7356-0998-5

John

"JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
news:mZsTc.208334$OB3.24877@.bgtnsc05-news.ops.worldnet.att.net...
> No one answered in another group, please help.
> Using access 2000 /msde
> I know how to compact the access project file (.adp), there's a menu
choice
> for that.
> How do I compact the tables and stored procedures on the server? ( The
..MDF
> file)
> I only have the DOS promp OSQL utility to work with.
> thanks

Communications link failure #0

We are intermittently getting this error on our SQL server. The front end is
access and this happens every so often causing the users to have to exit out
of access and reconnect. I'm by no means a SQL expert, and our programmer
who manages this database isn't sure what it is either, he says it's like
the server is just "forgetting his password" and dumping the ODBC
connection...
Any ideas/help would be appreciated.
Thanks!
ScottHi
You don't give what version of MDAC or SQL Server that you are using!
http://www.aspfaq.com/show.asp?id=2160
http://msdn.microsoft.com/data/mdac/default.aspx
Searching Google for "Communications link failure" turns up quite a few
things, it seemed to be quite common with SQL Server 6.5!
You may want to check that you don't have connection pooling.
Try logging the ODBC commands (on the client) and using Profiler on the
server to pin point if there is one particular set of circumstances when it
occurs.
Check your SQL Server logs and Event logs (on client and server) to see if
there in any information or event that corresponds to the timing of your
disconnections.
Use the MDAC component check to show the version and if you have a
consistent installation.
HTH
John
"Scott McDonald" wrote:
> We are intermittently getting this error on our SQL server. The front end is
> access and this happens every so often causing the users to have to exit out
> of access and reconnect. I'm by no means a SQL expert, and our programmer
> who manages this database isn't sure what it is either, he says it's like
> the server is just "forgetting his password" and dumping the ODBC
> connection...
> Any ideas/help would be appreciated.
> Thanks!
> Scott
>
>|||it's SQL 2000 on Windows 2003 and using Access 2003 - sorry . MDAC is
newest, which I think is x.7 or something...
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:2B4BF663-A19B-4E40-B44A-D9684B757E4B@.microsoft.com...
> Hi
> You don't give what version of MDAC or SQL Server that you are using!
> http://www.aspfaq.com/show.asp?id=2160
> http://msdn.microsoft.com/data/mdac/default.aspx
> Searching Google for "Communications link failure" turns up quite a few
> things, it seemed to be quite common with SQL Server 6.5!
> You may want to check that you don't have connection pooling.
> Try logging the ODBC commands (on the client) and using Profiler on the
> server to pin point if there is one particular set of circumstances when
> it
> occurs.
> Check your SQL Server logs and Event logs (on client and server) to see if
> there in any information or event that corresponds to the timing of your
> disconnections.
> Use the MDAC component check to show the version and if you have a
> consistent installation.
> HTH
> John
>
> "Scott McDonald" wrote:
>> We are intermittently getting this error on our SQL server. The front end
>> is
>> access and this happens every so often causing the users to have to exit
>> out
>> of access and reconnect. I'm by no means a SQL expert, and our programmer
>> who manages this database isn't sure what it is either, he says it's like
>> the server is just "forgetting his password" and dumping the ODBC
>> connection...
>> Any ideas/help would be appreciated.
>> Thanks!
>> Scott
>>|||Hi
You may also want to switch protocols and check out the network in
general for possible problems.
John
Scott McDonald wrote:
> it's SQL 2000 on Windows 2003 and using Access 2003 - sorry . MDAC is
> newest, which I think is x.7 or something...
>
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:2B4BF663-A19B-4E40-B44A-D9684B757E4B@.microsoft.com...
> > Hi
> >
> > You don't give what version of MDAC or SQL Server that you are
using!
> > http://www.aspfaq.com/show.asp?id=2160
> > http://msdn.microsoft.com/data/mdac/default.aspx
> >
> > Searching Google for "Communications link failure" turns up quite a
few
> > things, it seemed to be quite common with SQL Server 6.5!
> >
> > You may want to check that you don't have connection pooling.
> >
> > Try logging the ODBC commands (on the client) and using Profiler on
the
> > server to pin point if there is one particular set of circumstances
when
> > it
> > occurs.
> >
> > Check your SQL Server logs and Event logs (on client and server) to
see if
> > there in any information or event that corresponds to the timing of
your
> > disconnections.
> >
> > Use the MDAC component check to show the version and if you have a
> > consistent installation.
> >
> > HTH
> >
> > John
> >
> >
> > "Scott McDonald" wrote:
> >
> >> We are intermittently getting this error on our SQL server. The
front end
> >> is
> >> access and this happens every so often causing the users to have
to exit
> >> out
> >> of access and reconnect. I'm by no means a SQL expert, and our
programmer
> >> who manages this database isn't sure what it is either, he says
it's like
> >> the server is just "forgetting his password" and dumping the ODBC
> >> connection...
> >>
> >> Any ideas/help would be appreciated.
> >>
> >> Thanks!
> >>
> >> Scott
> >>
> >>
> >>

Communication link failure

Hello
We are using Access 2003 (SP2) to access a remote SQL 2000 database,
using ODBC with MDAC 2.8
This is an intermittent problem and it occurs on all the clients
(mostly winXP Pro)
The problem is that from time to time, the ODBC connection fails and we
get one of several errors:
ODBC--call failed
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (se
nd()).
(#10054) [Microsoft][ODBC SQL Server Driver][DBNETLIB].General n
etwork
error. Check your network documentation.
or
ODBC--call failed
[Microsoft][ODBC SQL Server Driver][Communications link failure
(0#)
We know that sometimes there are network problems (we connect accross a
CDMA mobile network) - the cause of this is not in question.....
Retrying the operation immediatly returns the same error but when we
close and re-open access and re-run the operation, and then it will
work straight away.
So, it seems that the driver enters a "failure state" - which is reset
when access is closed and re-opened.
I desperately need to find a way to "reset' the driver when this
happens so that we don't have to keep restarting access.
Anyway help would be much appreciated.
thanks !
RobHow you address it depends partly on how you are access the
SQL 2000 database, what this operation does that you are
retrying.
Just a guess but if this is linked tables, then you should
create some VBA function in Access to relink (not refresh)
the tables in code. Then you can try executing this function
instead.
-Sue
On 16 May 2006 03:17:49 -0700, saikosounds@.gmail.com wrote:

>Hello
>We are using Access 2003 (SP2) to access a remote SQL 2000 database,
>using ODBC with MDAC 2.8
>This is an intermittent problem and it occurs on all the clients
>(mostly winXP Pro)
>The problem is that from time to time, the ODBC connection fails and we
>get one of several errors:
>ODBC--call failed
>[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (s
end()).
>(#10054) [Microsoft][ODBC SQL Server Driver][DBNETLIB].General
network
>error. Check your network documentation.
>or
>ODBC--call failed
>[Microsoft][ODBC SQL Server Driver][Communications link failure
(0#)
>We know that sometimes there are network problems (we connect accross a
>CDMA mobile network) - the cause of this is not in question.....
>Retrying the operation immediatly returns the same error but when we
>close and re-open access and re-run the operation, and then it will
>work straight away.
>So, it seems that the driver enters a "failure state" - which is reset
>when access is closed and re-opened.
>I desperately need to find a way to "reset' the driver when this
>happens so that we don't have to keep restarting access.
>Anyway help would be much appreciated.
>thanks !
>Rob|||Yes, the tables are linked, but there are lots and lots of tables !
Relinking then through code takes longer than re-starting Access ! !|||Then try a refresh instead - I'm not sure if a restart does
this or not. The issue is more of one related to Access
linked tables so you may want to post this in an Access
newsgroup.
-Sue
On 16 May 2006 17:08:29 -0700, saikosounds@.gmail.com wrote:

>Yes, the tables are linked, but there are lots and lots of tables !
>Relinking then through code takes longer than re-starting Access ! !|||You either need to fix the network connection, relink the tables, or automat
e
a restart. In Access, you can actually reopen the database through VBA, if
I
remember correctly.
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Sue Hoegemeier" wrote:

> Then try a refresh instead - I'm not sure if a restart does
> this or not. The issue is more of one related to Access
> linked tables so you may want to post this in an Access
> newsgroup.
> -Sue
> On 16 May 2006 17:08:29 -0700, saikosounds@.gmail.com wrote:
>
>

Commons DBCP pooling problem

Hello,
I am using Commons DBCP to pool my sqlserver database connections.
Sometimes there is just an hourglass cursor when trying to access my
application - looks like there are no free connections available.
Am I doing anything wrong in my code? Should I be closing the
datasource, for example? Please see below.
Class for retrieving datasource:
package mypackage.name.here
import javax.sql.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;
import org.apache.commons.dbcp.*;
import org.apache.commons.pool.*;
import org.apache.commons.pool.impl.*;
public class DatabaseUtil {
static PoolingDataSource dataSource = null;
public static final String DB_PROPERTIES =
"myextension.properties";
// Returns a DataSource that implements connection pooling
public static synchronized DataSource getDataSource(String
configDir) throws Exception {
if (dataSource == null){
Properties p = new Properties();
try{
File cfg = new File(configDir, DB_PROPERTIES);
p.load(new FileInputStream(cfg));
} catch (IOException e) {
System.out.println(e);
}
String type = p.getProperty("dbType");
String url = p.getProperty("url");
String ip = p.getProperty("dbIP");
String name = p.getProperty("dbName");
String port = p.getProperty("dbPort");
String sid = p.getProperty("dbSid");
String fullUrl = null;
if (type.trim().equals("ms")){
// MSSQL
fullUrl = url+ip+"/"+name;
} else {
// ORACLE
fullUrl = url+ip+":"+port+":"+sid;
}
BasicDataSource ds = new BasicDataSource();
ds.setUrl(fullUrl);
ds.setDriverClassName(p.getProperty("driver"));
ds.setUsername(p.getProperty("usr"));
ds.setPassword(EncDoc.decode(p.getProperty("pass") ));
ds.setMaxActive(100);
// Create a PoolableDataSource
ObjectPool connectionPool = new GenericObjectPool(null);
ConnectionFactory connectionFactory = new
DataSourceConnectionFactory(ds);
PoolableObjectFactory poolableConnectionFactory = new
PoolableConnectionFactory(connectionFactory, connectionPool, null,
null, false, true);
dataSource = new PoolingDataSource(connectionPool);
}
return dataSource;
}
}
Sample JSP page:
...
DataSource dataSource = DatabaseUtil.getDataSource(configDir);
Connection myConnect = null;
Statement myStatement = null;
ResultSet myResults = null;
try{
myConnect = dataSource.getConnection();
myStatement = myConnect.createStatement();
...
myQuery = "select count (*) as 'total' from admin where courseID =
'"+courseID+"'";
myStatement.executeQuery(myQuery);
myResults = myStatement.getResultSet();
while (myResults.next()) count = myResults.getInt("total");
...
}catch (SQLException e) {
out.println("SQL Error: <br><br>"+e);
} finally {
try { myResults.close(); } catch(Exception e) {}
try { myStatement.close(); } catch(Exception e) { }
try { myConnect.close(); } catch(Exception e) { }
}
Thanks all.
downlode@.gmail.com wrote:

> Hello,
> I am using Commons DBCP to pool my sqlserver database connections.
> Sometimes there is just an hourglass cursor when trying to access my
> application - looks like there are no free connections available.
> Am I doing anything wrong in my code? Should I be closing the
> datasource, for example? Please see below.
Hi. The JSP code is excellent. Very safe and sure to close
the connection. I am not knowledgeable about that pool
implementation. I would suggest getting a full thread dump
of the JVM during that hang, and see what is waiting on what.
Can you find out how many JSPs are running concurrently
when such a hang is happening? If the pool implementation
ever calls DriverManager calls, it may be the cause. DriverManager
calls are all class-synchronized, and lots of JDBC drivers
make those calls under the covers, so any one long-running
DriverManager call (getConnection()) can halt all other JDBC
in the whole JVM...
Joe Weinstein at BEA Systems

> Class for retrieving datasource:
>
> package mypackage.name.here
> import javax.sql.*;
> import java.io.File;
> import java.io.FileInputStream;
> import java.io.IOException;
> import java.util.*;
> import org.apache.commons.dbcp.*;
> import org.apache.commons.pool.*;
> import org.apache.commons.pool.impl.*;
> public class DatabaseUtil {
> static PoolingDataSource dataSource = null;
> public static final String DB_PROPERTIES =
> "myextension.properties";
>
> // Returns a DataSource that implements connection pooling
> public static synchronized DataSource getDataSource(String
> configDir) throws Exception {
> if (dataSource == null){
> Properties p = new Properties();
> try{
> File cfg = new File(configDir, DB_PROPERTIES);
> p.load(new FileInputStream(cfg));
> } catch (IOException e) {
> System.out.println(e);
> }
> String type = p.getProperty("dbType");
> String url = p.getProperty("url");
> String ip = p.getProperty("dbIP");
> String name = p.getProperty("dbName");
> String port = p.getProperty("dbPort");
> String sid = p.getProperty("dbSid");
> String fullUrl = null;
> if (type.trim().equals("ms")){
> // MSSQL
> fullUrl = url+ip+"/"+name;
> } else {
> // ORACLE
> fullUrl = url+ip+":"+port+":"+sid;
> }
> BasicDataSource ds = new BasicDataSource();
> ds.setUrl(fullUrl);
> ds.setDriverClassName(p.getProperty("driver"));
> ds.setUsername(p.getProperty("usr"));
> ds.setPassword(EncDoc.decode(p.getProperty("pass") ));
> ds.setMaxActive(100);
> // Create a PoolableDataSource
> ObjectPool connectionPool = new GenericObjectPool(null);
> ConnectionFactory connectionFactory = new
> DataSourceConnectionFactory(ds);
> PoolableObjectFactory poolableConnectionFactory = new
> PoolableConnectionFactory(connectionFactory, connectionPool, null,
> null, false, true);
> dataSource = new PoolingDataSource(connectionPool);
> }
> return dataSource;
> }
> }
>
> Sample JSP page:
> ...
> DataSource dataSource = DatabaseUtil.getDataSource(configDir);
> Connection myConnect = null;
> Statement myStatement = null;
> ResultSet myResults = null;
> try{
> myConnect = dataSource.getConnection();
> myStatement = myConnect.createStatement();
> ...
> myQuery = "select count (*) as 'total' from admin where courseID =
> '"+courseID+"'";
> myStatement.executeQuery(myQuery);
> myResults = myStatement.getResultSet();
> while (myResults.next()) count = myResults.getInt("total");
> ...
> }catch (SQLException e) {
> out.println("SQL Error: <br><br>"+e);
> } finally {
> try { myResults.close(); } catch(Exception e) {}
> try { myStatement.close(); } catch(Exception e) { }
> try { myConnect.close(); } catch(Exception e) { }
> }
>
> Thanks all.
>
|||Joe Weinstein wrote:

>
> downlode@.gmail.com wrote:
>
> Hi. The JSP code is excellent. Very safe and sure to close
> the connection.
Actually, the one extra thing I might try, is to make the
connection and it's subobjects method-level objects. If the
JSP is typically one instantiated class, any multiple threads
executing it could overwrite the connection reference and
leak connections instead of having them all closed.
Joe

> I am not knowledgeable about that pool
> implementation. I would suggest getting a full thread dump
> of the JVM during that hang, and see what is waiting on what.
> Can you find out how many JSPs are running concurrently
> when such a hang is happening? If the pool implementation
> ever calls DriverManager calls, it may be the cause. DriverManager
> calls are all class-synchronized, and lots of JDBC drivers
> make those calls under the covers, so any one long-running
> DriverManager call (getConnection()) can halt all other JDBC
> in the whole JVM...
> Joe Weinstein at BEA Systems
>
|||Hi,
I'm not sure how I would go about making the connection a method-level
object in this scenario. Would I create a new class with a
getConnection() type method for creating and returning the connection?
Thanks,
Mike

> Actually, the one extra thing I might try, is to make the
> connection and it's subobjects method-level objects. If the
> JSP is typically one instantiated class, any multiple threads
> executing it could overwrite the connection reference and
> leak connections instead of having them all closed.
> Joe
|||downlode@.gmail.com wrote:
[vbcol=seagreen]
> Hi,
> I'm not sure how I would go about making the connection a method-level
> object in this scenario. Would I create a new class with a
> getConnection() type method for creating and returning the connection?
> Thanks,
> Mike
>
Hi. You could do that, or you could just make sure the scope of
the connection variable was within a block in the code, eg:
DataSource dataSource = DatabaseUtil.getDataSource(configDir);
try {
Connection myConnect = null; // ensure these have per-thread scope
Statement myStatement = null;
ResultSet myResults = null;
try{
myConnect = dataSource.getConnection();
myStatement = myConnect.createStatement();
...
myQuery = "select count (*) as 'total' from admin where courseID =
'"+courseID+"'";
myStatement.executeQuery(myQuery);
myResults = myStatement.getResultSet();
while (myResults.next()) count = myResults.getInt("total");
...
}catch (SQLException e) {
out.println("SQL Error: <br><br>"+e);
} finally {
try { myResults.close(); } catch(Exception e) {}
try { myStatement.close(); } catch(Exception e) { }
try { myConnect.close(); } catch(Exception e) { }
}
}
finally {}
|||OK, got you.
Thanks again.
Mike

Command line printing to specific printers, and specific trays

Hi All,

Could you guys please help me with printing reports invoked thru command line/ URL access to print automatically to specific printers and specific trays and also is it possible to set the specific printer and tray as parameters.

Any suggestions is appreciated

Thanks A lot in advance

e,g :

http://localhost/reportserver?/testreports/employee sales&UserID='ABC'&LName=Lastname='victor'&rs:Command=Render

There are no url parameters to make the report automatically print. You can print using the ActiveX control that ships with RS 2005 or you could look into using the printer delivery sample for printing your reports.