parameter problem in sql2005 sp

in sql2005 store procedure is accepting the varchar value which is more than the parameter's varchar value size it is not giving any exception error but removes the remaining character from right side

Example :

Table

-

create table employee

(Empname Varchar(5), EmpNo Int)

Procedure

create procedure test (@.para1 Varchar(5))

as

SELECT * FROM employee WHRE empNAME=@.PARA1

Execution

--

exec test 'ABCDEFGH'

it consider only 'ABCDE' and ignores remaining character without giving any exception error.

Thanks,

It is the specification. Not only in 2005 earlier versions also use the same specification.

But in sql server 2005 you can overcome this issue using Varchar(max) it never truncate the value.

Declare @.A varchar(1), @.B varchar(Max);

Set @.A = '1234' --Truncated value

Set @.B = '1234' --Never Truncate the value

Select @.A, @.B

Note:

The character length is only validated on Insert & Update statement.

|||thanks fo reply

parameter problem in sql2005 sp

in sql2005 store procedure is accepting the varchar value which is more than the parameter's varchar value size it is not giving any exception error but removes the remaining character from right side

Example :

Table

-

create table employee

(Empname Varchar(5), EmpNo Int)

Procedure

create procedure test (@.para1 Varchar(5))

as

SELECT * FROM employee WHRE empNAME=@.PARA1

Execution

--

exec test 'ABCDEFGH'

it consider only 'ABCDE' and ignores remaining character without giving any exception error.

Thanks,

It is the specification. Not only in 2005 earlier versions also use the same specification.

But in sql server 2005 you can overcome this issue using Varchar(max) it never truncate the value.

Declare @.A varchar(1), @.B varchar(Max);

Set @.A = '1234' --Truncated value

Set @.B = '1234' --Never Truncate the value

Select @.A, @.B

Note:

The character length is only validated on Insert & Update statement.

|||thanks fo replysql

Parameter problem

I have this sproc:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_CrimRecTest]

@.caseID nvarchar(12),
@.lastName nvarchar(25) output,
@.firstName nvarchar(20) output

AS
BEGIN
SET NOCOUNT ON;

SELECT dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @.caseID)

END

How do I get values into @.lastName and @.firstName? I have tried this:

SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME

I got this error:

Msg 141, Level 15, State 1, Procedure usp_CrimRecTest, Line 22
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Help!

Thanks.

Hi,

that a easy thing, like the error says, you can′t combine data retrieval (thats selecting or outputting the result *and* assigning values to variables. If you want to put out the *CASENO*, put in in a variable and do a SELECT @.CASENO afterwards fetching the values into the variables.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

This may be doing what Jens suggested, but I'm not sure. I changed:

SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME...

to this:

SELECT @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME...

and it worked.

Thanks for the reply Jens.

|||

Nice to hear that this helped, but if you want to select (output the case number, I am not sure after you last reply) you *could* do that:

SELECT @.Caseno = dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME...

Select @.Caseno

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Parameter problem

I have this sproc:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_CrimRecTest]

@.caseID nvarchar(12),
@.lastName nvarchar(25) output,
@.firstName nvarchar(20) output

AS
BEGIN
SET NOCOUNT ON;

SELECT dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @.caseID)

END

How do I get values into @.lastName and @.firstName? I have tried this:

SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME

I got this error:

Msg 141, Level 15, State 1, Procedure usp_CrimRecTest, Line 22
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Help!

Thanks.

Change
SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME.....

to

SELECT @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME......

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Parameter passing to a stored procedure

Hey huys, I got this stored procedure. First of all: could this work?

--start :)
CREATE PROCEDURE USP_MactchUser

@.domainUserID NVARCHAR(50) ,

@.EmployeeID NVARCHAR(50) ,

@.loginType bit = '0'
AS

INSERT INTO T_Login

(employeeID, loginType, domainUserID)

Values
(@.EmployeeID, @.loginType, @.domainUserID)
GO
--end :)

then I got this VB.Net code in my ASP.net page...

--begin :)
Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand
Dim daMatchedUser As SqlClient.SqlDataAdapter
SqlConnection1.Open()
'conn openned
daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1)
daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@.EmployeeID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@.domainUserID", SqlDbType.NVarChar, 50))
daMatchedUser.SelectCommand.Parameters("@.EmployeeID").Value = Trim(lblEmployeeID.Text)
daMatchedUser.SelectCommand.Parameters("@.domainUserID").Value = Trim(lblDomainuserID.Text)
daMatchedUser.SelectCommand.Parameters("@.EmployeeID").Direction = ParameterDirection.Output
daMatchedUser.SelectCommand.Parameters("@.domainUserID").Direction = ParameterDirection.Output
SqlConnection1.Close()
'conn closed

End Sub
--

If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?no.

Private Sub matchUser()
Dim insertMatchedUser As SqlClient.SqlCommand

insertMatchedUser = new SqlCommand;
insertMatchedUser.CommandText = "USP_MactchUser"
insertMatchedUser.CommandType = CommandType.StoredProcedure
insertMatchedUser.Parameters.Add(New SqlClient.SqlParameter("@.EmployeeID", SqlDbType.NVarChar, 50))
insertMatchedUser.Parameters.Add(New SqlClient.SqlParameter("@.domainUserID", SqlDbType.NVarChar, 50))
insertMatchedUser.Parameters("@.EmployeeID").Value = lblEmployeeID.Text.Trim()
insertMatchedUser.Parameters("@.domainUserID").Value = lblDomainuserID.Text.Trim()

insertMatchedUser.Connection = SqlConnection1;
SqlConnection1.Open()
'conn openned
insertMatcheduser.ExecuteNonQuery()

insertMatchedUser.Dispose()
SqlConnection1.Dispose()
End Sub|||Also:

CREATE PROCEDURE USP_MactchUser
@.domainUserID NVARCHAR(50) ,
@.EmployeeID NVARCHAR(50) ,
@.loginType bit = 0
AS

the bit data type literal should not have single quotes.sql

parameter passing problem

why this wont work?

error:
Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'test1' expects parameter '@.cid', which was not supplied.

.............................................................
ASPX CODE:
string cid12;
cid12 = "user5";
// Connection
ConnectionStringSettings mysettings;
mysettings = System.Configuration.ConfigurationManager.ConnectionStrings["dbase1_connection"];
string myConnectionString = mysettings.ConnectionString;
SqlConnection conn1 = new SqlConnection(myConnectionString);

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn1;
cmd.CommandText = "test1";
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = cmd.Parameters.Add("@.cid", SqlDbType.NChar, 20);
param.Direction = ParameterDirection.Input;
param.Value = cid12;

conn1.Open();
cmd.ExecuteNonQuery();
conn1.Close();
.............................................................

STORED PROCEDURE:
ALTER PROCEDURE dbo.test1
(
@.cid nvarchar(20)
)
AS
/* SET NOCOUNT ON */
select tech_id, customer_id, issue_main from [case] where customer_id = @.cid
--GO
RETURN

.............................................................

im using asp.net, C#, and sql express
forgive me. im new to this .net
PLS HELP

pls ignore the post above. i found out that nothing is wrong with that code. i discovered that one on my grid view tables is causing the issue and it is indeed not passing a parameter and thats the reason why i have wasted 12hrs looking at my monitor. hehehe.

my fault. im just human. forgive me.

thanks to you all.

parameter passing problem

why this wont work?
error:
Exception Details: System.Data.SqlClient.SqlException: Procedure or Function 'test1' expects parameter '@.cid', which was not supplied.
.................................................................................
ASPX CODE:
string cid12;
cid12 = "user5";
// Connection
ConnectionStringSettings mysettings;
mysettings = System.Configuration.ConfigurationManager.ConnectionStrings["dbase1_connection"];
string myConnectionString = mysettings.ConnectionString;
SqlConnection conn1 = new SqlConnection(myConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn1;
cmd.CommandText = "test1";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.Parameters.Add("@.cid", SqlDbType.NChar, 20);
param.Direction = ParameterDirection.Input;
param.Value = cid12;

conn1.Open();
cmd.ExecuteNonQuery();
conn1.Close();
.................................................................................
STORED PROCEDURE:
ALTER PROCEDURE dbo.test1
(
@.cid nvarchar(20)
)
AS
/* SET NOCOUNT ON */
select tech_id, customer_id, issue_main from [case] where customer_id = @.cid
--GO
RETURN
.................................................................................
im using asp.net, C#, and sql express
forgive me. im new to this .net
PLS HELP

forgive me. i found out that the codes above are just ok. i found out that one of my grid table is the one causing the problem - it is not passing a parameter. hehehe. my fault. im just human.

thanks to you all.

Parameter Number is Invalid

I am using Crystal Reports 8.0, with SQL Server.
I am trying to create a Report on a Stored Procedure.
My Stored Procedure is having 3 parameters (2 datetime parameters and 1 integer parameter).
When i select the stored procedure in the initial stage, it asks for the parameters. I tried entering 2050-01-01 00:00:00.000, etc etc ...

But I am getting an error "Parameter Number 1 is invalid" which is the same datetime field.

When I run the stored procedure in the query analyser. Its runs fine:
exec spTravelRpt_Person_chargeno '01-01-1990 00:00:00.000','01-01-2050 00:00:00.000',-1.

Can anyone plzzzz plzzz help me.. bcoz i am stuck.. i cannot move forward..

Ur help will be appreicated.Give only 2050-01-01 to datetime field and when calling the SP from your front end, pass the parameter in the format you want|||Hi Madhi,

I tried giving that too ... just
2050-01-01 ...
but it didnt help me...
Its giving the same error:
Parameter number 1 is invalid.
What do i do ????|||change the parameters datatype to varchar(20) instead of datetime and check.sql

Parameter multi-value problem when using a stored procedure

Hi folks,
I am describing the issue as below:
1. create a stored procedure as below
....where age in (@.p_age)
note: age is the table coumn of table table1 with datatype tinyint
2. .... and create a second dataset for parameter @.p_age
select distinct age from table1
3. associate the parameter...run it
4. There is no problem with single value. But when two ages are selected,
I got error message, "...Erro convert data type nvarchar to tinyint"
Please advise. PeterYou cannot pass and use multi-value parameters to a stored procedure and use
it in a query as you have. If that query was in RS itself then it would
work. This is not a RS thing, it is a SQL Server stored procedure issue.
Just try it from Query Analyzer and you will see what I mean. I do the
following, I create
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:74D9A9F0-B1F9-4925-8080-87FC99215462@.microsoft.com...
> Hi folks,
> I am describing the issue as below:
> 1. create a stored procedure as below
> ....where age in (@.p_age)
> note: age is the table coumn of table table1 with datatype tinyint
> 2. .... and create a second dataset for parameter @.p_age
> select distinct age from table1
> 3. associate the parameter...run it
> 4. There is no problem with single value. But when two ages are selected,
> I got error message, "...Erro convert data type nvarchar to tinyint"
> Please advise. Peter|||Try again, sent before done:
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:74D9A9F0-B1F9-4925-8080-87FC99215462@.microsoft.com...
> Hi folks,
> I am describing the issue as below:
> 1. create a stored procedure as below
> ....where age in (@.p_age)
> note: age is the table coumn of table table1 with datatype tinyint
> 2. .... and create a second dataset for parameter @.p_age
> select distinct age from table1
> 3. associate the parameter...run it
> 4. There is no problem with single value. But when two ages are selected,
> I got error message, "...Erro convert data type nvarchar to tinyint"
> Please advise. Peter

Parameter doesn''t get supplied to stored procedure

I'm rather new at reporting services, so bear with me on this one.

I have a stored procedure in MSSQL called prc_failedSLA_per_week, that has two parameters (@.startWeek and @.endWeek). I've added a dataset in reporting services which runs my procedure. when I run it from the data tab in RS, I get prompted for values on these two parameters, and a table gets generated. But if I try to preview my report, I get the error "Procedure of function "prc_failedSLA_per_week" expects parameter "@.startWeek", which was not supplied".

I've tried to find info on this in the help file to no avail, not even google helps me in any way. I guess it's some kind of newbie mistake, but I simply can't figure out what to do.

Any help would be greatly appreciated!
Nevermind, I got it solved. Seem that I had to press the refresh button in the dataset for the parameters to work, which wasn't mentioned anywhere that I looked. Unlogical, but now it works at least :-/
|||same thing happened to me. I actually thought that the code was messed up (code behind). So deleted my report and recreated another one. But then i refreshed my dataset over and over and now its workin!! THANKS!!!

Parameter doesn't get supplied to stored procedure

I'm rather new at reporting services, so bear with me on this one.

I have a stored procedure in MSSQL called prc_failedSLA_per_week, that has two parameters (@.startWeek and @.endWeek). I've added a dataset in reporting services which runs my procedure. when I run it from the data tab in RS, I get prompted for values on these two parameters, and a table gets generated. But if I try to preview my report, I get the error "Procedure of function "prc_failedSLA_per_week" expects parameter "@.startWeek", which was not supplied".

I've tried to find info on this in the help file to no avail, not even google helps me in any way. I guess it's some kind of newbie mistake, but I simply can't figure out what to do.

Any help would be greatly appreciated!
Nevermind, I got it solved. Seem that I had to press the refresh button in the dataset for the parameters to work, which wasn't mentioned anywhere that I looked. Unlogical, but now it works at least :-/
|||same thing happened to me. I actually thought that the code was messed up (code behind). So deleted my report and recreated another one. But then i refreshed my dataset over and over and now its workin!! THANKS!!!

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

Parameter Conversions Question from SQL Server

I have a question regarding a casting error I recieve when I explictly assign a value to a value in a stored procedure......
The value pstrLoginName is a public string defined in a code module
The stored procedure is NVARCHAR Datatypes....

Here is the Stored Procedure

CREATE procedure dbo.Appt_GetPermissions_NET
(
@.LoginName nvarchar(15),
@.Password NvarChar(15),
@.DeleteScan bit Output

)
as
select

@.DeleteScan=UserP_DeleteScan
from
Clinic_Users
where
UserName = @.LoginName
and
UserPassword = @.Password

GO

Here is my error

Specified cast is not valid
Line 194: cmdsql.CommandType = CommandType.StoredProcedure
Line 195:
Line 196: parmLogin = cmdsql.Parameters.Add("@.LoginName", SqlDbType.NVarChar, 15).Value = pstrLoginName
Line 197: parmPassword = cmdsql.Parameters.Add("@.LoginPassword", SqlDbType.NVarChar, 15).Value = pstrLoginPassword
Line 198: parmDelete = cmdsql.Parameters.Add("@.DeleteScan", SqlDbType.Bit)

my vb .net code to execute this

Public Sub ObtainPermission()

'get the needed data
Dim consql As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmdsql As New SqlCommand
Dim parmLogin As SqlParameter
Dim parmPassword As SqlParameter
Dim parmDelete As SqlParameter

cmdsql = New SqlCommand("Appt_GetPermissions_NET", consql)
cmdsql.CommandType = CommandType.StoredProcedure

parmLogin = cmdsql.Parameters.Add("@.LoginName", SqlDbType.NVarChar, 15).Value = pstrLoginName <--error occurs here

parmPassword = cmdsql.Parameters.Add("@.LoginPassword", SqlDbType.NVarChar, 15).Value = pstrLoginPassword <--I'm sure it will happen here then
parmDelete = cmdsql.Parameters.Add("@.DeleteScan", SqlDbType.Bit)
parmDelete.Direction = ParameterDirection.Output

consql.Open()
cmdsql.ExecuteNonQuery()

'obtain rights

pstrPermissions = cmdsql.Parameters("@.DeleteScan").Value
consql.Close()

End SubDude, your doing too much on one line!
If you are going to do all this on one line you'll have to add some paranthesis:
(parmLogin = cmdsql.Parameters.Add("@.LoginName", SqlDbType.NVarChar, 15)).Value = pstrLoginName

I'd recommend splitting the row in two lines for better readability.|||You have both this line:

Dim cmdsql As New SqlCommand

and this line:

cmdsql = New SqlCommand("Appt_GetPermissions_NET", consql)

Note that you have used "New" twice. Remove the "New" from the first line and you should have better luck.
Terri|||Ofcourse it's wrong to allocate an object twice,
but it shouldn't raise an error, since he hasn't used the variable
before allocating it the second time.
Won't it just 'leak' an object instance?sql

Parameter

Knowing the parameter used in a stored procedure how can I find out the stored procedure's name?create procedure test @.id int,@.id2 int
as
select @.id
go
select * from sysobjects where id in(
select id from syscolumns where name='@.id2')

Parallelism and performance

Hi,
We have a stored procedure that runs slow when the execution plan uses
parallelism vs. if does not.
My Question is when is it helpful to have parallelism and when not. Also is
it advisable to configure the sql server to not use parallelism at all.
Thanks
RahulSometimes SQL Server just creates parallel plans which do not perform well, although this seems to
be less and less of an issue with each release (and service pack). You can disable parallelism by:
MAXDOP 1 optimizer hint in the query
sp_configure and set "max degree of parallelism" to 1, will affect all queries
The "cost threshold for parallelism" affects the threshold for when parallel plans are considered
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message news:e0QcAwyOHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hi,
> We have a stored procedure that runs slow when the execution plan uses parallelism vs. if does
> not.
> My Question is when is it helpful to have parallelism and when not. Also is it advisable to
> configure the sql server to not use parallelism at all.
> Thanks
> Rahul
>|||On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Sometimes SQL Server just creates parallel plans which do not perform well, although this seems to
>be less and less of an issue with each release (and service pack). You can disable parallelism by:
>MAXDOP 1 optimizer hint in the query
>sp_configure and set "max degree of parallelism" to 1, will affect all queries
>The "cost threshold for parallelism" affects the threshold for when parallel plans are considered
Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
a box with four processors.
I think that set a new personal best for me!
J.|||Sounds like a query that needs optimization:).
--
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:ovi0r2tt33p1do0ialubbtbn5ujlfua75a@.4ax.com...
> On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>Sometimes SQL Server just creates parallel plans which do not perform
>>well, although this seems to
>>be less and less of an issue with each release (and service pack). You can
>>disable parallelism by:
>>MAXDOP 1 optimizer hint in the query
>>sp_configure and set "max degree of parallelism" to 1, will affect all
>>queries
>>The "cost threshold for parallelism" affects the threshold for when
>>parallel plans are considered
> Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
> a box with four processors.
> I think that set a new personal best for me!
> J.
>|||On Fri, 19 Jan 2007 09:50:24 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Sounds like a query that needs optimization:).
There must have been at least 21 rows to check!
J.sql

Parallelism and performance

Hi,
We have a stored procedure that runs slow when the execution plan uses
parallelism vs. if does not.
My Question is when is it helpful to have parallelism and when not. Also is
it advisable to configure the sql server to not use parallelism at all.
Thanks
Rahul
On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Sometimes SQL Server just creates parallel plans which do not perform well, although this seems to
>be less and less of an issue with each release (and service pack). You can disable parallelism by:
>MAXDOP 1 optimizer hint in the query
>sp_configure and set "max degree of parallelism" to 1, will affect all queries
>The "cost threshold for parallelism" affects the threshold for when parallel plans are considered
Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
a box with four processors.
I think that set a new personal best for me!
J.
|||Sounds like a query that needs optimization.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:ovi0r2tt33p1do0ialubbtbn5ujlfua75a@.4ax.com...
> On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
> a box with four processors.
> I think that set a new personal best for me!
> J.
>
|||On Fri, 19 Jan 2007 09:50:24 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>Sounds like a query that needs optimization.
There must have been at least 21 rows to check!
J.

Parallelism and performance

Hi,
We have a stored procedure that runs slow when the execution plan uses
parallelism vs. if does not.
My Question is when is it helpful to have parallelism and when not. Also is
it advisable to configure the sql server to not use parallelism at all.
Thanks
RahulSometimes SQL Server just creates parallel plans which do not perform well,
although this seems to
be less and less of an issue with each release (and service pack). You can d
isable parallelism by:
MAXDOP 1 optimizer hint in the query
sp_configure and set "max degree of parallelism" to 1, will affect all queri
es
The "cost threshold for parallelism" affects the threshold for when parallel
plans are considered
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rahul" <reach_aggarwal@.hotmail.com> wrote in message news:e0QcAwyOHHA.1248@.TK2MSFTNGP02.phx
.gbl...
> Hi,
> We have a stored procedure that runs slow when the execution plan uses par
allelism vs. if does
> not.
> My Question is when is it helpful to have parallelism and when not. Also i
s it advisable to
> configure the sql server to not use parallelism at all.
> Thanks
> Rahul
>|||On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Sometimes SQL Server just creates parallel plans which do not perform well,
although this seems to
>be less and less of an issue with each release (and service pack). You can
disable parallelism by:
>MAXDOP 1 optimizer hint in the query
>sp_configure and set "max degree of parallelism" to 1, will affect all quer
ies
>The "cost threshold for parallelism" affects the threshold for when parallel plans
are considered
Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
a box with four processors.
I think that set a new personal best for me!
J.|||Sounds like a query that needs optimization.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:ovi0r2tt33p1do0ialubbtbn5ujlfua75a@.
4ax.com...
> On Thu, 18 Jan 2007 19:18:26 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Hey Tibor, I had 21 copies of my SPID yesterday on a select query, on
> a box with four processors.
> I think that set a new personal best for me!
> J.
>|||On Fri, 19 Jan 2007 09:50:24 -0500, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:

>Sounds like a query that needs optimization.
There must have been at least 21 rows to check!
J.

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.

Paging query without using stored procedure

Hello, my table is clustered according to the date. Has anyone found an efficient way to page through 16 million rows of data? The query that I have takes waaaay too long. It is really fast when I page through information at the beginning but when someone tries to access the 9,000th page sometimes it has a timeout error. I am using sql server 2005 let me know if you have any ideas. Thanks

I am also thinking about switch datavase software to something that can handle that many rows. Let me know if you have a suggestion on a particular software that can handle paging through 16 million rows of data.

Hi~

You may take a look at this:

Efficiently Paging Through Large Amounts of Data (VB)

Efficiently Paging Through Large Amounts of Data(C#)

Hope this helps.

Paging problems

I am using a stored procedure to page some objects
The procedure looks like this:

CREATE PROCEDURE sw20aut.sw20_Kon
( @.sid_nr INT, @.sid_stl INT = 35, @.kid int )
AS BEGIN
SET NOCOUNT ON
DECLARE
@.rader INT, @.sid_antal INT, @.ubound int, @.lbound int

SELECT
@.rader = COUNT(*),
@.sid_antal = COUNT(*) / @.sid_stl
FROM
sw20aut.sw_kontakter WITH (NOLOCK)
WHERE
kund_id = @.kid AND del = '0'

IF @.rader % @.sid_stl != 0 SET @.sid_antal = @.sid_antal + 1
IF @.sid_nr < 1 SET @.sid_nr = 1
IF @.sid_nr > @.sid_antal SET @.sid_nr = @.sid_antal

SET @.ubound = @.sid_stl * @.sid_nr
IF(@.sid_antal > 0) SET @.lbound = @.ubound - (@.sid_stl - 1)
ELSE SET @.lbound = 0

SELECT
CurrentPage = @.sid_nr,
TotalPages = @.sid_antal,
TotalRows = @.rader

DECLARE @.ename VARCHAR(64), @.fname VARCHAR(64), @.konid VARCHAR(64)
SET ROWCOUNT @.lbound
SELECT @.ename = enamn, @.fname = fnamn, @.konid = kon_id FROM sw20aut.sw_kontakter WITH (NOLOCK)
WHERE kund_id = @.kid AND del = '0'
ORDER BY enamn, fnamn, kon_id
SET ROWCOUNT @.sid_stl
SELECT kon_id, enamn, fnamn FROM sw20aut.sw_kontakter WITH (NOLOCK)
WHERE enamn + fnamn + '~' + CAST(kon_id as VARCHAR(64)) >= @.ename + @.fname + '~' + @.konid AND (kund_id = @.kid AND del = '0')
ORDER BY enamn, fnamn, kon_id
SELECT startid = @.konid
SET ROWCOUNT 0
END

The big problem is that i need to display objet with the same name. In my book the best identifier is the PK and therefor i have sorted as above by ordering after LastName, FirstName, ContactId

After som thinking ive reached the conclusion that this dont work if the idnumbers isnt of the same length. as long as they are(for example two people named John Smith, one with id = '23' and one with id = '87' it works. If there ids would have been '23' and '1203' it will not work correctly) of the same length it works fine.

What im wondering is if anyone have a good solution to this? Only thing i can think of is filling all idnumbers with zeros to equal length. Dont know how and if this will affect performance though. Anyone has a practical solution to this?

Sorry I'm not clear what you want to do in the stored procedure, it may help to understand your issue if you can post some sample data and expecting result returned by the stored procedure. For paging result sets, if you're using SQL2005/Express you can try the new?Ranking functions