SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%table_name%'

Imports System.IO
Imports System.Data
Imports System.Text.RegularExpressions
Module CreateRDL

Sub Main()

Dim filepath As String = “c:\temp”
Dim ds As DataSet = DbTools.GetRDLs()
Try
System.Console.WriteLine(“total rdl for this ID: ” & ds.Tables(0).Rows.Count)
System.Threading.Thread.Sleep(1500)

For Each dr As DataRow In ds.Tables(0).Rows

Dim filename As String = dr.Item(“Name”).ToString() & “.rdl”
Dim folder As String = dr.Item(“path”).ToString().Replace(“/”, “\”)
Dim lastpos As Integer = folder.LastIndexOf(“\”)
folder = folder.Substring(0, lastpos)

Dim xml As String = dr.Item(“content”).ToString().Replace(“”, “”)
”xml = xml.Replace(“XYZDB”, “XYZDB_NEW”)

Dim newxml As String = xml

”newxml = xml.Replace(“XYZDB”, “XYZDB_NEW”)

SaveTextToFile(filepath & folder, filename, newxml)
System.Console.WriteLine(DateTime.Now & ” saving file ” & filepath & folder & “\” & filename)
System.Threading.Thread.Sleep(100)

Next

Catch ex As Exception
System.Console.WriteLine(ex.Message)
End Try
End Sub

Public Sub CreatePath(ByVal path As String)
Try
If Not Directory.Exists(path) Then
Directory.CreateDirectory(path)

End If

Catch ex As Exception
System.Console.WriteLine(ex.Message)
End Try
End Sub

Public Sub dummy()

‘Dim rgx As New Regex(“XYZDB”)
‘xml = rgx.Replace(xml, “XYZDB_NEW”)

‘xml = xml.Replace(“<DataSourceReference>XYZDB</DataSourceReference>”, “<DataSourceReference>netforumIAQA</DataSourceReference>”)
‘xml = xml.Replace(“<DataSource Name=””XYZDB””>”, “<DataSource Name=””XYZDB_NEW””>”)
‘xml = xml.Replace(“<DataSourceName>XYZDB</DataSourceName>”, “<DataSourceName>XYZDB_NEW</DataSourceName>”)

End Sub
Public Sub SaveTextToFile(ByVal FullPath As String, ByVal filename As String, ByVal strData As String, Optional ByVal ErrInfo As String = “”)

Dim bAns As Boolean = False
Dim objReader As StreamWriter
Try
CreatePath(FullPath)
objReader = New StreamWriter(FullPath & “\” & filename, True)
objReader.WriteLine(strData)
objReader.Close()
bAns = True
objReader.Dispose()
Catch Ex As Exception
ErrInfo = Ex.Message
System.Console.WriteLine(Ex.Message)
Finally

End Try

End Sub
End Module
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Public Class DbTools
#Region “check existing”

Public Shared Function GetRDLs() As DataSet

Dim getRequest As Boolean = False

Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyDataAdapter As SqlDataAdapter
Try

MyConnection = New SqlConnection(“server=servername;database=ReportServer;uid=username;pwd=password”)
‘remove 3 ids with huge number of rdls

Dim sql As String = “select [path], [name],convert(varchar(max), convert(varbinary(max), content)) as content from reportserver.dbo.catalog ” & _
” where content is not null and createdbyID NOT IN (‘E6BE2785-7123-4DE3-90F5-069BB019C739′,’2116DFFA-87EA-47E9-815A-45ADEC2357A1′,’174725B6-CFF1-4455-AC4D-C8E8AE272941’)”

MyDataAdapter = New SqlDataAdapter(sql, MyConnection)
MyDataAdapter.SelectCommand.CommandType = CommandType.Text
DS = New DataSet() ‘Create a new DataSet to hold the records.
MyDataAdapter.Fill(DS) ‘Fill the DataSet with the rows returned.

MyDataAdapter.Dispose()
MyConnection.Close()
Catch ex As Exception
System.Console.WriteLine(ex.Message)
End Try

Return DS

End Function

#End Region

End Class

https://www.sqlservercentral.com/Forums/Topic812372-149-1.aspx

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a35c5f07-8a75-43b8-a63e-19b1600aa34b/how-to-add-database-to-catalog-in-linked-server-?forum=sqldatabaseengine

good one

Using R in SQL Server Reporting Services (SSRS)

https://dba.stackexchange.com/questions/182227/sql-server-clears-plan-cache-and-execution-stats-periodically

SELECT

job.job_id,

notify_level_email,

name,

enabled,

description,

step_name,

command,

server,

database_name,

schedule_id

FROM

msdb.dbo.sysjobs job

INNER JOIN

msdb.dbo.sysjobsteps steps

ON job.job_id = steps.job_id

INNER JOIN msdb.dbo.sysjobschedules sch

on sch.job_id = job.job_id

WHERE

job.enabled = 1

https://stackoverflow.com/questions/3131160/create-procedure-permission-only

define a DML trigger to avoid it.

go to c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log

remove the SQLAGENT.# files.

DECLARE @OldestDate datetime
DECLARE @JobName varchar(256)
-- Keep Last 10 days
SET @OldestDate = GETDATE()-10
SET @JobName = 'daily_report_check'
EXEC msdb.dbo.sp_purge_jobhistory 
 @job_name=@JobName, 
 @oldest_date=@OldestDate