?
Solved

Stored Procedure Not Working in VB

Posted on 2011-04-19
3
Medium Priority
?
245 Views
Last Modified: 2012-05-11
Hi Experts,

I have the following sp that works fine executed on SSMS. All it does is retrun all the sales the day before by SKU code.

USE [DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sl_dailyItemSales]
AS
BEGIN
	SET NOCOUNT ON;
	SELECT		TOP (100) ct.SKU, SUM(OrdLines.olProdQty) AS Qty, SUM(OrdLines.olProdPrice) AS Revenue, ct.CartTitle
	FROM		Orders o
	INNER JOIN	OrdLines ON o.oID = OrdLines.oID 
	INNER JOIN	Cartridges ct ON OrdLines.olProdCode = ct.CartID
	WHERE		(CAST(CONVERT(varchar, o.oDate, 101) AS datetime) = CONVERT(DATETIME, dateadd(day, 0, datediff(day, 0, dateadd(day, -1, getdate()))), 101))
	GROUP BY	ct.SKU, ct.CartTitle
	ORDER BY	ct.SKU
END

Open in new window


The data is supposed to be formatted and sent by email using some VB (classic asp I'm afraid) code saved as a .vbs file that will be fired by a scheduled task. See snippet:

Dim oConn: Set oConn = Createobject("ADODB.Connection")

With oConn
	.ConnectionTimeout = 3600
	.ConnectionString =	"Provider=SQLNCLI10.1;Data Source=localhost;Catalog=DB;User Id=un; Password=pw;"
	.Open
End With
				
		set rstMx = Createobject("adodb.recordset")
		set rstMx = oConn.Execute("sl_dailyItemSales")  ' <<< fails here

		If Not rstMx.EOF Then
			Do While Not rstMx.EOF 
				ArrSales = rstMx.GetRows()
			Loop
			Const arrSKU = 0
			Const arrVOL = 1
			Const arrVAL = 2
			Const arrCN = 3
		End If

Open in new window


The error I get is:


ERROR: The conversion of a varchar data type to a datatime data type resulted in an out of range value.
CODE: 80040E07

So what the hell am I doing wrong here? Totally baffled!

Any help would be really appreciated.

Regs,
Numb


0
Comment
Question by:ComfortablyNumb
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 35423433
Because you use Cast to convert from varchar to datetime it depends on the language settings. When connection from SSMS you'll probably have US language settings which is fine. When connecting from the script you'll probably have UK language settings which results in wrong dates. Always use CONVERT with a style to prevent this. Change your code to:
WHERE		(CONVERT(datetime, CONVERT(varchar, o.oDate, 101),101) = CONVERT(datetime, CONVERT(varchar, getdate()-1, 101),101))

Open in new window

0
 

Author Closing Comment

by:ComfortablyNumb
ID: 35423455
Fantastic! Did the job. Thanks so much...
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35423468
Or better even change it to:
WHERE (o.oDate >= CONVERT(datetime, CONVERT(varchar, getdate()-1, 101),101) and o.oDate < CONVERT(datetime, CONVERT(varchar, getdate(), 101),101))

Open in new window

So an index (if present) can be used on the o.oDate column.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question