Order of Articles Replicated

Is there any way to specify the order of the articles replicated? What is happending is my merge replication is erroring because a table is being replicated that contains a computed column based on a user-defined function, but the user-defined fuction hasn't been replicated yet.

But I'm not sure replicating the user-defined functions first would solve the problem because they would probably blow up because the tables upon which they are based haven't been replicated yet.

So, how has anyone else solved this?

Thanks...

Scott

You can specify a script to run before or after the snapshot is applied. Create a script that checks to see if your function exists and create it if it does not. Then set that to run before the snapshot.

Go to the properties of your publication and select Subscription Options see section Run additional scripts.

I am not sure you can specify the order of the deployment but this would solve your probem.

Martin

|||Another option is to use sp_addscriptexec for in-flight scenarios.|||But to answer your original question, you can specify article ordering of tables but not other types of objects like views, functions, procs, etc. We're looking to fix this.

Order of Articles Replicated

Is there any way to specify the order of the articles replicated? What is happending is my merge replication is erroring because a table is being replicated that contains a computed column based on a user-defined function, but the user-defined fuction hasn't been replicated yet.

But I'm not sure replicating the user-defined functions first would solve the problem because they would probably blow up because the tables upon which they are based haven't been replicated yet.

So, how has anyone else solved this?

Thanks...

Scott

You can specify a script to run before or after the snapshot is applied. Create a script that checks to see if your function exists and create it if it does not. Then set that to run before the snapshot.

Go to the properties of your publication and select Subscription Options see section Run additional scripts.

I am not sure you can specify the order of the deployment but this would solve your probem.

Martin

|||Another option is to use sp_addscriptexec for in-flight scenarios.|||But to answer your original question, you can specify article ordering of tables but not other types of objects like views, functions, procs, etc. We're looking to fix this.sql

Order in which datasets executed

Can one specify the prder in which datasets are executed in the report?
Thanks
Sanjeev
Order in which datasets executedI dont know why you want order of execution, because SSRS runs all the query
and takes it to a Intermediate format, which is ready for rendering in any
format. So all the substitution parameter happens before creating this format.
Amarnath
"Sanjeev Rampersad" wrote:
> Can one specify the prder in which datasets are executed in the report?
> Thanks
> Sanjeev
> Order in which datasets executed
>
>

ORDER BY, CASE, with multiple columns

I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?

Thanks

SELECT ...
ORDER BY (CASE Lower(@.SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)

Also, i realize that in your order by statement, when you use CASE, all of your columns have to be the same data type.

So in ORDER BY clause above, i am attempting to order by "prodprice" as one of the possibilities. This produces the error:Error converting data type varchar to numeric.

The whole IDEA of a case statement is to avoid opening yourself to injection attacks by Dynamic Execution.

So...how can you use the case statement to order by multiple columns, and to order with different datatypes?|||Not sure it this helps butview post 386101 discusses something close to your question. Maybe the method discussed near the bottom can be adapted.|||Right, so you'd have something like this:

SET @.SortExpression = Lower(@.SortExpression)
SELECT ...
ORDER BY
CASE WHEN @.SortExpression = 'prodname' THEN prodname END,
CASE WHEN @.SortExpression = 'prodname' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodsize END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodname END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodname END,
prodcompany,
prodname

I'm not exactly sure what that'll do to performance. It'd be worth it to see what the execution plan says.

Terri|||that alleviated some of my problem. Thanks.

How exactly do i test the excecution plan? or check to see if it's compiling all the way?

Can i use a SQL Trace?|||Check out this article:SQL Server Query Execution Plan Analysis.

Terri

Oracle 9i -> SQL Server 2005: Schema_option parameter when adding an article to a publication

First of all; My Oracle publication works fine when I don't explicit specify the shema_option parameter for the articles I'm adding to the publication. The reason why I then want to explicit specify the parameter is as following.

I'm developing a replication solution to get data from our production server (Oracle) to our Data Warehouse (SQL Server). The SQL Server (and the Data Warehouse code) uses the SQL_Latin1_General_CP1_CI_AS collation. When I don't explicit specify the schema_option, the nvarchar columns of the replicated tables are created using the SQL_Latin1_General_CP1_CS_AS collation and this results in some comparison errors, when for instance a select statement is trying to compare two nvarchar strings using different collations.

I've tried to specify the schema_option parameter as "@.schema_option = 0x80" (Replicates primary key constraints.) to avoid the use of the SQL_Latin1_General_CP1_CS_AS collation when creating the destination tables - I'm not sure it's enough? No matter what, I'm getting an error when I'm doing it (see below).

Message
2006-07-13 12:00:15.529 Applied script 'ITEMTRANSLATION_2.sch'
2006-07-13 12:00:15.544 Bulk copying data into table 'ITEMTRANSLATION'
2006-07-13 12:00:15.544 Agent message code 20037. The process could not bulk copy into table '"ITEMTRANSLATION"'.
2006-07-13 12:00:15.591 Category:NULL
Source: Microsoft SQL Native Client
Number: 208
Message: Invalid object name 'ITEMTRANSLATION'.
2006-07-13 12:00:15.591 Category:NULL
Source:
Number: 20253

The questions are now whether I actually have a schema_option alternative for Oracle Publishing? If so, what is the solution, and eventually how can I avoid the error stated above?

If I'm not able to avoid the article columns getting created with the "wrong" collation, is there then any other obviously solution to the problem?

Thanks!
Best regards,

JB

Ok, now I've found out how to use more of those schema options together.

The default value for Oracle Publications (according to BOL) is 0x050D3. I've taken that value and subtracted the value 0x1000 (Replicates column-level collation.) which I think is causing the problem - that gives me a value of 0x40D2 which I've tried to use. Though I get the same error as stated above, so actually I'm not that very further...

There is a note to the "0x1000 Replicates column-level collation" schema option saying that "This option should be set for Oracle Publishers to enable case-sensitive comparisons.". Does that mean it's a required option (and is therefore causing the problem)? Because then I think I have a serious challenge here?

Jeppe

|||

Hi JB,

You can start trouble-shooting by checking the following:

1) Is the ITEMTRANSLATION table created at the subscriber?

2) Is there anything obviously amiss with the "CREATE TABLE" statement in ITEMTRANSLATION_2.sch? (Would be great if you can post it here so we can have a look.)

-Raymond

|||

Argh... me not paying enough attention before the morning caffeine kicks in :) You should change your schema option from 0x40D2 to 0x40D3 otherwise the create table statement will not be scripted.

-Raymond

|||

Thank you Raymond!

I think that solved the main part of the problem :) I still got a problem with the table itemtranslation, but now it's a primary key violation problem, and I think it's caused by a nvarchar column in the primary key constraint. I'll have to look deeper into that, before I'll bug you with that problem too :)

Jeppe

Oracle 9i -> SQL Server 2005: Schema_option parameter when adding an article to a publica

First of all; My Oracle publication works fine when I don't explicit specify the shema_option parameter for the articles I'm adding to the publication. The reason why I then want to explicit specify the parameter is as following.

I'm developing a replication solution to get data from our production server (Oracle) to our Data Warehouse (SQL Server). The SQL Server (and the Data Warehouse code) uses the SQL_Latin1_General_CP1_CI_AS collation. When I don't explicit specify the schema_option, the nvarchar columns of the replicated tables are created using the SQL_Latin1_General_CP1_CS_AS collation and this results in some comparison errors, when for instance a select statement is trying to compare two nvarchar strings using different collations.

I've tried to specify the schema_option parameter as "@.schema_option = 0x80" (Replicates primary key constraints.) to avoid the use of the SQL_Latin1_General_CP1_CS_AS collation when creating the destination tables - I'm not sure it's enough? No matter what, I'm getting an error when I'm doing it (see below).

Message
2006-07-13 12:00:15.529 Applied script 'ITEMTRANSLATION_2.sch'
2006-07-13 12:00:15.544 Bulk copying data into table 'ITEMTRANSLATION'
2006-07-13 12:00:15.544 Agent message code 20037. The process could not bulk copy into table '"ITEMTRANSLATION"'.
2006-07-13 12:00:15.591 Category:NULL
Source: Microsoft SQL Native Client
Number: 208
Message: Invalid object name 'ITEMTRANSLATION'.
2006-07-13 12:00:15.591 Category:NULL
Source:
Number: 20253

The questions are now whether I actually have a schema_option alternative for Oracle Publishing? If so, what is the solution, and eventually how can I avoid the error stated above?

If I'm not able to avoid the article columns getting created with the "wrong" collation, is there then any other obviously solution to the problem?

Thanks!
Best regards,

JB

Ok, now I've found out how to use more of those schema options together.

The default value for Oracle Publications (according to BOL) is 0x050D3. I've taken that value and subtracted the value 0x1000 (Replicates column-level collation.) which I think is causing the problem - that gives me a value of 0x40D2 which I've tried to use. Though I get the same error as stated above, so actually I'm not that very further...

There is a note to the "0x1000 Replicates column-level collation" schema option saying that "This option should be set for Oracle Publishers to enable case-sensitive comparisons.". Does that mean it's a required option (and is therefore causing the problem)? Because then I think I have a serious challenge here?

Jeppe

|||

Hi JB,

You can start trouble-shooting by checking the following:

1) Is the ITEMTRANSLATION table created at the subscriber?

2) Is there anything obviously amiss with the "CREATE TABLE" statement in ITEMTRANSLATION_2.sch? (Would be great if you can post it here so we can have a look.)

-Raymond

|||

Argh... me not paying enough attention before the morning caffeine kicks in :) You should change your schema option from 0x40D2 to 0x40D3 otherwise the create table statement will not be scripted.

-Raymond

|||

Thank you Raymond!

I think that solved the main part of the problem :) I still got a problem with the table itemtranslation, but now it's a primary key violation problem, and I think it's caused by a nvarchar column in the primary key constraint. I'll have to look deeper into that, before I'll bug you with that problem too :)

Jeppe