Thursday, September 22, 2011

How to stop and start RS server

Step 1:

DECLARE @Time VARCHAR(20), @cmdline VARCHAR(200)

SET @Time = CONVERT(VARCHAR(2),DATEPART(Hour, DATEADD(Mi, 1,GETDATE()))) +':' + CONVERT(VARCHAR(2), DATEPART(Mi, DATEADD(Mi, 1,GETDATE())))
SET @cmdline = 'AT \\svanwarsql02 '+@Time+' NET STOP ReportServer'
EXEC master..xp_cmdshell @cmdline

SET @Time = CONVERT(VARCHAR(2),DATEPART(Hour, DATEADD(Mi, 1,GETDATE()))) +':' + CONVERT(VARCHAR(2), DATEPART(Mi, DATEADD(Mi, 2,GETDATE())))
SET @cmdline = 'AT \\svanwarsql02 '+@Time+' NET START ReportServer'
EXEC master..xp_cmdshell @cmdline

Step 2:

Wait for 5 mins to allow step 1 to finish

WAITFOR delay '00:05:00'

How to search one specific object among databases

-- Run on the SVANWARSQL02

DECLARE @SearchString VARCHAR(255)

DECLARE @tblName    VARCHAR(300)      -- each table from select * from StagingData.dbo.stgETL0Cleanup delete from StagingData.dbo.stgETL0Cleanup where searchstr ='ch_ivr_audit'

DECLARE @db_name1    VARCHAR(300)
DECLARE @db_name2    VARCHAR(300)
DECLARE @sql        VARCHAR(500)
DECLARE @sql1       VARCHAR(500)
DECLARE @counter    INT
DECLARE @error_num INT

DECLARE @name VARCHAR(50)

SET @counter = 0
--set @name = 'blabla'
--select searchstr,@name from StagingData.dbo.stgETL0Cleanup

--TRUNCATE TABLE StagingData.dbo.ETL0_SearchObjectResults

--IF NOT EXISTS(SELECT * FROM StagingData..sysobjects WHERE ID = OBJECT_ID('StagingData.dbo.ETL0_SearchObjectResults'))
--   BEGIN
--    CREATE TABLE StagingData.dbo.ETL0_SearchObjectResults
--        (
--        sp_name                        VARCHAR(300),
--        dbName                        VARCHAR(300),
--        tblName                        VARCHAR(300),
--        ID                            VARCHAR(1000)
--        )
--    END

--TRUNCATE TABLE StagingData.dbo.ETL0_lkpResults

--IF NOT EXISTS(SELECT * FROM StagingData..sysobjects WHERE ID = OBJECT_ID('StagingData.dbo.ETL0_lkpResults'))
--   BEGIN
--    CREATE TABLE StagingData.dbo.ETL0_lkpResults
--        (
--        dbName                        VARCHAR(300),
--        tblName                        VARCHAR(300),
--        ID                            VARCHAR(1000)
--        )
--   END

--DECLARE tbl_cursor CURSOR FOR
--SELECT searchstr
--FROM StagingData.dbo.stgETL0Cleanup

DECLARE db_cursor CURSOR FOR
SELECT name                 -- get the list of all databases on this server
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerExternal','ReportServerExternalTempDB','ReportServerTempDB',
'Strata','TelcoReconciliation','TestForReplication','VanBMCProduction_Backup','MapInfo','RepMD','CommonEvent','CommonEvent_2011Q1',
'CommonEvent_2011Q2','CommonEvent_2011Q3','QuestSoftware','QuestWorkDatabase','ProdAdminDB_Backup','EventData_Detail_cw','Chr_Utils_WrkDB',
'VanReplicaOld'
)
ORDER by name


    OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

While @@FETCH_STATUS = 0
BEGIN
    --OPEN tbl_cursor
    --FETCH NEXT FROM tbl_cursor INTO @tblname

    --    While @@FETCH_STATUS = 0
    --        BEGIN

                --SET @counter = @counter + 1

                SET @db_name1 =@name+'.dbo.'+'syscomments'
                SET @db_name2 =@name+'.dbo.'+'sysobjects'

                SELECT @sql =
                'SELECT DISTINCT '''+@name+''',o.name,'+CONVERT(varchar(1000),@counter)+' From '+@db_name1+' s JOIN'+' '+@db_name2+' o ON s.id = o.id '

                --SELECT @sql = @sql+' WHERE s.text like '+'''%'+@tblname+'%'''
                SELECT @sql = @sql+' WHERE s.text like '+'''%'+'cc_optimal_settlement_toberemoved'+'%'''   --->>>>> individual search, change the name between % %
                --SELECT @sql ='INSERT INTO StagingData.dbo.ETL0_SearchObjectResults (sp_name,ID) '+@sql
                EXEC(@sql)

                Select @error_num = @@ERROR
                IF @error_num <> 0 Print @sql

                --SELECT @sql1 = 'SELECT '''+@name+''','''+@tblName+''','+CONVERT(varchar(1000),@counter)
                --SELECT @sql1 = 'INSERT INTO StagingData.dbo.ETL0_lkpResults(dbName,tblName,ID) '+@sql1

                --EXEC(@sql1)

                --Select @error_num = @@ERROR
                --IF @error_num <> 0 Print @sql


                --FETCH NEXT FROM tbl_cursor INTO @tblname

        --    END
        --Close tbl_cursor

    FETCH NEXT FROM db_cursor INTO @name
END

--DEALLOCATE tbl_cursor
Close db_cursor
DEALLOCATE db_cursor

--select distinct l.tblName,l.dbName,r.sp_name from StagingData.dbo.ETL0_SearchObjectResults r join StagingData.dbo.ETL0_lkpResults l on r.ID = l.ID order by l.tblName,l.dbName,r.sp_name

--SELECT DISTINCT o.name, 'Chr_Utils','ch_ivr_audit'
--From Chr_Utils.dbo.syscomments s JOIN Chr_Utils.dbo.sysobjects o ON s.id = o.id
--WHERE s.text like '%ch_ivr_audit%'

--, '+['@name']+','+['@tblname']+

SSRS error log file

C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\LogFiles

it varies depending on the environment you are working on

it details all the error happened on the RS server. Great way for trouble shooting…

Wednesday, September 21, 2011

Code for searching objects among databases and jobs

1. Among databases

-- Run on the SVANWARSQL02

DECLARE @SearchString VARCHAR(255)

DECLARE @name VARCHAR(50)

SET @counter = 0
--set @name = 'blabla'
--select searchstr,@name from StagingData.dbo.stgETL0Cleanup

TRUNCATE TABLE StagingData.dbo.ETL0_SearchObjectResults

IF NOT EXISTS(SELECT * FROM StagingData..sysobjects WHERE ID = OBJECT_ID('StagingData.dbo.ETL0_SearchObjectResults'))
   BEGIN
    CREATE TABLE StagingData.dbo.ETL0_SearchObjectResults
        (
        sp_name                        VARCHAR(300),
        dbName                        VARCHAR(300),
        tblName                        VARCHAR(300),
        ID                            VARCHAR(1000)
        )
    END

TRUNCATE TABLE StagingData.dbo.ETL0_lkpResults

IF NOT EXISTS(SELECT * FROM StagingData..sysobjects WHERE ID = OBJECT_ID('StagingData.dbo.ETL0_lkpResults'))
   BEGIN
    CREATE TABLE StagingData.dbo.ETL0_lkpResults
        (
        dbName                        VARCHAR(300),
        tblName                        VARCHAR(300),
        ID                            VARCHAR(1000)
        )
   END

DECLARE tbl_cursor CURSOR FOR
SELECT searchstr
FROM StagingData.dbo.stgETL0Cleanup

FETCH NEXT FROM db_cursor INTO @name

While @@FETCH_STATUS = 0
BEGIN
    OPEN tbl_cursor
    FETCH NEXT FROM tbl_cursor INTO @tblname

        While @@FETCH_STATUS = 0
            BEGIN

                SET @counter = @counter + 1

                SET @db_name1 =@name+'.dbo.'+'syscomments'
                SET @db_name2 =@name+'.dbo.'+'sysobjects'

                SELECT @sql =
                'SELECT DISTINCT '+'o.name,'+CONVERT(varchar(1000),@counter)+' From '+@db_name1+' s JOIN'+' '+@db_name2+' o ON s.id = o.id '

                SELECT @sql = @sql+' WHERE s.text like '+'''%'+@tblname+'%'''
                --SELECT @sql = @sql+' WHERE s.text like '+'''%'+@name+'%'''   --->>>>> individual search, change the name between % %
                SELECT @sql ='INSERT INTO StagingData.dbo.ETL0_SearchObjectResults (sp_name,ID) '+@sql
                EXEC(@sql)

                Select @error_num = @@ERROR
                IF @error_num <> 0 Print @sql

                SELECT @sql1 = 'SELECT '''+@name+''','''+@tblName+''','+CONVERT(varchar(1000),@counter)
                SELECT @sql1 = 'INSERT INTO StagingData.dbo.ETL0_lkpResults(dbName,tblName,ID) '+@sql1

                EXEC(@sql1)

                Select @error_num = @@ERROR
                IF @error_num <> 0 Print @sql


                FETCH NEXT FROM tbl_cursor INTO @tblname

            END
        Close tbl_cursor

    FETCH NEXT FROM db_cursor INTO @name
END

DEALLOCATE tbl_cursor
Close db_cursor
DEALLOCATE db_cursor

--select distinct l.tblName,l.dbName,r.sp_name from StagingData.dbo.ETL0_SearchObjectResults r join StagingData.dbo.ETL0_lkpResults l on r.ID = l.ID order by l.tblName,l.dbName,r.sp_name

--SELECT DISTINCT o.name, 'Chr_Utils','ch_ivr_audit'
--From Chr_Utils.dbo.syscomments s JOIN Chr_Utils.dbo.sysobjects o ON s.id = o.id
--WHERE s.text like '%ch_ivr_audit%'

--, '+['@name']+','+['@tblname']+

2. Among jobs

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- Instead of use nolock everytime

DECLARE @Str VARCHAR(100)

DECLARE tbl_cursor CURSOR FOR
SELECT searchstr
FROM StagingData.dbo.stgETL0Cleanup stc
WHERE stc.SearchStr not like 'ch%'

    OPEN tbl_cursor
    FETCH NEXT FROM tbl_cursor INTO @Str

        While @@FETCH_STATUS = 0
            BEGIN

SELECT @Str = '%' + @Str + '%'

                SELECT                            @str as SearchingName,
                                                CONVERT(VARCHAR(50),jo.name) AS JobName,
                                                jo.enabled,
                                                js.last_run_date,
                                                CONVERT(VARCHAR(30),js.database_name) AS dbname,
                                                CONVERT(VARCHAR(50), js.step_name) AS step_name,
                                                REPLACE(REPLACE(js.command, CHAR(10), ''), CHAR(13), '') AS Cmd,
                                                js.step_id
                FROM    msdb.dbo.sysjobs           jo
                JOIN      msdb.dbo.sysjobsteps js
                ON     jo.job_id     = js.job_id
                WHERE
                    ( jo.name    LIKE @Str
                        OR          js.step_name   LIKE @Str
                        OR          js.command       LIKE @Str
                      )

                ORDER BY jo.job_id, js.step_id

                FETCH NEXT FROM tbl_cursor INTO @Str

            END

Close tbl_cursor
DEALLOCATE tbl_cursor

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BI and other stuffs