Parameter direction of a stored procedure

I am using the MS SQL Server Management Studio Express to create a stored procedure in one of my databases. I specify one of the parameters as OUTPUT as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ProcRetDbl]

@.Threshold real, @.Result real OUTPUT
AS
BEGIN

SET NOCOUNT ON;

SELECT @.Result = Channel1 FROM DataTable WHERE Channel2 < @.Threshold
END

But then when I look at the properties of the @.Result parameter in the Object Explorer's tree, it is shown as "Input/Output". Now, this seems like no problem at all since it will work fine as output, even though I don't need it to be able to do input as well, but I'm wondering why that is happening.

I am using ADO.Net on the other end to execute the procedure and I need to decide what parameter type to set to the SqlParameter object: "Output" or "InputOutput". I'm sure I can sort this out but I usually like to know what I'm doing. Thanks for the help.

KamenYou are probably thinking too hard :)

You need to supply a parameter in to give you something to read when the value comes out. I can't remember what I use for sqlParameters - probably inputoutput. Try both - what have you got to lose?|||The only pure output from a stored procedure is the return code value and any result sets. Procedure parameters must be input, and can optionally be output too.

-PatP

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

Paging File size

I hope these are pretty basic SQL Server management questions. I'm running a SP that uses a lot of memory. The server has about 8GB and it uses 7.95GB. When the memory usage goes up, so does the size of the paging file. I'm assuming the page file grows so it can write the memory contents to disk in the case of a crash. How necessary is this? It seems like any advantage I'd gain from working out of memory is lost because I'm working off the drive too.

Can I/should I turn this "feature" off?

Thanks!

Brian

Paging file is used to swap files quickly in and out of RAM. The system can't possibly keep everything in RAM that it ever needs. So it swaps in use files back and forth between RAM and the pagefile as needed. It's a secondary backup to RAM, sort of.

The paging file is growing because if SQL Server is using 7.95 GB of RAM of 8 available, there's not enough RAM left for the operating system to function, and it is forced to rely on the paging file for its normal operations.

A general rule of thumb for a SQL Server is that the paging file should be at least double the amount of RAM. So if you have 8 GB of RAM, the paging file should be 16 GB ... a preferably on a different disk than any of the SQL files. Set the paging file to a hard size, don't let Windows manage your paging file.

If your server has 4 or more GB of RAM, you should reserve at least 1 GB of RAM for the OS. You do this by setting the maximum amount of memory that SQL Server can use to the total amount - 1 GB. So for 8GB of RAM, set SQL to use a maximum of 7 GB.

If 7.95 GB of RAM are being used though not necessarily by SQL Server, the OS may be forcing SQL Server to give up memory and forcing it to page. Make sure that SQL Server has been granted "Lock pages in memory" rights in the group policy editor.

|||

Robert,

The thing that bothers me is the files grow simultaneously. They both start out below 1GB and both grow at approximately the same rate. That is why I wondered if Windows was preparing for a memory dump by keeping an "Image" of memory in case of a crash. I haven't used this setting since XP, but assume it's still around in 2003. Although I think it's disabled by default.

Whatever the case, I'll assume that the behavior is normal for now. I have implemented a couple of your suggestions.

Thanks for the help.

Brian