SSRS - Windows Command to Run Report

To run a report and save result to Excel

rs -i "C:/Temp/Report.rss"
    -s http://localhost/reportserver
    -e Exec2005
    -v ReportPath="/FolderName/ReportName"
    -v OutputFileName="OutputFilePath"
    -v Parameter1="' + @Parameter

MSSQL - Addhoc Select from An Excel File

To read data from external Excel file:

SELECT *
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
                                        'Excel 12.0;Database=C:\Temp\SomeExcelFile.xlsx;HDR=YES;IMEX=1',
                                        'SELECT * FROM [Sheet1$]')

MSSQL - Using Excel File as A LinkedServer

Add Excel file as a LinkedServer:

/****** Object:  LinkedServer [EXCELFILE]    Script Date: 9/9/2013 11:31:34 AM ******/
EXEC master.dbo.sp_dropserver @server=N'EXCELFILE', @droplogins='UserName'
GO

EXEC master.dbo.sp_addlinkedserver @server = N'EXCELFILE', @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\TEMP\SomeExcelFile.xlsx', @provstr=N'Excel 12.0'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXCELFILE', @useself=N'False', @locallogin=NULL, @rmtuser=NULL, @rmtpassword=NULL
GO

INSERT INTO WebEx.dbo.WebEx_Host SELECT * FROM [EXCELFILE]...[Sheet1$]
GO

SSRS - Adding Page Break

To add page break to a large result set (http://social.msdn.microsoft.com/Forums/sqlserver/en-US/276acd62-49e7-41ad-a50e-7e4b97e44dcc/tablix-paging-in-ssrs): 

In your scenario, you want to display 10 records in each page, we can achieve it follow the steps below.

a.      Click the Details group in the Row Groups pane.

b.      From the Tablix member Properties pane, expand “Group”-> “PageBreak”.

c.       Set the “BreakLocation” to “End” and set the “Disable” property to the expression like below (Disable pagebreak on export):
=IIF(Globals!RenderFormat.Name = nothing, IIF((rownumber(nothing) mod 30) = 0,false,true), true) 

MSSQL - Find Blocking SQL

The script below used to find the SQL which is in blocking.


-- use command below to find the spid of the block sql
SP_WHO2

-- Another way to find blocked processSELECT    spid,
    status,
    loginame=SUBSTRING(loginame,1,12),
    hostname=SUBSTRING(hostname,1, 12),
    blk = CONVERT(char(3), blocked),
    dbname=SUBSTRING(DB_NAME(dbid),1, 10),
    cmd,
    waittype
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)


-- use the spid to list the SQL
DECLARE @handle binary(20)

SELECT @handle = max(sql_handle)
FROM master..sysprocesses
WHERE spid = @SPID

-- print SQL
SELECT [text] 
FROM ::fn_get_sql(@handle)

MSSQL - Rebuild DB Index

Script to rebuild index on selected DB.

--Script to rebuild all indexes for databases

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR 

SELECT name FROM master.dbo.sysdatabases  
WHERE name IN ('DB1', 'DB2')  
ORDER BY 1 

OPEN DatabaseCursor 

FETCH NEXT FROM DatabaseCursor INTO @Database 

WHILE @@FETCH_STATUS = 0 

BEGIN 
   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''  

     -- create table cursor 

     EXEC (@cmd) 

    OPEN TableCursor  

    FETCH NEXT FROM TableCursor INTO @Table  

    WHILE @@FETCH_STATUS = 0  

    BEGIN  
        IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
        BEGIN
            -- SQL 2005 or higher command

           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

           EXEC (@cmd)

       END

       ELSE

       BEGIN

          -- SQL 2000 command

          DBCC DBREINDEX(@Table,' ',@fillfactor) 

       END

 

       FETCH NEXT FROM TableCursor INTO @Table  

   END  

 

   CLOSE TableCursor   

   DEALLOCATE TableCursor 

   FETCH NEXT FROM DatabaseCursor INTO @Database 

END  

CLOSE DatabaseCursor  

DEALLOCATE DatabaseCursor

MSSQL - User Account Maintenance

Script here allows you to drop a user account from a SQL Server.

SET QUOTED_IDENTIFIER OFF
GO

-- Remove user from each database where the account exists

EXEC sp_Msforeachdb
    "IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'DOMAIN\UserName') 
         DROP USER [DOMAIN\UserName]"
GO

-- Remove login from this SQL Server

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'DOMAIN\UserName')
    DROP LOGIN [DOMAIN\UserName] 
GO