problem with conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

problem with conversion

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-02-22 : 13:46:28

i am trying to build a stored procedure that will take an array of varchar, run a cursor and generate text from each array into a text file. One of the actions in the cursor is to call a stored procedure and parm in a uniqueidentifier. I keep getting a conversion error, but carn't figure it out

ALTER PROCEDURE [dbo].[spBOSS2012_Submission_BACS_Building]
@LedgerkeyArray varchar(200)
AS
BEGIN
SET NOCOUNT ON;
Declare @FileName Nvarchar(1000) = '\\Standbymaster\share\bacs.txt'
Declare @WriteLine Varchar(1000)
DECLARE @xpreurn_Value INT
DECLARE @return_value int,
@RTNSTR varchar(200)

Declare @FirstFile as bit = 0
Create table #tempArrayTable (rid varchar(500))
Insert into #tempArrayTable (rid)
(select value from dbo.BOSS2012_Split(@LedgerkeyArray,';'))

DECLARE @ArrayItem varchar(100)
DECLARE @LedgerKey_Cursor CURSOR
SET @LedgerKey_Cursor = CURSOR FAST_FORWARD FOR select rid
from #tempArrayTable

OPEN @LedgerKey_Cursor
FETCH NEXT FROM @LedgerKey_Cursor INTO @ArrayItem
WHILE @@FETCH_STATUS = 0
BEGIN

Declare @Ledger as uniqueidentifier = CAST(@ArrayItem AS UniqueIdentifier) /* need to convert back to uniqueidentifier */

Print @Ledger
/* HDR1 */
EXEC @return_value = [dbo].[spBOSS2012_Submission_BACS_HDR1]
@LedgerKey = @Ledger,
@RTNSTR = @RTNSTR OUTPUT

if @return_value <> 0
Begin
return 1
End

If @FirstFile = 0
BEGIN
/* WRITE HDR1 */
Set @WriteLine = 'echo ' + @RtnStr + '> ' + @FileName
exec @xpreurn_Value = master..xp_cmdshell @WriteLine , no_output
END
If @FirstFile = 1
BEGIN
/* WRITE HDR1 */
Set @WriteLine = 'echo ' + @RtnStr + '>> ' + @FileName
exec @xpreurn_Value = master..xp_cmdshell @WriteLine , no_output
END
set @FirstFile = 1

If @xpreurn_Value <> 0
Begin
/* Error Writing File */
return 1
End

FETCH NEXT FROM @LedgerKey_Cursor INTO @ArrayItem

END
Close @LedgerKey_Cursor
deallocate @LedgerKey_Cursor

END

DECLARE @return_value int

EXEC @return_value = [dbo].[spBOSS2012_Submission_BACS_Building]
@LedgerkeyArray = N'''5E743F49-4AE1-442C-A33D-000590592472'';''7AA73D9E-96C7-4183-B7DA-0027A9CFA8D0'';''F541DFD6-A570-415A-8C4C-09C001F74775'';''5705F61A-0915-4937-A948-0A2C8C916898'''

SELECT 'Return Value' = @return_value

error
Msg 8169, Level 16, State 2, Procedure spBOSS2012_Submission_BACS_Building, Line 32
Conversion failed when converting from a character string to uniqueidentifier.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-22 : 14:22:23

Can you do a PRINT on the @ArrayItem before you try casting to see what it is. It appears that you have too many single quotes in the EXEEC call. Maybe try calling like:

EXEC @return_value = [dbo].[spBOSS2012_Submission_BACS_Building]
@LedgerkeyArray = N'5E743F49-4AE1-442C-A33D-000590592472;7AA73D9E-96C7-4183-B7DA-0027A9CFA8D0;F541DFD6-A570-415A-8C4C-09C001F74775;5705F61A-0915-4937-A948-0A2C8C916898'

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-02-22 : 15:14:24

spot on, sometimes its the easy things that catch us out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources