Hi, I don't know how too :
- print the same report 5 time but with different header on each copy
- suppress the "insert box" and the "insert text objet" with the parameter field I know how too suppress the"insert text objet" with the parameter field but not the "insert box"
can you help me please
thanksIs the header selection based on an input parameter? You could create 5 header sections and use a parameter to select or suppress the header sections accordingly.
I don't think you can suppress a box. My only suggestion is to do as above, create the section twice, one with box, one without, and then suppress an entire section instead of trying to suppress the box.
Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts
Thursday, March 8, 2012
Friday, February 24, 2012
Crossed transactional replication
Say we have office1 and office2, each has a copy of the database. We
want changes on one to be reflected ASAP on the other. And we have
the luxury of knowing that about 99.44% of the time, the two offices
work on separate accounts, so collisions, while not impossible, are
going to be possible on only a few tables and will happen very
infrequently, and say further that their resolution will be easy.
What I'd LIKE to do is set up unidirectional transactional replication
from #1 to to #2, and separately from #2 to #1. Will this even begin
to work, or will SQLServer forbid a publisher from also being a
subscriber to the same table? Yes, I could try it. No, I haven't
noticed it in BOL.
Now, it seems this would produce a looping situation, where an update
on #1 will cause an update on #2, which will then cause a redundant
update on #1, etc. UNLESS the replagent on the subscriber was smart
enough to turn on the "not for replication" flag automagically before
doing its thing. Is that logically correct? Is the capability
available? And if so, would it still be smart enough to detect
collisions if they occurred?
I think we have a pretty common scenario so maybe someone (or many
someones) is already doing it, but I am totally unclear from BOL as to
whether it's possible to solve directly with current tools. I can see
some home-grown ways to work around things, but they are necessarily
more complex than if the tool can do the job in the first place.
Thanks.
Josh
See "immediate updating option, overview" in BOL.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:i9c5f11pn844ka549jb4hcgduuoae6m77t@.4ax.com...
> Say we have office1 and office2, each has a copy of the database. We
> want changes on one to be reflected ASAP on the other. And we have
> the luxury of knowing that about 99.44% of the time, the two offices
> work on separate accounts, so collisions, while not impossible, are
> going to be possible on only a few tables and will happen very
> infrequently, and say further that their resolution will be easy.
> What I'd LIKE to do is set up unidirectional transactional replication
> from #1 to to #2, and separately from #2 to #1. Will this even begin
> to work, or will SQLServer forbid a publisher from also being a
> subscriber to the same table? Yes, I could try it. No, I haven't
> noticed it in BOL.
> Now, it seems this would produce a looping situation, where an update
> on #1 will cause an update on #2, which will then cause a redundant
> update on #1, etc. UNLESS the replagent on the subscriber was smart
> enough to turn on the "not for replication" flag automagically before
> doing its thing. Is that logically correct? Is the capability
> available? And if so, would it still be smart enough to detect
> collisions if they occurred?
> I think we have a pretty common scenario so maybe someone (or many
> someones) is already doing it, but I am totally unclear from BOL as to
> whether it's possible to solve directly with current tools. I can see
> some home-grown ways to work around things, but they are necessarily
> more complex than if the tool can do the job in the first place.
> Thanks.
> Josh
>
|||On Thu, 4 Aug 2005 20:57:17 -0700, "ChrisR" <noemail@.bla.com> wrote:
>See "immediate updating option, overview" in BOL.
Yes, I have, but it seems to be an alternative to crossed replication,
not really a satisfaction of it. It depends on using DTC to go behind
the replication and have the "subscriber" work directly against the
publisher, rather than cross replications. Anyway, it also uses GUIDs
and triggers, which are two more things we'd like to avoid.
Does anybody actually do it that way?
I mean, look, what if the two offices worked on COMPLETELY distinct
subsets of data, so IF we could have crossed replication it would
never have collisions, but the replication system would still have to
know the ranges on either side. We could probably implement this with
custom SPs or DTS filters, IF the crossed replication were legal in
the first place. Perhaps it is, that's what I'm asking, until I have
the time to simply try it - but I pretty much expect something in the
system will bark at me and it won't work. Again, I think this is a
very common requirement, and it would be nice to have it work out of
the box. Yukon?
Josh
|||I think most people just use immediate updating. Probably gonna have to test
your idea to see if it works.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:0ai7f1pqhmulj1ln5v7qhumkgan7ftt3ds@.4ax.com...
> On Thu, 4 Aug 2005 20:57:17 -0700, "ChrisR" <noemail@.bla.com> wrote:
> Yes, I have, but it seems to be an alternative to crossed replication,
> not really a satisfaction of it. It depends on using DTC to go behind
> the replication and have the "subscriber" work directly against the
> publisher, rather than cross replications. Anyway, it also uses GUIDs
> and triggers, which are two more things we'd like to avoid.
> Does anybody actually do it that way?
> I mean, look, what if the two offices worked on COMPLETELY distinct
> subsets of data, so IF we could have crossed replication it would
> never have collisions, but the replication system would still have to
> know the ranges on either side. We could probably implement this with
> custom SPs or DTS filters, IF the crossed replication were legal in
> the first place. Perhaps it is, that's what I'm asking, until I have
> the time to simply try it - but I pretty much expect something in the
> system will bark at me and it won't work. Again, I think this is a
> very common requirement, and it would be nice to have it work out of
> the box. Yukon?
> Josh
>
|||On Tue, 9 Aug 2005 13:23:24 -0700, "ChrisR" <noemail@.bla.com> wrote:
>I think most people just use immediate updating. Probably gonna have to test
>your idea to see if it works.
Guy at work claims he already has it going, he just had to customize
the SPs to prevent loops. When and if I actually get a look, I'll let
you know. FWIW he still had the distributors on the publishers.
J.
want changes on one to be reflected ASAP on the other. And we have
the luxury of knowing that about 99.44% of the time, the two offices
work on separate accounts, so collisions, while not impossible, are
going to be possible on only a few tables and will happen very
infrequently, and say further that their resolution will be easy.
What I'd LIKE to do is set up unidirectional transactional replication
from #1 to to #2, and separately from #2 to #1. Will this even begin
to work, or will SQLServer forbid a publisher from also being a
subscriber to the same table? Yes, I could try it. No, I haven't
noticed it in BOL.
Now, it seems this would produce a looping situation, where an update
on #1 will cause an update on #2, which will then cause a redundant
update on #1, etc. UNLESS the replagent on the subscriber was smart
enough to turn on the "not for replication" flag automagically before
doing its thing. Is that logically correct? Is the capability
available? And if so, would it still be smart enough to detect
collisions if they occurred?
I think we have a pretty common scenario so maybe someone (or many
someones) is already doing it, but I am totally unclear from BOL as to
whether it's possible to solve directly with current tools. I can see
some home-grown ways to work around things, but they are necessarily
more complex than if the tool can do the job in the first place.
Thanks.
Josh
See "immediate updating option, overview" in BOL.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:i9c5f11pn844ka549jb4hcgduuoae6m77t@.4ax.com...
> Say we have office1 and office2, each has a copy of the database. We
> want changes on one to be reflected ASAP on the other. And we have
> the luxury of knowing that about 99.44% of the time, the two offices
> work on separate accounts, so collisions, while not impossible, are
> going to be possible on only a few tables and will happen very
> infrequently, and say further that their resolution will be easy.
> What I'd LIKE to do is set up unidirectional transactional replication
> from #1 to to #2, and separately from #2 to #1. Will this even begin
> to work, or will SQLServer forbid a publisher from also being a
> subscriber to the same table? Yes, I could try it. No, I haven't
> noticed it in BOL.
> Now, it seems this would produce a looping situation, where an update
> on #1 will cause an update on #2, which will then cause a redundant
> update on #1, etc. UNLESS the replagent on the subscriber was smart
> enough to turn on the "not for replication" flag automagically before
> doing its thing. Is that logically correct? Is the capability
> available? And if so, would it still be smart enough to detect
> collisions if they occurred?
> I think we have a pretty common scenario so maybe someone (or many
> someones) is already doing it, but I am totally unclear from BOL as to
> whether it's possible to solve directly with current tools. I can see
> some home-grown ways to work around things, but they are necessarily
> more complex than if the tool can do the job in the first place.
> Thanks.
> Josh
>
|||On Thu, 4 Aug 2005 20:57:17 -0700, "ChrisR" <noemail@.bla.com> wrote:
>See "immediate updating option, overview" in BOL.
Yes, I have, but it seems to be an alternative to crossed replication,
not really a satisfaction of it. It depends on using DTC to go behind
the replication and have the "subscriber" work directly against the
publisher, rather than cross replications. Anyway, it also uses GUIDs
and triggers, which are two more things we'd like to avoid.
Does anybody actually do it that way?
I mean, look, what if the two offices worked on COMPLETELY distinct
subsets of data, so IF we could have crossed replication it would
never have collisions, but the replication system would still have to
know the ranges on either side. We could probably implement this with
custom SPs or DTS filters, IF the crossed replication were legal in
the first place. Perhaps it is, that's what I'm asking, until I have
the time to simply try it - but I pretty much expect something in the
system will bark at me and it won't work. Again, I think this is a
very common requirement, and it would be nice to have it work out of
the box. Yukon?
Josh
|||I think most people just use immediate updating. Probably gonna have to test
your idea to see if it works.
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:0ai7f1pqhmulj1ln5v7qhumkgan7ftt3ds@.4ax.com...
> On Thu, 4 Aug 2005 20:57:17 -0700, "ChrisR" <noemail@.bla.com> wrote:
> Yes, I have, but it seems to be an alternative to crossed replication,
> not really a satisfaction of it. It depends on using DTC to go behind
> the replication and have the "subscriber" work directly against the
> publisher, rather than cross replications. Anyway, it also uses GUIDs
> and triggers, which are two more things we'd like to avoid.
> Does anybody actually do it that way?
> I mean, look, what if the two offices worked on COMPLETELY distinct
> subsets of data, so IF we could have crossed replication it would
> never have collisions, but the replication system would still have to
> know the ranges on either side. We could probably implement this with
> custom SPs or DTS filters, IF the crossed replication were legal in
> the first place. Perhaps it is, that's what I'm asking, until I have
> the time to simply try it - but I pretty much expect something in the
> system will bark at me and it won't work. Again, I think this is a
> very common requirement, and it would be nice to have it work out of
> the box. Yukon?
> Josh
>
|||On Tue, 9 Aug 2005 13:23:24 -0700, "ChrisR" <noemail@.bla.com> wrote:
>I think most people just use immediate updating. Probably gonna have to test
>your idea to see if it works.
Guy at work claims he already has it going, he just had to customize
the SPs to prevent loops. When and if I actually get a look, I'll let
you know. FWIW he still had the distributors on the publishers.
J.
Sunday, February 19, 2012
Cross table copy working in Query Analyser, but not from code
I'm trying to copy a row from one table to another for audit purposes
using a 'INSERT INTO x SELECT y FROM z' statement. This works
absolutely fine in query analyser, however, when running the exact same
statement from code (.NET via oledb), it fails with the error:
An explicit value for the identity column in table 'x' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
or when specifying all columns:
Cannot insert explicit value for identity column in table 'x' when
IDENTITY_INSERT is set to OFF
Table 'x' has no identity columns, table 'y' has one (integer ID)
identity column.
Anyone have any ideas why this statement would work in the query
analyser, and not in code?
(SQL Server 2000, service pack 3a, .NET v1.1, latest MDAC)I would guess you are connected to different databases/instances and the
table schema are different. If needed, you can run a Profiler trace to
verify this.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rory" <rory.smith@.gmail.com> wrote in message
news:1137432432.481007.178730@.f14g2000cwb.googlegroups.com...
> I'm trying to copy a row from one table to another for audit purposes
> using a 'INSERT INTO x SELECT y FROM z' statement. This works
> absolutely fine in query analyser, however, when running the exact same
> statement from code (.NET via oledb), it fails with the error:
> An explicit value for the identity column in table 'x' can only be
> specified when a column list is used and IDENTITY_INSERT is ON.
> or when specifying all columns:
> Cannot insert explicit value for identity column in table 'x' when
> IDENTITY_INSERT is set to OFF
> Table 'x' has no identity columns, table 'y' has one (integer ID)
> identity column.
> Anyone have any ideas why this statement would work in the query
> analyser, and not in code?
> (SQL Server 2000, service pack 3a, .NET v1.1, latest MDAC)
>|||That's definately not the issue. I'm fairly experienced with database
applications, despite using .net. I've just never come across an
instance whereby the query analyser gave different results to the oledb
components.
Does either the Query Analyser or oledb .net component do anything
unusual behind the scenes? Or is it a possibility that this is
happening because in my program it is taking place inside a
transaction? (it's the first thing in the transaction, and the
exception is thrown immediately on adding the command to the
transaction)
Thanks for the reply Dan|||A Profiler trace should show all that is going on. I can't think of
anything that would cause different behavior for a single statement like
this. The error clearly indicates the target table has an identity column.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rory" <rory.smith@.gmail.com> wrote in message
news:1137437890.787728.312550@.o13g2000cwo.googlegroups.com...
> That's definately not the issue. I'm fairly experienced with database
> applications, despite using .net. I've just never come across an
> instance whereby the query analyser gave different results to the oledb
> components.
> Does either the Query Analyser or oledb .net component do anything
> unusual behind the scenes? Or is it a possibility that this is
> happening because in my program it is taking place inside a
> transaction? (it's the first thing in the transaction, and the
> exception is thrown immediately on adding the command to the
> transaction)
> Thanks for the reply Dan
>|||Rory (rory.smith@.gmail.com) writes:
> I'm trying to copy a row from one table to another for audit purposes
> using a 'INSERT INTO x SELECT y FROM z' statement. This works
> absolutely fine in query analyser, however, when running the exact same
> statement from code (.NET via oledb), it fails with the error:
> An explicit value for the identity column in table 'x' can only be
> specified when a column list is used and IDENTITY_INSERT is ON.
> or when specifying all columns:
> Cannot insert explicit value for identity column in table 'x' when
> IDENTITY_INSERT is set to OFF
> Table 'x' has no identity columns, table 'y' has one (integer ID)
> identity column.
> Anyone have any ideas why this statement would work in the query
> analyser, and not in code?
One thing to check is that there are not two table x in the database.
Own owned by dbo, which I assume that you run as from Query Analyzer,
and one owned by the user that you connect with from the application.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland - you were right - I'm unsure of where the 'phantom' table came
from - I created the database from a script which only has one instance
of the table. Anyway, all sorted now.
Many thanks to you both
using a 'INSERT INTO x SELECT y FROM z' statement. This works
absolutely fine in query analyser, however, when running the exact same
statement from code (.NET via oledb), it fails with the error:
An explicit value for the identity column in table 'x' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
or when specifying all columns:
Cannot insert explicit value for identity column in table 'x' when
IDENTITY_INSERT is set to OFF
Table 'x' has no identity columns, table 'y' has one (integer ID)
identity column.
Anyone have any ideas why this statement would work in the query
analyser, and not in code?
(SQL Server 2000, service pack 3a, .NET v1.1, latest MDAC)I would guess you are connected to different databases/instances and the
table schema are different. If needed, you can run a Profiler trace to
verify this.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rory" <rory.smith@.gmail.com> wrote in message
news:1137432432.481007.178730@.f14g2000cwb.googlegroups.com...
> I'm trying to copy a row from one table to another for audit purposes
> using a 'INSERT INTO x SELECT y FROM z' statement. This works
> absolutely fine in query analyser, however, when running the exact same
> statement from code (.NET via oledb), it fails with the error:
> An explicit value for the identity column in table 'x' can only be
> specified when a column list is used and IDENTITY_INSERT is ON.
> or when specifying all columns:
> Cannot insert explicit value for identity column in table 'x' when
> IDENTITY_INSERT is set to OFF
> Table 'x' has no identity columns, table 'y' has one (integer ID)
> identity column.
> Anyone have any ideas why this statement would work in the query
> analyser, and not in code?
> (SQL Server 2000, service pack 3a, .NET v1.1, latest MDAC)
>|||That's definately not the issue. I'm fairly experienced with database
applications, despite using .net. I've just never come across an
instance whereby the query analyser gave different results to the oledb
components.
Does either the Query Analyser or oledb .net component do anything
unusual behind the scenes? Or is it a possibility that this is
happening because in my program it is taking place inside a
transaction? (it's the first thing in the transaction, and the
exception is thrown immediately on adding the command to the
transaction)
Thanks for the reply Dan|||A Profiler trace should show all that is going on. I can't think of
anything that would cause different behavior for a single statement like
this. The error clearly indicates the target table has an identity column.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rory" <rory.smith@.gmail.com> wrote in message
news:1137437890.787728.312550@.o13g2000cwo.googlegroups.com...
> That's definately not the issue. I'm fairly experienced with database
> applications, despite using .net. I've just never come across an
> instance whereby the query analyser gave different results to the oledb
> components.
> Does either the Query Analyser or oledb .net component do anything
> unusual behind the scenes? Or is it a possibility that this is
> happening because in my program it is taking place inside a
> transaction? (it's the first thing in the transaction, and the
> exception is thrown immediately on adding the command to the
> transaction)
> Thanks for the reply Dan
>|||Rory (rory.smith@.gmail.com) writes:
> I'm trying to copy a row from one table to another for audit purposes
> using a 'INSERT INTO x SELECT y FROM z' statement. This works
> absolutely fine in query analyser, however, when running the exact same
> statement from code (.NET via oledb), it fails with the error:
> An explicit value for the identity column in table 'x' can only be
> specified when a column list is used and IDENTITY_INSERT is ON.
> or when specifying all columns:
> Cannot insert explicit value for identity column in table 'x' when
> IDENTITY_INSERT is set to OFF
> Table 'x' has no identity columns, table 'y' has one (integer ID)
> identity column.
> Anyone have any ideas why this statement would work in the query
> analyser, and not in code?
One thing to check is that there are not two table x in the database.
Own owned by dbo, which I assume that you run as from Query Analyzer,
and one owned by the user that you connect with from the application.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland - you were right - I'm unsure of where the 'phantom' table came
from - I created the database from a script which only has one instance
of the table. Anyway, all sorted now.
Many thanks to you both
Subscribe to:
Posts (Atom)