Access a SQL server without a password. After installing SQL server on a machine, it happens that you connect or disconnect that machine to domain. When you do this, the administrator account can no longer access the database engine. The below steps allow you to regain access to the SQL server Stop SQL…
–For MS SQL Server 2012 and above USE [master]; DECLARE @kill varchar(8000) = ”; SELECT @kill = @kill + ‘kill ‘ + CONVERT(varchar(5), session_id) + ‘;’ FROM sys.dm_exec_sessions WHERE database_id = db_id(‘MyDB’) EXEC(@kill); –For MS SQL Server 2000, 2005, 2008 USE master; DECLARE @kill varchar(8000); SET @kill = ”; SELECT @kill = @kill + ‘kill…
USE [master] GO /****** Object: StoredProcedure [dbo].[sp_who3] Script Date: 24/06/2016 15:29:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_who3] AS ——————————————————————————————————– — mimics sp_who2 with query text — SPID , Status , Login , HostName , BlkBy , DBName , Command , CPUTime , DiskIO , LastBatch , ProgramName , SPID…
DBCC TRACESTATUS (-1 ); ————————————– —–TRACEON ————————————– DBCC TRACEON (1117, -1 ); DBCC TRACEON (1118, -1 ); DBCC TRACEON (1222, -1 ); DBCC TRACEON (3226, -1 ); DBCC TRACEON (4199, -1 ); ————————————– —–TRACEOFF ————————————– –DBCC TRACEOFF — (1117, -1 — ); –DBCC TRACEOFF — (1118, -1 — ); –DBCC TRACEOFF — (1222, -1 —…
USE [msdb]; GO /****** Object: Job [USER Integrity Check main] Script Date: 19/04/2016 14:06:30 ******/ BEGIN TRANSACTION; DECLARE @ReturnCode INT; SELECT @ReturnCode = 0; /****** Object: JobCategory [Database Maintenance] Script Date: 19/04/2016 14:06:30 ******/ IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N’Database Maintenance’ AND category_class = 1 ) BEGIN EXEC @ReturnCode…
USE [master]; GO /****** Object: StoredProcedure [dbo].[DatabaseIntegrityCheck] Script Date: 19/02/2016 09:58:06 ******/ –DROP PROCEDURE [dbo].[DatabaseIntegrityCheck]; –GO /****** Object: StoredProcedure [dbo].[DatabaseIntegrityCheck] Script Date: 19/02/2016 09:58:06 ******/ SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck] @Databases NVARCHAR(MAX), @CheckCommands NVARCHAR(MAX)= ‘CHECKDB’, @PhysicalOnly NVARCHAR(MAX)= ‘N’, @NoIndex NVARCHAR(MAX)= ‘N’, @ExtendedLogicalChecks NVARCHAR(MAX)= ‘N’, @TabLock NVARCHAR(MAX)= ‘N’, @FileGroups NVARCHAR(MAX)=…
USE [msdb]; GO /****** Object: Job [USER INDEXES] Script Date: 18/02/2016 16:54:31 ******/ BEGIN TRANSACTION; DECLARE @ReturnCode INT; SELECT @ReturnCode = 0; /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 18/02/2016 16:54:31 ******/ IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]’ AND category_class = 1 ) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category…
/* Back up the master, msdb, and model databases to ensure that you can roll back to a previous configuration if problems occur. master: This database hosts system-level information. Back up this database regularly. model: You need to back up the template database only after you have made modifications. Because changes to the template database…
USE [MASTER]; GO SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO CREATE PROCEDURE dbo.CommandExecute @Command NVARCHAR(MAX), @CommandType NVARCHAR(MAX), @Mode INT, @Comment NVARCHAR(MAX)= NULL, @DatabaseName NVARCHAR(MAX)= NULL, @SchemaName NVARCHAR(MAX)= NULL, @ObjectName NVARCHAR(MAX)= NULL, @ObjectType NVARCHAR(MAX)= NULL, @IndexName NVARCHAR(MAX)= NULL, @IndexType INT= NULL, @StatisticsName NVARCHAR(MAX)= NULL, @PartitionNumber INT= NULL, @ExtendedInfo XML= NULL, @LogToTable NVARCHAR(MAX), @Execute NVARCHAR(MAX) AS…
USE [msdb]; GO IF ( SELECT 1 FROM [dbo].[sysoperators] WHERE name = ‘DBA_Alerts’ ) IS NULL BEGIN EXEC msdb.dbo.sp_add_operator @name = N’DBA_Alerts’, @enabled = 1, @pager_days = 0, @email_address = N’DBA_Alerts@dwf.co.uk’; END; ELSE BEGIN PRINT ‘Operator Already Exists’; END; GO USE [msdb]; GO IF ( SELECT 1 FROM [dbo].[sysalerts] WHERE name = ‘Corruption Alert 823…
USE [msdb]; GO EXEC master.dbo.xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’, N’UseDatabaseMail’, N’REG_DWORD’, 1; GO EXEC master.dbo.xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’, N’DatabaseMailProfile’, N’REG_SZ’, N’SQL_Alerting’; GO EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 1; GO –RESTART SQL SERVER AGENT
–Add TempDB Files to number of prcessors (up to 8) DECLARE @dbname VARCHAR(200); DECLARE @datafilename VARCHAR(200); DECLARE @logfilename VARCHAR(200); DECLARE @cpucount SMALLINT; DECLARE @datafilepath VARCHAR(200); DECLARE @logilepath VARCHAR(200); DECLARE @datasizeMB INT; DECLARE @logsizeMB INT; DECLARE @datafilegrowthMB INT; DECLARE @logfilegrowthMB INT; DECLARE @sqlstring VARCHAR(8000); DECLARE @loop SMALLINT; SELECT @dbname = ‘tempdb’; SELECT @datasizeMB = 1024; SELECT…
— Focus on these settings: — backup compression default (should be 1 in most cases) — clr enabled (only enable if it is needed) — cost threshold for parallelism (depends on your workload) 50 is good — lightweight pooling (should be zero) — max degree of parallelism (depends on your workload) 4-8 — max server…
USE [msdb]; GO /****** Object: Job [(00:00) Maintenance – CycleErrorLogs] Script Date: 06/10/2014 11:19:43 ******/ BEGIN TRANSACTION; DECLARE @ReturnCode INT; SELECT @ReturnCode = 0; /****** Object: JobCategory [DBA_Maintenance] Script Date: 06/10/2014 11:19:43 ******/ IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N’DBA_Maintenance’ AND category_class = 1 ) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category…
USE [master]; GO — setup ALTER DATABASE [msdb] MODIFY FILE (NAME = N’MSDBData’, SIZE = 51200 KB, FILEGROWTH = 204800 KB ); GO ALTER DATABASE [msdb] MODIFY FILE (NAME = N’MSDBLog’, SIZE = 51200 KB, MAXSIZE = UNLIMITED, FILEGROWTH = 204800 KB ); GO
SELECT st.dbid AS QueryExecutionContextDBID, DB_NAME(st.dbid) AS QueryExecContextDBNAME, st.objectid AS ModuleObjectId, SUBSTRING(st.TEXT, dmv_er.statement_start_offset/2 + 1, (CASE WHEN dmv_er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2 ELSE dmv_er.statement_end_offset END – dmv_er.statement_start_offset)/2) AS Query_Text, dmv_tsu.session_id , dmv_tsu.request_id, dmv_tsu.exec_context_id, (dmv_tsu.user_objects_alloc_page_count – dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts, (dmv_tsu.internal_objects_alloc_page_count – dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts, dmv_er.start_time, dmv_er.command, dmv_er.open_transaction_count, dmv_er.percent_complete, dmv_er.estimated_completion_time, dmv_er.cpu_time, dmv_er.total_elapsed_time, dmv_er.reads,dmv_er.writes, dmv_er.logical_reads, dmv_er.granted_query_memory, dmv_es.HOST_NAME,…
SELECT DB_NAME(dbid) AS [Database Name], COUNT(dbid) AS [No Of Connections], loginame AS [Login Name] FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame;
— High VLF counts can affect write performance — and they can make full database restores and crash recovery take much longer — Try to keep your VLF counts under 200 in most cases (depending on log file size) CREATE TABLE #VLFInfo ( FileID INT, FileSize BIGINT, StartOffset BIGINT, FSeqNo BIGINT, [Status] BIGINT, Parity BIGINT,…
declare @cmd nvarchar(200) set @cmd = ‘move \\server\UserDB\*.bak \\server\UserDB\Archive’ exec xp_cmdshell @cmd
DECLARE @query VARCHAR(1000), @dbname VARCHAR(1000), @count INT; SET NOCOUNT ON; DECLARE csr CURSOR FAST_FORWARD READ_ONLY FOR SELECT name FROM sys.databases; CREATE TABLE ##loginfo ( dbname VARCHAR(100), num_of_rows INT ); OPEN csr; FETCH NEXT FROM csr INTO @dbname; WHILE(@@fetch_status -1) BEGIN CREATE TABLE #log_info ( RecoveryUnitId TINYINT, fileid TINYINT, file_size BIGINT, start_offset BIGINT, FSeqNo INT, [status]…
Error message when you open SQL Server Configuration Manager in SQL Server: “Cannot connect to WMI provider. You do not have permission or the server is unreachable” https://support.microsoft.com/en-us/kb/956013
Summary The Disk Cleanup executable file cleanmgr.exe and the associated Disk Cleanup button are not present in Windows Server® 2008 or in Windows Server® 2008 R2 by default. Cause This is by design, as the Disk Cleanup button is part of the Desktop Experience feature. In order to have Disk Cleanup button appear on a disk’s Properties dialog,…
Static Tables – Set Fill Factor at 100 (or default server fill factor), As these tables are never changing, keeping the Fill Factor at 100 is the best option. They conserve the space, and also there is no fragmentation. Tables Updated Less Often – Set Fill Factor at 95. These tables are more or less…
— 25 missing indexes that will improve performance the most SELECT TOP 25 dm_mid.database_id AS DatabaseID, DB_NAME(dm_mid.database_id) AS [Database], dm_migs.avg_user_impact * ( dm_migs.user_seeks + dm_migs.user_scans ) AS [Avg Estimated Impact], dm_migs.last_user_seek AS [Last Usage Time], OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [Table], ‘CREATE INDEX [IDX_’ + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + ‘_’ + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ”), ‘, ‘, ‘_’), ‘[‘, ”),…
SELECT( cpu_count / hyperthread_ratio ) AS NumberOfPhysicalCPUs, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE(( cpu_count – hyperthread_ratio ) / ( cpu_count / hyperthread_ratio )) END AS NumberOfCoresInEachCPU, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE( cpu_count / hyperthread_ratio ) * (( cpu_count – hyperthread_ratio ) / ( cpu_count / hyperthread_ratio )) END AS TotalNumberOfCores,…
Classic way to check the database is alright. DBCC CHECKDB(‘DatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS
In the SQL Server Management Studio, to find out details of the active transaction, execute following command DBCC opentran() You will get the detail of the active transaction, then from the SPID of the active transaction. For example, if SPID is 69 then execute the command as sp_who2 69 sp_lock 69 Now , you can…
SELECT SJ.name as job_name , SJ.enabled as is_job_enabled , SS.enabled as is_schedule_enabled /*, ISNULL(SJ.description, ”) as job_desc*/ , SS.name as schedule_name , CASE freq_type WHEN 1 THEN ‘Occurs on ‘ + STUFF(RIGHT(active_start_date, 4), 3,0, ‘/’) + ‘/’ + LEFT(active_start_date, 4) + ‘ at ‘ + REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3,…
– Unused Index Script SELECT TOP 25 o.name AS ObjectName , i.name AS IndexName , i.index_id AS IndexID , dm_ius.user_seeks AS UserSeek , dm_ius.user_scans AS UserScans , dm_ius.user_lookups AS UserLookups , dm_ius.user_updates AS UserUpdates , p.TableRows , ‘DROP INDEX ‘ + QUOTENAME(i.name) + ‘ ON ‘ + QUOTENAME(s.name) + ‘.’ + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS ‘drop statement’…
SELECT DISTINCT wt.wait_type FROM sys.dm_os_waiting_tasks AS wt JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id WHERE s.is_user_process = 0
DECLARE @CounterPrefix NVARCHAR(30) SET @CounterPrefix = CASE WHEN @@SERVICENAME = ‘MSSQLSERVER’ THEN ‘SQLServer:’ ELSE ‘MSSQL$’ + @@SERVICENAME + ‘:’ END ; – Capture the first counter set SELECT CAST(1 AS INT) AS collection_instance , [OBJECT_NAME] , counter_name , instance_name , cntr_value , cntr_type , CURRENT_TIMESTAMP AS collection_time INTO #perf_counters_init FROM sys.dm_os_performance_counters WHERE ( OBJECT_NAME = @CounterPrefix + ‘Access Methods’ AND counter_name = ‘Full…
SELECT TOP 10 execution_count , statement_start_offset AS stmt_start_offset , sql_handle , plan_handle , total_logical_reads / execution_count AS avg_logical_reads , total_logical_writes / execution_count AS avg_logical_writes , total_physical_reads / execution_count AS avg_physical_reads , t.text FROM sys.dm_exec_query_stats AS s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t ORDER BY avg_physical_reads DESC
SELECT TOP 10 wait_type , max_wait_time_ms wait_time_ms , signal_wait_time_ms , wait_time_ms – signal_wait_time_ms AS resource_wait_time_ms , 100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits , 100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits , 100.0 * ( wait_time_ms – signal_wait_time_ms ) / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits FROM sys.dm_os_wait_stats WHERE…
To back up the tail of the transaction log 1 After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree. 2 Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database….
Types of Replication Microsoft SQL Server provides the following types of replication for use in distributed applications: Transactional replication. For more information, see Transactional Replication. Merge replication. For more information, see Merge Replication. Snapshot replication. For more information, see Snapshot Replication.
Index type Description Clustered A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values. Non clustered A non clustered…
Database Engine Stored Procedures (Transact-SQL) SQL Server 2014 QL Server supports the following system stored procedures that are used for general maintenance of an instance of SQL Server. sp_add_data_file_recover_suspect_db sp_executesql sp_add_log_file_recover_suspect_db sp_getapplock sp_addextendedproc sp_getbindtoken sp_addextendedproperty sp_help sp_addmessage sp_helpconstraint sp_addtype sp_helpdb sp_addumpdevice sp_helpdevice sp_altermessage sp_helpextendedproc sp_attach_db sp_helpfile sp_attach_single_file_db sp_helpfilegroup sp_autostats sp_helpindex sp_bindefault sp_helplanguage sp_bindrule sp_helpserver sp_bindsession…
READ UNCOMMITTED Specifies that statements can read rows that have been modified by other transactions but not yet committed. Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would…
CONCAT Function Prior to SQL Server 2012, we used to use the “+” operator to combine or concatenate two or more string values but starting with SQL Server 2012, we can use CONCAT function to perform this type of operation more neatly. For example, the below script and screenshot shows usage of the “+” operator…
STUFF Function We looked at using the CONCAT function to concatenate or combine two or more string values. With this function, string values are appended at the end of the prior string but there are times when you want to insert a string value into another string value or replace a part of one string…
To analyze further, the following query can be used to find out which indexes don’t have clustered indexes defined: WITH CTE_1 AS ( SELECT db_name() as dbname, o.name as tablename, (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = i.index_id AND i.object_id = p.object_id) as number_of_rows FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id =…
These three issues are all related, yet very different. When we deal with reports of problems, it’s essential to clearly identify what is actually happening. Sometimes, we need clarification by asking about the symptoms, instead of accepting what the user says without delving any deeper. The root cause of a problem may not even be…
SET XACT_ABORT is helpful in many ways. example: suppose you are working on a stored procedure which is doing a complex data manipulation. You though this query will run with in 1 minute when you run it from .NET or other code , but some how it took quite long time and with in one…
Locking hint Description HOLDLOCK Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE. NOLOCK Do not issue shared locks and do not honor exclusive locks. When this option is in effect,…
Errors 823,824, and 825 To do this, run the following command: USE msdb GO EXEC msdb.dbo.sp_add_alert @name = N’823 – Read/Write Failure’, @message_id = 823, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 1 GO EXEC msdb.dbo.sp_add_alert @name = N’824 – Page Error’, @message_id = 824, @severity = 0, @enabled =…
DBCC DBREINDEX This statement is used to recreate the indexes for a particular table. This statement rebuilds indexes in a single step. It also assigns fresh pages to reduce internal and external fragmentation. DBCC DBREPAIR This statement is used to drop or delete a damaged database. However, this command is no longer available with Microsoft…
As we use PERFMON – Performance Monitor to identify real time happening on SQL Server, Here using a DMV “sys.dm_os_performance_counters” may be used to calculate number of transactions on SQL Server as DECLARE @Counter bigint SELECT @Counter = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘Transactions/sec’ AND object_name=‘SQLServer:Databases’ AND instance_name =‘Test’ – Your DB Name – Wait for…
DECLARE @ret XML; EXEC @ret = [ufn_Common_Layout_function] @pLayout=@Layoutfull,@pValues=@Data SELECT @ret AS [result]
MS SQL 2005+ SELECT @@servername AS instance_name ,a.name AS database_name, c.data_Size_MBs AS data_size , b.log_Size_MBs AS log_size ,a.total_Size_MBs AS total_size FROM ( SELECT d.name, ROUND(SUM(mf.size) * 8 / 1024, 0) total_Size_MBs FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id WHERE d.database_id > 4 — Skip system databases GROUP BY d.name ) AS…
SELECT P.[publication] AS [Publication Name] ,A.[publisher_db] AS [Database Name] ,A.[article] AS [Article Name] ,A.[source_owner] AS [Schema] ,A.[source_object] AS [Table] FROM [distribution].[dbo].[MSarticles] AS A INNER JOIN [distribution].[dbo].[MSpublications] AS P ON (A.[publication_id] = P.[publication_id]) ORDER BY P.[publication], A.[article];
SELECT @@SERVERNAME AS server_name, sjs.last_run_outcome,sj.enabled, sj.name, sjs.step_id, sjs.step_name, sjs.database_name, CAST(CAST(sjs.last_run_date AS varchar(10)) AS datetime) AS failed_date FROM msdb.dbo.sysjobsteps AS sjs INNER JOIN msdb.dbo.sysjobs AS sj ON sjs.job_id = sj.job_id WHERE (sjs.last_run_date <> 0) AND (CAST(CAST(sjs.last_run_date AS varchar(10)) AS datetime) > GETDATE() – 2) ORDER BY sj.name
SELECT r.session_id, r.command, CONVERT(NUMERIC(6,2),r.percent_complete)AS [Percent Complete], CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) AS [SQL Command] FROM sys.dm_exec_requests AS r WHERE r.command IN (‘RESTORE DATABASE’,’BACKUP DATABASE’)
select pvt.abc,pvt.def,pvt.ghi from ( SELECT ConnectionName, Connection.ConnectionString FROM dbo.Global_Applications_ConnectionString AS Connection ) AS SourceTable pivot ( min(ConnectionString) for ConnectionName in (abc,def,ghi) ) as pvt
Visual studio: How to remove projects from the recent projects list. Open the registry by clicking Start (Start > Run) and type RegEdit > (enter) The Registry Editor will open. Drill down the following path… HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\ProjectMRUList… This is where you recent projects are stored in Visual Studio 2008. (HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\ProjectMRUList for Visual Studio 2008).
select article, * from dbo.MSreplication_objects where article like ‘Table_name’ select sp.name, ss.srvname, sa.name from sysarticles sa join syssubscriptions ss on ss.artid = sa.artid join syspublications sp on sa.pubid = sp.pubid where sa.name like ‘table_name’
SELECT DB_NAME() AS [Database], T.TABLE_NAME AS [Tables without Primary Keys] FROM INFORMATION_SCHEMA.TABLES AS T WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND T.TABLE_NAME = TC.TABLE_NAME) AND T.TABLE_TYPE = ‘BASE TABLE’
USE master; GO — Create the Event Session IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’DBDeletedCreated’) DROP EVENT SESSION DBDeletedCreated ON SERVER; GO CREATE EVENT SESSION DBDeletedCreated ON SERVER ADD EVENT sqlserver.object_created ( SET collect_database_name = (1) ACTION(sqlserver.nt_username,sqlserver.session_id,sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.sql_text) WHERE object_type = ‘DATABASE’ ), ADD EVENT sqlserver.object_deleted( SET collect_database_name = (1) ACTION(sqlserver.nt_username,sqlserver.session_id,sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.sql_text) WHERE object_type = ‘DATABASE’ )…
SELECT CustomerID , SalesOrderID , CAST(OrderDate AS DATE) AS OrderDate , TotalDue , LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS TD FROM Sales.SalesOrderHeader ORDER BY CustomerID
CREATE PROCEDURE dbo.ParseXMLtoTable @strXML AS XML ,@rootnode NVARCHAR(255) AS BEGIN SET NOCOUNT ON DECLARE @strText AS NVARCHAR(MAX) ,@idoc INT ,@id INT ,@parentid INT IF OBJECT_ID(‘tempdb..#ChildList’) IS NOT NULL DROP TABLE #ChildList CREATE TABLE #ChildList ( [RowNum] INT IDENTITY(1,1) NOT NULL, [parentid] INT NULL, [id] INT NULL, PRIMARY KEY (RowNum)) IF OBJECT_ID(‘tempdb..#NodeList’) IS NOT NULL DROP…
IF EXISTS (SELECT * FROM sys.objects WHERE type = ‘P’ AND name = ‘Usp_findstring’) DROP PROCEDURE Usp_findstring GO CREATE PROC Usp_findstring (@INPUTSTRING VARCHAR(max), @SEARCHSTRING VARCHAR(500)) AS BEGIN DECLARE @OCCURENCES AS INT, @POSITION AS INT, @RETURNVALUE INT SET @OCCURENCES = 0 SET @POSITION = 0 WHILE @POSITION < Len(@INPUTSTRING) BEGIN IF Charindex(@SEARCHSTRING, @INPUTSTRING, @POSITION) > 0…
USE [Leads] GO SET ANSI_NULLSON GO SET QUOTED_IDENTIFIERON GO CREATE PROCEDURE [dbo].[sp_PingBLSWebsites] AS DECLARE @counter int DECLARE @result VARCHAR(15) DECLARE @ip varchar(15) DECLARE @web varchar(100) DECLARE @webs varchar(100) set @counter = 0 — ping all the IP’s WHILE @counter <(SELECT COUNT(*)AS blscount FROM dbo.keywords_domains where URLip isnull) BEGIN SELECT top 1 @web=replace([url],’http://’,”),@webs=replace([url],’http://’,”) FROM keywords_domains WHERE…
Option Explicit ‘ Variables Dim objWSH, sProfile, objFolder Dim objFSO, sProfileRoot, objProfileFolder Dim sTemp, sWindows ‘ Object Initialisation set objFSO=CreateObject(“Scripting.FileSystemObject”) ‘ Get user profile root folder set objWSH = CreateObject(“WScript.Shell”) sTemp = objWSH.ExpandEnvironmentStrings(“%TEMP%”) sWindows = objWSH.ExpandEnvironmentStrings(“%WINDIR%”) sProfile = objWSH.ExpandEnvironmentStrings(“%USERPROFILE%”) sProfileRoot=objFSO.GetFolder(sProfile).ParentFolder.Path set objWSH=nothing ‘ Main Code set objProfileFolder=objFSO.GetFolder(sProfileRoot) for each objFolder in objProfileFolder.SubFolders…
ipconfig /all This option displays the same IP addressing information for each adapter as the default option. Additionally, it displays DNS and WINS settings for each adapter. ipconfig /release This option terminates any active TCP/IP connections on all network adapters and releases those IP addresses for use by other applications. ‘ipconfig /release” can be used…
Dim oShell Set oShell = WScript.CreateObject (“WScript.Shell” Dim counter counter = 0 Do counter = counter + 1 oShell.Run “C:\php\php.exe d:\data\webapps\php\crawler_all.php”, 9 WScript.Sleep 10000 Loop Until counter = 18
— “DT” is an abbreviation for “DATETIME”, not “DATE” DECLARE @StartDT DATETIME ,@EndDT DATETIME ; SELECT @StartDT = ‘2000-01-01 10:30:50.780’ ,@EndDT = ‘2000-01-02 12:34:56.789’ ; –===== Display the dates and the duration SELECT StartDT = @StartDT ,EndDT = @EndDT ,Duration = @EndDT-@StartDT ;
USE [leads] GO /****** Object: StoredProcedure [dbo].[sp_get_ip_address] Script Date: 02/22/2013 17:08:45 ******/ SET ANSI_NULLSON GO SET QUOTED_IDENTIFIERON GO CREATE Procedure [dbo].[sp_get_ip_address](@web varchar(100), @ip varchar(15) out) as begin Declare @ipLine varchar(200) Declare @pos int DECLARE @strCmd VARCHAR(60) DECLARE @strServerIP VARCHAR(100) DECLARE @result VARCHAR(15) set nocounton set @ip =null set @strServerIP =@web SELECT @strCmd =‘ping ‘+ @strServerIP Insert…
How to Fix SQL Server Database Error 3707? 1. ALTER Database leads SET Emergency; GO 2. ALTER Database leads Set Single_User GO 3. DBCC CHECKDB (leads, REPAIR_ALLOW_DATA_LOSS) With ALL_ERRORMSGS; GO 4. ALTER Database leads set multi_user GO
USE [PingService] GO /****** Object: StoredProcedure [dbo].[sp_PingServer_failed_sendemail_now] Script Date: 02/26/2010 16:33:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO — ============================================= — Author: Clive Roberts — Create date: 30 november 2007 — Description: sends infomation to notification services to send email — ============================================= CREATE PROCEDURE [dbo].[sp_PingServer_failed_sendemail_now] — Add the parameters for the stored procedure here @customers_domain…
EXEC @result=Master..xp_cmdShell @strCmd
backup log Leads with TRUNCATE_ONLY dbcc shrinkdatabase (Leads , TRUNCATEONLY)
DBCC SHRINKDATABASE (N’Leads’, 0,TRUNCATEONLY)
backup.sql …. BACKUP DATABASE [AdaptiveCR] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdaptiveCR.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdaptiveCR-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO MF20bak.cmd…. sqlcmd -S ICOWK077\AMCR -i C:\backup.sql Scheduled Tasks to call MF20bak.cmd
(16777216 * CONVERT(float, PARSENAME(URLip, 4))
PARSENAME(URLip, 4)
— Declare variables to use DECLARE @x int DECLARE @Price money DECLARE @LENGTH int DECLARE @prefix1 varchar(100) DECLARE @Match varchar (100) — Test for supplied value SET @Price = (select price from table1 where breakout_no LIKE @prefix ) — Was it found? If so, then print it and skip the rest of the code IF…
EXEC sp_configure ‘xp_cmdshell’, 1
How to output a file from a stored procedures in MS SQL, this is how I did it a few years ago exec master..xp_cmdShell ‘bcp “SELECT url.loc, url.publication_date, url.keywords FROM ICONews..vw_googlenews url For XML Auto,Elements” queryout C:InetpubwwwrootwebsiteSQLgoogle.xml -c -t, -T -S IP address’ Link: http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell
oShell.run “cmd /K CD C:\ & ipconfig/flushdns” oShell.run “cmd /K CD C:\ & Netsh interface ip set address name=””Local Area Connection”” source=static addr=IP mask=255.255.252.0 gateway=IP gwmetric=1″
Set WshShell = WScript.CreateObject(“WScript.Shell”)
HAVING (COUNT(*) > 1)
(PATINDEX(‘%.%’, REPLACE(REPLACE(url, ‘www.’, ”), RIGHT(url, 7), ”)) > 1)
{ fn LCASE((CASE WHEN CHARINDEX(‘/’, url) = 0 THEN ‘http://www.’ + REPLACE(REPLACE(url, ‘www3.’, ”), ‘www.’, ”)
ELSE CASE WHEN (url) LIKE ‘www’ THEN ” ELSE ‘http://www.’ + REPLACE(REPLACE(REPLACE(LEFT(url, CHARINDEX(‘/’, url)), ‘www3.’, ”), ‘www.’, ”), ‘/’,
”) END END)) } AS websiteurl
strCommand = “taskkill /F /IM php.exe” WshShell.Run strCommand, 0, TRUE WScript.Sleep 100 ‘bit of an over kill but it makes sure its dead WshShell.Run strCommand, 0, TRUE WScript.Sleep 100 WshShell.Run strCommand, 0, TRUE WScript.Sleep 100 Software engineers do the job better!
CODE .vbs to close a browser window
Full document on, how to setup Notification services Opened SQL Server Management Studio and right-click the Notification Services object in the Object Explorer Click on New Notification Service Instance In configuration file click browse. Within that panel, location of the ICF, Select the NSLimeICF.xml file and press open Software engineers do the job better!
CODE to: connect to Ms SQL, search google, and scrape sponsored links.
DECLARE @enteredOn datetime; — Sun IF datepart(dw, getdate()) = 1 Set @enteredOn =getdate()-7 (CONVERT(datetime, CONVERT(varchar, SOEB.ASK_salesproccomp, 103), 103) >= CONVERT(datetime, CONVERT(varchar, @enteredOn, 103), 103)) Software engineers do the job better!
DECLARE @Datafull XML DECLARE @Layoutfull XML SET @Datafull = dbo.ufn_Common_Layout_DataCells_AsXML(@Layoutfull, 24125, 1, 0, 0) SET @Layoutfull = dbo.ufn_Common_Layout_ByDealerAndItemFromCache_AsXML_ex(5,5030, 658) select @Datafull AS [values] select @Layoutfull AS [layout] DECLARE @ret XML; EXEC @ret = [ufn_Common_Layout_InsertValues] @pLayout=@Layoutfull,@pValues=@Datafull select @ret AS [result]
CASE WHEN DATEDIFF(mm, NPB.CreatedOn, soeb.ASK_ContractEndDate) > 1 THEN DATEDIFF(mm,NPB.CreatedOn, soeb.ASK_ContractEndDate) ELSE 0 END Software engineers do the job better!
MS SQL To find the start of the month This code: DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) was excuted on 2/3/2010 at 16:11 the result: 01/03/2010 00:00:00 Software engineers do the job better!
To start you should have MS SQL 2005 and Notification Services installed and IIS with SMPT Open files and copy and rename sub NSPing-files to c:NSPing open NSLime.ssmssqlproj with MS SQL 2005 Server Management Studio and connect to your SQL server Select and right click on NS, select New Notification Services Instance.. Browse to c:NSPing…
If your website goes down and you have pay per click, you still pay for every click. If you know your website is down you can disable PPC until your website is back up. But who will watch your site 24/7. I have over 10,000 domains, so I wrote this program using Microsoft’s Notification Services,…