September 15, 2010 at 4:04 pm
I am getting this error when trying to start debubbing a sp
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
I have looked all over for an answer. Can anyone help please? Yes I have double checked the datatypes of variables to the table def's.
TIA
Jim
here is my sp...
CREATE PROCEDURE DBO.GPM_SAFETY_INSPECTION_LOAD_INSPECTION(
@LOCATION VARCHAR(50),
@EMPLOYEE VARCHAR(50),
@DATE DATETIME)
AS
DECLARE
@COUNT INT,
@QUARTER INT,
@MONTH INT,
@DESCRIPTION VARCHAR(5000),
@NEXTID INT
SELECT @MONTH = MONTH(GETDATE())
IF @MONTH < 4 SET @QUARTER = 1
IF @MONTH > 3 AND @MONTH < 7 SET @QUARTER = 2
IF @MONTH > 6 AND @MONTH < 10 SET @QUARTER = 3
IF @MONTH > 9 SET @QUARTER = 4
SELECT
@COUNT = COUNT(*)
FROM
GPM_SAFETY_INSPECTION
WHERE
SLOCATION = @LOCATION AND
IYEAR = YEAR(GETDATE()) AND
IQUARTER = @QUARTER
IF @COUNT = 0
BEGIN
INSERT INTO GPM_SAFETY_INSPECTION(
SLOCATION,
SINSPECTED_BY,
DINSPECTION_DATE,
IYEAR,
IQUARTER)
VALUES(
@LOCATION,
@EMPLOYEE,
@DATE,
YEAR(GETDATE()),
@QUARTER)
SET @NEXTID = @@IDENTITY
DECLARE DESCRIPTIONS CURSOR STATIC
FOR
SELECT
SDESCRIPTION
FROM
GMP_SAFETY_INSPECTION_DESCRIPTIONS
ORDER BY
ISORT_ORDER1,
ISORT_ORDER2
OPEN DESCRIPTIONS
FETCH FROM DESCRIPTIONS INTO @DESCRIPTION
WHILE @@FETCH_STATUS = 0
BEGIN
--INSERT INTO GPM_SAFETY_INSPECTION_DETAILS(INSPECTION_ID, SDESCRIPTION) VALUES (@NEXTID, @DESCRIPTION)
FETCH FROM DESCRIPTIONS INTO @DESCRIPTION
END
CLOSE DESCRIPTIONS
DEALLOCATE DESCRIPTIONS
END
GO
September 15, 2010 at 6:13 pm
When running your SP using the debugger and imputing the @Date value as
'2010-09-15 20:03:05.043'
I get your error message. Attempt to debug without enclosing the @date as if it were character data, which does not produce your stated error.
And please, please next time when posting to a forum follow the procedure to script all tables, provide sample data as outlined in the article by Jeff Moden which you can read by clicking on the first link in my signature block
September 15, 2010 at 7:19 pm
You might want to change this:
IF @MONTH < 4 SET @QUARTER = 1
IF @MONTH > 3 AND @MONTH < 7 SET @QUARTER = 2
IF @MONTH > 6 AND @MONTH < 10 SET @QUARTER = 3
IF @MONTH > 9 SET @QUARTER = 4
with this:
SET @QUARTER = DatePart(quarter, GetDate());
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 16, 2010 at 9:51 am
Ron,
Thank you for your reply. Attached is the scripts and sample data. Thanks for the posting etiquette, didn't know. Still getting same error. I included a screen shot also.
Thanks again
Jim
September 16, 2010 at 11:56 am
The only input data supplied:
SET IDENTITY_INSERT GPM_Safety_Inspection_Descriptions ON
INSERT INTO GPM_Safety_Inspection_Descriptions
(ID, sDescription, iSort_Order1, iSort_Order2)
SELECT '1','Are Material Safety Data Sheets (MSDS) on file and available for all employees?','1','1' UNION ALL
SELECT '2','Work Comp Certificates are posted in each site office','1','2' UNION ALL
SELECT '3','Employees are not engaged in ergonomic hazards, e.g. awkward posture, prolonged repetitive motion, contact stress, etc.','2','1' UNION ALL
SELECT '4','Mechanical aids/equipment such as carts and dollies are provided where needed','2','2' UNION ALL
SELECT '5','Employees work areas are adequately illuminated.','3','1'
SET IDENTITY_INSERT GPM_Safety_Inspection_Descriptions ON
The code as I tested:
alter PROCEDURE DBO.GPM_SAFETY_INSPECTION_LOAD_INSPECTION(
@LOCATION VARCHAR(50),
@EMPLOYEE VARCHAR(50),
@DATE DATETIME)
AS
DECLARE
@COUNT INT,
@QUARTER INT,
@MONTH INT,
@DESCRIPTION VARCHAR(5000),
@NEXTID INT
SELECT @MONTH = MONTH(GETDATE())
IF @MONTH < 4 SET @QUARTER = 1
IF @MONTH > 3 AND @MONTH < 7 SET @QUARTER = 2
IF @MONTH > 6 AND @MONTH < 10 SET @QUARTER = 3
IF @MONTH > 9 SET @QUARTER = 4
SELECT @COUNT = COUNT(*) FROM GPM_SAFETY_INSPECTION
WHERE SLOCATION = @LOCATION AND IYEAR = YEAR(GETDATE()) AND
IQUARTER = @QUARTER
IF @COUNT = 0
BEGIN
INSERT INTO GPM_SAFETY_INSPECTION(
SLOCATION,SINSPECTED_BY,DINSPECTION_DATE,IYEAR,IQUARTER)
VALUES(@LOCATION,@EMPLOYEE,@DATE,YEAR(GETDATE()),@QUARTER)
--Add next for testing
SELECT * FROM GPM_SAFETY_INSPECTION
--End insert for testing
SET @NEXTID = @@IDENTITY
--Add next for testing
SELECT SDESCRIPTION
FROM dbo.GPM_Safety_Inspection_Descriptions
ORDER BY ISORT_ORDER1,ISORT_ORDER2
--End insert for testing
DECLARE DESCRIPTIONS CURSOR STATIC
FOR SELECT SDESCRIPTION
FROM GMP_SAFETY_INSPECTION_DESCRIPTIONS
ORDER BY ISORT_ORDER1,ISORT_ORDER2
OPEN DESCRIPTIONS
FETCH FROM DESCRIPTIONS INTO @DESCRIPTION
WHILE @@FETCH_STATUS = 0
BEGIN
--INSERT INTO GPM_SAFETY_INSPECTION_DETAILS(INSPECTION_ID, SDESCRIPTION) VALUES (@NEXTID, @DESCRIPTION)
FETCH FROM DESCRIPTIONS INTO @DESCRIPTION
END
CLOSE DESCRIPTIONS
DEALLOCATE DESCRIPTIONS
END
/* run as:
DBO.GPM_SAFETY_INSPECTION_LOAD_INSPECTION 'NY','ron','2010-09-16'
*/
Ran above using the debugger entered date as 2010-09-16 WITHOUT A ' OR " around the date .
No error messages, no problems that prevented the code from running in the debugger or executed as a SP
Note: I did NOT uncomment your line
--INSERT INTO GPM_SAFETY_INSPECTION_DETAILS(INSPECTION_ID, SDESCRIPTION) VALUES (@NEXTID, @DESCRIPTION)
September 16, 2010 at 12:52 pm
Thanks Ron!:-D
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply