Search

Thursday 16 June 2011

SSRS Date format expressions

Information gleamed from http://www.sqlservercentral.com/Forums/Topic290724-150-3.aspx

The Format function is locale aware and can be used as follows [not the case senstivity]
Set Language=User!language in the Report properties and use the Format function with the following codes:

Standard Format Specifiers for Dates and Times:
The table below shows the standard date and time formatters.
d Short Date
D Long date
f long date & short time
F long date and long time
g short date and short time
G short date and long time
M or m month and day
Y or y year and month
t short time
T long time
s displays in ISO 8601 format using local time
u displays in ISO 8601 format using universal time
U date and time in unversal time
R or r displays in RFC 1123 format

Custom formatting sequences:
There are also specific character sequences that can be used to achieve custom formatting of dates and times.

Format Description
d day of month (1 or 2 digits as required)
dd day of month (always 2 digits, with a leading 0 if needed)
ddd day of week (3 letter abbreviation)
dddd day of week (full name)
M month number (1 or 2 digits as required)
MM month number (always 2 digits, with a leading 0 if needed)
MMM month name (3 letter abbreviation)
MMMM month name (full name)
y year ( last 1 or 2 digits, no leading 0)
yy year (last 2 digits)
yyyy year (4 digits)
H hour in 24-hour format (1 or 2 digits as required)
HH hour in 24-hour format (always 2 digits, with a leading 0 if needed)
h hour in 12-hour format (1 or 2 digits as required)
hh hour in 12 hour format
m minutes (1 or 2 digits as required)
mm minutes (always 2 digits, with a leading 0 if needed)
s seconds (1 or 2 digits as required)
ss seconds
t first character in the am/pm designator
tt am/pm designator
z time zone offset, hour only (1 or 2 digits as required)
zz time zone offset, hour only (always 2 digits, with a leading 0 if needed)
zzz time zone offset, hour and minute
/ default date separator
: default time separator
\ escape characters

Tuesday 14 June 2011

SSIS : connection to ssis service failed. access denied

SSIS : connection to ssis service failed. access denied


http://www.sqlservercentral.com/Forums/Topic311706-146-1.aspx

A few things to check:

If you have a firewall between the developer and the sql server:

  • open a port for SSIS on the firewall (3882 and an additional port for the return traffic)
  • switch from using a dynamic return port to a static port:

    1. hkey_classes_root\appid\{F38B7F09-979B-4241-80D9-2EADED02954F}
    2. new reg_mutlistring
    3. Key=Endpoints
    4. Value=ncacn_ip_tcp,0,port#

      (port# is the port you have open on your firewall for SSIS)

    Check MsDtsServer in Component Services:

    1. add all permissions for msdtsserver for the developer account
    2. component services > My computer properties > MSDTC > Security Configuration > Network DTC Access > Allow Remote clients
    3. COM Security > Access permissions > Edit Limits
    4. add all permissions for msdtsserver for the developer account
    5. COM Security > Launch and activation permissions > Edit Limits
    6. add all permissions for msdtsserver for the developer account
    7. Allow inbound
    8. allow outbound
    9. restart MSDTC
    10. restart SSIS
    If this does not fix the problem, make sure you have build 2153:
    select @@version
    Microsoft SQL Server 2005 - 9.00.2153.00

    If you do not have that build, apply SP1 and then the post SP1 rollup in the correct order (apply all six patches in the order listed in the aticle below):

    SP1 - http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en
    rollup - http://support.microsoft.com/Default.aspx?id=918222

    If you still can't connect to SSIS, you need to check other things like DNS and account permissions.

    Or - as per the forum thread, try
    "The user's Windows account needed to be in the Distributed COM Users group on the server."
  • Thursday 2 June 2011

    SQL Setting Up Windows Service Accounts

    Setting Up Windows Service Accounts


    http://msdn.microsoft.com/en-us/library/ms143504.aspx

    Each service in SQL Server represents a process or a set of processes to manage authentication of SQL Server operations with Windows. This topic describes the default configuration of services in this release of SQL Server, and configuration options for SQL Server services that you can set during SQL Server installation.

    Depending on the components that you decide to install, SQL Server Setup installs the following services:

  • SQL Server Database Services - The service for the SQL Server relational Database Engine.

  • SQL Server Agent - Executes jobs, monitors SQL Server, fires alerts, and enables automation of some administrative tasks.

    Note
    For SQL Server and SQL Server Agent to run as services in Windows, SQL Server and SQL Server Agent must be assigned a Windows user account. For more information about how to customize account information for each service, see How to: Install SQL Server 2008 R2 (Setup).


  • Analysis Services - Provides online analytical processing (OLAP) and data mining functionality for business intelligence applications.

  • Reporting Services - Manages, executes, creates, schedules, and delivers reports.

    Integration Services - Provides management support for Integration Services package storage and execution.

    SQL Server Browser - The name resolution service that provides SQL Server connection information for client computers.

    Full-text search - Quickly creates full-text indexes on content and properties of structured and semistructured data to provide document filtering and word-breaking for SQL Server.

    SQL Server Active Directory Helper - Publishes and manages SQL Server services in Active Directory.

    SQL Writer - Allows backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework.

    Important
    Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. Other tools such as the Windows Services Control Manager can change the account name but do not change associated settings. If the service cannot access the SQL Server portion of the registry, the service may not start properly.

    Important
    For Analysis Services instances that you deploy in a SharePoint farm, always use SharePoint Central Administration to change the server accounts for PowerPivot service applications and the Analysis Services service. Associated settings and permissions are updated to use the new account information when you use Central Administration.

    http://msdn.microsoft.com/en-us/library/ms143504.aspx