Parameter Queries in Analyser

Real new person question here but during my query practise I created some
parameter queries in the enterprise manager however theses will not work when
I open query analyser. Can anyone tell where I’ve boobed?
Many thanks
Sam
What did you create?
Stored procedures?
Which database - are you in the correct database?
Easiest to create them using query analyser using create proc then you can
save the script.
"Sam" wrote:

> Real new person question here but during my query practise I created some
> parameter queries in the enterprise manager however theses will not work when
> I open query analyser. Can anyone tell where I’ve boobed?
> Many thanks
> Sam
>

Parameter Queries

If I understand what I am doing ...
I can run an unnamed parameter query in Query Analyzer
SELECT au_lname, au_fname
FROM dbo.authors
WHERE (state = ?)
However, that is about all I can do with it. I have not been able to do
anything with named parameter queries.
I guess the Books Online is a little off on these?
Is it possible to create some form of parameter query that accomplishes the
same thing as they do in Access?
Thanks.
Mike.You need a stored proc
--first create the procedure
Create Procedure prAuthorStates @.State char(2)
as
set nocount on
SELECT au_lname, au_fname,state
FROM dbo.authors
WHERE (state = @.State)
set nocount off
GO
--then execute the proc
exec prAuthorStates 'CA'
The code that you potsde can be used from ASP/PHP/JSP etc by using
prepared statements but not from Query Analyzer
Denis the SQL Menace
http://sqlservercode.blogspot.com/
MikeV06 wrote:
> If I understand what I am doing ...
> I can run an unnamed parameter query in Query Analyzer
> SELECT au_lname, au_fname
> FROM dbo.authors
> WHERE (state = ?)
> However, that is about all I can do with it. I have not been able to do
> anything with named parameter queries.
> I guess the Books Online is a little off on these?
> Is it possible to create some form of parameter query that accomplishes th
e
> same thing as they do in Access?
> Thanks.
> Mike.|||Adding to the post by "Menace" -- Don't expect SQL Server to prompt you to
enter the parameter value (in a dialog box like MS Access does). That
functionality is not built in to SQL Server. You would need to build your
own GUI for that...
Keith Kratochvil
"MikeV06" <me@.privacy.net> wrote in message
news:189zasiw6e1fr$.dlg@.mycomputer06.invalid.com...
> If I understand what I am doing ...
> I can run an unnamed parameter query in Query Analyzer
> SELECT au_lname, au_fname
> FROM dbo.authors
> WHERE (state = ?)
> However, that is about all I can do with it. I have not been able to do
> anything with named parameter queries.
> I guess the Books Online is a little off on these?
> Is it possible to create some form of parameter query that accomplishes
> the
> same thing as they do in Access?
> Thanks.
> Mike.|||On Wed, 31 May 2006 14:04:08 -0500, Keith Kratochvil wrote:

> Adding to the post by "Menace" -- Don't expect SQL Server to prompt you to
> enter the parameter value (in a dialog box like MS Access does). That
> functionality is not built in to SQL Server. You would need to build your
> own GUI for that...
Thanks to you both. I thought as much. It is a shame though. The Query
Designer will pop up a dialog box for filling in the values. Works with the
? unnamed one OK; has a problem with the named one.
I guess I will have to find another way.
Thanks again.

Parameter Queries

Is there a way in Transact-SQL to do a "runtime" parameter query within a MS
Access adp frontend? This is easily done in a pure MS Access mdb using the
square brackets, e.g. [ENTER Customer Number].Hi
SQL Server will not allow this, but you may want to ask in an access
newsgroup to find the best solution.
John
"Peter Marshall at OCC" wrote:

> Is there a way in Transact-SQL to do a "runtime" parameter query within a
MS
> Access adp frontend? This is easily done in a pure MS Access mdb using th
e
> square brackets, e.g. [ENTER Customer Number].
>
>|||If you call a SQL Server stored proc that takes parameters without passing a
ny MSAccess will contrive to prompt you for values. The
prompt will be the name of the parameter, with the window title of : "Enter
Parameter Value".
Good enough?
If you want to replace the prompt, you'll have to code
"Peter Marshall at OCC" <peter.marshall@.ohiocoatingscompany.com> wrote in message news:ubCn
sarvFHA.596@.TK2MSFTNGP12.phx.gbl...
> Is there a way in Transact-SQL to do a "runtime" parameter query within a
MS
> Access adp frontend? This is easily done in a pure MS Access mdb using th
e
> square brackets, e.g. [ENTER Customer Number].
>

parameter problem with report

I am trying to build a report based on the following query .
I want the user to enter the date value for the R.ANNLAPPT_DATE but when I try to run the query I get the following msg
ORA-00904: invalid column name

The data source for this report is an oracle db

SELECT C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME AS CLINICIAN, R.DEPT_CODE,
D.DEPT_TITLE, R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODE, MAX(A.APPT_DATE) AS EXPR1,
R.ANNLAPPT_DATE AS EXPR2
FROM ORACARE.K_REFLREG R, ORACARE.K_CPIREG C, ORACARE.K_DEPTLIST D, ORACARE.K_PROFREG P, ORACARE.K_REFPLIST RP,
ORACARE.K_APPTREG A
WHERE R.PT_CODE = C.PT_CODE AND R.DEPT_CODE = D.DEPT_CODE AND R.HOSP_CODE = D.HOSP_CODE AND R.CONS_MD_CODE = P.MPROF_CODE AND
R.REFP_CODE = RP.REFP_CODE AND R.EVENT_NO = A.EVENT_NO (+) AND

(R.ANNLAPPT_DATE < "@.ANNALAPPT_DATE")

GROUP BY C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME, R.DEPT_CODE, D.DEPT_TITLE,
R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODEI don't think you want to enclose the parameter name in quotes, and if you are using Oracle, you may need to use a ? in place of @.ANNALAPPT_DATE. I think that depends on which driver you are using, though.|||Thanks for the reply,

sql server automaically inserted the double quotes I will try ?.
|||Thanks for the help the ? did the trick.|||

Can you mark the helpful response as an answer?

Parameter problem

hi,
i m using following query in my dataset query string

SELECT OrderNumber, OrderDate, Name AS Client, orderstatus
FROM Orders
Where @.Param

i m sending boolean exp as value for @.param as string like " YEAR(ORderDate) = YEAR(getDate()) "

but is showing following error

An expression of non-boolean type specified in a context where a condition is expected,near '@.param'. (Microsoft Sql Server, Error:4145)

please help me,

thanks
You cannot do that unless you use dynamic Sql in a procedure, passing the string as the parameter which should be avoided.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||thanks for reply

but can u explain further because i m new to it
|||

You dont need to send the parameter string because whatever you trying to do can be achieved in plain SQL in dataset query itself:

SELECT OrderNumber, OrderDate, Name AS Client, orderstatus
FROM Orders
Where YEAR(OrderDate) = YEAR(GETDATE())

Shyam

|||Wee that depends. if you have the same query everything with carying parameters you can use something like Where YEAR(@.SomeVar) = YEAR(Getdate()), the parameter will automatically mapped to the report if you write such a query in the pane of the query editor in the report designer. but if you want to pass in a whole condition rather than just a parameter you would probably need to pass it to a stored procedure to make it execute in a dynamic SQL statement.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Parameter passing to a DTS package

Hi,
I have a DTS package that exports a query result from SQLServer to a text
file . I sheculed it daily.
What I want to do is give the text file name as paramaetric, I mean,
e.g. salesresultddmmhhss.txt ddmmhhss : run time day month hour and second
Is it possible?Hi
The DTSRun utility has a /A parameter that allows you to set the values of a
global variable. This can then be used to set the filename. If the name is
consistent you should be able to do this in code without passing a value.
You may also want to check out http://www.sqldts.com/default.aspx?292 for
renaming a file and http://www.sqldts.com/default.aspx?234
John
"Banu_tr" wrote:

> Hi,
> I have a DTS package that exports a query result from SQLServer to a text
> file . I sheculed it daily.
> What I want to do is give the text file name as paramaetric, I mean,
> e.g. salesresultddmmhhss.txt ddmmhhss : run time day month hour and secon
d
> Is it possible?

Parameter Optimization

Hi guys,
I have a rather complex query which accepts 2 date parameters. When the
dates are hard-coded the query runs in 2 seconds; however when a parameter
is passed with the same values the query runs in excess of 2 minutes.
I got around this by building the entire query into a varchar variable, and
then using the exec(strexpression).
Is there a problem in the way the SQL server optimizer handles parameterized
queries? And what are the alternate ways to get around this?
Thanks,
JustinMaybe post some ddl on this specifying what the datatypes are.
"Justin" wrote:

> Hi guys,
>
> I have a rather complex query which accepts 2 date parameters. When the
> dates are hard-coded the query runs in 2 seconds; however when a parameter
> is passed with the same values the query runs in excess of 2 minutes.
>
> I got around this by building the entire query into a varchar variable, an
d
> then using the exec(strexpression).
>
> Is there a problem in the way the SQL server optimizer handles parameteriz
ed
> queries? And what are the alternate ways to get around this?
>
> Thanks,
> Justin
>
>|||I noticed that if you have many records in tables, conversion from varchar
to nvarchar or back can cause behavior you talk about. maybe when you
hardcode dates u use different type than when you pass them as params?
peter

Parameter not refreshing default data selection

Here is the senerio:

Parameter 1: Company - multi-value

Parameter 2: Employee - multi-value

Available values: Query - Dataset: EmpList

Default values: Query - Dataset: EmpList

Parameter 2 is dependant on Parameter 1.

When a Company is selected, it does update the list of employees in parameter 2. But what it isn't doing is updating my default values.

I select Company A. It populates the Employee list with all of Company A's employees and marks them all as selected (check in the checkbox) and the "Select All" checkbox is also checked.

I then decide I want to include Company B. It populates the Employee list with all of Company B's employees but it does NOT select the employee's from Company B. It is like it ignored the default values query.

I would also think that if "Select All" is checked all new values coming in would be checked.

Is there a way around this problem?

I need to know if this is a bug or if there is something I can do to fix this.

Parameter Mapping in an Execute SQL Task

I am trying to assign the same package variable value to three different parameters in a query. The variable contains the name of a database which the user will input during package execution. First I check to see if the database exists (if it does I drop it), then in either case I create the database. See code:

if exists

(

select name

from sys.databases

where name = ?

)

begin

drop database ?;

end;

go

create database ?;

go

This is the error I am getting:

[Execute SQL Task] Error: Executing the query "if exists ( select name from sys.databases where name = ? ) begin drop database ?; end; " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

My "User::DestinationDatabase" variable is mapped to 0,1,2 using an OLE DB connection. Any suggestions would be welcome.

Regards,

DO

Try creating three variables with the same value and map them to the parameters.|||go is not a valid TSQL command. You need to seperate into seperate ExecuteSQL tasks. If you then have 2 executeSQL tasks you should then find it works. You probably want to bypass prepare as I have found that the easiest way of getting the parameter mapping to work.|||

Kaarthik Sivashanmugam wrote:

Try creating three variables with the same value and map them to the parameters.

And if you do do that, the 2 new variables that you create only have to be made equal to the first variable by using expressions.

-Jamie

|||I tried your idea. Unfortuantely, it did not work. Anymore suggestions?|||I removed the "GO" command and tried separate tasks (one to drop the other to create). It did not work. I then set ByPass Prepare to true for both. Again no go. Please let me know if you can think of anything else.|||I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.|||

DatabaseOgre wrote:

I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.

They both achieve the same thing. It is of course your choice which you choose. I would always choose expressions because:

1) Less coding to do

2) Less work for the package to do (i.e. less tasks in your control flow)

3) Expressions use out-of-the-box functionality. Scripts are more of a workaround.

4) The expression is evaluated when the variable is called. If you go the script task route you have to make sure that the value is explicitly changed prior to calling it. Hence I think expressions are more intuitive and easier to be understood by a person who has to understand your package later.

-Jamie

|||

A couple of points. DROP DATABASE can't take a variable the database name.

Secondly I don't think that you can use paramaters in the execute sql task with a sql statement only an SP.

For this reason if you want to execute a sql statement you need to build it up in an expression and then execute that

|||Thanks. I did not know exactly how expressions worked, so the information is much appreciated. Since I am already using the Script Task to accomplish multiple tasks, I will probably stick to using it for now.|||

Thanks. I did not know that you can not use a parameter for a DDL statement. I tried to create a DB, table, view....nothing worked. I guess my options are to use dynamic SQL within the Execute SQL Task, pass the query in a variable, or a Script Task that makes a connection, checks for the db, etc. With regard to your second point, you can use parameters in a SQL query within an Execute SQL Task. However, it only seems to work with very simple queries i.e. select * from table where column = ?. Anything more creative than that seems to throw it for a loop. I am probably going to try the SQL query in a variable method. Let me know if you think of anything else. Thanks again for your post.

=== Edited by DatabaseOgre @. 10 Mar 2006 10:54 PM UTC===
I ended up using the SQL query in a variable method and it worked. Kudos to Jamie, I used one of his other posts to figure it out.

|||Yeh I think it has to be a single statement.|||If your complex query with parameters fails, then try setting "ByPassPrepare=true".sql

Parameter Mapping in an Execute SQL Task

I am trying to assign the same package variable value to three different parameters in a query. The variable contains the name of a database which the user will input during package execution. First I check to see if the database exists (if it does I drop it), then in either case I create the database. See code:

if exists

(

select name

from sys.databases

where name = ?

)

begin

drop database ?;

end;

go

create database ?;

go

This is the error I am getting:

[Execute SQL Task] Error: Executing the query "if exists ( select name from sys.databases where name = ? ) begin drop database ?; end; " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

My "User::DestinationDatabase" variable is mapped to 0,1,2 using an OLE DB connection. Any suggestions would be welcome.

Regards,

DO

Try creating three variables with the same value and map them to the parameters.|||go is not a valid TSQL command. You need to seperate into seperate ExecuteSQL tasks. If you then have 2 executeSQL tasks you should then find it works. You probably want to bypass prepare as I have found that the easiest way of getting the parameter mapping to work.|||

Kaarthik Sivashanmugam wrote:

Try creating three variables with the same value and map them to the parameters.

And if you do do that, the 2 new variables that you create only have to be made equal to the first variable by using expressions.

-Jamie

|||I tried your idea. Unfortuantely, it did not work. Anymore suggestions?|||I removed the "GO" command and tried separate tasks (one to drop the other to create). It did not work. I then set ByPass Prepare to true for both. Again no go. Please let me know if you can think of anything else.|||I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.|||

DatabaseOgre wrote:

I assign the variable in a Script Task that captures the data from an InputBox. I added code to make param2 = param1 and param3 = param2 after param1 has been initialized. Do expressions help me gain something or are they basically two ways to do the same thing? Just wondering if one is better than the other.

They both achieve the same thing. It is of course your choice which you choose. I would always choose expressions because:

1) Less coding to do

2) Less work for the package to do (i.e. less tasks in your control flow)

3) Expressions use out-of-the-box functionality. Scripts are more of a workaround.

4) The expression is evaluated when the variable is called. If you go the script task route you have to make sure that the value is explicitly changed prior to calling it. Hence I think expressions are more intuitive and easier to be understood by a person who has to understand your package later.

-Jamie

|||

A couple of points. DROP DATABASE can't take a variable the database name.

Secondly I don't think that you can use paramaters in the execute sql task with a sql statement only an SP.

For this reason if you want to execute a sql statement you need to build it up in an expression and then execute that

|||Thanks. I did not know exactly how expressions worked, so the information is much appreciated. Since I am already using the Script Task to accomplish multiple tasks, I will probably stick to using it for now.|||

Thanks. I did not know that you can not use a parameter for a DDL statement. I tried to create a DB, table, view....nothing worked. I guess my options are to use dynamic SQL within the Execute SQL Task, pass the query in a variable, or a Script Task that makes a connection, checks for the db, etc. With regard to your second point, you can use parameters in a SQL query within an Execute SQL Task. However, it only seems to work with very simple queries i.e. select * from table where column = ?. Anything more creative than that seems to throw it for a loop. I am probably going to try the SQL query in a variable method. Let me know if you think of anything else. Thanks again for your post.

=== Edited by DatabaseOgre @. 10 Mar 2006 10:54 PM UTC===
I ended up using the SQL query in a variable method and it worked. Kudos to Jamie, I used one of his other posts to figure it out.

|||Yeh I think it has to be a single statement.|||If your complex query with parameters fails, then try setting "ByPassPrepare=true".

Parameter Mapping format string

Hi,

I have used Data Flow component that refers to named query of data source view. It is a OLE DB source.

The SQL Command property of data flow component shows SELECT * FROM Tablename as I defiend in named query. I have modified the query to accept a parameter as SELECT * FROM Tablename WHERE Status = ?

Now I need to pass the package variable to this parameter. How to I pass using ParameterMapping property?. What is correct way of passing the parameter mapping ? I tried with @.[TestNS:tatus] variable. But it throws error The parameter mapping string is not in the correct format.

Thanks in advance

Hello Prabha!

The best way to set parameter mappings on your OLE DB source is by using the "Parameters" button in the edit dialog for the source component. Once you've clicked that, a pop-up will appear with a grid where you can select variables you want to map to parameters of your SQL statement. If you have problems with this approach, feel free to post a follow up and one of us will be sure to help you from there.

Thanks, -David

|||

Hi David,

I agree that the suggested approch will help in configuing parameterized SQL Command. But my scenario is to use named query as source.

I do not get the 'Parameters' button as I have selected named query option in OLEDB source. However, I am allowed to change the SQL command property to have the parameter place holder '?' under Properties window after configuring the OLEDB connection Manager.

There is also one more property called 'ParameterMapping' under custom properties of OLEDB source. I do not find any reference on the format of the parameter mapping. Can you forward any reference on this ?

Thanks,

Prabha

|||

Sorry, Prabha, I sort of missed that you're using a DSV named query.

Officially, Named Queries don't support parameters, but as you found, there are ways to manually change the SQL in your DSV, such as by changing the xml directly. I wouldn't recommend that because other things using these DSVs are likely to break.

That having been said, I was able to convince the OLE DB source to bind in variables to the parameters I added manually to the Named Query's SQL, by setting the ParameterMapping manually. The format is:

"<param-name>","<variable-guid>";...

But, instead of constructing it manually, which seems kind of error prone, try this:

1) In the OLE DB Source editor, copy the SQL for your named query to the clipboard.

2) Switch to "SQL Command" mode for your OLE DB Source.

3) Paste the query from the clipboard to the SQL editor.

4) Click "Parameters...".

5) For each parameter, choose a variable.

6) Close the parameters screen, close the OLE DB source editor.

7) Find the "ParameterMapping" field in the Properties editor, copy that to the clipboard.

8) Re-open the OLE DB source editor, switch back to the Named Query.

9) Close the OLE DB source editor

10) Paste the ParameterMapping value from the clipboard back into the properties window

A lot of steps, but I think it's better than trying to hand-assemble the thing. Fair warning though, this isn't supported, so it could stop working at any time. From what I know, DSVs don't officially support parameters, so the safe bet would be to switch to SQL Command mode for this.

-David

|||

Thank you so much David, The given work around works well.

- Prabha

Parameter isn't refreshing

Hi.
I have a datasource that depend on parameter A.
Parameter A get his values from query and have a defualt value.
Parameter B get his default value from query that depend on parameter
A.
Now, whan I run the report, parameter A get a value and then parameter
B get his value and the
datasource run OK.
But the problem is that when I'm changing the A value (from the value
list) - The datasource run fine but Parameter B stay with the old value
with out any change.
Does any one know how to solve it.
I'll be happy to give more explanation if it isn't clear enough.
Thanks.Problem solved.
I set the parameter to "Internal".
thanks.
nicknack =D7=9B=D7=AA=D7=91:
> Hi.
> I have a datasource that depend on parameter A.
> Parameter A get his values from query and have a defualt value.
> Parameter B get his default value from query that depend on parameter
> A.
> Now, whan I run the report, parameter A get a value and then parameter
> B get his value and the
> datasource run OK.
> But the problem is that when I'm changing the A value (from the value
> list) - The datasource run fine but Parameter B stay with the old value
> with out any change.
> Does any one know how to solve it.
> I'll be happy to give more explanation if it isn't clear enough.
> Thanks.

Parameter isn't refreshing

Hi.
I have a datasource that depend on parameter A.
Parameter A get his values from query and have a defualt value.
Parameter B get his default value from query that depend on parameter A.

Now, whan I run the report, parameter A get a value and then parameter B get his value and the
datasource run OK.

But the problem is that when I'm changing the A value (from the value list) - The datasource run fine but Parameter B stay with the old value with out any change.

Does any one know how to solve it.
I'll be happy to give more explanation if it isn't clear enough.

Thanks.

I think we need more clarification.

How do the parameters get passed to the report? I don't understand why you seem to think that Parameter B should be updated when you change Parameter A.

Parameter B may seem to depend on Parameter A logically speaking, but as far as I know you can't set up parameters to be dependent on one another.

|||

Actually,

Parameter can be dependent on another, they are considered cascading parameters.

On your problem Roy, I was thinking that your report maybe caching and not refreshing for that reason. You can start a profiler on your SQL instance and you should see Parameter B SQL fire after selecting Parameter "A" to verify the cascading is actually working.

I hope this helps.

Ham

|||I stand corrected. Thanks Ham.|||Hi, thanks for your reply.

I found a sulotion in another forum.
I just needed to set the parameter as "Internal" and then he is refreshing according to the first parameter.

Thanks to both of you for answring.

Roy.

Parameter Information cannot be derived from SQL Statements with sub-select queries

I have the following query that finds the most Serial number of the
most current part manufactured at point 105 (near the end of the
process), then uses that serial number to find out when it started
production at point 39 (the front of the process). Then, it gives a
listing, summarized by part type (CCode), of everything in process from
the time that part entered the line, until it passes point 105. This
gives an accurate snapshot of everthing that is on the production line.
However, I need to modify this query so that the user can enter a start
date and time, and the query will find a part near that time (either
the next part, or the part just before), and produce the same results.
Here is my original query (It works with no issue):
SELECT CCode, COUNT(CCode) AS CCount
FROM [Broadcast] A
WHERE (ReportingPoint = '39') AND
(ProcessDate >= (SELECT W.ProcessDate
FROM [Broadcast] AS W JOIN
(SELECT TOP 1 ProcessDate, SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105'
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
WHERE W.ReportingPoint = '39'))
GROUP BY CCode
ORDER BY CCode
I changed the subquery to get user entry as follows:
(SELECT SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105' AND
ProcessDate >= @.GetDateFromUser
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
But I get the following error:
Parameter Information cannot be derived from SQL Statements with
sub-select queries. Set parameter information before preparing
command.
How can I get this infomation from the user before the sub-select query?Bump
Timothy.Rybak@.gmail.com wrote:
> I have the following query that finds the most Serial number of the
> most current part manufactured at point 105 (near the end of the
> process), then uses that serial number to find out when it started
> production at point 39 (the front of the process). Then, it gives a
> listing, summarized by part type (CCode), of everything in process from
> the time that part entered the line, until it passes point 105. This
> gives an accurate snapshot of everthing that is on the production line.
> However, I need to modify this query so that the user can enter a start
> date and time, and the query will find a part near that time (either
> the next part, or the part just before), and produce the same results.
> Here is my original query (It works with no issue):
> SELECT CCode, COUNT(CCode) AS CCount
> FROM [Broadcast] A
> WHERE (ReportingPoint = '39') AND
> (ProcessDate >=> (SELECT W.ProcessDate
> FROM [Broadcast] AS W JOIN
> (SELECT TOP 1 ProcessDate, SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105'
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> WHERE W.ReportingPoint = '39'))
> GROUP BY CCode
> ORDER BY CCode
> I changed the subquery to get user entry as follows:
> (SELECT SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105' AND
> ProcessDate >= @.GetDateFromUser
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> But I get the following error:
> Parameter Information cannot be derived from SQL Statements with
> sub-select queries. Set parameter information before preparing
> command.
> How can I get this infomation from the user before the sub-select query?

Parameter Available Values

If I have a paramter that has query based available values, how do I get it to use a connection string specified in another paramter to do the query?

The scenario is that I have one set of reports that pull from a number of databases that have identical schemas and each serve a different customer. i need to be able to somehow populate the valid values with the right data from the appropriate database at runtime.

Nevermind, I found it.

I just had to populate an array of ParameterValues with just the connection string and then do a call to GetReportParameters, that made sure that the other parameters had the data that they needed.

parameter and Oracle DB

I have a dataset that queries an Oracle DB. I am getting an error when I try
to set a parameter in the query. Basically I have the following:
WHERE x.system_num = s.system_num
and na.service_id = x.service_id
and na.location_num = sl.location_num
and sd.device_mux_id = dm2.device_mux_id
and (x.auth_date >= @.param_start)
ORDER BY m.mso_name, sl.system_name, system_type, d.device_address
The error I get is the following:
ORA-00936: missing expression
(System.Data.OracleClient)
When I remove the "and (x.auth_date >= @.param_start)" the error goes away.
The missing expression sounds like I have a comma at the end of the SELECT or
FROM line; but when the param line is removed the error goes away. So I
don't think that is it.
Could it be in how the parameter is set up? Just throwing out ideas here.
Thank you for any help offered.
Rob CuscadenFYI
After several HOURS of silly errors someone found the solution to this
problem. I wanted to post it here so that others can gain from my hours of
frustration. I will not say that it will work in ALL cases; but that it
works in mine and it is something for others to try if they are stuck.
Problem, using Reporting Services 2005 and accessing an Oracle database with
parameters.
In Reporting Services queries of a SQL DB, I have seen the @.param_startdate
used in the query and then the parameter is defined in the "Report
Parameters" option on the "Reports" top menu option.
So, for example
Select *
From sometable
Where sometable.datefield >= @.param_startdate
Then you go modify the Report Parameters option for the project.
If you try this with an ORACLE DB you will get several errors (ORA-00936:
missing expression is one of them).
SOLUTION: To solve this for an ORACLE DB, use the following:
SELECT *
FROM sometable
WHERE sometable.datefield = :param_startdate
-----
Now I am sure that this little ( : ) is documented somewhere in a document
out there somewhere. BUT for crying out loud... such a simple solution that
took 6 hours to find.
Please feel my frustration and I hope I prevent this from happening elsewhere.
Rob Cuscaden
"Rob" wrote:
> I have a dataset that queries an Oracle DB. I am getting an error when I try
> to set a parameter in the query. Basically I have the following:
> WHERE x.system_num = s.system_num
> and na.service_id = x.service_id
> and na.location_num = sl.location_num
> and sd.device_mux_id = dm2.device_mux_id
> and (x.auth_date >= @.param_start)
> ORDER BY m.mso_name, sl.system_name, system_type, d.device_address
> The error I get is the following:
> ORA-00936: missing expression
> (System.Data.OracleClient)
> When I remove the "and (x.auth_date >= @.param_start)" the error goes away.
> The missing expression sounds like I have a comma at the end of the SELECT or
> FROM line; but when the param line is removed the error goes away. So I
> don't think that is it.
> Could it be in how the parameter is set up? Just throwing out ideas here.
> Thank you for any help offered.
> Rob Cuscaden
>|||Rob,
I have been having the same problem and my queries look up an Oracle DB
also. I ended up getting so frustrated at mine, that our Oracle DB
Administrator told me to use a program called Toad for Oracle. Used that
which the SQL module in that program works very similar as the Microsoft
Query tool (Data, Import External Data,
New Database Query).
Anyway, I looked at the SQL Statement after building the Query (joining
tables, adding fields, adding criteria filters, etc). And I saw the same
Where table.field LIKE :whatever
Well, I tried using that in the Microsoft Query (SQL Statement) and I end up
getting some other error which I can't duplicate right now. I tried it again
and now it just completely bails me out of excel with the ever famous Debug,
Send Error Report, etc.
Anyway, wasn't sure if you ran into anything more you could share that might
help in my situation.
I also posted this issue somewhere else on here explaining more in detail
about how originally I created the query with one line of criteria where 3 of
my criteria having 5 "or" conditions. When I refresh or return data to Excel
it works fine.
Problem starts occurring if I ever have to go back in to the query to edit
and then try and refresh or return the data again. That's where I get the
ORA-00936 Missing expression error message.
I'm so stuck on this and really need to resolve somehow, because the data
that's returned from this query is used for charts, graphs, pivot tables,
that are all part of a giant VBA I wrote to automate dashboards for a bunch
of engineers.
Anwyay, any suggestions you or anyone might have on this would be greatly
appreciated.
"Rob" wrote:
> FYI
> After several HOURS of silly errors someone found the solution to this
> problem. I wanted to post it here so that others can gain from my hours of
> frustration. I will not say that it will work in ALL cases; but that it
> works in mine and it is something for others to try if they are stuck.
> Problem, using Reporting Services 2005 and accessing an Oracle database with
> parameters.
> In Reporting Services queries of a SQL DB, I have seen the @.param_startdate
> used in the query and then the parameter is defined in the "Report
> Parameters" option on the "Reports" top menu option.
> So, for example
> Select *
> From sometable
> Where sometable.datefield >= @.param_startdate
> Then you go modify the Report Parameters option for the project.
> If you try this with an ORACLE DB you will get several errors (ORA-00936:
> missing expression is one of them).
> SOLUTION: To solve this for an ORACLE DB, use the following:
> SELECT *
> FROM sometable
> WHERE sometable.datefield = :param_startdate
> -----
> Now I am sure that this little ( : ) is documented somewhere in a document
> out there somewhere. BUT for crying out loud... such a simple solution that
> took 6 hours to find.
> Please feel my frustration and I hope I prevent this from happening elsewhere.
> Rob Cuscaden
> "Rob" wrote:
> > I have a dataset that queries an Oracle DB. I am getting an error when I try
> > to set a parameter in the query. Basically I have the following:
> >
> > WHERE x.system_num = s.system_num
> > and na.service_id = x.service_id
> > and na.location_num = sl.location_num
> > and sd.device_mux_id = dm2.device_mux_id
> > and (x.auth_date >= @.param_start)
> >
> > ORDER BY m.mso_name, sl.system_name, system_type, d.device_address
> >
> > The error I get is the following:
> >
> > ORA-00936: missing expression
> > (System.Data.OracleClient)
> >
> > When I remove the "and (x.auth_date >= @.param_start)" the error goes away.
> > The missing expression sounds like I have a comma at the end of the SELECT or
> > FROM line; but when the param line is removed the error goes away. So I
> > don't think that is it.
> >
> > Could it be in how the parameter is set up? Just throwing out ideas here.
> >
> > Thank you for any help offered.
> > Rob Cuscaden
> >sql

Parameter All Level

Hello,

I'm using SQL Server Reporting Services with an Analysis Services Cube. My current report has a query based parameter. The query returns country codes and the ALL-Level of my country dimension.

Is there a way to remove the ALL-Level of the result set of the query? How can I realize this?

Thanks in advance.

Christian

Hi,

I suggest to change the query. I think, your generated query looks like:

WITH MEMBER [Measures].[ParameterCaption] AS '[Country].[Country].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Country].[Country].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Country].[Country].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Country].[Country].ALLMEMBERS ON ROWS FROM [MyCube]

Change it to:

WITH MEMBER [Measures].[ParameterCaption] AS '[Country].[Country].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Country].[Country].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Country].[Country].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , Descendants([Country].[Country], [Country].[yourLevel1name]) ON ROWS FROM [MyCube]

For further help, look at the Descendants Function in MDX.

Hans

Parameter & Execution plans.

Hi all,

I have a table TableA with few million rows. When I query TableA , the execution plans changes based on the input parameter as shown below . Why this happens ? How to resolve this ? Any inputs would be appreciated.

SELECT * FROM TableA WHERE Column1 = 1 => SELECT -> Clustered Index Scan (100%)

SELECT * FROM TableA WHERE Column1 = 2 => SELECT -> Clustered Index Scan (100%)

SELECT * FROM TableA WHERE Column1 = 3 => SELECT -> Parallelism (3%) -> Clustered Index Scan (97%)

SELECT * FROM TableA WHERE Column1 = 4 => SELECT -> Nested Loops -> Index Seek (50%) -> Clustered Index Seek (50%)

(takes a very long time to retrieve the records)

Thanks in advance,

DBLearner.

Hi DBLearner,

SQL Server recompiles your SQL each time becuase you are running Adhoc SQL. You can either create a stored procedure or use Forced Parameterization database option (sql 2005 ONLY).

The reason its using different access methods could be down to what STATISTICS are stored for those values. You might want to update stats and try it again.

Jag

|||

what all are the indexes created on this table/column. ALso tell us the approx occurence of this column value (if its not PK).

Madhu

Paramaterised Query to ORACLE database - not all variables bound

Hi

when I run this query against an Oracle 9 datasource I get a message saying 'not all variables bound'

select <some fields> from star
where nr_jahr_star = :Year

my datasource is correct as the same report runs fine if I just put in a fixed year and I have the Oracle client software on both my PC and the RS server

I've tried various combinations with the parameter settings also with no luck.

Thanks

Steven

Year is a reserved word - did you try changing the parameter name?

Paramater in Case statement

When I enter the following query in the MSSQL 2005 Server Management Studio,
I get results, but when i enter the same query (Minus the top 2 lines) in RS,
i get an a error:
Title: Microsoft Visual Database Tools
Error: The Parameter is incorrect.
Any ideas what i'm doing wrong? This query is just a simplified example
using the northwind db.
DECLARE @.Region AS NVARCHAR(15)
SELECT @.Region = '1'
SELECT *
FROM Customers
WHERE Region = CASE
WHEN @.Region = '1'
THEN 'SP'
END
--
Lucas DargisHi Lucas,
> DECLARE @.Region AS NVARCHAR(15)
> SELECT @.Region = '1'
You are using a variable (@.Region) in your query and if you leave off the
top 2 lines, you are not declaring the variable and setting its value. The
statement needs this.
HTH!
Kind regards - Fred|||thanks Fred.
I left off the top two lines because in RS you specify the parameters in the
'Report > Report Parameters' window.
i found that this is just a bug in the design view of RS. when i Previewed
the report, it worked just fine.
thanks
--
Lucas Dargis
"Fred Block" wrote:
> Hi Lucas,
> > DECLARE @.Region AS NVARCHAR(15)
> > SELECT @.Region = '1'
> You are using a variable (@.Region) in your query and if you leave off the
> top 2 lines, you are not declaring the variable and setting its value. The
> statement needs this.
> HTH!
> Kind regards - Fred
>
>|||This is not true. The way it works, if you have not declared it then RS
knows that it is a query parameter and automatically creates a report
parameter for it. I used the below code against adventureworks and it works:
SELECT *
FROM sales.Customer
WHERE TerritoryID =CASE
WHEN @.Region = '2'
THEN 2
END
Use the generic query designer (the button to switch to generic mode is one
of the buttons to the right of the ...).
Execute the query, you should be prompted for a value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fred Block" <fblock@.no_spams.w-systems.com> wrote in message
news:utQYEFuTHHA.1636@.TK2MSFTNGP02.phx.gbl...
> Hi Lucas,
>> DECLARE @.Region AS NVARCHAR(15)
>> SELECT @.Region = '1'
> You are using a variable (@.Region) in your query and if you leave off the
> top 2 lines, you are not declaring the variable and setting its value. The
> statement needs this.
> HTH!
> Kind regards - Fred
>|||Bruce,
that is what i said... just not as well.
i just gave him the 'check' since i couldn't give it to myself.
--
Lucas Dargis
"Bruce L-C [MVP]" wrote:
> This is not true. The way it works, if you have not declared it then RS
> knows that it is a query parameter and automatically creates a report
> parameter for it. I used the below code against adventureworks and it works:
> SELECT *
> FROM sales.Customer
> WHERE TerritoryID => CASE
> WHEN @.Region = '2'
> THEN 2
> END
> Use the generic query designer (the button to switch to generic mode is one
> of the buttons to the right of the ...).
> Execute the query, you should be prompted for a value.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Fred Block" <fblock@.no_spams.w-systems.com> wrote in message
> news:utQYEFuTHHA.1636@.TK2MSFTNGP02.phx.gbl...
> > Hi Lucas,
> >
> >> DECLARE @.Region AS NVARCHAR(15)
> >> SELECT @.Region = '1'
> >
> > You are using a variable (@.Region) in your query and if you leave off the
> > top 2 lines, you are not declaring the variable and setting its value. The
> > statement needs this.
> >
> > HTH!
> >
> > Kind regards - Fred
> >
>
>