September 2, 2015 at 10:09 am
Hi all,
and nice to be part of this forum I hope I can assist and be assisted when needed.
Requirement :
I want to build a job that checks every X hours all the records of a database that have a specific status (=1)
When there are some it takes it one by one (through cursor)
Passes some parameters to an SP
And sends the results in a file with DbMail
My only issue is passing the parameters to the SP as string
Here is the code which I hope it is quite easy to understand:
Declare @Q1 as nvarchar(max)
Declare @ID uniqueidentifier
Declare @SecondID uniqueidentifier
Declare @Title nvarchar(30)
Declare @RDate datetime
Declare @IsN bit
DECLARE EmailOpsCursor CURSOR FOR
Select id, sid, [title], rdate2, isnotification FROM tblReport where status = 1 order by rdate2 asc
OPEN EmailOpsCursor
FETCH NEXT FROM EmailOpsCursor INTO @ID, @SecondId ,@Title , @RDate , @IsN
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Now I want to pass these parameters to an SP which will create the desire format
-- The results will be exported in a file and send to mail
-- Problem is passing the SP parameters as string since some are UIDs
SET @Q1 = N'exec RPT_MailOps'+@ID+','+@SecondId+','+@Title+','+@Rdate+','+@IsN+'' --THIS IS THE IDEA WHICH FAILS
EXEC msdb..sp_send_dbmail
@profile_name='DBMailAdmin',
@recipients='blah@blahblah.com',
@query = @Q1,
@subject='Automated Report Received',
@body='',
@attach_query_result_as_file = 1 ;
FETCH NEXT FROM EmailOpsCursor INTO @ID, @SecondId ,@Title , @RDate , @IsN
END
Close EmailOpsCursor
Deallocate EmailOpsCursor
Many thanks in advance for any help or ideas !
Cheers,
Alex
September 2, 2015 at 12:30 pm
The alternative I thought while driving home, is to skip the SP , write the SP code in the cursor, fill a temp table and select from that table in the DBMail code.
I think however that the SP and smaller cursor code will be easier to maintain, than creating yet another temp table. Performance wise I don't care that much as it will be max 20 to 30 rows each time.
If I someone comes with an idea to pass the parameters as in displayed on my example I would love to hear it !
Cheers,
Alex
September 2, 2015 at 12:51 pm
When you concatenate strings, you must insure that higher-precedence data types are first converted to strings. Try something like this:
SET @Q1 = N'exec RPT_MailOps '''+CAST(@ID AS nvarchar(36))+''','''+CAST(@SecondId AS nvarchar(36))+''','''+
@Title+''','''+CONVERT(nvarchar(8),@Rdate,112)+''','+CAST(@IsN AS nchar(1))+';'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 2, 2015 at 3:55 pm
Many, many thanks. Humiliating enough (for me) it was that simple. I think I tried almost everything else apart from that.
The only change I did (which of course you couldn't know) is the rdate had to be used with time in 24h format so I did the conversion like this :
CONVERT(nvarchar(25),@Rdate,120)
Cheers,
Alex
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply