Reacting to DB events

I'd like to build an application that will react to specific changes to
data in a set of tables in a database. The application would replicate
these data changes to another database. The target database won't be
SQLServer. Neither is this simple replication, at times the application
will need to get extra data from the source database before the target
is updated.
In other DBMS systems I am involved with the DBMS has the facilty to
write to an application message queue so that the monitoring application
only has to monitor the queue rather than a database. What I'd like to
do is something like this:
1. Some application changes data in a table.
2. A trigger reacts to the change and writes a message to an application
queue.
3. A windows service/process monitors the queue and picks up the
message. It then carries out whatever replication/DB actions are necessary.
This would mean defining a number of new triggers on existing tables and
developing the windows service/process. Existing applications and the
existing tables in the database would remain unchanged. I'm not a
windows programmer but I have someone in my team who is and who will
build the windows service/process.
The bit I'm unsure about is how a trigger can write to an application
queue or communicate with the windows service/process. I may be using
the wrong terminology as I have more knowledge of Unix than Windows.
Could anyone help with how I can do this or suggest any alternative
strategies.
Thanks In Advance.
LaurenceYou can get all the queuing infrastructure for free. SQL Server 2005 include
s a functionality called
"Service Broker", where your trigger does SEND to write to the queue and the
service uses RECEIVE to
read off of the queue.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message news:43A2EAFC.5020500@.open.ac.uk
..
> I'd like to build an application that will react to specific changes to da
ta in a set of tables in
> a database. The application would replicate these data changes to another
database. The target
> database won't be SQLServer. Neither is this simple replication, at times
the application will
> need to get extra data from the source database before the target is updat
ed.
> In other DBMS systems I am involved with the DBMS has the facilty to write
to an application
> message queue so that the monitoring application only has to monitor the q
ueue rather than a
> database. What I'd like to do is something like this:
> 1. Some application changes data in a table.
> 2. A trigger reacts to the change and writes a message to an application q
ueue.
> 3. A windows service/process monitors the queue and picks up the message.
It then carries out
> whatever replication/DB actions are necessary.
> This would mean defining a number of new triggers on existing tables and d
eveloping the windows
> service/process. Existing applications and the existing tables in the dat
abase would remain
> unchanged. I'm not a windows programmer but I have someone in my team who
is and who will build
> the windows service/process.
> The bit I'm unsure about is how a trigger can write to an application queu
e or communicate with
> the windows service/process. I may be using the wrong terminology as I ha
ve more knowledge of
> Unix than Windows.
> Could anyone help with how I can do this or suggest any alternative strate
gies.
> Thanks In Advance.
> Laurence
>|||Thanks Tibor,
We currently run SQL Server 2000 but will upgrade to 2005 in time. If
there's no way to do this in 2000 I'll have to wait a bit.
Thanks Again.
Laurence
Tibor Karaszi wrote:
> You can get all the queuing infrastructure for free. SQL Server 2005
> includes a functionality called "Service Broker", where your trigger
> does SEND to write to the queue and the service uses RECEIVE to read off
> of the queue.
>|||There is nothing that stop you from doing this now. Just have the trigger wr
ite to a table and have
the app polling the table, reading off of it and removing the rows that has
been handled. But, I
think you should look into Service Broker as it provide you with so much inf
rastructure code. At
least grab a book and read about SB (or play with it), so you can make the r
ight decision. I
wouldn't be surprised if you in the end decide to wait until 2005 is impleme
nted to cut down on the
dev time for this (thanks to Service Broker).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Laurence Breeze" <i.l.breeze@.open.ac.uk> wrote in message news:43A7CA1D.6060804@.open.ac.uk
..
> Thanks Tibor,
> We currently run SQL Server 2000 but will upgrade to 2005 in time. If the
re's no way to do this
> in 2000 I'll have to wait a bit.
> Thanks Again.
> Laurence
>
> Tibor Karaszi wrote:
>

RDA Push fails with SQL Server 2005 Compact Edition

Hello
I have a SQL Server 2005 (build 9.0.3050) database with tables belonging to
different schemas. I am in the process of developing a Windows Mobile 5.0
application that will work against this database, and Iâ'm planning to use
Remote Data Access with SQL Server 2005 CE to synch data.
I have my RDA all set up and working correctly IIS-wise, and have no problem
pulling a table from my database. However, when I attempt to push a modified
table back to the database, I get an â'Internal error: Failure setting up
bindings, possibly caused by insufficient permissions.â' error (which equates
to error 28621: SSCE_M_BINDINGS), which according to
http://msdn2.microsoft.com/en-us/library/ms172898.aspx is an internal error
which â'cannot be resolved by common troubleshooting techniquesâ'. The
â'insufficient permissionsâ' is a red herring I think, since the problem
persists even if the SQL Server login that is being used for RDA is in the
sysadmin role.
Iâ've attempted to identify the problem by starting from scratch with a new
database and a new table with a couple of test columns. With the table
freshly created as dbo.TableName RDA works fine. However, when I create a
schema and transfer the table to the schema, RDA push fails as above, so to
me it looks like the schema is causing the problem.
Anyone any ideas about this? All help greatly appreciated!
Thanks - GrahamIn case this helps anyone else, I lodged a support call with Microsoft about
this and it has been confirmed that "SQL Server Compact Edition does not
support RDA.Push for non-default Schema tables". Hopefully this will be
fixed in the future.
"GrahamS" wrote:
> Hello
> I have a SQL Server 2005 (build 9.0.3050) database with tables belonging to
> different schemas. I am in the process of developing a Windows Mobile 5.0
> application that will work against this database, and Iâ'm planning to use
> Remote Data Access with SQL Server 2005 CE to synch data.
> I have my RDA all set up and working correctly IIS-wise, and have no problem
> pulling a table from my database. However, when I attempt to push a modified
> table back to the database, I get an â'Internal error: Failure setting up
> bindings, possibly caused by insufficient permissions.â' error (which equates
> to error 28621: SSCE_M_BINDINGS), which according to
> http://msdn2.microsoft.com/en-us/library/ms172898.aspx is an internal error
> which â'cannot be resolved by common troubleshooting techniquesâ'. The
> â'insufficient permissionsâ' is a red herring I think, since the problem
> persists even if the SQL Server login that is being used for RDA is in the
> sysadmin role.
> Iâ've attempted to identify the problem by starting from scratch with a new
> database and a new table with a couple of test columns. With the table
> freshly created as dbo.TableName RDA works fine. However, when I create a
> schema and transfer the table to the schema, RDA push fails as above, so to
> me it looks like the schema is causing the problem.
> Anyone any ideas about this? All help greatly appreciated!
> Thanks - Graham|||There is a workaround for this problem - you can use a synonym in place of
the base table name. Crucially, the synonym must be named the same as the
base table ie 'CREATE SYNONYM dbo.MyTable FOR MySchema.MyTable'.
"GrahamS" wrote:
> In case this helps anyone else, I lodged a support call with Microsoft about
> this and it has been confirmed that "SQL Server Compact Edition does not
> support RDA.Push for non-default Schema tables". Hopefully this will be
> fixed in the future.
> "GrahamS" wrote:
> > Hello
> >
> > I have a SQL Server 2005 (build 9.0.3050) database with tables belonging to
> > different schemas. I am in the process of developing a Windows Mobile 5.0
> > application that will work against this database, and Iâ'm planning to use
> > Remote Data Access with SQL Server 2005 CE to synch data.
> >
> > I have my RDA all set up and working correctly IIS-wise, and have no problem
> > pulling a table from my database. However, when I attempt to push a modified
> > table back to the database, I get an â'Internal error: Failure setting up
> > bindings, possibly caused by insufficient permissions.â' error (which equates
> > to error 28621: SSCE_M_BINDINGS), which according to
> > http://msdn2.microsoft.com/en-us/library/ms172898.aspx is an internal error
> > which â'cannot be resolved by common troubleshooting techniquesâ'. The
> > â'insufficient permissionsâ' is a red herring I think, since the problem
> > persists even if the SQL Server login that is being used for RDA is in the
> > sysadmin role.
> >
> > Iâ've attempted to identify the problem by starting from scratch with a new
> > database and a new table with a couple of test columns. With the table
> > freshly created as dbo.TableName RDA works fine. However, when I create a
> > schema and transfer the table to the schema, RDA push fails as above, so to
> > me it looks like the schema is causing the problem.
> >
> > Anyone any ideas about this? All help greatly appreciated!
> >
> > Thanks - Graham

RDA Push fails with SQL Server 2005 Compact Edition

Hello

I have a SQL Server 2005 (build 9.0.3050) database with tables belonging to
different schemas. I am in the process of developing a Windows Mobile 5.0
application that will work against this database, and I’m planning to use
Remote Data Access with SQL Server 2005 CE to synch data.

I have my RDA all set up and working correctly IIS-wise, and have no problem
pulling a table from my database. However, when I attempt to push a modified
table back to the database, I get an ‘Internal error: Failure setting up
bindings, possibly caused by insufficient permissions.’ error (which equates
to error 28621: SSCE_M_BINDINGS), which according to
http://msdn2.microsoft.com/en-us/library/ms172898.aspx is an internal error
which “cannot be resolved by common troubleshooting techniques”. The
‘insufficient permissions’ is a red herring I think, since the problem
persists even if the SQL Server login that is being used for RDA is in the
sysadmin role.

I’ve attempted to identify the problem by starting from scratch with a new
database and a new table with a couple of test columns. With the table
freshly created as dbo.TableName RDA works fine. However, when I create a
schema and transfer the table to the schema, RDA push fails as above, so to
me it looks like the schema is causing the problem.

Anyone any ideas about this? All help greatly appreciated!

Thanks - Graham

In case this helps anyone else, I lodged a support call with Microsoft about
this and it has been confirmed that "SQL Server Compact Edition does not
support RDA.Push for non-default Schema tables". Hopefully this will be
fixed in the future.
|||There is a workaround for this problem - you can use a synonym in place of the base table name. Crucially, the synonym must be named the same as the base table ie 'CREATE SYNONYM dbo.MyTable FOR MySchema.MyTable'.

RDA Push fails with SQL Server 2005 Compact Edition

Hello

I have a SQL Server 2005 (build 9.0.3050) database with tables belonging to
different schemas. I am in the process of developing a Windows Mobile 5.0
application that will work against this database, and I’m planning to use
Remote Data Access with SQL Server 2005 CE to synch data.

I have my RDA all set up and working correctly IIS-wise, and have no problem
pulling a table from my database. However, when I attempt to push a modified
table back to the database, I get an ‘Internal error: Failure setting up
bindings, possibly caused by insufficient permissions.’ error (which equates
to error 28621: SSCE_M_BINDINGS), which according to
http://msdn2.microsoft.com/en-us/library/ms172898.aspx is an internal error
which “cannot be resolved by common troubleshooting techniques”. The
‘insufficient permissions’ is a red herring I think, since the problem
persists even if the SQL Server login that is being used for RDA is in the
sysadmin role.

I’ve attempted to identify the problem by starting from scratch with a new
database and a new table with a couple of test columns. With the table
freshly created as dbo.TableName RDA works fine. However, when I create a
schema and transfer the table to the schema, RDA push fails as above, so to
me it looks like the schema is causing the problem.

Anyone any ideas about this? All help greatly appreciated!

Thanks - Graham

In case this helps anyone else, I lodged a support call with Microsoft about
this and it has been confirmed that "SQL Server Compact Edition does not
support RDA.Push for non-default Schema tables". Hopefully this will be
fixed in the future.
|||There is a workaround for this problem - you can use a synonym in place of the base table name. Crucially, the synonym must be named the same as the base table ie 'CREATE SYNONYM dbo.MyTable FOR MySchema.MyTable'.

rc:stylesheet and sql server 2005 sp2

Hello,

I build a report and changed its style by scripting into the URL line

rc : stylesheet = myStyle. It worked great. After installing SP2 of SQL Server 2005:

the report's style is ok when the report is first loaded, but after clicking the "view report" button the report's style changes to the default reportserver style. How can i fix it? (I may not change the rsreportserver.config file nor the original style file)

Thanks,

Liat

I have the exact same problem. Everything was working fine with the custom stylesheet being remembered when the View Report button was clicked until I installed SP2. I really need this ability, and can't find any answer anywhere in all the forums I have searched. Does anyone have an answer to this? Thanks in Advance - Cathy

RB - New table info didn't show up on the Report Builder report

Hi All - I'd like to add some more info to an existing RB report by adding a new table. I am able to add a new table to DSV and build relationship. I am also able to add the table to report model as a new entity and source fields. The report model gets rebuilt and redeployed without problem, and the old report still runs fine. But when I open the report in report builder and want to add more info, the new table/entity/attributes are not showing up at all.

I've had this problem before also, sometimes when I create a report model with multiple tables I can only drag and drop values from one table onto the report builder grid. Once I drag values from the one table there is no way to go back and add values from the other table.

Why is that?

|||

A report that already contains data displays the entities related to the data already in the report. If your new entity is not related to the data in the report, you will not be able to select fields from that entity and add them to your report. If the entities should be related, make sure you have created a Role in the report model that defines that relationship.