Created a stored procedure in SQL 9 (2005) and have since upgraded to SQL 10 (2008). Since then, the following stored procedure has stopped working and thrown up the above error:
ALTER PROCEDURE [dbo].[GetModifiedPages]
@vPortalUID nvarchar(32) = ''
AS
BEGIN
-- Convert GUID to UI
DECLARE @nPortalUID AS uniqueidentifier
SET @nPortalUID = CAST(@vPortalUID AS uniqueidentifier)
The passed in param @vPortalUID contains: 2A66057D-F4E5-4E2B-B2F1-38C51A96D385. I execute the stored proc like this:
EXEC GetModifiedPages '2A66057D-F4E5-4E2B-B2F1-38C51A96D385'
It falls over. I have tried Convert aswell. Still no joy. Have also had the value going in with { } around it. I removed these programatically and manually as above.
If you are interested I am running the SP from an ASP Classic page, although that should not affect this as the above code was run using SSMS.
Thanks in advance for your help. James
this fails:
DECLARE @vPortalUID NVARCHAR(32)
SET @vPortalUID='2A66057D-F4E5-4E2B-B2F1-38C51A96D385'
DECLARE @nPortalUID AS UNIQUEIDENTIFIER
SET @nPortalUID = CAST(@vPortalUID AS uniqueidentifier)
PRINT @nPortalUID
this works
DECLARE @vPortalUID NVARCHAR(36)
SET @vPortalUID='2A66057D-F4E5-4E2B-B2F1-38C51A96D385'
DECLARE @nPortalUID AS UNIQUEIDENTIFIER
SET @nPortalUID = CAST(@vPortalUID AS UNIQUEIDENTIFIER)
PRINT @nPortalUID
the difference is NVARCHAR(36)
, your input parameter is too small!