HTML Links


00/01/1900 in MS Excel

00/01/1900 is zero i.e. 0 formatted as a date ‘dd/mm/yyyy’
Custom Formats in MS Excel accept multiple arguments, so if you want a 0 but formatted as a blank then use a custom format
that’s 2 x ;; on the end, indicating put nothing if you get a zero

SSRS Configuration

> Internet > Tools > Options > Security > Local Intranet > Sites > Advanced. Add address of local server.
For me only – see files in vs\projects\ssrs\configuration

Save (Not Permitted) Dialog Box. Tables will need to be dropped and re-created

Tools > Options > Designers > Tables and Database Designers
Check or Uncheck > Prevent saving changes that require table re-creation

SQL Server Profiler – “Not enough memory was available for trace”

Need to enable AWE on SQL Server which is an element of memory
Before doing this need to follow
"How to: Enable the Lock Pages in Memory Option (Windows)"
Then "Enabling AWE Memory for SQL Server"
The following is copied from there:

The following example shows how to activate AWE and configure a limit of 1 GB for min server memory and 6 GB for max server memory.
First, configure AWE:
Copy Code

sp_configure 'show advanced options', 1

sp_configure 'awe enabled', 1

After SQL Server restarts, the following message is written to the SQL Server error log: "Address Windowing Extensions enabled."
Next, configure memory:
Copy Code

sp_configure 'min server memory', 1024

sp_configure 'max server memory', 6144


Dataset loses default connection My.MySettings when moved between projects

When moving dataset from one project to another, for example to create N-Tier then it may well lose the default connection. It is almost right but loses the My.MySettings bit.
Open DS.xsd file in notepad
Locate PropertyReference=”ApplicationSettings.ProjectName.MySettings.GlobalReference.Default.conStr…”
Change to PropertyReference=”ApplicationSettings.ProjectName.My.MySettings.GlobalReference.Default.conStrOperaDataset”

SQL Database Mail – User security to allow facility

Security > Logins > Pick Login > Properties > User Mapping > msdb > tick on
Database role membership include DatabaseMailUserRole
msdb has more database role memberships. May be resolution to LinkedServer issue. 
Procedure sp_send_dbmail, line 141 profile name is not valid
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @principal_name = 'ApplicationUser',
    @profile_name = 'AdventureWorks2008R2 Administrator Profile',
    @is_default = 1 ;