Showing posts with label procedure. Show all posts
Showing posts with label procedure. Show all posts

Wednesday, March 28, 2012

Problems with Stored Procedure Call

I have developed an application that interfaces with SQL 2000 via stored procedure calls. The execution time of a particular stored procedure takes approximately 2 seconds on average, but we see spikes every so often up to 150 ~ 200 seconds. The timing of the spikes are erratic and do not correlate with any blocking or maintenance job runs. We have reviewed the stored procedure and optimized it to the nth degree and still no improvement. Any suggestions on how I can track down what is causing the spikes?

index fragmentation

database statictics

disk i/o

hope this helps,

Derek

sql

Problems with stored procedure

I'm getting a strange problem with a stored procedure. The SP returns results for use as part of a simple site search and is based on the search at the top of this site: http://www.stayinwales.co.uk

There are only two parameters passed to the SP. The problem is slightly complicated to explain fully but essentially what's happening is that the second and third IF statements do not return any values and I've no idea why, as the SELECT statement is almost identical in all four.

ALTER PROCEDURE developers.fort_GetSearchResults
(
@.propertyType int,
@.county int
)
AS
IF ((@.propertyType = 0) AND (@.county = 0))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps,
Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
ORDER BY NEWID()
ELSE
IF (@.propertyType = 0)
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @.propertyType)
ELSE
IF (@.county = 0)
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode,
Experimental_ContactDetails.telephone, Experimental_ContactDetails.fax,
Experimental_ContactDetails.email, Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps,
Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_ContactDetails.county = @.county)
ORDER BY NEWID()
ELSE
IF ((@.propertyType != 0) AND (@.county != 0))
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName, Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription, Experimental_Accommodation.description,
Experimental_Property_Type.name AS Expr1, Experimental_Grading.grading,
Experimental_Images.filename, Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @.propertyType)
AND (Experimental_ContactDetails.county = @.county)
ORDER BY NEWID()
RETURN

Condition 2 and 3 may have some no inclusives here:

1. ((@.propertyType = 0) AND (@.county = 0))

2. ((@.propertyType = 0) AND (@.county != 0))

3. ((@.propertyType != 0) AND (@.county = 0))

4. ((@.propertyType != 0) AND (@.county != 0))

Could you try these conditions and check SQL statement with hard coded value whether they are returning results/

From your script, my guess is:

1. ((@.propertyType = 0) AND (@.county = 0))

you don't use your parameters' value in your sql

2. ((@.propertyType = 0) AND (@.county != 0))

you may need (@.county value in your sql instead of @.propertyType

3. ((@.propertyType != 0) AND (@.county = 0))

you may need (@.propretyType value in your sql instead of @.county

4. ((@.propertyType != 0) AND (@.county != 0))

you use both parameters' values here.

Let us know if you get this work.

Limno

|||

IF(@.propertyType = 0)
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @.propertyType)

Do you have any propertyTypes that are 0?

|||

Motley wrote:

IF(@.propertyType = 0)
SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name,
Experimental_ContactDetails.address, Experimental_ContactDetails.district,
Experimental_ContactDetails.town, Experimental_Counties.County,
Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone,
Experimental_ContactDetails.fax, Experimental_ContactDetails.email,
Experimental_ContactDetails.contactName,
Experimental_Accommodation.gradingDescription,
Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing,
Experimental_Accommodation.shortDescription,
Experimental_Accommodation.description, Experimental_Property_Type.name
AS Expr1, Experimental_Grading.grading, Experimental_Images.filename,
Experimental_Images.alt
FROM Experimental_Accommodation
INNER JOIN Experimental_ContactDetails
ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id
INNER JOIN Experimental_Counties
ON Experimental_ContactDetails.county = Experimental_Counties.id
INNER JOIN Experimental_Property_Type
ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id
INNER JOIN Experimental_Grading
ON Experimental_Accommodation.grading = Experimental_Grading.id
INNER JOIN Experimental_Images
ON Experimental_Grading.iconId = Experimental_Images.id
WHERE (Experimental_Accommodation.propertyType = @.propertyType)

Do you have any propertyTypes that are 0?


Yes. The first item in each dropdown list have values of '0' - the dropdown lists are databound but the first items are then inserted programmatically and given the value '0'.

In any case, if there wasn't a propertyType of 0, the IF ((@.propertyType = 0) AND (@.county = 0)) line wouldn't work either, and it does. It's strange that the only SELECT statements that don't work are the ones with only a single parameter checked by the IF statement.

Problems with stored procedure

This is probably a stupid question but I cannot find the data. The code
following is in a stored procedure I am running in SS 2000. It executes, and
if I run it in Query Analyzer I see the results. I have the parameters
defined in the Parameters collection of an ADO command object and have the
direction defined as output (I've also tried 'adParamInputOutput') However,
the values of the parameter objects all contain NULL (except the first two
which are input parameters).
Where is the returned data? I have even have the execute method return a
resultset and checked the Fields collection on it. I have written quite a fe
w
stored procedures, but never any that returned data in output parameters. I
am beginning to see why.
========================================
========
CREATE PROCEDURE sp_hr_pay_register_get_summary_totals
@.PayDate smalldatetime,
@.Location nvarchar(5),
@.RegHours numeric(8,2) OUTPUT,
@.RegEarnings numeric(8,2) OUTPUT,
@.OTHours numeric(8,2) OUTPUT,
@.OTEarnings numeric(8,2) OUTPUT,
@.OtherHours numeric(8,2) OUTPUT,
@.OtherEarnings numeric(8,2) OUTPUT,
@.Taxes numeric(8,2) OUTPUT,
@.Deductions numeric(8,2) OUTPUT
AS
SELECT @.RegHours = SUM(ISNULL(P.Hours, 0))
FROM tblPayrollRegisterPayments P INNER JOIN
tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
P.PayTypeCode = 'regular pa'
SELECT @.OTHours = SUM(ISNULL(P.Hours, 0))
FROM tblPayrollRegisterPayments P INNER JOIN
tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
P.PayTypeCode = 'overtime'
SELECT @.OtherHours = SUM(ISNULL(P.Hours, 0))
FROM tblPayrollRegisterPayments P INNER JOIN
tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
P.PayTypeCode NOT IN ('regular pa', 'overtime')
SELECT @.RegEarnings = SUM(ISNULL(P.Amount, 0))
FROM tblPayrollRegisterPayments P INNER JOIN
tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
P.PayTypeCode = 'regular pa'
SELECT @.OTEarnings = SUM(ISNULL(P.Amount, 0))
FROM tblPayrollRegisterPayments P INNER JOIN
tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
P.PayTypeCode = 'overtime'
SELECT @.OtherEarnings = SUM(ISNULL(P.Amount, 0))
FROM tblPayrollRegisterPayments P INNER JOIN
tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
P.PayTypeCode NOT IN ('regular pa', 'overtime')
SELECT @.Taxes = SUM(ISNULL(Ded.Amount, 0))
FROM tblPayrollRegisterDeductions Ded INNER JOIN
tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID AND
Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID INNER
JOIN
tblDeductionCodes DC ON Ded.DeductionCode =
DC.DeductionCode
WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'TAX') AND
(Ded.PayPeriodEndingDate = @.PayDate)
SELECT @.Deductions = SUM(ISNULL(Ded.Amount, 0))
FROM tblPayrollRegisterDeductions Ded INNER JOIN
tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID AND
Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
tblDepartments D ON E.DepartmentCode = D.DeptID INNER
JOIN
tblDeductionCodes DC ON Ded.DeductionCode =
DC.DeductionCode
WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'DED') AND
(Ded.PayPeriodEndingDate = @.PayDate)
-- SELECT @.RegHours, @.RegEarnings, @.OTHours, @.OTEarnings, @.OtherHours,
@.OtherEarnings, @.Taxes, @.Deductions
GOSuggest you put
SET NOCOUNT ON
at the start of the stored procedure. There is a known ADO issue with
this.|||Did not make any difference.
"markc600@.hotmail.com" wrote:

> Suggest you put
> SET NOCOUNT ON
> at the start of the stored procedure. There is a known ADO issue with
> this.
>|||You didn't show your ASP code but that seems to be where the problem is
(since it works in QA, the problem clearly isn't the SQL). Have a look at
http://www.aspfaq.com/params.htm for a walk-through of using output
parameters from ASP.
That said.
You should never use sp_ prefix on user-defined stored procedures.
And if you think you are being more efficient by using OUTPUT parameters and
changing a SELECT query into a dozen different queries, you should test both
versions. All told, I am betting it will be more efficient to just run a
single GROUP BY query and return a result set to ASP.
"Doug" <dbb211-hd204@.yahoo.com> wrote in message
news:D74A5285-C542-41C5-AF5C-C2F2A59A7ABE@.microsoft.com...
> This is probably a stupid question but I cannot find the data. The code
> following is in a stored procedure I am running in SS 2000. It executes,
> and
> if I run it in Query Analyzer I see the results. I have the parameters
> defined in the Parameters collection of an ADO command object and have the
> direction defined as output (I've also tried 'adParamInputOutput')
> However,
> the values of the parameter objects all contain NULL (except the first two
> which are input parameters).
> Where is the returned data? I have even have the execute method return a
> resultset and checked the Fields collection on it. I have written quite a
> few
> stored procedures, but never any that returned data in output parameters.
> I
> am beginning to see why.
> ========================================
========
>
> CREATE PROCEDURE sp_hr_pay_register_get_summary_totals
> @.PayDate smalldatetime,
> @.Location nvarchar(5),
> @.RegHours numeric(8,2) OUTPUT,
> @.RegEarnings numeric(8,2) OUTPUT,
> @.OTHours numeric(8,2) OUTPUT,
> @.OTEarnings numeric(8,2) OUTPUT,
> @.OtherHours numeric(8,2) OUTPUT,
> @.OtherEarnings numeric(8,2) OUTPUT,
> @.Taxes numeric(8,2) OUTPUT,
> @.Deductions numeric(8,2) OUTPUT
> AS
> SELECT @.RegHours = SUM(ISNULL(P.Hours, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location
> AND
> P.PayTypeCode = 'regular pa'
> SELECT @.OTHours = SUM(ISNULL(P.Hours, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location
> AND
> P.PayTypeCode = 'overtime'
> SELECT @.OtherHours = SUM(ISNULL(P.Hours, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location
> AND
> P.PayTypeCode NOT IN ('regular pa', 'overtime')
> SELECT @.RegEarnings = SUM(ISNULL(P.Amount, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location
> AND
> P.PayTypeCode = 'regular pa'
> SELECT @.OTEarnings = SUM(ISNULL(P.Amount, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location
> AND
> P.PayTypeCode = 'overtime'
> SELECT @.OtherEarnings = SUM(ISNULL(P.Amount, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location
> AND
> P.PayTypeCode NOT IN ('regular pa', 'overtime')
> SELECT @.Taxes = SUM(ISNULL(Ded.Amount, 0))
> FROM tblPayrollRegisterDeductions Ded INNER JOIN
> tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID
> AND
> Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID INNER
> JOIN
> tblDeductionCodes DC ON Ded.DeductionCode =
> DC.DeductionCode
> WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'TAX') AND
> (Ded.PayPeriodEndingDate = @.PayDate)
>
> SELECT @.Deductions = SUM(ISNULL(Ded.Amount, 0))
> FROM tblPayrollRegisterDeductions Ded INNER JOIN
> tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID
> AND
> Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID INNER
> JOIN
> tblDeductionCodes DC ON Ded.DeductionCode =
> DC.DeductionCode
> WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'DED') AND
> (Ded.PayPeriodEndingDate = @.PayDate)
> -- SELECT @.RegHours, @.RegEarnings, @.OTHours, @.OTEarnings, @.OtherHours,
> @.OtherEarnings, @.Taxes, @.Deductions
> GO
>|||The program is running in VB6, not ASP. If you look at the code, you will
realize there are several different conditions in the WHERE clauses. Some ar
e
testing on PayType code, joining one set of tables, others are testing on
DeductionType, joining another set of tables. There are three different test
conditions on PayTypeCode and two different test conditions on DeductionType
.
That aside, because it has nothing to do with the problem, I am using
standard ADO within VB. I have a class that I developed 5-6 years ago that
encapsulates all the functionality of ADO with methods to set properties, ad
d
parameters to an ADOCommand object, set the values of the parameters, get th
e
values, work with a resultset if one is returned, handle errors, etc. The
basic structure is to create a parameter object, set its properties and
append it to the Parameters collection. It has worked stable for years on
hundreds of stored procedures so I do not suspect it. As I stated, I have
tried this with the data direction set to both Output and InputOutput. I am
really flustered by this.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You didn't show your ASP code but that seems to be where the problem is
> (since it works in QA, the problem clearly isn't the SQL). Have a look at
> http://www.aspfaq.com/params.htm for a walk-through of using output
> parameters from ASP.
> That said.
> You should never use sp_ prefix on user-defined stored procedures.
> And if you think you are being more efficient by using OUTPUT parameters a
nd
> changing a SELECT query into a dozen different queries, you should test bo
th
> versions. All told, I am betting it will be more efficient to just run a
> single GROUP BY query and return a result set to ASP.
>|||So what is different between this stored procedure and the hundreds of
others that do work? You might use profiler to make sure it is getting
executed and you might also write a version that just returns fixed values
for the output parameters to see if it's a problem with the parameters or
with the stored procedure logic. When I hear it works in QA and not in the
code, it usually means either the parameters are different between the code
and QA or there's a permissions issue. Since rowcount doesn't affect your
execution, your magic classes must be receiving the rowcount so make sure
it's the same in your code as it is in QA.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Doug" <dbb211-hd204@.yahoo.com> wrote in message
news:27131BEA-1D7C-4C19-A3AD-01263BAEEA63@.microsoft.com...
> The program is running in VB6, not ASP. If you look at the code, you will
> realize there are several different conditions in the WHERE clauses. Some
> are
> testing on PayType code, joining one set of tables, others are testing on
> DeductionType, joining another set of tables. There are three different
> test
> conditions on PayTypeCode and two different test conditions on
> DeductionType.
> That aside, because it has nothing to do with the problem, I am using
> standard ADO within VB. I have a class that I developed 5-6 years ago that
> encapsulates all the functionality of ADO with methods to set properties,
> add
> parameters to an ADOCommand object, set the values of the parameters, get
> the
> values, work with a resultset if one is returned, handle errors, etc. The
> basic structure is to create a parameter object, set its properties and
> append it to the Parameters collection. It has worked stable for years on
> hundreds of stored procedures so I do not suspect it. As I stated, I have
> tried this with the data direction set to both Output and InputOutput. I
> am
> really flustered by this.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>|||There is some text cut and pasted from VB6 help below but note, definitely
put SET NOCOUNT ON at the top of your stored procedure and definitely close
your recordset in order to retreive your output values. If you're still
having problems after that, break it down. Create a simple stored procedure
with one OUTPUT parameter, create a simple VB ADO project to access it,
forget your class. See if that works.
Let me know how you get on.
Damien
The output and return parameter can also be specified through the Parameter
objects, but the actual value for both parameters is not returned until the
data of the Recordset object has been completely fetched out or the Recordse
t
has been closed.
The ADO code is:
Dim Cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim param As Parameter
Cmd.ActiveConnection = "DSN=pubs;UID=sa"
Cmd.CommandText = "myproc"
Cmd.CommandType = adCmdStoredProc
' Set up parameters.
Set param = Cmd.CreateParameter("Return", adInteger, adParamReturnValue, , 0
)
Cmd.Parameters.Append param
Set param = Cmd.CreateParameter("Output", adInteger, adParamOutput, , 0)
Cmd.Parameters.Append param
Set rs = Cmd.Execute
If Not rs.EOF And Not rs.BOF Then
Debug.Print rs(0)
rs.Close
End If
Debug.Print Cmd(0) ' The return code
Debug.Print Cmd(1) ' The Output parameter
"Doug" wrote:

> This is probably a stupid question but I cannot find the data. The code
> following is in a stored procedure I am running in SS 2000. It executes, a
nd
> if I run it in Query Analyzer I see the results. I have the parameters
> defined in the Parameters collection of an ADO command object and have the
> direction defined as output (I've also tried 'adParamInputOutput') However
,
> the values of the parameter objects all contain NULL (except the first two
> which are input parameters).
> Where is the returned data? I have even have the execute method return a
> resultset and checked the Fields collection on it. I have written quite a
few
> stored procedures, but never any that returned data in output parameters.
I
> am beginning to see why.
> ========================================
========
>
> CREATE PROCEDURE sp_hr_pay_register_get_summary_totals
> @.PayDate smalldatetime,
> @.Location nvarchar(5),
> @.RegHours numeric(8,2) OUTPUT,
> @.RegEarnings numeric(8,2) OUTPUT,
> @.OTHours numeric(8,2) OUTPUT,
> @.OTEarnings numeric(8,2) OUTPUT,
> @.OtherHours numeric(8,2) OUTPUT,
> @.OtherEarnings numeric(8,2) OUTPUT,
> @.Taxes numeric(8,2) OUTPUT,
> @.Deductions numeric(8,2) OUTPUT
> AS
> SELECT @.RegHours = SUM(ISNULL(P.Hours, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AN
D
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AN
D
> P.PayTypeCode = 'regular pa'
> SELECT @.OTHours = SUM(ISNULL(P.Hours, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AN
D
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AN
D
> P.PayTypeCode = 'overtime'
> SELECT @.OtherHours = SUM(ISNULL(P.Hours, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AN
D
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AN
D
> P.PayTypeCode NOT IN ('regular pa', 'overtime')
> SELECT @.RegEarnings = SUM(ISNULL(P.Amount, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AN
D
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AN
D
> P.PayTypeCode = 'regular pa'
> SELECT @.OTEarnings = SUM(ISNULL(P.Amount, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AN
D
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AN
D
> P.PayTypeCode = 'overtime'
> SELECT @.OtherEarnings = SUM(ISNULL(P.Amount, 0))
> FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AN
D
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
> WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AN
D
> P.PayTypeCode NOT IN ('regular pa', 'overtime')
> SELECT @.Taxes = SUM(ISNULL(Ded.Amount, 0))
> FROM tblPayrollRegisterDeductions Ded INNER JOIN
> tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID
AND
> Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNE
R
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID INNE
R
> JOIN
> tblDeductionCodes DC ON Ded.DeductionCode =
> DC.DeductionCode
> WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'TAX') AND
> (Ded.PayPeriodEndingDate = @.PayDate)
>
> SELECT @.Deductions = SUM(ISNULL(Ded.Amount, 0))
> FROM tblPayrollRegisterDeductions Ded INNER JOIN
> tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID
AND
> Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNE
R
> JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID INNE
R
> JOIN
> tblDeductionCodes DC ON Ded.DeductionCode =
> DC.DeductionCode
> WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'DED') AND
> (Ded.PayPeriodEndingDate = @.PayDate)
> -- SELECT @.RegHours, @.RegEarnings, @.OTHours, @.OTEarnings, @.OtherHours,
> @.OtherEarnings, @.Taxes, @.Deductions
> GO
>|||Sometimes the simplest solution is to take a day off it and come back
tomorrow with a fresh mind. Actually, it had nothing to do with SQL or VB,
but the value of one of the parameters being passed to the stored procedure.
When I executed it in QA, I hardcoded the date, but when executed in code,
the date is actually a calculated date. Since a new pay period had cycled
since I last loaded date, the date was now invalid. Actually, it was valid,
there just was no data for it, so it was, in fact, returning the correct
values. :-(
Doug
p.s. Thanks to everyone who offered suggestions and possible solutions.
"Damien" wrote:
> There is some text cut and pasted from VB6 help below but note, definitely
> put SET NOCOUNT ON at the top of your stored procedure and definitely clos
e
> your recordset in order to retreive your output values. If you're still
> having problems after that, break it down. Create a simple stored procedu
re
> with one OUTPUT parameter, create a simple VB ADO project to access it,
> forget your class. See if that works.
> Let me know how you get on.
>
> Damien
> The output and return parameter can also be specified through the Paramete
r
> objects, but the actual value for both parameters is not returned until th
e
> data of the Recordset object has been completely fetched out or the Record
set
> has been closed.
> The ADO code is:
> Dim Cmd As New ADODB.Command
> Dim rs As New ADODB.Recordset
> Dim param As Parameter
> Cmd.ActiveConnection = "DSN=pubs;UID=sa"
> Cmd.CommandText = "myproc"
> Cmd.CommandType = adCmdStoredProc
> ' Set up parameters.
> Set param = Cmd.CreateParameter("Return", adInteger, adParamReturnValue, ,
0)
> Cmd.Parameters.Append param
> Set param = Cmd.CreateParameter("Output", adInteger, adParamOutput, , 0)
> Cmd.Parameters.Append param
> Set rs = Cmd.Execute
> If Not rs.EOF And Not rs.BOF Then
> Debug.Print rs(0)
> rs.Close
> End If
> Debug.Print Cmd(0) ' The return code
> Debug.Print Cmd(1) ' The Output parameter
>
> "Doug" wrote:
>|||On Fri, 10 Mar 2006 12:40:27 -0800, Doug wrote:

>CREATE PROCEDURE sp_hr_pay_register_get_summary_totals
(snip)
Hi Doug,
I know that this is not what you're asking about, but do you realise
that you could rewrite your stored procedure to make it much more
efficient?
First: rename it. The prefix "sp_" on stored procedures is reserved for
system stored procedures. On encountering this prefix, SQL Server will
first check the master DB for a stored procedure of that name and run it
instead of yours. In the best case, you take a performance hit. In the
worst case, Microsoft adds a system stored procedure with the exact same
name to the master DB with the next service pack and your code suddenly
starts acting _very_ funny!
The code below, consisting of six SELECT statements, can be combined
into the one SELECT statement I'll give below:
(snip)
>SELECT @.RegHours = SUM(ISNULL(P.Hours, 0))
>FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER J
OIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
>WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
>P.PayTypeCode = 'regular pa'
>SELECT @.OTHours = SUM(ISNULL(P.Hours, 0))
>FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER J
OIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
>WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
>P.PayTypeCode = 'overtime'
>SELECT @.OtherHours = SUM(ISNULL(P.Hours, 0))
>FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER J
OIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
>WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
>P.PayTypeCode NOT IN ('regular pa', 'overtime')
>SELECT @.RegEarnings = SUM(ISNULL(P.Amount, 0))
>FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER J
OIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
>WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
>P.PayTypeCode = 'regular pa'
>SELECT @.OTEarnings = SUM(ISNULL(P.Amount, 0))
>FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER J
OIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
>WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
>P.PayTypeCode = 'overtime'
>SELECT @.OtherEarnings = SUM(ISNULL(P.Amount, 0))
>FROM tblPayrollRegisterPayments P INNER JOIN
> tblPayrollRegisterEmployee E ON P.EmpID = E.EmpID AND
> P.PayPeriodEndingDate = E.PayPeriodEndingDate INNER J
OIN
> tblDepartments D ON E.DepartmentCode = D.DeptID
>WHERE P.PayPeriodEndingDate = @.PayDate AND D.DeptLocation = @.Location AND
>P.PayTypeCode NOT IN ('regular pa', 'overtime')
SELECT @.RegHours = SUM(CASE WHEN P.PayTypeCode = 'regular pa'
THEN ISNULL(P.Hours, 0) ELSE 0 END),
@.OTHours = SUM(CASE WHEN P.PayTypeCode = 'overtime'
THEN ISNULL(P.Hours, 0) ELSE 0 END),
@.OtherHours = SUM(CASE WHEN P.PayTypeCode NOT IN ('regular
pa', 'overtime')
THEN ISNULL(P.Hours, 0) ELSE 0 END),
@.RegEarnings = SUM(CASE WHEN P.PayTypeCode = 'regular pa'
THEN ISNULL(P.Amount, 0) ELSE 0 END),
@.OTEarnings = SUM(CASE WHEN P.PayTypeCode = 'overtime'
THEN ISNULL(P.Amount, 0) ELSE 0 END),
@.OtherEarnings = SUM(CASE WHEN P.PayTypeCode NOT IN ('regular
pa', 'overtime')
THEN ISNULL(P.Hours, 0) ELSE 0 END)
FROM tblPayrollRegisterPayments AS P
INNER JOIN tblPayrollRegisterEmployee AS E
ON P.EmpID = E.EmpID
AND P.PayPeriodEndingDate = E.PayPeriodEndingDate
INNER JOIN tblDepartments AS D
ON E.DepartmentCode = D.DeptID
WHERE P.PayPeriodEndingDate = @.PayDate
AND D.DeptLocation = @.Location
And in a similar vein, the last two queries can also be combined into
one:

>SELECT @.Taxes = SUM(ISNULL(Ded.Amount, 0))
>FROM tblPayrollRegisterDeductions Ded INNER JOIN
> tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID A
ND
> Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
>JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID INNER
>JOIN
> tblDeductionCodes DC ON Ded.DeductionCode =
>DC.DeductionCode
>WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'TAX') AND
> (Ded.PayPeriodEndingDate = @.PayDate)
>
>SELECT @.Deductions = SUM(ISNULL(Ded.Amount, 0))
>FROM tblPayrollRegisterDeductions Ded INNER JOIN
> tblPayrollRegisterEmployee E ON Ded.EmpID = E.EmpID A
ND
> Ded.PayPeriodEndingDate = E.PayPeriodEndingDate INNER
>JOIN
> tblDepartments D ON E.DepartmentCode = D.DeptID INNER
>JOIN
> tblDeductionCodes DC ON Ded.DeductionCode =
>DC.DeductionCode
>WHERE (D.DeptLocation = @.Location) AND (DC.DeductionType = 'DED') AND
> (Ded.PayPeriodEndingDate = @.PayDate)
SELECT @.Taxes = SUM(CASE WHEN DC.DeductionType = 'TAX'
THEN ISNULL(Ded.Amount, 0) ELSE 0 END),
@.Deductions = SUM(CASE WHEN DC.DeductionType = 'DED'
THEN ISNULL(Ded.Amount, 0) ELSE 0 END)
FROM tblPayrollRegisterDeductions AS Ded
INNER JOIN tblPayrollRegisterEmployee AS E
ON Ded.EmpID = E.EmpID
AND Ded.PayPeriodEndingDate = E.PayPeriodEndingDate
INNER JOIN tblDepartments AS D
ON E.DepartmentCode = D.DeptID
INNER JOIN tblDeductionCodes AS DC
ON Ded.DeductionCode = DC.DeductionCode
WHERE D.DeptLocation = @.Location
AND Ded.PayPeriodEndingDate = @.PayDate
Note that these queries are untested. Check out www.aspfaq.com/5006 if
you prefer a tested reply.
Hugo Kornelis, SQL Server MVP

Problems with SQL7 replication...

Trying to remove replication i get the error message..
Error 2812: Could not find stored procedure 'distribution.dbo.MSdrop_snapshot_dirs
Any idea ?
Thanks.
Andrew Ratley MCDBA.Details as such:
-What is the replication topology
- Number of servers involved
- How many servers involved?
- All the servers are in same domain?
- Level of service pack on involved SQL servers?|||The service pack should be 4 on all machines.

They are all in the same domain ..

1 box is SQL 7 server the other is using desktop edition.

Someone has removed parts of the replication without tryint to do it via the wizard so bits might be missing i think.

The replication used was transactional when i last saw it. And was working fine i think it stoped working and the network admin started to mess around..

What i want to be able to do is remove it so its in a fit state to reestablish replication on the database.|||Try with sp_removedbreplication from query analyzer.
Refer to books online for more information.|||I tried creating a new distribution database but this also would not remove and did not contain the stored procedure.

So on another server i installed replication and then used the stored procedure i found under there i created it on the 2 distribution databases. The replication removed itself fine and the new setup seems to be running ok.

Thanks for the help

Andrew.

Monday, March 26, 2012

Problems with sp_helpdb stored procedure

Hi,
One of our customers claims that the sp_helpdb truncates database name
larger than 24 characters, the release is 7.00.1094, did any one
encounter such a problem ?"Eyal Goren" <eyal_goren@.bmc.com> wrote in message
news:88eabf1e.0404240956.2861e16c@.posting.google.c om...
> Hi,
> One of our customers claims that the sp_helpdb truncates database name
> larger than 24 characters, the release is 7.00.1094, did any one
> encounter such a problem ?

Probably easy enough to determine.

sp_helptext sp_helpdb

And they can read the text for themselves.

I don't see anything limiting to 24 characters at a quick pass. The user
defined datatype of sysname should be limited to 128 characters.

Perhaps someone changed it?|||Eyal Goren (eyal_goren@.bmc.com) writes:
> One of our customers claims that the sp_helpdb truncates database name
> larger than 24 characters, the release is 7.00.1094, did any one
> encounter such a problem ?

Yes, I was able to repeat this problem on an SQL7 server. It turns
out that in the final SELECT, there is an explicit substring():

select name = substring(d.name, 1, 24),
db_size = str(sum(convert(dec(15),v.size))*
(select low from master.dbo.spt_values
where type = 'E' and number = 1)
/ 1048576,10,2)+ ' MB',
owner = substring(suser_sname(d.sid), 1, 24),
dbid = d.dbid,
created = convert(char(11), d.crdate),
status = s.dbdesc
from master.dbo.sysdatabases d, #spfiledesc v, #spdbdesc s
where d.dbid = s.dbid
and s.dbid = v.dbid
group by d.name,d.sid,d.dbid,d.crdate,s.dbdesc
order by d.name

If you are really venturesome you could change the procedure yourself,
but note that this is definitely not supported.

This problem does not appear in SQL2000.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Problems with simple procedure

<deleted>Hello,

the price field in the table on_sale is missing ..

11 UPDATE on_sale
12 SET price = price * 0.9
13 WHERE CURRENT OF item_cur;

you update the table on_sale and multilpe the field in on_sale with 9 ?

Hope that helps ?

Best regards
Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.com

Originally posted by appoooh
I'm having problems getting a simple procedure to compile, could someone possibly show me where I've gone wrong?

TIA

SQL> CREATE OR REPLACE PROCEDURE update_price
2 (p_cutoff IN CHAR)
3 IS
4 CURSOR item_cur IS
5 SELECT items.price, on_sale.start_date
6 FROM items, on_sale
7 WHERE on_sale.start_date = TO_DATE(p_cutoff)
8 FOR UPDATE;
9 BEGIN
10 FOR item_rec IN item_cur LOOP
11 UPDATE on_sale
12 SET price = price * 0.9
13 WHERE CURRENT OF item_cur;
14 END LOOP;
15 COMMIT;
16 END;
17 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE UPDATE_PRICE:

LINE/COL ERROR
--- -------------------
11/9 PL/SQL: SQL Statement ignored
12/17 PLS-00417: unable to resolve "PRICE" as a column

These are the tables I'm working with.

CREATE TABLE ITEMS
(id NUMBER,
name VARCHAR2 (10),
price NUMBER (5,2),
CONSTRAINT items_id_pk PRIMARY KEY (Id));

CREATE TABLE ON_SALE
(item_id NUMBER,
start_date DATE,
end_date DATE,
CONSTRAINT on_sale_item_id_fk FOREIGN KEY (item_id)
REFERENCES items (id));

CREATE SEQUENCE items_id_seq
START WITH 1001
INCREMENT by 1
NOMAXVALUE
NOCACHE;|||The procedure compiles now, so I'm making some progress, but when I call it with EXEC update_price('30-AUG-03'), price doesn't actually get updated. Could someone tell me why?

CREATE OR REPLACE PROCEDURE update_price
(p_cutoff IN CHAR)
IS
CURSOR item_cur IS
SELECT price, start_date
FROM items, on_sale
WHERE items.id = on_sale.item_id
AND p_cutoff = on_sale.start_date
FOR UPDATE of price;
BEGIN
FOR item_rec IN item_cur LOOP
UPDATE items
SET price = price * 0.9
WHERE CURRENT OF item_cur;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(item_rec.price, '$99.99') || ' ' || TO_CHAR(item_rec.start_date));
END LOOP;
COMMIT;

END;
/

EXEC update_price('30-AUG-03');|||Originally posted by appoooh
The procedure compiles now, so I'm making some progress, but when I call it with EXEC update_price('30-AUG-03'), price doesn't actually get updated. Could someone tell me why?

CREATE OR REPLACE PROCEDURE update_price
(p_cutoff IN CHAR)
IS
CURSOR item_cur IS
SELECT price, start_date
FROM items, on_sale
WHERE items.id = on_sale.item_id
AND p_cutoff = on_sale.start_date
FOR UPDATE of price;
BEGIN
FOR item_rec IN item_cur LOOP
UPDATE items
SET price = price * 0.9
WHERE CURRENT OF item_cur;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(item_rec.price, '$99.99') || ' ' || TO_CHAR(item_rec.start_date));
END LOOP;
COMMIT;

END;
/

EXEC update_price('30-AUG-03');
Seems OK to me:

SQL> select * from items;

ID NAME PRICE
---- ---- ----
1 thing 10

SQL> select * from on_sale;

ITEM_ID START_DATE END_DATE
---- ---- ----
1 30-AUG-2003 31-DEC-2003

SQL> EXEC update_price('30-AUG-03');
$10.00 30-AUG-2003

PL/SQL procedure successfully completed.

SQL> select * from items;

ID NAME PRICE
---- ---- ----
1 thing 9

Are you being confused by the fact that the output from PUT_LINE still shows the old price?

BTW, it could more efficiently be written without a cursor like this:

CREATE OR REPLACE PROCEDURE update_price
(p_cutoff IN CHAR)
IS
BEGIN
update items
set price = price*0.9
where id in
( select item_id
from on_sale
where start_date = to_date(p_cutoff,'DD-MON-RR')
);
END;
/

(I would not perform the commit in the procedure - prefer to leave that decision to the caller).

problems with running stored procedure via MSDTC on server 2003

Hi,
I would like a little help from someone who is experienced with msdtc on
server 2003.
We have a trouble with running a stored procedure using dtc on win2003server
ent. With sql server 2000 SP3.
For some reason it returns this error:
Server: Msg 7391, Level 16, State 1, Procedure
> sp_BannerOnKeyword_Update, Line 29 The operation could not be
> performed because the OLE DB provider 'SQLOLEDB' was unable
> to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ] OLE DB
> error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
Error comes out in place where that sp points on linked server, defined in
linked server in ent. Manager.
On w2k sp4 it works without problems, with the same linked server.
All servers runs msdtc and when I perform with dtcping.exe they see each
other without problems.
Also servers are on the same switch without firewall.
Also I tried to enable network dtc access in 2003 settings, which didn't
help.
When browsing google it seemed to me that its quite common problem since
nt4, but I didn't find solution for fixing it.
Thanks a lot for help, it would be very helpful for me.
regards
Martin Cvancara
Chief System Administrator
Atlas.cz a.s.Although I don't know which ports/services need to be opened up, your
problem is related to the fact the Win2003 has everything close down ( like
DCOM, etc) when it is installed...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martin Cvancara" <martin@.cvancara.com> wrote in message
news:#ChuXLnjDHA.2536@.tk2msftngp13.phx.gbl...
> Hi,
>
> I would like a little help from someone who is experienced with msdtc on
> server 2003.
>
> We have a trouble with running a stored procedure using dtc on
win2003server
> ent. With sql server 2000 SP3.
>
> For some reason it returns this error:
>
> Server: Msg 7391, Level 16, State 1, Procedure
> > sp_BannerOnKeyword_Update, Line 29 The operation could not be
> > performed because the OLE DB provider 'SQLOLEDB' was unable
> > to begin a distributed transaction.
> > [OLE/DB provider returned message: New transaction cannot
> > enlist in the specified transaction coordinator. ] OLE DB
> > error trace [OLE/DB Provider 'SQLOLEDB'
> > ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> Error comes out in place where that sp points on linked server, defined in
> linked server in ent. Manager.
>
> On w2k sp4 it works without problems, with the same linked server.
>
> All servers runs msdtc and when I perform with dtcping.exe they see each
> other without problems.
> Also servers are on the same switch without firewall.
>
> Also I tried to enable network dtc access in 2003 settings, which didn't
> help.
>
> When browsing google it seemed to me that its quite common problem since
> nt4, but I didn't find solution for fixing it.
>
> Thanks a lot for help, it would be very helpful for me.
>
> regards
>
> Martin Cvancara
> Chief System Administrator
> Atlas.cz a.s.
>

Friday, March 23, 2012

Problems with Return Value with Stored Procedure

Hi,

I have facing this problem, and i don't know what is going wrong.

The SP when tested in the Query Analyser works fine.

the resultant is either a 0 or PID where 0 is returned when the insert doesn't happen

I might have some extra declaration, which might be causing this error , or not defining the right thing.

Any suggestion is welcomed and greatly appreciated.

Thanks


Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
Dim sSQLConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim daInsertSku As SqlDataAdapter = New SqlDataAdapter()
Try
sSQLConnection.Open()
Dim cmdInsert As New SqlCommand("sp_request", sSQLConnection)
cmdInsert.CommandType = CommandType.StoredProcedure

cmdInsert.Parameters.Add("@.Username", SqlDbType.Int, 4)
cmdInsert.Parameters("@.Username").Value = "0"

Dim PID As SqlParameter = New SqlParameter("@.identity", SqlDbType.Int)
PID.Direction = ParameterDirection.ReturnValue
cmdInsert.Parameters.Add(PID)

daInsertSku.InsertCommand = cmdInsert
cmdInsert.ExecuteNonQuery()
Response.Write(PID.Value)

Catch Exc As Exception
' error handling
Finally
' close connection
End Try
End Sub

Is PID a return value, or an output parameter? You should use an output parameter to return an ident from a sproc.

And what is wrong? Are you getting an error?|||I did get it working, i changed to Output Parameter instead or Return

thank yousql

Wednesday, March 21, 2012

Problems with output variables when using varchar

I am using c# in Visual Studio 2005 to run a stored procedure in a sql 2000 databse. this is the stored procedure

CREATE procedure dbo.returnVendor4
@.vendorID varchar(10),
@.name nvarchar output
as
select @.name = vendor_Name
from vendors
where Code = @.vendorID

I steped through the c# code, it gives me an error when the procedure is ran (at the ExecuteNonQuery() part). this is the error message

"String[0]: the Size property has an invalid size of 0."

I have a similar procedure that returns an int, and it works okay. My c# code that calls the procedure is below

string retVal ;
SqlConnection con = new SqlConnection(
"server = lissadell; database = s00048271; uid = s00048271; pwd = 'chocolate'");
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "returnVendor4";

cmd.Parameters.Add("name", SqlDbType.NVarChar);
cmd.Parameters.Add("vendorID", SqlDbType.VarChar);

cmd.Parameters["name"].Direction = ParameterDirection.Output;
cmd.Parameters["vendorID"].Value = "G/03/I";
cmd.ExecuteNonQuery();

retVal = (string)(cmd.Parameters["name"].Value);
textBox1.Text = retVal.ToString() ;

just an idea - have you tried switching the order of your Parameters.Add statements? I think SQL will want the input parameter first. No idea if this will make a difference, just something off the top of my head.

Another guess - can you specify the size of the parameters when you add them? i.e. varchar(10)?

|||i tried both ideas, they didn't work|||

IN your Stored Procedure you need to add a length to your @.name parameter

@.name nvarchar(50) output.

Also, I think your C# Parameter.Add syntax may be a little off (Been a while since I coded in C#), try

cmd.Parameters.Add("@.name", SqlDbType.NVarChar,50);
cmd.Parameters.Add("@.vendorID", SqlDbType.VarChar,10);

sql

Monday, March 12, 2012

problems with insert query and primary key

I am trying to run an insert query off of a sql datasource and I am erroring out. My code and stored procedure as of now are listed below. You will notice the section of the stored procedure that is pulling the value for facility_ID (primary key). I have also tried to pull these and pass the parameter from a label, but that does not work, giving an error that the stored procedure expects the parameter @.Facility_ID which was not supplied. One other odd thing is that stepping through the code, I watched the parameter count total 21, but when running the insert command, the insert parameter count shows 20. With the code below (my current project), I get an error that null values can not be entered for facility_ID. Please help.

CODE:

Dim myConnection As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("FacilitiesBuild").ConnectionString)

'open myconnection

myConnection.Open()

Dim myCommand As New Data.SqlClient.SqlCommand(SqlDataSourceFac.InsertCommand, myConnection)

myCommand.CommandType = Data.CommandType.StoredProcedure

myCommand.Parameters.Add("@.Name", Data.SqlDbType.VarChar, 50).Value = CType(Me.DetailsView1.FindControl("Textbox5"), TextBox).Text
myCommand.Parameters.Add("@.Address1", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox3"), TextBox).Text
myCommand.Parameters.Add("@.Address2", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox4"), TextBox).Text
myCommand.Parameters.Add("@.State", Data.SqlDbType.VarChar, 2).Value = CType(Me.DetailsView1.FindControl("Textbox17"), TextBox).Text
myCommand.Parameters.Add("@.Zip", Data.SqlDbType.VarChar, 10).Value = CType(Me.DetailsView1.FindControl("Textbox6"), TextBox).Text
myCommand.Parameters.Add("@.Phone", Data.SqlDbType.VarChar, 14).Value = CType(Me.DetailsView1.FindControl("Textbox7"), TextBox).Text
myCommand.Parameters.Add("@.Admin_Name", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox8"), TextBox).Text
myCommand.Parameters.Add("@.Comments", Data.SqlDbType.VarChar, 250).Value = CType(Me.DetailsView1.FindControl("Textbox10"), TextBox).Text
myCommand.Parameters.Add("@.Owner", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("TypeOwnerInsert"), Label).Text
myCommand.Parameters.Add("@.Beds", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("BedsInsert"), Label).Text
myCommand.Parameters.Add("@.Population", Data.SqlDbType.NText).Value = CType(Me.DetailsView1.FindControl("PopulationInsert"), Label).Text
myCommand.Parameters.Add("@.Type_Facility", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("TypeFacilityInsert"), Label).Text
myCommand.Parameters.Add("@.Type_Other", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("TypeOtherInsert"), Label).Text
myCommand.Parameters.Add("@.Profit", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ProfitInsert"), Label).Text
myCommand.Parameters.Add("@.Religious", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ReligiousInsert"), Label).Text
myCommand.Parameters.Add("@.Licensed", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("LicensedInsert"), Label).Text
myCommand.Parameters.Add("@.Active", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ActiveInsert"), Label).Text
myCommand.Parameters.Add("@.City_ID", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("InsertCityLabel"), Label).Text
myCommand.Parameters.Add("@.Agency_ID", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("InsertAgencyLabel"), Label).Text
myCommand.Parameters.Add("@.County", Data.SqlDbType.NVarChar, 3).Value = CType(Me.DetailsView1.FindControl("InsertCountyLabel"), Label).Text
myCommand.Parameters.Add("@.Facility_ID", Data.SqlDbType.VarChar, 6).Value = CType(Me.DetailsView1.FindControl("InsertFacilityLabel"), Label).Text

If CType(Me.DetailsView1.FindControl("DropDownList1"), DropDownList).SelectedItem.Text = "Please Select One" Then
MsgBox("You must select an agency")
Else : SqlDataSourceFac.Insert()
End If

STORED PROCEDURE:

ALTER PROCEDURE [dbo].[SP_OMBFacilityAddDOTNET]


@.Name varchar(50),
@.Address1 varchar(40),
@.Address2 varchar(40),
@.State varchar(2),
@.Zip varchar(10),
@.Phone varchar(14),
@.Admin_Name varchar(40),
@.Comments varchar(250),
@.Owner char(1),
@.Beds int,
@.Population numeric(10,0),
@.Type_Facility char(1),
@.Type_Other varchar(40),
@.Profit char(1),
@.Religious char(1),
@.Licensed char(1),
@.Active char(1),
@.City_ID int,
@.Agency_ID int,
@.County nvarchar(3)

AS
BEGIN
DECLARE @.Facility_ID varchar(6)
DECLARE @.nextID varchar(3)

/* get next facilityID */
SELECT @.nextID = MAX(RIGHT(Facility_ID, LEN(Facility_ID)-(CHARINDEX('-', Facility_ID)))) + 1
From OMBFacility
Where Agency_ID = @.Agency_ID

SELECT @.Facility_ID = CAST(@.Agency_ID AS varchar(2)) + '-' + RIGHT('000' + RTRIM(@.nextID), 3)
INSERT INTO [AIMS].[dbo].[OMBFacility]
([Name],
[Address1],
[Address2],
[State],
[Zip],
[Phone],
[Admin_Name],
[Comments],
[Owner],
[Beds],
[Population],
[Type_Facility],
[Type_Other],
[Profit],
[Religious],
[Licensed],
[Active],
[City_ID],
[Agency_ID],
[County],
[Facility_ID])

VALUES
(@.Name
,@.Address1
,@.Address2
,@.State
,@.Zip
,@.Phone
,@.Admin_Name
,@.Comments
,@.Owner
,@.Beds
,@.Population
,@.Type_Facility
,@.Type_Other
,@.Profit
,@.Religious
,@.Licensed
,@.Active
,@.City_ID
,@.Agency_ID
,@.County
,@.Facility_ID)

END

Here you are:Getting the identity of the most recently added record

|||

I don't mean to be a moron, but will that work in a stored procedure as well? Where would I place "SET @.NewId = Scope_Identity()" in the stored procedure? I tried it at the first using both NewId thinking I could declare it later and Facility_ID which is the name of my primary key, but either way it tells me I have to declare the variable. This error comes up whether or not I declare the variable with the main set of variables in the stored procedure.

|||

lukeduke:

I don't mean to be a moron

Sorry - I'm the moron for not reading your post correctly. I saw the subject and had an almost Pavlovian reaction. The article specifically talks about getting the value of autoincrement fields. Your's isn't doing that. I need to study your post a bit more carefully.

|||

That's cool man, wipe the slobber off of your keyboard. Have you got any idea on what's causing the issue, I am pulling hair out trying to find it.

|||

If i am reading this all correctly here might be a solution:

If you want the ID of the newly inserted record(facility_ID), dont pass that value to the stored procedure, but declare that variable in the procedure like you have

declare @.faility_ID int; set @.facility_ID = 0

dont have it in the insert list, so the list should end with County and @.County. After the insert code run this:

INSERT INTO [AIMS].[dbo].[OMBFacility] ([Name], [Address1], [Address2], [State], [Zip], [Phone], [Admin_Name], [Comments], [Owner], [Beds], [Population], [Type_Facility], [Type_Other], [Profit], [Religious], [Licensed], [Active], [City_ID], [Agency_ID], [County])VALUES (@.Name ,@.Address1 ,@.Address2 ,@.State ,@.Zip ,@.Phone ,@.Admin_Name ,@.Comments ,@.Owner ,@.Beds ,@.Population ,@.Type_Facility ,@.Type_Other ,@.Profit ,@.Religious ,@.Licensed ,@.Active ,@.City_ID ,@.Agency_ID ,@.County)
if(@.@.error = 0)begin set @.facility_ID = scope_identity()end

and have a

select @.facility_ID facilityID

at the end to return the newly inserted ID, this will return a value > 0 if success and 0 if failure.

|||

kyle.spitzer:

If i am reading this all correctly here might be a solution:

I think you fell into the same trap as I did. Scope_Identity will only return an Identity column. Lukeduke's constructing his own varchar PK.

@.LukeDuke:

Could you post some more code so I have half a chance of knocking up a sample page to test? The aspx perhaps? Or just the relevant parts?

|||

Let me try a couple of things that were suggested first. If they don't work, I'll try and build a mockup to post the code for. The app I'm building now is a beast with seven datasources pulling from different tables, a gridview and two detailsview that are both using about 15 template fields each. I'll post the mockup this afternoon or tomorrow morning if my other attempts fail. Thanks for your input so far.

|||

One thing that Kyle did point out though - you have @.FacilityID in the parameter list, but whatever value you have set that doesn't appear to be used anywhere in the procedure. You have declared @.FacilityID as a local variable in the proc and are setting its value purely internally. I can't see why you have added it to the datasource control's parameter collection.

|||

That is where everything gets weird. Earlier in the code, I call a stored procedure that fetches the next facility id and assigns it to the text property of a databound label (the one that supplies the parameter). I run the program in VS2005 debug mode using autoS and set a breakpoint when the parameters are being passed. I step through the code and when the paramter collection is completed, autoS shows that the parameter count is 21.

The next step in the code is to make sure the user selected an agency and to run the insert command of the data source. When stepping to that point, autoS shows that the insertparameter count is 20. Each time facility_ID is dropped. I have moved it up in the list of parameters being passed thinking maybe the bottom parameter is being ignored, but it drops the value for facility_ID each time. I have tried to remove the databinding from the label and I get the same error. Each time I get, the stored procedure SP_OMBFacilityAddDOTNET expects the parameter @.Facility_ID which was not supplied.

Does that have anything to do with the Facility_ID being a primary key (and foreign key to other tables)?

There are two options I am going to try now. First, I'm going to make the facility_ID generate in an event that would fire before the button click event that is populating the parameter collection.

The other option I am going to attempt is to call the stored procedure that returns the new facility_ID inside of my the current stored procedure. The tricky part of that is the Facility_ID is in the format of Agency_ID - Facility_ID or 2-125 for example. In my label, I have to combine the two fields to set the text property.

|||

I fixed the problem. I had to get another eye on the code, but the parameter collection was not passing to the sqldatasource. The way I worked it out was to go into the properties of the sqldatasource and declare the parameters on the insert query. I then used session variables to pass the parameters and everything is working fine.

Thanks for all of your help!

|||

Yes, your original code seemed a little odd.

You built up a sqlcommand object using the stored procedure name stored in your SqlDatasource, manually added a bunch of parameters and assigned the values to them, and then you executed the insert of the SqlDatasource (instead of the SqlCommand).

The SqlCommand, and the parameters you built aren't being used at all.

I'm glad you got it finally working, however, you may want to take a look at implementing your code inside the SqlDatasource_Inserting event instead. Inside there, you can assign the values (or modify the default ones). That way you aren't stuffing arbitrary values into the session object that really don't belong in session. Although, I'm a little confused as to why you aren't using two-way databinding since it appears all the data is coming from a detailsview object.

problems with IF @test_var = NULL

hey,
this is my procedure but is doesn't work. who can tell me what's wrong?
CREATE PROCEDURE new_procedure
@.test_var int
AS
BEGIN
IF @.test_var = NULL
BEGIN
INSERT INTO table (isnull) VALUES (0)
END
ELSE
BEGIN
INSERT INTO table (isnull) VALUES (1)
END
END
the result of "IF @.test_var = NULL" always seems to be false, because I
only get 1 in the column.
Why does this not work?
thanxFirst of all if would suggest NOT to use reserved snytax word as
columnnames etc. (those one marked as colored in QA :-) ). Second, to
the base problem "= NULL" is NOT the same as "IS NULL" un less you
specify the ANSI_NULLS switch:
SET ANSI_NULLS ON
DECLARE @.test varchar(100)
Select 1 where @.test = NULL
SELECT 1 where @.test is null
GO
SET ANSI_NULLS OFF
DECLARE @.test varchar(100)
Select 1 where @.test = NULL
SELECT 1 where @.test is null
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||CREATE PROCEDURE new_procedure
@.test_var int
AS
BEGIN
IF @.test_var is NULL
BEGIN
INSERT INTO table (isnull) VALUES (0)
END
ELSE
BEGIN
INSERT INTO table (isnull) VALUES (1)
END
END|||To add to the other responses, consider simplifying your proc with a CASE
expression:
CREATE PROCEDURE new_procedure
@.test_var int
AS
INSERT INTO MyTable (isnull)
VALUES (CASE WHEN @.test_var IS NULL THEN 0 ELSE 1 END)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Rudderius" <dries@.bestopia.be> wrote in message
news:43202221$0$335$ba620e4c@.news.skynet.be...
> hey,
> this is my procedure but is doesn't work. who can tell me what's wrong?
> CREATE PROCEDURE new_procedure
> @.test_var int
> AS
> BEGIN
> IF @.test_var = NULL
> BEGIN
> INSERT INTO table (isnull) VALUES (0)
> END
> ELSE
> BEGIN
> INSERT INTO table (isnull) VALUES (1)
> END
> END
> the result of "IF @.test_var = NULL" always seems to be false, because I
> only get 1 in the column.
> Why does this not work?
> thanx

Problems with IF

Hi,

I have this stored procedure

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[sp_insertpositionsheader]

@.settlementDate DATETIME,

@.description VARCHAR(50),

@.calculatedUsed INT = 0,

@.refRecId INT OUTPUT

AS

SELECT @.refRecId = convert(int, Id) FROM ARSNettedPositions

WHERE SettlementDate = @.settlementDate

IF (@.refRecId = 0)

INSERT INTO ARSNettedPositions (SettlementDate, Description, CalculatedUsed) VALUES (@.settlementDate, @.description, @.calculatedUsed) SET @.refRecId = SCOPE_IDENTITY()

RETURN @.refRecId

I call it from my code, like this:

sqlCommand = new SqlCommand();

sqlCommand.Connection = dbConnection;

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlCommand.CommandText = "sp_insertpositionsheader";

sqlCommand.Parameters.AddWithValue("@.settlementDate", settlementDate);

sqlCommand.Parameters.AddWithValue("@.description", "MAN Settlement report");

sqlCommand.Parameters.AddWithValue("@.calculatedUsed", isCalculated);

SqlParameter refRecIdParameter = new SqlParameter("@.refRecId", SqlDbType.Int);

refRecIdParameter.Direction = ParameterDirection.Output;

sqlCommand.Parameters.Add(refRecIdParameter);

sqlCommand.ExecuteScalar();

refRecId = (int)refRecIdParameter.Value;

On the last line, I get "specified cast is not valid" error. I don't know why. And when I delete IF in the stored procedure, everything runs ok (but I get wrong number Smile ).

Could you help me explain it?

Change the top to:

ALTER PROCEDURE [dbo].[sp_insertpositionsheader]

@.settlementDate DATETIME,

@.description VARCHAR(50),

@.calculatedUsed INT = 0,

@.refRecId INT OUTPUT

AS

SET NOCOUNT ON

SELECT @.refRecId = convert(int, Id) FROM ARSNettedPositions

.

.
.|||

Helps not.

Situation is still the same.

|||OHHHHH. Now that I look at it, you are missing BEGIN/END on your IF. Change it to:

SET NOCOUNT ON

SELECT @.refRecId = convert(int, Id) FROM ARSNettedPositions

WHERE SettlementDate = @.settlementDate

IF (@.refRecId = 0)

BEGIN

INSERT INTO ARSNettedPositions (SettlementDate, Description, CalculatedUsed) VALUES (@.settlementDate, @.description, @.calculatedUsed)

SET @.refRecId = SCOPE_IDENTITY()

END

RETURN @.refRecId

You are running 2 statements under your IF which requires BEGIN/END. I have a rule of ALWAY using BEGIN/END after IF, even when technically unnecessary because you never know when you will want 2 statements.

Problems with float results in calculation.

I have a stored procedure that uses the LOG10 function to calculate a logarithmic average. The first part of the calculation takes 1 divided by the number of results achieved per group. This always results in something like 0.2 or 0.3. Now, the problem is that SQL Server rounds off the result to the nearest 0, which always results in a result of 0. This then causes problems with the LOG10 function which results in a "Domain error" message. Anyone know of a way I can tell SQL to keep the value of 0.3 or 0.2 when it is calculated and not do a rounding? I've tried to convert the result of the calculation to float or real, but this did not help. Anyone know how to fix this?

Thanks,

Bcs you are doing integer division.

use the following sampel to fix yours..

Code Snippet

Select

1/Count(*) [1/Count(*)], --Wrong, Integer Division

1.0/Count(*) [1.0/Count(*)], --Partially Correct

1.0/Cast(Count(*) as Float) [1.0/Cast(Count(*) as Float)], --Perfect Expression

Log10(1.0/Cast(Count(*) as Float)) [Your Expression] --This is your answer

From

Sysobjects

|||Thanks a lot. It helped.

Friday, March 9, 2012

Problems with float results in calculation.

I have a stored procedure that uses the LOG10 function to calculate a logarithmic average. The first part of the calculation takes 1 divided by the number of results achieved per group. This always results in something like 0.2 or 0.3. Now, the problem is that SQL Server rounds off the result to the nearest 0, which always results in a result of 0. This then causes problems with the LOG10 function which results in a "Domain error" message. Anyone know of a way I can tell SQL to keep the value of 0.3 or 0.2 when it is calculated and not do a rounding? I've tried to convert the result of the calculation to float or real, but this did not help. Anyone know how to fix this?

Thanks,

Bcs you are doing integer division.

use the following sampel to fix yours..

Code Snippet

Select

1/Count(*) [1/Count(*)], --Wrong, Integer Division

1.0/Count(*) [1.0/Count(*)], --Partially Correct

1.0/Cast(Count(*) as Float) [1.0/Cast(Count(*) as Float)], --Perfect Expression

Log10(1.0/Cast(Count(*) as Float)) [Your Expression] --This is your answer

From

Sysobjects

|||Thanks a lot. It helped.

problems with float numbers

hi

im using a stored procedure to insert float numbers in a data base...the parameter i send to the stored procedure is a float but every time i use it, it inserts a number with a lot of extra numbers i mean if i try to insert 5.3 it inserts 5.30000019073486 any idea why this is happening?

thak you

Himarcelocr2,

Have you done any sort of calculation before add/update or during you retrieve the float value?

Anyway, try this before you update your float value:

Math.Round(yourFloatValue, numberOfDecimal)

Hope this is help

|||

What is the datatype of the column in the table that will have these values? If you only need 3 digits after the decimal use the decimal datatype and set the precision to 3. You can do this in the column properties under design view. Enterprise Mgr -> Right click on the table -> Design View.

|||

hi

thank you to both of you

i tried with the math.round, but it converts the numbers to integers, and i need the numbers to be float numbers...

i cant change the type in the sqlserver because i need it to be a float number

is there a way to show only 3 or 4 numbers after the ","?

thank you

|||

Hello,

You should let is save like that only and should use round(SQL function-round(fieldname,4)) in Stored Procedures where you retrive data.

|||

Hi,

The Float datatype in both SQL Server and .NET framework is a floating point value. It is not accurate but an approximate value. This is defined by way that float is stored in memory.

However, if you need an accurate value, try to use Decimal instead in both SQL Server and .NET framework.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Wednesday, March 7, 2012

Problems with carating Stored Procedure from ADO

Hi
I try to create SP from ADO. Open connection and excecute queries.
con.Execute "SET QUOTED_IDENTIFIER OFF "
con.Execute "SET ansinull ON"
con.Execute TextProc
TextProc content
"
create procedure BISSGetDllName (
@.ID_MESS numeric(10) = 0,
@.SwiftOperType int = 0,
@.IsAction int=1
) as
Begin
declare
@.ret int,
@.MessageType int,
@.AccBalance1 int,
@.urgency varchar(1)
select
@.MessageType = MessageType
from BISSHead
where ID_MESS = @.ID_MESS
select @.urgency='s'
from BISSMess
where
ID_MESS=@.ID_MESS and
NumOfField='203B' and
convert(int,ContextOfField)=1
if @.MessageType= 202
begin
if exists(select 1 from BISSMess where ID_MESS=@.ID_MESS and
NumOfField ='58E')
select '_trbis202.dll','TrBIS2202'+@.urgency+'NR'
else
select '_trbis202.dll','TrBIS2202'+@.urgency
return 1
end
else if @.MessageType= 103
begin
declare @.IsTax int
exec @.ret=BISSIsTax @.ID_MESS, @.IsTax output
if @.ret<>1
return @.ret
if @.IsTax=1
begin
select '_trbistax.dll','TrBISTax'+@.urgency
return 1
end
/*select @.AccBalance1 = convert(int,substring(KorrAccount,1,4))
from BISSMessAddition
where ID_MESS = @.ID_MESS
*/
/*if exists(select 1
from ReportSetup
where
Code = 29 and
Amount1=0 and
AccBalance1<>@.AccBalance1
) */
begin
if exists(select 1 from BISSMess where ID_MESS=@.ID_MESS and
NumOfField ='57E')
select '_trbiss100.dll','TrBIS100'+@.urgency+'NR'
else
select '_trbiss100.dll','TrBIS100'+@.urgency
return 1
end
end
return -1221
end
"
ADO sad [SYBASE][OLE DB Sybase provider]Value has not been specified for
parameter 1.
May bee ADO find parametrs in Create query.. and want values for parametrs.
How could execute this query...
You need to bind to the return parameter.
Michael
"Dmitry" <Savtchin@.st.by> wrote in message
news:OhPrMFaxEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I try to create SP from ADO. Open connection and excecute queries.
> con.Execute "SET QUOTED_IDENTIFIER OFF "
> con.Execute "SET ansinull ON"
> con.Execute TextProc
> TextProc content
> "
> create procedure BISSGetDllName (
> @.ID_MESS numeric(10) = 0,
> @.SwiftOperType int = 0,
> @.IsAction int=1
> ) as
> Begin
> declare
> @.ret int,
> @.MessageType int,
> @.AccBalance1 int,
> @.urgency varchar(1)
> select
> @.MessageType = MessageType
> from BISSHead
> where ID_MESS = @.ID_MESS
> select @.urgency='s'
> from BISSMess
> where
> ID_MESS=@.ID_MESS and
> NumOfField='203B' and
> convert(int,ContextOfField)=1
> if @.MessageType= 202
> begin
> if exists(select 1 from BISSMess where ID_MESS=@.ID_MESS and
> NumOfField ='58E')
> select '_trbis202.dll','TrBIS2202'+@.urgency+'NR'
> else
> select '_trbis202.dll','TrBIS2202'+@.urgency
> return 1
> end
> else if @.MessageType= 103
> begin
> declare @.IsTax int
> exec @.ret=BISSIsTax @.ID_MESS, @.IsTax output
> if @.ret<>1
> return @.ret
> if @.IsTax=1
> begin
> select '_trbistax.dll','TrBISTax'+@.urgency
> return 1
> end
> /*select @.AccBalance1 = convert(int,substring(KorrAccount,1,4))
> from BISSMessAddition
> where ID_MESS = @.ID_MESS
> */
> /*if exists(select 1
> from ReportSetup
> where
> Code = 29 and
> Amount1=0 and
> AccBalance1<>@.AccBalance1
> ) */
> begin
> if exists(select 1 from BISSMess where ID_MESS=@.ID_MESS and
> NumOfField ='57E')
> select '_trbiss100.dll','TrBIS100'+@.urgency+'NR'
> else
> select '_trbiss100.dll','TrBIS100'+@.urgency
> return 1
> end
>
> end
> return -1221
> end
> "
> ADO sad [SYBASE][OLE DB Sybase provider]Value has not been specified for
> parameter 1.
> May bee ADO find parametrs in Create query.. and want values for
parametrs.
> How could execute this query...
>
>

Problems with carating Stored Procedure from ADO

Hi
I try to create SP from ADO. Open connection and excecute queries.
con.Execute "SET QUOTED_IDENTIFIER OFF "
con.Execute "SET ansinull ON"
con.Execute TextProc
TextProc content
"
create procedure BISSGetDllName (
@.ID_MESS numeric(10) = 0,
@.SwiftOperType int = 0,
@.IsAction int=1
) as
Begin
declare
@.ret int,
@.MessageType int,
@.AccBalance1 int,
@.urgency varchar(1)
select
@.MessageType = MessageType
from BISSHead
where ID_MESS = @.ID_MESS
select @.urgency='s'
from BISSMess
where
ID_MESS=@.ID_MESS and
NumOfField='203B' and
convert(int,ContextOfField)=1
if @.MessageType= 202
begin
if exists(select 1 from BISSMess where ID_MESS=@.ID_MESS and
NumOfField ='58E')
select '_trbis202.dll','TrBIS2202'+@.urgency+'NR'
else
select '_trbis202.dll','TrBIS2202'+@.urgency
return 1
end
else if @.MessageType= 103
begin
declare @.IsTax int
exec @.ret=BISSIsTax @.ID_MESS, @.IsTax output
if @.ret<>1
return @.ret
if @.IsTax=1
begin
select '_trbistax.dll','TrBISTax'+@.urgency
return 1
end
/*select @.AccBalance1 = convert(int,substring(KorrAccount,1,4))
from BISSMessAddition
where ID_MESS = @.ID_MESS
*/
/*if exists(select 1
from ReportSetup
where
Code = 29 and
Amount1=0 and
AccBalance1<>@.AccBalance1
) */
begin
if exists(select 1 from BISSMess where ID_MESS=@.ID_MESS and
NumOfField ='57E')
select '_trbiss100.dll','TrBIS100'+@.urgency+'NR'
else
select '_trbiss100.dll','TrBIS100'+@.urgency
return 1
end
end
return -1221
end
"
ADO sad [SYBASE][OLE DB Sybase provider]Value has not been specified
for
parameter 1.
May bee ADO find parametrs in Create query.. and want values for parametrs.
How could execute this query...You need to bind to the return parameter.
Michael
"Dmitry" <Savtchin@.st.by> wrote in message
news:OhPrMFaxEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I try to create SP from ADO. Open connection and excecute queries.
> con.Execute "SET QUOTED_IDENTIFIER OFF "
> con.Execute "SET ansinull ON"
> con.Execute TextProc
> TextProc content
> "
> create procedure BISSGetDllName (
> @.ID_MESS numeric(10) = 0,
> @.SwiftOperType int = 0,
> @.IsAction int=1
> ) as
> Begin
> declare
> @.ret int,
> @.MessageType int,
> @.AccBalance1 int,
> @.urgency varchar(1)
> select
> @.MessageType = MessageType
> from BISSHead
> where ID_MESS = @.ID_MESS
> select @.urgency='s'
> from BISSMess
> where
> ID_MESS=@.ID_MESS and
> NumOfField='203B' and
> convert(int,ContextOfField)=1
> if @.MessageType= 202
> begin
> if exists(select 1 from BISSMess where ID_MESS=@.ID_MESS and
> NumOfField ='58E')
> select '_trbis202.dll','TrBIS2202'+@.urgency+'NR'
> else
> select '_trbis202.dll','TrBIS2202'+@.urgency
> return 1
> end
> else if @.MessageType= 103
> begin
> declare @.IsTax int
> exec @.ret=BISSIsTax @.ID_MESS, @.IsTax output
> if @.ret<>1
> return @.ret
> if @.IsTax=1
> begin
> select '_trbistax.dll','TrBISTax'+@.urgency
> return 1
> end
> /*select @.AccBalance1 = convert(int,substring(KorrAccount,1,4))
> from BISSMessAddition
> where ID_MESS = @.ID_MESS
> */
> /*if exists(select 1
> from ReportSetup
> where
> Code = 29 and
> Amount1=0 and
> AccBalance1<>@.AccBalance1
> ) */
> begin
> if exists(select 1 from BISSMess where ID_MESS=@.ID_MESS and
> NumOfField ='57E')
> select '_trbiss100.dll','TrBIS100'+@.urgency+'NR'
> else
> select '_trbiss100.dll','TrBIS100'+@.urgency
> return 1
> end
>
> end
> return -1221
> end
> "
> ADO sad [SYBASE][OLE DB Sybase provider]Value has not been specifi
ed for
> parameter 1.
> May bee ADO find parametrs in Create query.. and want values for
parametrs.
> How could execute this query...
>
>

Problems with carating Stored Procedure from ADO

Hi
I try to create SP from ADO. Open connection and excecute queries.
con.Execute "SET QUOTED_IDENTIFIER OFF "
con.Execute "SET ansinull ON"
con.Execute TextProc
TextProc content
"
create procedure BISSGetDllName (
@.ID_MESS numeric(10) = 0,
@.SwiftOperType int = 0,
@.IsAction int=1
) as
Begin
/************************************************** ********
@.ID_MESS - message
@.SwiftOperType - operation
@.IsAction - action or rollback?
************************************************** *******/
declare
@.ret int,
@.MessageType int,
@.AccBalance1 int,
@.urgency varchar(1)
select
@.MessageType = MessageType
from BISSHead
where ID_MESS = @.ID_MESS
select @.urgency='s'
from BISSMess
where
ID_MESS=@.ID_MESS and
NumOfField='203B' and
convert(int,ContextOfField)=1
/*BIS aaieianeee */
if @.MessageType= 202
begin
if exists(select 1 from BISSMess where ID_MESS=@.ID_MESS and
NumOfField ='58E')
select '_trbis202.dll','TrBIS2202'+@.urgency+'NR'
else
select '_trbis202.dll','TrBIS2202'+@.urgency
return 1
end
else if @.MessageType= 103
begin
/*BIS iaeiae*/
declare @.IsTax int
exec @.ret=BISSIsTax @.ID_MESS, @.IsTax output
if @.ret<>1
return @.ret
if @.IsTax=1
begin
select '_trbistax.dll','TrBISTax'+@.urgency
return 1
end
/*select @.AccBalance1 = convert(int,substring(KorrAccount,1,4))
from BISSMessAddition
where ID_MESS = @.ID_MESS
*/
/*BIS eeeaioneee*/
/*if exists(select 1
from ReportSetup
where
Code = 29 and
Amount1=0 and
AccBalance1<>@.AccBalance1
) */
begin
if exists(select 1 from BISSMess where ID_MESS=@.ID_MESS and
NumOfField ='57E')
select '_trbiss100.dll','TrBIS100'+@.urgency+'NR'
else
select '_trbiss100.dll','TrBIS100'+@.urgency
return 1
end
end
return -1221
end
"
ADO sad [SYBASE][OLE DB Sybase provider]Value has not been specified for
parameter 1.
May bee ADO find parametrs in Create query.. and want values for parametrs.
How could execute this query...
Please don't multi-post. And stop using a Sybase provider if you're
connecting to SQL Server! http://www.aspfaq.com/2126
"Dmitry" <Savtchin@.st.by> wrote in message
news:u2irvPaxEHA.3096@.TK2MSFTNGP14.phx.gbl...
> Hi
> I try to create SP from ADO. Open connection and excecute queries.
> con.Execute "SET QUOTED_IDENTIFIER OFF "
> con.Execute "SET ansinull ON"
> con.Execute TextProc
> TextProc content
> "
> create procedure BISSGetDllName (
> @.ID_MESS numeric(10) = 0,
> @.SwiftOperType int = 0,
> @.IsAction int=1
> ) as
> Begin
> /************************************************** ********
> @.ID_MESS - message
> @.SwiftOperType - operation
> @.IsAction - action or rollback?
>
> ************************************************** *******/
> declare
> @.ret int,
> @.MessageType int,
> @.AccBalance1 int,
> @.urgency varchar(1)
> select
> @.MessageType = MessageType
> from BISSHead
> where ID_MESS = @.ID_MESS
> select @.urgency='s'
> from BISSMess
> where
> ID_MESS=@.ID_MESS and
> NumOfField='203B' and
> convert(int,ContextOfField)=1
> /*BIS aaieianeee */
> if @.MessageType= 202
> begin
> if exists(select 1 from BISSMess where ID_MESS=@.ID_MESS and
> NumOfField ='58E')
> select '_trbis202.dll','TrBIS2202'+@.urgency+'NR'
> else
> select '_trbis202.dll','TrBIS2202'+@.urgency
> return 1
> end
> else if @.MessageType= 103
> begin
> /*BIS iaeiae*/
> declare @.IsTax int
> exec @.ret=BISSIsTax @.ID_MESS, @.IsTax output
> if @.ret<>1
> return @.ret
> if @.IsTax=1
> begin
> select '_trbistax.dll','TrBISTax'+@.urgency
> return 1
> end
> /*select @.AccBalance1 = convert(int,substring(KorrAccount,1,4))
> from BISSMessAddition
> where ID_MESS = @.ID_MESS
> */
> /*BIS eeeaioneee*/
> /*if exists(select 1
> from ReportSetup
> where
> Code = 29 and
> Amount1=0 and
> AccBalance1<>@.AccBalance1
> ) */
> begin
> if exists(select 1 from BISSMess where ID_MESS=@.ID_MESS and
> NumOfField ='57E')
> select '_trbiss100.dll','TrBIS100'+@.urgency+'NR'
> else
> select '_trbiss100.dll','TrBIS100'+@.urgency
> return 1
> end
>
> end
> return -1221
> end
> "
> ADO sad [SYBASE][OLE DB Sybase provider]Value has not been specified for
> parameter 1.
> May bee ADO find parametrs in Create query.. and want values for
parametrs.
> How could execute this query...
>