DynamicValuesUnavailable

I am using the WS interface and returning reports for rendering will our UI.
I am successfully getting back the parameters. I would like to create a
drop down list box where appropriate for the parameters. So i iterate
through the ValidValues[]. This works fine for the exception of when those
values are driven from a secondary dataset in the report. In this case i
get the DynamicValuesUnavailable enum for the State and the ValidValues[] is
undefined value. I believe I have ruled out security in that both the main
report and the drop down are driven from the same shared data source. Any
Ideas?Never mind - need to set ForRendering to true, although the one liner on
msdn below is a little misleading.
ForRendering
A Boolean expression that indicates how the parameter values are to be used.
If set to a value of true, parameter properties that are returned are based
on the parameter data that was used during the execution of the report.
"jsl" <someone@.microsoft.com> wrote in message
news:%23I%23LaIQmFHA.3448@.TK2MSFTNGP12.phx.gbl...
>I am using the WS interface and returning reports for rendering will our
>UI. I am successfully getting back the parameters. I would like to create
>a drop down list box where appropriate for the parameters. So i iterate
>through the ValidValues[]. This works fine for the exception of when those
>values are driven from a secondary dataset in the report. In this case i
>get the DynamicValuesUnavailable enum for the State and the ValidValues[]
>is undefined value. I believe I have ruled out security in that both the
>main report and the drop down are driven from the same shared data source.
>Any Ideas?
>

Dynamically change the DataFlow Queries

Hi Guys,

This is Ravi. I'm working on SSIS 2005 version. I have created the DTSX file from the SQL Server and executed it successfully from my .NET 2005 code.

Now I have a requirement that I need to dynamically change the Source database query. ie., based on the user selection I need to get the data from different tables of SQL and put it into an Excel file.

Can anyone help me in this..

Regards,
Ravi K. Kalyan
Mascon Global Limited.

You can only change the query if the metadata of the data-flow is unchanged thereafter.

If this is the case then read this: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

It tells you how to dynamically alter your SQL queries.

-Jamie

|||

Thanks for the reply.

I tried to modify my code using the dataflow, but cud't do it exactly the way I wanted.

I have a requirement of exporting the data into excel file using the SSIS. For that I have used Application & Package class of DTS Namespace.

I can able to load and execute the package. Now according to the user selection I need to export the data from different tables. Can I pass the source query to the package object from my .NET 2005 code? If Yes, can u please give me some sample code or any reference links.

--Kalyan

Dynamic SQL Concatenation

In FIGURE 2, I'm successfully building a dynamic statement on Northwind.
However, in FIGURE 1, I'm trying to add a WHERE subquery that should return
FIGURE 2's results but only for the last 10 days. I'm getting a strange
error in QA. Can someone help me with my syntax in FIGURE 1 near the ERROR
comment?
-- FIGURE 1:
declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
int, @.timeID int, @.dtMaxDate datetime
SET @.Country = N'Germany'
SET @.ShipVia = '1'
set @.typeID = '1'
SET @.timeID = '1'
set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
SET @.SQL = 'SELECT Customers.CompanyName, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + '
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55))
IF @.timeID = 1 -- ERROR IS IN NEXT 2 LINES
SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ' +
CAST(@.dtMaxDate AS VARCHAR(55))
SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
SET @.SQL = @.SQL + 'GROUP BY Customers.CompanyName'
EXEC(@.SQL)
-- FIGURE 2:
declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID int
SET @.Country = N'Germany'
SET @.ShipVia = '1'
set @.typeID = '1'
SET @.SQL = 'SELECT Customers.CompanyName, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + '
FROM
Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE
Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55)) + '
GROUP BY
Customers.CompanyName'What's the error you're getting?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"scott" <sbailey@.mileslumber.com> wrote in message
news:OVCoeAt7FHA.204@.TK2MSFTNGP10.phx.gbl...
> In FIGURE 2, I'm successfully building a dynamic statement on Northwind.
> However, in FIGURE 1, I'm trying to add a WHERE subquery that should
> return FIGURE 2's results but only for the last 10 days. I'm getting a
> strange error in QA. Can someone help me with my syntax in FIGURE 1 near
> the ERROR comment?
> -- FIGURE 1:
> declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
> int, @.timeID int, @.dtMaxDate datetime
> SET @.Country = N'Germany'
> SET @.ShipVia = '1'
> set @.typeID = '1'
> SET @.timeID = '1'
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> SET @.SQL = 'SELECT Customers.CompanyName, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + '
> FROM Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55))
> IF @.timeID = 1 -- ERROR IS IN NEXT 2 LINES
> SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ' +
> CAST(@.dtMaxDate AS VARCHAR(55))
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Customers.CompanyName'
> EXEC(@.SQL)
> -- FIGURE 2:
> declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
> int
> SET @.Country = N'Germany'
> SET @.ShipVia = '1'
> set @.typeID = '1'
> SET @.SQL = 'SELECT Customers.CompanyName, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + '
> FROM
> Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE
> Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55)) + '
> GROUP BY
> Customers.CompanyName'
>|||scott (sbailey@.mileslumber.com) writes:
> In FIGURE 2, I'm successfully building a dynamic statement on Northwind.
> However, in FIGURE 1, I'm trying to add a WHERE subquery that should
> return
> FIGURE 2's results but only for the last 10 days. I'm getting a strange
> error in QA. Can someone help me with my syntax in FIGURE 1 near the ERROR
> comment?
Please, next time include the error message you get.
Here is what I think is suspect:

> CAST(@.dtMaxDate AS VARCHAR(55))
Use "convert(char(8), @.dtMaxDate, 112)" to get a date format that
is not subject to different interpretation.
Or even, better throw this code away entirely. I don't know what you
are trying to achieve, but if this is an exercise with dynamic SQL,
please to it the proper way and use sp_executesql instead. Then you
don't have to deal with cast and convert to interpolate variable values.
Please see sp_executesql in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '6'.
What's confusing is their is no '6' in my code.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uadSxFt7FHA.2176@.TK2MSFTNGP14.phx.gbl...
> What's the error you're getting?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:OVCoeAt7FHA.204@.TK2MSFTNGP10.phx.gbl...
>|||Below in FIGURE 1 is my code using the new date format. I'm getting the
below error below. Can you help me solve this? I hear what you're saying
about the other method, but want to get this one fixed due to time
constraints.
ERROR:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
-- FIGURE 1:
declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
int, @.timeID int, @.dtMaxDate datetime
SET @.Country = N'Germany'
SET @.ShipVia = '1'
set @.typeID = '1'
SET @.timeID = '1'
set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
SET @.SQL = 'SELECT Customers.CompanyName, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + '
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE
Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55))
IF @.timeID = 1
SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ' +
convert(char(8), @.dtMaxDate, 112)
SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
SET @.SQL = @.SQL + 'GROUP BY Customers.CompanyName'
EXEC(@.SQL)
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9715E9F9B8E8FYazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Please, next time include the error message you get.
> Here is what I think is suspect:
>
> Use "convert(char(8), @.dtMaxDate, 112)" to get a date format that
> is not subject to different interpretation.
> Or even, better throw this code away entirely. I don't know what you
> are trying to achieve, but if this is an exercise with dynamic SQL,
> please to it the proper way and use sp_executesql instead. Then you
> don't have to deal with cast and convert to interpolate variable values.
> Please see sp_executesql in Books Online.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||CONVERT (varchar(12), @.dtMaxDate, 101) fixed it. thanks for your input.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9715E9F9B8E8FYazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Please, next time include the error message you get.
> Here is what I think is suspect:
>
> Use "convert(char(8), @.dtMaxDate, 112)" to get a date format that
> is not subject to different interpretation.
> Or even, better throw this code away entirely. I don't know what you
> are trying to achieve, but if this is an exercise with dynamic SQL,
> please to it the proper way and use sp_executesql instead. Then you
> don't have to deal with cast and convert to interpolate variable values.
> Please see sp_executesql in Books Online.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Scott (sbailey@.mileslumber.com) writes:
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near '6'.
> What's confusing is their is no '6' in my code.
It's imperative when you work with dynamic SQL to include things like:
IF @.debug = 1 PRINT @.sql
so that you can see what you actually have produced.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns971668A0F963EYazorman@.127.0.0.1...
> It's imperative when you work with dynamic SQL to include things like:
> IF @.debug = 1 PRINT @.sql
How about, instead, using a macro and putting it in debug mode? :)
http://www.datamanipulation.net/tsqlmacro
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

> so that you can see what you actually have produced.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 21 Nov 2005 18:19:56 -0600, Scott wrote:

>CONVERT (varchar(12), @.dtMaxDate, 101) fixed it. thanks for your input.
Hi Scott,
I severely doubt it. You're not getting errors anymore, but your results
are incorrect.
If you had followed Erlands original advice to print the SQL string you
are executing, you'd have seen that your original query created code
like this:
WHERE Orders.OrderDate <= 20051123
Since 20051123 is a numeric and OrderDate is a datetime, SQL Server
attempts to convertthe numeric 20051123 to datetime. Conversion rule
says that the number is the number of days since jan 1st 1900. If I
didn't miscalculate, adding 20,051,123 to the start of 1900 takes us
some 55,000 years in the future - well beyond the maximum capacity of
the datetime datatype. That's whhy you got the error.
Your "fix" now generates code like this:
WHERE Orders.OrderDate <= 11/23/2005
This will first divide 11 by 23 (using integer division, yielding 0),
then divide that by 2005 (result still 0). This will then be converted
to datetime ==> 0 days after jan 1st 1900 is jan 1st 1900. Regardless of
the value of @.dtMaxDate, you'll be comparing to 1900-01-01.
The correct way to pass a date literal is to use the yyyymmdd format,
and to enclose it in single quotes:
WHERE Orders.OrderDate <= '20051123'
Or to fix this particular line of your code:
SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ''' +
convert(char(8), @.dtMaxDate, 112) + ''''
(Note that a single quotes within single quotes must be doubled!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks, that fixed it.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:4vd7o19vos75ef7eumvphndgcafspqkjv7@.
4ax.com...
> On Mon, 21 Nov 2005 18:19:56 -0600, Scott wrote:
>
> Hi Scott,
> I severely doubt it. You're not getting errors anymore, but your results
> are incorrect.
> If you had followed Erlands original advice to print the SQL string you
> are executing, you'd have seen that your original query created code
> like this:
> WHERE Orders.OrderDate <= 20051123
> Since 20051123 is a numeric and OrderDate is a datetime, SQL Server
> attempts to convertthe numeric 20051123 to datetime. Conversion rule
> says that the number is the number of days since jan 1st 1900. If I
> didn't miscalculate, adding 20,051,123 to the start of 1900 takes us
> some 55,000 years in the future - well beyond the maximum capacity of
> the datetime datatype. That's whhy you got the error.
>
> Your "fix" now generates code like this:
> WHERE Orders.OrderDate <= 11/23/2005
> This will first divide 11 by 23 (using integer division, yielding 0),
> then divide that by 2005 (result still 0). This will then be converted
> to datetime ==> 0 days after jan 1st 1900 is jan 1st 1900. Regardless of
> the value of @.dtMaxDate, you'll be comparing to 1900-01-01.
>
> The correct way to pass a date literal is to use the yyyymmdd format,
> and to enclose it in single quotes:
> WHERE Orders.OrderDate <= '20051123'
> Or to fix this particular line of your code:
> SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' +
> convert(char(8), @.dtMaxDate, 112) + ''''
> (Note that a single quotes within single quotes must be doubled!)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)