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)
>
>

I'm retarded and need help with Derived Column or Script Task

I am moving data from an oracle database to sql server. Two of the source fields are dates, which sometimes contain values I know to be incorrect ('0001/01/01' and '1900/01/01').

I'd like to use either the derived column or script task (or any other appropriate one) to update these incorrect values (in both columns) to null before inserting into sql server, smalldatetime field.

In sql 2000 dts, I would simply use a VBScript IF statement, but I'm somewhat clueless when it comes to .Net.

Does anyone have any ideas or sample code that may help?

Thank you much.

In a derived column you could do a comparison expression such as:

[ORACLE_COL] == (DT_DBTIMESTAMP)"0001/01/01" || [ORACLE_COL] == (DT_DBTIMESTAMP)"1900/01/01" ? NULL(DT_DBTIMESTAMP) : [ORACLE_COL]

|||

That worked perfectly, thank you. However it also looks like it's slowing me down quite a bit. I'm moving a lot of data, 16 million records, from oracle to sql server. Moving this into a temp table without the derived column task only took 8 minutes. After adding the new task I can see it would take far longer.

Do you know of any good learning resources for SSIS besides Books on Line?

Thank you.

|||

www.sqlis.com is the recognised community site.

The guys that run it (Darren and Allan) are also running a new wiki site: http://wiki.sqlis.com/

-Jamie

|||I found that the WROX press book "SQL Server 2005 Integration Services",, ISBN 0-7645-8435-9 is a very good resource. I normally do not like using anything other than online resources either.

Im baffled by the single quotes with STMT

Hi,

Don't worry about the vars, they are defined,
the following line give me an err of "Incorrect syntax near '.'."
Goal: to rename nonstardard column name.

EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%[^A-Za-z0-9_#$@.]%','')',
'COLUMN';

Thanks.Doug Baroter (qwert12345@.boxfrog.com) writes:
> Don't worry about the vars, they are defined,
> the following line give me an err of "Incorrect syntax near '.'."
> Goal: to rename nonstardard column name.
> EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%[^A-Za-z0-9_#$@.]%','')',
> 'COLUMN';

You can only pass constants and variables as parameters to stored procedures.
You cannot pass an expression as a parameter, but you must put everything
in variables.

Furthermore, replace() only handles fixed strings, and does not have
any capacbilities to find patterns.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Thanks for the quick response. Please my further question below.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns941BEC43659B9Yazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.boxfrog.com) writes:
> > Don't worry about the vars, they are defined,
> > the following line give me an err of "Incorrect syntax near '.'."
> > Goal: to rename nonstardard column name.
> > EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%[^A-Za-z0-9_#$@.]%','')',
> > 'COLUMN';
> You can only pass constants and variables as parameters to stored procedures.
> You cannot pass an expression as a parameter, but you must put everything
> in variables.
Are you saying I should do something like
set @.tbuffer = '''+@.tbuffer+''';
set @.cbuffer = '''+@.cbuffer+''';
and then
EXEC sp_rename @.tbuffer+'.['+@.cbuffer+']' ...

> Furthermore, replace() only handles fixed strings, and does not have
> any capacbilities to find patterns.
Since replace can't do the job for this case, what other option do I
have to remove the non-standard character(s), I've looked at charindex
and stuff function, could they fit in here or a better way to do it?|||Doug Baroter (qwert12345@.boxfrog.com) writes:
> Are you saying I should do something like
> set @.tbuffer = '''+@.tbuffer+''';
> set @.cbuffer = '''+@.cbuffer+''';
> and then
> EXEC sp_rename @.tbuffer+'.['+@.cbuffer+']' ...

Rather:

SELECT @.old_name = 'tbl.' + <whatever>
SELECT @.new_name = <whichever>
EXEC sp_rename @.old_name, @.new_name, 'column'

> Since replace can't do the job for this case, what other option do I
> have to remove the non-standard character(s), I've looked at charindex
> and stuff function, could they fit in here or a better way to do it?

Unfortunately, SQL is poor for this task. You are probably better off
if you write some program in Perl, Visual Basic, VBscript, C or whatever
is your favourite client language.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland, pls see below.
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns941D8E88A84Yazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.boxfrog.com) writes:
> > Are you saying I should do something like
> > set @.tbuffer = '''+@.tbuffer+''';
> > set @.cbuffer = '''+@.cbuffer+''';
> > and then
> > EXEC sp_rename @.tbuffer+'.['+@.cbuffer+']' ...
> Rather:

> SELECT @.old_name = 'tbl.' + <whatever>
> SELECT @.new_name = <whichever>
> EXEC sp_rename @.old_name, @.new_name, 'column'
Unfortunately the tbl name is dynamically determined. But as you
recommended below, I just use a non-SQL language to take care of the
whole problem except one minor one, that is, I haven't got the RegExp
part fully completed.

> > Since replace can't do the job for this case, what other option do I
> > have to remove the non-standard character(s), I've looked at charindex
> > and stuff function, could they fit in here or a better way to do it?
> Unfortunately, SQL is poor for this task. You are probably better off
> if you write some program in Perl, Visual Basic, VBscript, C or whatever
> is your favourite client language.|||Doug Baroter (qwert12345@.boxfrog.com) writes:
>> SELECT @.old_name = 'tbl.' + <whatever>
>> SELECT @.new_name = <whichever>
>> EXEC sp_rename @.old_name, @.new_name, 'column'
> Unfortunately the tbl name is dynamically determined.

SELECT @.old_name = @.tbl + '.' + @.column

The point is that when you come to sp_rename you must have a single
value.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK. Thanks.
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns941D616A4DA25Yazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.boxfrog.com) writes:
> >> SELECT @.old_name = 'tbl.' + <whatever>
> >> SELECT @.new_name = <whichever>
> >> EXEC sp_rename @.old_name, @.new_name, 'column'
> > Unfortunately the tbl name is dynamically determined.
> SELECT @.old_name = @.tbl + '.' + @.column
> The point is that when you come to sp_rename you must have a single
> value.