Is this possible? Monitor replication from VB 6.0

I have a client with approximately 30 laptops all running SQL Server
2000 (databse is about 13 gigabytes, this is _not_ MSDE). They have a
legacy application written in VB 6.0.
The decision has been made to change their replication model.
Replication between a "central" SQL Server database and the laptops
used to be triggered through the application whenever the laptop user
chose to. Now they are going to use a two-part method, so that the
user's logs are replicated whenever they connect to the central server,
and product data (which can be huge) will be scheduled in stages
overnight. The VB application will have no control over when they
replicate. The logs will use Merge replication and the product data
will use Transactional replication.
The problem is they expect the application to continue to monitor the
replication "progress" in both scenarios. I have gotten them to accept
that their progress bars will go away, that there will only be an item
in their status bars that it is running (or not).
I have figured out a way to monitor that Merge replication is going on
by watching the MSmerge_history and MSmerge_agents tables in the
Distribution database, but I am not sure that even this will continue
to work once the laptops stop using the ActiveX MS SQL Merge Control to
trigger replication, and it will not work at all for Transactional
replication.
Does anyone have a way (or have a suggestion that might lead to a way)
as to how I can tell from VB whether replication is in progress against
the local SQL Server database? If you believe this is impossible, I
would appreciate hearing that as well.
Thanks,
Austin
Have you looked at the status event?
Hilary Cotter
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
"AustinMN" <tacooper260@.hotmail.com> wrote in message
news:1126531606.616225.228610@.g14g2000cwa.googlegr oups.com...
> I have a client with approximately 30 laptops all running SQL Server
> 2000 (databse is about 13 gigabytes, this is _not_ MSDE). They have a
> legacy application written in VB 6.0.
> The decision has been made to change their replication model.
> Replication between a "central" SQL Server database and the laptops
> used to be triggered through the application whenever the laptop user
> chose to. Now they are going to use a two-part method, so that the
> user's logs are replicated whenever they connect to the central server,
> and product data (which can be huge) will be scheduled in stages
> overnight. The VB application will have no control over when they
> replicate. The logs will use Merge replication and the product data
> will use Transactional replication.
> The problem is they expect the application to continue to monitor the
> replication "progress" in both scenarios. I have gotten them to accept
> that their progress bars will go away, that there will only be an item
> in their status bars that it is running (or not).
> I have figured out a way to monitor that Merge replication is going on
> by watching the MSmerge_history and MSmerge_agents tables in the
> Distribution database, but I am not sure that even this will continue
> to work once the laptops stop using the ActiveX MS SQL Merge Control to
> trigger replication, and it will not work at all for Transactional
> replication.
> Does anyone have a way (or have a suggestion that might lead to a way)
> as to how I can tell from VB whether replication is in progress against
> the local SQL Server database? If you believe this is impossible, I
> would appreciate hearing that as well.
> Thanks,
> Austin
>
|||Hilary Cotter wrote
> Have you looked at the status event?
Yes, I have. The Status event applies to the ActiveX replication
components, whicb are being removed from the application. It only reports
on replication started by the components, so is useless for replication
initiated by the Distributor/Publisher database.
Austin
You programmed with 1s and 0s? We only had 0s!
There are no X characters in my address
> --
> Hilary Cotter
> 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
> "AustinMN" <tacooper260@.hotmail.com> wrote in message
> news:1126531606.616225.228610@.g14g2000cwa.googlegr oups.com...
>
|||"AustinMN" <tacooper260@.hotmail.com> wrote in message
news:1126531606.616225.228610@.g14g2000cwa.googlegr oups.com...
>I have a client with approximately 30 laptops all running SQL Server
> The problem is they expect the application to continue to monitor the
> replication "progress" in both scenarios. I have gotten them to accept
> that their progress bars will go away, that there will only be an item
> in their status bars that it is running (or not).
> I have figured out a way to monitor that Merge replication is going on
> by watching the MSmerge_history and MSmerge_agents tables in the
> Distribution database, but I am not sure that even this will continue
> to work once the laptops stop using the ActiveX MS SQL Merge Control to
> trigger replication, and it will not work at all for Transactional
> replication.
> Does anyone have a way (or have a suggestion that might lead to a way)
> as to how I can tell from VB whether replication is in progress against
> the local SQL Server database? If you believe this is impossible, I
> would appreciate hearing that as well.
>
My experience is with Oracle. With Oracle Enterprise Manager, you can see a list
of current connections (which I do fairly often), and you can see lists or logs
of recent changes (which I don't do much). It seems like there would have to be
something similar in SQL Server. Activity at the level of replication can't be
completely hidden. It is just a matter of finding out which thing to look
for...Maybe you could even detect the running process on the laptop with an API
call?

Is this a BUG

SQL Server 2005 SP2.
The following SQL works on SQL Server 7.0 but it doesn't work on SQL Server
2000 or 2005. The first column (cust_code) displays correct in all the
versions but the second column (related_info2 - That has all the concatenated
stuff) displays different. In 7.0, it brings all the values and if the column
has NULL, it doesn't bring anything for THAT PARTICULAR COLUMN but brings out
the rest of the columns data.
In SQL 2000 and 2005 it brings NULL for everything if one of the columns has
NULL value.
Is this a bug or it was a bug that was fixed in SQL 2000 and 2005 ?
select customer.cust_code,
customer.bus_name
+ ' '
+ customer.address3
+ ' Direct: '
+ customer.phone_direct
+ ' 800: '
+ customer.phone_800
related_info2
from customer
order by customer.cust_code
and here is the DDL:
CREATE TABLE [dbo].[customer] (
[cust_code] [int] NOT NULL ,
[bus_name] [varchar] (40) NULL ,
[address3] [varchar] (30) NULL ,
[phone_800] [varchar] (30) NULL ,
[phone_direct] [varchar] (30) NULL
) ON [PRIMARY]
GO
Thanks.
No, this is not a bug. Take a look at the CONCAT_NULL_YIELDS_NULL option in
Books Online. Probably it is set OFF on your SQL Server 7 database, or the
client API that connects sets it to off. You can check it like this:
SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL')
On SQL Server 2000 & 2005 it is ON by default, and most new client libraries
will set it to ON, which overrides the database settings.
You can easily get the same result by using COALESCE or ISNULL:
SELECT customer.cust_code,
COALESCE(customer.bus_name, '')
+ ' '
+ COALESCE(customer.address3, '')
+ ' Direct: '
+ COALESCE(customer.phone_direct, '')
+ ' 800: '
+ COALESCE(customer.phone_800, '')
AS related_info2
FROM customer
ORDER BY customer.cust_code
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Thanks Tibor but How can I put this into a view on SQL Server 2005 ?
COALESCE doesn't work because we have to get all the NULL and NON NULL rows.
"Tibor Karaszi" wrote:

> Check out SET CONCAT_NULL_YIELDS_NULL option. This is also available as a database option (see ALTER
> DATABASE). Note that the behavior you see on 2000 and 2005 is the standard behavior so I suggest you
> adapt your code instead of tweaking SQL Server into running in a non-standard way. For instance you
> can use ISNULL for each nullable column and convert to empty string if value is NULL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C57DC90D-0BBE-452D-802B-50C7F262AB6A@.microsoft.com...
>
|||My bet.........Sorry..........COALESCE works. I just forgot to apply it
to whole fields in the select statement and not only the sub select.
Thanks again Tibor & Plamen.
"Tibor Karaszi" wrote:

> You can't (assuming you refer to the session setting) since this isn't a sticky option (like SET
> ANSI_NULLS). So, either use the database option or adjust your code to work regardless.
>
> You lost me there. Can you explain with an example?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8D646949-DFA0-43FA-9AEA-B2D558AFC71C@.microsoft.com...
>