Microsoft Reporting Services - Error - Expects Parameter - Stored Procedure

Solution:
This happens to me quite a bit...its because the designer removes the
code that defines what
values are passed to your stored procedure.
You have to select the report you're working on > Right-Click 'Code' >
Locate the <Query> XML tag...and you'll have to re-define the
<QueryParameters>.
<Query>
<DataSourceName>GPS</DataSourceName>
<CommandType>StoredProcedure</CommandType>
<CommandText>spLRGetLeadRotationSummary</CommandText>
<QueryParameters>
<QueryParameter Name="@.StartDate">
<Value>=Parameters!StartDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@.EndDate">
<Value>=Parameters!EndDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@.RegionID">
<Value>=Parameters!RegionID.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>You can also do this by clicking on the ..., parameters tab. Modifying the
xml is dangerous. Although if you have a lot of query parameters and RS
keeps losing it then I have sometimes saved the section off so I could copy
it back in.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<petejk@.gmail.com> wrote in message
news:1135124852.986963.63390@.f14g2000cwb.googlegroups.com...
> Solution:
> This happens to me quite a bit...its because the designer removes the
> code that defines what
> values are passed to your stored procedure.
> You have to select the report you're working on > Right-Click 'Code' >
> Locate the <Query> XML tag...and you'll have to re-define the
> <QueryParameters>.
>
> <Query>
> <DataSourceName>GPS</DataSourceName>
> <CommandType>StoredProcedure</CommandType>
> <CommandText>spLRGetLeadRotationSummary</CommandText>
> <QueryParameters>
> <QueryParameter Name="@.StartDate">
> <Value>=Parameters!StartDate.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.EndDate">
> <Value>=Parameters!EndDate.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.RegionID">
> <Value>=Parameters!RegionID.Value</Value>
> </QueryParameter>
> </QueryParameters>
> </Query>
>

Microsoft OLE DB Provider for SQL Server error '80004005' - Unspecified error

Hi,
I have an ASP code that calls a stored procedure. I use a Command
object to execute the stored procedure. The Stored procedure returns
data when executed in Query analyser. But it takes around 1 minute for
this. The stored procedure involves querying a huge amount of data.
When I try to open the ASP page, I get the following error :
Microsoft OLE DB Provider for SQL Server error '80004005'
Unspecified error
Any suggestions?
TIA,
AnishyaHi
Have you checked with SQL Profiler that the procedure is being called? Check
that the login/user you are using has the correct permissions. If this has
timed out then I would expect that a TIMEOUT error message. You may also want
to check http://www.aspfaq.com/show.asp?id=2009
John
"Ani" wrote:
> Hi,
> I have an ASP code that calls a stored procedure. I use a Command
> object to execute the stored procedure. The Stored procedure returns
> data when executed in Query analyser. But it takes around 1 minute for
> this. The stored procedure involves querying a huge amount of data.
> When I try to open the ASP page, I get the following error :
> Microsoft OLE DB Provider for SQL Server error '80004005'
> Unspecified error
> Any suggestions?
> TIA,
> Anishya
>|||Hi,
The procedure is being called in the Profiler. I tried executing the
same stored procedure call in query analyser and saw that it works
fine. The only problem is that it takes around 1 minute. So I am sure
this is why the ASP page gives the error. The stored proc makes the use
of a temporary table. I tried executing the stored proc by using a
permanent table instead of the temporary table. It doesnt take much
time. But doesn't permanent tables involve more performance overheads
than temporary tables? I am trying to find another alternative. Is
there any way that I could increase the timeout period for ASP pages?
Thanks,
Anishya|||Hi
You could be seeing contention on tempdb if a lot of people are
simulataneously creating temporary tables, see
http://support.microsoft.com/kb/328551. You should see if the temporary table
is really necessary or possibly use a table variable as an alternative.
You may also want to change the timeout on your command object
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprocommandtimeout.asp
John
"Ani" wrote:
> Hi,
> The procedure is being called in the Profiler. I tried executing the
> same stored procedure call in query analyser and saw that it works
> fine. The only problem is that it takes around 1 minute. So I am sure
> this is why the ASP page gives the error. The stored proc makes the use
> of a temporary table. I tried executing the stored proc by using a
> permanent table instead of the temporary table. It doesnt take much
> time. But doesn't permanent tables involve more performance overheads
> than temporary tables? I am trying to find another alternative. Is
> there any way that I could increase the timeout period for ASP pages?
> Thanks,
> Anishya
>

Metadata for SP parameter default values

I am looking for a way to list any default values for stored procedure
parameters.
For example with this stored procedure:
CREATE PROC dbo.pr_GetSomeData
(
@.SomeInt int,
@.AnotherInt int = 0,
@.SomeDate datetime = NULL
)
I'd like to be able to create a table that would look like this
ParameterName DefaultValue
@.SomeInt
@.AnotherInt 0
@.SomeDate NULL
I can get at the parameters themselves through information_schema.PARAMETERS
but I don't see a way to get at the default value.
Any suggestions would be appreciated.
Joel Reinford
Data Management Solutions LLCSQL Server does not store information about this in any sys table. One
possible solution could be parsing the sp code from syscomment or
information_schema.routines for the sp in question.
AMB
"Joel Reinford" wrote:

> I am looking for a way to list any default values for stored procedure
> parameters.
> For example with this stored procedure:
> CREATE PROC dbo.pr_GetSomeData
> (
> @.SomeInt int,
> @.AnotherInt int = 0,
> @.SomeDate datetime = NULL
> )
> I'd like to be able to create a table that would look like this
> ParameterName DefaultValue
> @.SomeInt
> @.AnotherInt 0
> @.SomeDate NULL
> I can get at the parameters themselves through information_schema.PARAMETE
RS
> but I don't see a way to get at the default value.
> Any suggestions would be appreciated.
>
> Joel Reinford
> Data Management Solutions LLC
>
>

Meta Data

Greetings -
Can somebody please help me out with retrieving Meta Data dynamically for a
Stored Procedure.
I know I can use SET FRMONLY ON to retrieve it - but this return a Header
info. What I want is a resultset containing that META Data that can be
inserted or used in a SELECT statement fo further manipulations.
TIA.
KOYSee my response in microsoft.public.sqlserver.programming
"Kayode Yusuf" <KayodeYusuf@.discussions.microsoft.com> wrote in message
news:92EF08FB-0675-4B90-9563-E29482987018@.microsoft.com...
> Greetings -
> Can somebody please help me out with retrieving Meta Data dynamically for
a
> Stored Procedure.
> I know I can use SET FRMONLY ON to retrieve it - but this return a Header
> info. What I want is a resultset containing that META Data that can be
> inserted or used in a SELECT statement fo further manipulations.
> TIA.
> KOY|||For starters, Tanx for the post. WHat needs to be done is process the
ResultSet in TSQL as done through an API.
If you make call a Call to SQLServer throughan API with SET FMTON - it
executes
the procedure and returns a zero lenght resultselt - the META Data - you can
then through the API - iterate through th ResultSet Column names to find out
the META data for the procedure.
Essentially, I want to achive the Same in TSQL mode - I need to get the META
data of a Stored procedure and be able to identify the Columns returned by
the call using SET FMTONL directive.
TIA,
KOY
"Armando Prato" wrote:
> See my response in microsoft.public.sqlserver.programming
>
> "Kayode Yusuf" <KayodeYusuf@.discussions.microsoft.com> wrote in message
> news:92EF08FB-0675-4B90-9563-E29482987018@.microsoft.com...
> > Greetings -
> >
> > Can somebody please help me out with retrieving Meta Data dynamically for
> a
> > Stored Procedure.
> >
> > I know I can use SET FRMONLY ON to retrieve it - but this return a Header
> > info. What I want is a resultset containing that META Data that can be
> > inserted or used in a SELECT statement fo further manipulations.
> >
> > TIA.
> >
> > KOY
>
>

message queues

I having a fight with what I think is the message queue system when
running stored procedures. If I have this as a sp
print 'Handling First File'
(do some processing on the first file)
print 'Handling Second File'
(do some processing on the second file)
print 'Handling Third File'
(do some processing on the third file)
print 'Done'
I want to use the SQLMDO 'ExecuteWithResultsAndMessages' and
'ServerMessage' functions to use print commands as part of my front end
but what happens is that while all the processing is done my dialog
filled by 'ServerMessage.Message' is blank and then all my print
commands come together - London bus style. (Query Analyser does the
same).
This presumably is down to Message Queuing ? One help file I saw says
something like "when you create a Stored Procedure (with queuing
enabled)...." suggesting that you can create a sp with queing
disabled? but I cant find anything in the syntax to stipulate that.
Is there a way round this so that messages are forwarded by the agent
as they are reached in the script and not queued.?
Thanks
GlennIt really has nothing to do with queuing per say it is that the packet does
not get sent to the client until the batch is done or the buffer is full.
In a nutshell since the packets are around 4K in size it doesn't waste many
round trips each time a little bit of info is placed in the buffer. It
waits until it is full until it sends it or when the batch is done. Here is
an example to show this. Comment out the replicate statement and you will
see it doesn't print until the batch is done.
DECLARE @.X INT
SET @.X = 1
WHILE @.X < 100
BEGIN
PRINT CAST(@.X AS VARCHAR(20))
PRINT REPLICATE(' ',8000)
SET @.X = @.X + 1
waitfor delay '00:00:01'
END
Andrew J. Kelly SQL MVP
<glenn.hughes@.luk.net> wrote in message
news:1140543654.666118.191320@.g44g2000cwa.googlegroups.com...
>I having a fight with what I think is the message queue system when
> running stored procedures. If I have this as a sp
> print 'Handling First File'
> (do some processing on the first file)
> print 'Handling Second File'
> (do some processing on the second file)
> print 'Handling Third File'
> (do some processing on the third file)
> print 'Done'
> I want to use the SQLMDO 'ExecuteWithResultsAndMessages' and
> 'ServerMessage' functions to use print commands as part of my front end
> but what happens is that while all the processing is done my dialog
> filled by 'ServerMessage.Message' is blank and then all my print
> commands come together - London bus style. (Query Analyser does the
> same).
> This presumably is down to Message Queuing ? One help file I saw says
> something like "when you create a Stored Procedure (with queuing
> enabled)...." suggesting that you can create a sp with queing
> disabled? but I cant find anything in the syntax to stipulate that.
> Is there a way round this so that messages are forwarded by the agent
> as they are reached in the script and not queued.?
> Thanks
> Glenn
>|||You can 'cheat' the messages out faster by raising low-level errors...
RAISERROR('Hack!!!',0,1) WITH NOWAIT
HTH,
Ben
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SqYsHxNGHA.1676@.TK2MSFTNGP09.phx.gbl...
> It really has nothing to do with queuing per say it is that the packet
> does not get sent to the client until the batch is done or the buffer is
> full. In a nutshell since the packets are around 4K in size it doesn't
> waste many round trips each time a little bit of info is placed in the
> buffer. It waits until it is full until it sends it or when the batch is
> done. Here is an example to show this. Comment out the replicate
> statement and you will see it doesn't print until the batch is done.
> DECLARE @.X INT
> SET @.X = 1
> WHILE @.X < 100
> BEGIN
> PRINT CAST(@.X AS VARCHAR(20))
> PRINT REPLICATE(' ',8000)
> SET @.X = @.X + 1
> waitfor delay '00:00:01'
> END
>
> --
> Andrew J. Kelly SQL MVP
>
> <glenn.hughes@.luk.net> wrote in message
> news:1140543654.666118.191320@.g44g2000cwa.googlegroups.com...
>|||Thanks to both of you. Ben, I just love that word "Cheat" its what
programming is all about. The error level idea sounds good. I'll try
that
Cheers
Glenn