Script to update WSRepts table for SRS

SELECT *
FROM WSRepts
UPDATE WSRepts
SET ReportLocation='http://xpsql08:8080/ReportServer/TWO/WennSoft Service/Service Call Work Order'
where ReportReference='SV_Workorder_1'

Did this help you?  If so, please leave a comment!

Script to view index fragmentation

--Use a script like this to monitor index fragmentation:
SELECT d.name,
s.OBJECT_ID,
s.index_id,
s.index_type_desc,
s.avg_fragmentation_in_percent,
s.avg_page_space_used_in_percent
FROM sys.databases AS d
INNER JOIN sys.dm_db_index_physical_stats(NULL, NULL, NULL,
NULL, 'SAMPLED')
AS s ON d.database_id = s.database_id
WHERE d.NAME = 'DYNAMICS'
ORDER BY s.avg_fragmentation_in_percent DESC



Did this help you?  If so, please leave a comment!

Script to Move tempdb database

--To move tempdb database:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
ALTER DATABASE [mydb] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [mydb] MODIFY FILE ( NAME = [logicalname], FILENAME = 'V:\tempdb\tempdb.mdf' )
ALTER DATABASE [mydb] MODIFY FILE ( NAME = [logicalname], FILENAME = 'V:\tempdb\templog.ldf' )
ALTER DATABASE [mydb] SET ONLINE

Did this help you?  If so, please leave a comment!

SQL Scripts from Synergy - Example of cross join

--A LONG RUNNING QUERY
SELECT 'LETS CREATE A CARTESIAN PRODUCT'
GO  --WITHOUT THE GO, IT RUNS AS A SINGLE BATCH

SELECT *
FROM SV00400
CROSS JOIN SV00500


Did this help you?  If so, please leave a comment!

SQL Scripts from Synergy - example of table variable

--LET SAY I NEED THE DISTINCT LIST OF KEY VALUES FROM CONTRACT MASTER AND HISTORY
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM dbo.SV00500
UNION
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM dbo.SV00501
--I WILL BE DOING DIFFERENT THINGS WITH THE DATA RETURNED
--RATHER THAN RUN THE ABOVE QUERY MULTIPLE TIMES
--I CAN USE A TABLE VARIABLE
DECLARE @CONTRACTS AS TABLE(
 CUSTNMBR CHAR(15),
 ADRSCODE CHAR(15),
 Contract_Number CHAR(11),
 WSCONTSQ INT
 ,UNIQUE(CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ)  --THIS TRICKS SQL INTO GIVING ME AN INDEX
 )
INSERT INTO @CONTRACTS
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM dbo.SV00500
UNION
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM dbo.SV00501
--NOW I CAN USE MY TABLE VARIABLE PRETTY MUCH LIKE A NORMAL TABLE
SELECT CUSTNMBR,ADRSCODE,Contract_Number,WSCONTSQ
FROM @CONTRACTS
SELECT *
FROM dbo.SV00582 S
 INNER JOIN @CONTRACTS C
  ON S.CUSTNMBR=C.CUSTNMBR
   AND S.ADRSCODE=C.ADRSCODE
   AND S.Contract_Number=C.Contract_Number
   AND S.WSCONTSQ=C.WSCONTSQ


Did this help you?  If so, please leave a comment!

Script to turn on Auto Create Statistics

sp_helpdb TWO
GO
ALTER DATABASE TWO SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE TWO SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE TWO SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO

Did this help you?  If so, please leave a comment!

SQL Scripts from Synergy - examples for execution plans

--BASIC SELECT *
SELECT *
FROM dbo.SV00585

--SPECIFY COLUMNS
SELECT Contract_Number,WSCONTSQ,Equipment_ID
FROM dbo.SV00585
WHERE WSCONTSQ=1

--USING AN INDEX TO GET RID OF THE TABLE SCAN
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID
FROM dbo.SV00585 T
 INNER JOIN dbo.SV00501 H
  ON T.Contract_Number=H.Contract_Number
   AND T.WSCONTSQ=H.WSCONTSQ
   AND T.ADRSCODE=H.ADRSCODE
   AND T.CUSTNMBR=H.CUSTNMBR
WHERE T.WSCONTSQ=1

--EXAMPLE OF ELIMINATING TABLE SCAN ON DATE FUNCTION
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID
FROM dbo.SV00585 T
 INNER JOIN dbo.SV00501 H
  ON T.Contract_Number=H.Contract_Number
   AND T.WSCONTSQ=H.WSCONTSQ
   AND T.ADRSCODE=H.ADRSCODE
   AND T.CUSTNMBR=H.CUSTNMBR
WHERE DATEADD(day, 15, T.Schedule_Date) = '01/16/2017'
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID
FROM dbo.SV00585 T
 INNER JOIN dbo.SV00501 H
  ON T.Contract_Number=H.Contract_Number
   AND T.WSCONTSQ=H.WSCONTSQ
   AND T.ADRSCODE=H.ADRSCODE
   AND T.CUSTNMBR=H.CUSTNMBR
WHERE T.Schedule_Date = DATEADD(day, -15, '01/16/2017')


--EXAMPLE OF LIKE OPERATOR
SELECT A.Equipment_ID
FROM dbo.SV00400 A
WHERE A.Equipment_ID LIKE '%A%'

--IMPLICIT CONVERSIONS
DECLARE @NCHAR NCHAR(15),
  @CHAR CHAR(15)
SELECT @NCHAR='MAIN OFFICE',
  @CHAR='MAIN OFFICE'
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID,T.CUSTNMBR,T.ADRSCODE,H.*
FROM dbo.SV00585 T
 INNER JOIN dbo.SV00501 H
  ON T.Contract_Number=H.Contract_Number
   AND T.WSCONTSQ=H.WSCONTSQ
   AND T.ADRSCODE=H.ADRSCODE
   AND T.CUSTNMBR=H.CUSTNMBR
WHERE H.ADRSCODE=@NCHAR
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID,T.CUSTNMBR,T.ADRSCODE,H.*
FROM dbo.SV00585 T
 INNER JOIN dbo.SV00501 H
  ON T.Contract_Number=H.Contract_Number
   AND T.WSCONTSQ=H.WSCONTSQ
   AND T.ADRSCODE=H.ADRSCODE
   AND T.CUSTNMBR=H.CUSTNMBR
WHERE H.ADRSCODE=@CHAR

Did this help you?  If so, please leave a comment!

SQL Scripts from Synergy - view compiled execution plans

--Query to view compiled execution plans
SELECT [cp].[refcounts]
, [cp].[usecounts]
, [cp].[objtype]
, [st].[dbid]
, [st].[objectid]
, [st].[text]
, [qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp


Did this help you?  If so, please leave a comment!