case expression plus outer join in ole db source

I'm trying to generate the data for a 2-column table, where both columns are defined as NOT NULL and the second column is a uniqueidentifier.

In SQL Server Management Studio, this works fine:

insert into table_3(column_a, column_b)

select table_1.column_a, (case when table_2.column_b is NULL then newid() else table_2.column_b end) as column_b

from table_1 left outer join table_2 on table_1.column_c = table_2.column_c

That is, column_b of the SELECT result has no NULL values, and all 35,986 rows are successfully inserted into a previously empty table_3. (If I comment out the INSERT INTO clause and project table_2.column_b instead of "(case ... end) as column_b", the SELECT result includes 380 rows with a NULL in column_b, so I know the case expression plus the outer join are working as expected.)

But when I use the SELECT query as the SQL command in an OLE DB Source component that is connected directly to the OLE DB Destination for the result table, I get this error:

There was an error with input column "column_b" (445) on input "OLE DB Destination Input" (420

The column status returned was: "The value violated the integrity constraints for the column.".

And sure enough, when I modify the result table to allow NULL in column_b, truncate it, and re-run the data flow, it inserts the exact same 380 rows with a NULL in column_b among the 35,986 rows.

So what is SSIS doing to screw up the results of the SELECT command?

Kevin,

Can you see the values of column_b when you use the 'preview' of the OLE DB source?

Did you check twice the mapping tab in your OLE DB destination to make sure nothing is missing?

Have you used a data view right before the OLE DB Destination to check the values of column_b are shown correctly?

If the answer to those 3 questions is yes, I am affraid I could not help you.

Rafael Salas

|||Could there be an issue in executing the newid() function?|||

Argh, the problem was due to operator error: somehow the source component SQL statement had gotten out of sync with the corresponding variable. How embarrassing!

But many thanks to Rafael and Phil, who got me to examine the data flow task closely enough to discover my error.

Cascading parameters problem : First parameter is textbox

Hi,
I am using cascading parameters in my report. The first paramter is a
servername which user types in a TextBox. The second parameter is
populated based on the first parameter.
The problem is secord paramter drop down list doesn't get populated
unless I press "View Report" button in the preview mode.
Is there any way to populate second parameter (drop downlist) as user
finished typing the first parameter (TextBox)
please help,
regards,
SAchinUser needs to press "Tab" key to populate and need not press "view report"
Amarnath
"sachin laddha" wrote:
> Hi,
> I am using cascading parameters in my report. The first paramter is a
> servername which user types in a TextBox. The second parameter is
> populated based on the first parameter.
> The problem is secord paramter drop down list doesn't get populated
> unless I press "View Report" button in the preview mode.
> Is there any way to populate second parameter (drop downlist) as user
> finished typing the first parameter (TextBox)
> please help,
> regards,
> SAchin
>

cascading parameters in Webpart

Hi,
In a Webpart, I have a report with 2 parameters.. the second parameter is
dependent on the first parameter.. I have noticed that when I changed the
first parameter, the values of the second parameter doesn't changed.., so I
got an error..
Can anybody help me?Do you get the same behavior when not in the webpart?
--
| Thread-Topic: cascading parameters in Webpart
| thread-index: AcWCyilGfXMDE2rkQn6X17V43/TYaw==| X-WBNR-Posting-Host: 195.6.59.36
| From: "=?Utf-8?B?cjM4ODA0Mg==?=" <r388042@.discussions.microsoft.com>
| Subject: cascading parameters in Webpart
| Date: Thu, 7 Jul 2005 01:02:03 -0700
| Lines: 7
| Message-ID: <9C86DC82-335A-4E95-93FB-4D3DE94B72A4@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:47555
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi,
| In a Webpart, I have a report with 2 parameters.. the second parameter is
| dependent on the first parameter.. I have noticed that when I changed the
| first parameter, the values of the second parameter doesn't changed.., so
I
| got an error..
| Can anybody help me?
|
||||No. The application runs correctly in Visual Studio.net or in the reporting
services server.
I thinks that the problem is due to the parameters or a property of the
Webpart. But I don't know exactly.
""Brad Syputa - MS"" wrote:
> Do you get the same behavior when not in the webpart?
> --
> | Thread-Topic: cascading parameters in Webpart
> | thread-index: AcWCyilGfXMDE2rkQn6X17V43/TYaw==> | X-WBNR-Posting-Host: 195.6.59.36
> | From: "=?Utf-8?B?cjM4ODA0Mg==?=" <r388042@.discussions.microsoft.com>
> | Subject: cascading parameters in Webpart
> | Date: Thu, 7 Jul 2005 01:02:03 -0700
> | Lines: 7
> | Message-ID: <9C86DC82-335A-4E95-93FB-4D3DE94B72A4@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:47555
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi,
> | In a Webpart, I have a report with 2 parameters.. the second parameter is
> | dependent on the first parameter.. I have noticed that when I changed the
> | first parameter, the values of the second parameter doesn't changed.., so
> I
> | got an error..
> | Can anybody help me?
> |
> |
>

cascading parameters in a Webpart

In a webpart, I have a report with 2 parameters..
the second parameter is dependent on the first parameter..
I have noticed that when I changed the first parameter, the values of the second parameter doesn't changed.., so I got an error..
Can you help me?Hi,
I am having the exact same problem. It seems that the default values set initially in the report aren't being carried over to all of the parameters in the web parts.
I have not been able to find a solution yet. Please let me know if you have an answer.
Thanks!|||Hi
I got something similar did you get an answer?

Cascading Parameters

I have a report that is based on 2 listboxes, the second one's values dependent on the value selected in the first box. How would I display all values in both list boxes on the report if I so decided?

Thanks,

The Rook

I'd be very interested to see how you got one parameter's available values to be dependant on another's. Every way I've tried, I've had a forward dependency error.

Do you mean all available values, or the selected values?

|||I meant the "selected values." MSDN has a walkthrough that's very helpful and it's URL is: http://msdn2.microsoft.com/en-us/library/aa337426(d=printer).aspx - good luck!|||

Thanks for the link.

As for displaying the selected values on the report, that depends. If you are using just single-valued parameters, you can just drop a textbox for each parameter on the page, and set its value to the parameter. If you have a multi-valued parameter, you can try =Join(Parameters!Foo, ", ") for a comma-separated list of selected values. If you wanted something fancier, the only way I can think of is to use that same join syntax to pass the selected parameters to a query that returns the rowset that can populate, say, a table.

cascading parameter help - solution

I a report that will have two parameters (Year and Account name), I'm having trouble setting up the second parameter to be filtered based on the value of the first parameter.

here is the second parameter with a value hard coded.(see bold text) this works fine when I run the report.

WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,([Job Complete Date].[Year].&[2006],[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]

When I try the following code where I have added in the equal sign and double quotes I get an error message (see below)

= "WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,(" & Parameter!JobCompleteDateYear.value & ",[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]"

error message

TITLE: Microsoft Visual Studio

The query cannot be retrieved from the query builder.
Check the query for syntax errors.
Reporting Services will continue to use the most recent valid query.

ADDITIONAL INFORMATION:

Query preparation failed. (Microsoft.AnalysisServices.Controls)

Query (1, 1) Parser: The syntax for '=' is incorrect. (msmgdsrv)

BUTTONS:

OK

Any suggestions ?

thanks

I found my solution. I needed to use the Strtomember function and inserted the parameter name as @.parametername

here is the final code

before

WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,([Job Complete Date].[Year].&[2006],[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]

after

WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,(Strtomember(@.JobCompleteDateYear),[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]

cascading parameter help

I a report that will have two parameters (Year and Account name), I'm having trouble setting up the second parameter to be filtered based on the value of the first parameter.

here is the second parameter with a value hard coded.(see bold text) this works fine when I run the report.

WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,([Job Complete Date].[Year].&[2006],[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]

When I try the following code where I have added in the equal sign and double quotes I get an error message (see below)

= "WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,(" & Parameter!JobCompleteDateYear.value & ",[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]"

error message

TITLE: Microsoft Visual Studio

The query cannot be retrieved from the query builder.
Check the query for syntax errors.
Reporting Services will continue to use the most recent valid query.

ADDITIONAL INFORMATION:

Query preparation failed. (Microsoft.AnalysisServices.Controls)

Query (1, 1) Parser: The syntax for '=' is incorrect. (msmgdsrv)

BUTTONS:

OK

Any suggestions ?

thanks

I found my solution. I needed to use the Strtomember function and inserted the parameter name as @.parametername

here is the final code

before

WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,([Job Complete Date].[Year].&[2006],[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]

after

WITH MEMBER [Measures].[ParameterCaption] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[CUSTOMER JOB].[ACCOUNT NAME].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,FILTER([CUSTOMER JOB].[ACCOUNT NAME].ALLMEMBERS,(Strtomember(@.JobCompleteDateYear),[Measures].[Billed Sales Amount])) ON ROWS FROM [DW PREP ARCHIVE]

cascade combo boxes

I follow sql coding for cascading combo boxes that populates them
if the first one populates the cdname the second one should populate the cd group and the third one the composers with the songs or the samthing with music hymnals. I am trying the steps they aren't populating. Where are simple books on this?

mikevds@.optonline.netI follow sql coding for cascading combo boxes that populates them
if the first one populates the cdname the second one should populate the cd group and the third one the composers with the songs or the samthing with music hymnals. I am trying the steps they aren't populating. Where are simple books on this?

mikevds@.optonline.net

what language are you using??

Cartesian Join

Hello,
I have a table tblCustomer with fields CustomerName and Revenue. I have a
second table with Revenue ranges
LowRange HighRange Bucket
0 10 1
10 20 2
20 30 3
Is there a way to use a Cross Join to create the following output
Customer Revenue Bucket
Cust1 5 1
Cust2 28 3
--
Thanks in advance,
Steven"Steven K" <skaper@.troop.com> wrote in message
news:e5ySMdqvDHA.2148@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table tblCustomer with fields CustomerName and Revenue. I have a
> second table with Revenue ranges
> LowRange HighRange Bucket
> 0 10 1
> 10 20 2
> 20 30 3
> Is there a way to use a Cross Join to create the following output
> Customer Revenue Bucket
> Cust1 5 1
> Cust2 28 3
>
> --
Sure. It's not even a Cross Join, since each customer is matched with one
Bucket.
Select Customer, Revenue, Bucket
from Customer join Bucket
on Customer.Revenue >= Bucket.LowRange
and Customer.Revenue < Bucket.HighRange
David|||It's not a cross join. Use a LEFT OUTER, RIGHT OUTER or INNER join depending
on your exact requirements:
SELECT C.customer, C.revenue, R.bucket
FROM Customers AS C
LEFT JOIN RevenueRanges AS R
ON C.revenue >= R.lowrange AND C.revenue < R.highrange
I'm assuming that none of the ranges in RevenueRanges will overlap.
--
David Portas
--
Please reply only to the newsgroup
--

capacity planning

Hi all
how can we know how much IO/sec server is using..
second wot is allocated cache memory or how can we configure cache memory of sqlserver..or cache area.
i've to do capacity planning of sqlserver..can anyone help me out in this regard.
Regards"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:02D6A931-8D1F-4A59-B0FD-8E5C1D0C540A@.microsoft.com...
> Hi all
> how can we know how much IO/sec server is using..
perfmon will give you IO/Sec
> second wot is allocated cache memory or how can we configure cache memory
of sqlserver..or cache area.
Perfmon again has counters for the memory used by SQL server. If you mean
buffer cache then pretty much the only thing you can do configuration wise
is limit the size to a fixed range - the default is to keep consuming memory
until all RAM is utilized by SQL Server.
> i've to do capacity planning of sqlserver..can anyone help me out in this
regard.
> Regards
>
Niall Litchfield
Oracle DBA
Audit Commission UK