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’
— Stored proc
EXEC(‘Select col1,col2 from table’)
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
:on error ignore
PRINT ‘Running Procedures’
:r “..<storedproc_name2> .sql”
:r “..<storedproc_name3> .sql”
PRINT ‘End of Running Procedures’
PRINT ‘The patch is done!’
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.
To kill a process just enter the word kill followed by the SPID:
The field Blkby will identify the SPID that is causing any blocks.