Embeded case in where clause - causing problems.

Hi.
I have a sp that allows the user to search based on a variable set of parame
ters for example just a home phone or just a buss. phone however when search
ing by last name the user also has to supply the ZIP
optionally he can filter that search by first name or address. I am trying t
o accomplish that by using embede cases - the procedure compiles - but I do
not get the results I want
For example if i pass in '%s%' for @.Lastname and '%1%' for zip - it does not
work - I am sure that are fields with those values in the db
(note: everying worked as expected before I added the embeded cases (and wil
dcard stuff))
the following is the SP - (sorry for the long snippet)
ALTER procedure GetDupCheckResults
(
@.HomePhone varchar(50),
@.BussPhone varchar(50),
@.Email varchar(50),
@.LastName varchar(50),
@.FirstName varchar(50),
@.Address varchar(50),
@.FirmZip varchar(50),
@.PersonZip varchar(50),
@.FirmName varchar(50)
)
as
begin
SELECT nmfid, nmffirst, nmflast, nafcompany, nafadd1, nafadd2, naftype +
' (' + nafdesc + ') ' AS [Adress Type], npfarea + '-' + npfphone AS Phone
FROM
(
select v_nmf_naf_npf_linked.* from
v_nmf_naf_npf_linked inner join
(
Select distinct npfseq from
(
select
npfseq,'HomePhone' FoundSource
from
v_nmf_naf_npf_Linked
where
npfArea + '-' + NPFPhone = @.HomePhone
and
naftype = 'HOME'
and
(
@.HomePhone <> ''
and
@.HomePhone <> '%%'
)
union
select
npfseq,'BussPhone'
from
v_nmf_naf_npf_Linked
where
npfArea+'-'+NPFPhone = @.BussPhone
and
naftype = 'BF'
and
@.BussPhone <> ''
union
select
npfseq,'NameAddress'
from
V_nmf_naf_npf_Linked
where
@.PersonZIP like nafzip
and
@.LastName like nmflast
and
-- optionally allow filter on address and last name
1 =
Case @.Address
When '' then 1
When '%%' then 1 -- when wild cards are in use
Else
(
Case
when @.Address like nafAdd1 then 1
when @.Address like nafAdd2 then 1
else
0
end
)
end
and
1 =
Case @.LastName
When '' then 1
When '%%' then 1
Else
(
Case
when @.Lastname like nmfLast then 1
else 0
end
)
end
and not
(
(@.LastName = '' or @.PersonZip = '')
or
(@.LastName = '%%' or @.PersonZip = '%%')
)
union
select
npfseq,'Company'
from
V_nmf_naf_npf_Linked
where
nafZip like @.FirmZip
and
nafCompany like @.FirmName
and not @.FirmName = ''
and not @.FirmZip = ''
and not @.FirmName = '%%'
and not @.FirmZip = '%%'
)Temptab
)
unionResult on unionResult.npfseq = v_nmf_naf_npf_Linked.npfseq
) dd
end
thank you for slogging thru it.You are sure you have people with the last name '%s%' and zip '%1%'?
What country is this?
Most likely you don't have such data in your database, but that is exactly
what this code will try to find, since your condition (on last name, for
example) is
@.Lastname like nmfLast
While this stored procedure looks far more complicated than it needs to
be, my guess is that you want
nmfLast LIKE @.Lastname
instead of the other way around.
Steve Kass
Drew University
Madler wrote:

>Hi.
>I have a sp that allows the user to search based on a variable set of param
eters for example just a home phone or just a buss. phone however when searc
hing by last name the user also has to supply the ZIP
>optionally he can filter that search by first name or address. I am trying
to accomplish that by using embede cases - the procedure compiles - but I do
not get the results I want
>For example if i pass in '%s%' for @.Lastname and '%1%' for zip - it does no
t work - I am sure that are fields with those values in the db
>(note: everying worked as expected before I added the embeded cases (and wi
ldcard stuff))
>the following is the SP - (sorry for the long snippet)
>ALTER procedure GetDupCheckResults
>(
> @.HomePhone varchar(50),
> @.BussPhone varchar(50),
> @.Email varchar(50),
> @.LastName varchar(50),
> @.FirstName varchar(50),
> @.Address varchar(50),
> @.FirmZip varchar(50),
> @.PersonZip varchar(50),
> @.FirmName varchar(50)
> )
>as
>begin
>SELECT nmfid, nmffirst, nmflast, nafcompany, nafadd1, nafadd2, naftype
+ ' (' + nafdesc + ') ' AS [Adress Type], npfarea + '-' + npfphone AS Phone
>FROM
>(
>select v_nmf_naf_npf_linked.* from
> v_nmf_naf_npf_linked inner join
> (
> Select distinct npfseq from
> (
> select
> npfseq,'HomePhone' FoundSource
> from
> v_nmf_naf_npf_Linked
> where
> npfArea + '-' + NPFPhone = @.HomePhone
> and
> naftype = 'HOME'
> and
> (
> @.HomePhone <> ''
> and
> @.HomePhone <> '%%'
> )
> union
> select
> npfseq,'BussPhone'
> from
> v_nmf_naf_npf_Linked
> where
> npfArea+'-'+NPFPhone = @.BussPhone
> and
> naftype = 'BF'
> and
> @.BussPhone <> ''
> union
> select
> npfseq,'NameAddress'
> from
> V_nmf_naf_npf_Linked
> where
> @.PersonZIP like nafzip
> and
> @.LastName like nmflast
> and
> -- optionally allow filter on address and last name
> 1 =
> Case @.Address
> When '' then 1
> When '%%' then 1 -- when wild cards are in use
> Else
> (
> Case
> when @.Address like nafAdd1 then 1
> when @.Address like nafAdd2 then 1
> else
> 0
> end
> )
> end
> and
> 1 =
> Case @.LastName
> When '' then 1
> When '%%' then 1
> Else
> (
> Case
> when @.Lastname like nmfLast then 1
> else 0
> end
> )
> end
> and not
> (
> (@.LastName = '' or @.PersonZip = '')
> or
> (@.LastName = '%%' or @.PersonZip = '%%')
> )
>
> union
> select
> npfseq,'Company'
> from
> V_nmf_naf_npf_Linked
> where
> nafZip like @.FirmZip
> and
> nafCompany like @.FirmName
> and not @.FirmName = ''
> and not @.FirmZip = ''
> and not @.FirmName = '%%'
> and not @.FirmZip = '%%'
>
> )Temptab
> )
> unionResult on unionResult.npfseq = v_nmf_naf_npf_Linked.npfseq
> ) dd
>end
>
>thank you for slogging thru it.
>
>

Embed HTML tags into report fields

I have a need to render data returned from a relational DB with spacing intact. The application is a viewer for input data so the end user can determine if the input data is different than the file layout specified so the data will be parsed correctly, so spacing is critical.

I changed to a fixed-pitch font to ensure that the text records align properly in VS (which they do), but when I render to HTML, the spaces collapse (of course). PDFs render correctly, but we were trying for HTML output.

Is there any way to replace the native spaces with &nbsp; so it will render correctly in HTML? From the other posts, it seems not, but I want to double check.

If anyone knows of a way to do this, I would appreciate any insight.

Thx.See http://blogs.msdn.com/bimusings/archive/2005/12/14/503648.aspx.sql

Embed HTML tags into report fields

I have a need to render data returned from a relational DB with spacing intact. The application is a viewer for input data so the end user can determine if the input data is different than the file layout specified so the data will be parsed correctly, so spacing is critical.

I changed to a fixed-pitch font to ensure that the text records align properly in VS (which they do), but when I render to HTML, the spaces collapse (of course). PDFs render correctly, but we were trying for HTML output.

Is there any way to replace the native spaces with &nbsp; so it will render correctly in HTML? From the other posts, it seems not, but I want to double check.

If anyone knows of a way to do this, I would appreciate any insight.

Thx.See http://blogs.msdn.com/bimusings/archive/2005/12/14/503648.aspx.

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 through SQL

I am trying to setup my sql to send automatic emails to different companies but I am not doing so well. The user puts an email address in the database and then presses a button (command button) that will execute a stored procedure xp_sendmail to a recepient. I need to know how to include read receipts and delivery receipts into the stored procedure, because right now its set up in his outlook as a rule to do this and I want to set it up in sql. The user deals with companies and these companies have several contacts with several email addresses which sql will store in a seperate table.Hiya Des...I'm sure others more knowledgeable will soon come to your rescue, but I don't think there are that kind of specific email options in xp_sendmail. However, Though I use the xp_sendmail quite a bit, I am no expert (as my recent thread posting shows).

HOWEVER, that said, perhaps you could send a "regular" email to your user from SQL Server, then set up some rules in Outlook to massage the necessary options and forward the email on to the appropriate customers? I know that's kinda a round-about way to get there, but it seems to me that it would work if nobody comes up with a definitive answer from SQL Server...

Just a thought

Email subscription Issue

We have deployed SSRS 2005 on internet using Forms authentication. every thing works just fine except the email subscription;

when a user who is part of browser role (manage individual subscription) clicks on new subscription his To field is grayed out (unable to modify) and account showing in it is the service account we use to run reporting service.

we need to give users permission to specity To, CC, BCC fields without letting them see other subscriptions.

can you help us configuring this?

Hi Aneel-

You need to go into the rsreportserver.config file located in the ReportServer folder and look for the "<SendEmailToUserAlias>" node. If it is "False", change it to "True" and you'll be able to enter e-mail addresses to send to. You will only get the "To" field, not "Cc" or "Bcc" fields.

Note you may have to restart IIS.

Regards,
Scott

Email subscription error on credentials

Hello,

When trying to subscribe to an report via email I get to following error

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. (rsInvalidDataSourceCredentialSetting)

Can anybody help me with this issue.

Greetings

Vinnie

The username and password need to be stored on the server.

The report is trying to connect to the db with the datasource, but you have not supplied any credentials.

In the data source properties, enter the username and password you wish the RS to connect to the db with.|||

hi,

thanks for the quick response.

I get what you mean, but why does the reportserver does not ask for those credentials when i generate the report

with the reportviewer, but only for a subscription?

Does this mean that i have to specify username and password in my datasource for my reports (in the credentials tab)

Greetings

Vinnie

|||When you are running the report through reportviewer, it already has your credentials.
When it is running as a subscription, it is essentially running 'unattended' so it cannot validate whether it can connect to the db.

By embedding your credentials, it can check if it has permissions to run the report, just as if you were logged on to your machine and trying to run it through IE/report viewer

EMAIL Sent nobody Get?!

Well i cofigured one of my reports to be sent as email to user in the domain.
In the Report manager appeared that the mail was sent successfully but the
user didn't get it.
When I sent it to a outdoor email it didn't send it at all,why?
Where i can check the smtp setup 4 reporting services?If you look in the RSReportServer.config file you will see the SMTP settings
that Report Services use. If the subscription status says "Mail sent ..."
then RS has sent the mail to the SMTP server and you would need to look
there for possible errors.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"'" <@.discussions.microsoft.com> wrote in message
news:AE026676-803E-4FED-A7E7-BCF9D38292FB@.microsoft.com...
> Well i cofigured one of my reports to be sent as email to user in the
> domain.
> In the Report manager appeared that the mail was sent successfully but the
> user didn't get it.
> When I sent it to a outdoor email it didn't send it at all,why?
> Where i can check the smtp setup 4 reporting services?sql

Email report

Is there any way a user could email a report that is being viewed in Report
Manager? I see options to export the report but no option to email it. We
are using SQL Server 2005 Std.
Thanks.
TimI wish there was an e-mail button too. The simplest way for now is to export
to pdf, select "Open" instead of "Save", and from Adobe Reader, e-mail the
pdf.
,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Tim Kelley" <tkelley@.company.com> wrote in message
news:%231DCRkveIHA.4476@.TK2MSFTNGP06.phx.gbl...
> Is there any way a user could email a report that is being viewed in
> Report Manager? I see options to export the report but no option to email
> it. We are using SQL Server 2005 Std.
> Thanks.
> Tim
>sql

Email on demand

Looking for an example of sending a 2005 RS report via a web page on demand.
I would need to fire it when a user clicked a button on the web form.
In the old days, in plain old ASP, we used to build an HTML formatted email
body and then fire it off using CDO. We could still do that with ASPX but
I'd much prefer to build a report in RS (more formatting and doc type options)
and then fire it off.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1On Oct 3, 1:07 pm, "wnichols via SQLMonster.com" <u3357@.uwe> wrote:
> Looking for an example of sending a 2005 RS report via a web page on demand.
> I would need to fire it when a user clicked a button on the web form.
> In the old days, in plain old ASP, we used to build an HTML formatted email
> body and then fire it off using CDO. We could still do that with ASPX but
> I'd much prefer to build a report in RS (more formatting and doc type options)
> and then fire it off.
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1
This link should help.
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
You will want to create a Proxy (Web Reference) to
ReportingService2005.asmx. Then attach the newly created file to an
email created via ASP.NET. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
Thanks for the info.
EMartinez wrote:
>> Looking for an example of sending a 2005 RS report via a web page on demand.
>> I would need to fire it when a user clicked a button on the web form.
>[quoted text clipped - 6 lines]
>> --
>> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1
>This link should help.
>http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
>You will want to create a Proxy (Web Reference) to
>ReportingService2005.asmx. Then attach the newly created file to an
>email created via ASP.NET. Hope this helps.
>Regards,
>Enrique Martinez
>Sr. Software Consultant
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1|||On Oct 4, 11:04 am, "wnichols via SQLMonster.com" <u3357@.uwe> wrote:
> Enrique,
> Thanks for the info.
>
> EMartinez wrote:
> >> Looking for an example of sending a 2005 RS report via a web page on demand.
> >> I would need to fire it when a user clicked a button on the web form.
> >[quoted text clipped - 6 lines]
> >> --
> >> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1
> >This link should help.
> >http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingser...
> >You will want to create a Proxy (Web Reference) to
> >ReportingService2005.asmx. Then attach the newly created file to an
> >email created via ASP.NET. Hope this helps.
> >Regards,
> >Enrique Martinez
> >Sr. Software Consultant
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

e-mail in SSRS reports with attachment

Hi All,

In my SSRS report there is a column with email field, when the user clicks on it -- it should open the outlook and in the attachment field there should be a screenshot of that report

i think anything i could do is only in the jump to url in the navigation tab......Please suggest me the solution or any other alternatives.

Thanks in advance for help

Warm Regards,

Chanduu.

I am using an alternate approach, but that is also not working

The approach I was using is writing a code in c# to export that report in PDF programmatically using c#, without opening SSRS so that I could attach it outlook/write a mail program, for this I got some help from this site http://www.programurl.com/pdf-reporting-services.htm, but this file is saving a PDG file without any data.

If you have any solution, please send me so that I could move further

Warm Regards,

Chanduu

e-mail in SSRS reports with attachment

Hi All,

In my SSRS report there is a column with email field, when the user clicks on it -- it should open the outlook and in the attachment field there should be a screenshot of that report

i think anything i could do is only in the jump to url in the navigation tab......Please suggest me the solution or any other alternatives.

Thanks in advance for help

Warm Regards,

Chanduu.

I am using an alternate approach, but that is also not working

The approach I was using is writing a code in c# to export that report in PDF programmatically using c#, without opening SSRS so that I could attach it outlook/write a mail program, for this I got some help from this site http://www.programurl.com/pdf-reporting-services.htm, but this file is saving a PDG file without any data.

If you have any solution, please send me so that I could move further

Warm Regards,

Chanduu

email Hyperlink

Hi! I have a report that is showing an email account.
I wanted to let the user click on this field, so, if he/she has an
email program set up, they can email directly.
Do you know how can I do it?
Thanks!!Yeah. I think you'll want to use an "action".
Ryan
Smelly wrote:
> Hi! I have a report that is showing an email account.
> I wanted to let the user click on this field, so, if he/she has an
> email program set up, they can email directly.
> Do you know how can I do it?
> Thanks!!|||Properties/Navigation/Jump to URL:
mailto:name@.company.com
"Smelly" wrote:
> Hi! I have a report that is showing an email account.
> I wanted to let the user click on this field, so, if he/she has an
> email program set up, they can email directly.
> Do you know how can I do it?
> Thanks!!
>

Email From a CLR Stored Proc - SMTPPermission

I am trying to send email from a CLR Stored proc.

I get the following error.

A .NET Framework error occurred during execution of user defined routine or aggregate 'HelloWorld':
System.Security.SecurityException:

Request for the permission of type 'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net.Mail.SmtpClient.Initialize()
at System.Net.Mail.SmtpClient..ctor(String host)
at StoredProcedures.HelloWorld()
.

How do I handle the SmtpPermission?

Here is the stored proc

public static void HelloWorld()
{
MailMessage mail = new MailMessage();

//set the addresses
mail.From = new MailAddress("test@.test.EDU");
mail.To.Add("test@.test.EDU");

//set the content
mail.Subject = "Hello World";
mail.Body = "Did you get this? I am emailing from a CLR stored proc!";

//send the message
SmtpClient smtp = new SmtpClient("127.0.0.1");
smtp.Send(mail);

}

SQL Server comes with when last I counted three different mail internal so you are reinventing the wheel by using System.Net. Run a search for SQL Server mail and SQL Server Agent mail in the BOL (books online) because I think there are known issues with the IMAPI mail. SQL Server Agent mail can also be used to send pages. Hope this helps.|||

Have the same problen - and the above RUDE comment isnt any help!

Instantiating SmtpClient gives -exception of type 'System.Security.SecurityException'

Hmmm - if it would work we've got a lot of objects we could put to work without reenventing new methods just to work in a CLR proc!

What's necessary to get SmtpClient to work in a CLR proc?

|||

OK - there's 2 or 3 ways to get the job done...

Easiest( although not MSDN recommended just to jet a CLR proc to run) is to set the permission level to External_Access...

SQL Server Host Policy Level Permission Sets
The set of code access security permissions granted to assemblies by the SQL Server host policy level is determined by the permission set specified when creating the assembly. There are three permission sets:SAFE,EXTERNAL_ACCESS andUNSAFE.

The permision level is set on the properties pages of the CLR project , database tab - set Permission Level-External, set Aassembly Owner-dbo, and run tsql 'ALTERDATABASE DataBaseName SET TRUSTWORTHYON'
This will get the job DONE! - and the SmtpClient wiill work ok...

Then do it right and Sign the Assenbly with a Strong name Key file...
Read MSDN

Creating an Assembly

Discusses creating SAFE, EXTERNAL_ACCESS, and UNSAFE CLR assemblies in SQL Server

That's it...

RLewis - MCSD

(and there sure is a lot of NO HELP answers goin around...)

|||

Brilliant!!!!!

Solution 2 worked like a charm. El mucho gracias!!!!

e-mail Delivery report by trigger or user solicitation.

It's possivel delivery a report after trigger execution ou user solicitation?

Thanks in advance, Rui Figueiredo

Yes, you can externally trigger the execution and email delivery of a report (= report subscription) using the FireEvent SOAP API. Here's a link that explains the required steps:

http://blogs.msdn.com/lukaszp/archive/2005/10/07/478391.aspx

Also check out the RS script utility that will make it easier fire the event:

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

Email

Do you know a tool for email sending that make authentication of user and
password in my server external smtp of my net and that she makes use of
format HTML? thanks!Since you post in a SQL Server group, I assume you want to do this from with
in SQL Server.
SQL Server 2005 has built-in support for SMTP, authentication and HTML. The
feature is called
"Database Mail".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Diogo Guides" <DiogoGuides@.discussions.microsoft.com> wrote in message
news:A812A9D4-0C51-4443-BF1D-3C64CB0538AC@.microsoft.com...
> Do you know a tool for email sending that make authentication of user and
> password in my server external smtp of my net and that she makes use of
> format HTML? thanks!|||I use the 2000!
"Tibor Karaszi" wrote:

> Since you post in a SQL Server group, I assume you want to do this from wi
thin SQL Server.
> SQL Server 2005 has built-in support for SMTP, authentication and HTML. Th
e feature is called
> "Database Mail".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Diogo Guides" <DiogoGuides@.discussions.microsoft.com> wrote in message
> news:A812A9D4-0C51-4443-BF1D-3C64CB0538AC@.microsoft.com...
>
>

email

what is the most common way to send an email to an user who wants to be
notified when a answer is given to his guestion?
how is this handled with microsofts newsgroup?
(using cdo or notificationservice or ... ?)
Your scenario can be acomplished both with Notification Services or SQL
Mail.
If you decide to use Notification Services which is more apropriate for this
case take a look at the following walkthrough:
"Walkthrough: Creating a Stock Notification Application"
http://msdn.microsoft.com/library/de...onfig_2kc3.asp
Cristian Lefter, SQL Server MVP
"Wilco" <Wilco@.discussions.microsoft.com> wrote in message
news:572D78EF-3D8E-4EAF-BBB2-4CC26E80EA68@.microsoft.com...
> what is the most common way to send an email to an user who wants to be
> notified when a answer is given to his guestion?
> how is this handled with microsofts newsgroup?
> (using cdo or notificationservice or ... ?)
>
|||'notifyMS@.microsoft.com'
how is this handled with microsofts newsgroup?
"Cristian Lefter" wrote:

> Your scenario can be acomplished both with Notification Services or SQL
> Mail.
> If you decide to use Notification Services which is more apropriate for this
> case take a look at the following walkthrough:
> "Walkthrough: Creating a Stock Notification Application"
> http://msdn.microsoft.com/library/de...onfig_2kc3.asp
> Cristian Lefter, SQL Server MVP
> "Wilco" <Wilco@.discussions.microsoft.com> wrote in message
> news:572D78EF-3D8E-4EAF-BBB2-4CC26E80EA68@.microsoft.com...
>
>
|||It is not. He thought you were referring to a system under your control.
The most common way is to get a newsreader (running on your machine) that
provides the functionality you want.
"Wilco" <Wilco@.discussions.microsoft.com> wrote in message
news:355F67CC-100E-41BE-A87F-5BBA7CF3EF2F@.microsoft.com...[vbcol=seagreen]
> 'notifyMS@.microsoft.com'
> how is this handled with microsofts newsgroup?
>
> "Cristian Lefter" wrote:
this[vbcol=seagreen]
http://msdn.microsoft.com/library/de...onfig_2kc3.asp[vbcol=seagreen]
be[vbcol=seagreen]
|||thanks,
but what do you mean with newsreader?
"Scott Morris" wrote:

> It is not. He thought you were referring to a system under your control.
> The most common way is to get a newsreader (running on your machine) that
> provides the functionality you want.
> "Wilco" <Wilco@.discussions.microsoft.com> wrote in message
> news:355F67CC-100E-41BE-A87F-5BBA7CF3EF2F@.microsoft.com...
> this
> http://msdn.microsoft.com/library/de...onfig_2kc3.asp
> be
>
>
|||A program that allows you to read and post messages in newsgroups. In other
words, the program you are using to post your messages and read the
responses (including mine). If you are using a web-based newsreader, then
perhaps you should look for one that allows you to monitor messages/threads.
"Wilco" <Wilco@.discussions.microsoft.com> wrote in message
news:04218ACB-2FA0-4F01-AC62-5418A06D660B@.microsoft.com...[vbcol=seagreen]
> thanks,
> but what do you mean with newsreader?
>
> "Scott Morris" wrote:
control.[vbcol=seagreen]
that[vbcol=seagreen]
SQL[vbcol=seagreen]
for[vbcol=seagreen]
http://msdn.microsoft.com/library/de...onfig_2kc3.asp[vbcol=seagreen]
to[vbcol=seagreen]
|||but i'm being notified (email) when someone answered.
so there is something like sql mail to send this mail to me.
my qeustion is how this email is being generated...
"Scott Morris" wrote:

> A program that allows you to read and post messages in newsgroups. In other
> words, the program you are using to post your messages and read the
> responses (including mine). If you are using a web-based newsreader, then
> perhaps you should look for one that allows you to monitor messages/threads.
> "Wilco" <Wilco@.discussions.microsoft.com> wrote in message
> news:04218ACB-2FA0-4F01-AC62-5418A06D660B@.microsoft.com...
> control.
> that
> SQL
> for
> http://msdn.microsoft.com/library/de...onfig_2kc3.asp
> to
>
>

Email

Do you know a tool for email sending that make authentication of user and
password in my server external smtp of my net and that she makes use of
format HTML? thanks!Since you post in a SQL Server group, I assume you want to do this from within SQL Server.
SQL Server 2005 has built-in support for SMTP, authentication and HTML. The feature is called
"Database Mail".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Diogo Guides" <DiogoGuides@.discussions.microsoft.com> wrote in message
news:A812A9D4-0C51-4443-BF1D-3C64CB0538AC@.microsoft.com...
> Do you know a tool for email sending that make authentication of user and
> password in my server external smtp of my net and that she makes use of
> format HTML? thanks!|||I use the 2000!
"Tibor Karaszi" wrote:
> Since you post in a SQL Server group, I assume you want to do this from within SQL Server.
> SQL Server 2005 has built-in support for SMTP, authentication and HTML. The feature is called
> "Database Mail".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Diogo Guides" <DiogoGuides@.discussions.microsoft.com> wrote in message
> news:A812A9D4-0C51-4443-BF1D-3C64CB0538AC@.microsoft.com...
> > Do you know a tool for email sending that make authentication of user and
> > password in my server external smtp of my net and that she makes use of
> > format HTML? thanks!
>
>

email

what is the most common way to send an email to an user who wants to be
notified when a answer is given to his guestion?
how is this handled with microsofts newsgroup?
(using cdo or notificationservice or ... ?)Your scenario can be acomplished both with Notification Services or SQL
Mail.
If you decide to use Notification Services which is more apropriate for this
case take a look at the following walkthrough:
"Walkthrough: Creating a Stock Notification Application"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlntsv/htm/ns_adminconfig_2kc3.asp
Cristian Lefter, SQL Server MVP
"Wilco" <Wilco@.discussions.microsoft.com> wrote in message
news:572D78EF-3D8E-4EAF-BBB2-4CC26E80EA68@.microsoft.com...
> what is the most common way to send an email to an user who wants to be
> notified when a answer is given to his guestion?
> how is this handled with microsofts newsgroup?
> (using cdo or notificationservice or ... ?)
>|||'notifyMS@.microsoft.com'
how is this handled with microsofts newsgroup?
"Cristian Lefter" wrote:
> Your scenario can be acomplished both with Notification Services or SQL
> Mail.
> If you decide to use Notification Services which is more apropriate for this
> case take a look at the following walkthrough:
> "Walkthrough: Creating a Stock Notification Application"
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlntsv/htm/ns_adminconfig_2kc3.asp
> Cristian Lefter, SQL Server MVP
> "Wilco" <Wilco@.discussions.microsoft.com> wrote in message
> news:572D78EF-3D8E-4EAF-BBB2-4CC26E80EA68@.microsoft.com...
> > what is the most common way to send an email to an user who wants to be
> > notified when a answer is given to his guestion?
> >
> > how is this handled with microsofts newsgroup?
> >
> > (using cdo or notificationservice or ... ?)
> >
>
>|||It is not. He thought you were referring to a system under your control.
The most common way is to get a newsreader (running on your machine) that
provides the functionality you want.
"Wilco" <Wilco@.discussions.microsoft.com> wrote in message
news:355F67CC-100E-41BE-A87F-5BBA7CF3EF2F@.microsoft.com...
> 'notifyMS@.microsoft.com'
> how is this handled with microsofts newsgroup?
>
> "Cristian Lefter" wrote:
> > Your scenario can be acomplished both with Notification Services or SQL
> > Mail.
> > If you decide to use Notification Services which is more apropriate for
this
> > case take a look at the following walkthrough:
> > "Walkthrough: Creating a Stock Notification Application"
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlntsv/htm/ns_adminconfig_2kc3.asp
> >
> > Cristian Lefter, SQL Server MVP
> >
> > "Wilco" <Wilco@.discussions.microsoft.com> wrote in message
> > news:572D78EF-3D8E-4EAF-BBB2-4CC26E80EA68@.microsoft.com...
> > > what is the most common way to send an email to an user who wants to
be
> > > notified when a answer is given to his guestion?
> > >
> > > how is this handled with microsofts newsgroup?
> > >
> > > (using cdo or notificationservice or ... ?)
> > >
> >
> >
> >|||thanks,
but what do you mean with newsreader?
"Scott Morris" wrote:
> It is not. He thought you were referring to a system under your control.
> The most common way is to get a newsreader (running on your machine) that
> provides the functionality you want.
> "Wilco" <Wilco@.discussions.microsoft.com> wrote in message
> news:355F67CC-100E-41BE-A87F-5BBA7CF3EF2F@.microsoft.com...
> > 'notifyMS@.microsoft.com'
> >
> > how is this handled with microsofts newsgroup?
> >
> >
> >
> > "Cristian Lefter" wrote:
> >
> > > Your scenario can be acomplished both with Notification Services or SQL
> > > Mail.
> > > If you decide to use Notification Services which is more apropriate for
> this
> > > case take a look at the following walkthrough:
> > > "Walkthrough: Creating a Stock Notification Application"
> > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlntsv/htm/ns_adminconfig_2kc3.asp
> > >
> > > Cristian Lefter, SQL Server MVP
> > >
> > > "Wilco" <Wilco@.discussions.microsoft.com> wrote in message
> > > news:572D78EF-3D8E-4EAF-BBB2-4CC26E80EA68@.microsoft.com...
> > > > what is the most common way to send an email to an user who wants to
> be
> > > > notified when a answer is given to his guestion?
> > > >
> > > > how is this handled with microsofts newsgroup?
> > > >
> > > > (using cdo or notificationservice or ... ?)
> > > >
> > >
> > >
> > >
>
>|||A program that allows you to read and post messages in newsgroups. In other
words, the program you are using to post your messages and read the
responses (including mine). If you are using a web-based newsreader, then
perhaps you should look for one that allows you to monitor messages/threads.
"Wilco" <Wilco@.discussions.microsoft.com> wrote in message
news:04218ACB-2FA0-4F01-AC62-5418A06D660B@.microsoft.com...
> thanks,
> but what do you mean with newsreader?
>
> "Scott Morris" wrote:
> > It is not. He thought you were referring to a system under your
control.
> > The most common way is to get a newsreader (running on your machine)
that
> > provides the functionality you want.
> >
> > "Wilco" <Wilco@.discussions.microsoft.com> wrote in message
> > news:355F67CC-100E-41BE-A87F-5BBA7CF3EF2F@.microsoft.com...
> > > 'notifyMS@.microsoft.com'
> > >
> > > how is this handled with microsofts newsgroup?
> > >
> > >
> > >
> > > "Cristian Lefter" wrote:
> > >
> > > > Your scenario can be acomplished both with Notification Services or
SQL
> > > > Mail.
> > > > If you decide to use Notification Services which is more apropriate
for
> > this
> > > > case take a look at the following walkthrough:
> > > > "Walkthrough: Creating a Stock Notification Application"
> > > >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlntsv/htm/ns_adminconfig_2kc3.asp
> > > >
> > > > Cristian Lefter, SQL Server MVP
> > > >
> > > > "Wilco" <Wilco@.discussions.microsoft.com> wrote in message
> > > > news:572D78EF-3D8E-4EAF-BBB2-4CC26E80EA68@.microsoft.com...
> > > > > what is the most common way to send an email to an user who wants
to
> > be
> > > > > notified when a answer is given to his guestion?
> > > > >
> > > > > how is this handled with microsofts newsgroup?
> > > > >
> > > > > (using cdo or notificationservice or ... ?)
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||but i'm being notified (email) when someone answered.
so there is something like sql mail to send this mail to me.
my qeustion is how this email is being generated...
"Scott Morris" wrote:
> A program that allows you to read and post messages in newsgroups. In other
> words, the program you are using to post your messages and read the
> responses (including mine). If you are using a web-based newsreader, then
> perhaps you should look for one that allows you to monitor messages/threads.
> "Wilco" <Wilco@.discussions.microsoft.com> wrote in message
> news:04218ACB-2FA0-4F01-AC62-5418A06D660B@.microsoft.com...
> > thanks,
> >
> > but what do you mean with newsreader?
> >
> >
> > "Scott Morris" wrote:
> >
> > > It is not. He thought you were referring to a system under your
> control.
> > > The most common way is to get a newsreader (running on your machine)
> that
> > > provides the functionality you want.
> > >
> > > "Wilco" <Wilco@.discussions.microsoft.com> wrote in message
> > > news:355F67CC-100E-41BE-A87F-5BBA7CF3EF2F@.microsoft.com...
> > > > 'notifyMS@.microsoft.com'
> > > >
> > > > how is this handled with microsofts newsgroup?
> > > >
> > > >
> > > >
> > > > "Cristian Lefter" wrote:
> > > >
> > > > > Your scenario can be acomplished both with Notification Services or
> SQL
> > > > > Mail.
> > > > > If you decide to use Notification Services which is more apropriate
> for
> > > this
> > > > > case take a look at the following walkthrough:
> > > > > "Walkthrough: Creating a Stock Notification Application"
> > > > >
> > >
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlntsv/htm/ns_adminconfig_2kc3.asp
> > > > >
> > > > > Cristian Lefter, SQL Server MVP
> > > > >
> > > > > "Wilco" <Wilco@.discussions.microsoft.com> wrote in message
> > > > > news:572D78EF-3D8E-4EAF-BBB2-4CC26E80EA68@.microsoft.com...
> > > > > > what is the most common way to send an email to an user who wants
> to
> > > be
> > > > > > notified when a answer is given to his guestion?
> > > > > >
> > > > > > how is this handled with microsofts newsgroup?
> > > > > >
> > > > > > (using cdo or notificationservice or ... ?)
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>

EM wants to confirm user's password

Hi,
I'm having a problem with Enterprise Manager. The problem is that, whenever
I use EM to modify a user's SQL Server account (any modification), EM will a
sk me to confirm the user's pasword. I don't know the user passwords. A few
months ago I read about
this problem but now I can't find any info and have forgotten where I read a
bout this bug. I have other tools to use to make changes so, I haven't worri
ed about it. But now, I want to fix the bug. On my desk top I'm running SQL
2000 client tools. Our serv
ers are mostly SQL 2000 with SP3 with a few SQL 7 SP4.
Any information would be helpful.
Thanks,
JoeSounds like this may be the bug you are referring to:
FIX: You Are Prompted for Password Confirmation After You
Change a Standard SQL Server Login
http://support.microsoft.com/?kbid=826161
-Sue
On Fri, 26 Mar 2004 11:16:08 -0800, Joe <joeydba@.yahoo.com>
wrote:

>Hi,
> I'm having a problem with Enterprise Manager. The problem is that, whenever I use
EM to modify a user's SQL Server account (any modification), EM will ask me to conf
irm the user's pasword. I don't know the user passwords. A few months ago I read ab
out
this problem but now I can't find any info and have forgotten where I read a
bout this bug. I have other tools to use to make changes so, I haven't worri
ed about it. But now, I want to fix the bug. On my desk top I'm running SQL
2000 client tools. Our ser
vers are mostly SQL 2000 with SP3 with a few SQL 7 SP4.
> Any information would be helpful.
>Thanks,
>Joe