Embedding literals in RDL that will come through in HTML

I am trying to create some reports that when rendered in HTML are supposed to have some interactivity built into them. I understand that there aren't any facilities in RDL itself that would help facilitate this process for me, but is there any way to embed some literal strings that will come through in the HTML?

The items I'm trying to finesse through are <script> blocks, checkboxes, and <object> tags. When I use the designer to put these items into textboxes, they get encoded with the HTML values (&gt; instead of >, etc...) so that the code is displayed instead of interpreted.

I thought maybe there would be a VB function that I can wrap around the HTML to prevent it from being encoded so that when the report is displayed on the screen that the interactive elements are there.

Any ideas?

I think this relates to formatting text within a cell, which does not appear to be possible. Perhaps some sort of javascript which will change the innerHTML of the report document?|||

Thanks for the response.

I think that I have solved the problem for my situation. Here's what I've discovered so far:

If you use a textbox control, you can set the value of it to an HTML snippet (I'm using an object tag with some param tags as well as checkbox controls). When the report is rendered to HTML, the HTML that was embedded into the report shows as source on the page. I've taken that HTML, saved it off to a file and modified it using some .NET classes. Specifically the HTTPUtility.HtmlDecode function. That function will take the HTML file and undo all of the encoding that Reporting Services has done to your HTML. I also did a small test where I inserted a "&lt;" character somewhere else in the HTML document to simulate something that may actually occur. When I decoded the file that time, all of the encoded HTML snippets were properly decoded, and the less than sign was left alone.

If someone were rendering using the Web Service APIs for Reporting Services, this would work because you are given the stream back. In the case of using a WebForms ReportViewer though, it might be more difficult.

I hope this helps anyone who has a similar problem.

|||Sounds interesting, can you post the code or a snippet of it?
This may help MS to build as a feature in the next SP as this seems like a pretty common thing (how do I embed html/change style within a text box)|||

Hi Ken,

i'm new with sql reporting service, may i know anyway to make my report export with html format and allow me to send to customer using the ms outlook?

my report contain chart and also table, and image.

i did trying to export using mhtml, but after i send it using the email. at my desktop i able to see it. but my manager not able to read all the image and chart.

thanks for your help.

|||

Hello,

Can you please share the code or detail the approach you have taken?

Thanks

Embedding literals in RDL that will come through in HTML

I am trying to create some reports that when rendered in HTML are supposed to have some interactivity built into them. I understand that there aren't any facilities in RDL itself that would help facilitate this process for me, but is there any way to embed some literal strings that will come through in the HTML?

The items I'm trying to finesse through are <script> blocks, checkboxes, and <object> tags. When I use the designer to put these items into textboxes, they get encoded with the HTML values (&gt; instead of >, etc...) so that the code is displayed instead of interpreted.

I thought maybe there would be a VB function that I can wrap around the HTML to prevent it from being encoded so that when the report is displayed on the screen that the interactive elements are there.

Any ideas?

I think this relates to formatting text within a cell, which does not appear to be possible. Perhaps some sort of javascript which will change the innerHTML of the report document?|||

Thanks for the response.

I think that I have solved the problem for my situation. Here's what I've discovered so far:

If you use a textbox control, you can set the value of it to an HTML snippet (I'm using an object tag with some param tags as well as checkbox controls). When the report is rendered to HTML, the HTML that was embedded into the report shows as source on the page. I've taken that HTML, saved it off to a file and modified it using some .NET classes. Specifically the HTTPUtility.HtmlDecode function. That function will take the HTML file and undo all of the encoding that Reporting Services has done to your HTML. I also did a small test where I inserted a "&lt;" character somewhere else in the HTML document to simulate something that may actually occur. When I decoded the file that time, all of the encoded HTML snippets were properly decoded, and the less than sign was left alone.

If someone were rendering using the Web Service APIs for Reporting Services, this would work because you are given the stream back. In the case of using a WebForms ReportViewer though, it might be more difficult.

I hope this helps anyone who has a similar problem.

|||Sounds interesting, can you post the code or a snippet of it?
This may help MS to build as a feature in the next SP as this seems like a pretty common thing (how do I embed html/change style within a text box)|||

Hi Ken,

i'm new with sql reporting service, may i know anyway to make my report export with html format and allow me to send to customer using the ms outlook?

my report contain chart and also table, and image.

i did trying to export using mhtml, but after i send it using the email. at my desktop i able to see it. but my manager not able to read all the image and chart.

thanks for your help.

|||

Hello,

Can you please share the code or detail the approach you have taken?

Thanks

sql

Embedding literals in RDL that will come through in HTML

I am trying to create some reports that when rendered in HTML are supposed to have some interactivity built into them. I understand that there aren't any facilities in RDL itself that would help facilitate this process for me, but is there any way to embed some literal strings that will come through in the HTML?

The items I'm trying to finesse through are <script> blocks, checkboxes, and <object> tags. When I use the designer to put these items into textboxes, they get encoded with the HTML values (&gt; instead of >, etc...) so that the code is displayed instead of interpreted.

I thought maybe there would be a VB function that I can wrap around the HTML to prevent it from being encoded so that when the report is displayed on the screen that the interactive elements are there.

Any ideas?

I think this relates to formatting text within a cell, which does not appear to be possible. Perhaps some sort of javascript which will change the innerHTML of the report document?|||

Thanks for the response.

I think that I have solved the problem for my situation. Here's what I've discovered so far:

If you use a textbox control, you can set the value of it to an HTML snippet (I'm using an object tag with some param tags as well as checkbox controls). When the report is rendered to HTML, the HTML that was embedded into the report shows as source on the page. I've taken that HTML, saved it off to a file and modified it using some .NET classes. Specifically the HTTPUtility.HtmlDecode function. That function will take the HTML file and undo all of the encoding that Reporting Services has done to your HTML. I also did a small test where I inserted a "&lt;" character somewhere else in the HTML document to simulate something that may actually occur. When I decoded the file that time, all of the encoded HTML snippets were properly decoded, and the less than sign was left alone.

If someone were rendering using the Web Service APIs for Reporting Services, this would work because you are given the stream back. In the case of using a WebForms ReportViewer though, it might be more difficult.

I hope this helps anyone who has a similar problem.

|||Sounds interesting, can you post the code or a snippet of it?
This may help MS to build as a feature in the next SP as this seems like a pretty common thing (how do I embed html/change style within a text box)|||

Hi Ken,

i'm new with sql reporting service, may i know anyway to make my report export with html format and allow me to send to customer using the ms outlook?

my report contain chart and also table, and image.

i did trying to export using mhtml, but after i send it using the email. at my desktop i able to see it. but my manager not able to read all the image and chart.

thanks for your help.

|||

Hello,

Can you please share the code or detail the approach you have taken?

Thanks

embedding a DTS package which contains a 'Copy SQL Server Objects Task'

Hi,

I am getting an error when doing the above.

I create a new SSIS package, drag in an Execute DTS 2000 Package Task, select and embed a DTS package which consists only one one task (as above), and then change the source & destination details (svr + user/pwd). Then when I go to the Copy tab, I get the following error when I hit Select Objects, to view the objects which the embedded DTS package should copy:

SQL-DMO error 21776: general error.

On further inspection, none of the objects selected for copy within the atomic/original DTS package, remain selected for copy within the embedded DTS package.

I have googled to search for an answer to this one, but to no avail. Any ideas would be greatly welcomed.

Thanks,

Tamim.

Off the top of my head I am not sure...but I wonder if there is more to this or perhaps what the real goal is?

I ask because if your only dealing with one transfer task from a dts package, then why not just create a new SSIS package using the new ssis transfer tasks? Prehaps you have 100s of said DTS packages, and you need to keep them going rather than rebuild all. However, we should always keep in mind that while the Execute DTS 2000 Package Task is nice for 'keeping things running' from the old version, over time you will want to rebuild any DTS pacakge as a 'native' SSIS 2005 pacakge allows you to do a lot more AND DTS is considered a deprecated technology, which does mean its fading form the picure. If you want you can read more about that here.

http://msdn2.microsoft.com/en-us/library/ms403408.aspx

Hope that helps.

|||

'...if your only dealing with one transfer task from a dts package, then why not just create a new SSIS package using the new ssis transfer tasks?...'

Sure. And that's exactly what I did, thus reducing the problem to being merely academic - but it's still there....

Thanks Craig,

Emailing results from query

Hi,
I just installed sql05 ent version and set up the DB Mail and ran a test
mail without a problem. I also managed to create a job which would execute a
query. I'd like now the sql server send to several email addresses the
results of the query and here where I'm stuck.
HOWDO you do it?
TIA
Ana
Read about sp_send_dbmail.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test mail without a problem. I
> also managed to create a job which would execute a query. I'd like now the sql server send to
> several email addresses the results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana
|||Hi,
This atricle may be of use:
http://builder.com.com/5100-6388-6164310-2.html#Listing%20B
Dan
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test
> mail without a problem. I also managed to create a job which would execute
> a query. I'd like now the sql server send to several email addresses the
> results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana

Emailing results from query

Hi,
I just installed sql05 ent version and set up the DB Mail and ran a test
mail without a problem. I also managed to create a job which would execute a
query. I'd like now the sql server send to several email addresses the
results of the query and here where I'm stuck.
HOWDO you do it?
TIA
AnaRead about sp_send_dbmail.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test m
ail without a problem. I
> also managed to create a job which would execute a query. I'd like now the
sql server send to
> several email addresses the results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana|||Hi,
This atricle may be of use:
http://builder.com.com/5100-6388-61...tml#Listing%20B
Dan
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test
> mail without a problem. I also managed to create a job which would execute
> a query. I'd like now the sql server send to several email addresses the
> results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana

Emailing results from query

Hi,
I just installed sql05 ent version and set up the DB Mail and ran a test
mail without a problem. I also managed to create a job which would execute a
query. I'd like now the sql server send to several email addresses the
results of the query and here where I'm stuck.
HOWDO you do it?
TIA
AnaRead about sp_send_dbmail.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test mail without a problem. I
> also managed to create a job which would execute a query. I'd like now the sql server send to
> several email addresses the results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana|||Hi,
This atricle may be of use:
http://builder.com.com/5100-6388-6164310-2.html#Listing%20B
--
Dan
"Ana" <ananospam@.yahoo.es> wrote in message
news:E878A2A8-B75A-4040-A252-DE709E44A372@.microsoft.com...
> Hi,
> I just installed sql05 ent version and set up the DB Mail and ran a test
> mail without a problem. I also managed to create a job which would execute
> a query. I'd like now the sql server send to several email addresses the
> results of the query and here where I'm stuck.
> HOWDO you do it?
> TIA
> Ana

email with trigger inserted row

I'm trying to create a trigger which, upon a row insert, an email is sent containing some of the inserted row information. Apparently the built-in stored procedure for email starts it's own session, so I can't use the local variables of the trigger. My solution was to create a temp table, copy the inserted row in, then refer to that from the email SP, then drop the table at the end. When I try to insert a row, it runs for a very long time, then gives an error message saying that it timed out. It was suggested I add COMMIT TRANSACTION in to force it to commit the data to the temp table. Doing this, it gives an error, saying "The transaction ended in the trigger. The batch has been aborted. Mail queued." In the table view, I'm forced to hit esc and abort the insertion. However, if I refresh the table, the row has been inserted ok, and the email does get sent with the inserted row.

Code:
--

CREATE TRIGGER [newTicket_notify]

ON [sysdba].[TICKET]

AFTER INSERT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

create table insertedTemp

(

TICKETID char(12),

ACCOUNTID char(12),

ACCOUNT varchar(128),

DIVISION varchar(64),

EMAIL varchar(128),

MAINPHONE varchar(32)
)

declare @.ticketID char(12), @.accountID char(12), @.account varchar(128),

@.division varchar(64), @.email varchar(128), @.mainphone varchar(32)

select @.ticketID = TICKETID, @.accountID = ACCOUNTID

from inserted

select @.account = ACCOUNT, @.division = DIVISION, @.email = EMAIL,

@.mainphone = MAINPHONE

from sysdba.ACCOUNT

where @.accountID = ACCOUNTID

insert into insertedTemp values (@.ticketID, @.accountID, @.account,

@.division, @.email, @.mainphone)

commit transaction

EXEC msdb.dbo.sp_send_dbmail

@.profile_name = 'Test',

@.recipients = 'user@.test.com',

@.body = 'Inserted row info',

@.subject = 'DB Test',

@.query = 'select TICKETID [Ticket ID], ACCOUNTID [Account ID],

ACCOUNT [Account], DIVISION [Division], EMAIL [Email],

MAINPHONE [Mainphone]

from dbo.insertedTEMP',

@.execute_query_database = 'database',

@.attach_query_result_as_file = '0';

drop table insertedTEMP

END

You're really doing a little too much with the trigger here. A trigger should be a quick thing.

Have you considered using Service Broker for this? Or even just a SQL Job which looks for new rows and does the emailing there? You might not get an immediate response (although if you make the SQL Job run every 10 seconds it will feel pretty immediate), but at least your initial insertion will complete happily.

Rob|||

SQL Server 2k5 uses the Sevice broker already for mail sending.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Agreed. And it will make the trigger you write look so much easier. Can you send multiple rows to a Service Broker queue at once? If not I would still create an email queue table and build a job to send emails. Emails aren't immediate things no matter what, and it will be a lot easier to debug an email queue not working if you don't have the added excitement of your ticket system failing because of it.sql

E-Mail User When StoredProc Fails

Hi,

I want to e-mail a user when a Stored Proc fails, what is the best way to do this? I was going to create a DTS package or is this too complicated?

Also, the Stored Proc inserts data from one table to another, I would like to use Transactions so that if this fails it rolls back to where it was, I'm not sure of the best way to go about this. Could anyone possibly point me in the right direction? Here's a copy of some of the stored procedure to give an idea of what I am doing:

-- insert data into proper tables with extract date added
INSERT INTO tbl_Surgery
SELECT
SurgeryKey,
GETDATE(),
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey,
@.practiceCode --SUBSTRING(SurgeryKey,PATINDEX('%.%',SurgeryKey)+1, 5)
FROM tbl_SurgeryIn

INSERT INTO tbl_SurgerySlot
SELECT
SurgerySlotKey,
GETDATE(),
SurgeryKey,
Length,
Deleted,
StartTime,
RestrictionDays,
Label,
IsRestricted,
@.practiceCode
FROM tbl_SurgerySlotIn

INSERT INTO tbl_Appointment
SELECT
AppointmentKey,
GETDATE(),
SurgerySlotKey,
PatientKey,
Cancelled,
Continuation,
Deleted,
Reason,
DateMade
FROM tbl_AppointmentIn

-- empty input tables
DELETE FROM tbl_SurgeryIn
DELETE FROM tbl_SurgerySlotIn
DELETE FROM tbl_AppointmentIn

Any help would me very much appreciated,

ThanksSomething like this should work:

CREATE PROCEDURE ProcName

AS

BEGIN TRANSACTION transaction_1

DECLARE @.error_handle VARCHAR(255)

-- insert data into proper tables with extract date added
INSERT INTO tbl_Surgery
SELECT
SurgeryKey,
GETDATE(),
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey,
@.practiceCode --SUBSTRING(SurgeryKey,PATINDEX('%.%',SurgeryKey)+1, 5)
FROM tbl_SurgeryIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_Surgery insert.'
GOTO error_handle
END

INSERT INTO tbl_SurgerySlot
SELECT
SurgerySlotKey,
GETDATE(),
SurgeryKey,
Length,
Deleted,
StartTime,
RestrictionDays,
Label,
IsRestricted,
@.practiceCode
FROM tbl_SurgerySlotIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_SurgerySlot insert.'
GOTO error_handle
END

INSERT INTO tbl_Appointment
SELECT
AppointmentKey,
GETDATE(),
SurgerySlotKey,
PatientKey,
Cancelled,
Continuation,
Deleted,
Reason,
DateMade
FROM tbl_AppointmentIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_Appointment insert.'
GOTO error_handle
END

-- empty input tables
DELETE FROM tbl_SurgeryIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_Surgery delete.'
GOTO error_handle
END

DELETE FROM tbl_SurgerySlotIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_SurgerySlotIn delete.'
GOTO error_handle
END

DELETE FROM tbl_AppointmentIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_AppointmentIn delete.'
GOTO error_handle
END

end_procedure:
COMMIT TRANSACTION transaction_1 --Commits transactions if no errors occurred.
RETURN 0 --Indicates succcess.

error_handle
ROLLBACK TRANSACTION transaction_1
RAISERROR(@.error_handle,16,1)
EXEC xp_sendmail 'user@.mail.com',@.error_handle
RETURN 1

Alternately, if you are running this from a job, you can strip out the xp_sendmail and just have it send email on failure. The RETURN 1 with the RAISERROR will indicate failure.|||I would strongly suggest creating a job to run the stored procedure, and having the job email you on failure. It is easy to do, and relatively foolproof!

-PatP|||Thanks, that's great, I'll give it a go. So the RAISERROR and RETURN 1 are just a way of letting SQL know that the procedure has failed??

The only thing I'm confused/worried about is the best place to put the BEGIN TRANSACTION and the end_procedure code. The stored procedure I've inherited has rather a lot of BEGINS/ENDS so I'm worried about confusing it, here's a copy of the original, any indication you could give me would really help:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE sproc_48hrAccess_Upload

AS

SET DATEFORMAT dmy

DECLARE @.practiceCode char(5)
DECLARE @.server varchar(255)
DECLARE @.inPath varchar(255)
DECLARE @.archiveBase varchar(255)
DECLARE @.archivePath varchar(255)
DECLARE @.zipPath varchar(255)
DECLARE @.cmdshell varchar(255)
DECLARE @.result int
DECLARE @.date varchar(10)

SET @.server = 'MURDOCH'
SET @.inPath = 'E:\48hrAccess\48hrDataIn\'
SET @.archiveBase = 'E:\48hrAccess\48hrDataArchive\'
SET @.zipPath = 'C:\Progra~1\WinZip\' --C:\Program Files\Winzip\
SET @.date = CONVERT(varchar(2),DATEPART(dd,GETDATE()))+CONVERT (varchar(2),DATEPART(mm,GETDATE()))+CONVERT(char(4 ),DATEPART(yy,GETDATE()))

-- upload for each practice in tbl_Practice
DECLARE allPractices CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT practiceCode FROM tbl_Practice
OPEN allPractices
FETCH NEXT FROM allPractices INTO @.practiceCode

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.archivePath = @.archiveBase+@.practiceCode+'\'
-- copy files into archive folder
SET @.cmdshell = 'MOVE '+@.inPath+'48hr_'+@.practiceCode+'_'+@.date+'.zip '+@.archivePath
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

-- unzip file
SET @.cmdshell = @.zipPath+'WZUNZIP -ybc -o -sPASSWORD '+@.archivePath+'48hr_'+@.practiceCode+'_'+@.date+'.z ip '+@.archivePath
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

-- copy data files into upload tables
SET @.cmdshell = 'ECHO ** BEGIN Upload '+CONVERT(varchar,GETDATE())+' ******************************* >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

SET @.cmdshell = 'ECHO xv_Surgery.dat >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT
SET @.cmdshell = 'bcp GMS_48hrAccess..tbl_SurgeryIn in '+@.archivePath+'xv_Surgery.dat -n -V65 -t"||" -r"|||\n" -S'+@.server+' -T >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

SET @.cmdshell = 'ECHO xv_SurgerySlot.dat >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT
SET @.cmdshell = 'bcp GMS_48hrAccess..tbl_SurgerySlotIn in '+@.archivePath+'xv_SurgerySlot.dat -n -V65 -t"||" -r"|||\n" -S'+@.server+' -T >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

SET @.cmdshell = 'ECHO xv_Appointment.dat >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT
SET @.cmdshell = 'bcp GMS_48hrAccess..tbl_AppointmentIn in '+@.archivePath+'xv_Appointment.dat -n -V65 -t"||" -r"|||\n" -S'+@.server+' -T >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

-- clean up
SET @.cmdshell = 'DEL /F '+@.archivePath+'*.dat '+@.archivePath+'48hrAccess.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

-- update tbl_SurgerySlotDescription (will fire trigger if new labels appear)
INSERT INTO tbl_SurgerySlotDescription
SELECT DISTINCT @.practiceCode, ssi.Label, ssi.AutoFillMessage, ssi.IsBookable, null
FROM tbl_SurgerySlotIn ssi
WHERE SUBSTRING(ssi.SurgerySlotKey,PATINDEX('%.%',ssi.Su rgerySlotKey)+1,5) = @.practiceCode
AND ssi.Label NOT IN (
SELECT Label
FROM tbl_SurgerySlotDescription
WHERE PracticeCode = @.practiceCode
)

-- insert data into proper tables with extract date added
INSERT INTO tbl_Surgery
SELECT
SurgeryKey,
GETDATE(),
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey,
@.practiceCode --SUBSTRING(SurgeryKey,PATINDEX('%.%',SurgeryKey)+1, 5)
FROM tbl_SurgeryIn

INSERT INTO tbl_SurgerySlot
SELECT
SurgerySlotKey,
GETDATE(),
SurgeryKey,
Length,
Deleted,
StartTime,
RestrictionDays,
Label,
IsRestricted,
@.practiceCode
FROM tbl_SurgerySlotIn

INSERT INTO tbl_Appointment
SELECT
AppointmentKey,
GETDATE(),
SurgerySlotKey,
PatientKey,
Cancelled,
Continuation,
Deleted,
Reason,
DateMade
FROM tbl_AppointmentIn

-- empty input tables
DELETE FROM tbl_SurgeryIn
DELETE FROM tbl_SurgerySlotIn
DELETE FROM tbl_AppointmentIn

FETCH NEXT FROM allPractices INTO @.practiceCode
END

CLOSE allPractices
DEALLOCATE allPractices

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Email subscription failing when report is included in email.

Hey All,

I am using SQL Server RS 2000 and I am running an issue. I create a subscription to email a report to a list of users. Everything works fine if I just setup the subscription so that a link to the report is to be sent in the email. But if I tell the subscription to include the report in the email, the sending of the email fails. What am I doing wrong? Is this a known issue? If so, is there a patch for it?

Thanks,

Jean

I ran into similar emailing issues a while ago when I created a data-driven subscription. I changed the RenderFormat from "Web archive" to "Web page for IE 5.0 or later" and it worked for me.

Email Subscription

Hi

I have uploaded a report onto my 2005 Reporting Manager and want to create a daily email of the report... How do I set up the email facility? Can someone give me a link?

I not sure what to do because the company uses Exchange Server and not SMTP....

Thanks

Hi,

First you need to set up the email settings for SSRS. If you use exchange server, that is fine, so you can add as your SMTP server.

Open Reporting Services Configuration manager from start menu/Microsoft SQL server 2005/Configuration tools.

Connect to your SSRS instance.

Choose email settings from the left sidebar of the application.

Specify the "Sender Address" as eg: noreply@.mydomain.tld

SMTP server name should be your exchange server.

If your exchange server is not letting anonymous sending, then assign the appropiate permission to the SSRS service account to send email through your exchange server.

Regards,

Janos

|||

thanks for the quick reply... I have alreasy tried what u sed but got the following error ;(

Failure sending mail: The transport failed to connect to the server.

does it matter that the mail server n reporting services server are 2 different machines?

thanks again

|||

Hi,

Try to ask your sys admin to add the appropiate permission to your SSRS service account on the exchange server.

I got also 2 separate server for SSRS and Exchange

Regards,

Janos

|||how do I set the permission to th SSRS account...soz Im new at networking and support stuff! ;'(|||

You can assign the permission on the SMTP virtual server in the Exchange management console,

maybe you should set up anonymous submit via teh smtp, then you do not need to specify explicit permission.

Email Subscription

Hi There,
Is it posible to create a subscribers list during run time of the report?
How can I specify that while creating a sbuscription in Report Manager?.
For Example,
I've a report which takes 3 parameters. Date1,Date2,EmpID.
The report has to send to only one user thru depending on the EmpID parameter. How can I specify the query while creating the subscription?.
If this can not be done thru subscription. Can this can be done on DeliveryExtension. Any code samples?.
Any help would be much appreciated!.
Thanks in advance.Data Driven Subscriptions can accomplish this. Look at the following
article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_manager_v1_9syq.asp
You can not dynamically change the delivery extension though.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kavi" <Kavi@.discussions.microsoft.com> wrote in message
news:26B225A6-AA5D-4630-ABA8-D119D3E03087@.microsoft.com...
> Hi There,
> Is it posible to create a subscribers list during run time of the report?
> How can I specify that while creating a sbuscription in Report Manager?.
> For Example,
> I've a report which takes 3 parameters. Date1,Date2,EmpID.
> The report has to send to only one user thru depending on the EmpID
parameter. How can I specify the query while creating the subscription?.
> If this can not be done thru subscription. Can this can be done on
DeliveryExtension. Any code samples?.
> Any help would be much appreciated!.
> Thanks in advance.
>|||Hi Daniel,
I've tried this before I put this query on Newsgroup.
I find the difficulty in specifying the query in the subscription:
select email from tbl_subscribers where uid=@.EmpID(Which is Report Parameter).
can I do this, if so how can I specify the query. The above query is not validating & gives an error message "@.EmpID must be declared".
Thnaks.
"Daniel Reib [MSFT]" wrote:
> Data Driven Subscriptions can accomplish this. Look at the following
> article:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_manager_v1_9syq.asp
> You can not dynamically change the delivery extension though.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> news:26B225A6-AA5D-4630-ABA8-D119D3E03087@.microsoft.com...
> > Hi There,
> >
> > Is it posible to create a subscribers list during run time of the report?
> >
> > How can I specify that while creating a sbuscription in Report Manager?.
> >
> > For Example,
> >
> > I've a report which takes 3 parameters. Date1,Date2,EmpID.
> >
> > The report has to send to only one user thru depending on the EmpID
> parameter. How can I specify the query while creating the subscription?.
> >
> > If this can not be done thru subscription. Can this can be done on
> DeliveryExtension. Any code samples?.
> >
> > Any help would be much appreciated!.
> >
> > Thanks in advance.
> >
>
>|||Sorry. I hope I can not use Data Driven Subscription, since I don't have "Enterprise Edition of SQL Server 2000". I've only Standard Edition.
Is there any other way I can accomplish the task & any samples?.
Thanks.
"Daniel Reib [MSFT]" wrote:
> Data Driven Subscriptions can accomplish this. Look at the following
> article:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_manager_v1_9syq.asp
> You can not dynamically change the delivery extension though.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> news:26B225A6-AA5D-4630-ABA8-D119D3E03087@.microsoft.com...
> > Hi There,
> >
> > Is it posible to create a subscribers list during run time of the report?
> >
> > How can I specify that while creating a sbuscription in Report Manager?.
> >
> > For Example,
> >
> > I've a report which takes 3 parameters. Date1,Date2,EmpID.
> >
> > The report has to send to only one user thru depending on the EmpID
> parameter. How can I specify the query while creating the subscription?.
> >
> > If this can not be done thru subscription. Can this can be done on
> DeliveryExtension. Any code samples?.
> >
> > Any help would be much appreciated!.
> >
> > Thanks in advance.
> >
>
>|||Unfortunately there is no real way to accomplish this in the Standard
Edition. You would have to create a subscription for each user.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kavi" <Kavi@.discussions.microsoft.com> wrote in message
news:1824340C-E14E-470F-835C-FE8DC3904ABF@.microsoft.com...
> Sorry. I hope I can not use Data Driven Subscription, since I don't have
"Enterprise Edition of SQL Server 2000". I've only Standard Edition.
> Is there any other way I can accomplish the task & any samples?.
> Thanks.
> "Daniel Reib [MSFT]" wrote:
> > Data Driven Subscriptions can accomplish this. Look at the following
> > article:
> >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_manager_v1_9syq.asp
> >
> > You can not dynamically change the delivery extension though.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> > news:26B225A6-AA5D-4630-ABA8-D119D3E03087@.microsoft.com...
> > > Hi There,
> > >
> > > Is it posible to create a subscribers list during run time of the
report?
> > >
> > > How can I specify that while creating a sbuscription in Report
Manager?.
> > >
> > > For Example,
> > >
> > > I've a report which takes 3 parameters. Date1,Date2,EmpID.
> > >
> > > The report has to send to only one user thru depending on the EmpID
> > parameter. How can I specify the query while creating the subscription?.
> > >
> > > If this can not be done thru subscription. Can this can be done on
> > DeliveryExtension. Any code samples?.
> > >
> > > Any help would be much appreciated!.
> > >
> > > Thanks in advance.
> > >
> >
> >
> >|||Ohh...Thnaks Daniel.
"Daniel Reib [MSFT]" wrote:
> Unfortunately there is no real way to accomplish this in the Standard
> Edition. You would have to create a subscription for each user.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> news:1824340C-E14E-470F-835C-FE8DC3904ABF@.microsoft.com...
> > Sorry. I hope I can not use Data Driven Subscription, since I don't have
> "Enterprise Edition of SQL Server 2000". I've only Standard Edition.
> >
> > Is there any other way I can accomplish the task & any samples?.
> >
> > Thanks.
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> > > Data Driven Subscriptions can accomplish this. Look at the following
> > > article:
> > >
> > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_manager_v1_9syq.asp
> > >
> > > You can not dynamically change the delivery extension though.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Kavi" <Kavi@.discussions.microsoft.com> wrote in message
> > > news:26B225A6-AA5D-4630-ABA8-D119D3E03087@.microsoft.com...
> > > > Hi There,
> > > >
> > > > Is it posible to create a subscribers list during run time of the
> report?
> > > >
> > > > How can I specify that while creating a sbuscription in Report
> Manager?.
> > > >
> > > > For Example,
> > > >
> > > > I've a report which takes 3 parameters. Date1,Date2,EmpID.
> > > >
> > > > The report has to send to only one user thru depending on the EmpID
> > > parameter. How can I specify the query while creating the subscription?.
> > > >
> > > > If this can not be done thru subscription. Can this can be done on
> > > DeliveryExtension. Any code samples?.
> > > >
> > > > Any help would be much appreciated!.
> > > >
> > > > Thanks in advance.
> > > >
> > >
> > >
> > >
>
>

Email report

Hi Freinds,
SQL2005 beta,
I create a subscription and want to email report based on a schedule. It
doesn't seems working, no error either.
Where can I track it down? Which config files keep the mail SMTP
information?
Thanks,
PatLook in the windows service log file in the LogFiles folder.
The file rsreportserver.config contains the email delivery extension
configuration.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patrick" <patriarck@.gmail.com> wrote in message
news:OQwr7TeHFHA.2704@.tk2msftngp13.phx.gbl...
> Hi Freinds,
> SQL2005 beta,
> I create a subscription and want to email report based on a schedule. It
> doesn't seems working, no error either.
> Where can I track it down? Which config files keep the mail SMTP
> information?
> Thanks,
> Pat
>

email on insert

Hi all,

I wanted sql server to shoot an email upon insert into a table. I treated a
trigger on that table as below.

CREATE TRIGGER [emailoninsert] ON [dbo].[table_name]
FOR INSERT
AS
exec sp_sendSMTPmail 'user@.user.com', 'New records are inserted in
table_name table', 'Please investigate and take necessary actions.',
@.cc='', @.BCC = '',
@.Importance=1,
@.Attachments='', @.HTMLFormat = 0,@.From =
'notification@.sqlserver.com'

Is this solution a good method?

Thanks,

Guju"Guju" <patelroshanr@.yahoo.com.au> wrote in message
news:42508c0c_1@.news.iprimus.com.au...
> Hi all,
> I wanted sql server to shoot an email upon insert into a table. I treated
a
> trigger on that table as below.
> CREATE TRIGGER [emailoninsert] ON [dbo].[table_name]
> FOR INSERT
> AS
> exec sp_sendSMTPmail 'user@.user.com', 'New records are inserted in
> table_name table', 'Please investigate and take necessary actions.',
> @.cc='', @.BCC = '',
> @.Importance=1,
> @.Attachments='', @.HTMLFormat = 0,@.From =
> 'notification@.sqlserver.com'
>
> Is this solution a good method?

No.

It will greatly slow down insert speeds.

> Thanks,
> Guju|||Guju wrote:
> Hi all,
> I wanted sql server to shoot an email upon insert into a table. I
treated a
> trigger on that table as below.
> CREATE TRIGGER [emailoninsert] ON [dbo].[table_name]
> FOR INSERT
> AS
> exec sp_sendSMTPmail 'user@.user.com', 'New records are inserted in
> table_name table', 'Please investigate and take necessary actions.',
> @.cc='', @.BCC = '',
> @.Importance=1,
> @.Attachments='', @.HTMLFormat = 0,@.From =
> 'notification@.sqlserver.com'
>
> Is this solution a good method?
> Thanks,
> Guju

It may be a better idea to create a script that checks for new records
in the table every x number of hours (run it as an sql agent job).
the script may save the last record id that it already saw in a table
for this purpose.
As mentioned above, the solution you implemented means the email is
sent at the expense of the insert statement , making it horribly slow.
My way you can also send one mail if 10 records were inserted in stead
of 10, with the data of all 10, which you may trust me is more useful
to the sorry person actually receiving these mails.

hope this helps.

Tzvika|||Is it possible for you to post a sample script..I am attempting to get
the same result as the author.|||Is it possible for you to post a sample script..I am attempting to get
the same result as the author.

E-mail delivery

Hi All,
I need to programatically create a report, and deliver it.
I managed to create the report and a schedule.
When I call CreateSubcription, it fails
(InvalidDataSourceCredentialSettingException is thrown)
the message is:
"The current action cannot be completed because the user data source
credentials that are
required to execute this report are not stored in the report server
database."
Any ideas ?Make sure you are using stored credentials on the data source the report
uses. This is required for subscription processing.
--
Adrian M.
MCP
"Geek 007" <holmes@.programmer.net> wrote in message
news:uML1ADLTFHA.2304@.tk2msftngp13.phx.gbl...
> Hi All,
> I need to programatically create a report, and deliver it.
> I managed to create the report and a schedule.
> When I call CreateSubcription, it fails
> (InvalidDataSourceCredentialSettingException is thrown)
> the message is:
> "The current action cannot be completed because the user data source
> credentials that are
> required to execute this report are not stored in the report server
> database."
> Any ideas ?
>
>
>

Email Attachment Problem

Hi all,
I create a subscription to send the report (in pdf format) to users once every week. The subscription works, all users manage to receive the email but some of them do not have the pdf file attached to the email. Anyone know what is wrong?

DarenSounds like a firewall setting blocking attachments on those emails.|||

Hi,

just wondering if you found a fix for this problem cause I have te same problem.

I noticed that the problem somehow connects to Outlook, cause when i retreve the mail in my Web-mailclient I get an attachment and in my version of Outlook (version 2003 English) there is the attachment and the size of the mail remains the same..

Lucas

|||Hi IjoNL,
I could not find any solution to it, but there's a temporary solution to this is ask the person to try and forward the email they received in outlook then they will find the attachment file there.

Daren

Email Attachment Problem

Hi all,
I create a subscription to send the report (in pdf format) to users once every week. The subscription works, all users manage to receive the email but some of them do not have the pdf file attached to the email. Anyone know what is wrong?

DarenSounds like a firewall setting blocking attachments on those emails.|||

Hi,

just wondering if you found a fix for this problem cause I have te same problem.

I noticed that the problem somehow connects to Outlook, cause when i retreve the mail in my Web-mailclient I get an attachment and in my version of Outlook (version 2003 English) there is the attachment and the size of the mail remains the same..

Lucas

|||Hi IjoNL,
I could not find any solution to it, but there's a temporary solution to this is ask the person to try and forward the email they received in outlook then they will find the attachment file there.

Daren

Email Attachements

Whenever I try to create an attachment with email, the attachment is created
but it is empyu in the case of Excel or I get an open error when creating a
PDF file attachment. If insted of using email, I create a file on a server
share it works fine and I see the report. Also if I include a link on the
email that works fine also and I see the report. It's only the attachments
that aren't working. We are using Lotus Notes for our email system.It's a known bug. Apply the hotfix described in M$ KB # 872774
X
"Jeff" wrote:
> Whenever I try to create an attachment with email, the attachment is created
> but it is empyu in the case of Excel or I get an open error when creating a
> PDF file attachment. If insted of using email, I create a file on a server
> share it works fine and I see the report. Also if I include a link on the
> email that works fine also and I see the report. It's only the attachments
> that aren't working. We are using Lotus Notes for our email system.|||A hotfix has been issued for this issue:
http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:A8509E81-7A2C-460F-B82E-F4D06B8C4A83@.microsoft.com...
> Whenever I try to create an attachment with email, the attachment is
> created
> but it is empyu in the case of Excel or I get an open error when creating
> a
> PDF file attachment. If insted of using email, I create a file on a
> server
> share it works fine and I see the report. Also if I include a link on the
> email that works fine also and I see the report. It's only the
> attachments
> that aren't working. We are using Lotus Notes for our email system.

EM SQL Scripts

The EM can produce SQL Scripts of tables/indexes/constraints/default values etc from a database. When doing so, the create statement for tables and the alter statements for constraints (including the default values) are separated. In effect, the create table-statement will only create the table; keys, default values, indexes are changes on the table. My question is: when saving the results in separated files as a basis to continue working on, would you take the effort of changing the create-tables statements so that will also fill in the default values or keep it separate?I've never bothered to mangle the generated scripts... I've never seen any need for it.

-PatP|||Pat: Thing is, there's a db here without any of the tables/views/sp's stored on disk making it, imho, an unhealthy basis to continue building on. So I've scripted the db layout, views and all that allowing me to create a dev. I'm just considering whether I should take the effort or not; when changes are made to the db/tables I'll process it into the create-scripts as well.
I wonder though, do you have any create scripts that create all the tables in a database, do you keep it separate from the indexes/constraints? I don't want to start a flame-thing on "who's responsible", just wondering if you'd take the trouble?|||I'm not really clear what you are asking, so this response may seem way off base.

If you are talking about the option that causes Enterprise Mangler to script the PK/FK/Default/Check Constraints as part of the table versus scripting the table "raw" then creating ALTER TABLE statements to add the constraints, I usually don't care how it gets done. If there was some compelling reason to set it one way or the other, then I would. Since the net effect after running the whole script will be the same, I've never bothered to monkey with it... Either way works fine for me, and produces the same results in the end.

-PatP

EM snap in

Hi
I'm not sure if this is possible but does anyone know if there is a way to
create a MMC snap in for EM and only have certain features of EM showing in
that snap in e.g. not let the user see DTS jobs etc.
I know you can restrict access so the user won't have the rights to run
certain things but is it possible to set up so they don't even see certain
things when opening the snap in. I have had a look and can't see how this can
be done so any suggestions would be welcome.
Thanks
JonjoYou would have to do that on your own, quering the sql server via SQLDMO.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"jonjo" <jonjo@.discussions.microsoft.com> schrieb im Newsbeitrag
news:5EAB76B4-016A-41F9-84D5-C2A405220C62@.microsoft.com...
> Hi
> I'm not sure if this is possible but does anyone know if there is a way to
> create a MMC snap in for EM and only have certain features of EM showing
> in
> that snap in e.g. not let the user see DTS jobs etc.
> I know you can restrict access so the user won't have the rights to run
> certain things but is it possible to set up so they don't even see certain
> things when opening the snap in. I have had a look and can't see how this
> can
> be done so any suggestions would be welcome.
> Thanks
> Jonjo|||... or also using the SQL.NS API (should save some work compared to only using DMO)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in message
news:uVJC6oJXFHA.3348@.TK2MSFTNGP14.phx.gbl...
> You would have to do that on your own, quering the sql server via SQLDMO.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "jonjo" <jonjo@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:5EAB76B4-016A-41F9-84D5-C2A405220C62@.microsoft.com...
>> Hi
>> I'm not sure if this is possible but does anyone know if there is a way to
>> create a MMC snap in for EM and only have certain features of EM showing in
>> that snap in e.g. not let the user see DTS jobs etc.
>> I know you can restrict access so the user won't have the rights to run
>> certain things but is it possible to set up so they don't even see certain
>> things when opening the snap in. I have had a look and can't see how this can
>> be done so any suggestions would be welcome.
>> Thanks
>> Jonjo
>