ORDER BY in subquery of a UNION fails ??

Hi all,

I have the following UNION ALL statement that is my attempt to gather data for the past 5 weekdays (adding a "dummy" row for today's data).

I want the final output to end up in descending order, so for today, I would want today first, then Tuesday, then Monday, then Friday, then Thursday (provided there is data for each sequential day - if not, you get the idea, I want to select back to get the latest 5 days, most recent to oldest).

This select fails, because it doesn't like the ORDER BY in the subqueryselect
CASE
WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN
'FRI'
END AS Dow, 'N/A' AS Freight
UNION ALL
(select top 4
CASE
WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN
'FRI'
END as DOW,
CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight
from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc )

I know you can't use an ORDER BY in a subquery, UNLESS the subquery also uses a TOP n (which this one does)...but does anyone know why this isn't liking my code?

I got the select to work the way I want it to by doing the following (really UGLY) code...SELECT U.DOW, U.Freight FROM
((select
GETDATE() as [OrderDate],
CASE
WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN
'FRI'
END AS Dow, 'N/A' AS Freight )
UNION ALL
(select h.OrderDate as [OrderDate], h.DOW, h.Freight FROM
(select top 4
[OrderDate] as [OrderDate],
CASE
WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN
'FRI'
END as DOW,
CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight
from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc ) H)) U
order by OrderDate descbut am still confounded about why my original sub-select is rejected with such impunity.

My confusion seems likely related to understanding the set theory or basic concepts of the building of the select/Union rather than the way I am using the ORDER BY syntax, but I just can't seem to explain it to myself.

Thoughts?
Thanks!EDIT: Oops - retracted.|||dang, and I had my blistering rebuttal all ready ;)|||Damn that sucks. I was about to post that your place holder does not have an orderdate but neither does the one that works. Going to have to look at this when I get home.|||OMG NOOOOOO!!! Not working on SQL at HOME!! I wouldn't want to be responsible for THAT in any way, shape or form!

Home is for Beer, relaxation, Lovin', and...well...pretty much ANYTHING but working on Paul's SQL questions ;)

...and what do you mean by "placeholder", BTW?|||please explain what "isn't liking my code" means

is there an error message?

without it, we're just guessing

there are other ways of getting the last 4 order dates, by the way, but they involve a self-join or yet another subselect|||Server: Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'order'.Yeah, This is not probably the way I woulda done it...

Had a peer at work come to me with this, and ask me why the order-by in the sub-query doesn't work. I could not explain it, so I just banged my head against it until I figured out a way. Then posted here my ugly code just to show what worked. And so anyone so motivated could run the select to see what I am after for output.

Of course a coupla decades in software development have taught me that you are indeed right...more'n one way to skin a catfish. I am of course open to other alternatives, though am not sure off the top of my head that a self-join would be any less ugly (especially when this sub-select theoretically SHOULD work). Of course, I am always learning, and not typically arrogant enough to think my way is the only way :) (though mind you , I am in no way adverse to steppin up to arm-wrestle about it being the RIGHT way :D )

If you want to take the time to post other ways or improvements, I am ALWAYS open to such nudges or downright slapdowns - in fact I LOVE 'em.

I always appreciate it when someone sees thier way clear to burn a few brain cells on my behalf.|||let's start by replacing that hideous CASE expression

select upper(left(datename(dw,getdate())
,3+datepart(dw,getdate())%2)) as DOW|||i'm sorry, i just discovered i don't have northwind to play with

i'll see if i can squeeze this in tomorrow at the job|||since she's been gone it's all work,exercise study sleep. occasionally I go out with the boys, but my first instinct was right.

in the first one that does not work there is no order date in the first half of your union and in the second one you alias GETDATE() as your order date. my brain was just too mushy earlier.|||Hmmmm...if I understand you correctly, I think that is my intent (which doesn't mean SQL will let me do what I think I want to *L*)

I am only trying to ORDER BY the second select in the UNION...that is because the table used in the second union has daily data in it since about June of 1963 (aka, many more than the 4 dates I am interested in). So the second part of the union is simply trying to grab the last 4 dates (most recent) which is why I am trying to use the TOP 4...ORDER BY - - to get just the last 4 dates in table).

My intent in the first (non-working) UNION is to put my "hard-coded" row of TODAY's data in, then add to it the latest 4 days from the table in the second select of the UNION.

I also want the whole thing ordered by date, which is missing from the first UNION, but that part shouldn't matter to me, should it? At least relative to the pulling of the 5 rows I want. The second (working, ugly) select DOES have the overall ORDER BY in it, which probably causes some confusion relative to my question.

I am just trying to figure out why the initial (non-working) select complains of a syntax error, when to my way of thinking, I should be able to put an idependent select in the second half of the UNION and apply the ORDER BY only to that sub-select (it would, again, be a seperate issue to order the whole result of the UNION).

In a nutshell, if I run the second select in the first UNION by itself, it works fine. But when I try to run the whole UNION select, it fails with the previously-noted error. Shouldn't my sub-select work within the UNION if I place it inside parenthesis as I have?

Part of the problem may be that I am not doing a good job of explaining specifically what I think should work *LOL* - - or not understanding your explaination of why it WON'T.

As always, thanks for expending brain cell activity on my behalf.|||the whole UNION is one result set and the order by applies to the whole result set.|||here, this works:select *
from (
select top 4
[OrderDate]
, substring('SUN MON TUESWED THURFRI SAT '
, 4*datepart(dw,[OrderDate])-3,4) as DOW
, right(space(10)+CAST(Freight*100 as VARCHAR(10)),10) as Freight
from Northwind.dbo.orders
where employeeid = 9
order by [OrderDate] desc
) as dt
union all
select getdate()
, substring('SUN MON TUESWED THURFRI SAT '
, 4*datepart(dw,getdate())-3,4) as DOW
, ' N/A' AS Freight|||he already had one that works. he was just wondering why the other did not. but yes yours is prettier.|||AH-HAA!!!!!

I KNEW it was a syntax assumption...

FROM BOL:
The ORDER BY clause can include items not appearing in the select list. However, if SELECT DISTINCT is specified, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.

MY problem was that I was trying to FORCE the ORDER BY in my sub-query to work like a NON-UNION sub-query, and could not figure out why my sub-query (with order by) would not function as an independent sub-query (as it would in a JOIN, for example).

And you guys, on a different plane *L*, assumed that I was playing by the taken-for-granted UNION rules understanding.

I KNEW it was gonna be something like that.

Sincere thanks for your patience and willingness to work with me on this. When I cannot understand why something doesn't work when I am playing by the rules, it often turns out that I am not playing by the rules ;)

...either that, or God is just toying with me at the time...

In this case it was the former.

Thanks for trying to explain when I refused to listen - y'all are now in the same fine group as my parents, teachers, and...well, pretty much everyone else I know :D|||in the first one that does not work there is no order date in the first half of your union and in the second one you alias GETDATE() as your order date. my brain was just too mushy earlier.

this is what i was saying, right?|||Yeah, that's what I mean though...I was thinking differently, so even though you posted that, I thought "huh? I don't WANT a date in the first part of the UNION", because I didn't need it in the final output.

At that time I didn't yet realize/know that it was REQUIRED to make the UNION work.

Theoretically, I shouldn't NEED it (SQL Server notwithstanding) because the output of various selects in the UNION should (logically, or non-logically ;) ) be independent of each other, right? From a set theory-type perspective, anyway.

My second select (the one that worked) only ACCIDENTALLY unbroke the rules that got me in the first one.

Thanks regardless, Sean...I really shouldn't let anyone know any more about my thought process than I have to ;)

ORDER BY decreases performance by 40x?

I am astounded. I haven't read any where that adding a sort order to a query
would drastically increase read time for the same query.
This query performed on a table with 360,000 records:
SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
FROM [PLVWDIV_INV_SHORT]
ORDER BY [SEARCHKEY] DESC
Takes 40 seconds! While:
SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
FROM [PLVWDIV_INV_SHORT]
Takes 1 second. Wow.
For now we will query the server without the ORDER BY and do a sort in the
Client application.
It seems like adding an ORDER BY on a large table or view increases the read
time by an order of magnitude.
Removing DESC speeds up the query somewhat.
Any thoughts? Thanks in advance...Hi John
Do you have an index on ID?
What happens if you ORDER by the table column, rather than the column alias,
which is an expression ?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"John Kotuby" <johnk@.powerlist.com> wrote in message
news:ux1ET$K4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I am astounded. I haven't read any where that adding a sort order to a
>query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort in the
> Client application.
> It seems like adding an ORDER BY on a large table or view increases the
> read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
> Any thoughts? Thanks in advance...
>|||"John Kotuby" <johnk@.powerlist.com> wrote in message
news:ux1ET$K4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I am astounded. I haven't read any where that adding a sort order to a
>query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort in the
> Client application.
> It seems like adding an ORDER BY on a large table or view increases the
> read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
Take off the rtrim(), and is your ID column indexed in both directions? I
know that more ofther your index is ascending and we always want the max
value on top. So set your index to be descending instead.
HTH
__Stephen|||John Kotuby wrote:
> I am astounded. I haven't read any where that adding a sort order to
> a query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort
> in the Client application.
> It seems like adding an ORDER BY on a large table or view increases
> the read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
> Any thoughts? Thanks in advance...
Is there an index on SEARCHKEY? Have you looked at the execution plan to
make sure the index is being used?
The "TOP 1000 " causes the query to stop after 1000 rows are retrieved.
Using the ORDER BY forces the entire 360000 rows to be sorted before getting
the top 1000, This can be time consuming, especially when there is no index
to be used.
Your plan to to the sorting in the client has a drawback: you will have
different results doing it that way. Look at this set or data:
36
55
79
28
44
If you take the top 2 and then sort them in descending order, you get
55
36
If you sort them first and then take the top 2, you get
79
55
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||John Kotuby wrote:
> I am astounded. I haven't read any where that adding a sort order to
> a query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort
> in the Client application.
> It seems like adding an ORDER BY on a large table or view increases
> the read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
>
Oh, my bad, I did not notice you were sorting on the result of the
calculation. This can really slow things down as it prevents an index from
being used. See the difference if you take Kalen's advice and ORDER BY ID
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks for the responses guys...
All of them have merit. Bob, you hit the nail on the head when you reminded
me that the ORDER BY is performed on all 360,000 records before the TOP
1000 are selected. I feel like a real dope. Doing an ORDER BY on ID (which
is Indexed ascending) cut the time in half. I can see how Indexing
Descending will help even more.
What really fixed it for us is that we have an indexed INV_DATE field in the
view.
So I tried using
WHERE INV_DATE > DATEADD(DAY,-30,GETDATE()) and got it down to 2 seconds.
This gives me the last 30 days worth of invoices which will work in most
cases for a Browse. We supply other methods in the Client app to get to
specific records like exact Inv#, Date Range, etc in case the needed record
is not in the last 30 days.
Adding the WHERE clause immediately reduced the number of records that
needed to be sorted.
Once again, thank you all for your speedy replies.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:eDDoaKL4FHA.4076@.TK2MSFTNGP15.phx.gbl...
> John Kotuby wrote:
> Oh, my bad, I did not notice you were sorting on the result of the
> calculation. This can really slow things down as it prevents an index from
> being used. See the difference if you take Kalen's advice and ORDER BY ID
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>

Order By Adding Rows?

Hi All,
Having trouble with the following SQL statement. If there is multiple
ORDER by's it adds rows to the result. The SQL statement below adds
rows to the result, if I remove block_line_no
from the orer by it returns the correct data.
SELECT *
FROM nc_block_data
WHERE (PROGRAM_ID = 2607) OR
(PROGRAM_ID = 2608) OR
(PROGRAM_ID = 2609) OR
(PROGRAM_ID = 2610) OR
(PROGRAM_ID = 2612) OR
(PROGRAM_ID = 2613) OR
(PROGRAM_ID = 2614) OR
(PROGRAM_ID = 2615) OR
(PROGRAM_ID = 2616) OR
(PROGRAM_ID = 2617) OR
(PROGRAM_ID = 2618) OR
(PROGRAM_ID = 2619) OR
(PROGRAM_ID = 2620) OR
(PROGRAM_ID = 2621)
ORDER BY program_id, block_line_noSkip wrote:
> Hi All,
> Having trouble with the following SQL statement. If there is multiple
> ORDER by's it adds rows to the result. The SQL statement below adds
> rows to the result, if I remove block_line_no
> from the orer by it returns the correct data.
>
> SELECT *
> FROM nc_block_data
> WHERE (PROGRAM_ID = 2607) OR
> (PROGRAM_ID = 2608) OR
> (PROGRAM_ID = 2609) OR
> (PROGRAM_ID = 2610) OR
> (PROGRAM_ID = 2612) OR
> (PROGRAM_ID = 2613) OR
> (PROGRAM_ID = 2614) OR
> (PROGRAM_ID = 2615) OR
> (PROGRAM_ID = 2616) OR
> (PROGRAM_ID = 2617) OR
> (PROGRAM_ID = 2618) OR
> (PROGRAM_ID = 2619) OR
> (PROGRAM_ID = 2620) OR
> (PROGRAM_ID = 2621)
> ORDER BY program_id, block_line_no
Can you post some code to reproduce that problem - CREATE TABLE
followed by INSERTs of one or two sample rows. Also tell us what
version, edition and service pack of SQL Server you are using. If your
server is patched and you can't reproduce the problem then maybe you
are looking at some corrupt data or index somewhere.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Here is the table structure. I created a test table but was not able to
reproduce the problem. Could it be something with the table structure?
USE [iFrame]
GO
/****** Object: Table [dbo].[Nc_Block_Data] Script Date: 04/11/2006
14:32:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Nc_Block_Data](
[PROGRAM_ID] [int] NOT NULL,
[BLOCK_LINE_NO] [int] NOT NULL,
[PROG_LINE_NO] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NC_CODE] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HOLE_NAME] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NCBLOCK_STATUS] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NCBLOCK_STATUS_DT] [datetime] NULL,
[ERROR_INF] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF|||Not sure why but it looks like it was bad data, just in one section of
the DB. Does this make sense? If I grab a different group of data all
works fine.|||If you can be certain that the data is not corrupt, try reindexing the
suspicious block_line_no column.
ML
http://milambda.blogspot.com/sql

Order By

How would i add order by to the syntax below:

"Select * Into ETCLog_holding from etclog where box# BETWEEN " & Box1 &
" and " & Box2

i have tried adding it after Box2 but it doesnt work.

Any ideas?I have tried and its executed ok adding "Order by" after Box2...may be
you shoud to revise "box#" part.

Good luck.|||(pkruti@.hotmail.com) writes:
> How would i add order by to the syntax below:
> "Select * Into ETCLog_holding from etclog where box# BETWEEN " & Box1 &
> " and " & Box2
> i have tried adding it after Box2 but it doesnt work.

You can add an ORDER BY clause after Box2, just to be careful to add a
space.

However, it is a fairly pointless thing to do. If you expect data in
ETCLog_holding to have a certain order, you are forgetting the fact
that tables are unordered sets.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Oracle DSV: Problems creating relationships with reported inconsistent datatypes.

I am adding tables to the DSV and adding the relationships but SSAS keeps complaining that the data types of the FK and PK tables do not match, even though I can see that they are in fact the same, ie NUMBER with no scale/precision set. Presumably this means Oracle uses a default?

Any ideas as to the fix? I have created a NamedQuery as a SELECT * FROM <table> and this seems to work but does seem to be a hack and is very annoying!

Searching connect, I found at that this is a bug. however, it was reported as fixed in SP1 but I am using SP2 CTP.. so I have added a new bug report for this under SP2.

the only workaround is to go back and edit the XML file. (View Code in the solution explorer context menu).

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