Clive Roberts

SQL DBA

Menu
  • Server Setup Scritps
  • mssql
  • Scripts
  • MS Notification Services

Category: mssql

Access a SQL server without a password.

November 9, 2017
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE: kill SQL processes

March 30, 2017
| No Comments
| mssql

–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…

Read More »

Posted in mssql

CODE:using TempDB currently

July 29, 2016
| No Comments
| mssql

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,…

Read More »

Posted in mssql

CODE: connected to SQL server

May 1, 2016
| No Comments
| mssql

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;

Read More »

Posted in mssql

CODE: VLF counts

April 19, 2016
| No Comments
| mssql

— 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,…

Read More »

Posted in mssql

CODE: move backups

April 11, 2016
| No Comments
| mssql

declare @cmd nvarchar(200) set @cmd = ‘move \\server\UserDB\*.bak \\server\UserDB\Archive’ exec xp_cmdshell @cmd

Read More »

Posted in mssql

CODE: VLF SQL 2012+

March 19, 2016
| No Comments
| mssql

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]…

Read More »

Posted in mssql

Error message when you open SQL Server Configuration Manager in SQL Server

February 19, 2016
| No Comments
| mssql

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  

Read More »

Posted in mssql

Fill Factor

November 11, 2015
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE: find missing indexes

October 26, 2015
| No Comments
| mssql

— 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, ”), ‘, ‘, ‘_’), ‘[‘, ”),…

Read More »

Posted in mssql

CODE: number of cores on a SQL server

September 18, 2015
| No Comments
| mssql

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,…

Read More »

Posted in mssql

CODE: DBCC CHECKDB

August 6, 2015
| No Comments
| mssql

Classic way to check the database is alright. DBCC CHECKDB(‘DatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS

Read More »

Posted in mssql

CODE: SQL error 1222

July 15, 2015
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE: Agent job details

July 1, 2015
| No Comments
| mssql

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,…

Read More »

Posted in mssql

CODE:Unused Index Script

July 1, 2015
| No Comments
| mssql

– 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’…

Read More »

Posted in mssql

CODE:waiting_tasks

July 1, 2015
| No Comments
| mssql

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

Read More »

Posted in mssql

CODE: counter

July 1, 2015
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE: execution count

July 1, 2015
| No Comments
| mssql

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

Read More »

Posted in mssql

CODE: wait times

July 1, 2015
| No Comments
| mssql

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…

Read More »

Posted in mssql

To back up the tail of the transaction log

June 11, 2015
| No Comments
| mssql

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….

Read More »

Posted in mssql

Types of Replication

May 19, 2015
| No Comments
| mssql

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.

Read More »

Posted in mssql

Index type

May 19, 2015
| No Comments
| mssql

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…

Read More »

Posted in mssql

Database Engine Stored Procedures (Transact-SQL)

May 8, 2015
| No Comments
| mssql

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 More »

Posted in mssql

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

May 7, 2015
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE:CONCAT Function

May 1, 2015
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE: STUFF Function

May 1, 2015
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE: Tables without clustered indexes defined

April 15, 2015
| No Comments
| mssql

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 =…

Read More »

Posted in mssql

Blocking, Timeouts and Deadlocks

April 7, 2015
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE: SET XACT_ABORT

March 30, 2015
| No Comments
| mssql

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…

Read More »

Posted in mssql

Locking hint

March 7, 2015
| No Comments
| mssql

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,…

Read More »

Posted in mssql

CODE: Errors 823, 824, and 825

February 5, 2015
| No Comments
| mssql

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 =…

Read More »

Posted in mssql

DBCC commands

January 13, 2015
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE: calculate number of transactions on SQL Server

December 4, 2014
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE: Call a function

November 29, 2014
| No Comments
| mssql

DECLARE @ret XML; EXEC @ret = [ufn_Common_Layout_function] @pLayout=@Layoutfull,@pValues=@Data SELECT @ret  AS [result]

Read More »

Posted in mssql

CODE: databse,details

October 10, 2014
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE: replication

September 10, 2014
| No Comments
| mssql

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];  

Read More »

Posted in mssql

CODE: find jobs on a server

August 10, 2014
| No Comments
| mssql

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

Read More »

Posted in mssql

CODE:Check Backup or Restore progress

July 10, 2014
| No Comments
| mssql

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’)

Read More »

Posted in mssql

MS SQL pivit table

June 9, 2014
| No Comments
| mssql

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

Read More »

Posted in mssql

Visual studio: How to remove projects from the recent projects list.

May 18, 2014
| No Comments
| mssql

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).

Read More »

Posted in mssql

MS SQL : MS replication_objects

April 19, 2014
| No Comments
| mssql

  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’

Read More »

Posted in mssql

CODE:List tables with no primary key – SQL Server 2000

March 10, 2014
| No Comments
| mssql

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’  

Read More »

Posted in mssql

CODE: Audit who Dropped the Database

February 15, 2014
| No Comments
| mssql

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’ )…

Read More »

Posted in mssql

CODE: last_value

January 15, 2014
| No Comments
| mssql

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

Read More »

Posted in mssql

CODE: Parse XML to table

December 15, 2013
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE: Search a string in a text with number of occurrences

November 15, 2013
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE: Ping update

August 9, 2013
| No Comments
| mssql

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…

Read More »

Posted in mssql

CODE: Calculating the Difference (Duration) Between Two DATETIMES

March 15, 2013
| No Comments
| mssql

— “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 ;

Read More »

Posted in mssql

CODE: get ip address

February 22, 2013
| No Comments
| mssql

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…

Read More »

Posted in mssql

REF.How to Fix SQL Server Database Error 3707?

January 19, 2013
| No Comments
| mssql

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

Read More »

Posted in mssql

Code:Ping from a stored procedure

August 18, 2012
| No Comments
| mssql

EXEC @result=Master..xp_cmdShell @strCmd

Read More »

Posted in mssql

CODE: MS SQL TRUNCATE LOGS

June 28, 2012
| No Comments
| mssql

backup log Leads with TRUNCATE_ONLY dbcc shrinkdatabase (Leads , TRUNCATEONLY)

Read More »

Posted in mssql

CODE:MS SQL SHRINKDATABASE

May 28, 2012
| No Comments
| mssql

DBCC SHRINKDATABASE (N’Leads’, 0,TRUNCATEONLY)

Read More »

Posted in mssql

CODE: MS SQL Express backup

April 27, 2012
| No Comments
| mssql

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

Read More »

Posted in mssql

CODE: MSSQL convert an IP into a IP number

March 3, 2012
| No Comments
| mssql

(16777216 * CONVERT(float, PARSENAME(URLip, 4))

Read More »

Posted in mssql

CODE: MSSQL devide up an IP

February 3, 2012
| No Comments
| mssql

PARSENAME(URLip, 4)

Read More »

Posted in mssql

CODE:nearest match

January 27, 2012
| No Comments
| mssql

— 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…

Read More »

Posted in mssql

CODE: Enable the Xp_CmdShell

October 21, 2011
| No Comments
| mssql

EXEC sp_configure ‘xp_cmdshell’, 1

Read More »

Posted in mssql

Code:Output a file from a stored procedures in MS SQL

September 19, 2011
| No Comments
| mssql

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

Read More »

Posted in mssql

CODE: find duplicate urls

June 26, 2011
| No Comments
| mssql

HAVING (COUNT(*) > 1)

Read More »

Posted in mssql

CODE: pull out sub domains

May 26, 2011
| No Comments
| mssql

(PATINDEX(‘%.%’, REPLACE(REPLACE(url, ‘www.’, ”), RIGHT(url, 7), ”)) > 1)

Read More »

Posted in mssql

CODE: clean urls

April 26, 2011
| No Comments
| mssql

{ 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

Read More »

Posted in mssql

CODE to: connect to Ms SQL, search google, and scrape sponsored links.

September 16, 2010
| No Comments
| mssql

CODE to: connect to Ms SQL, search google, and scrape sponsored links.

Read More »

Posted in mssql

MS SQL SP Variables

August 4, 2010
| No Comments
| mssql

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!

Read More »

Posted in mssql

CODE: XML

July 7, 2010
| No Comments
| mssql

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]

Read More »

Posted in mssql

MS SQL CASE statement

May 4, 2010
| No Comments
| mssql

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!

Read More »

Posted in mssql

Code: MS SQL To find the start of the month

March 2, 2010
| No Comments
| mssql

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!

Read More »

Posted in mssql

Categories

Sidebar

  • November 2017 (1)
  • March 2017 (1)
  • October 2016 (13)
  • July 2016 (1)
  • May 2016 (1)
  • April 2016 (2)
  • March 2016 (1)
  • February 2016 (1)
  • November 2015 (2)
  • October 2015 (1)
  • September 2015 (1)
  • August 2015 (1)
  • July 2015 (7)
  • June 2015 (1)
  • May 2015 (6)
  • April 2015 (2)
  • March 2015 (2)
  • February 2015 (1)
  • January 2015 (1)
  • December 2014 (1)
  • November 2014 (1)
  • October 2014 (1)
  • September 2014 (1)
  • August 2014 (1)
  • July 2014 (1)
  • June 2014 (1)
  • May 2014 (1)
  • April 2014 (1)
  • March 2014 (1)
  • February 2014 (1)
  • January 2014 (1)
  • December 2013 (1)
  • November 2013 (1)
  • August 2013 (1)
  • July 2013 (1)
  • June 2013 (1)
  • April 2013 (1)
  • March 2013 (1)
  • February 2013 (1)
  • January 2013 (1)
  • December 2012 (1)
  • August 2012 (1)
  • June 2012 (1)
  • May 2012 (1)
  • April 2012 (1)
  • March 2012 (1)
  • February 2012 (1)
  • January 2012 (1)
  • October 2011 (1)
  • September 2011 (1)
  • August 2011 (1)
  • July 2011 (1)
  • June 2011 (1)
  • May 2011 (1)
  • April 2011 (1)
  • March 2011 (1)
  • January 2011 (1)
  • December 2010 (1)
  • September 2010 (1)
  • August 2010 (1)
  • July 2010 (1)
  • May 2010 (1)
  • March 2010 (1)
  • February 2010 (1)
  • January 2010 (1)
  • Server Setup Scritps
  • mssql
  • Scripts
  • MS Notification Services