Msg 22049-Error executing extended stored procedure: Invalid Parameter

Hi Guys,

It's time to learn from the error which I went today. If you remember I have posted here a backup script. The purpose of the script is to take Database backup along with few options. The script has undergone through few changes now.

But here I want to discuss an issue which I faced during implementing this code on one of new environment.

Scenario:

1. We wanted to configure this Database Backup Script in a new Server. There where few Databases on the Server. What we noticed was one of the Database name in the that Server was having 60 character (It was an Share Point Application Database).
2. And due to that we were getting this (Msg 8152, Level 16, State 2, Line 1 String or binary data would be truncated) error message.
3. For sake, we had changed the variable to Nvarchar(Max).
4. After changing this variable there was another error popped up.Error was (Msg 22049, Level 15, State 0, Line 0 Error executing extended stored procedure: Invalid Parameter).
5. Please see the Figure 1 snapshot.
6. But in between Step 3 and 4; I was executing the SP from a Job and was unable to see this error message. As some of the error message were truncated. So I ran the SP manually from SSMS and I got to know about this error.
7. Looking into the error, I came to know there was a problem in my last portion of the code.That means First portion 'To take DB Backup' was running fine and the Second part 'To delete the old Backup' was creating issue as I was using Extended Stored Procedure (i.e dbo.xp_delete_file)
8. Not sure what was the issue after doing little research found an partial solution and then tested the code with some changes in variable and it was successful.
9. Please see the demo code in Figure 2.  

Figure 1

Figure 2

Can you find any difference in Figure 1 and Figure 2?? (Of course apart from the Error)

The solution for the error was @path variable in the above code has datatype Nvarchar(Max) and if that is called from the extended Stored Procedure like in our case from dbo.xp_delete_file it throws the above error.

But if we pass the datatype as Nvarchar() like in Figure 2 and passed to Extended Stored Procedure the error is solved. 

So do remember if we are passing any variable to Extended Stored Procedure do defined a fixed value to the variable.

Keep Learning and Enjoy Learning!!!