SQL: ASCII characters code for each character of a field (all records)

I wanted to look for all non-standard (i.e. A-Z/a-z/0-9) characters in a field across all records in a table. Therefore, I have adjusted my previous script to do so:

SET NOCOUNT ON;
CREATE TABLE #Characters
( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  RecordID INT NOT NULL,
  [Character] NCHAR(1),
  [ASCIIValue] INT
)
DECLARE @RecordID INT;
DECLARE @counter int = 1;
DECLARE @colString NVARCHAR(MAX);
DECLARE search CURSOR FAST_FORWARD FOR
	SELECT RecordID, TextString
	FROM tableName WHERE ISNULL(TextString, '') <> ''
OPEN search
FETCH NEXT FROM search INTO @RecordID, @colString
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @counter = 1
	WHILE @counter <= DATALENGTH(@colString)
	BEGIN
		INSERT INTO #Characters (RecordID, [Character], [ASCIIValue])
		SELECT @RecordID,
			CHAR(ASCII(SUBSTRING(@colString, @counter, 1))) as [Character],
			ASCII(SUBSTRING(@colString, @counter, 1)) as [ASCIIValue]
		SET @counter = @counter + 1
	END
	FETCH NEXT FROM search INTO @RecordID, @colString
END
CLOSE search
DEALLOCATE search
SELECT * FROM #Characters
WHERE (ASCIIValue < 48 OR (ASCIIValue > 57 -- 0-9
  AND ASCIIValue < 65) OR (ASCIIValue > 90 -- A-Z
  AND ASCIIValue < 97) OR ASCIIValue > 122) -- a-z
ORDER BY ASCIIValue
DROP TABLE #Characters
SET NOCOUNT OFF
GO

Simply change to the required database, and change the select statement in the cursor.

Please always test on a development server/database before running on Production.

SSMS can’t see network locations

I was having problems with SSMS (SQL Server Management Studio) 2016 which I have been running with administrator permissions, whereby I couldn’t open files on my mapped network drives.

Doing a bit of Google’ing, I came across this article – https://technet.microsoft.com/en-us/library/ee844140(v=WS.10).aspx

I applied the registry fix:

To configure the EnableLinkedConnections registry value

  1. Click Start, type regedit in the Start programs and files box, and then press ENTER.
  2. Locate and then right-click the registry subkey HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindowsCurrentVersionPoliciesSystem.
  3. Point to New, and then click DWORD Value.
  4. Type EnableLinkedConnections, and then press ENTER.
  5. Right-click EnableLinkedConnections, and then click Modify.
  6. In the Value data box, type 1, and then click OK.
  7. Exit Registry Editor, and then restart the computer.

And, it has fixed the problem 🙂

SQL: Error “Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.”

Earlier today I discovered a SQL Agent job that was failing with the error “Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.”

It turns out that the person who set up the job did not have sysadmin rights.

I changed the owner of the job to a sysadmin user, and now it works 🙂

Hope this works for anyone else who gets this error.

SQL: Useful shortcuts

I was searching the internet for useful shortcuts for SQL Server, and came across this Stack Overflow post – http://stackoverflow.com/questions/101079/sql-server-management-studio-tips-for-improving-the-tsql-coding-process

Below are some useful highlights that I will be using:

  • Rectangular Selection – To create a rectangular selection for copying or deleting, press ALT and then drag from corner to corner.
  • Comment Selection – To comment out a selection of lines, press CTRL + K and CTRL + C
  • Uncomment Selection – To remove comments from a selection of lines, press CTRL + K then CTRL + U
  • Query Designer – To display the Query Designer press CTRL + SHIFT + Q
  • List of columns in Query Window – From Object Explorer, expand a table, and drag the “Columns” node Object Explorer into a query windows.  It will create a CSV list of columns.
  • Display Estimated Execution Plan – Ctrl+L
  • Show/Hide Results Pane – Ctrl+R
  • Query Shortcuts
    • Alt+F1 – executes sp_help.  Simple highlight a table or another object in the Query Window, and press Alt+F1 to show the sp_help information
    • Ctrl+1 – executes sp_who
    • Ctrl+2 – executes sp_lock
    • To change these shortcuts, or other Ctrl+number keys, go to Tools, Options, Environment, Keyboard, and Query Shortcuts

Enjoy! 🙂

SQL: Return the MAX of all dates within all tables within all databases

I wanted to produce a list of the MAX of date fields (DateTime, SmallDateTime, TimeStamp) for all tables within all databases held in an instance of SQL Server.

The script below was written for SQL Server 2000, but should be easily customisable for SQL Server 2005

Please ensure you test any script taken from my website on a test/development machine, before running on a production server

USE master
GO
 
DECLARE @DatabaseID INT
DECLARE @DatabaseName VARCHAR(500)
DECLARE @SQL NVARCHAR(1000)
DECLARE @TableName VARCHAR(100)
DECLARE @FieldName VARCHAR(100)
DECLARE @MaxDate DATETIME
 
CREATE TABLE #MaxDates
(  DatabaseID   INT,
  DatabaseName  VARCHAR(100),
  TableName VARCHAR(100),
  FieldName VARCHAR(100),
  MaxDate   DATETIME
)
  
DECLARE DatabaseCursor CURSOR FAST_FORWARD 
FOR
  SELECT dbid, name
  FROM Master.dbo.sysdatabases
  WHERE name NOT IN
    ('master', 'msdb', 'tempdb', 'model')
  AND status & 512 = 0
  ORDER BY name
 
OPEN DatabaseCursor
 
FETCH NEXT FROM DatabaseCursor
  INTO @DatabaseID, @DatabaseName
 
WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE @Tables CURSOR
  SET @SQL = N'SET @Tables = CURSOR STATIC '
  SET @SQL+='FOR SELECT Name '
  SET @SQL+='FROM [' + @DatabaseName 
  SET @SQL+='].dbo.sysobjects '
  SET @SQL+='WHERE type = ''U'' '
  SET @SQL+='ORDER BY Name;'
  SET @SQL+='OPEN @Tables'
  EXEC sp_executesql
    @SQL, N'@Tables cursor OUTPUT'
        , @Tables OUTPUT
 
  SET NOCOUNT ON
 
  FETCH NEXT FROM @Tables INTO @TableName
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    DECLARE @Fields CURSOR
    -- Select fields that are SMALLDATETIME, 
    --   DATETIME or TIMESTAMP
    SET @SQL = N'SET @Fields = CURSOR STATIC '
    SET @SQL+='FOR SELECT C.name '
    SET @SQL+='FROM [' + @DatabaseName + ']'
    SET @SQL+='.dbo.syscolumns C INNER JOIN '
    SET @SQL+='[' + @DatabaseName + ']'
    SET @SQL+='.dbo.sysobjects T ON C.id=T.id '
    SET @SQL+='WHERE T.name = ' + CHAR(39)
    SET @SQL+=@TableName + CHAR(39) 
    SET @SQL+='AND C.xusertype '
    SET @SQL+='IN (58, 61, 189) '
    SET @SQL+='ORDER BY C.colid; '
    SET @SQL+='OPEN @Fields'
    EXEC sp_executesql
    @SQL, N'@Fields cursor OUTPUT',
                @Fields OUTPUT
 
    FETCH NEXT FROM @Fields 
      INTO @FieldName
         
    WHILE @@FETCH_STATUS = 0
    BEGIN
      SET @SQL='INSERT INTO #MaxDates '
      SET @SQL+='(DatabaseID, DatabaseName, '
      SET @SQL+='TableName, FieldName, '
      SET @SQL+='MaxDate) SELECT '
      SET @SQL+=LTRIM(RTRIM(STR(@DatabaseID))) 
      SET @SQL+=' AS dbID, '
      SET @SQL+=CHAR(39) + @DatabaseName
      SET @SQL+=CHAR(39) + ' AS db, '
      SET @SQL+=CHAR(39)+@TableName 
      SET @SQL+=CHAR(39)+' AS tbl, '
      SET @SQL+=CHAR(39)+@FieldName
      SET @SQL+=CHAR(39)+' AS field, '
      SET @SQL+='MAX([' + @FieldName + ']) '
      SET @SQL+='AS MaxDate '
      SET @SQL+='FROM [' + @DatabaseName + ']'
      SET @SQL+='.dbo.[' + @TableName + ']'
      EXEC (@SQL)
      FETCH NEXT FROM @Fields INTO @FieldName
    END
    CLOSE @Fields
    DEALLOCATE @Fields
    FETCH NEXT FROM @Tables INTO @TableName
  END
  CLOSE @Tables
  DEALLOCATE @Tables
  FETCH NEXT FROM DatabaseCursor
  INTO @DatabaseID, @DatabaseName
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
 
DROP TABLE #MaxDates

SQL: Maintenance Plan Error “Server: Msg 22029, Level 16, State 1, Line 0 sqlmaint.exe failed.”

Earlier today, I discovered that the database maintenance plan on one of our SQL Server machine wasn’t working. When I reviewed the logs, it simply said “sqlmaint.exe failed”.

I ran the following command (copied from the SQL Server Agent job) using Query Analyzer (as this was a SQL Server 2000 machine):

EXECUTE master.dbo.xp_sqlmaint N'-PlanID C009A320-69FF-48D6-AC76-74292CD765AF -Rpt "E:BackupDatabasesBackups4.txt" -WriteHistory  -VrfyBackup -BkUpOnlyIfClean -CkDB  -BkUpMedia DISK -BkUpDB "E:BackupDatabases" -DelBkUps 2WEEKS -CrBkSubDir -BkExt "BAK"'

The error returned, was:

(5 row(s) affected)

Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.

I tried running exactly the same command through the command-line:

CD \Program Files\Microsoft SQL Server\MSSQL\Binn

sqlmaint -PlanID C009A320-69FF-48D6-AC76-74292CD765AF -Rpt "E:\Backup\Databases\Backups4.txt" -WriteHistory  -VrfyBackup -BkUpOnlyIfClean -CkDB  -BkUpMedia DISK -BkUpDB "E:\Backup\Databases" -DelBkUps 2WEEKS -CrBkSubDir -BkExt "BAK"




This worked!!!

Both the SQL Server service, and the SQL Server Agent are using the Local Service account.

I have asked our server hosting provider to restart the server, as I have tried restarting the services, but still the maintenance plan doesn’t work through SQL Server Agent.

I shall update this post once the server has been restarted.

SQL: Red Gate’s SQL Search

Hi.

I have just downloaded Red Gate’s FREE SQL Search tool from http://www.red-gate.com/products/SQL_Search/index.htm

Features:

  • Find fragments of SQL text within stored procedures, functions, views and more
  • Quickly navigate to objects wherever they happen to be on your servers
  • Find all references to an object
  • Integrates with SSMS

Why use SQL Search?

  • Impact Analysis
  • You want to rename one of your table columns but aren’t sure what stored procedures reference it. Using SQL Search, you can search for the column name and find all the stored procedures where it is used.

  • Work faster
  • Finding anything in the SSMS object tree requires a lot of clicking. Using SQL Search, you can press the shortcut combo, start typing the name, and jump right there.

  • Make your life easier
  • You need to find stored procedures you’ve not yet finished writing. Using SQL Search, you can search for stored procedures containing the text ‘TODO’.

  • Increase efficiency, reduce errors
  • You are a DBA, and developers keep using ‘SELECT *’ in their views and stored procedures. You want to find all these and replace them with a correct list of columns to improve performance and prevent future bugs. Using SQL Search, you can look for ‘SELECT *’ in the text of stored procedures and views.

Below are the technical requirements for SQL Search.

  • Supported OS:
    • Windows XP
    • Windows Vista
    • Windows 7
    • Windows Server 2003
    • Windows Server 2008
  • Supported editors:
    • SSMS 2005
    • SSMS 2008
    • SSMS 2008 Express. Note: To use SQL Search with SSMS 2005 Express Edition, extensibility.dll must be installed.
  • Supported database versions:
    • SQL 2005
    • SQL 2008