Is it possible to merge the 2 datasets into 3 dataset/into 2nd dat

Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
dataset rows into into 2nd dataset?
Actually i don't have access to opendataset and openrowset query execution
on different server. so, i am planning to create dataset for each server and
want merge the 2 dataset output into 3 rd dataset.Check out subreports. That is how this sort of thing is handled. Or you do
the merging in a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sriman" <Sriman@.discussions.microsoft.com> wrote in message
news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
> Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
> dataset rows into into 2nd dataset?
> Actually i don't have access to opendataset and openrowset query execution
> on different server. so, i am planning to create dataset for each server
> and
> want merge the 2 dataset output into 3 rd dataset.|||Hi,
We have 2 servers, one in USA and another in Canada. How can i merge
without using openrowset ... ? How can i use subreport to show the all data
into one table?
Regards,
sri.
"Bruce L-C [MVP]" wrote:
> Check out subreports. That is how this sort of thing is handled. Or you do
> the merging in a stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
> > Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
> > dataset rows into into 2nd dataset?
> > Actually i don't have access to opendataset and openrowset query execution
> > on different server. so, i am planning to create dataset for each server
> > and
> > want merge the 2 dataset output into 3 rd dataset.
>
>|||You can put a subreport into a cell of the table object.
Create your main report and test.
Create the report to be the subreport separately and test.
Drag and drop the subreport into a cell of the table control, right mouse
click and map the report parameter of the subreport to a field of the
dataset in the main report.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sriman" <Sriman@.discussions.microsoft.com> wrote in message
news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
> Hi,
> We have 2 servers, one in USA and another in Canada. How can i merge
> without using openrowset ... ? How can i use subreport to show the all
> data
> into one table?
> Regards,
> sri.
> "Bruce L-C [MVP]" wrote:
>> Check out subreports. That is how this sort of thing is handled. Or you
>> do
>> the merging in a stored procedure.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
>> > Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
>> > dataset rows into into 2nd dataset?
>> > Actually i don't have access to opendataset and openrowset query
>> > execution
>> > on different server. so, i am planning to create dataset for each
>> > server
>> > and
>> > want merge the 2 dataset output into 3 rd dataset.
>>|||Hi bruce,
thanks for the response.
i don't want to show the 2 datasets records. I have to check the 1st dataset
rows are available in 2nd dataset or not. If exists then show, otherwise
don't show . that is my task.
actually requirement is to list down the common server names from the 2
database(i.e 2 datasets).
Regards,
Sri
"Bruce L-C [MVP]" wrote:
> You can put a subreport into a cell of the table object.
> Create your main report and test.
> Create the report to be the subreport separately and test.
> Drag and drop the subreport into a cell of the table control, right mouse
> click and map the report parameter of the subreport to a field of the
> dataset in the main report.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
> > Hi,
> >
> > We have 2 servers, one in USA and another in Canada. How can i merge
> > without using openrowset ... ? How can i use subreport to show the all
> > data
> > into one table?
> >
> > Regards,
> > sri.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Check out subreports. That is how this sort of thing is handled. Or you
> >> do
> >> the merging in a stored procedure.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> >> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
> >> > Is it possible to merge the 2 datasets into 3 dataset or merge the 1st
> >> > dataset rows into into 2nd dataset?
> >> > Actually i don't have access to opendataset and openrowset query
> >> > execution
> >> > on different server. so, i am planning to create dataset for each
> >> > server
> >> > and
> >> > want merge the 2 dataset output into 3 rd dataset.
> >>
> >>
> >>
>
>|||Ahh, this is very easy from stored procedure. Bring in the first resultset
into a temp table (use linked servers). Then join the temp table with the
table(s) for the second resultset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sriman" <Sriman@.discussions.microsoft.com> wrote in message
news:A30D9C21-F110-4DA0-BE54-A3D830805B5F@.microsoft.com...
> Hi bruce,
> thanks for the response.
> i don't want to show the 2 datasets records. I have to check the 1st
> dataset
> rows are available in 2nd dataset or not. If exists then show, otherwise
> don't show . that is my task.
> actually requirement is to list down the common server names from the 2
> database(i.e 2 datasets).
> Regards,
> Sri
> "Bruce L-C [MVP]" wrote:
>> You can put a subreport into a cell of the table object.
>> Create your main report and test.
>> Create the report to be the subreport separately and test.
>> Drag and drop the subreport into a cell of the table control, right mouse
>> click and map the report parameter of the subreport to a field of the
>> dataset in the main report.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
>> > Hi,
>> >
>> > We have 2 servers, one in USA and another in Canada. How can i merge
>> > without using openrowset ... ? How can i use subreport to show the all
>> > data
>> > into one table?
>> >
>> > Regards,
>> > sri.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Check out subreports. That is how this sort of thing is handled. Or
>> >> you
>> >> do
>> >> the merging in a stored procedure.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> >> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
>> >> > Is it possible to merge the 2 datasets into 3 dataset or merge the
>> >> > 1st
>> >> > dataset rows into into 2nd dataset?
>> >> > Actually i don't have access to opendataset and openrowset query
>> >> > execution
>> >> > on different server. so, i am planning to create dataset for each
>> >> > server
>> >> > and
>> >> > want merge the 2 dataset output into 3 rd dataset.
>> >>
>> >>
>> >>
>>|||Yea, i know. but don't have rights to create linkedserver,no permission to
create DTS.
"Bruce L-C [MVP]" wrote:
> Ahh, this is very easy from stored procedure. Bring in the first resultset
> into a temp table (use linked servers). Then join the temp table with the
> table(s) for the second resultset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> news:A30D9C21-F110-4DA0-BE54-A3D830805B5F@.microsoft.com...
> > Hi bruce,
> > thanks for the response.
> > i don't want to show the 2 datasets records. I have to check the 1st
> > dataset
> > rows are available in 2nd dataset or not. If exists then show, otherwise
> > don't show . that is my task.
> > actually requirement is to list down the common server names from the 2
> > database(i.e 2 datasets).
> >
> > Regards,
> > Sri
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You can put a subreport into a cell of the table object.
> >> Create your main report and test.
> >> Create the report to be the subreport separately and test.
> >> Drag and drop the subreport into a cell of the table control, right mouse
> >> click and map the report parameter of the subreport to a field of the
> >> dataset in the main report.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> >> news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
> >> > Hi,
> >> >
> >> > We have 2 servers, one in USA and another in Canada. How can i merge
> >> > without using openrowset ... ? How can i use subreport to show the all
> >> > data
> >> > into one table?
> >> >
> >> > Regards,
> >> > sri.
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> Check out subreports. That is how this sort of thing is handled. Or
> >> >> you
> >> >> do
> >> >> the merging in a stored procedure.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
> >> >> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
> >> >> > Is it possible to merge the 2 datasets into 3 dataset or merge the
> >> >> > 1st
> >> >> > dataset rows into into 2nd dataset?
> >> >> > Actually i don't have access to opendataset and openrowset query
> >> >> > execution
> >> >> > on different server. so, i am planning to create dataset for each
> >> >> > server
> >> >> > and
> >> >> > want merge the 2 dataset output into 3 rd dataset.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||You don't need DTS. However, you would need the DBA to create a linked
server for you and you would need rights to create a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sriman" <Sriman@.discussions.microsoft.com> wrote in message
news:868070F0-4165-4CA2-A473-23D2925B2932@.microsoft.com...
> Yea, i know. but don't have rights to create linkedserver,no permission to
> create DTS.
> "Bruce L-C [MVP]" wrote:
>> Ahh, this is very easy from stored procedure. Bring in the first
>> resultset
>> into a temp table (use linked servers). Then join the temp table with the
>> table(s) for the second resultset.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> news:A30D9C21-F110-4DA0-BE54-A3D830805B5F@.microsoft.com...
>> > Hi bruce,
>> > thanks for the response.
>> > i don't want to show the 2 datasets records. I have to check the 1st
>> > dataset
>> > rows are available in 2nd dataset or not. If exists then show,
>> > otherwise
>> > don't show . that is my task.
>> > actually requirement is to list down the common server names from the 2
>> > database(i.e 2 datasets).
>> >
>> > Regards,
>> > Sri
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> You can put a subreport into a cell of the table object.
>> >> Create your main report and test.
>> >> Create the report to be the subreport separately and test.
>> >> Drag and drop the subreport into a cell of the table control, right
>> >> mouse
>> >> click and map the report parameter of the subreport to a field of the
>> >> dataset in the main report.
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> >> news:967BA3D3-D11D-4B19-BF15-AC207FE87752@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > We have 2 servers, one in USA and another in Canada. How can i
>> >> > merge
>> >> > without using openrowset ... ? How can i use subreport to show the
>> >> > all
>> >> > data
>> >> > into one table?
>> >> >
>> >> > Regards,
>> >> > sri.
>> >> >
>> >> > "Bruce L-C [MVP]" wrote:
>> >> >
>> >> >> Check out subreports. That is how this sort of thing is handled. Or
>> >> >> you
>> >> >> do
>> >> >> the merging in a stored procedure.
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Bruce Loehle-Conger
>> >> >> MVP SQL Server Reporting Services
>> >> >>
>> >> >> "Sriman" <Sriman@.discussions.microsoft.com> wrote in message
>> >> >> news:192C8B12-8E91-43D5-9DB8-08525EA9A96D@.microsoft.com...
>> >> >> > Is it possible to merge the 2 datasets into 3 dataset or merge
>> >> >> > the
>> >> >> > 1st
>> >> >> > dataset rows into into 2nd dataset?
>> >> >> > Actually i don't have access to opendataset and openrowset query
>> >> >> > execution
>> >> >> > on different server. so, i am planning to create dataset for each
>> >> >> > server
>> >> >> > and
>> >> >> > want merge the 2 dataset output into 3 rd dataset.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Hi,
I have 2 datasets.
One dataset is ProdSales
in which i use the query ="with SET ATCPRODS AS '{FILTER([PRODUCT].[ATC].[PACK].members,[PRODUCT].[ATC].currentmember.parent.name = """ + parameters!pProductName.Value + """)}' member [PRODUCT].[ATC].packname as '[PRODUCT].[ATC].currentmember.uniquename' select {[REIMBURSEMENT FLAGS].[All REIMBURSEMENT FLAGS]} on 0, crossjoin({[Measures].[Values],[Measures].[Units]},
crossjoin(ATCPRODS,[BENCHMARK].[BENCHMARK TYPE].allmembers)) on 1 from Austria WHERE ([GEOGRAPHY].[" + parameters!pShop.value + "]," + parameters!pPeriod.value + ")"
and the other dataset is ProdSalesOTC in which i use query
="with SET OTCPRODS AS '{FILTER([PRODUCT].[OTC].[PACK].members,[PRODUCT].[OTC].currentmember.parent.name = """ + parameters!pProductName.Value + """)}' member [PRODUCT].[OTC].packname as '[PRODUCT].[OTC].currentmember.uniquename' select {[REIMBURSEMENT FLAGS].[All REIMBURSEMENT FLAGS]} on 0, crossjoin({[Measures].[Values],[Measures].[Units]},
crossjoin(OTCPRODS,[BENCHMARK].[BENCHMARK TYPE].allmembers)) on 1 from Austria WHERE ([GEOGRAPHY].[" + parameters!pShop.value + "]," + parameters!pPeriod.value + ")"
How to write a new datset by merging the above dataset so that i can use the fields in the new dataset in report?

Is it possible to merge date from 2 or 3 tables into a View?

Suppose I have three tables Type1Events, Type2Events, and Type3Events where
each table has a DateTime column for the date and time the event occurs,
along with some data specific to the event. Can I pull all events from the
three tables into a single View so that the data appears as if it were one
single table, and use a where condition for the date range so that only
events in a certain date range for all three tables appear? So my output
might look like this
EventDate Type
=================
1/1/2001 1
1/2/2001 2
2/1/2003 3
... etc...
Not sure if this makes sense but. I really don't care about the data in the
tables for this example, only the ocurrances in dates ranges for all three.
thanks,
JIM
EventTables
CREATE TABLE dbo.Type1Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type2Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type3Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2001',
'data1' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2002',
'data2' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '1/1/2003',
'data3' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '2/1/2002',
'data4' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '3/1/2001',
'data5' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '4/1/2002',
'data6' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '5/1/2006',
'data7' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '6/1/2004',
'data8' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '7/1/2005',
'data9' )
GOcreate view events
as
select eventDate, someData from Type1Events
union all
select eventDate, someData from Type2Events
union all
select eventDate, someData from Type3Events
go|||Sorry, a more complete solution is:
create view events
as
select 1 as eventType, eventDate, someData from Type1Events
union all
select 2, eventDate, someData from Type2Events
union all
select 3, eventDate, someData from Type3Events
go|||Jeff,
Thanks a lot!, I figured it should be easy but havn't used union much so...
thanks,
JIM
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142016783.270339.21280@.u72g2000cwu.googlegroups.com...
> Sorry, a more complete solution is:
> create view events
> as
> select 1 as eventType, eventDate, someData from Type1Events
> union all
> select 2, eventDate, someData from Type2Events
> union all
> select 3, eventDate, someData from Type3Events
> go
>

Is it possible to merge continuously between 8am and 5pm?

I have servers in a merge scenario that replicate every ten minutes. When
changes occur, they need to replicate fairly quickly (within 30 minutes),
but changes may not occur in every hour.
I think it would be most efficient to run continuous rather than every 10
minutes. However, I would really like replication to not be running at all
outside of the 8am-5pm window. Is it possible to have continuous
replication, but only from 8am to 5pm? The SQL Enterprise Manager does not
seem to allow for it.
Rob Kraft
Sure. Add a job that runs at 8AM and again at 5PM. What does this job do?
It changes the schedule on the replication job.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
sql

is it possible to merge 2 columns into 1 in SQL server

Hi..

is it possible to merge 2 columns into 1 to hold data like this... When the user imports the file in particular file they will be

ACT_ID1 Tot_ACT1 ACT_ID2 TOT_ACT2 ..... until 15

BB 1245.45 CT some amount ....

The 2 letter character may change prob for each file.. at leat i know of somethem may change

So while i transfer the data to the production database can is it possible to do some thing like

COL1 BB 1245.45

COL2 CT 12456.12 etc..

Any help will be appreciated.

Regards

Karen

Hi Karen

You could try two inserts. Do the first one on column 1 and 2 and then do the second one on 3 and 4 in the same destination table. Put them in a stored proc that gets called on the import. You may also want to add a column that identifies which set of columns they are from.

|||

Charles,

Thanks for your answer... I am getting these acronymns from another dbf file which the user imports... so does it makes sense to create a table called acronymns (may change the name later) and have the following fields..

AcryID(int identity) Name Description

so when i am importing the information into the production database i can reference the acronymn from this table by inner joining it and then insert the data into the production database... like the way you suggested...

Is this approach good or would it slow down things...

Any other suggestions are welcome too.

Regards

Karen

|||

Anything you can do to normalize the data is a good thing, in terms of performance, maintenance and scalability.

|||

so do u think the approach is good or bad ?

|||

Yes. Here's an article about normalization:

http://en.wikipedia.org/wiki/Database_normalization

Is IIS always required for replication?

I would like to take advantage of either Merge or RDA replication over HTTP, but would like to avoid setting up IIS.

Is IIS always required to support connectivity for SQL Server Mobile replication back to a desktop SQL Server database?

Thanks, Tim

Yes IIS is very much required for Merge or RDA with SQL Server. However, setting up IIS is not a big thing as we have a "Configure Web Synchronization" wizard that is very helpful for you in this regard.

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||Hi

And how setting up a replication bettween a Windows Mobile 5 Pocket PC and a Windows XP Home Edition PC when IIS can't be install on Home Edition ?

?

Thanks for help
Jo?l

|||

1) What is your backend database?

2) Can I know what is the problem in moving to Win XP Professional Edition?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||1) To replace synchronisation between Access/PocketAccess (not more supported with WM5), we use know a MSDE database (on PC) with a SQL Server CE 2.0 database (on PocketPC). The replication work fine on Windows XP Pro, but on Windows XP Home there is no IIS to do the replication.

2) Our customer have Windows XP Home Edition. I'm ok with you that's not very "professional", but we can't tell to our customer : "Hey guys, you must move your forty PC to Windows XP Pro, because our software don't run on XP Home". They will say us "bye bye".

And they have already some old PocketPC (before WM5) with this application installed and it works with XP Home ... that's a big problem for us.

Thanks for helping us

|||I have heard that we can synchronize using ActiveSync is it correct or not, because on Online Book I read we can synchronize using IIS. I need to synchronize my database on Pocket PC to my Desktop PC without using IIS (if we have the cradle why we need access to internet from Pocket PC).

And if synchronized only can work with IIS than use the old way try to send each data by client server application. Its a silly things, but thats the only way if we can only synchronize using IIS.

Is IIS always required for replication?

I would like to take advantage of either Merge or RDA replication over HTTP, but would like to avoid setting up IIS.

Is IIS always required to support connectivity for SQL Server Mobile replication back to a desktop SQL Server database?

Thanks, Tim

Yes IIS is very much required for Merge or RDA with SQL Server. However, setting up IIS is not a big thing as we have a "Configure Web Synchronization" wizard that is very helpful for you in this regard.

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||Hi

And how setting up a replication bettween a Windows Mobile 5 Pocket PC and a Windows XP Home Edition PC when IIS can't be install on Home Edition ?

?

Thanks for help
Jo?l|||

1) What is your backend database?

2) Can I know what is the problem in moving to Win XP Professional Edition?

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||1) To replace synchronisation between Access/PocketAccess (not more supported with WM5), we use know a MSDE database (on PC) with a SQL Server CE 2.0 database (on PocketPC). The replication work fine on Windows XP Pro, but on Windows XP Home there is no IIS to do the replication.

2) Our customer have Windows XP Home Edition. I'm ok with you that's not very "professional", but we can't tell to our customer : "Hey guys, you must move your forty PC to Windows XP Pro, because our software don't run on XP Home". They will say us "bye bye".

And they have already some old PocketPC (before WM5) with this application installed and it works with XP Home ... that's a big problem for us.

Thanks for helping us|||I have heard that we can synchronize using ActiveSync is it correct or not, because on Online Book I read we can synchronize using IIS. I need to synchronize my database on Pocket PC to my Desktop PC without using IIS (if we have the cradle why we need access to internet from Pocket PC).

And if synchronized only can work with IIS than use the old way try to send each data by client server application. Its a silly things, but thats the only way if we can only synchronize using IIS.

Is complete control over subscriber-to-publisher inserts/updates possible?

Hi, we are using merge replication from our server (SQL Server 2005) to what
will soon be hundreds of subscribers. The subscribers are actually from a
Windows app that uses a SQLCE database. The client app is able to insert and
update records that are then merged up to the server. Everything I have
mentioned up to this point is already functional, but we recently realized
that more "control" over the replication process is needed. Here's what we
want to do: When a record that was inserted at the subscriber is replicated
up to the server, it must be caught and redirected to a stored procedure
which will perform the insert instead. This should be completely transparent
to the subscriber (meaning we can't return them an error code).
Unless I am mistaken the only way to get this kind of functionality is by
using a business logic handler. I have a sample which demonstrates some very
basic uses for the business logic handler but unfortunately it barely
scratches the surface of what is possible. This doc page from the BO explains
a scenario very similar to what we need (Custom Change Handling -> Apply
custom data), but I do not know how I would go about implementing it.
Can anyone who has done something like this before point me in the right
direction?
Using the BusinessLogic resolver is the correct way of doing it.
If the logic is such that it is only in one direction or only one table is
affected you can either use transactional replication from the subscribers
to the publisher (you will need to upgrade your subscribers to SQL Server
2005 standard for this), or have a trigger hanging of the publisher table so
when the insert arrives this trigger will process it and fire the stored
procedure.
You can use the session_property function to detect if the process doing the
dml is a replication process.
if convert(bit, sessionproperty('replication_agent'))=1
do work.
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
"Chris" <no@.spam.com> wrote in message
news:%2367uVkx0HHA.4932@.TK2MSFTNGP03.phx.gbl...
> Hi, we are using merge replication from our server (SQL Server 2005) to
> what
> will soon be hundreds of subscribers. The subscribers are actually from a
> Windows app that uses a SQLCE database. The client app is able to insert
> and
> update records that are then merged up to the server. Everything I have
> mentioned up to this point is already functional, but we recently realized
> that more "control" over the replication process is needed. Here's what we
> want to do: When a record that was inserted at the subscriber is
> replicated
> up to the server, it must be caught and redirected to a stored procedure
> which will perform the insert instead. This should be completely
> transparent
> to the subscriber (meaning we can't return them an error code).
> Unless I am mistaken the only way to get this kind of functionality is by
> using a business logic handler. I have a sample which demonstrates some
> very
> basic uses for the business logic handler but unfortunately it barely
> scratches the surface of what is possible. This doc page from the BO
> explains
> a scenario very similar to what we need (Custom Change Handling -> Apply
> custom data), but I do not know how I would go about implementing it.
> Can anyone who has done something like this before point me in the right
> direction?
|||Thank you Hilary. I would prefer to use the business logic resolver though if
possible, but the more I dig into this it seems like it won't work the way I
need it to. I believe all I can do with it is modify the record sent from the
subscriber. Please correct me if I'm wrong, but it will not allow me to
*stop* the insert unless I outright reject it. Meaning I can't "redirect" the
insert to a SP and still tell the subscriber that everything went fine.
Using a trigger on the publisher table seems interresting. I will have to
look into that further. Thanks for your insight.
Chris
On 7/31/2007 6:39:27 AM, "Hilary Cotter" wrote:
> Using the BusinessLogic resolver is the correct way of doing it.
> If the logic is such that it is only in one direction or only one table is
> affected you can either use transactional replication from the subscribers
> to the publisher (you will need to upgrade your subscribers to SQL Server
> 2005 standard for this), or have a trigger hanging of the publisher table so
> when the insert arrives this trigger will process it and fire the stored
> procedure.
> You can use the session_property function to detect if the process doing the
> dml is a replication process.
> if convert(bit, sessionproperty('replication_agent'))=1
> do work.
>