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 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 missing

Hi,

I was developed the rdl report and i deployed to the server, In my report having the single input parameter When i run the report

asking parameter value and i was gave the value it was generated the report it is working fine.

Then this rdl report bind to the reportviewer control using

Report viwer tasks :

Choose Report : < Server Report>

Server Report Url:http://localhost/reportserver

Report Path : /Sales/Details

then i run the error should shown:

An error has occurred during report processing.

Query execution failed for data set 'Details'.

Procedure 'Usp_Details' expects parameter '@.PId', which was not supplied.

How i pass the parameter value in reportviewer.

Plz any one would help to me.

regards

kumar

First deploy it and go to the IIS and open the Report folder and browse it.You can find your report there.Run that report.If that is working fine so some other problem was there.Do like that way if it is working so the page should work fine.

|||

Hi,

Thanks for your response

I did you told that way it is working fine and i was bind the rdl report to Reportviewer in my webpage then i ran the error should occured

An error has occurred during report processing.

Query execution failed for data set 'Details'.

Procedure 'Usp_Details' expects parameter '@.PId', which was not supplied|||

ptParameters[0] =
new Microsoft.Reporting.WebForms.ReportParameter("ClientId", sessParam );

this.ReportViewer1.ServerReport.SetParameters(RptParameters);
this.ReportViewer1.ServerReport.Refresh();

check this article for more infohttp://www.codeproject.com/sqlrs/ReportViewer2005.asp

|||

It is working fine thanks lot

regards

kumar

sql

Paramaters

Hi, we run an ASP.NET portal that has fairly extensive reporting needs (it's
a government portal...) We're thinking of switching to reporting services
from a custom xml/xsl solution. My questions are:
1. interfacing with RS from our existing .net web app. We have screens set
up to let the user do things like select paramaters and stuff. Can this be
integrated? Currently for things that require multiple selections (for
instance, selecting a set of cities to report on), we build a string of city
ids (eg. 1,3,45,60,61) that gets passed into a stored procedure, which uses
the string to select info on each city (using IN(1,3,45,60,61)). Is this
possible in RS, and can we use our existing frontend? If we use our existing
frontend, can we still have access to the toolbar, for things like exporting
to different formats?
2. I know dynamic fields aren't possible. But can fields be left out /
included based on the presence of data to fill it?
3. Is it possible to use XML as a datasource to a report?
If anyone has any insight into these, I'd really appreciate it. Thanks in
advance
Peter LRS has two ways to integrate an existing app. You can either use web
services or you can use URL integration. To be able to have the toolbar you
need to use the URL integration. What you want to do with #1 is very easy to
do using URL integration. For question number 2, whether something is
visible can be set with an expression. The expression can decide not to show
it based on whatever you choose. #3 is possible but a little more difficult.
It is possible to create your own data extension. I suggest first seeing if
you really need to do this or if you can just use the stored procedures
and/or queries instead.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"plandry@.newsgroups.nospam"
<plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:CF96A1DF-84CA-4136-BCF4-9AF3BBC345CF@.microsoft.com...
> Hi, we run an ASP.NET portal that has fairly extensive reporting needs
> (it's
> a government portal...) We're thinking of switching to reporting services
> from a custom xml/xsl solution. My questions are:
> 1. interfacing with RS from our existing .net web app. We have screens set
> up to let the user do things like select paramaters and stuff. Can this be
> integrated? Currently for things that require multiple selections (for
> instance, selecting a set of cities to report on), we build a string of
> city
> ids (eg. 1,3,45,60,61) that gets passed into a stored procedure, which
> uses
> the string to select info on each city (using IN(1,3,45,60,61)). Is this
> possible in RS, and can we use our existing frontend? If we use our
> existing
> frontend, can we still have access to the toolbar, for things like
> exporting
> to different formats?
> 2. I know dynamic fields aren't possible. But can fields be left out /
> included based on the presence of data to fill it?
> 3. Is it possible to use XML as a datasource to a report?
>
> If anyone has any insight into these, I'd really appreciate it. Thanks in
> advance
> Peter L

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?

Parallel execution in SQL Server

Is there any way to run a stored procedure in parallel to another one? i.e. I have a stored procedure that sends an email. I then scan a table and send any unsent emails. I do not want the second part to slow the response to the user.

Why don't make a JOB for send any unsend emails ?

Store procedure only insert a row in a buffer table and job consume the rows in that table.

|||I have tried using a Job before and it was not a nice experience - guess I will have to sort it out properly this time.

Pakcage Execution Error under SQL server agent

Hi SSIS experts!

I have been trying to schedule a package I design to run off hour, but unable to do so. Here is a strange issue:

1. I am able to run and excute the package successfully through VSS. After I finished designing all my flows and containers, my exceution was successful to all my data sources.

2. I was able to deploy and run the actual package by sending to my local file system and it runs successfully through Execute Package Utility.

HOWEVER!!! when I tried to schedule this package through file system under sql server agent to run at night or through start job within SQL agent always failed...

I am puzzled so I added some logging on the package. The error message shows the following....

<message>The connection "{087B883F-D188-440A-9501-FF38CF5CEC28}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

<message>Failed to acquire connection "10.0.2.2.LogDB.jhwang". Connection may not be configured correctly or you may not have the right permissions on this connection.

But I thought if I had set the connection correctly to remember my passwords and using SQL server standard login within my package (connection manager) and should resolved the connection issues....

Why did it failed when I try to run it under sqlagent? But not through Execute Package Utility? Is there is a special setting I need to do for it to run under sql agent?

I notice within the job step when I choose the file source to point to my package... there was a tab called data sources where it has the connections I defined in my package. Does it matter if I put a check box on them or not? Either way they failed to connect.

Please help!

JON

Not sure what the problem is but you may want to debug it by using this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

PAGEIOLATCH_SH wait time is 80%

Hi,
One of my sql job contain 80% read & 20% insert /updates.
This job also contains number of cursor operations. It use to run for 2Hrs
or less and now it runs for 8hrs.
When I check the process wait time. The PAGEIOLATCH_SH is 80% and
PAGEIOLATCH_EX is 10%.
I am not sure what is causing this high PAGEIOLATCH_SH wait time?
Any thoughts on what could be the issue here?
Regards,
Sarav.
Maybe http://sqldev.net/articles/WaitTypes.htm can help you.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
|||I'm going to venture a guess here, but I'd bet that your use of cursors is
at the root of your problem. Resource utilization by cursors has a way of
growing in non-linear ways. Some of the gurus here might be able to offer
set based solutions if you can succinctly describe the process.
Bob Castleman
SuccessWare Software
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
|||Hi Sarav
The process wait time % split seems normal to me given your estimate of 80%
read / 20% insert / updates. The PAGEIOLATCH_SH represents the shared locks
taken under the 80% read operations and thePAGEIOLATCH_EX represents the
exclusive locks required to perform the insert / updates.
Perhaps the post re cursors has some merit. I'd also be looking at general
resource availability (memory / cache stats / lock blocks) and whether the
sql job is being blocked by other processes.
HTH
Regards,
Greg Linwood
SQL Server MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
|||That means you have a heck of a lot of disk access going on. I would ensure
the operations are well tuned first.
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>

PAGEIOLATCH_SH wait time is 80%

Hi,
One of my sql job contain 80% read & 20% insert /updates.
This job also contains number of cursor operations. It use to run for 2Hrs
or less and now it runs for 8hrs.
When I check the process wait time. The PAGEIOLATCH_SH is 80% and
PAGEIOLATCH_EX is 10%.
I am not sure what is causing this high PAGEIOLATCH_SH wait time?
Any thoughts on what could be the issue here?
Regards,
Sarav.Maybe http://sqldev.net/articles/WaitTypes.htm can help you.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||I'm going to venture a guess here, but I'd bet that your use of cursors is
at the root of your problem. Resource utilization by cursors has a way of
growing in non-linear ways. Some of the gurus here might be able to offer
set based solutions if you can succinctly describe the process.
Bob Castleman
SuccessWare Software
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||Hi Sarav
The process wait time % split seems normal to me given your estimate of 80%
read / 20% insert / updates. The PAGEIOLATCH_SH represents the shared locks
taken under the 80% read operations and thePAGEIOLATCH_EX represents the
exclusive locks required to perform the insert / updates.
Perhaps the post re cursors has some merit. I'd also be looking at general
resource availability (memory / cache stats / lock blocks) and whether the
sql job is being blocked by other processes.
HTH
Regards,
Greg Linwood
SQL Server MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||That means you have a heck of a lot of disk access going on. I would ensure
the operations are well tuned first.
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>

PAGEIOLATCH_SH wait time is 80%

Hi,
One of my sql job contain 80% read & 20% insert /updates.
This job also contains number of cursor operations. It use to run for 2Hrs
or less and now it runs for 8hrs.
When I check the process wait time. The PAGEIOLATCH_SH is 80% and
PAGEIOLATCH_EX is 10%.
I am not sure what is causing this high PAGEIOLATCH_SH wait time?
Any thoughts on what could be the issue here?
Regards,
Sarav.Maybe http://sqldev.net/articles/WaitTypes.htm can help you.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||I'm going to venture a guess here, but I'd bet that your use of cursors is
at the root of your problem. Resource utilization by cursors has a way of
growing in non-linear ways. Some of the gurus here might be able to offer
set based solutions if you can succinctly describe the process.
Bob Castleman
SuccessWare Software
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||Hi Sarav
The process wait time % split seems normal to me given your estimate of 80%
read / 20% insert / updates. The PAGEIOLATCH_SH represents the shared locks
taken under the 80% read operations and thePAGEIOLATCH_EX represents the
exclusive locks required to perform the insert / updates.
Perhaps the post re cursors has some merit. I'd also be looking at general
resource availability (memory / cache stats / lock blocks) and whether the
sql job is being blocked by other processes.
HTH
Regards,
Greg Linwood
SQL Server MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||That means you have a heck of a lot of disk access going on. I would ensure
the operations are well tuned first.
--
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>