Monday, February 10, 2014

Changing SQL Server New Stored Procedure Template

You can change the default SQL Server's Stored Procedure Template by editing the following files. This will change the template which appears when you right click on Store Procedure folder (in object explorer) and select "New Stored Procedure..." Command

SQL Server 2008 R2
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\Stored Procedure\Create Stored Procedure (New Menu).sql

SQL Server 2012
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql\Stored Procedure\Create Stored Procedure (New Menu).sql

Note that these files may only be editable by Administrators only, so you may need to open your editor in Administrator mode.

Monday, July 16, 2012

LightSwitch: How to get Total Number of Records in all pages in a Multi-Page DataGrid

Level: Intermediate

Knowledge Required:
  • LightSwitch
  • Search Screen
  • DataGrid
  • Creating a new property and placing it on Screen

This post explains how to get the total number of records in all the pages of Multi-Page DataGrid.

Monday, May 14, 2012

T-SQL Search String From Right / Reverse Search

DECLARE @SomeText varchar(255);
DECLARE @TextToSearch varchar(10);

SET @SomeText = 'There are 3 spaces';
SET @TextToSearch = ' ';

Print CharIndex(@TextToSearch, @SomeText);
-- Following line will search @TextToSearch in @SomeText from right
Print
        CASE WHEN CharIndex(@TextToSearch, @SomeText) > 0 THEN
            (Len(@SomeText + '-') - 1) - 
                (CharIndex(Reverse(@TextToSearch), Reverse(@SomeText)) + (Len(@TextToSearch + '-')-1) - 1) + 1
        ELSE
            0
        END

-- Output:
-- -------------------------------------
-- 6
-- 12
-- -------------------------------------
-- Tips: 
--
-- Reverse() function reverses the string
--
-- Len(@SomeText + '-') - 1, returns the actual string length even if
-- @SomeText have space at the end. Note that Len() function ignores
-- the spaces at the end of string that is why we have placed a '-'
-- at the end of string then subtracted 1 from length so that Len()
-- function should return the correct length

Sunday, April 22, 2012

Loading and Disposing Crystal Reports on ASP.net Page

Recently one of my friend mentioned strange Crystal Reports behavior on ASP.net page. The scenario was simple,

  1. ASP.net web page
  2. A Button
  3. A CrystalReportViewer Control
  4. A simple Crystal Report

What he doing was, on clicking of button he was loading the report in CrsytalReportViewer control. The report was being displayed correctly, but when he was trying to Zoom In, Zoom Out or Exporting the report he was getting error “No valid report source is available”.