Data Driven Subscriptions Updated

Question

Data Driven Subscriptions Updated

Viewing 15 posts - 61 through 75 (of 90 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Matthew Lehn (3/25/2010)
Don't use the UPDATETEXT method. I recall people saying they were having trouble with that method. Refer to sample SQL given on page 5 and at http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html for an alternate using the XML data type. If you still have issues, let me know and I'll see what I can do as time permits.

Thank Matthew for keeping up on this. I'm so busy spinning plates these days that I can't get back to this thread as often as I'd like. I'm alao not involved with RS that much any more so it's a little rusty in my head.....So thanks a ton for picking up my slack....:-D

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Mark F-428640 Hall of Fame Points: 3659 More actions · Answer 2

Thanks Mathew this worked for me in SQL 2008. I really appreciate you guys providing this information was a great help. Took a little time to wrap my head around it but it works.

Thanks,

Mark Gamble Valued Member Points: 70 More actions · Answer 3

Jason, Any word as yet on if this is supported in SSRS 2008.... I like it... but having issues getting it to work. Any assistance you could render would be much appreciated.

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 4

Mark Gamble (6/3/2010)
Jason, Any word as yet on if this is supported in SSRS 2008.... I like it... but having issues getting it to work. Any assistance you could render would be much appreciated.

This does work in SSRS 2008 with a few changes from the original, as discussed in prior messages in this forum. The key is to use the XML format and avoid the UPDATETEXT DDL command.

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

Mark Gamble Valued Member Points: 70 More actions · Answer 5

Thaks Matt for the info.... I have it all setup with the subscription...the function, the proc and the new table on the report server database of the hosting website....When I go to run the sample code

DECLARE@SubscriptionID uniqueidentifier,

@parameterNameLIST nvarchar(4000), -- pipe delimeted

@parameterValueLIST nvarchar(4000), -- pipe delimeted

@ExtensionSettingNameLIST nvarchar(4000),

@ExtensionSettingValueLIST nvarchar(4000),

@exitCd int ,

@exitMsg nvarchar(255),

@exitCode int ,

@exitMessage nvarchar(255)

exec [Custom_data_driven_subscription]

@SubscriptionID = '56YB32BA-BEB2-4066-B0FC-BB7691E6ED38',

@parameterNameLIST = 'REQUESTID',

@parameterValueLIST = '5715', -- POPULATE THIS FIELD

@ExtensionSettingNameLIST = 'TO|Subject',

@ExtensionSettingValueLIST = 'A016668|@ReportName was executed at @ExecutionTime', -- POPULATE THIS FIELD

@exitCode = @exitCD out,

@exitMessage = @exitMSG out

select @exitCD, @exitMSG

I get this error message

Msg 8114, Level 16, State 5, Procedure Custom_data_driven_subscription, Line 0

Error converting data type varchar to uniqueidentifier.

(1 row(s) affected)

Any ideas????

Mark Gamble Valued Member Points: 70 More actions · Answer 6

Mark Gamble (6/3/2010)
Thaks Matt for the info.... I have it all setup with the subscription...the function, the proc and the new table on the report server database of the hosting website....When I go to run the sample code
I get this error message
Msg 8114, Level 16, State 5, Procedure Custom_data_driven_subscription, Line 0
Error converting data type varchar to uniqueidentifier.
(1 row(s) affected)
Any ideas????

If you can help please let me know..

Sorry for the nasty way I presented my post prior to this one...

Thx Mark

cgrammont SSC Veteran Points: 207 More actions · Answer 7

I'm not sure why but SQL Server doesn't think that's a legitmate GUID.

This works:

declare @aTest uniqueidentifier

set @aTest = 'B85E62C3-DC56-40C0-852A-49F759AC68FB'

select @aTest

But this doesn't:

declare @aTest uniqueidentifier

set @aTest = '56YB32BA-BEB2-4066-B0FC-BB7691E6ED38'

select @aTest

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 8

The UID needs to be hexadecimal. As such, there can't be a "Y" in there, hence the reason for the error. Not sure how the "Y" got into the sample provided above, lol. Regardless, the SubscriptionID pulled from the SQL DB will have the correct value.

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

JayK SSCrazy Points: 2679 More actions · Answer 9

Hi,

I have implemented this solution and it works great sending emils with PDF attachment but I can't seem to get the email body to include the new line character.

Here is how I am calliing the sp:

DECLARE@return_value int,

@exitCode int,

@exitMessage nvarchar(255)

EXEC@return_value = [dbo].[ExecuteSubscribedReport]

@scheduleName = N'As Run Errors',

@emailTO = xxxxxx,

@emailCC = xxxxxx,

@emailReplyTO = xxxxxx,

@emailBODY = ?,

@parameterName = N'Channel',

@parameterValue = ?,

@sub = ?,

@renderFormat = N'PDF',

@exitCode = @exitCode OUTPUT,

@exitMessage = @exitMessage OUTPUT

I am passing in a string to the emailBODY parameter with tags but all my text comes out on one line in the email body - can't seem to get this to wotk :doze:

JayK SSCrazy Points: 2679 More actions · Answer 10

JayK

SSCrazy

Points: 2679

August 16, 2011 at 2:00 pm

#1369312

All Ok - used and worked OK 🙂

dguthrie_2010 SSC Veteran Points: 207 More actions · Answer 11

Hi All,

Thank you Jason for this article, it has been very helpful.

We are having a similar problem to the one JayK described on 8/16/2011 regarding formatting the body of the email. Everything else works fine.

Similar to JayK, we are passing a string to @emailBODY with HTML "line break" tags to separate paragraphs but no matter what we do, the text in the body of the email displays as one long string.

Apparently JayK was able to get this to work. Can JayK or anyone else shed any light on this?

Thank you.

John

cgrammont SSC Veteran Points: 207 More actions · Answer 12

Was JayK's solution. Did it not work for you?

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 13

dguthrie_2010 (11/29/2011)
Hi All,
Thank you Jason for this article, it has been very helpful.
We are having a similar problem to the one JayK described on 8/16/2011 regarding formatting the body of the email. Everything else works fine.
Similar to JayK, we are passing a string to @emailBODY with HTML "line break" tags to separate paragraphs but no matter what we do, the text in the body of the email displays as one long string.
Apparently JayK was able to get this to work. Can JayK or anyone else shed any light on this?
Thank you.
John

Paste the @emailBody value here within code .. /code tags.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

dguthrie_2010 SSC Veteran Points: 207 More actions · Answer 14

This is what we are testing:

@emailBODY = 'first second third'

cgrammont SSC Veteran Points: 207 More actions · Answer 15

@emailBODY = 'first

 

second

 

third'