Propagation of event notification when tables are updated.

I would like to propagate an event signal to an external application
when a table in my MSSql2000 server is updated.

Prog A; I have an external application adding records to a table.
Prog B; I have another external application using this table as well.

When Prog A creates a new record in the Table, how can I have Prog B be
notified of the event without polling the table or creating a link
between Prog A and Prog B.

Thanks.> When Prog A creates a new record in the Table, how can I have Prog B be
> notified of the event

Depends what you mean by "notified". If you want to invoke some code within
the current process (B) then the obvious way is to have B read a table or
watch for some other event and then act accordingly. What exactly do you
want to achieve? Is this simply about optimistic locking?

--
David Portas
SQL Server MVP
--|||dubian (collatz@.bigtexansoftware.com) writes:
> I would like to propagate an event signal to an external application
> when a table in my MSSql2000 server is updated.
> Prog A; I have an external application adding records to a table.
> Prog B; I have another external application using this table as well.
> When Prog A creates a new record in the Table, how can I have Prog B be
> notified of the event without polling the table or creating a link
> between Prog A and Prog B.

By far, the easiest way is to poll.

The other way would be to have a trigger on the table, that fires of an
extended stored procedure or OLE object to somehow send a singal to
Process B. Since extended stored procedures and local OLE objects in
the same memory space as the rest of SQL Server, they are somewhat
dangerous: if they crash on an access violation, the entire server
goes belly-up.

I believe that you also can use the sp_OAxxx routiens to start an
OLE object on a remote server. In this there is less risk for crashes.
But this is like to take time, and when you are in a trigger you are
in a transaction hold locks. If the update frequency is high, this
trigger can kill your throughput.

A variation is to have the trigger to write to a table, and then
run a job from SQL Server Agent that reads the table and alerts the
other process. Such a job would run once a minute or so. Since this job
could be an ActiveX task you could signal with XP:s and that.

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

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

Progress Reporting in an external application

I have a custom external application that executes some packages. I would like to display the execution progress as it would appear in the Progress tab of the SSIS designer. How would I do that? Any help is appreciated.Implement IDTSEvents interface (the simplest way is inherit from DefaultEvents class, and override the methods you need), then call package.Execute(,,IDTSEvents,) method passing your object. Process the events from package and show the progress in the way you want.

Programmatically chosing an image in table

So we've got a report, and some external images (i.e. sitting on the
server's HD). I don't really want to put them in the SQL 2000 database if I
don't have to (mainly because I don't know how), but I want one column of
the report to be one of 5 images, depending on the value a column in the
table. If I embed the images, I can't see a way to chose one of the images
in the table; I can just make all the column the same. Or can I just code
the HTML line manually... Have to try that, I guess. Any other ideas?
If it helps, the report is a table of calls our customers have submitted,
and the image displays a bug, dollar sign, plus sign, script page or
question mark depending on whether the call is a bug, customization, feature
request, script request, or 2nd level support question.
ClintWe have created some "dashboard" like reports that have different icons
(for data audits). Obviously this might not be the best method for
everyone, but it works for us. We have folder structure like below:
Production
Images
Audit Reports
User Reports
Manager Reports
We implement our images through relative URLs. We put the images we
need inside the Images folder (this allows for reuse). In the report,
I insert an image from a URL (When using the wizard, I use a random
image url). Once the wizard is finished, I change the Value property
to be the URL like the following =iif(condition,
""../../Images/StoplightSingleRed.gif",
"../../Images/StoplightSingleGreen.gif")
You could also just use an absolute path (we used the relative in case
we changed server names).
Hopefully this solution will work for you.
Regards,
Dan|||Maybe I wasn't as clear as I could've been. The folder structure above
is what we have implemented in the Report Manager. We then uploaded
the files to the report manager inside the image folder.|||Dan,
Worked like a hot darn! Thanks. FYI, the solution was to drop in an image,
link it to one of my existing images, just for giggles. Then I modified the
Value section on the Properties of that object to:
=Switch(Fields!PDPType.Value="Bug", "http://localhost/images/bug.gif",
Fields!PDPType.Value="Customization Request",
"http://localhost/images/customization.gif", Fields!PDPType.Value="New
Feature/Enhancement", "http://localhost/images/enhancement.gif",
Fields!PDPType.Value="Script Request", "http://localhost/images/script.gif",
Fields!PDPType.Value="Support Request", http://localhost/images/support.GIF)
I might have to tweak and tune my file locations, and I've got to re-size
all my images so they're consistent (darn graphics people...). But I didn't
know you could put a conditional value in the image Value section. That's
the piece I was missing. The documentation on a lot of this stuff seems to
be rather lacking. It seems to cover the very minimum amount.
Clint
"Dan" <daniel.lenz@.qg.com> wrote in message
news:1141321207.081912.144230@.z34g2000cwc.googlegroups.com...
> Maybe I wasn't as clear as I could've been. The folder structure above
> is what we have implemented in the Report Manager. We then uploaded
> the files to the report manager inside the image folder.
>|||Ok, so this started working. But now I've thrown sorting on the report, and
as soon as I sort, the images get lost. If I look at the source for the
page, the image tag comes up like:
<IMG class="r1" src="http://pics.10026.com/?src="/>
, whereas the pre-sorted image tag looks like:
<IMG class="r1"
SRC="/Reports/Reserved.ReportViewerWebControl.axd?ReportSession=cc3iww2i5xcx2jea3gi0ia45&ControlID=33d1f91a-7bc7-4876-a195-7210eae11061&Culture=1033&UICulture=9&ReportStack=1&OpType=ReportImage&StreamID=56"/>
All this works fine in the Preview in VS2005 (the sorting, that is). Any
ideas? I'm beginning to think I should have imported the images into SQL...
Clint
"Clint" <nobody@.nowhere.non> wrote in message
news:%23zeX2MjPGHA.3728@.tk2msftngp13.phx.gbl...
> Dan,
> Worked like a hot darn! Thanks. FYI, the solution was to drop in an
> image, link it to one of my existing images, just for giggles. Then I
> modified the Value section on the Properties of that object to:
> =Switch(Fields!PDPType.Value="Bug", "http://localhost/images/bug.gif",
> Fields!PDPType.Value="Customization Request",
> "http://localhost/images/customization.gif", Fields!PDPType.Value="New
> Feature/Enhancement", "http://localhost/images/enhancement.gif",
> Fields!PDPType.Value="Script Request",
> "http://localhost/images/script.gif", Fields!PDPType.Value="Support
> Request", http://localhost/images/support.GIF)
> I might have to tweak and tune my file locations, and I've got to re-size
> all my images so they're consistent (darn graphics people...). But I
> didn't know you could put a conditional value in the image Value section.
> That's the piece I was missing. The documentation on a lot of this stuff
> seems to be rather lacking. It seems to cover the very minimum amount.
> Clint
> "Dan" <daniel.lenz@.qg.com> wrote in message
> news:1141321207.081912.144230@.z34g2000cwc.googlegroups.com...
>> Maybe I wasn't as clear as I could've been. The folder structure above
>> is what we have implemented in the Report Manager. We then uploaded
>> the files to the report manager inside the image folder.
>|||Ahhh, sweetness happens! We happened to have a MS rep here today who
specializes in ReportServer, and I mentioned my problem. He suggested
embedding the images, changing the source to Embedded, and then changing the
Value statement to:
=Switch(Fields!PDPType.Value="Bug", "Bug",
Fields!PDPType.Value="Customization Request", "Customization",
Fields!PDPType.Value="New Feature/Enhancement", "Enhancement",
Fields!PDPType.Value="Script Request", "Script",
Fields!PDPType.Value="Support Request", "Support")
Besides working properly with the sort, it has be added benefit of
distributing the images with the report. Now if I could only get the
RowNumber function to work!
Clint
"Clint" <nobody@.nowhere.non> wrote in message
news:exU7IhwPGHA.140@.TK2MSFTNGP12.phx.gbl...
> Ok, so this started working. But now I've thrown sorting on the report,
> and as soon as I sort, the images get lost. If I look at the source for
> the page, the image tag comes up like:
> <IMG class="r1" src="http://pics.10026.com/?src="/>
> , whereas the pre-sorted image tag looks like:
> <IMG class="r1"
> src="http://pics.10026.com/?src=/Reports/Reserved.ReportViewerWebControl.axd?ReportSession=cc3iww2i5xcx2jea3gi0ia45&ControlID=33d1f91a-7bc7-4876-a195-7210eae11061&Culture=1033&UICulture=9&ReportStack=1&OpType=ReportImage&StreamID=56"/>
> All this works fine in the Preview in VS2005 (the sorting, that is). Any
> ideas? I'm beginning to think I should have imported the images into
> SQL...
> Clint
> "Clint" <nobody@.nowhere.non> wrote in message
> news:%23zeX2MjPGHA.3728@.tk2msftngp13.phx.gbl...
>> Dan,
>> Worked like a hot darn! Thanks. FYI, the solution was to drop in an
>> image, link it to one of my existing images, just for giggles. Then I
>> modified the Value section on the Properties of that object to:
>> =Switch(Fields!PDPType.Value="Bug", "http://localhost/images/bug.gif",
>> Fields!PDPType.Value="Customization Request",
>> "http://localhost/images/customization.gif", Fields!PDPType.Value="New
>> Feature/Enhancement", "http://localhost/images/enhancement.gif",
>> Fields!PDPType.Value="Script Request",
>> "http://localhost/images/script.gif", Fields!PDPType.Value="Support
>> Request", http://localhost/images/support.GIF)
>> I might have to tweak and tune my file locations, and I've got to re-size
>> all my images so they're consistent (darn graphics people...). But I
>> didn't know you could put a conditional value in the image Value section.
>> That's the piece I was missing. The documentation on a lot of this stuff
>> seems to be rather lacking. It seems to cover the very minimum amount.
>> Clint
>> "Dan" <daniel.lenz@.qg.com> wrote in message
>> news:1141321207.081912.144230@.z34g2000cwc.googlegroups.com...
>> Maybe I wasn't as clear as I could've been. The folder structure above
>> is what we have implemented in the Report Manager. We then uploaded
>> the files to the report manager inside the image folder.
>>
>