Order in the middle of a table

Hi,
I publish a table on a website. At the end of each row I want to show an up-
and downbutton. The buttons haves an id as parameter. Inside my table I have
a vieworder column, this is the way I sort the table.
What I want is that when you press on the up- or downbutton that record
moves up or down. This means an update of that record and the record above
or below. I want to know what is the best way how to realize this.
Ideas? Are there (build-in) funtions for this?
Thank!You could easily write a stored proc to do this. One of the many
wonderful things about the UPDATE statement in the SQL language is that
it is an all-at-once operation. You can increment the vieworder for a
row and either increment or decrement its neighbour as appropriate all
at the same time. Try this batch:
use tempdb
go
-- A little setting up...
create table #tmp
(
vieworder int not null,
blah char(1) primary key clustered
)
insert into #tmp (vieworder, blah) values (1, 'A')
insert into #tmp (vieworder, blah) values (2, 'B')
insert into #tmp (vieworder, blah) values (3, 'C')
insert into #tmp (vieworder, blah) values (4, 'D')
insert into #tmp (vieworder, blah) values (5, 'E')
insert into #tmp (vieworder, blah) values (6, 'F')
insert into #tmp (vieworder, blah) values (7, 'G')
insert into #tmp (vieworder, blah) values (8, 'H')
insert into #tmp (vieworder, blah) values (9, 'I')
insert into #tmp (vieworder, blah) values (10, 'J')
-- --
-- the row to shuffle
declare @.id int
set @.id = 10
-- The original result set
select * from #tmp order by vieworder
-- Shuffle up
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder + 1)
when (vieworder = @.id + 1) then (vieworder - 1)
end
where (vieworder = @.id or vieworder = @.id + 1) -- Only
shuffle the 2 rows in question
and exists (select * from #tmp where vieworder = @.id + 1) -- Just
to make sure you don't shuffle a row "off the end"
-- Let's see it after the shuffle up
select * from #tmp order by vieworder
-- Shuffle down
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder - 1)
when (vieworder = @.id - 1) then (vieworder + 1)
end
where (vieworder = @.id or vieworder = @.id - 1) -- Only
shuffle the 2 rows in question
and exists (select * from #tmp where vieworder = @.id - 1) -- Just
to make sure you don't shuffle a row "off the end"
-- Let's see it after the shuffle down
select * from #tmp order by vieworder
-- Tidy up
drop table #tmp
Embed that kind of logic inside a stored procedure and then you just
call the stored proc with the appropriate parameters (a direction to
shuffle and a baseline row to shuffle along with its neighbour) from
your webpage using ADO (with a postback to reload the same webpage I guess).
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Arjen wrote:

>Hi,
>I publish a table on a website. At the end of each row I want to show an up
-
>and downbutton. The buttons haves an id as parameter. Inside my table I hav
e
>a vieworder column, this is the way I sort the table.
>What I want is that when you press on the up- or downbutton that record
>moves up or down. This means an update of that record and the record above
>or below. I want to know what is the best way how to realize this.
>Ideas? Are there (build-in) funtions for this?
>Thank!
>
>|||Okay, thanks!
I will try this.
Arjen
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> schreef in bericht news:e
yGZJViqFHA.564@.TK2MSFTNGP10.phx.gbl...
You could easily write a stored proc to do this. One of the many wonderful
things about the UPDATE statement in the SQL language is that it is an all-a
t-once operation. You can increment the vieworder for a row and either incr
ement or decrement its neighbour as appropriate all at the same time. Try t
his batch:
use tempdb
go
-- A little setting up...
create table #tmp
(
vieworder int not null,
blah char(1) primary key clustered
)
insert into #tmp (vieworder, blah) values (1, 'A')
insert into #tmp (vieworder, blah) values (2, 'B')
insert into #tmp (vieworder, blah) values (3, 'C')
insert into #tmp (vieworder, blah) values (4, 'D')
insert into #tmp (vieworder, blah) values (5, 'E')
insert into #tmp (vieworder, blah) values (6, 'F')
insert into #tmp (vieworder, blah) values (7, 'G')
insert into #tmp (vieworder, blah) values (8, 'H')
insert into #tmp (vieworder, blah) values (9, 'I')
insert into #tmp (vieworder, blah) values (10, 'J')
-- --
-- the row to shuffle
declare @.id int
set @.id = 10
-- The original result set
select * from #tmp order by vieworder
-- Shuffle up
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder + 1)
when (vieworder = @.id + 1) then (vieworder - 1)
end
where (vieworder = @.id or vieworder = @.id + 1) -- Only shuffle t
he 2 rows in question
and exists (select * from #tmp where vieworder = @.id + 1) -- Just to make s
ure you don't shuffle a row "off the end"
-- Let's see it after the shuffle up
select * from #tmp order by vieworder
-- Shuffle down
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder - 1)
when (vieworder = @.id - 1) then (vieworder + 1)
end
where (vieworder = @.id or vieworder = @.id - 1) -- Only shuffle t
he 2 rows in question
and exists (select * from #tmp where vieworder = @.id - 1) -- Just to make s
ure you don't shuffle a row "off the end"
-- Let's see it after the shuffle down
select * from #tmp order by vieworder
-- Tidy up
drop table #tmp
Embed that kind of logic inside a stored procedure and then you just call th
e stored proc with the appropriate parameters (a direction to shuffle and a
baseline row to shuffle along with its neighbour) from your webpage using AD
O (with a postback to reload the same webpage I guess).
Hope this helps.
mike hodgson
blog: http://sqlnerd.blogspot.com
Arjen wrote:
Hi,
I publish a table on a website. At the end of each row I want to show an up-
and downbutton. The buttons haves an id as parameter. Inside my table I have
a vieworder column, this is the way I sort the table.
What I want is that when you press on the up- or downbutton that record
moves up or down. This means an update of that record and the record above
or below. I want to know what is the best way how to realize this.
Ideas? Are there (build-in) funtions for this?
Thank!

Order By Parameter

Is it possible to use a variable like @.SelectOrderBy with the Order By of a stored procedure? or do I need several SP's?

Select *
FROM Table
Order By @.SelectOrderBy

I want to let a user select from a drop down on a webform the order by column to use.

ThanksYou'd either have seperate stored procedures (or a conditional within one stored procedure), or to implement it like your example above you would have to use dynamic SQL (look in Books Online for proper syntax, mine is iffy):

Declare @.strSQL VARCHAR(8000)
SELECT @.strSQL = "SELECT * FROM table ORDER BY " + @.SelectOrderBy
EXEC @.strSQL

It's not advisable to do it this way however, due to security and efficiencty issues. Stick with the conditional within one proc if it's not too complex!|||the other option is to pull the data back into a dataset and then use a dataview with a sort, passing in the column they selected.

cs

ORDER BY parameter

How can I write that SORT depends on parameter, something like:
ORDER BY column1 case when @.param=0 then DESC else ASC end
Regards,SStandard SQL-92 does not allow you to use a function or expression in
an ORDER BY clause. The ORDER BY clause is part of a cursor and it can
only see the column names that appear in the SELECT clause list that
was used to build the result set. BP will now chime in that SQL-99
(officially called "a standard in progress" and not recognized by the
U.S. Government for actual use) does allow this.
But aside from this, there is the good programming practice of showing
the fields that are used for the sort to the user, usually on the left
side of each line since we read left to right.
The standard trick for picking a sorting order at run time is to use a
flag in CASE expression. If you want to sort on more than one column
and allow all possible combinations of sorting use one CASE per column:
SELECT
CASE @.flag_1
WHEN 'a' THEN CAST (a AS CHAR(n))
WHEN 'b' THEN CAST (b AS CHAR(n))
WHEN 'c' THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1,
CASE @.flag_2
WHEN 'x' THEN CAST (x AS CHAR(n))
WHEN 'y' THEN CAST (y AS CHAR(n))
WHEN 'z' THEN CAST (z AS CHAR(n))
ELSE NULL END AS sort_2,
..
CASE @.flag_n
WHEN 'n1' THEN CAST (n1 AS CHAR(n))
WHEN 'n2' THEN CAST (n2 AS CHAR(n))
WHEN 'n3' THEN CAST (n3 AS CHAR(n))
ELSE NULL END AS sort_2,
FROM Foobar
WHERE ...
ORDER BY sort_1, sort_2, ...
More than one sort column and only a limited set of combinations then
use concatenation.
CASE @.flag_1
WHEN 'ab'
THEN CAST(a AS CHAR(n)) ||' ' || CAST(b AS CHAR(n))
WHEN 'ba'
THEN CAST(b AS CHAR(n)) ||' ' || CAST(a AS CHAR(n))
ELSE NULL END AS sort_1,
If you need ASC and DESC options, then use a combination of CASE and
ORDER BY
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'ASC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'ASC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'ASC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_a,
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'DESC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'DESC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'DESC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_d
. ORDER BY sort_1_a ASC, sort_1_d DESC
I have shown explicit CAST(<exp> AS CHAR(n)), but if the datatypes of
the THEN clause expressions were already the same, there would be no
reason to force the conversions.
You change the ELSE NULL clause to any constant of the appropriate
datatype, but it should be something useful to the reader.
A neater way of doing this is to use one column for each sorting option
so you do not have worry about CAST() operations.
SELECT ...
CASE WHEN @.flag = 'a' THEN a ELSE NULL END AS sort1,
CASE WHEN @.flag = 'b' THEN b ELSE NULL END AS sort2,
CASE WHEN @.flag = 'c' THEN c ELSE NULL END AS sort3
FROM Foobar
WHERE ...
ORDER BY sort1, sort2, sort3;|||hi,
probably this link should answer your question:
http://www.aspfaq.com/2501
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"simon" wrote:

> How can I write that SORT depends on parameter, something like:
> ORDER BY column1 case when @.param=0 then DESC else ASC end
> Regards,S
>
>sql

Order By Param

Is there a way to have a report parameter for the selection of what field
the user wants a table to be grouped on? For instance, the parameter would
have 3 options, clientid, clientname, and clientcity. I want to be able to
select one of these and have the table group on this selection.
Or any generic ways or ideas on how to accomplish this?
Thanks!!!I am looking for the same solution. I have a client that I have promised 10
reports to and they gave me a few but want sort capability on 5 different
fields. I could say that those are 5 different reports, but there should be
an easy way to list the fields in a drop down parameter and adjust the ORDER
BY based on this. Shouldn't there?
Thanks in advance.
ANDY
"Amon Borland" wrote:
> Is there a way to have a report parameter for the selection of what field
> the user wants a table to be grouped on? For instance, the parameter would
> have 3 options, clientid, clientname, and clientcity. I want to be able to
> select one of these and have the table group on this selection.
> Or any generic ways or ideas on how to accomplish this?
> Thanks!!!
>
>|||can you pass the field to sort on into the report as a parameter and use a
CASE in your query to conditionally sort based on whatever you passed in as
a parameter?
Bill
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:C80B4FC3-FA92-4ADE-BFEA-D6B0867FDE97@.microsoft.com...
> I am looking for the same solution. I have a client that I have promised
10
> reports to and they gave me a few but want sort capability on 5 different
> fields. I could say that those are 5 different reports, but there should
be
> an easy way to list the fields in a drop down parameter and adjust the
ORDER
> BY based on this. Shouldn't there?
> Thanks in advance.
> ANDY
> "Amon Borland" wrote:
> > Is there a way to have a report parameter for the selection of what
field
> > the user wants a table to be grouped on? For instance, the parameter
would
> > have 3 options, clientid, clientname, and clientcity. I want to be able
to
> > select one of these and have the table group on this selection.
> >
> > Or any generic ways or ideas on how to accomplish this?
> >
> > Thanks!!!
> >
> >
> >|||It seems all my replied are this..
Sorting is easy
DECLARE @.sql as varchar(100)
SET @.sql = 'SELECT * FROM blah WHERE blah = blah ORDER BY '
@.paramete
EXEC sp_executesql @.sq
Grouping on the other hand I don't think you can do dynamically

Order By madness

I have a simple case statement which changes the "ORDER BY" clause based on
a parameter passed to a stored proc. 4 out of 5 cases work, but one fails
with the following:
Server: Msg 295, Level 16, State 3, Procedure pr_reportPartnersDBTest, Line
12
Syntax error converting character string to smalldatetime data type.
If I comment out the case block and put each ORDER BY as an independent
statement (uncommenting one at a time), they all work without error.
When the case block is used and the int 4 is passed (my zip field), I
receive the error message above. If I comment out the case statement and
just use "ORDER BY zip" it works fine.
I must be missing something obvious, but I'm too close and suspect I'm
missing something that someone else will immediately see.
All comments, ideas or thoughts welcomed.
-- CODE snippet --
select ...
from ...
where...
order by
case @.orderBy
when 1 then dateRegistered
when 2 then gender
when 3 then bodyType
when 4 then zip
when 5 then ageRange
else dateRegistered
end
-- The next lines are for debugging purposes...
-- I commented out the case block above, then tested each of the lines below
one at a time. They all work as expected. But the case statement above fails
when I pass it a 4 (zip). All other values work as expected.
-- order by dateRegistered
-- order by gender
-- order by bodyType
-- order by zip
-- order by ageRangeCASE in SQL Server is an expression that returns a scalar value of a
specific datatype. That means that if the separate expressions for each WHEN
have a different datatype, they will explicitly be converted to the datatype
with the highest datatype precedence, in your case smalldatetime. And you
can't convert all zipcodes into a smalldatetime value.
So the simplest way to do it is to have a separate CASE for each column:
order by
case @.orderBy when 2 then gender end,
case @.orderBy when 3 then bodyType end,
case @.orderBy when 4 then zip end,
case @.orderBy when 5 then ageRange end,
case when @.OrderBy NOT IN (2,3,4,5) then dateRegistered end
Jacco Schalkwijk
SQL Server MVP
"Don B" <DonBaarns@.hotmail.com> wrote in message
news:OlsEGQOUFHA.1432@.TK2MSFTNGP09.phx.gbl...
>I have a simple case statement which changes the "ORDER BY" clause based on
> a parameter passed to a stored proc. 4 out of 5 cases work, but one fails
> with the following:
> Server: Msg 295, Level 16, State 3, Procedure pr_reportPartnersDBTest,
> Line
> 12
> Syntax error converting character string to smalldatetime data type.
> If I comment out the case block and put each ORDER BY as an independent
> statement (uncommenting one at a time), they all work without error.
> When the case block is used and the int 4 is passed (my zip field), I
> receive the error message above. If I comment out the case statement and
> just use "ORDER BY zip" it works fine.
> I must be missing something obvious, but I'm too close and suspect I'm
> missing something that someone else will immediately see.
> All comments, ideas or thoughts welcomed.
> -- CODE snippet --
> select ...
> from ...
> where...
> order by
> case @.orderBy
> when 1 then dateRegistered
> when 2 then gender
> when 3 then bodyType
> when 4 then zip
> when 5 then ageRange
> else dateRegistered
> end
> -- The next lines are for debugging purposes...
> -- I commented out the case block above, then tested each of the lines
> below
> one at a time. They all work as expected. But the case statement above
> fails
> when I pass it a 4 (zip). All other values work as expected.
>
> -- order by dateRegistered
> -- order by gender
> -- order by bodyType
> -- order by zip
> -- order by ageRange
>|||It is also worth mention that a CASE is not allowed in the ORDER BY in
Standard SQL-92. You can put it in a column in the select list, name
it and use that name in the ORDER BY. This way your code will port, be
readable to people who do not speak T-SQL dialect and your user will be
able to tell what the sort was done on, instead of trying to guess.
In the olde days, we would print the sort key at both the left and
right sides of a print out, so you could lay a ruler acorss the 132
column page. The printout were pretty fuzzy at times and this really
helped. Today, I assume that the sort column would be on the right and
locked so you can scroll horizonally.|||Jacco,
EXCELLENT... I clearly don't know enough fundamentals about T-SQL. That
works great for my example.
I would really like a secondary sort on zip code (a varchar in my case)
which would be something like this:
order by zip, dateRegistered
and/or
order by bodyType, zip
The two statements above work as expected.
I can't just put the two columns with a comma inside the Then/End
combination as the case statement uses the comma as a delimiter. I don't
know how to escape it (or the equivalent) so the case statement can return a
set of columns for the order by clause. I may also want to change the
ASC/DESC order on specific columns but I assume if I figure out the syntax
for the multiple columns it will be similar if I add specific sort orders
for some columns.
All ideas welcomed!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:O0KSlYOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> CASE in SQL Server is an expression that returns a scalar value of a
> specific datatype. That means that if the separate expressions for each
WHEN
> have a different datatype, they will explicitly be converted to the
datatype
> with the highest datatype precedence, in your case smalldatetime. And you
> can't convert all zipcodes into a smalldatetime value.
> So the simplest way to do it is to have a separate CASE for each column:
> order by
> case @.orderBy when 2 then gender end,
> case @.orderBy when 3 then bodyType end,
> case @.orderBy when 4 then zip end,
> case @.orderBy when 5 then ageRange end,
> case when @.OrderBy NOT IN (2,3,4,5) then dateRegistered end
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Don B" <DonBaarns@.hotmail.com> wrote in message
> news:OlsEGQOUFHA.1432@.TK2MSFTNGP09.phx.gbl...
on
fails
>|||Use the same approach:
order by
case @.orderBy when 1 then zip end,
case @.orderBy when 1 then dateRegistered end,
case @.orderBy when 2 then bodyType end,
case @.orderBy when 2 then zip end ASC,
case @.orderBy when 3 then bodyType end,
case @.orderBy when 3 then zip end DESC
Terri Morton
MVP - ASP/ASP.NET
"Don B" <DonBaarns@.hotmail.com> wrote in message
news:OrWlLtOUFHA.3716@.TK2MSFTNGP12.phx.gbl...
> Jacco,
> EXCELLENT... I clearly don't know enough fundamentals about T-SQL. That
> works great for my example.
> I would really like a secondary sort on zip code (a varchar in my case)
> which would be something like this:
> order by zip, dateRegistered
> and/or
> order by bodyType, zip
> The two statements above work as expected.
> I can't just put the two columns with a comma inside the Then/End
> combination as the case statement uses the comma as a delimiter. I don't
> know how to escape it (or the equivalent) so the case statement can return
> a
> set of columns for the order by clause. I may also want to change the
> ASC/DESC order on specific columns but I assume if I figure out the syntax
> for the multiple columns it will be similar if I add specific sort orders
> for some columns.
> All ideas welcomed!
>
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:O0KSlYOUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> WHEN
> datatype

ORDER BY CASE

Here is my ORDER BY Clause, and based on the @.Option parameter, I do
different order by. This works fine since the order is set from just one
column.
ORDER BY CASE @.Option
WHEN '1' THEN IsApproved ASC
WHEN '2' THEN CompletedDate
WHEN '3' THEN Priority
END
The problem is that as I add more columns to do order by, it errors (like
the following).
ORDER BY CASE 1
WHEN '1' THEN IsApproved ASC, CompletedDate DESC, ProjectID DESC,
Priority DESC, IsApproved DESC
WHEN '2' THEN CompletedDate ASC, ProjectID DESC
WHEN '3' THEN Priority ASC, Priority DESC
END
It seems you cannot have more than one columns if you use case statement in
the order by clause. then what is the purpose of case if you can just do
sort by just one column?
Additionaly, can this be done other than dynamic query (and other than bunch
of if else statement)?
Thanks all
JOrdering by CASE using multiple columns is not, unfortunately, very
straightforward. You have to do something like:
ORDER BY
CASE @.Option
WHEN 1 THEN IsApproved
WHEN 2 THEN CompletedDate
WHEN 3 THEN Priority
END ASC,
CASE @.Option
WHEN 1 THEN CompletedDate
WHEN 2 THEN ProjectId,
WHEN 3 THEN Priority
END DESC,
..
This may or may not be better than using dynamic SQL -- it really depends on
just how dynamic/complex the conditions are.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Justin" <jyang@.ioutsource.info> wrote in message
news:%23lx4Z1GaGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Here is my ORDER BY Clause, and based on the @.Option parameter, I do
> different order by. This works fine since the order is set from just one
> column.
> ORDER BY CASE @.Option
> WHEN '1' THEN IsApproved ASC
> WHEN '2' THEN CompletedDate
> WHEN '3' THEN Priority
> END
> The problem is that as I add more columns to do order by, it errors (like
> the following).
> ORDER BY CASE 1
> WHEN '1' THEN IsApproved ASC, CompletedDate DESC, ProjectID DESC,
> Priority DESC, IsApproved DESC
> WHEN '2' THEN CompletedDate ASC, ProjectID DESC
> WHEN '3' THEN Priority ASC, Priority DESC
> END
> It seems you cannot have more than one columns if you use case statement
> in the order by clause. then what is the purpose of case if you can just
> do sort by just one column?
> Additionaly, can this be done other than dynamic query (and other than
> bunch of if else statement)?
> Thanks all
> J
>|||I believe that Case returns a value, not a clause, so your case function is
treated like a single column and cannot be an order by clause in itself. In
this case, you could try using several case statements, although there may
be a better way.
For Case = 3 you have priority listed twice, in two different orders. I
assume this is a typo. Basically, you need a case for each column, not each
sort order.
Try this approach. It is really tedious, but should work.
ORDER BY
CASE
WHEN @.Option = '1' THEN IsApproved ASC
WHEN @.Option = '2' THEN CompletedDate ASC
WHEN @.Option = '3' THEN Priority ASC
END
,CASE
WHEN @.Option = '1' THEN CompletedDate DESC
WHEN @.Option = '2' THEN ProjectID DESC
WHEN @.Option = '3' THEN 1
END
,CASE
WHEN @.Option = '1' THEN ProjectID DESC
WHEN @.Option = '2' THEN 1
WHEN @.Option = '3' THEN 1
END
,CASE
WHEN @.Option = '1' THEN Priority DESC
WHEN @.Option = '2' THEN 1
WHEN @.Option = '3' THEN 1
END
,CASE
WHEN @.Option = '1' THEN IsApproved DESC
WHEN @.Option = '2' THEN 1
WHEN @.Option = '3' THEN 1
END
"Justin" <jyang@.ioutsource.info> wrote in message
news:%23lx4Z1GaGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Here is my ORDER BY Clause, and based on the @.Option parameter, I do
> different order by. This works fine since the order is set from just one
> column.
> ORDER BY CASE @.Option
> WHEN '1' THEN IsApproved ASC
> WHEN '2' THEN CompletedDate
> WHEN '3' THEN Priority
> END
> The problem is that as I add more columns to do order by, it errors (like
> the following).
> ORDER BY CASE 1
> WHEN '1' THEN IsApproved ASC, CompletedDate DESC, ProjectID DESC,
> Priority DESC, IsApproved DESC
> WHEN '2' THEN CompletedDate ASC, ProjectID DESC
> WHEN '3' THEN Priority ASC, Priority DESC
> END
> It seems you cannot have more than one columns if you use case statement
in
> the order by clause. then what is the purpose of case if you can just do
> sort by just one column?
> Additionaly, can this be done other than dynamic query (and other than
bunch
> of if else statement)?
> Thanks all
> J
>|||I missed on thing here...
As Adam has in his post, the ASC, DESC has to come after the END of the case
function.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eHtPc9GaGHA.440@.TK2MSFTNGP05.phx.gbl...
> I believe that Case returns a value, not a clause, so your case function
is
> treated like a single column and cannot be an order by clause in itself.
In
> this case, you could try using several case statements, although there may
> be a better way.
> For Case = 3 you have priority listed twice, in two different orders. I
> assume this is a typo. Basically, you need a case for each column, not
each
> sort order.
> Try this approach. It is really tedious, but should work.
> ORDER BY
> CASE
> WHEN @.Option = '1' THEN IsApproved
> WHEN @.Option = '2' THEN CompletedDate
> WHEN @.Option = '3' THEN Priority
> END ASC
> ,CASE
> WHEN @.Option = '1' THEN CompletedDate
> WHEN @.Option = '2' THEN ProjectID
> WHEN @.Option = '3' THEN 1
> END DESC
> ,CASE
> WHEN @.Option = '1' THEN ProjectID
> WHEN @.Option = '2' THEN 1
> WHEN @.Option = '3' THEN 1
> END DESC
> ,CASE
> WHEN @.Option = '1' THEN Priority
> WHEN @.Option = '2' THEN 1
> WHEN @.Option = '3' THEN 1
> END DESC
> ,CASE
> WHEN @.Option = '1' THEN IsApproved
> WHEN @.Option = '2' THEN 1
> WHEN @.Option = '3' THEN 1
> END DESC
> "Justin" <jyang@.ioutsource.info> wrote in message
> news:%23lx4Z1GaGHA.1220@.TK2MSFTNGP02.phx.gbl...
one
(like
> in
do
> bunch
>|||you can try it this way. I can understand its painful but I don't see any
other option
ORDER BY CASE @.option
WHEN '1' THEN IsApproved
WHEN '2' THEN CompletedDate
WHEN '3' THEN Priority
END ASC,
CASE @.option
WHEN '1' THEN CompletedDate
WHEN '2' THEN ProjectID
WHEN '3' THEN Priority
END DESC
by the way
ORDER BY CASE @.Option
WHEN '1' THEN IsApproved ASC <-- ASC here will not work to my knowledge
WHEN '2' THEN CompletedDate
WHEN '3' THEN Priority
END|||Justin,
I'd suggest this approach:
ORDER BY
CASE WHEN @.Option = 1 THEN IsApproved END ASC,
CASE WHEN @.Option = 1 THEN CompletedDate END DESC,
CASE WHEN @.Option = 1 THEN ProjectID END DESC,
CASE WHEN @.Option = 2 THEN CompletedDate END ASC,
CASE WHEN @.Option = 2 THEN ProjectID END DESC,
CASE WHEN @.Option = 3 THEN Priority END ASC,
CASE WHEN @.Option = 3 THEN CompletedDate END DESC,
CASE WHEN @.Option = 3 THEN CompletedDate END DESC
It's relatively manageable, and it also avoids the type conversion
errors you can get when you have more than one CASE alternative.
Steve Kass
Drew University
Justin wrote:

>Here is my ORDER BY Clause, and based on the @.Option parameter, I do
>different order by. This works fine since the order is set from just one
>column.
>ORDER BY CASE @.Option
> WHEN '1' THEN IsApproved ASC
> WHEN '2' THEN CompletedDate
> WHEN '3' THEN Priority
> END
>The problem is that as I add more columns to do order by, it errors (like
>the following).
>ORDER BY CASE 1
> WHEN '1' THEN IsApproved ASC, CompletedDate DESC, ProjectID DESC,
>Priority DESC, IsApproved DESC
> WHEN '2' THEN CompletedDate ASC, ProjectID DESC
> WHEN '3' THEN Priority ASC, Priority DESC
> END
>It seems you cannot have more than one columns if you use case statement in
>the order by clause. then what is the purpose of case if you can just do
>sort by just one column?
>Additionaly, can this be done other than dynamic query (and other than bunc
h
>of if else statement)?
>Thanks all
>J
>
>

ORDER BY - parameter

In order to allow our users to create custom report queries, we've created a report with two parameters: "Where" and "OrderBy". In the Where parameter we pass the columns to be checked (Division = 'south' AND price > 100). This works great. The "OrderBy" parameter also works, if and only if, one column name is passed. If we attempt to pass a parameter (both in the designer and URL string) formatted as "col1, col2", the preview fails when the comma is encountered. Is it not possible to create a parameter that contains more than one column for the ORDER BY clause (parameter)?
ThanksThis works, I used this type of parameter all the time. Is you query an
expression? Or are you using an @. parameter. It will only work as an
expression. To pass the parameter on the ULR it must be encoded.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> In order to allow our users to create custom report queries, we've created
a report with two parameters: "Where" and "OrderBy". In the Where parameter
we pass the columns to be checked (Division = 'south' AND price > 100). This
works great. The "OrderBy" parameter also works, if and only if, one column
name is passed. If we attempt to pass a parameter (both in the designer and
URL string) formatted as "col1, col2", the preview fails when the comma is
encountered. Is it not possible to create a parameter that contains more
than one column for the ORDER BY clause (parameter)?
> Thanks|||John.,
I changed your Where parameter name to WherePrm and added OrderBy1, OrderBy2
parameters. If needed assign default values.
SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE @.WherePrm ORDER BY @.OrderBy1, @.OrderBy2
Cem
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:7A469A37-A660-4626-A73A-09BCC1C28F46@.microsoft.com...
> Jason,
> Here is the actual statement: (again, the "where" works fine with multiple
entries)
> ="SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE (" & Parameters!Where.Value & ") ORDER BY (" &
Parameters!OrderBy.Value & ")"
> Do I need to further define whatever in the designer?
> Thanks,
> "Jason Carlson [MSFT]" wrote:
> > This works, I used this type of parameter all the time. Is you query an
> > expression? Or are you using an @. parameter. It will only work as an
> > expression. To pass the parameter on the ULR it must be encoded.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
> > news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> > > In order to allow our users to create custom report queries, we've
created
> > a report with two parameters: "Where" and "OrderBy". In the Where
parameter
> > we pass the columns to be checked (Division = 'south' AND price > 100).
This
> > works great. The "OrderBy" parameter also works, if and only if, one
column
> > name is passed. If we attempt to pass a parameter (both in the designer
and
> > URL string) formatted as "col1, col2", the preview fails when the comma
is
> > encountered. Is it not possible to create a parameter that contains more
> > than one column for the ORDER BY clause (parameter)?
> > >
> > > Thanks
> >
> >
> >|||Remove the parens in the order by. It is not valid SQL.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:7A469A37-A660-4626-A73A-09BCC1C28F46@.microsoft.com...
> Jason,
> Here is the actual statement: (again, the "where" works fine with multiple
entries)
> ="SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE (" & Parameters!Where.Value & ") ORDER BY (" &
Parameters!OrderBy.Value & ")"
> Do I need to further define whatever in the designer?
> Thanks,
> "Jason Carlson [MSFT]" wrote:
> > This works, I used this type of parameter all the time. Is you query an
> > expression? Or are you using an @. parameter. It will only work as an
> > expression. To pass the parameter on the ULR it must be encoded.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
> > news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> > > In order to allow our users to create custom report queries, we've
created
> > a report with two parameters: "Where" and "OrderBy". In the Where
parameter
> > we pass the columns to be checked (Division = 'south' AND price > 100).
This
> > works great. The "OrderBy" parameter also works, if and only if, one
column
> > name is passed. If we attempt to pass a parameter (both in the designer
and
> > URL string) formatted as "col1, col2", the preview fails when the comma
is
> > encountered. Is it not possible to create a parameter that contains more
> > than one column for the ORDER BY clause (parameter)?
> > >
> > > Thanks
> >
> >
> >

Oracle stored proc with OUT parameter

Hi,

I am calling an Oracle stored proc which contains an IN and an OUT parameter also.

To the stored proc, I pass two reports parameteres. I get following error when I execute the report:

PLS-00306: wrong number or types of arguments in call to <Procedure name>

Where am I going wrong?

TIA,

Tanmaya

Out parameters for Oracle stored procedures are not supported. Only an OUT REF cursor is supported. Basically, you need to write a wrapper stored procedure that has only one OUT REF cursor and no other OUT parameters.

-- Robert

Oracle statement

Hi all,
what do i need to do to use in Oracle query statement the parameter? In SQL
server, I use @.ParameterName but I can not use it like that in Oracle.
In SQL Server: select * from tableA where columnID = @.ParameterName
This works great in SQL
I can not use the same thing in oracle. Please help. ThanksTry Using just the question mark "?". That's what I have been doing. It
seems that the oracle client cannot handle named parameters.
"VNN" <VNN@.hotmail.com> wrote in message
news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> what do i need to do to use in Oracle query statement the parameter? In
> SQL server, I use @.ParameterName but I can not use it like that in Oracle.
> In SQL Server: select * from tableA where columnID = @.ParameterName
> This works great in SQL
> I can not use the same thing in oracle. Please help. Thanks
>|||I use the question mark "?" but I got another error message:
ORA-01036: illegal variable name/number.
I put the where columnName='?' and run the query, it pops up a dialog to
type in the value (dropdown list has null and blank), this is the same
dialog when I use SQL with @.parameterName, I entered the string value and
hit OK, then I got the error dialog box.
Please advice.
"Gregory C. Quinn, Jr." <gquinn@.triad.rr.com> wrote in message
news:X12Od.68699$fE4.11961448@.twister.southeast.rr.com...
> Try Using just the question mark "?". That's what I have been doing. It
> seems that the oracle client cannot handle named parameters.
> "VNN" <VNN@.hotmail.com> wrote in message
> news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> > Hi all,
> >
> > what do i need to do to use in Oracle query statement the parameter? In
> > SQL server, I use @.ParameterName but I can not use it like that in
Oracle.
> >
> > In SQL Server: select * from tableA where columnID = @.ParameterName
> >
> > This works great in SQL
> >
> > I can not use the same thing in oracle. Please help. Thanks
> >
>|||I think Oracle uses a : but I am not sure. I remember it is not a @. and it
is not a ?. Try
select * from tableA where columnID = :ParameterName
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"VNN" <VNN@.hotmail.com> wrote in message
news:u%23jQXLiDFHA.936@.TK2MSFTNGP12.phx.gbl...
> I use the question mark "?" but I got another error message:
> ORA-01036: illegal variable name/number.
> I put the where columnName='?' and run the query, it pops up a dialog to
> type in the value (dropdown list has null and blank), this is the same
> dialog when I use SQL with @.parameterName, I entered the string value and
> hit OK, then I got the error dialog box.
> Please advice.
> "Gregory C. Quinn, Jr." <gquinn@.triad.rr.com> wrote in message
> news:X12Od.68699$fE4.11961448@.twister.southeast.rr.com...
> > Try Using just the question mark "?". That's what I have been doing.
It
> > seems that the oracle client cannot handle named parameters.
> >
> > "VNN" <VNN@.hotmail.com> wrote in message
> > news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> > > Hi all,
> > >
> > > what do i need to do to use in Oracle query statement the parameter?
In
> > > SQL server, I use @.ParameterName but I can not use it like that in
> Oracle.
> > >
> > > In SQL Server: select * from tableA where columnID = @.ParameterName
> > >
> > > This works great in SQL
> > >
> > > I can not use the same thing in oracle. Please help. Thanks
> > >
> >
> >
>|||This depends on what you are doing. Oracle is a bit special. If you use the
graphical query designer it is using OLEDB if you are using the generic it
is using the managed provider. At runtime it uses the managed provider. My
suggestion is to use the generic query designer. Then do this:
select * from sometable where somefield = :ParameterName
With the managed provider that is how a named parameter works.
Here is some additional info from a MS dude:
>>>>>>>>>>>>
Robert Bruckner [MSFT] Mar 15 2004, 8:03 pm show options
Newsgroups: microsoft.public.sqlserver.reportingsvcs
From: "Robert Bruckner [MSFT]" <rob...@.online.microsoft.com> - Find
messages by this author
Date: Mon, 15 Mar 2004 20:00:54 -0800
Local: Mon, Mar 15 2004 8:00 pm
Subject: Re: Dynamic Oracle Query with Parameters Example (not using
Packages/Procs)
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
A good KB article is given at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
The Visual Data Tools (VDT) query designer (2 panes) actually uses OLE DB in
the preview pane. The text-based generic query designer (GQD; 4 panes) uses
the .NET provider for Oracle. Whenever you get strange results in VDT, you
should try it with GQD.
Note: the syntax for parameters in oracle queries depends on the provider
you are using:
* Managed Oracle provider (named parameters):
select from table where ename = :parameter
* OLE DB for Oracle (unnamed parameters):
select from table where ename = ?
The parameter binding between query parameters (accessible on the dataset
dialog) and report parameters (accessible through the menu - report - report
parameters) will be done automatically by the query designer.
Robert M. Bruckner
Microsoft SQL Server Reporting Services
>>>>>>>>>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"VNN" <VNN@.hotmail.com> wrote in message
news:u%23jQXLiDFHA.936@.TK2MSFTNGP12.phx.gbl...
> I use the question mark "?" but I got another error message:
> ORA-01036: illegal variable name/number.
> I put the where columnName='?' and run the query, it pops up a dialog to
> type in the value (dropdown list has null and blank), this is the same
> dialog when I use SQL with @.parameterName, I entered the string value and
> hit OK, then I got the error dialog box.
> Please advice.
> "Gregory C. Quinn, Jr." <gquinn@.triad.rr.com> wrote in message
> news:X12Od.68699$fE4.11961448@.twister.southeast.rr.com...
> > Try Using just the question mark "?". That's what I have been doing.
It
> > seems that the oracle client cannot handle named parameters.
> >
> > "VNN" <VNN@.hotmail.com> wrote in message
> > news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> > > Hi all,
> > >
> > > what do i need to do to use in Oracle query statement the parameter?
In
> > > SQL server, I use @.ParameterName but I can not use it like that in
> Oracle.
> > >
> > > In SQL Server: select * from tableA where columnID = @.ParameterName
> > >
> > > This works great in SQL
> > >
> > > I can not use the same thing in oracle. Please help. Thanks
> > >
> >
> >
>|||That's it. It works great. Thanks very much.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u7kH6diDFHA.3908@.TK2MSFTNGP15.phx.gbl...
> This depends on what you are doing. Oracle is a bit special. If you use
> the
> graphical query designer it is using OLEDB if you are using the generic it
> is using the managed provider. At runtime it uses the managed provider. My
> suggestion is to use the generic query designer. Then do this:
> select * from sometable where somefield = :ParameterName
> With the managed provider that is how a named parameter works.
> Here is some additional info from a MS dude:
>>>>>>>>>>>>
> Robert Bruckner [MSFT] Mar 15 2004, 8:03 pm show options
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> From: "Robert Bruckner [MSFT]" <rob...@.online.microsoft.com> - Find
> messages by this author
> Date: Mon, 15 Mar 2004 20:00:54 -0800
> Local: Mon, Mar 15 2004 8:00 pm
> Subject: Re: Dynamic Oracle Query with Parameters Example (not using
> Packages/Procs)
> Reply to Author | Forward | Print | Individual Message | Show
> original
> | Report Abuse
> A good KB article is given at:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
>
> The Visual Data Tools (VDT) query designer (2 panes) actually uses OLE DB
> in
> the preview pane. The text-based generic query designer (GQD; 4 panes)
> uses
> the .NET provider for Oracle. Whenever you get strange results in VDT, you
> should try it with GQD.
>
> Note: the syntax for parameters in oracle queries depends on the provider
> you are using:
> * Managed Oracle provider (named parameters):
> select from table where ename = :parameter
> * OLE DB for Oracle (unnamed parameters):
> select from table where ename = ?
>
> The parameter binding between query parameters (accessible on the dataset
> dialog) and report parameters (accessible through the menu - report -
> report
> parameters) will be done automatically by the query designer.
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
>>>>>>>>>>>>>>>>>>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
>
>
> "VNN" <VNN@.hotmail.com> wrote in message
> news:u%23jQXLiDFHA.936@.TK2MSFTNGP12.phx.gbl...
>> I use the question mark "?" but I got another error message:
>> ORA-01036: illegal variable name/number.
>> I put the where columnName='?' and run the query, it pops up a dialog to
>> type in the value (dropdown list has null and blank), this is the same
>> dialog when I use SQL with @.parameterName, I entered the string value and
>> hit OK, then I got the error dialog box.
>> Please advice.
>> "Gregory C. Quinn, Jr." <gquinn@.triad.rr.com> wrote in message
>> news:X12Od.68699$fE4.11961448@.twister.southeast.rr.com...
>> > Try Using just the question mark "?". That's what I have been doing.
> It
>> > seems that the oracle client cannot handle named parameters.
>> >
>> > "VNN" <VNN@.hotmail.com> wrote in message
>> > news:%23ZvRckZDFHA.1280@.TK2MSFTNGP10.phx.gbl...
>> > > Hi all,
>> > >
>> > > what do i need to do to use in Oracle query statement the parameter?
> In
>> > > SQL server, I use @.ParameterName but I can not use it like that in
>> Oracle.
>> > >
>> > > In SQL Server: select * from tableA where columnID = @.ParameterName
>> > >
>> > > This works great in SQL
>> > >
>> > > I can not use the same thing in oracle. Please help. Thanks
>> > >
>> >
>> >
>>
>

Oracle Procedure with OUT Parameters

I get this error message when I try to create a DataSet for an Oracle
procedure that has an OUT Parameter.
PLS-00306: wrong number or types of parameters in call to 'procedure name'
The Error happens when I click on Refresh Fields.
I can execute procedures with a REFCURSOR OUT Parameter just fine. I only
get this message when the procedure has other out types like DATE or CHAR.
Any help would be greatly appreciated.
FabianOnly out ref cursors are supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fabian" <Fabian@.discussions.microsoft.com> wrote in message
news:8BE134E6-FE00-48CB-B64A-9EF81FC43BAE@.microsoft.com...
> I get this error message when I try to create a DataSet for an Oracle
> procedure that has an OUT Parameter.
> PLS-00306: wrong number or types of parameters in call to 'procedure name'
> The Error happens when I click on Refresh Fields.
> I can execute procedures with a REFCURSOR OUT Parameter just fine. I only
> get this message when the procedure has other out types like DATE or CHAR.
> Any help would be greatly appreciated.
> Fabian|||Thank you Robert. I wrote a wrapper.
Fabian
"Robert Bruckner [MSFT]" wrote:
> Only out ref cursors are supported. Please follow the guidelines in the
> following article on MSDN (scroll down to the section where it talks about
> "Oracle REF CURSORs") on how to design the Oracle stored procedure:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> To use a stored procedure with regular out parameters, you should either
> remove the parameter (if it is possible) or write a little wrapper around
> the original stored procedure which checks the result of the out parameter
> and just returns the out ref cursor but no out parameter.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Fabian" <Fabian@.discussions.microsoft.com> wrote in message
> news:8BE134E6-FE00-48CB-B64A-9EF81FC43BAE@.microsoft.com...
> > I get this error message when I try to create a DataSet for an Oracle
> > procedure that has an OUT Parameter.
> >
> > PLS-00306: wrong number or types of parameters in call to 'procedure name'
> >
> > The Error happens when I click on Refresh Fields.
> >
> > I can execute procedures with a REFCURSOR OUT Parameter just fine. I only
> > get this message when the procedure has other out types like DATE or CHAR.
> >
> > Any help would be greatly appreciated.
> >
> > Fabian
>
>

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