SRSS – Get list of Reports and Its Data sources and Datasets

I needed to get the list of my reports and what data source and dataset they use.

use ReportServer;
WITH XMLNAMESPACES ( DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition’, ‘http://schemas.microsoft.com/SQLServer/reporting/reportdesigner’ AS rd )
SELECT distinct ReportName = name
,DataSetName = x.value(‘(@Name)[1]’, ‘VARCHAR(250)’)
,DataSourceName = x.value(‘(Query/DataSourceName)[1]’,’VARCHAR(250)’)
,CommandText = x.value(‘(Query/CommandText)[1]’,’VARCHAR(250)’)

FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
FROM ReportServer.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
) a
CROSS APPLY reportXML.nodes(‘/Report/DataSets/DataSet’) r ( x )
CROSS APPLY x.nodes(‘Fields/Field’) f(df)
ORDER BY name

image.png

 

 

Good Luck

SQL – Getting list of jobs and their steps

How to get list of jobs an their steps. For this I will use three tables dbo.sysjobs, dbo.sysjobsteps and dbo.sysjobschedules in msdb database.

You can create select from each of these tables to get relevant data.

select name,enabled,description from msdb.dbo.sysjobs
select job_id,step_id,step_name,command,database_name from msdb.dbo.sysjobsteps
select job_id,next_run_date,next_run_time from msdb.dbo.sysjobschedules

 

But we can combine these three tables, to get unified complex select.

SELECT
    job.name,
    job.enabled,
    job.description,
    steps.step_name,
    steps.command,
    steps.database_name,
    sch.next_run_date AS ‘Sched Date’,
    sch.next_run_time AS ‘Sched Time’
FROM msdb.dbo.sysjobs job JOIN
     msdb.dbo.sysjobsteps steps       
        ON job.job_id = steps.job_id INNER JOIN
    msdb.dbo.sysjobschedules AS sch ON job.job_id = sch.job_id
–where name like ‘%BACKUP%’ –you can also filter with where clause
order by name

 

 

 

Good Luck

SQL – Table comparison

For table comparison, i like to use Red Gate SQL compare. Great tool, but if you don’t have money for it, you can always try to write your own comparation script.
You can then script it as function, and use it. It does not have, options like SQL Compare, but for now it will do the trick.
This simple comparation, uses only names of columns to compare. But you can code it to check also field type and field length.

First of all, lets create two simple tables:

CREATE TABLE [dbo].[Table_One](
    [Column1] [nchar](10) NULL,
    [Column2] [varchar](50) NULL,
    [Column3] [int] NULL,
    [Coulmn4] [datetime] NULL,
    [Column10] [char](20) NULL
) ON [PRIMARY]

CREATE SCHEMA [test] AUTHORIZATION [dbo]
GO
CREATE TABLE [test].[Table_Two](
    [Column1] [nchar](10) NULL,
    [Column7] [int] NULL,
    [Column8] [int] NULL,
    [Coulmn9] [datetime] NULL,
    [Column10] [varchar](20) NULL
) ON [PRIMARY]

As you see, I left

Then, declare variables for table names. It will be easier to handle table names in one place.
DECLARE @T1 VARCHAR(30) = ‘Table_One’, –names without schemas
        @T2 VARCHAR(30) = ‘Table_Two’

 

Now, we just have to create comparison select. Selecting distinct values from information_schema view first for one table, than the other.

— In TableOne but not in TableTwo
SELECT DISTINCT
       @T1 AS [First table],
       ‘>>’ AS Dir, –Direction
       @T2 AS [Second table],
       ISC1.COLUMN_NAME,
       ISC1.DATA_TYPE        
  FROM INFORMATION_SCHEMA.COLUMNS ISC1
WHERE ISC1.COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS ISC2 WHERE ISC2.TABLE_NAME = @T2)
        AND ISC1.TABLE_NAME = @T1
UNION ALL
— In TableTwo but not in TableOne
SELECT DISTINCT
       @T1 AS [First_table],
       ‘<<‘ AS Dir, –Direction
       @T2 AS [Second_table],
       ISC1.COLUMN_NAME,
       ISC1.DATA_TYPE        
  FROM INFORMATION_SCHEMA.COLUMNS ISC1
WHERE ISC1.COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS ISC2 WHERE ISC2.TABLE_NAME = @T1)
        AND ISC1.TABLE_NAME = @T2
ORDER BY Dir DESC, COLUMN_NAME ASC

 

And the result is:
image

 

Good Luck

T-SQL: Get number from string

When you are working with custom software over your database, you can easily find some data you need to clean up. It can be trash from poorly written code, or users can write rubbish if no controls are established. So next couple of posts will be about searching and updating this kind of trash values.
First is getting a number from a string.
So, I had a task of exporting phone numbers in unified format, but numbers were in all variety:
+(385)11 1111-111
0038511/1111-111
etc.
I used to write WHILE loop which would read a character by character in string, and then get the number in numeric string.
Then I found out that there are couple of functions PATINDEX and STUFF which can help me out quickly.
This idea cam from this site SQLAuthoritiy from Pinal Dave.

PATINDEX(‘%pattern%’,expression) – returns first position of pattern
image
STUFF(expression,start,length,replace_string) – replaces length in expression from position start with replace string
image

Now, when we got this clear, create a function which will return only string. Function is created on database level. So if you want to use it everywhere you must have permission and use it like this: database.schema.function

use database
go

CREATE FUNCTION fnGetNumberFromString
(@strInput VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intNumber int
SET @intNumber = PATINDEX(‘%[^0-9]%’, @strInput) – set first location of number
BEGIN
WHILE @intNumber > 0 –until position number of number in string is null or 0
BEGIN
SET @strInput = STUFF(@strInput, @intNumber, 1, ” ) –replace string with ‘blank’
SET @intNumber = PATINDEX(‘%[^0-9]%’, @strInput )–next position of number
END
END
RETURN ISNULL(@strInput,0)
END
GO

Now just to test it

image 

That’s how you get numbers from string.

Good luck

TSQL – working with folders and output to file

This article shows how to create a folder for backup. The idea is to create folder with date part in its name, and then backup a specific database to that folder.

First we need to create a parameters to save our SQL command in it.
use master
declare @sql1 nvarchar(max)
declare @sql2 nvarchar(max)
declare @var1 varchar(100)
declare @var2 varchar(100)

–So first we create a folder, manually, called BACKUP. In this folder we will save our subfolders and backups.
set @var1 = N’"C:\BACKUP\SQLDB ‘+(select convert(varchar,getdate(),104))+’"’    –(CONVERT turns the date into dd.MM.yyyy format)

–save to @sql1 statement you wish to execute
set @sql1 = N’exec xp_create_subdir ‘+@var1

–next, we have to crate a variable which will contain path and name of a backup file.
set @var2 = N’C:\BACKUP\SQLDB ‘+(select convert(varchar,getdate(),104))+’\Database_’+convert(varchar,getdate(),112)+’.bkp’

–now, create @sql2 statement you wish to execute
set @sql2 =N’BACKUP DATABASE Database TO DISK = ‘ + QUOTENAME( @var2 , ”” )+’ WITH FORMAT, stats;’  –@var2 replaces path and filename of backup

 

Now execute both @sql1 and @sql2 statements

EXEC sp_executesql @sql1

EXEC sp_executesql @sql2

 

You can also export query result to file with small program called BCP.
To call BCP from SQL Query you need to use a function called xp_cmdshell. But to use this function, it must be enabled in SQL Server Advanced options.
The quickest way is to write TSQL for it

— Set advanced options to be changed.
EXEC sp_configure ‘show advanced options’, 1
GO
— Update the change
RECONFIGURE
GO
— To enable the xp_cmdshell feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
— To update the currently configured value for this feature.
RECONFIGURE
GO

 

Now we can export it with the following statement:

exec master..xp_cmdshell ‘bcp "select top 500 * from sysobjects" queryout "c:\id\tempexportfile.txt" -c -t, -T -S ‘+@@servername

path to file – is referenced to server side path
– C use default code page for char, varchar or text columns
-t     use TAB as field terminator
-T    use trusted connection to server
-S  use server –S servername