immediate updating triggers have "incorrect syntax near ')'."

Hello,
I am using SQL 7.0 to replicate to an immediate-updating subscriber and I am
having trouble when updating a table from the subscriber. When I run the
update statement, I get the error "incorrect syntax near ')'. I believe that
this is either in the auto-generated MSsync trigger or the sp on the
publisher. I am sure the original update statement is correct.
It seems almost as though a parameter is missing... I don't know.
One thing that I noticed is that I only receive this error on certain
tables. The main differences are:
1. the tables that don't work are really wide in columns (by all means they
should be three tables each) whereas the tables that do work are relatively
small.
2. the tables that don't work used to have text columns, but I changed them
to enormous varchar columns. The tables that do work have never had text
columns.
My experience with replication is limited, so any help or advice in this
matter would be greatly appreciated.
Thanks in advance,
-b.
Please can you confirm what service pack you are using, as this issue (or a
v.similar one) cropped up prior to sp2 and was fixed there
(http://support.microsoft.com/kb/238658/EN-US/).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you for your reply. Yes, I read something about that. I had applied sp4
and assumed that it was cumulative... is it not?
-b.
"Paul Ibison" wrote:

> Please can you confirm what service pack you are using, as this issue (or a
> v.similar one) cropped up prior to sp2 and was fixed there
> (http://support.microsoft.com/kb/238658/EN-US/).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Okay, I just read that the service packs are cumulative.
However, I did read something that said that text and image columns will not
replicate normally from immediate updating subscribers, and that special
considerations need to be made
(http://msdn.microsoft.com/library/de...lplan_1l4e.asp)
I no longer have text columns in the tables, but I DID, however, publish to
the subscriber previously with text fields before changing them to varchars
and publishing to the subscriber again. Is it possible that these triggers
remain from the previous publication...?
-b.
"pukeboot" wrote:
[vbcol=seagreen]
> Thank you for your reply. Yes, I read something about that. I had applied sp4
> and assumed that it was cumulative... is it not?
> -b.
> "Paul Ibison" wrote:
|||That shouldn't be the case, unless you did a nosync initialization. Can you
post up the text of some of the triggers so I can take a look?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||to avoid a massively lengthy post, I have sent the trigger code directly to
you via attachment...
Thanks again,
-b.
"Paul Ibison" wrote:

> That shouldn't be the case, unless you did a nosync initialization. Can you
> post up the text of some of the triggers so I can take a look?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||My email address isn't the one I post with (long story). Please send to Paul
.. Ibison@.ReplicationAnswers . Com (no spaces).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I read that as "paulibison@.replicationanswers.com" ... I wasn't sure with the
line breaks and the elipsis.
I forwarded the message there. I hope that's it.
-b.
"Paul Ibison" wrote:

> My email address isn't the one I post with (long story). Please send to Paul
> .. Ibison@.ReplicationAnswers . Com (no spaces).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Bobby,
there's nothing I can see wrong in the trigger, and no reference to text
columns. As you are on sp4 (publisher, distributor and subscriber?), this is
baffling. I'd run profiler to see where it gets to, and fails, in the
synchronization, and also enable logging to see what that reveals
(http://support.microsoft.com/?id=312292).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I am unfamiliar with the profiler, but I gave it a go and found that the
"internal abort" event was occuring after this line in the trigger (bear with
this... it's a long line):
exec @.retcode = dbo.sp_executesql @.rpc_proc, @.rpc_types,
@.c1
output,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8,@.c9,@.c10,@.c11,@. c12,@.c13,@.c14,@.c15,@.c16,@.c17,@.c18,@.c19,@.c20,@.c21,@. c22,@.c23,@.c24,@.c25,@.c26,@.c27,@.c28,@.c29,@.c30,@.c31,@. c32,@.c33,@.c34,@.c35,@.c36,@.c37,@.c38,@.c39,@.c40,@.c41,@. c42,@.c43,@.c44,@.c45,@.c46,@.c47,@.c48,@.c49,@.c50,@.c51,@. c52,@.
c53,@.c54,@.c55,@.c56,@.c57,@.c58,@.c59,@.c60,@.c61,@.c62,@. c63,@.c64,@.c65,@.c66,@.c67,@.c68,@.c69,@.c70,@.c71,@.c72,@. c73,@.c74,@.c75,@.c76,@.c77,@.c78,@.c79,@.c80,@.c81,@.c82,@. c83,@.c84,@.c85,@.c86,@.c87,@.c88,@.c89,@.c90,@.c91,@.c92,@. c93,@.c94,@.c95,@.c96,@.c97,@.c98,@.c99,@.c100,@.c101,@.c10 2,@.c1
03,@.c104,@.c105,@.c106,@.c107,@.c108,@.c109,@.c110,@.c111 ,@.c112,@.c113,@.c114,@.c115,@.c116,@.c117,@.c118,@.c119,@. c120,@.c121,@.c122,@.c123,@.c124,@.c125,@.c126,@.c127,@.c1 28,@.c129,@.c130,@.c131,@.c132,@.c133,@.c134,@.c135,@.c136 ,@.c137,@.c138,@.c139,@.c140,@.c141,@.c142,@.c143,@.c144,@. c145,
@.c146,@.c147,@.c148,@.c149,@.c150,@.c151,@.c152,@.c153,@.c 154,@.c155,@.c156,@.c157,@.c158,@.c159,@.c160,@.c161,@.c16 2,@.c163,@.c164,@.c165,@.c166,@.c167,@.c168,@.c169,@.c170, @.c171,@.c172,@.c173,@.c174,@.c175,@.c176,@.c177,@.c178,@.c 179,@.c180,@.c181,@.c182,@.c183,@.c184,@.c185,@.c186,@.c18 7,@.c1
88,@.c189,@.c190,@.c191,@.c192,@.c193,@.c194,@.c195,@.c196 ,@.c197,@.c198,@.c199,@.c200,@.c201,@.c202,@.c203,@.c204,@. c205,@.c206,@.c207,@.c208,@.c209,@.c210,@.c211,@.c212,@.c2 13,@.c214,@.c215,@.c216,@.c217,@.c218,@.c219,@.c220,@.c221 ,@.c222,@.c223,@.c224,@.c225,@.c226,@.c227,@.c228,@.c229,@. c230,
@.c231,@.c232,@.c233,@.c234,@.c235,@.c236
output,@.c237,@.c238,@.c239,@.c240,@.c241,@.c242,@.c243,@. c244,@.c245,@.c246,@.c247,@.c248
,@.c1_old,@.c2_old,@.c3_old,@.c4_old,@.c5_old,@.c6_old,@. c7_old,@.c8_old,@.c9_old,@.c10_old,@.c11_old,@.c12_old, @.c13_old,@.c14_old,@.c15_old,@.c16_old,@.c17_old,@.c18_ old,@.c19_old,@.c20_old,@.c21_old,@.c22_old,@.c23_old,@. c24_old,@.c25_old,@.c26_old,@.c27_old,@.c28_old,@.c29_o ld,@.c
30_old,@.c31_old,@.c32_old,@.c33_old,@.c34_old,@.c35_ol d,@.c36_old,@.c37_old,@.c38_old,@.c39_old,@.c40_old,@.c4 1_old,@.c42_old,@.c43_old,@.c44_old,@.c45_old,@.c46_old ,@.c47_old,@.c48_old,@.c49_old,@.c50_old,@.c51_old,@.c52 _old,@.c53_old,@.c54_old,@.c55_old,@.c56_old,@.c57_old, @.c58_
old,@.c59_old,@.c60_old,@.c61_old,@.c62_old,@.c63_old,@. c64_old,@.c65_old,@.c66_old,@.c67_old,@.c68_old,@.c69_o ld,@.c70_old,@.c71_old,@.c72_old,@.c73_old,@.c74_old,@.c 75_old,@.c76_old,@.c77_old,@.c78_old,@.c79_old,@.c80_ol d,@.c81_old,@.c82_old,@.c83_old,@.c84_old,@.c85_old,@.c8 6_old
,@.c87_old,@.c88_old,@.c89_old,@.c90_old,@.c91_old,@.c92 _old,@.c93_old,@.c94_old,@.c95_old,@.c96_old,@.c97_old, @.c98_old,@.c99_old,@.c100_old,@.c101_old,@.c102_old,@.c 103_old,@.c104_old,@.c105_old,@.c106_old,@.c107_old,@.c 108_old,@.c109_old,@.c110_old,@.c111_old,@.c112_old,@.c 113_o
ld,@.c114_old,@.c115_old,@.c116_old,@.c117_old,@.c118_o ld,@.c119_old,@.c120_old,@.c121_old,@.c122_old,@.c123_o ld,@.c124_old,@.c125_old,@.c126_old,@.c127_old,@.c128_o ld,@.c129_old,@.c130_old,@.c131_old,@.c132_old,@.c133_o ld,@.c134_old,@.c135_old,@.c136_old,@.c137_old,@.c138_o ld,@.c
139_old,@.c140_old,@.c141_old,@.c142_old,@.c143_old,@.c 144_old,@.c145_old,@.c146_old,@.c147_old,@.c148_old,@.c 149_old,@.c150_old,@.c151_old,@.c152_old,@.c153_old,@.c 154_old,@.c155_old,@.c156_old,@.c157_old,@.c158_old,@.c 159_old,@.c160_old,@.c161_old,@.c162_old,@.c163_old,@.c 164_o
ld,@.c165_old,@.c166_old,@.c167_old,@.c168_old,@.c169_o ld,@.c170_old,@.c171_old,@.c172_old,@.c173_old,@.c174_o ld,@.c175_old,@.c176_old,@.c177_old,@.c178_old,@.c179_o ld,@.c180_old,@.c181_old,@.c182_old,@.c183_old,@.c184_o ld,@.c185_old,@.c186_old,@.c187_old,@.c188_old,@.c189_o ld,@.c
190_old,@.c191_old,@.c192_old,@.c193_old,@.c194_old,@.c 195_old,@.c196_old,@.c197_old,@.c198_old,@.c199_old,@.c 200_old,@.c201_old,@.c202_old,@.c203_old,@.c204_old,@.c 205_old,@.c206_old,@.c207_old,@.c208_old,@.c209_old,@.c 210_old,@.c211_old,@.c212_old,@.c213_old,@.c214_old,@.c 215_o
ld,@.c216_old,@.c217_old,@.c218_old,@.c219_old,@.c220_o ld,@.c221_old,@.c222_old,@.c223_old,@.c224_old,@.c225_o ld,@.c226_old,@.c227_old,@.c228_old,@.c229_old,@.c230_o ld,@.c231_old,@.c232_old,@.c233_old,@.c234_old,@.c235_o ld,@.c236_old,@.c237_old,@.c238_old,@.c239_old,@.c240_o ld,@.c
241_old,@.c242_old,@.c243_old,@.c244_old,@.c245_old,@.c 246_old,@.c247_old,@.c248_old
, @.bitmap
plus, I also get an error that says:
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@.c30o'.
If you have any ideas what might be causing this, or if you have any further
advice on what I can do to resolve the problem, I would love to hear it.
Thanks for all your input.
-b.
"Paul Ibison" wrote:

> Bobby,
> there's nothing I can see wrong in the trigger, and no reference to text
> columns. As you are on sp4 (publisher, distributor and subscriber?), this is
> baffling. I'd run profiler to see where it gets to, and fails, in the
> synchronization, and also enable logging to see what that reveals
> (http://support.microsoft.com/?id=312292).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Immediate Updating Subscription - Hangs

We have installed SQL Server 2000 on a new server running Windows 2003.
This server is the distirbution Server. It also contains a one database. THis database subscibes to data from other servers and publishes data to other servers.
One of the publications is an updateable subscription. Ths subscription is pushed to the subscriber.
The replication works fine from publisher to subscriber, but when the subscriber attempts an update it just hangs. I see no blocking on either server.
DTC is running on both servers. I executed sp_link_pubisher on the subscriber to populate the publisher login and password in the MSsubscription_properties table.
I must be missing something else.
Can anyone help?
When you say it hangs do you mean it is slow or it doesn't work?
Do you eventually get an error message saying "Access denied"?
If you get this error message, DTC on your Subcriber cannot talk to your Publisher. Its either a name resolution problem, or more likely a permissions problem. Check the accound your SQL Server agent runs under on the Susbcriber and make sure it is part o
f the local administrators group on your Publisher.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thank you Hilary for your prompt reply.
WHat I mean by it hangs is it never completes. And it doesn't appear to time out.
I do feel it is something to do with DTC. I will investigate the account SQL Server agent runs under on the Susbcriber
and make sure it is part of the local administrators group on the Publisher.
If you cant hink of anything else in the meantime, I am all ears.
Thanks again.
|||I verified that the account the SQL Server agent runs under on the Susbcriber is part of the local administrators group on the Publisher.
I do not get an "Access denied" error. I get no response at all.
Can you suggest anything else?
-- Hilary Cotter wrote: --
When you say it hangs do you mean it is slow or it doesn't work?
Do you eventually get an error message saying "Access denied"?
If you get this error message, DTC on your Subcriber cannot talk to your Publisher. Its either a name resolution problem, or more likely a permissions problem. Check the accound your SQL Server agent runs under on the Susbcriber and make sure it is p
art of the local administrators group on your Publisher.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||what does your msdtc log say. It can be found in
C:\WINDOWS\System32\MSDtc
"casey@.newsgroups.nospam" <anonymous@.discussions.microsoft.com> wrote in
message news:24BA0D29-2D9B-417D-8730-3B2920FBF11B@.microsoft.com...
> I verified that the account the SQL Server agent runs under on the
Susbcriber is part of the local administrators group on the Publisher.
> I do not get an "Access denied" error. I get no response at all.
> Can you suggest anything else?
> -- Hilary Cotter wrote: --
> When you say it hangs do you mean it is slow or it doesn't work?
> Do you eventually get an error message saying "Access denied"?
> If you get this error message, DTC on your Subcriber cannot talk to
your Publisher. Its either a name resolution problem, or more likely a
permissions problem. Check the accound your SQL Server agent runs under on
the Susbcriber and make sure it is part of the local administrators group on
your Publisher.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||Helen,
The DTC log contains mostly non-displayable characters. Same for the Trace. Is there a tool I should be using to read this file?
|||My problem is resolved.
The trouble was that with Windows 2003, Enable network DTC access is not "enabled" by default. Once I enabled this, everything works fine.
Thanks for your help.

Immediate Updating Subscriber not Queuing updates

Hey all,
I have a problem with replication that I was hoping someone might be able to
shed some light on. Heres the scenario.
Server A, setup as a publisher + distributor. A publication was created and
marked for transaction replication with immediate updating with queued as a
failover.
Server B is the only subscriber. I had to run sp_link_publication in order
to get it to work.
I can successfully replication changes from A - B, and B - A. If B goes
down, and comes back up, it will re-sync no problem. My problem is that when
A goes down, I am not getting anything saved into the queue. When I try to
do any updating, all I get is 'SQL Server does not exist, etc'. There are no
other triggers on the table.
Thoughts?
One more thing, this is SQL 2000 SP3 (.818)
"MJM" wrote:

> Hey all,
> I have a problem with replication that I was hoping someone might be able to
> shed some light on. Heres the scenario.
> Server A, setup as a publisher + distributor. A publication was created and
> marked for transaction replication with immediate updating with queued as a
> failover.
> Server B is the only subscriber. I had to run sp_link_publication in order
> to get it to work.
> I can successfully replication changes from A - B, and B - A. If B goes
> down, and comes back up, it will re-sync no problem. My problem is that when
> A goes down, I am not getting anything saved into the queue. When I try to
> do any updating, all I get is 'SQL Server does not exist, etc'. There are no
> other triggers on the table.
> Thoughts?
|||you have to manually failover your subscriber from using immediate updating
to using queued updating. There is no automatic failover.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"MJM" <MJM@.discussions.microsoft.com> wrote in message
news:EDD81CC4-9696-4AAB-A468-B8734DC08473@.microsoft.com...[vbcol=seagreen]
> One more thing, this is SQL 2000 SP3 (.818)
> "MJM" wrote:
able to[vbcol=seagreen]
and[vbcol=seagreen]
as a[vbcol=seagreen]
order[vbcol=seagreen]
when[vbcol=seagreen]
to[vbcol=seagreen]
are no[vbcol=seagreen]

immediate updating isnt working

Im doing some testing on Immediate Updating Transactional Replication. I
generated a script to drop the Subscription and Article to a particular
table to add an Identity column on it. After that, I re-added the table to
the Publication and re-snapshotted the table. No problem and all is good at
this point. However now when I insert a record into the Subscriber, the row
isn't getting back to the Publisher *for this table only.* It is working for
other tables though. I scripted out the sp_addarticle for both this table
and a table that the immediate updating is working properly on and they are
identicle with the exception of the names. Whats the dealio? All ideas are
appreciated.
SQL2K SP3
TIA, ChrisR
I figured out whats up. The triggers for Insert, Update, and Deletes arent
being created like they were during the original snapshot. Does anyone know
why?
"ChrisR" <bla@.noemail.com> wrote in message
news:u58jVxkLFHA.2796@.tk2msftngp13.phx.gbl...
> Im doing some testing on Immediate Updating Transactional Replication. I
> generated a script to drop the Subscription and Article to a particular
> table to add an Identity column on it. After that, I re-added the table to
> the Publication and re-snapshotted the table. No problem and all is good
at
> this point. However now when I insert a record into the Subscriber, the
row
> isn't getting back to the Publisher *for this table only.* It is working
for
> other tables though. I scripted out the sp_addarticle for both this table
> and a table that the immediate updating is working properly on and they
are
> identicle with the exception of the names. Whats the dealio? All ideas are
> appreciated.
> --
> SQL2K SP3
> TIA, ChrisR
>
|||how did you resync after making changes to your subscription.
I think it would be best to recreate your snapshot - it sounds like your
metadata is out of sync.
Hilary Cotter
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
"ChrisR" <bla@.noemail.com> wrote in message
news:%23fFESGmLFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I figured out whats up. The triggers for Insert, Update, and Deletes arent
> being created like they were during the original snapshot. Does anyone
know[vbcol=seagreen]
> why?
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:u58jVxkLFHA.2796@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
> at
> row
> for
table[vbcol=seagreen]
> are
are
>
|||I re-snapshotted the table.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uKk9rboLFHA.580@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> how did you resync after making changes to your subscription.
> I think it would be best to recreate your snapshot - it sounds like your
> metadata is out of sync.
> --
> Hilary Cotter
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:%23fFESGmLFHA.1476@.TK2MSFTNGP09.phx.gbl...
arent[vbcol=seagreen]
> know
I[vbcol=seagreen]
particular[vbcol=seagreen]
table[vbcol=seagreen]
> to
good[vbcol=seagreen]
the[vbcol=seagreen]
working[vbcol=seagreen]
> table
they
> are
>
|||I realize that - but I think you need to re-snapshot the entire publication.
I had problems like this with merge replication, which could only be solved
by a complete resnapshotting and re-synchronization.
Hilary Cotter
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
"ChrisR" <bla@.noemail.com> wrote in message
news:eV5KqXvLFHA.3512@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> I re-snapshotted the table.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uKk9rboLFHA.580@.TK2MSFTNGP15.phx.gbl...
> arent
Replication.[vbcol=seagreen]
> I
> particular
> table
> good
> the
> working
> they
ideas
>
|||I was afraid of that. Just wanted to make sure we were on the same page.
Thanks Hillary.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#BL4sBwLFHA.2796@.tk2msftngp13.phx.gbl...
> I realize that - but I think you need to re-snapshot the entire
publication.
> I had problems like this with merge replication, which could only be
solved[vbcol=seagreen]
> by a complete resnapshotting and re-synchronization.
> --
> Hilary Cotter
> 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
> "ChrisR" <bla@.noemail.com> wrote in message
> news:eV5KqXvLFHA.3512@.TK2MSFTNGP15.phx.gbl...
your[vbcol=seagreen]
anyone[vbcol=seagreen]
> Replication.
is[vbcol=seagreen]
Subscriber,[vbcol=seagreen]
this
> ideas
>
sql

Immediate update/Queued update problem

I'm having a problem creating immediate update/queued update transactional
replication on my test servers. I go through the wizards and create the
publication using the advanced mode and set it up. It appears to work fine.
When I create the subscription, nothing happens. I cannot get the inital
snapshot to work and it will not create the triggers and stored procedures.
Anyone have any ideas?
Connect to your publisher in EM, expand Replication Monitor, expand the
replication agents, expand the distribution agent folder, locate the
distribution agent for your publication, right click on it, select Agent
Properties and change the job owner to sa.
Right click on your distribution agent and restart it. You may have to also
do this for the snapshot agent.
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:5289270A-7B92-45F6-9887-A78D66ACB061@.microsoft.com...
> I'm having a problem creating immediate update/queued update transactional
> replication on my test servers. I go through the wizards and create the
> publication using the advanced mode and set it up. It appears to work
> fine.
> When I create the subscription, nothing happens. I cannot get the inital
> snapshot to work and it will not create the triggers and stored
> procedures.
> Anyone have any ideas?
|||Thank for your help.
I found that there was an issue with the computer name. The computer had
gotten its name changed and after rectifying that the triggers were written.
So.. Now I have a problem on the subscriber writing back in a two phase
commit back to the publisher. I get an error during the insert that indicates
that the sa password cannot connect. I traced the call and I see the call to
openserver where the sa login is used and the password is set as an empty
string. I cannot find where to set the login and password for the writeback.
Could someone help me out?
"Hilary Cotter" wrote:

> Connect to your publisher in EM, expand Replication Monitor, expand the
> replication agents, expand the distribution agent folder, locate the
> distribution agent for your publication, right click on it, select Agent
> Properties and change the job owner to sa.
> Right click on your distribution agent and restart it. You may have to also
> do this for the snapshot agent.
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:5289270A-7B92-45F6-9887-A78D66ACB061@.microsoft.com...
>
>

Immediate Synchronization?

Is it possible in SQL Server to have replication happen immediately as
changes are made. That is, a change is made on server A, and that change is
automatically applied to server B, rather than the replication happening at
set intervals?
Thanks.
Neil,
this is possible from subscriber to publisher in the case of immediate
updating subscribers. In other cases you have to accept a degree of latency,
which can be minimised by optimisation.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Consider distributed transactions for this. You can also set your polling
interval on your log reader and distribution agent to 1s, and your latency
will be around 10-20s.
Hilary Cotter
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
"Neil" <nospam@.nospam.net> wrote in message
news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
> Is it possible in SQL Server to have replication happen immediately as
> changes are made. That is, a change is made on server A, and that change
is
> automatically applied to server B, rather than the replication happening
at
> set intervals?
> Thanks.
>
|||I'm using an Access 2000 front end which updates a lot of data through the
ODBC driver. Thus, I wouldn't be able to use distributed transactions in
every situation. Anything else that can immediately update one server from
the other?
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Consider distributed transactions for this. You can also set your polling
> interval on your log reader and distribution agent to 1s, and your latency
> will be around 10-20s.
> --
> Hilary Cotter
> 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
> "Neil" <nospam@.nospam.net> wrote in message
> news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
> is
> at
>
|||Distributed transactions are a coded solution where you do something like
this
begin trans
insert into server1.database1.dbo.table1 (charcol) values ('test')
insert into msaccessdb.table1 (charcol) values ('test')
if @.@.errornum<>0
rollback tran
else
commit tran
It can be done using MS DTC as well.
Hilary Cotter
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
"Neil" <nospam@.nospam.net> wrote in message
news:Jg9Ue.7705$4P5.4425@.newsread2.news.pas.earthl ink.net...[vbcol=seagreen]
> I'm using an Access 2000 front end which updates a lot of data through the
> ODBC driver. Thus, I wouldn't be able to use distributed transactions in
> every situation. Anything else that can immediately update one server from
> the other?
> Thanks.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
polling[vbcol=seagreen]
latency[vbcol=seagreen]
change[vbcol=seagreen]
happening
>
|||What I mean is that I'm using bound forms in the MDB to a large extent.
Updating of the data through the forms is handled automatically through the
ODBC driver without code.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uAWYaJUtFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Distributed transactions are a coded solution where you do something like
> this
> begin trans
> insert into server1.database1.dbo.table1 (charcol) values ('test')
> insert into msaccessdb.table1 (charcol) values ('test')
> if @.@.errornum<>0
> rollback tran
> else
> commit tran
> It can be done using MS DTC as well.
>
> --
> Hilary Cotter
> 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
> "Neil" <nospam@.nospam.net> wrote in message
> news:Jg9Ue.7705$4P5.4425@.newsread2.news.pas.earthl ink.net...
> polling
> latency
> change
> happening
>
|||Neil wrote:
> What I mean is that I'm using bound forms in the MDB to a large extent.
> Updating of the data through the forms is handled automatically through the
> ODBC driver without code.
>
Hi Neil,
I know that in an ideal world, you'd like your two servers to be
perfectly synchronised. Can we explore the reasons for such a desire?
If it is for failover, it may be best to look into what clustering can
do for you. That is, if you have the $s to spend, since it quickly gets
expensive. Anything you "roll-your-own" with (e.g. having an update
trigger on the server A tables that updates the server B tables) can
actually make your system *more* brittle (if server B goes down, you'll
probably lose the ability to change anything in server A).
So, my first question is why do you want this immediate
synchronisation? We may be able to offer better ideas...
Damien

Immediate Synchronization?

Is it possible in SQL Server to have replication happen immediately as
changes are made. That is, a change is made on server A, and that change is
automatically applied to server B, rather than the replication happening at
set intervals?

Thanks.Consider distributed transactions for this. You can also set your polling
interval on your log reader and distribution agent to 1s, and your latency
will be around 10-20s.

--
Hilary Cotter
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
"Neil" <nospam@.nospam.net> wrote in message
news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
> Is it possible in SQL Server to have replication happen immediately as
> changes are made. That is, a change is made on server A, and that change
is
> automatically applied to server B, rather than the replication happening
at
> set intervals?
> Thanks.|||I'm using an Access 2000 front end which updates a lot of data through the
ODBC driver. Thus, I wouldn't be able to use distributed transactions in
every situation. Anything else that can immediately update one server from
the other?

Thanks.

"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Consider distributed transactions for this. You can also set your polling
> interval on your log reader and distribution agent to 1s, and your latency
> will be around 10-20s.
> --
> Hilary Cotter
> 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
> "Neil" <nospam@.nospam.net> wrote in message
> news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
>> Is it possible in SQL Server to have replication happen immediately as
>> changes are made. That is, a change is made on server A, and that change
> is
>> automatically applied to server B, rather than the replication happening
> at
>> set intervals?
>>
>> Thanks.
>>
>>|||Distributed transactions are a coded solution where you do something like
this
begin trans
insert into server1.database1.dbo.table1 (charcol) values ('test')
insert into msaccessdb.table1 (charcol) values ('test')
if @.@.errornum<>0
rollback tran
else
commit tran

It can be done using MS DTC as well.

--
Hilary Cotter
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
"Neil" <nospam@.nospam.net> wrote in message
news:Jg9Ue.7705$4P5.4425@.newsread2.news.pas.earthl ink.net...
> I'm using an Access 2000 front end which updates a lot of data through the
> ODBC driver. Thus, I wouldn't be able to use distributed transactions in
> every situation. Anything else that can immediately update one server from
> the other?
> Thanks.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> > Consider distributed transactions for this. You can also set your
polling
> > interval on your log reader and distribution agent to 1s, and your
latency
> > will be around 10-20s.
> > --
> > Hilary Cotter
> > 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
> > "Neil" <nospam@.nospam.net> wrote in message
> > news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
> >> Is it possible in SQL Server to have replication happen immediately as
> >> changes are made. That is, a change is made on server A, and that
change
> > is
> >> automatically applied to server B, rather than the replication
happening
> > at
> >> set intervals?
> >>
> >> Thanks.
> >>
> >>|||What I mean is that I'm using bound forms in the MDB to a large extent.
Updating of the data through the forms is handled automatically through the
ODBC driver without code.

"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uAWYaJUtFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Distributed transactions are a coded solution where you do something like
> this
> begin trans
> insert into server1.database1.dbo.table1 (charcol) values ('test')
> insert into msaccessdb.table1 (charcol) values ('test')
> if @.@.errornum<>0
> rollback tran
> else
> commit tran
> It can be done using MS DTC as well.
>
> --
> Hilary Cotter
> 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
> "Neil" <nospam@.nospam.net> wrote in message
> news:Jg9Ue.7705$4P5.4425@.newsread2.news.pas.earthl ink.net...
>> I'm using an Access 2000 front end which updates a lot of data through
>> the
>> ODBC driver. Thus, I wouldn't be able to use distributed transactions in
>> every situation. Anything else that can immediately update one server
>> from
>> the other?
>>
>> Thanks.
>>
>> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>> news:e6RW67FtFHA.3252@.TK2MSFTNGP10.phx.gbl...
>> > Consider distributed transactions for this. You can also set your
> polling
>> > interval on your log reader and distribution agent to 1s, and your
> latency
>> > will be around 10-20s.
>>> > --
>> > Hilary Cotter
>> > 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
>> > "Neil" <nospam@.nospam.net> wrote in message
>> > news:8KRTe.2584$9x2.1236@.newsread3.news.pas.earthl ink.net...
>> >> Is it possible in SQL Server to have replication happen immediately as
>> >> changes are made. That is, a change is made on server A, and that
> change
>> > is
>> >> automatically applied to server B, rather than the replication
> happening
>> > at
>> >> set intervals?
>> >>
>> >> Thanks.
>> >>
>> >>
>>>>
>>|||Neil wrote:
> What I mean is that I'm using bound forms in the MDB to a large extent.
> Updating of the data through the forms is handled automatically through the
> ODBC driver without code.
Hi Neil,

I know that in an ideal world, you'd like your two servers to be
perfectly synchronised. Can we explore the reasons for such a desire?

If it is for failover, it may be best to look into what clustering can
do for you. That is, if you have the $s to spend, since it quickly gets
expensive. Anything you "roll-your-own" with (e.g. having an update
trigger on the server A tables that updates the server B tables) can
actually make your system *more* brittle (if server B goes down, you'll
probably lose the ability to change anything in server A).

So, my first question is why do you want this immediate
synchronisation? We may be able to offer better ideas...

Damien

Immediate processing of large recordset with VB

I have a table with roughly 165,000,000 records. Within
this table is a subset of roughly 6,000,000 records I
need to retrieve and process sequentially with some VB
code. These records are identified by a numeric code in
1 field. There is an index built on this field. When I
run a simple query, it takes roughly 20 minutes to return
the subset of records.
My question: Is there a way to have SQL return record 1
immediately to the VB program so it can begin
processing? As it stands when I issue the ADO command to
build the recordset I am continuosly hitting timeouts. I
assume that's because the SQL server is building the
entire recordset to return to the client. While I can
conceiveably increase my timeout(s), I'd like the client
to start processing whe the SQL server has pulled the
first record, or block of 1000 or something, rather than
wait for the entire recordset to be assembled.
Thanks for your help.You can use:
SELECT ...
FROM ...
WHERE...
OPTION (FAST n)
Where n will be number of rows in the first batch that will returned
quicker. Note that using this option can slow down overal execution time of
your query, and by a not inconsiderable amount.
Why exactly do you need to process 6 million rows sequentially in VB? There
are many things that can be done quicker in T-SQL than in VB with a set that
large.
Jacco Schalkwijk
SQL Server MVP
"DB" <dave_nospam_blair@.adelphia.net> wrote in message
news:130301c52faf$e7457bc0$a501280a@.phx.gbl...
>I have a table with roughly 165,000,000 records. Within
> this table is a subset of roughly 6,000,000 records I
> need to retrieve and process sequentially with some VB
> code. These records are identified by a numeric code in
> 1 field. There is an index built on this field. When I
> run a simple query, it takes roughly 20 minutes to return
> the subset of records.
> My question: Is there a way to have SQL return record 1
> immediately to the VB program so it can begin
> processing? As it stands when I issue the ADO command to
> build the recordset I am continuosly hitting timeouts. I
> assume that's because the SQL server is building the
> entire recordset to return to the client. While I can
> conceiveably increase my timeout(s), I'd like the client
> to start processing whe the SQL server has pulled the
> first record, or block of 1000 or something, rather than
> wait for the entire recordset to be assembled.
> Thanks for your help.|||See clause OPTION of the select statement. There is an option FAST (xxx) tha
t
can help. If you are using ADO, also recommend to increase recordset propert
y
CacheSize.
Can you explain what are you trying to do?, may be somebody can come with a
set-based solution.
AMB
"DB" wrote:

> I have a table with roughly 165,000,000 records. Within
> this table is a subset of roughly 6,000,000 records I
> need to retrieve and process sequentially with some VB
> code. These records are identified by a numeric code in
> 1 field. There is an index built on this field. When I
> run a simple query, it takes roughly 20 minutes to return
> the subset of records.
> My question: Is there a way to have SQL return record 1
> immediately to the VB program so it can begin
> processing? As it stands when I issue the ADO command to
> build the recordset I am continuosly hitting timeouts. I
> assume that's because the SQL server is building the
> entire recordset to return to the client. While I can
> conceiveably increase my timeout(s), I'd like the client
> to start processing whe the SQL server has pulled the
> first record, or block of 1000 or something, rather than
> wait for the entire recordset to be assembled.
> Thanks for your help.
>|||You can use TOP to retrieve a batch of a predefined number of rows:
SELECT TOP 10000 WITH TIES col1, col2, ...
FROM YourTable
WHERE key_col > @.key_col
/* @.key_col = The primary key of the last value processed */
AND code = 1
ORDER BY key_col
Also consider whether your VB process would be better done in SQL or
located in the middle tier if it isn't already.
David Portas
SQL Server MVP
--|||> I am continuosly hitting timeouts. I
> assume that's because the SQL server is building the
> entire recordset to return to the client.
Yeah, some of the time that you have to wait might involve query processing
time but the rest of the time involves all that data flowing across the
network onto your PC. I assume that the memory usage on the PC that the VB
program is running on goes up as the 6 million rows are returned.
When you "process" a row of data is there any way to determine that the
specific row is processed? If so you could do something like:
SELECT TOP 1000
FROM YourTable
/*pull the rows (based on the numeric code) that you are interested in*/
WHERE SomeColumn = @.TheNumericCode
/*this pulls the rows that have not been processed*/
AND HasThisRowBeenProcessed = 0
/*important to make sure that the rows are processed in the correct order*/
ORDER BY ...
Keith
"DB" <dave_nospam_blair@.adelphia.net> wrote in message
news:130301c52faf$e7457bc0$a501280a@.phx.gbl...
> I have a table with roughly 165,000,000 records. Within
> this table is a subset of roughly 6,000,000 records I
> need to retrieve and process sequentially with some VB
> code. These records are identified by a numeric code in
> 1 field. There is an index built on this field. When I
> run a simple query, it takes roughly 20 minutes to return
> the subset of records.
> My question: Is there a way to have SQL return record 1
> immediately to the VB program so it can begin
> processing? As it stands when I issue the ADO command to
> build the recordset I am continuosly hitting timeouts. I
> assume that's because the SQL server is building the
> entire recordset to return to the client. While I can
> conceiveably increase my timeout(s), I'd like the client
> to start processing whe the SQL server has pulled the
> first record, or block of 1000 or something, rather than
> wait for the entire recordset to be assembled.
> Thanks for your help.|||I wonder is possible to indetify that subset of data? I mean, using an
indentity field.
In any case, carrying up that subset into a temp table and working with that
?
See you,
"Keith Kratochvil" wrote:

> Yeah, some of the time that you have to wait might involve query processin
g
> time but the rest of the time involves all that data flowing across the
> network onto your PC. I assume that the memory usage on the PC that the V
B
> program is running on goes up as the 6 million rows are returned.
> When you "process" a row of data is there any way to determine that the
> specific row is processed? If so you could do something like:
> SELECT TOP 1000
> FROM YourTable
> /*pull the rows (based on the numeric code) that you are interested in*/
> WHERE SomeColumn = @.TheNumericCode
> /*this pulls the rows that have not been processed*/
> AND HasThisRowBeenProcessed = 0
> /*important to make sure that the rows are processed in the correct order*
/
> ORDER BY ...
> --
> Keith
>
> "DB" <dave_nospam_blair@.adelphia.net> wrote in message
> news:130301c52faf$e7457bc0$a501280a@.phx.gbl...
>

Immediate IFF() ?

Is there an Immediate IFF() function in SQL 7.0?
I'm trying to do this:
INSERTINTO Myfile(Name,IFF(TDate='',Ddate,Tdate)
SELECTd.Name, d.tdate
FROMMasterfile d, Anotherfile s
WHEREd.Name = s.Name
thx,
mac
>> Is there an Immediate IFF() function in SQL 7.0?
No, in most cases, CASE expressions would suffice. For details and other
alternatives, read through the topics CASE, COALESCE, ISNULL & NULLIF in SQL
Server Books Online.
Anith
sql

Immediate IFF() ?

Is there an Immediate IFF() function in SQL 7.0?
I'm trying to do this:
INSERT INTO Myfile(Name,IFF(TDate='',Ddate,Tdate)
SELECT d.Name, d.tdate
FROM Masterfile d, Anotherfile s
WHERE d.Name = s.Name
thx,
mac>> Is there an Immediate IFF() function in SQL 7.0?
No, in most cases, CASE expressions would suffice. For details and other
alternatives, read through the topics CASE, COALESCE, ISNULL & NULLIF in SQL
Server Books Online.
--
Anith

Immediate IFF() ?

Is there an Immediate IFF() function in SQL 7.0?
I'm trying to do this:
INSERT INTO Myfile(Name,IFF(TDate='',Ddate,Tdate)
SELECT d.Name, d.tdate
FROM Masterfile d, Anotherfile s
WHERE d.Name = s.Name
thx,
mac>> Is there an Immediate IFF() function in SQL 7.0?
No, in most cases, CASE expressions would suffice. For details and other
alternatives, read through the topics CASE, COALESCE, ISNULL & NULLIF in SQL
Server Books Online.
Anith

Immediate Help Needed Please!

Hi All,
This is a repost from last Friday
Has anyone used the "Jump to Report" to pull up any one report from a
selection of 7 other sub-reports.
I am trying to pull up a specific detail report from 7 sub-reports using an
individual pin # and the "report type" from the main report.
These sub-report types are grouped from type 1 thru 7. I have used the the
"IIF" statement and the "CASE" statement within the (Fx in the Advanced Text
Box Properties) but neither of the statements worked for me. Any help?
Below is my case Statement...
CASE (WHEN ReportType = 1 THEN 'First Report.rdl' WHEN ReportType = 2
THEN 'Second Report.rdl' WHEN ReportType = 3 THEN 'Third Report.rdl' WHEN
ReportType = 4 THEN Fourth Report.rdl' WHEN ReportType = 5 THEN 'Fifth
Report rdl' WHEN ReportType = 6 THEN Sixth Report .rdl' WHEN ReportType = 7 THEN Seventh Report .rdl') ENDWhat is the jump to producing now? Is it jumping to any report? Are you
getting an error message?
"Ben Cole" wrote:
> Hi All,
> This is a repost from last Friday
> Has anyone used the "Jump to Report" to pull up any one report from a
> selection of 7 other sub-reports.
> I am trying to pull up a specific detail report from 7 sub-reports using an
> individual pin # and the "report type" from the main report.
> These sub-report types are grouped from type 1 thru 7. I have used the the
> "IIF" statement and the "CASE" statement within the (Fx in the Advanced Text
> Box Properties) but neither of the statements worked for me. Any help?
> Below is my case Statement...
>
> CASE (WHEN ReportType = 1 THEN 'First Report.rdl' WHEN ReportType = 2
> THEN 'Second Report.rdl' WHEN ReportType = 3 THEN 'Third Report.rdl' WHEN
> ReportType = 4 THEN Fourth Report.rdl' WHEN ReportType = 5 THEN 'Fifth
> Report rdl' WHEN ReportType = 6 THEN Sixth Report .rdl' WHEN ReportType => 7 THEN Seventh Report .rdl') END
>|||Daw,
The error msge I'm getting is: "Report can not be compiled. Verify the
report name and that the report is in the current project."
I made sure the report names are all in the current project and are viewable.
Thx
"daw" wrote:
> What is the jump to producing now? Is it jumping to any report? Are you
> getting an error message?
> "Ben Cole" wrote:
> > Hi All,
> > This is a repost from last Friday
> >
> > Has anyone used the "Jump to Report" to pull up any one report from a
> > selection of 7 other sub-reports.
> > I am trying to pull up a specific detail report from 7 sub-reports using an
> > individual pin # and the "report type" from the main report.
> >
> > These sub-report types are grouped from type 1 thru 7. I have used the the
> > "IIF" statement and the "CASE" statement within the (Fx in the Advanced Text
> > Box Properties) but neither of the statements worked for me. Any help?
> > Below is my case Statement...
> >
> >
> >
> > CASE (WHEN ReportType = 1 THEN 'First Report.rdl' WHEN ReportType = 2
> > THEN 'Second Report.rdl' WHEN ReportType = 3 THEN 'Third Report.rdl' WHEN
> > ReportType = 4 THEN Fourth Report.rdl' WHEN ReportType = 5 THEN 'Fifth
> > Report rdl' WHEN ReportType = 6 THEN Sixth Report .rdl' WHEN ReportType => > 7 THEN Seventh Report .rdl') END
> >|||I tested it with iif and got it to work with the following format:
=iif( Fields!MyField.Value ="MyValue", "Report 1", "Report 2")
I don't think that you can use CASE in the Edit Expression window. This
example assumes that your reports are in the same project. You don't need
.rdl at the end of the names - I grabbed the report names by expanding
Constants in the Edit Expression window.
Hope that helps!
"Ben Cole" wrote:
> Hi All,
> This is a repost from last Friday
> Has anyone used the "Jump to Report" to pull up any one report from a
> selection of 7 other sub-reports.
> I am trying to pull up a specific detail report from 7 sub-reports using an
> individual pin # and the "report type" from the main report.
> These sub-report types are grouped from type 1 thru 7. I have used the the
> "IIF" statement and the "CASE" statement within the (Fx in the Advanced Text
> Box Properties) but neither of the statements worked for me. Any help?
> Below is my case Statement...
>
> CASE (WHEN ReportType = 1 THEN 'First Report.rdl' WHEN ReportType = 2
> THEN 'Second Report.rdl' WHEN ReportType = 3 THEN 'Third Report.rdl' WHEN
> ReportType = 4 THEN Fourth Report.rdl' WHEN ReportType = 5 THEN 'Fifth
> Report rdl' WHEN ReportType = 6 THEN Sixth Report .rdl' WHEN ReportType => 7 THEN Seventh Report .rdl') END
>|||Thx daw for your help... I did try your example and still got the same error
msge.
I hope I'm not asking for too much here?
I was wondering if you can convert my CASE statement below with IIF
statement so I see what it actually looks like
thx for all your help!!
"daw" wrote:
> I tested it with iif and got it to work with the following format:
> =iif( Fields!MyField.Value ="MyValue", "Report 1", "Report 2")
> I don't think that you can use CASE in the Edit Expression window. This
> example assumes that your reports are in the same project. You don't need
> .rdl at the end of the names - I grabbed the report names by expanding
> Constants in the Edit Expression window.
> Hope that helps!
> "Ben Cole" wrote:
> > Hi All,
> > This is a repost from last Friday
> >
> > Has anyone used the "Jump to Report" to pull up any one report from a
> > selection of 7 other sub-reports.
> > I am trying to pull up a specific detail report from 7 sub-reports using an
> > individual pin # and the "report type" from the main report.
> >
> > These sub-report types are grouped from type 1 thru 7. I have used the the
> > "IIF" statement and the "CASE" statement within the (Fx in the Advanced Text
> > Box Properties) but neither of the statements worked for me. Any help?
> > Below is my case Statement...
> >
> >
> >
> > CASE (WHEN ReportType = 1 THEN 'First Report.rdl' WHEN ReportType = 2
> > THEN 'Second Report.rdl' WHEN ReportType = 3 THEN 'Third Report.rdl' WHEN
> > ReportType = 4 THEN Fourth Report.rdl' WHEN ReportType = 5 THEN 'Fifth
> > Report rdl' WHEN ReportType = 6 THEN Sixth Report .rdl' WHEN ReportType => > 7 THEN Seventh Report .rdl') END
> >|||Try this:
=iif(Fields!ReportType.Value =1, "First Report",iif(Fields!ReportType.Value
=2, "Second Report",iif(Fields!ReportType.Value =3, "Third Report",
iif(Fields!ReportType.Value =4,"Fourth Report" ,iif(Fields!ReportType.Value
=5, "Fifth Report", iif(Fields!ReportType.Value =6,"Sixth Report","Seventh
Report"))))))
This statement assumes that if the ReportType value is not 1 through 6, that
it is 7 and will jump to the seventh report. Good luck!
"Ben Cole" wrote:
> Thx daw for your help... I did try your example and still got the same error
> msge.
> I hope I'm not asking for too much here?
> I was wondering if you can convert my CASE statement below with IIF
> statement so I see what it actually looks like
> thx for all your help!!
> "daw" wrote:
> > I tested it with iif and got it to work with the following format:
> >
> > =iif( Fields!MyField.Value ="MyValue", "Report 1", "Report 2")
> >
> > I don't think that you can use CASE in the Edit Expression window. This
> > example assumes that your reports are in the same project. You don't need
> > .rdl at the end of the names - I grabbed the report names by expanding
> > Constants in the Edit Expression window.
> >
> > Hope that helps!
> >
> > "Ben Cole" wrote:
> >
> > > Hi All,
> > > This is a repost from last Friday
> > >
> > > Has anyone used the "Jump to Report" to pull up any one report from a
> > > selection of 7 other sub-reports.
> > > I am trying to pull up a specific detail report from 7 sub-reports using an
> > > individual pin # and the "report type" from the main report.
> > >
> > > These sub-report types are grouped from type 1 thru 7. I have used the the
> > > "IIF" statement and the "CASE" statement within the (Fx in the Advanced Text
> > > Box Properties) but neither of the statements worked for me. Any help?
> > > Below is my case Statement...
> > >
> > >
> > >
> > > CASE (WHEN ReportType = 1 THEN 'First Report.rdl' WHEN ReportType = 2
> > > THEN 'Second Report.rdl' WHEN ReportType = 3 THEN 'Third Report.rdl' WHEN
> > > ReportType = 4 THEN Fourth Report.rdl' WHEN ReportType = 5 THEN 'Fifth
> > > Report rdl' WHEN ReportType = 6 THEN Sixth Report .rdl' WHEN ReportType => > > 7 THEN Seventh Report .rdl') END
> > >|||A BIG Thx to you, daw!!! I finally got it to work!!
Thx again!
"daw" wrote:
> Try this:
> =iif(Fields!ReportType.Value =1, "First Report",iif(Fields!ReportType.Value
> =2, "Second Report",iif(Fields!ReportType.Value =3, "Third Report",
> iif(Fields!ReportType.Value =4,"Fourth Report" ,iif(Fields!ReportType.Value
> =5, "Fifth Report", iif(Fields!ReportType.Value =6,"Sixth Report","Seventh
> Report"))))))
> This statement assumes that if the ReportType value is not 1 through 6, that
> it is 7 and will jump to the seventh report. Good luck!
> "Ben Cole" wrote:
> > Thx daw for your help... I did try your example and still got the same error
> > msge.
> >
> > I hope I'm not asking for too much here?
> > I was wondering if you can convert my CASE statement below with IIF
> > statement so I see what it actually looks like
> > thx for all your help!!
> >
> > "daw" wrote:
> >
> > > I tested it with iif and got it to work with the following format:
> > >
> > > =iif( Fields!MyField.Value ="MyValue", "Report 1", "Report 2")
> > >
> > > I don't think that you can use CASE in the Edit Expression window. This
> > > example assumes that your reports are in the same project. You don't need
> > > .rdl at the end of the names - I grabbed the report names by expanding
> > > Constants in the Edit Expression window.
> > >
> > > Hope that helps!
> > >
> > > "Ben Cole" wrote:
> > >
> > > > Hi All,
> > > > This is a repost from last Friday
> > > >
> > > > Has anyone used the "Jump to Report" to pull up any one report from a
> > > > selection of 7 other sub-reports.
> > > > I am trying to pull up a specific detail report from 7 sub-reports using an
> > > > individual pin # and the "report type" from the main report.
> > > >
> > > > These sub-report types are grouped from type 1 thru 7. I have used the the
> > > > "IIF" statement and the "CASE" statement within the (Fx in the Advanced Text
> > > > Box Properties) but neither of the statements worked for me. Any help?
> > > > Below is my case Statement...
> > > >
> > > >
> > > >
> > > > CASE (WHEN ReportType = 1 THEN 'First Report.rdl' WHEN ReportType = 2
> > > > THEN 'Second Report.rdl' WHEN ReportType = 3 THEN 'Third Report.rdl' WHEN
> > > > ReportType = 4 THEN Fourth Report.rdl' WHEN ReportType = 5 THEN 'Fifth
> > > > Report rdl' WHEN ReportType = 6 THEN Sixth Report .rdl' WHEN ReportType => > > > 7 THEN Seventh Report .rdl') END
> > > >

IMMEDIATE ATTENTION PLEASE.....

hi all..
hope u guys can help me out here.....
'm new to .net so hope to get all the basic infomr that would b required...

'm on a proj..basically a data mining proj...
'm using sql 2005 enterprise edn's analysis services to perform my data minning tasks...
here is what i'd like to know....
i've been following the sql online tutorial book...
n as per the tutorial on data minning...i have created a data source ... based on the decision tree algorithm i have even obtained some results..
now.....here is the problem..

i'd like to know if i can actually get all these obtained patterns onto a webpage...
i mean can i actually use asp.net n achieve this task of migrating the patterns generated at the bids to a webpage?

please guys if i can do so...then do tell me how to do so...
my proj dline is in 2 weeks....i need desperate help....
'm waitin...

here is how i vusualize my goal..... web pages that display patterns generated after an analysis has performed......

thxxxx... a ton
hanish

AS includes a set of data mining viewer controls intended for this. You can also query the mining models from RS.

See this topics in Books Online.

Using Analysis Services DMX Query Designer

And this link to download the Data Mining controls:

http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

The mining controls are halfway down the page.

|||

...umm...alrite thanks...

but the thing is i really don't wan't to open bids n check out the report.....

i am asking if there is any way how i can view the obtained patters at a report on a webpage without having to open

BIDS ....ll explain my problem ...........

my task is to illustrate data minning using asp.net as the front end and SQL server 2005 entp.as back end.....

BIDS will take care of all the data minning operations that i need to perform...and will generate reports corresponding to various data minning models.......

now i intend to create a UI using asp.net(c# codin.) and then link these web-pages to the already obtained reports.....can i do it without having the user open BIDS?

plzzzzzz plzzzz guys help me.....

thx.....

|||

That functionality is exactly what the data mining controls provide. The user doesn't have to use BIDS to see the results. For that matter, if you develop a report in Reporting Services and deploy it, the user doesn't need BIDS.

For your task, you need to look at the data mining controls. Or you could query directly through ADOMD (an OLEDB provider), but the mining controls handle the visual display for you.

|||

hi jwelch

thx for ur instructions for my problem.......

as per u instructed i d/loaded Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider which i suppose helps provide the data mining controls....... here is an honest appeal...after having installed it i havn't really seen any change or havn't really been able to discover the data mining controls...

i am certain that it is because i am totally new to sql server, i am getting a little nervous if i can really do this task, please would u mind helping me in achieving my task......

i've done minning on the adventure datawarehouse and now have obtained the patterns which i can view in BIDS......i would now like to know the steps i should follow to make use of the data mining controls by which i can migrate these results onto a browser.......you have told me this is possible ...please i have no other option but to take your help do consider my request ......

waiting for your reply..

|||

any body here ...

plzzzzzz help me in figurin my way out.....

mr.john u der?

|||

No, it's not the OLE DB provider. It's the data mining viewer controls. They are about 4 items below the OLE DB provider.

http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

Microsoft SQL Server 2005 Datamining Viewer Controls

The Data Mining Web Controls Library is a set of Microsoft Windows Forms controls that enable software developers to display data mining models created using Microsoft SQL Server 2005 Analysis Services in their client-side applications. The controls in this library display the patterns that are contained in Analysis Services mining models.
|||

hj wrote:

any body here ...

plzzzzzz help me in figurin my way out.....

mr.john u der?

hanish,

you really need a course on forum ettiquette: don't demand things, don't write all in capitals (your subject line), and don't expect people to do your work for you.

What exactly are you wanting? It seems to me that you want to know how to run or request reports from within a standard ASP.Net page?

|||

i do apologize ,

yup sluggy u got my prob rite......im tryin to run the results i got after the minin operation from within a std asp.net page..

hey..n thx ...mr.john....

|||

hi ...'m back again with my nt so good doubts..

;m stuck on thiz one thing mr.john...........

after havning installed the data mining viewer controls what is my next step....i mean i don't even see these contrls in my web dvlpr toolbar....

after going tro some threads here..i figured out that the nxt step is to add the viewer contrls into the toolkit of visual c# web dvplr.....

but ;m not succesful in doing so.....

jus like i told u before ...my task is to bring out these patters onto a web page.........

one more doubt......

is there any tutorial about what each of these web contrl does? whr can i learn more bout these controls......are there any sample videos i could get access to, to actually know more about how to use these controls....

please help me ....thx a lot

hanish|||

hj wrote:

hi ...'m back again with my nt so good doubts..

;m stuck on thiz one thing mr.john...........

after havning installed the data mining viewer controls what is my next step....i mean i don't even see these contrls in my web dvlpr toolbar....

after going tro some threads here..i figured out that the nxt step is to add the viewer contrls into the toolkit of visual c# web dvplr.....

but ;m not succesful in doing so.....

jus like i told u before ...my task is to bring out these patters onto a web page.........

one more doubt......

is there any tutorial about what each of these web contrl does? whr can i learn more bout these controls......are there any sample videos i could get access to, to actually know more about how to use these controls....

please help me ....thx a lot

hanish

Have you looked in your C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\DataMining\Data Mining Web Controls folder? You may need to install the SQL Server Samples to use this.

|||

yup...dat is de exact prob....i did install dm viewer controls but den din find a samples folder under \Program Files\Microsoft SQL Server\90.......

now i did install all the samples that came with the sql cd..could u tell me whr i could download these samples from the net.....

my sincere thanks mr john..

feel like ur the only one replyin to my posts here..

thx again.......

hanish

|||one more question...'m working with sql server 2005 enterprise edn........do i hav to install sp2 for sql server to achieve my tasks...|||

hj wrote:

yup...dat is de exact prob....i did install dm viewer controls but den din find a samples folder under \Program Files\Microsoft SQL Server\90.......

now i did install all the samples that came with the sql cd..could u tell me whr i could download these samples from the net.....

my sincere thanks mr john..

feel like ur the only one replyin to my posts here..

thx again.......

hanish

This link has the lastest sample applications for SQL Server.

http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

|||

hj wrote:

one more question...'m working with sql server 2005 enterprise edn........do i hav to install sp2 for sql server to achieve my tasks...

I don't think it is required. However, the samples I referenced above have been updated for SP2.

IMMEDIATE ATTENTION PLEASE!!!

hi all..
hope u guys can help me out here.....
'm new to .net so hope to get all the basic infomr that would b required...

'm on a proj..basically a data mining proj...
'm using sql 2005 enterprise edn's analysis services to perform my data minning tasks...
here is what i'd like to know....
i've been following the sql online tutorial book...
n as per the tutorial on data minning...i have created a data source ... based on the decision tree algorithm i have even obtained some results..
now.....here is the problem..

i'd like to know if i can actually get all these obtained patterns onto a webpage...
i mean can i actually use asp.net n achieve this task of migrating the patterns generated at the bids to a webpage?

please guys if i can do so...then do tell me how to do so...
my proj dline is in 2 weeks....i need desperate help....
'm waitin...
thxxxx... a ton
hanish

i'd like to code in c#...so plzz tell me a way out of this prob.....|||

if you dump your results into a table you can report against it using .net tools like OWC (i. e. FrontPage, Excel, RS).

Also, here is the link to the article on how to build a model dynamically:

http://www.aspnetpro.com/newsletterarticle/2004/10/asp200410ri_l/asp200410ri_l.asp

|||

my task is to illustrate data minning using asp.net as the front end and SQL server 2005 entp.as back end.....

BIDS will take care of all the data minning operations that i need to perform...and will generate reports corresponding to various data minning models.......

now i intend to create a UI using asp.net(c# codin.) and then link these web-pages to the already obtained reports.....can i do it without having the user open BIDS?

plzzzzzz plzzzz guys help me.....

thx.....

|||

Included with SQL Server as a sample are the sample Data Mining web viewers - I think this should give you what you need. You can see them in action by launching the sample from here http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/52.aspx

Also you can render your results as a report using the example here:

http://blogs.msdn.com/jamiemac/archive/2006/06/22/642541.aspx

|||hello Hanish,,,
i need decision tree code in c#,if u have plz help me.........
nd reply me at innocent_desire_89@.hotmail.com,waitng 4 ur reply
tcsql