Accessing Initiating Event + Data From Existing External DB

Hello,
I have been exploring NS and I would like to use it as a rudimentary transfer tool. I have an existing database table from where I would to transfer a record to subscribers and then possibly move the records out of that table.

1. How do I set up the ..\SubscriptionClasses\SubscriptionClass\EventRules\EventRule so that it reads from another database? The TSQL Statement should probably work accross databases?

2. As I scroll through the sample ADFs I would think that ..\Providers\HostedProvider should also change too to another type.

Thank you very much,

Lubomir

Hi Lubomir -

Using the SQL Server Event Provider, you can collect events from tables in other databases or even other instances of SQL Server.

Configure the HostedProvider node to use the SQL Server Event Provider. Use the EventsQuery element to enter the T-SQL code that you will use to recognize new rows (or events of interest) in the table. Here's a sample.

<HostedProvider>
<ProviderName>SqlPrEP</ProviderName>
<ClassName>SQLProvider</ClassName>
<SystemName>%_NSSystem_%</SystemName>
<Schedule>
<Interval>P0DT00H00M60S</Interval>
</Schedule>
<Arguments>
<Argument>
<Name>EventsQuery</Name>
<Value>SELECT rowId, col1, col2, col3 FROM AnotherDb.dbo.vwCurrentRows WHERE rowId NOT IN (SELECT rowId FROM MyChron)</Value>
</Argument>
<Argument>
<Name>EventClassName</Name>
<Value>PressRelease</Value>
</Argument>
</Arguments>
</HostedProvider>

Next you can use the EventRule node of the SubscriptionClass to define your match rule; that is to write the T-SQL code that matches the events that you've collected to those subscribers who are interested in your events.

You EventRule would look something like this.

<EventRule>
<EventClassName>PressRelease</EventClassName>
<RuleName>PrEventRule</RuleName>
<Action>
INSERT INTO PrNotifications(
SubscriberId,
DeviceName,
SubscriberLocale,
col1,
col2)
SELECT
s.SubscriberId,
s.SubscriberDeviceName,
s.SubscriberLocale,
e.col1,
e.col2
FROM
PressRelease e,
PrSubscription s
WHERE
e.col3 = s.col3
</Action>
<ActionTimeout>P0DT00H00M45S</ActionTimeout>
</EventRule>

HTH...

Joe

Access Reports using ASP.net application?

Hi,
I am currently exploring Reporting services. I have created a simple report and would like to give access to my reports inside my asp.net application.
Can someone help me on how to go about doing this.
Thanks.

Hi eeyore21

The easiest way to do this is to create an IFRAME obtject and point the souce to the reportserver URL.
You could check out posting 'Adding Parameters from .ASPX file'on this forum. I posted some simular info there
,L0n3i200n

|||You can also use the rendering methodology. Go to and look up Reporting Services Renderingwww.microsoft.com
and search for Reporting Services Rendering. They have examples thatwill allow you to use reports in your asp.net app. I actually havearound 14 reports that are a part of a web app that I created inASP.Net that use this method.

Access parameters within dynamic SQL

I am exploring the use of dynamic SQL within a stored procedure and have run
in to a problem. The dynamic SQL has no visibility of variables declared
outside the dynamic SQL. Try this snippet which causes an error:
declare @.branch int
set @.branch = 10
exec ( 'select @.branch_no' )
Is there any way to make these variables visible to the dynamic sql without
concatenation?
The reason why this will be a problem is that I will be use the openxml
command within the dynamic sql. I will be using very large XML strings so I
am pretty sure that I will have problems concatenating XML strings with
dynamic sql statements.
Any ideas?
McGy
[url]http://mcgy.blogspot.com[/url]> The reason why this will be a problem is that I will be use the openxml
> command within the dynamic sql. I will be using very large XML strings so
> I
> am pretty sure that I will have problems concatenating XML strings with
> dynamic sql statements.
As long as each string is <= 8000 characters (or 4000 characters with
Unicode), you can say EXEC(@.sql1 + @.sql2 + @.sql3);
A|||Lookup the topic sp_ExecuteSQL in SQL Server Books Online. There is an
example which explains how to pass & return values from such strings.
--
Anith|||Great that was really useful. I have combined a couple of examples (openxml
and sp_executesql) from books online in the snippet below to show how XML
can be passed in as a parameter to dynamic sql:
DECLARE @.SQLString NVARCHAR(500)
/* Build the SQL string */
SET @.SQLString =
N'
DECLARE @.idoc int
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, ''/ROOT/Customer'',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @.idoc'
/* Execute the string */
EXECUTE sp_executesql @.SQLString, N'@.doc text',
@.doc = '<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
McGy
[url]http://mcgy.blogspot.com[/url]
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#d272ckPGHA.740@.TK2MSFTNGP12.phx.gbl...
> Lookup the topic sp_ExecuteSQL in SQL Server Books Online. There is an
> example which explains how to pass & return values from such strings.
> --
> Anith
>|||>> am exploring the use of dynamic SQL within a stored procedure and have ru
n
in to a problem. <<
As well you should!! This is an awful way to even think of writing
code of any kind. Remember coupling, cohesion and all that stuff in
your fist Software Engineering course?
So you want on-the-fly, mixed, proprietary languages so you can
manipulate XML with T-SQL? This whole thing sounds like a pile of
kludges, but without better specs we can only guess at a relatioanl
solution.|||The reason for dynamic SQL is that I need to parameterize the database name
in the queries. We have a database with 20 odd tables with exactly the same
structure - so rather than duplicating the stored procedure 20 odd times I
am looking at writing it once with dynamic SQL.
The reason for XML is so that I can send large batches of data at a time to
the stored procedure. Which is very efficient.
I am not sure that I will use this technique but it is certainly one of
several I am considering. Its not a position I relish being in but that's
the way the database is so more likely than not I will have to work with its
shortcomings.
See another post by my titled "Parameterize table name without constructing
dynamic query?"
McGy
[url]http://mcgy.blogspot.com[/url]
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141350534.964106.45490@.t39g2000cwt.googlegroups.com...
> in to a problem. <<
> As well you should!! This is an awful way to even think of writing
> code of any kind. Remember coupling, cohesion and all that stuff in
> your fist Software Engineering course?
>
> So you want on-the-fly, mixed, proprietary languages so you can
> manipulate XML with T-SQL? This whole thing sounds like a pile of
> kludges, but without better specs we can only guess at a relatioanl
> solution.
>