SQL Azure DATETIME Functions

Microsoft SQL has always included a number of date and time functions.  These results of these functions were always based on the operating system for the machine the SQL server was running on.  But what results will you get when you’re dealing with SQL Azure?

The server(s) are all virtual.

They are all based on UTC, Coordinated Universal Time.  Yes, I know the acronym should be CUT.  Just go to Wikipedia if you want to try and make sense of this.

Since all these functions will run based on UTC, you’ll always get the same results, no matter which data center you choose to be your primary.

The table below shows the results from the following query run against a SQL Azure instance hosted in North Europe on 20th June 2011 07:08:53 UTC.

SELECT
    SYSDATETIME() as SYSDATETIME,
    SYSDATETIMEOFFSET() as SYSDATETIMEOFFSET,
    SYSUTCDATETIME() as SYSUTCDATETIME,
    CURRENT_TIMESTAMP as currenttimestamp,
    GETDATE() as getdate,
    GETUTCDATE() as getUTCdate;

Query
SYSDATETIME() 2011-06-20 07:08:53.1026073
SYSDATETIMEOFFSET() 2011-06-20 07:08:53.1026073 +00:00
SYSUTCDATETIME() 2011-06-20 07:08:53.1026073
CURRENT_TIMESTAMP 2011-06-20 07:08:53.113
GETDATE() 2011-06-20 07:08:53.113
GETUTCDATE() 2011-06-20 07:08:53.100

If you are like me, whenever you deal with international users, you already changed your servers to UTC, so taking this into consideration for SQL Azure should be nothing new.  If you’re not used to this.  Perhaps now is the time to consider the benefits to having your server use UTC, store UTC + offsets in your tables, and allow users to run with multiple time zones.

It sure makes scheduling easier that way.  Or at least it beats trying to figure out what time to hold a meeting between London the US and Mexico, both before and after Daylight Savings time kicks in!

Windows Azure Reporting missing .dll

I deployed my project successfully to Windows Azure, but got a surprise when I ran reports, they gave error mentioning a missing assembly Microsoft.ReportViewer.ProcessingObjectModel.dll

now I new how to add references to Microsoft.ReportViewer.Common.dll and Microsoft.ReportViewer.WebForms.dll but I couldn't find the missing one anywhere, but with a help from a colleague here is the workaround.

how did I get the third assembly, Microsoft.ReportViewer.ProcessingObjectModel.dll?

Apparently this assembly was found only in the GAC. Here is how you copy the file in the GAC :

1. Open command prompt (run as Adminsitrator)
2. cd C:\WINDOWS\assembly\GAC_MSIL\Microsoft.ReportViewer.ProcessingObjectModel
3. do dir.
4. You see either one or both of the following folder:
   8.0.0.0__b03f5f7f11d50a3a
   9.0.0.0__b03f5f7f11d50a3a
   10.0.0.0__b03f5f7f11d50a3a
5. cd to one of them, and do dir.
6. You should see the Microsoft.ReportViewer.ProcessingObjectModel.dll assembly.
7. You can perform copy operation to your preferred destination folder.

MsgBox or MessageBox Error on Windows Azure

Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotification or DefaultDesktopOnly style to display a notification from a service application.
so if you hit the error above, you have used MsgBox or MessageBox dialogue boxes to confirm the user click.

well the solution is simple use Ajax ConfirmButton 

see the link below for syntax:
http://www.asp.net/AJAX/AjaxControlToolkit/Samples/ConfirmButton/ConfirmButton.aspx

The report definition for report 'path' has not been specified

So I guess you hit the error like it did, reports were working fine in your local project, but when uploaded to windows azure they stopped working.

The full errormessage is:
The report definition for report 'path' has not been specified. Could not find file 'path'.

This error might occur when you try to run a local report (rdlc).

The solution is to set the property "Build Action" of the rdlc to "Content". Now your installer will copy the physical rdlc.

you have to mark all .rdlc files in your project as Content.

SSRS 2008 Tablix control Repeat Column Headers does not work

If you faced the issue which I have faced, that you created your nice looking report, and next thing you realise the Column headers do not repeat, no matter what you did was not good enough to fix it.

Here is the solution:

Select the Tablix that you want to repeat column headers for by clicking on it.

At the bottom of the screen, find the "Row Groups" and "Column Groups" section.

Click the small drop-down-arrow on the right side of that section, and select the Advanced Mode.

Now you'll see additional lines called (static) in rows and columns groups.

In the "Row Groups" section, locate the top-outermost "static" row and click on it.

In the properties pane, you need to set the following properties:

KeepTogether = True
KeepWithGroup = After
RepeatOnNewPage = True

All of these properties must be set for this to work properly.

Good luck!

MS SQL Create Clustered Index on All Tables

I was creating Windows Azure project, and was faced when uploading the database to SQL Azure, that many of the tables didn't have Clustered Indexes, thought of saving some time by writing a script.

This script will create Clustered Index on all tables which do not have clustered index.


DECLARE @table_name VARCHAR(50); -- table name 
DECLARE @col_name VARCHAR(50); -- column name 
DECLARE @idx_name VARCHAR(256); -- name for index 
DECLARE @cmd1 NVARCHAR(256); -- command syntax

DECLARE db_cursor CURSOR FOR 
SELECT     sys.sysobjects.name AS tbName, sys.syscolumns.name AS colName, 'idx_' + sys.sysobjects.name + '_' + sys.syscolumns.name as idxName
FROM         sys.sysobjects INNER JOIN
                      sys.syscolumns ON sys.sysobjects.id = sys.syscolumns.id INNER JOIN
                      sys.systypes ON sys.syscolumns.xtype = sys.systypes.xtype LEFT OUTER JOIN
                      sys.indexes ON sys.sysobjects.id = sys.indexes.object_id
WHERE     (sys.sysobjects.xtype = 'U')  AND (sys.systypes.name = 'int') AND (sys.syscolumns.colid = 1) AND
                      (sys.indexes.index_id = 0)
ORDER BY tbName;

OPEN db_cursor;  
FETCH NEXT FROM db_cursor INTO @table_name, @col_name, @idx_name;  

WHILE @@FETCH_STATUS = 0  
BEGIN  

SET @cmd1 = 'CREATE CLUSTERED INDEX ' + @idx_name + ' ON ' + @table_name + '(' + @col_name + ');'

EXECUTE sp_executesql @cmd1

       FETCH NEXT FROM db_cursor INTO @table_name, @col_name, @idx_name;
END  

CLOSE db_cursor; 
DEALLOCATE db_cursor;

SQL Find tables without Clustered Indexes

USE AdventureWorks ----Replace AdventureWorks with your DBName
GO

SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY [TABLE]
GO

Create SSL test Certificate

Open a Visual Studio Command Prompt

Change your active directory to the location where you wish to place your certificate

Enter the following command

makecert -r -pe -n "CN=AzureSSL" -sky 1 "azuressl.cer" -sv "azuressl.pvk" -ss My

after the process has succeeded

Now enter the following command

pvk2pfx -pvk "azuressl.pvk" -spc "azuressl.cer" -pfx "azuressl.pfx" -pi password1

and you are good to go, you have your test certificate