1. SSRS and Columns from Dynamic SQL

    In SSRS when you bind a stored proc, it does not always auto populate the column headers. You may end up adding all the columns manually. There is a quick work around for this.

    In your stored proc, use EXEC(” your sql query”) instead of storing the text in Variable and then executing the variable. SSRS plays nice if all your columns are laid out in your EXEC statment.

    For example this approach will not detect col1 and col2 in SSRS

    — Stored proc
    Declare @sql varchar(max)
    SET @sql = ”Select col1,col2 from table’
    exec @sql

    This will

    — Stored proc
    EXEC(‘Select col1,col2 from table’) 

    Reference:
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/c3c6bc0d-728c-4a6e-abeb-2ea961f38e44

    10 months ago  /  0 notes

  2. Deploy changes in SQL Server using sqlcmd utility

    Note: This is mainly for small to mid level team and project systems which does not need complex build and deployment processes.

    Recently I learned about SQLCMD utility which started shipping with SQL Server 2005 version. IMO you can use this tool and establish a very easy process to deploy database changes from one database to another. 

    Lets take an example of deploying 20 stored procedures to the production server. Normally I would go into the production server and execute them one by one but it can easily get tedious as the number of changes increase. 

    One better way to do this is by creating a Patch file and using SQLCMD to deploy changes. 

    Here are the steps: This method requires that you save your stored procedure on local drive.

    1. Create and save patch file

    Open text pad and copy paste below. Save file as “<name>.sql” , make sure all your stored procedures are in the same folder. We do this by saving this file under same source control folder where stored procs. are checked-in.

    SET NOCOUNT ON

    :error “<name>.err”

    :on error ignore

    USE [<Database>]

    GO

    PRINT ‘Running Procedures’

    :r “..<storedproc_name1>.sql”

    :r “..<storedproc_name2> .sql”

    :r “..<storedproc_name3> .sql”

    PRINT ‘End of Running Procedures’

    PRINT ‘The patch is done!’

    GO

    2. Now open Command prompt and browse to the folder where you have saved patch file. Make sure Patch file is in the same folder as stored procedure. Run below command in command prompt.

    sqlcmd -U <userid> -P <password> -S <server> -d <database> -i C:<folders><PatchFileName>.sql

    That is it. 

    All the changes will be deployed to whatever server and database name you provide above. 

    11 months ago  /  1 note

  3. Kill blocked process in SQL Server 2008

    http://amitchandnz.wordpress.com/2010/08/29/kill-process-sp_who2/

    Run sp_who2;
    To kill a process just enter the word kill followed by the SPID:
    kill SPID;
    The field Blkby will identify the SPID that is causing any blocks.

    1 year ago  /  0 notes