Dynamic query

Hello

I'm a newbie in sql server. anyone can explain what does Dynamic query means?

thanks.

Sure, but for some basic understandings (perhaps your questions will be cleared after the detailed the explanation of this text) look for:

http://www.sommarskog.se/dynamic_sql.html

HTH, jens Suessmeyer.

|||On Sat, 21 Jan 2006 11:30:01 -0800,

wrote:

>I'm a newbie in sql server. anyone can explain what does Dynamic query

>means?

Hi Dato0011,

Dynamic SQL means that the query is not precoded in a stored procedure

or application, but created on the spot. Example of non-dynamic SQL:

SELECT COUNT(*) FROM pubs..authors

Example of dynamic SQL:

DECLARE @.SQL nvarchar(400)

SET @.SQL = 'SELECT COUNT(*) FROM pubs..authors'

EXECUTE (@.SQL)

(In a real situation, part of the dynamic SQL would be based on user

input).

Dynamic SQL can be advantegeous in some situations, but it is also

extremely dangerous. Using dynamic SQL requires permissions for the end

users on your tables, and -the most important danger!- exposes your DB

to the danger of SQL injection.

For a completediscussion of the curse and blessing of dynamic SQL, see

Erland Sommarskog's article:

http://www.sommarskog.se/dynamic_sql.html

--

Hugo Kornelis, SQL Server MVP|||

Hi Dato0011,

It seems that some weird bug deleted the formatting of my reply. Let's do that one more time, shall we?

On Sat, 21 Jan 2006 11:30:01 -0800, <Dato0011@.discussions.microsoft.com>
wrote:

>I'm a newbie in sql server. anyone can explain what does Dynamic query
>means?

Hi Dato0011,

Dynamic SQL means that the query is not precoded in a stored procedure
or application, but created on the spot. Example of non-dynamic SQL:

SELECT COUNT(*) FROM pubs..authors

Example of dynamic SQL:

DECLARE @.SQL nvarchar(400)
SET @.SQL = 'SELECT COUNT(*) FROM pubs..authors'
EXECUTE (@.SQL)

(In a real situation, part of the dynamic SQL would be based on user
input).

Dynamic SQL can be advantegeous in some situations, but it is also
extremely dangerous. Using dynamic SQL requires permissions for the end
users on your tables, and -the most important danger!- exposes your DB
to the danger of SQL injection.

For a completediscussion of the curse and blessing of dynamic SQL, see
Erland Sommarskog's article:
http://www.sommarskog.se/dynamic_sql.html

--
Hugo Kornelis, SQL Server MVP

Dynamic Query

I'm a bit of a newbie to SRS / CRM3 and have worked through the Adventure
Works example http://msdn2.microsoft.com/en-us/library/ms171046(SQL.90).aspx
so I can get my head around creating reports.
I am already struggling with Dynamic Queries, my first attempt has failed.
= "SELECT New_LiveDate, New_sale_description, New_Amount FROM New_GIISale"
& Iif(Parameters!Amount.Value = 0.0, "", " WHERE New_Amount = " &
Parameters!Amount.Value)
I get an error
"Cannot set the command text for data set xxxxxxxxx"
"Error during processing of the CommandText expression of dataset xxxxxxx"
Can anyone tell me where I'm going wrong and point me in the direction of
any other tutorials on creating these queries.
Thanks in advance.
StuartFirst off, I only use dynamic queries in very very rare circumstances.
Unless you are doing something like dynamically determining the table to
query in most cases you do not need to do that.
Now, when you do need to do a dynamic query the thing to realize is that you
are creating a string with the query you want. The best way to see if you
are creating the string properly is to have a report with nothing on it but
a text box. Then set the textbox to an expression. The expression would be
what you have below (so your report would still have the report parameters).
Note that with a dynamic query you don't get a field list generated or the
report parameters created for you automatically.
Also, just glancing at it it looks to me the last line is the problem:
Parameters!Amount.Value)
should be
Parameters!Amount.Value & ")"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stuart" <Stuart@.discussions.microsoft.com> wrote in message
news:AAF6F385-0C34-461B-93EA-8EAC9A379885@.microsoft.com...
> I'm a bit of a newbie to SRS / CRM3 and have worked through the Adventure
> Works example
> http://msdn2.microsoft.com/en-us/library/ms171046(SQL.90).aspx
> so I can get my head around creating reports.
> I am already struggling with Dynamic Queries, my first attempt has failed.
> = "SELECT New_LiveDate, New_sale_description, New_Amount FROM
> New_GIISale"
> & Iif(Parameters!Amount.Value = 0.0, "", " WHERE New_Amount = " &
> Parameters!Amount.Value)
> I get an error
> "Cannot set the command text for data set xxxxxxxxx"
> "Error during processing of the CommandText expression of dataset xxxxxxx"
> Can anyone tell me where I'm going wrong and point me in the direction of
> any other tutorials on creating these queries.
> Thanks in advance.
> Stuart
>|||You should rewrite your query like this and forget about dynamic sql...
SELECT
New_LiveDate,
New_sale_description,
New_Amount
FROM
New_GIISale
WHERE
(@.Amount != 0.0 and
New_Amount = @.Amount) or
@.Amount = 0.0
As a best practice I also would suggest to always package your queries
in stored procedures on the database...
Hope this helps!
--
Ben Sullins
http://bensullins.com

Dynamic Measure?

Hi,
I am newbie in Analysis services. My problem is that I have to show a yearly report for credit expiry. In my fact table the measure is credit rate.
I have attributes of start_day_key and end_day_key. Now when I go through analysis services it does not let me create a measure so that I can count end_day_key for a year (end_day_key is linked to day table with daykey. It has date) and show. The thing is the measure I want is not stored (i.e: the count of end days for a year ). How do I go about it?

Should I create a calculated cell? (I tried that, but mdx does not work)
I used : Count({[Measures].[End Key]})< 366
Now this is not wha I want, I want the end_key count by matching date in day dimension for the current year? Any help is greatly appreciated.

Mdxyou can create a count measure. I found out by myself

Dynamic connection string problem in SSRS2005

Hi, I'm a newbie to SSRS, and was wondering if anybody can shed light on a problem I have. I have a report which every client uses, but each client's data is held in its own database. Rather than create many reports, is it possible to create 1 report, which all can use, passing in the different datasource? I was thinking of a hidden parameter, passed by URL. Or maybe using the report viewer control in VS2005. Can anybody please help?

Thanks

Dan

Yes, private data sources can have expression-based connection strings. Please check the following threads:

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

|||

Thats great, thanks. I have used a report viewer, pointing to a report on a server, with a dynamic connection string.

One more question though, I am connecting to a secure server, and need to provide a login, eg sql_user, pword. Where / how do I provide this in the code / url?

Thanks

Dan

|||How would you collect the credentials? If the user enters them before running the report you have no other way but passing them as parameters to the report (something you should avoid). If they are known values, you can save them in the Report Server web.config file as the ExpressionBasedConnection report demonstrates.|||

Oh, ok I'll try that tomorrow.

Thanks Teo.