Dynamic Queries Errors in Report Manager

I am trying to replicate the use of Dynamic Queries in SQL Server 2000
Reporting Services documented in the "Hitchhiker's Guide to SS 2000 RS."
I have done everything the book has told me to do. I can build and run the
project in test mode, and deploy to my test server
(http:\\localhost\ReportServer) without error.
I have already set up the datasouce for the application in the Report Server
when I try to run the deployed project. Every time I run the project I get
the following:
1) An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
2) Query execution failed for data set 'dsCustomers'.
(rsErrorExecutingCommand) Get Online Help
3) Line 1: Incorrect syntax near '='.
I think it's pointing at my Data set in my data tab, but am not sure why it
will point there when it worked in test mode!
Does anyone know how to fix this problem?Hello,
I am guessing that by dynamic queries you mean queries with parameters.
I am using them with a teradata db and to get the parameters to work in
the query I have to precede the query with =" and end it with ". To
insert the parameters you put "+Parameter!Name.Value+".
I hope this helps,
Lilja|||Lilja, first I want to thank you for lending a hand. I appreciate it very
much.
Second, I want to restate you comments in my own words. For a dynamic query
with Parameters, I will start my statement with an equals sign (=).
To the left of the equal sign there will be nothing. To the right of the
equals I need to put the equation (which calls an assembly outside of my
project) in between double quotes: ="proj.class.method()".
Whenever I want to add a parameter to the equation I drop out of the string,
and add the literal with a + before and after the parameter:
="proj.class.method("+Parameter!Name.Value+")"
When I run the project in local debug mode, it works fine. When I change
the settings to Debug, give it a path "http://localhost/ReportServer" and
deploy it, I still get the same error. Is there something else that could be
the problem?
My equation runs over into a second line. Does it need the VB underscore to
carry the line over? ' _ '
Thank you very much again.
KurT
"kisa" wrote:
> Hello,
> I am guessing that by dynamic queries you mean queries with parameters.
> I am using them with a teradata db and to get the parameters to work in
> the query I have to precede the query with =" and end it with ". To
> insert the parameters you put "+Parameter!Name.Value+".
> I hope this helps,
> Lilja
>

Dynamic Ports value changes

Hi,

Using SQL Configuration Manager, i have set my local instance to use TCP Dynamic Ports by setting the value under IPAll to be 0 (the value TCP Port is blank). However, when i start up the server this value gets set to a specific port. ie Before startup TCP Dynamic Ports = 0, After startup TCP Dynamic Ports = 2832. This value persists throughout SQL Server restarts.

Is this behaviour correct as I would have expected this value to stay 0?

I am using SQL Standard, SP2. SQL Browser is running.

Thanks in advance!

No the value will not stay to 0. The value displayed while SQL is running is the current port that SQL is listening on.

When setting SQL Server for a dynamic port, SQL Server selects an available port at random when starting up. That TCP port is used for the duration of the time that SQL Server is running. Upon shutdown and restart of SQL Server another port is selected.

|||Thanks very much for your response.

I'd just like to clarify that although the port selected will replace the 0 in the configuration GUI, the next time it starts up, it would still be aware that it is in dynamic ports "mode". I just want to ensure the following scenario would not happen:-

Dynamic Ports = 0
SQL Server starts up and dynamically assings port 2234
Dynamic Ports = 2234
Shut down SQL Server
New application starts and listens on port 2234
SQL Server tries to start up on port 2234 and fails.
Manually have to reset dynamic ports = 0

If this isn't the case thats ok though i find it a little confusing that the value doesn't stay at 0 in the GUI as you don't really have any indication that its set to use Dynamic Ports do you?
|||

Correct, it is still in dynamic mode.

The only way to know that it's using dynamic ports is that the number is in the "TCP Dynamic Ports" section not in the "TCP Port" section.

Dynamic Date Parameter in Report Manager

Hi all,

I was wondering if there's a way to override the default date parameter of a report to a dynamic value such as Today(), Now(), DateAdd(),... through Report Manager.

I know this can be done in Report Designer, but never been successful when I try to change the date parameter value in Report Manager.

I've tried to change the date parameter value by clicking on the Override Default button in the report's properties page from Report Manager, error like the one below will be shown after I entered Now() in the Default Value field and clicked Apply:
The value provided for the report parameter 'StartDate' is not valid for its type. (rsReportParameterTypeMismatch)

Thanks in advance.

You can set defaults by editing the parameter properties from the Layout tab. Just right click anywhere on the "blank" non used area of your report. Click the "Report Parameters" option. Select the parameter you want to default, and use the Non-Queried option. I used the following code to set Start and End date defaults for the current month. Hope that this helps.

For the Start Date parameter I have this in the box:

=DateAdd("d",-Day(Today())+1,Today())

End Date:

=DateAdd("h",23,DateAdd("d",-1,DateAdd("m",1,DateAdd("d",-Day(Today())+1,Today()))) )

For October of 2006, this should set the Start and End date parameters to 10/01/2006 12:00:00 AM and 10/31/2006 11:00:00 PM

Dynamic Date Parameter in Report Manager

Hi all,

I was wondering if there's a way to override the default date parameter of a report to a dynamic value such as Today(), Now(), DateAdd(),... through Report Manager.

I know this can be done in Report Designer, but never been successful when I try to change the date parameter value in Report Manager.

I've tried to change the date parameter value by clicking on the Override Default button in the report's properties page from Report Manager, error like the one below will be shown after I entered Now() in the Default Value field and clicked Apply:
The value provided for the report parameter 'StartDate' is not valid for its type. (rsReportParameterTypeMismatch)

Thanks in advance.

You can set defaults by editing the parameter properties from the Layout tab. Just right click anywhere on the "blank" non used area of your report. Click the "Report Parameters" option. Select the parameter you want to default, and use the Non-Queried option. I used the following code to set Start and End date defaults for the current month. Hope that this helps.

For the Start Date parameter I have this in the box:

=DateAdd("d",-Day(Today())+1,Today())

End Date:

=DateAdd("h",23,DateAdd("d",-1,DateAdd("m",1,DateAdd("d",-Day(Today())+1,Today()))) )

For October of 2006, this should set the Start and End date parameters to 10/01/2006 12:00:00 AM and 10/31/2006 11:00:00 PM

Dynamic Date Parameter in Report Manager

Hi all,

I was wondering if there's a way to override the default date parameter of a report to a dynamic value such as Today(), Now(), DateAdd(),... through Report Manager.

I know this can be done in Report Designer, but never been successful when I try to change the date parameter value in Report Manager.

I've tried to change the date parameter value by clicking on the Override Default button in the report's properties page from Report Manager, error like the one below will be shown after I entered Now() in the Default Value field and clicked Apply:
The value provided for the report parameter 'StartDate' is not valid for its type. (rsReportParameterTypeMismatch)

Thanks in advance.

You can set defaults by editing the parameter properties from the Layout tab. Just right click anywhere on the "blank" non used area of your report. Click the "Report Parameters" option. Select the parameter you want to default, and use the Non-Queried option. I used the following code to set Start and End date defaults for the current month. Hope that this helps.

For the Start Date parameter I have this in the box:

=DateAdd("d",-Day(Today())+1,Today())

End Date:

=DateAdd("h",23,DateAdd("d",-1,DateAdd("m",1,DateAdd("d",-Day(Today())+1,Today()))) )

For October of 2006, this should set the Start and End date parameters to 10/01/2006 12:00:00 AM and 10/31/2006 11:00:00 PM

|||

Does anyone have a solution to the original question? Because if you override the default and put in an expression, you get an error like stated in the first post. It works before you override the default, but if you override the default and then enter in the modified expression, I get 'Syntax error converting datetime from character string.'

Thanks,

Iris

Dynamic connection manager file name

Hi There

This should be an easy one i hope.

I need to dynamically change the file name of my destination flat file connection everytime the package runs, obviously i do not want to edit the config file everytime.

I think the best way to do this is by a script taks that sets the connection manager filename property with a variable that i dynamically populate.

Is this the right way?

I just want to be sure that there is not an easier or better way to do this?

Thanx

Yes, that sounds like a good approach. Don't use a script task though (because its not possible to do it with a script task), use a property expression (http://www.google.co.uk/search?hl=en&q=ssis+expressions&meta=).

You can parameterise the package execution by passing values in from the command-line using the /SET option.

-Jamie

|||Thats is exactly what i was looking for, thanx a million Jamie|||Sorry to be daft, but how to do you get to the setting, when I click on the flat file source, I pull up the connection manager, which doesn't have an expressions section like the others. Could someone give me a general walk through as to how I can set the expressions property for the flat file source file name?

Thank you
|||If you select the connection for the flat file in the connection managers section of the package, the properties window will display for the properties for that connection. One of the properties is Expressions. You can probably figure it out from there.|||Right...Thanks a lot

Dynamic connection manager file name

Hi There

This should be an easy one i hope.

I need to dynamically change the file name of my destination flat file connection everytime the package runs, obviously i do not want to edit the config file everytime.

I think the best way to do this is by a script taks that sets the connection manager filename property with a variable that i dynamically populate.

Is this the right way?

I just want to be sure that there is not an easier or better way to do this?

Thanx

Yes, that sounds like a good approach. Don't use a script task though (because its not possible to do it with a script task), use a property expression (http://www.google.co.uk/search?hl=en&q=ssis+expressions&meta=).

You can parameterise the package execution by passing values in from the command-line using the /SET option.

-Jamie

|||Thats is exactly what i was looking for, thanx a million Jamie|||Sorry to be daft, but how to do you get to the setting, when I click on the flat file source, I pull up the connection manager, which doesn't have an expressions section like the others. Could someone give me a general walk through as to how I can set the expressions property for the flat file source file name?

Thank you|||If you select the connection for the flat file in the connection managers section of the package, the properties window will display for the properties for that connection. One of the properties is Expressions. You can probably figure it out from there.|||Right...Thanks a lot

Dynamic changes of AS Connection Manager failed

Hi,

in my actual szenario I have to change the Server (Connection String) of Analysis Services Connection Manager from my testserver SERVER1 to my production server SERVER2. Nearly everything is easy...I set a variable to the correct servername and I used a expression to change the connection string of the connection manager during run-time. I used this technic serveral time with ole db and it works fine.

Debugging of the project shows that the variable is set correctly. The delay validation flag is set to true. When I run the package the connection establish a connection to the server which I use due designtime.

Does anybody have an idea how I can fix it?

Regards
Klaus Hoeltgen

Hi,

I am having the same issue as Klaus - is there no suggestion to solve that?

Or is it "just by design"?

cheers,
Markus