Microsoft SQLServer/Reporting Services

V případě potřeby konfigurace Reporting Services po instalaci na serveru se dají některé položky změnit v configuračních souborech serveru.

Tyto soubory jsou standardně umístěny:

1. c:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager\RSWebApplication.config, rsmgrpolicy.config

2. c:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config, rssrvpolicy.config

Na níže uvedených adresách najdete přesné postupy jak editovat konfigurační soubory. Konfigurace komponent reporting services http://msdn2.microsoft.com/en-us/library/ms159624.aspx a konfigurace zabezpečení serveru na adrese http://msdn2.microsoft.com/en-us/library/ms157198.aspx

Description:

XPSMTP provides a SMTP based SQL Mail solution for sending MIME based email over SMTP, implemented as an Extended Stored Procedure.
It does not require any software to be installed, just a SMTP email server that can handle the outgoing mail request. XPSMTP is using TCP/IP sockets to communicate to port 25. XPSMTP does not spawn additional threads, the xp_smtp_sendmail request is handled on the same thread as it is being called on by SQL Server. Each call to xp_smtp_sendmail establishes a connection to the SMTP server and disconnects when done sending the email. The connection is created using asynchronous communication and aborts based on a timeout value (@timeout which by default is 10000 milliseconds, so 10 seconds).

Usage:

XPSMTP only supports named parameters, since the number possible of parameters is already big and will get extended over time and since most of these parameters are optional, XPSMTP does not support ordinal parameters, mainly to reduce complexity, ambiguity and the change on mistakes.

Installation:

To install XPSMTP follow these instructions:

1. For SQL Server 2000, download XPSMTP80.ZIP and unzip the files. Files be located on end this contribution

2. Copy xpsmtpXX.dll into the SQL Server BINN directory. For SQL Server 7.0 copy XPSMTP70.DLL, for SQL Server 2000 copy XPSMTP80.DLL
For SQL Server 7.0 the default installation location is "C:\MSSQL7\BINN"
For SQL Server 2000 the default location is "C:\Program Files\Microsoft SQL Server\MSSQL\Binn"

3. Register the extended stored procedure using OSQL or SQL Query Analyzer by executing:
— SQL Server 7.0 install
exec sp_addextendedproc ‘xp_smtp_sendmail’, ‘xpsmtp70.dll’
— SQL Server 2000 install
exec sp_addextendedproc ‘xp_smtp_sendmail’, ‘xpsmtp80.dll’

4. Grant rights to the correct set of users using OSQL or SQL Query Analyzer by executing:
grant execute on xp_smtp_sendmail to public
By default only the member of the sysadmin role have execution rights on the XP after it is being registered

Parameters:

This is the complete list off parameters used by xp_smtp_sendmail. The parameter names used are case-insensitive. When a parameter is marked as mandatory (only @FROM and @TO) this means you have to provide a valid value, all other parameters are optional. Either because default values are provides, either because they are really optional in nature.

Tries to connect to @server (using @timeout), if successfuly connected @rc = 0 otherwise @rc = 1. This can be used to validate connections to the SMTP server

Return codes:

The procedure returns only two return codes, 0 (zero) indicating successful execution, 1 indicating failure always accompanied with an error message.

This is how to retrieve the return code:

declare @rc int

exec @rc = master.dbo.xp_smtp_sendmail …

select @rc

Uninstall:

To remove XPSMTP from a system follow these instructions:

1. Force the DLL out of memory, using OSQL or SQL Query Analyzer by executing:
dbcc xpsmtp70(free) — for SQL Server 7.0
dbcc xpsmtp80(free) — for SQL Server 2000

2. Unregistered the XP from the system, using OSQL or SQL Query Analyzer by executing:
exec sp_dropextendedproc ‘xp_smtp_sendmail’

3. Delete the XPSMTP70.DLL or XPSMTP80.DLL from the SQL Server BINN directory

Tento software umí např. on-line přenést reporty z jednoho na druhý server, provést dump reportů do souboru a ty následně spustit na nějakém vzdáleném serveru (typicky zákazník).

Adresa k souboru je zde: http://www.sqldbatips.com/showarticle.asp?ID=62

Pokud není již při instalaci nastaven SMTP protokol pro Reporting Services je potřeba ručně editovat jeden config soubor, který se nachází na cestě

C:\ProgramFiles\MicrosoftSQLServer\MSSQL\ReportingServices\ReportServer\RSReportServer.config

V tomto souboru je potřeba editovat vybrané parametry

• <RSEmailDPConfiguration>
• <SMTPServer>192.168.168.10</SMTPServer>
• <SMTPServerPort>25</SMTPServerPort>
• <SMTPAccountName></SMTPAccountName>
• <SMTPConnectionTimeout></SMTPConnectionTimeout>
• <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
• <SMTPUseSSL></SMTPUseSSL>
• <SendUsing>2</SendUsing>
• <SMTPAuthenticate></SMTPAuthenticate>
• <From>iris.reports@dsis.cz</From>
• <EmbeddedRenderFormats>
• <RenderingExtension>MHTML</RenderingExtension>
• </EmbeddedRenderFormats>
• <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
• <ExcludedRenderFormats>
• <RenderingExtension>HTMLOWC</RenderingExtension>
• <RenderingExtension>NULL</RenderingExtension>
• </ExcludedRenderFormats>
• <SendEmailToUserAlias>True</SendEmailToUserAlias>
• <DefaultHostName></DefaultHostName>
• <PermittedHosts></PermittedHosts>
• </RSEmailDPConfiguration>

SMTPServer – ip adresa firemního poštovního serveru

SMTPServerPort – výchozí je 25, v případě nefungování zasílání reportů je potřeba ověřit tento port na SMTP zákazníka

SendUsing – nastavit vždy na hodnotu 2

From – napsat nějaký email jako odesílatel reportů

Může nastat problém se založením či editaci subscriptionu:

Pro založení subscriptionu

db master – xp_sqlagent_notify – nestačí RSExecRole, přidat práva na execute

db msdb – sp_add_category – nestačí RSExecRole, přidat práva na execute

Pro změnu (editaci) subscriptionu

db master – xp_sqlagent_enum_jobs – nestačí RSExecRole, přidat práva na execute

db msdb – sp_verify_job_identifiers – nestačí RSExecRole, přidat práva na execute

db msdb – tabulka sysjobs – nestačí RSExecRole, přidat práva na execute

1. Přeinstalovat .NET Framework

2. Odebrat z SQL Serveru login ASPNET, a vytvorit jej znovu

3. Dat uctu ASPNET v databazich ReportServer a ReportServerTempDB prava RsExecRole+Public

4. Nastavit aplikacni mapovani v IIS na adresari ReportServer (Vlastnosti Virtualniho adresare – Kliknout na konfigurace) a to:

4.1 Na zalozce Mapovani smazat vsechny aplikacni mapovani

4.2 Vytvorit mapovani Program: c:\windows\microsoft.net\framework\v1.1.4322\aspnet_isapi.dll

Pripona: *

        Akce: Vsechny akce

        Zakliknout: Skriptovaci stroj

4.3 Vytvorit mapovani Program: c:\windows\microsoft.net\framework\v1.1.4322\aspnet_isapi.dll

        Pripona: .java

        Akce: GET,HEAD,POST,DEBUG

        Zakliknout: Skriptovaci stroj

4.4 Vytvorit mapovani Program: c:\windows\microsoft.net\framework\v1.1.4322\aspnet_isapi.dll

        Pripona: .jsl

        Akce: GET,HEAD,POST,DEBUG

        Zakliknout: Skriptovaci stroj

4.5 Vytvorit mapovani Program: c:\windows\microsoft.net\framework\v1.1.4322\aspnet_isapi.dll

        Pripona: .vjsproj

        Akce: GET,HEAD,POST,DEBUG

        Zakliknout: Skriptovaci stroj

        Nyni by melo vse fungovat