EXEC as part of a SELECT

I wrote a stored proc that results in a dynamic rowset of different columns.
(not predefined except ID). To Make it available for a join with i.e. a view
,
I want to use an inline-tablereturn function, because this kind of function
doesnt need a predefined Tabledefinition, too.
But: I have just one Select-Statement to work with. And I dont know how to
hook them together.
something like:
Select * From Exec <myproc> <myparams>
(does somebody know how to work with derived tables? its probably a way)Klaus,
Might check out Erland's article:
How to share data between stored procedures
http://www.sommarskog.se/share_data.html
HTH
Jerry
"KlausSarbeach" <KlausSarbeach@.discussions.microsoft.com> wrote in message
news:C87327E7-8988-4DF5-96DB-6F95F9E2AAEC@.microsoft.com...
>I wrote a stored proc that results in a dynamic rowset of different
>columns.
> (not predefined except ID). To Make it available for a join with i.e. a
> view,
> I want to use an inline-tablereturn function, because this kind of
> function
> doesnt need a predefined Tabledefinition, too.
> But: I have just one Select-Statement to work with. And I dont know how to
> hook them together.
> something like:
> Select * From Exec <myproc> <myparams>
> (does somebody know how to work with derived tables? its probably a way)
>

Exclusive selection

Maybe it is really simple but right now it's pretty late and I don't have a clue:
Basicly I have two tables with two columns (first one is numeric)

Table A
1:A
2:A
3:B
5:NULL
7:NULL
8:C

Table B
1:A
2:NULL
3:F
5:F
7:NULL
8:NULL

The result should be:

Result Table
1:A
2:A
3:NULL
5:F
7:NULL
8:C

I tried a variaty of joins, subselects and whatever, but failed.
I would appreciate any help.

Kindest regards,
kromoyou will have to explain what you want

the results do not give a clue

for example, how do you get NULL from 3:B and 3:F ??

what are you trying to do?|||Suppose your tables are created as
CREATE TABLE taba (rb number, val varchar2(1));
CREATE TABLE tabb (rb number, val varchar2(1));
and populated as in your example.

Would this do the job?

SELECT rb, MAX(result)
FROM (
SELECT
a.rb,
DECODE(a.val, b.val, b.val, NULL, DECODE(b.val, NULL, NULL, b.val)) result
FROM TABA a, TABB b
WHERE a.rb = b.rb
UNION
SELECT
b.rb,
DECODE(b.val, a.val, a.val, NULL, DECODE(a.val, NULL, NULL, a.val)) result
FROM TABA a, TABB b
WHERE a.rb = b.rb
)
GROUP BY rb
;|||Sorry I didn't explain it further. Say the first column is named ID and the second VALUE.
The result should be for (A.ID = B.ID) and sort of XOR for VALUES.

IF (A.VALUE = B.VALUE)
A.VALUE [OR B.VALUE, it doesn't matter]

IF ( (A.VALUE IS NOT NULL) AND (B.VALUE IS NULL) )
A.VALUE

IF ( (A.VALUE IS NULL) AND (B.VALUE IS NOT NULL) )
B.VALUE

IF ( (A.VALUE IS NOTNULL) AND (B.VALUE IS NOT NULL) AND (A.VALUE <> B.VALUE))
NULL

I am working with Oracle 9.2 (right now), if there is a special thing for Oracle I'll take it, if there is an general solution I would prefer that one.

Thank you.

Kindest regards,
kromo|||Select ta.id, (CASE WHEN ta.value = tb.value THEN ...)
from tableA ta
INNER JOIN
tableB tb ON
ta.id = tb.id|||Thank you all very much.

I tried the "CASE" approach and it worked like a charm.

You saved my day.

Kindest regards,
kromo

Exclusive OR condition needed for Check Constraint

I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksTry this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>|||Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?

Exclusive OR condition needed for Check Constraint

I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksLaurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allo
ws
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:[vbcol=seagreen]
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>sql

Exclusive OR condition needed for Check Constraint

I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
ThanksLaurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
--
David Portas
SQL Server MVP
--|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:
> >
> > I want to put a check constraint on a table to enforce the following
> > condition:
> >
> > Of four columns A, B, C, D one must have a value and the other three must be
> > null.
> >
> > A is a varchar column, the others are integer.
> >
> > It seemed like I needed to create a bitwise Exclusive OR expression, with
> > some function to return true/false for each column having a value/Null.
> >
> > The best I could do was this:
> >
> > ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> >
> > This stops inserts when 0, 2 or 4 of the columns have values, and allows
> > inserts when 1 column has a value (which is correct). However it also allows
> > inserts when 3 of the columns have values. I have no idea why. Can anyone
> > fix the expression, or give me an alternative expression that fits the
> > requirements?
> >
> > Thanks|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegroups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>

Exclusive OR condition needed for Check Constraint

I want to put a check constraint on a table to enforce the following
condition:
Of four columns A, B, C, D one must have a value and the other three must be
null.
A is a varchar column, the others are integer.
It seemed like I needed to create a bitwise Exclusive OR expression, with
some function to return true/false for each column having a value/Null.
The best I could do was this:
( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
This stops inserts when 0, 2 or 4 of the columns have values, and allows
inserts when 1 column has a value (which is correct). However it also allows
inserts when 3 of the columns have values. I have no idea why. Can anyone
fix the expression, or give me an alternative expression that fits the
requirements?
Thanks
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
isnumeric(len(A)) + isnumeric(B) + isnumeric(C) + isnumeric(D) = 1 ?
|||Try this:
... CHECK (
CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
You could also consider changing your design. Perhaps you only need one
column.
David Portas
SQL Server MVP
|||Laurence,
Perhaps not the shortest nor efficient but this seems to work:
ALTER TABLE <TABLE> ADD CONSTRAINT <CONSTRAINTNAME> CHECK ((A IS NOT NULL
AND B IS NULL AND C IS NULL AND D IS NULL)OR (A IS NULL AND B IS NOT NULL
AND C IS NULL AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NOT NULL
AND D IS NULL) OR (A IS NULL AND B IS NULL AND C IS NULL AND D IS NOT NULL))
HTH
Jerry
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23%233KVUY2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must
> be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also
> allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
>
|||Yet another solution:
CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
CAST( COALESCE(D,C,B) AS varchar(..)), A) )
This solution approach would be cleaner if A had the same data type as
B, C and D. Then it would simply be:
CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
And I have to agree with David. If you need this check, then your data
model might not be properly normalized. In that case you might want to
review your design.
HTH,
Gert-Jan
Laurence Neville wrote:
> I want to put a check constraint on a table to enforce the following
> condition:
> Of four columns A, B, C, D one must have a value and the other three must be
> null.
> A is a varchar column, the others are integer.
> It seemed like I needed to create a bitwise Exclusive OR expression, with
> some function to return true/false for each column having a value/Null.
> The best I could do was this:
> ( isnumeric(len(A)) ^ isnumeric(B) ^ isnumeric(C) ^ isnumeric(D) ) = 1
> This stops inserts when 0, 2 or 4 of the columns have values, and allows
> inserts when 1 column has a value (which is correct). However it also allows
> inserts when 3 of the columns have values. I have no idea why. Can anyone
> fix the expression, or give me an alternative expression that fits the
> requirements?
> Thanks
|||Ah, forget this solution, it is no good :-(
Gert-Jan
Gert-Jan Strik wrote:[vbcol=seagreen]
> Yet another solution:
> CHECK ( COALESCE(A, CAST( COALESCE(B,C,D) AS varchar(..)) ) = COALESCE(
> CAST( COALESCE(D,C,B) AS varchar(..)), A) )
> This solution approach would be cleaner if A had the same data type as
> B, C and D. Then it would simply be:
> CHECK ( COALESCE(A,B,C,D) = COALESCE(D,C,B,A) )
> And I have to agree with David. If you need this check, then your data
> model might not be properly normalized. In that case you might want to
> review your design.
> HTH,
> Gert-Jan
> Laurence Neville wrote:
|||I went with David's solution because it is the easiest to interpret.
Mikito's solution also worked.
I know the table design is unusual and could be normalized. It is
deliberately this way to make certain queries perform faster (less joins to
make).
Thanks for so many quick replies!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1130260200.705449.86860@.g49g2000cwa.googlegro ups.com...
> Try this:
> ... CHECK (
> CASE WHEN A IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN B IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN C IS NOT NULL THEN 1 ELSE 0 END+
> CASE WHEN D IS NOT NULL THEN 1 ELSE 0 END=1) ...
> You could also consider changing your design. Perhaps you only need one
> column.
> --
> David Portas
> SQL Server MVP
> --
>

Exclude columns from a select statement

Hi,
Simple question:
Is there any way to exclude some columns with a SELECT Table1.* statement?
Thanks for any infosnope.
You can name the columns that you wish to return or create a dynamic sql statement with the columns in.

It is considered bad practise to use select * for returning data.|||For example:

table collums: id - name - adress - phone

If you wish select only name you can use the following query:

"SELECT name FROM table"

To add new collums you must add it and put behind a "," like:

"SELECT name,phone FROM table"

[ ]'s|||Hi,

In fact, I've an HUGE view with many column and i have to select all the fields without the description ones for a translation process. I'm forced to use SELECT Col1, Col2 ,,,,,, Col30... FROM View.

If a such notation exist I just have to write SELECT all appart(Col20,Col22,Col25) FROM View

Thanks anyway for answers

Exclude column from replication, but leave column on subscriber.

It seems I can't figure out how to have two tables, say integer columns named
A, B, and C, with a rowguid column.
I want that table structure on my subscriber (A, B, C, rowguid) but I only want
to replicate data from columns A and B.
As it stands, replication tries to create it on the subscriber as A, B,
rowguid, leaving the C column off.
Thanks for any help.
Brian K
should have specified, this is merge replication, SQL Server 2000.
Brian
In article <4IJtc.16319051$Id.2708018@.news.easynews.com>, Brian Keener wrote:
> It seems I can't figure out how to have two tables, say integer columns named
> A, B, and C, with a rowguid column.
> I want that table structure on my subscriber (A, B, C, rowguid) but I only want
> to replicate data from columns A and B.
> As it stands, replication tries to create it on the subscriber as A, B,
> rowguid, leaving the C column off.
> Thanks for any help.
> Brian K
|||Brian,
is it a requirement that the rowguid changes independantly on publisher and
subscriber? If not and the guid can't change then why not include it?
Regards,
Paul Ibison
|||In article <OT6YKXNREHA.3300@.tk2msftngp13.phx.gbl>, Paul Ibison wrote:
> Brian,
> is it a requirement that the rowguid changes independantly on publisher and
> subscriber? If not and the guid can't change then why not include it?
> Regards,
> Paul Ibison
>
Hmm, rowguid is not a column I'm worried about. It can stay the same
across the board. Row C need to be able to change independently on
the two tables.
Brian K
|||Brian,
you'd have to edit the merge triggers to enable this by preventing entries
into MSmerge_contents from changes to C, and I wouldn't recommend it. It
would also make validation impossible. You could however achieve the same
functionality using views - the view would be a join returning columns A, B,
C, rowguid while the underlying tables would be
table1: A,B,rowguid and
table2: C,pk (one of A or B)
Then you just replicate table1. If changes can occur to cols A/B and C in
one update then you'll need to set up instead of triggers.
HTH,
Paul Ibison
|||its not the merge triggers you edit for this. Its the stored procedures that
is uses which look like this
sp_ins_3477CF08A8A2481D3269F4FE171245B8
look for a statement that looks like this.
if @.retcode<>0 or @.@.ERROR<>0
begin
set @.errcode= 0
goto Failure
end
insert into [dbo].[authors] ( [au_id] , [au_lname] , [au_fname] , [phone] ,
[address] , [city] , [state] , [zip] , [contract] , [msrepl_tran_version] ,
[rowguid] ) values ( @.p1 , @.p2 , @.p3 , @.p4 , @.p5 , @.p6 , @.p7 , @.p8 , @.p9 ,
@.p10 , @.p11 )
select @.rowcount= @.@.rowcount, @.error= @.@.error
do this for both the insert and update procedures
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23zbYbaOREHA.3012@.tk2msftngp13.phx.gbl...
> Brian,
> you'd have to edit the merge triggers to enable this by preventing entries
> into MSmerge_contents from changes to C, and I wouldn't recommend it. It
> would also make validation impossible. You could however achieve the same
> functionality using views - the view would be a join returning columns A,
B,
> C, rowguid while the underlying tables would be
> table1: A,B,rowguid and
> table2: C,pk (one of A or B)
> Then you just replicate table1. If changes can occur to cols A/B and C in
> one update then you'll need to set up instead of triggers.
> HTH,
> Paul Ibison
>
|||Hilary,
I agree that it could be done in the stored procedure, but think the trigger
modification is simpler:
For an update, what I had in mind is something like
IF Not UPDATE(C)
BEGIN
existing trigger code
END
The insert and delete triggers could be left as they are, as both should
propagate through the normal merge replication.
Cheers,
Paul Ibison
|||Paul
After posting my solution I had reservations about it.
The advantage of your solution is the update trigger will fire but won't
write replication metadata for updates to the columns you wish to exclude.
My solution is that all updates will be propagated to the subscriber, but
will not be applied.
In retrospect I think your solution is perhaps the better one.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23c9lNXaREHA.2408@.tk2msftngp13.phx.gbl...
> Hilary,
> I agree that it could be done in the stored procedure, but think the
trigger
> modification is simpler:
> For an update, what I had in mind is something like
> IF Not UPDATE(C)
> BEGIN
> existing trigger code
> END
> The insert and delete triggers could be left as they are, as both should
> propagate through the normal merge replication.
> Cheers,
> Paul Ibison
>
|||In article <#zbYbaOREHA.3012@.tk2msftngp13.phx.gbl>, Paul Ibison wrote:
> Brian,
> you'd have to edit the merge triggers to enable this by preventing entries
> into MSmerge_contents from changes to C, and I wouldn't recommend it. It
> would also make validation impossible. You could however achieve the same
> functionality using views - the view would be a join returning columns A, B,
> C, rowguid while the underlying tables would be
> table1: A,B,rowguid and
> table2: C,pk (one of A or B)
> Then you just replicate table1. If changes can occur to cols A/B and C in
> one update then you'll need to set up instead of triggers.
> HTH,
> Paul Ibison
>
Pretty much what I assumed.
I've already implemented this as a view in our test environment, but
thanks for the information.
Brian K

Excel To Sql Server 2005

HI Friends,

i was created on xls file in my dektop name (student) with 2 columns

sno sname marks

1 a 10

2 b 20

3 c 30

4 d 40

these records added to excel file only

Now : i created a table in sql server 2005

sno :numeric(18, 0)

sname :varchar(50)

marks :numeric(18, 0)

NOW in ssis package

1) i place excel datasource (selected the student excel sheet$1)

2) i placed a lookup controle and selected the server student table

Question : when we map the excel sno= server sno

ERROR : data type mismatch to any of the column ?

please give me the related steps

You need to use a data convertor component in the data flow and convert the columns to be the same as the destination. SSIS does not allow implicit conversion.|||

Hint: If you use the editor correctly there is no need to create the table in SQL.

Add a Excel source

Use Excel Editor to look for your xls file

Add an SQL Server Destination -->Use an OLE DB Provider

Establish the path from Excel to SQL Server

Confgiure destination using the OLE DB Provider

Use the SQL Destination Editor to generate the table -->important step

You are done.

Excel to MSSQL

hi

i have an excel sheet with 2 columns
regno name
-- --
and a table regdetails of the form

regdetails
(
regno bigint
name varchar(30)
)

my requirement is to export the data from excel sheet to the table.
the excel sheet may have duplicate entries but i want only distinct
records to be exported to the table.

can i create a DTS package to perform this ?

please post ur commentsYes you can accomplish this with DTS, import to a temp table and delete duplicate rows then copy to original table. May check http://www.sqldts.com website for more information.

Excel Time-Series Addin Problem

Have an Excel Spreadsheet with two columns and 52 rows (retail sales)

Date Qty

1 5

2 8

etc

Tried to run the above Addin. Error message:

"Session mining object (...) can not be created on this instance."

Any ideas?

Thanks,

Sergei.

You need to set the DataMinnig\AllowSessionMiningModels property to true on your Analysis Services server. Run SQL Server Management Studio, right click on the server in the object explorer, and select properties to set it.

Excel task has no output columns

I created an Excel Source and used a query to get the data,i.e

SELECT F1,F2,F3,F4,F5,F6,F7 FROM [Fut Days$A20:G1480]

The query works fine, the preview returns the rows, but SSIS will not generate output Columns nor will it let me manually add them? Am I missing something?

Here is some information that may answer your question:

1. In the Excel Source Editor, navigate to the Columns page.

You can modify the output column names that are listed in the External Column/Output Column table by clicking in the field and typing a new name.|||

Hi,

The problem is there's no output columns listed, and you cannot manually add them. The problem is when I specify a query instead of an entire worksheet, the External Column meta-data is not added like it is when you select a worksheet by name.

Dave

|||

Hi,

Have you tried naming the columns in your excel source as some meaningful names and then try to connect your excel source. It will give you all the names ,after that you can select the ones you need and dont tick the checkboxes of the ones you dont need.

Regards,

Vikram Kansal

excel source with optional columns

Hi:

I use a SSIS package to loop thro a folder and load data from multiple excel files to a SQL2005 table. Works fine except when an excel has a missing col.

Col names in xls are always a subset of col names in the table. The missing cols are random, else I would just have made another package:-)

Once a missing column is found, I get runtime and design time errors, and metadata problems. How can a get SSIS to ignore missing columns?

TIA

I recently solved this problem using a dynamically built select statement. Is it always just 1 column that's missing or do you need to load a dynamic number of columns? If it's a truly dynamic then the algorithm is a little more complex...|||

Thanks for your response. Request you tell me more aboout it.

I did the whole thing in BIDS in a SSIS project, using a ForEach container, a Excel Source and an OleDB destination. I was hoping to achieve my objectives with these objects and their settings :-).

|||I used a For Each Loop and then a For Loop to solve this problem.

The first For Each Loop iterates threw the columns names in the spreadsheet. It contains a script component that counts the columns storing the result in a variable. There might be a more efficient way to count columns but I couldn't figure out how.

The second For Loop container uses this counter variable to select and load each column one at a time. It contains 2 components; a script component that builds a select statement and a data flow task that actually moves the data using the select statement.

Here is the script code that dynamically builds each select statement:

Public Sub Main()
Dim SelectCommand As String
Dim WorksheetName As String
Dim ColumnLoopIndex As Integer
WorksheetName = Dts.Variables("WorksheetName").Value.ToString
ColumnLoopIndex = CInt(Dts.Variables("ColumnLoopIndex").Value)
SelectCommand = "Select F" & ColumnLoopIndex.ToString & " AS CurrentColumn from [" & WorksheetName & "]"
Dts.Variables("SelectCommand").Value = SelectCommand
Dts.TaskResult = Dts.Results.Success
End Sub

Please note: Depending on your data and how dynamic you want the

package to be you could skip the second For Loop and build a single select

statement that loads all of the columns. In this case your dynamically built select

statement would contain return fields like "SELECT F1, F2, NULL AS F3, NULL AS F4

FROM [myworksheetname]" to account for missing F3 and F4 columns.

Excel Source Returning NULL

I've a package that has a excel source. But i'm having a strange problem with it. One of the columns in the source file have a lot of null values but not all of them. But when i run the package a put a data viewer right after the source and i can see that it's showing that the few fields that should have values are also null. I've tried a lot of things but they didn't work. I need some help and fast if possible.
Example: Source file.xls
Name Grade OtherGrade
John 30 30.23
In the DataViewer
Name Grade OtherGrade
John 30 NULL

thanks

Adriano Coura

Try setting IMEX=1 in your Excel connection properties.

http://support.microsoft.com/default.aspx/kb/194124|||I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.|||

I have never heard of decimal points being stripped out.

Are you in fact in a locale that uses the comma rather than the period as in US settings? (One of your messages shows the dot, the other message shows a comma.) If regional settings seem to be an issue, I would be inclined to try an OLE DB Connection Manager and set Locale ID, or to add "Locale Identifier=nnn" to your Excel connection string.

-Doug

|||

abcoura wrote:

I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.

This seems to indicate to me that perhaps the data isn't stored as a number, but rather it has a format applied to it in Excel. Can you confirm or deny this?|||The regional settings are all ok. i may have write two diferent things but that′s not the problem. But it was something like Phil said, sometimes the SSIS consider the column number and sometimes text. In the excel they are all number. But its working now. Thanks for all the replys they're very useful.

Thanks Again|||I will take advantage of this tread to ask the important question about the problem. Here in Brazil the id of a person can come with ou without letters like: m11.333.444 or 11.333.444. So if i have a column and in the first 8 rows i got 5 with only numbers and 3 with letters. So the excel source handle the column as double and put nulls on every row that got a letter in it. I'm thinking if there's a way to always consider the column as text, avoiding the problem with looking to the values as double. Anybody can help solve this question. And putting the excel in order by the value so that the ones with a letter come first doesn't count.

Thanks in Advance.|||You need to override the "guess" of the Excel data type and set it to a text string.|||

IMEX=1. Please see Phil's response earlier in this thread.

-Doug

Excel Source Returning NULL

I've a package that has a excel source. But i'm having a strange problem with it. One of the columns in the source file have a lot of null values but not all of them. But when i run the package a put a data viewer right after the source and i can see that it's showing that the few fields that should have values are also null. I've tried a lot of things but they didn't work. I need some help and fast if possible.
Example: Source file.xls
Name Grade OtherGrade
John 30 30.23
In the DataViewer
Name Grade OtherGrade
John 30 NULL

thanks

Adriano Coura

Try setting IMEX=1 in your Excel connection properties.

http://support.microsoft.com/default.aspx/kb/194124|||I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.|||

I have never heard of decimal points being stripped out.

Are you in fact in a locale that uses the comma rather than the period as in US settings? (One of your messages shows the dot, the other message shows a comma.) If regional settings seem to be an issue, I would be inclined to try an OLE DB Connection Manager and set Locale ID, or to add "Locale Identifier=nnn" to your Excel connection string.

-Doug

|||

abcoura wrote:

I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.

This seems to indicate to me that perhaps the data isn't stored as a number, but rather it has a format applied to it in Excel. Can you confirm or deny this?|||The regional settings are all ok. i may have write two diferent things but that′s not the problem. But it was something like Phil said, sometimes the SSIS consider the column number and sometimes text. In the excel they are all number. But its working now. Thanks for all the replys they're very useful.

Thanks Again|||I will take advantage of this tread to ask the important question about the problem. Here in Brazil the id of a person can come with ou without letters like: m11.333.444 or 11.333.444. So if i have a column and in the first 8 rows i got 5 with only numbers and 3 with letters. So the excel source handle the column as double and put nulls on every row that got a letter in it. I'm thinking if there's a way to always consider the column as text, avoiding the problem with looking to the values as double. Anybody can help solve this question. And putting the excel in order by the value so that the ones with a letter come first doesn't count.

Thanks in Advance.|||You need to override the "guess" of the Excel data type and set it to a text string.|||

IMEX=1. Please see Phil's response earlier in this thread.

-Doug

EXCEL -SL SERVER 2005

Hi friends

student table contain the 3 columns : SNO SNAME MARKS

by using these controles i can able to upload the records (which is not exist in the database)

Excel Source 1 --student.xls

Data Conversion 1 --for destination datatype convertion

Fuzzy Lookupmap with database student table with sno inner join

Conditional Split (if simularity =1 then ignored the record) else inserted the database

OLE DB Destination save the new records

+++++++++++++++++++++++++++++++++++NOW ++++++++++++++++++++++++++++++++++++

in student.xls containt 7 records (1-7)

in student table(server) containt 7 records (1-7)

but marks is diffrent from excel sheet NOW

i want to update the marks ,field only (may be tomorow more than 1 column i have to update)

== SIMULTANIOUSLY how to insert a new record and EXISTING RECORD update only marks ========

REGRADS

KOTI

Basically you want to update the row if it already exists in the target table. If that is the case, then this thread will help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1

Excel Rendering - Urgent

Hello,
I have a fairly simple report (with a lot of columns) that I'm trying to
export to Excel. Everytime I do this, I get the following error:
"Excel Rendering Extension: Width of excel cell in the excel sheet exceeded
the maximum limit of 1726.5 Points."
I know that 1726.5 equates to 255 characters in a column, but when I export
to CSV and check the lengths of every value, none of them reach 255.
Is there a workaround to this bug?
Any guidance on this matter would be much apprecited.
Best Regards,
Benjamin Pierce
Open Text CorporationThe experience I have is when the report is empty and you want to render it
to Excel, RS will combine all the columns into one single cell in Excel. One
way to solve this is to shrink the width of all the columns so that the total
length is less than 255 characters.
"Benjamin Pierce" wrote:
> Well, after much trial and error I found the problem.
> I had two tables. A summary table then a "data dump" below that. On the
> first table, I had the "PageBreakAtEnd" property set to true. When I
> changed it to False, the error went away.
> As a workaround, I attempted to set the "PageBreakAtStart" property of the
> second table to true, but this re-introduced the error.
> @. Microsoft: Is this a known bug? I'm attaching my RDL for further
> analysis. It seems like a fairly serious flaw in the Excel Renderer.
> Please let me know if you need more info.
>
> Regards,
> Benjamin Pierce
> Open Text Corporation
>
>
> "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> news:eRd9aCymEHA.3868@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I have a fairly simple report (with a lot of columns) that I'm trying to
> > export to Excel. Everytime I do this, I get the following error:
> >
> > "Excel Rendering Extension: Width of excel cell in the excel sheet
> exceeded
> > the maximum limit of 1726.5 Points."
> >
> > I know that 1726.5 equates to 255 characters in a column, but when I
> export
> > to CSV and check the lengths of every value, none of them reach 255.
> >
> > Is there a workaround to this bug?
> >
> > Any guidance on this matter would be much apprecited.
> >
> >
> >
> > Best Regards,
> >
> > Benjamin Pierce
> > Open Text Corporation
> >
> >
> >
>
>
>|||The fix will be available in the next service pack (SP2).
You may also contact Microsoft Product Support Services. See
http://support.microsoft.com/default.aspx?pr=cntactms&style=home. A hotfix
for the issue is available. The KB article (883501) should be available
soon.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Benjamin Pierce" <bpierce@.opentext.com> wrote in message
news:eRd9aCymEHA.3868@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a fairly simple report (with a lot of columns) that I'm trying to
> export to Excel. Everytime I do this, I get the following error:
> "Excel Rendering Extension: Width of excel cell in the excel sheet
exceeded
> the maximum limit of 1726.5 Points."
> I know that 1726.5 equates to 255 characters in a column, but when I
export
> to CSV and check the lengths of every value, none of them reach 255.
> Is there a workaround to this bug?
> Any guidance on this matter would be much apprecited.
>
> Best Regards,
> Benjamin Pierce
> Open Text Corporation
>
>|||This patch appears to fix the stated problem, but creates another.
This is not a catastrophe; I will structure the report differently to work
around the problem, but I thought RS Engineering might want to hear about it.
The report contains a table followed by a textbox. One of the cells in the
table contains a formula like this:
=ReportItems!TextBoxInThisTable.Value *
ReportItems!TextBoxOnTheSecondPage.Value
This is rendered properly within the report designer and to excel. In
excel, the table data and the standalone textbox are rendered correctly and
the cell containing the formula is calculated correctly. The excel version
retains the formula which something like: _123 * _456.
So far so good.
The problem arises when I define a PageBreakAfter attribute on the table.
Before applying the hotfix, when I attempted to save as excel, report
designer generated the error as described in kb883501: "Excel Rendering
Extension : Width of excel cell in the excel sheet exceeded the maximum limit
of 1726.5 Points."
After the hotfix, the report designer saves the file, but the resulting file
contains a fatal flaw:
The cell containing the formula simply displays the value contained in
ReportItems!TextBoxInThisTable. When I click on the offending cell to view
the formula, Excel crashes with this signature:
AppName: excel.exe AppVer: 11.0.6355.0 AppStamp:40aa979f
ModName: excel.exe ModVer: 11.0.6355.0 ModStamp:40aa979f
fDebug: 0 Offset: 0010be7e
My guess is that this is related to the fact that Excel has always disliked
formulae that refer to cells in other sheets, though there are ways of
"tricking it" into working.
Cheers,
Jeff Gilbert|||I've created a simple rdl which reproduces the problem. It can be found
attached to SRX050121600563, or just let me know and I can provide it.
-Jeff|||Does this SP need to be applied to the Report Server as well or simply on
workstations developing?
"Robert Bruckner [MSFT]" wrote:
> The fix will be available in the next service pack (SP2).
> You may also contact Microsoft Product Support Services. See
> http://support.microsoft.com/default.aspx?pr=cntactms&style=home. A hotfix
> for the issue is available. The KB article (883501) should be available
> soon.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> news:eRd9aCymEHA.3868@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I have a fairly simple report (with a lot of columns) that I'm trying to
> > export to Excel. Everytime I do this, I get the following error:
> >
> > "Excel Rendering Extension: Width of excel cell in the excel sheet
> exceeded
> > the maximum limit of 1726.5 Points."
> >
> > I know that 1726.5 equates to 255 characters in a column, but when I
> export
> > to CSV and check the lengths of every value, none of them reach 255.
> >
> > Is there a workaround to this bug?
> >
> > Any guidance on this matter would be much apprecited.
> >
> >
> >
> > Best Regards,
> >
> > Benjamin Pierce
> > Open Text Corporation
> >
> >
> >
>
>|||It needs to be applied on the report server also.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:1B570DFA-5592-4557-914A-740474FBBFDF@.microsoft.com...
> Does this SP need to be applied to the Report Server as well or simply on
> workstations developing?
> "Robert Bruckner [MSFT]" wrote:
>> The fix will be available in the next service pack (SP2).
>> You may also contact Microsoft Product Support Services. See
>> http://support.microsoft.com/default.aspx?pr=cntactms&style=home. A
>> hotfix
>> for the issue is available. The KB article (883501) should be available
>> soon.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
>> news:eRd9aCymEHA.3868@.TK2MSFTNGP11.phx.gbl...
>> > Hello,
>> >
>> > I have a fairly simple report (with a lot of columns) that I'm trying
>> > to
>> > export to Excel. Everytime I do this, I get the following error:
>> >
>> > "Excel Rendering Extension: Width of excel cell in the excel sheet
>> exceeded
>> > the maximum limit of 1726.5 Points."
>> >
>> > I know that 1726.5 equates to 255 characters in a column, but when I
>> export
>> > to CSV and check the lengths of every value, none of them reach 255.
>> >
>> > Is there a workaround to this bug?
>> >
>> > Any guidance on this matter would be much apprecited.
>> >
>> >
>> >
>> > Best Regards,
>> >
>> > Benjamin Pierce
>> > Open Text Corporation
>> >
>> >
>> >
>>

Excel rendering - merged columns etc.

Hi guys,

i have a client who is dissatisfied with the merged columns and rows produced when SSRS renders to Excel, it prevents them from easily copying and pasting data into another workbook.

Is there going to be any work done in this area of the product in the near future? My understanding is that this 'issue' has been around for quite some time, and shortly after the SSRS2005 RTM release the team were going to look at tidying up the Excel rendering. Can anyone from MS make an official comment on this?

Thanks,

sluggy

Hello,

This is a well known issue that people often encounter when rendering to Excel. First, I suggest reading this blog post which describes the problem and suggests several ways to reduce cell merging:

http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx

There will be some improvement in this area in the next major release of Reporting Services. We have made some refinements to the renderer to prevent any adverse merging that is a result of internal rounding issues. This means that overall you should see a reduction in the number of merged cells, but you will still need to proactively make sure that your report items align properly as described in the referenced blog article.

Best regards,
Chris

|||

Chris Baldwin - MSFT wrote:

There will be some improvement in this area in the next major release of Reporting Services. We have made some refinements to the renderer to prevent any adverse merging that is a result of internal rounding issues.

Thanks Chris, that was the answer i was after, i can now tell the customer with some certainty that there will be an improvement in the future. In my particular case, i don't care about the header of the reports, i just care about the merged cells in the data area; i have seen cases where one table column spans three excel columns (merged into one), but when those excel columns are unmerged the data in the first few rows could be in colunmn 3 and for the rest of the rows it will be in column 2, with column 1 containing the column title - merged cells would be easier to live with if the data (and title) was in consistent columns once unmerged.

Thanks for you answer :)

sluggy

Excel into Temp table and validations in SSIS

i have an excel sheet with about 30 columns of data ...i want to validate all the data in these cells of the excel through SSIS.
I want to get this data to a temporary table before running my validation stored proc...how do i get this data from the excel to the temp table , this temp table should accept all the data from the excel file in whatever form it is there should be no rows that get discarded while filling this table from the excel.
Moreever how do i get the column header data if the first row in the excel contains Column names how do i get these names and validate them if they are conferring to a
set of names.

Excel sheet ::

ColName1 ColName 2 ........ColName30

ColData11 ColData12..........ColData130

.....
..... ..... .. ...
..... ..... .. ...
Temp table ::

ColName1 ColName2 .. ...... ColName30 IsValid Description

Also can i do validations like Datatype and Length directly in SSIS ? or do i need to do it with a stored proc
Please Help....

Thanks
Clayton

Hi,

In SSIS, when you are transferring data from one Excel File to Another File you have to create an Excel Connection Manager for Source and Destination and in that you have an option as "First Row has Column Names".

But I am not sure about the validation at this point of time. Will verify and let you know that.

Thanks,

Prakash Srinivasan

Excel into Temp table and validations in SSIS

i have an excel sheet with about 30 columns of data ...i want to validate all the data in these cells of the excel through SSIS.
I want to get this data to a temporary table before running my validation stored proc...how do i get this data from the excel to the temp table , this temp table should accept all the data from the excel file in whatever form it is there should be no rows that get discarded while filling this table from the excel.
Moreever how do i get the column header data if the first row in the excel contains Column names how do i get these names and validate them if they are conferring to a
set of names.

Excel sheet ::

ColName1 ColName 2 ........ColName30

ColData11 ColData12..........ColData130

.....
..... ..... .. ...
..... ..... .. ...
Temp table ::

ColName1 ColName2 .. ...... ColName30 IsValid Description

Also can i do validations like Datatype and Length directly in SSIS ? or do i need to do it with a stored proc
Please Help....

Thanks
Clayton

Hi,

In SSIS, when you are transferring data from one Excel File to Another File you have to create an Excel Connection Manager for Source and Destination and in that you have an option as "First Row has Column Names".

But I am not sure about the validation at this point of time. Will verify and let you know that.

Thanks,

Prakash Srinivasan