Search Every Column in Every Table

-- Update to exclude image and other blob types

SELECT 'SELECT [' + COLUMN_NAME + '] ''[' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'' FROM [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] WHERE CAST ([' + COLUMN_NAME + '] AS VARCHAR (MAX)) like ''%86110%''' FROM INFORMATION_SCHEMA.Columns c

TSQL Script to identify unused indexes

http://beyondrelational.com/modules/30/scripts/485/scripts/11430/tsql-script-to-identify-unused-indexes.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2012Mar

WITH cte AS (

02.SELECT

03.'['+ c.name + '].[' + o.name + ']' AS TableName,

04.i.name AS IndexName,

05.i.index_id AS IndexID,

06.user_seeks + user_scans + user_lookups AS Reads,

07.user_updates AS Writes,

08.(

09.SELECT SUM(p.rows)

10.FROM sys.partitions p

11.WHERE p.index_id = s.index_id

12.AND s.object_id = p.object_id

13.) AS TotalRows,

14.CASE

15.WHEN s.user_updates < 1 THEN 100

16.ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups)

17./ s.user_updates

18.END AS ReadsPerWrite,

19.'DROP INDEX ' + QUOTENAME(i.name)

20.+ ' ON ' + QUOTENAME(c.name)

21.+ '.' + QUOTENAME(OBJECT_NAME(s.object_id))

22.AS 'DropSQL'

23.FROM sys.dm_db_index_usage_stats s

24.INNER JOIN sys.indexes i ON i.index_id = s.index_id

25.AND s.object_id = i.object_id

26.INNER JOIN sys.objects o on s.object_id = o.object_id

27.INNER JOIN sys.schemas c on o.schema_id = c.schema_id

28.WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1

29.AND s.database_id = DB_ID()

30.AND i.type_desc = 'nonclustered'

31.AND i.is_primary_key = 0

32.AND i.is_unique_constraint = 0

33.AND

34.(

35.SELECT SUM(p.rows)

36.FROM sys.partitions p

37.WHERE p.index_id = s.index_id

38.AND s.object_id = p.object_id

39.) > 10000

40.)

41.SELECT * FROM cte

42./*

43.TableName Name ID Reads Writes Rows ReadsPerWrite DropSQL

44.---------- ----- -- ------- ------ ----- ------------- -------

45.[dbo].[t1] IDX_1 20 1227972 996081 78599 1.23280335635 DROP ..

46.[dbo].[t2] IDX_2 21 51359 33912 78599 1.51447865062 DROP ..

47.[dbo].[t3] IDX_3 22 235280 159808 78599 1.47226672006 DROP ..

48.*/

Find Non-numeric Rows in Column

CREATE TABLE #foo (

ident BIGINT IDENTITY (0, 1)

, someString VARCHAR (128) DEFAULT '')

INSERT #foo ( someString )

SELECT '4546546'

UNION

SELECT '456654.475'

UNION

SELECT 'afafsdf'

UNION

SELECT '78998'

UNION

SELECT '87979z'

SELECT ident

, someString

, PATINDEX ('%[^0-9]%', someString)

FROM #foo

WHERE PATINDEX ('%[^0-9]%', someString) != 0

Dump Database

Find/export all data in database

SELECT 'SELECT ''' + c.TABLE_CATALOG + '.' + c.TABLE_SCHEMA + '.' + c.TABLE_NAME + '.' + c.COLUMN_NAME + ''', * FROM ['
+ c.TABLE_CATALOG + '].[' + c.TABLE_SCHEMA + '].[' + c.TABLE_NAME
+ '] WHERE [' + c.COLUMN_NAME + '] LIKE ''%dss20%'' OR [' + c.COLUMN_NAME + '] LIKE ''%MCEPSIWDB001%'' OR [' + c.COLUMN_NAME + '] LIKE ''%MCEPSIWWS001%'''
FROM INFORMATION_SCHEMA.columns c
JOIN INFORMATION_SCHEMA.tables t
ON t.TABLE_CATALOG = c.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.DATA_TYPE LIKE '%char%'
AND t.TABLE_TYPE = 'BASE TABLE'

Table / Column Search

SELECT

c.TABLE_NAME

, c.COLUMN_NAME

FROM INFORMATION_SCHEMA.TABLES t

JOIN INFORMATION_SCHEMA.COLUMNS c

ON t.TABLE_CATALOG = c.TABLE_CATALOG

AND t.TABLE_SCHEMA = c.TABLE_SCHEMA

AND t.TABLE_NAME = c.TABLE_NAME

AND t.TABLE_TYPE = 'BASE TABLE'

WHERE c.COLUMN_NAME LIKE '%user%'

Really - I'm Not a DBA :: Custom DB Permissions: user - schema - db - login Part 2

Example test scripts for "Really - I'm Not a DBA :: Custom DB Permissions: user - schema - db - login Part 1"


USE TargetDB
GO

-- Server: SERVER
-- DB: TargetDB


SET NOCOUNT ON

-- should work
SELECT 'WORK: CREATE TABLE bud.jkTest_UserA'
CREATE TABLE bud.jkTest_UserA (aVC VARCHAR (128), bINT int)
GO

-- should FAIL
SELECT 'FAIL: CREATE TABLE pa.jkTest_UserA'
CREATE TABLE pa.jkTest_UserA (aVC VARCHAR (128), bINT int)
GO

-- should FAIL
SELECT 'FAIL: CREATE TABLE UCSF_SYSTEM.dbo.jkTest_UserA'
CREATE TABLE UCSF_SYSTEM.dbo.jkTest_UserA (aVC VARCHAR (128), bINT int)
GO

-- should work
SELECT 'WORK: INSERT bud.jkTest_UserA'
INSERT bud.jkTest_UserA (aVC, bINT)
SELECT SUSER_NAME(), 1
GO

-- should work
SELECT 'WORK: INSERT bud.jkTest'
INSERT bud.jkTest (aVC, bINT)
SELECT SUSER_NAME(), 1
GO

-- should FAIL
SELECT 'FAIL: INSERT pa.jkTest'
INSERT pa.jkTest (aVC, bINT)
SELECT SUSER_NAME(), 1
GO

-- should FAIL
SELECT 'FAIL: INSERT UCSF_SYSTEM.dbo.jkTest '
INSERT UCSF_SYSTEM.dbo.jkTest (aVC, bINT)
SELECT SUSER_NAME(), 1
GO

-- should work
SELECT 'WORK: SELECT * FROM bud.jkTest '
SELECT * FROM bud.jkTest
GO

-- should work
SELECT 'WORK: SELECT * FROM pa.jkTest '
SELECT * FROM pa.jkTest
GO

-- should work
SELECT 'WORK: SELECT TOP 33 * FROM TARGETDB.dbo.T_Name '
SELECT TOP 33 * FROM TARGETDB.dbo.T_Name
GO

Last Time Tables Used - Referenced

http://beyondrelational.com/modules/1/justlearned/388/tips/9111/sql-server-finding-last-accessed-time-for-tables-by-queries-or-find-unused-tables.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2012Feb


Requires 8.0 compatibility.

--select su.[admin] from sys_users su where su.username like 'kellyjoe%'

-- update su set su.[admin] = 0 from sys_users su where su.username like 'kellyjoe%'
WITH LastActivity (ObjectID, LastAction)
AS
(
SELECT object_id AS TableName, Last_User_Seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id)AS TableName,
MAX(la.LastAction)as LastSelect
FROM
sys.objects so
LEFT JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100 --returns only the user tables.Tables with objectid<100 are systables.
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)

Proc Name from Within Proc

http://www.sqldbpros.com/2011/01/sql-server-get-proc-name-from-within-proc/

SQL Server – Get Proc Name From Within Proc

Want to get the name of a SQL Server stored procedure from with the procedure itself? No problem! This quick T-SQL script is all you need.

SELECT OBJECT_NAME(@@PROCID)

Need the schema as well?

SELECT OBJECT_SCHEMA_NAME(@@PROCID), OBJECT_NAME(@@PROCID)

I've found this particularly helpful when logging stored procedure execution to an audit table.

p.s. I think this goes without saying but if you need only the object id the following complicated query can be used:

SELECT @@PROCID

How to debug ActiveX dll’s (VB6)

How to debug ActiveX dll’s (VB6)

1.) Get the method call, i.e. getDetails("1206", "1601001", "guya", "2", vTable, WriteAccess, RowCount)
2.) Open VB project, change project properties from “ActiveX DLL” to “Standard EXE” and start-up object to “Sub Main”
3.) Add in a Sub Main constructor, doing so in a *.bas (not *.class) module is fine

Public Sub Main()

' Note that switching from an ActiveX DLL to a Standard EXE may/will change the ' class module “Instancing” property from “Multiuse” to “Private” (error: “No
' creatable public component detected". To get back to the ActiveX DLL change the ' class module “Instancing” property back to “Multiuse”

Dim sError, vTable, WriteAccess, RowCount
‘ name of class that contains the function to invoke

Dim foo As New cReports

‘ invoke function
sError = foo.getDetails("1206", "1601001", "guya", "2", vTable, WriteAccess, RowCount)

‘F8 – away!

‘modal for a breakpoint
MsgBox Command$, vbOK, "Command Line params"

End Sub

SQL Regex

http://beyondrelational.com/justlearned/posts/763/find-column-names-which-has-special-characters.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2012Jan

USE[]
02.GO
03.
04.SELECT tab.TABLE_NAME,col.COLUMN_NAME
05.FROM INFORMATION_SCHEMA.COLUMNS col
06.INNER JOIN INFORMATION_SCHEMA.TABLES tab
07.ON tab.Table_name = col.TABLE_NAME
08.WHERE tab.TABLE_TYPE = 'BASE TABLE'
09.AND
10.col.COLUMN_NAME LIKE '%[^a-zA-Z0-9]%'

SQL Server Table and Column with Extended Properties

USE FooBar
GO

CREATE TABLE dbo.tFoo (
ident INT IDENTITY (1, 1)
, PayCode CHAR (4) NOT NULL PRIMARY KEY
, GroupCode CHAR (16) DEFAULT ''
, GroupCodeDescription VARCHAR (256) DEFAULT ''
, crDate DATETIME DEFAULT GETDATE ()
, uDate DATETIME DEFAULT GETDATE ()
)

EXEC sys.sp_addextendedproperty
@name = N'tFoo - Purpose',
@value = N'Provide a logical extension to tBar without breaking any unknown unqualified inserts and provide a way to alert of any new paycodes that require a group association.',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'tFoo';
GO

EXEC sys.sp_addextendedproperty
@name = N'tFoo - PayCode',
@value = N'PayCode is the primary key and the foreign key to fBar.',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'tFoo',
@level2type = N'COLUMN',
@level2name = N'PayCode';
GO

WaitFor Delay

ALTER PROC Portal.WaitForStub (@seconds BIGINT)
AS
BEGIN

/****************************************************************************
Joe Kelly
2011-12-09 14:03:18.870

Stub method to simulate a long-running process up to a day.

Input is in seconds.

EXEC Portal.WaitForStub 3

****************************************************************************/

SET NOCOUNT ON

DECLARE @time DATETIME
SELECT @time = CAST((@seconds / 3600) AS VARCHAR(2)) + ':' + CAST((@seconds / 60) AS VARCHAR(2)) + ':' + CAST((@seconds % 60) AS VARCHAR(2))

WAITFOR DELAY @time


END

Get Columns in Table

http://beyondrelational.com/justlearned/posts/792/sql-server-to-get-comma-separated-value-for-every-column-name-in-tables-with-t-sql.aspx

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+', ' ,'') + Name
FROM sys.columns
WHERE OBJECT_NAME(OBJECT_ID)='dat_groups'
SELECT @listStr

T-SQL Count Spaces in String

http://stanbiron.com/2010/07/29/SimpleWayToCountCharactersAndWordsUsingTSQL.aspx

-- Count the number of words

DECLARE @String VARCHAR(100)

SELECT @String = 'SQL Server 2005 Stan test code'

SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1

Longest Running Queries

http://www.sqlservercentral.com/Forums/Topic619606-360-1.aspx#bm1151148

SELECT TOP 100
qs.total_elapsed_time / qs.execution_count / 1000000.0 average_seconds
, qs.total_elapsed_time / 1000000.0 total_seconds
, qs.execution_count
, SUBSTRING (qt.text,qs.statement_start_offset/2
, ( CASE
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset
)/2
) individual_query
, o.name object_name
, DB_NAME(qt.dbid) database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
LEFT OUTER JOIN sys.objects o
ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC

SSMS REGEX - Remove Carriage Returns

http://www.simple-talk.com/sql/sql-training/regex-based-finding-and-replacing-of-text-in-ssms/

\n @\n
\n

SQL Regex

http://beyondrelational.com/blogs/naomi/archive/2011/10/19/remove-bad-characters-from-a-string.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2011Nov02

01.CREATE FUNCTION dbo.RemoveBadChars
02. (@String VARCHAR(8000),
03. @AllowedPattern VARCHAR(100))
04.RETURNS VARCHAR(8000)
05.AS
06. BEGIN
07. DECLARE @nBadChars INT,
08. @Rest VARCHAR(100)
09.
10. SET @String = '!@#$% 123 ??abc##rd,,,'
11.
12. SET @nBadChars = Patindex('%[^' + @AllowedPattern + ']%',@String)
13.
14. WHILE @nBadChars > 0
15. BEGIN
16. SET @Rest = Substring(@String,@nBadChars + 1,Len(@String))
17.
18. SET @String = Substring(@String,1,@nBadChars - 1)
19.
20. SET @Rest = Substring(@Rest,Patindex('%[' + @AllowedPattern + ']%',@Rest),
21. Len(@Rest))
22.
23. SET @String = @String + @Rest
24.
25. SET @nBadChars = Patindex('%[^' + @AllowedPattern + ']%',@String)
26. END
27.
28. RETURN @String
29. END
30.
31.GO



1.SELECT dbo.RemoveBadChars('1234@@@@@@@@@????????djwkejwk^&*-+=a',' 0-9a-z')

test

test

SQL Server Good Guidance on Missing Indices

http://beyondrelational.com/blogs/martinschmidt/archive/2011/09/27/missing-indexes.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2011Oct18 One of the most common things I encounter when asked to help with performance problems, is wrong or inadequate indexing. Creating the optimal indexes for a system, is no where near a trivial excercise. You need to consider the read/write ratio as well as how you write your queries. This is not the scope of this blog post. SQL Server has a number of dynamic management views/functions, which is very useful for different purposes. At least if you know they are there, and how to use them. For missing index information, these views are important: view source print? 1.SELECT * FROM sys.dm_db_missing_index_group_stats 2.SELECT * FROM sys.dm_db_missing_index_details 3.SELECT * FROM sys.dm_db_missing_index_groups sys.dm_db_missing_index_group_stats tracks information about indexes which could have been useful to the SQL Server. It holds information about how many times the SQL Server could have used that index, and how great an impact it would have had on the query. This DMV does not give you the details about the table and columns that the index should have been created on. This is however available in the sys.dm_db_missing_index_details DMV, where you get information about the database_id, object_id and the columns in the index. The last DMV is only used to bind the two DMV’s together. The DMV’s contains data on server level, so it will show details about missing index in all your databases. Let me show how a basic query on these DMV’s could look like: view source print? 1.SELECT * 2.FROM 3. sys.dm_db_missing_index_groups g 4. INNER JOIN sys.dm_db_missing_index_group_stats gs ON gs.group_handle = g.index_group_handle 5. INNER JOIN sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle On my local SQL Server, this gives me an empty resultset: So, let me create some test data: view source print? 01.CREATE DATABASE IndexDemo 02.GO 03.USE IndexDemo 04.GO 05.CREATE TABLE DemoData ( 06. Id INT IDENTITY PRIMARY KEY, 07. Val1 INT, 08. Val2 INT, 09. Val3 CHAR(4000) 10.) 11.GO 12.INSERT INTO DemoData (Val1, Val2, Val3) 13.VALUES (1,1, '') 14.GO 15. 16.INSERT INTO DemoData (Val1, Val2, Val3) 17.SELECT Val1, Val2, Val3 18.FROM DemoData 19.GO 15 I have created a table with a clustered index on the Id column, and filled the table with 32K rows. Now I will create a query to return the Id and Val1 column, given a specific value for Val2: view source print? 1.SELECT Id, Val1 2.FROM DemoData 3.WHERE Val2 = 123 So, now let me try to run the missing index query again, but this time only looking at a limited number of columns: view source print? 01.SELECT 02. user_seeks, 03. last_user_seek, 04. avg_total_user_cost, 05. avg_user_impact, 06. database_id, 07. object_id, 08. equality_columns, 09. inequality_columns, 10. included_columns 11.FROM 12. sys.dm_db_missing_index_groups g 13. INNER JOIN sys.dm_db_missing_index_group_stats gs ON gs.group_handle = g.index_group_handle 14. INNER JOIN sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle Now one row is returned. Let’s go through the returned columns, and see what the values mean: user_seeks This tells you how many times the SQL Server could have used this missing index to lookup data. The higher this number is, the more reason to build the index. How high this is, depends on your system. On high volume OLTP setups, numbers of less the 10.000 is not worth worrying about, and on smaller systems a value of 20-100 might be high enough to consider the index. last_user_seek This tells you when the missing index was last needed. If this value is equal to last saturday at night, it _could_ mean that you have a nightly job running on saturday, which might need this index. avg_total_user_cost This value does not have a unit, but it’s a value that tells you something about the estimated cost of the queries that needed the index. The higher the value is, the more resource intensive is the query that needs the index. If the value is very low (perhaps 0.01) then the query that needed the index was not very resource intensive. Think of a query that executes in 2ms compared to a query that takes 2000ms. You will probably gain more by adding an index to satisfy the 2000ms query than the 2ms one. avg_user_impact This is the expected improvement of the query if you build the query. The closer the value is to 100, the more the query will benefit from building the index. database_id This is the database where you need to build the index. object_id This is the object_id of the table that could need an index. equality_columns These columns are the ones that you should build the index on. Equality columns means that the predicate used the column like this: “Val2 = x”, which was exactly how I wrote my demo query. inequality_columns You should also add these columns to your index. Inequality columns are used for range predicates, like: “Val2 > x”. My missing index query returned NULL in this column, because my demo query had no such predicate. included_columns If columns are used in the SELECT part of your query, and not in the predicate, you can add these columns as included columns in the index. This way all needed data is available in the index, and no key lookup is needed on the clustered index. But beware! The more columns you add to your index, the more write IO and space you need. My rule of thumb says, that the total number of columns (equality_columns + inequality_columns + included_columns) should not exceed 5. If the total number of columns exceeds 5, that does not mean that you should not build the index – it just means that you need to think carefully about what you are doing! So, normally I run this query, and returns the rows ordered by user_seeks desc. Then I look at the top 10-20 rows, and see if any of them have avg_user_impact in the 90-100 range. Then I look for the number of columns in the index, and then I decide whether or not the index should be build. This is NOT a query that gives you the answer to everything, but it can definately help you spot the indexes that you should add to your system. Finally you can actually modify the query to give you the full CREATE INDEX statement, so you just need to copy that to a query window and execute it. The full query I usually use, looks like this: view source print? 01.DECLARE @DBName VARCHAR(100) 02.DECLARE @TableName VARCHAR(100) 03.--If this line is commented in, the missing index list will only contain 04.--missing indexes for the given database 05.--SET @DBName = 'MyDatabase' 06.--SET @TableName = 'MyTable' 07. 08.;WITH CTE 09.AS 10.( 11.SELECT 12. DB_NAME(d.database_id) AS DatabaseName, 13. user_seeks, 14. user_scans, 15. avg_total_user_cost, 16. avg_user_impact, 17. d.equality_columns, 18. d.inequality_columns, 19. d.included_columns, 20. 'USE ' + DB_NAME(d.database_id) + '; CREATE NONCLUSTERED INDEX IX_' + 21. replace(replace(replace(replace(isnull(equality_columns, '') + 22. isnull(inequality_columns, ''), ',', '_'), '[', ''),']', ''), ' ', '') + 23. CASE WHEN included_columns IS NOT NULL 24. THEN '_INC_' + replace(replace(replace(replace(included_columns, ',', '_'), '[', ''),']', ''), ' ', '') 25. ELSE '' END + ' ON ' + statement + ' (' + 26. CASE 27. WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL 28. THEN equality_columns + ', ' + inequality_columns 29. WHEN equality_columns IS NOT NULL AND inequality_columns IS NULL 30. THEN equality_columns 31. WHEN equality_columns IS NULL AND inequality_columns IS NOT NULL 32. THEN inequality_columns 33. END + ')' + 34. CASE WHEN included_columns IS NOT NULL THEN ' INCLUDE (' + 35. replace(replace(replace(included_columns, '[', ''),']', ''), ' ', '') + ')' 36. ELSE '' END + 37. CASE WHEN @@Version LIKE '%Enterprise%' THEN ' WITH (ONLINE = ON)' 38. ELSE '' END AS CreateIndex 39.FROM 40. sys.dm_db_missing_index_groups g 41. INNER JOIN sys.dm_db_missing_index_group_stats gs on gs.group_handle = g.index_group_handle 42. INNER JOIN sys.dm_db_missing_index_details d on g.index_handle = d.index_handle 43.WHERE 44. (DB_NAME(d.database_id) = @DBName 45. OR @DBName IS NULL) 46.) 47.SELECT * FROM CTE 48.WHERE CreateIndex LIKE '%'+@TableName+'%' OR @TableName IS NULL 49.ORDER BY user_seeks DESC Feel free to modify to match your naming standards.

SQL Server - Is Agent Running?

xp_servicecontrol 'querystate', 'SQLSERVERAGENT'

$ajax Web Service Not Working

Really big gotcha on a site I recently inherited. Scenario: Ajax call worked fine in dev, did not work upon deployment. RDC to test server and run same page from there, all works fine. So, works when invoked locally but not by remote client. Same code ... same(?) security context... Symptom: (overly helpful) Server response is “Request format is unrecognized for URL unexpectedly ending in … ” Solution: http://aspadvice.com/blogs/ssmith/archive/2007/09/04/FIX-Request-format-is-unrecognized-for-URL-unexpectedly-ending-in.aspx Add this node under < webServices> < protocols> < add name="HttpGet"/> < add name="HttpPost"/> < /protocols> < /webServices> Reminder: Trap and log errors in your $ajax calls

gacutil

C:\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Bin C:\Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\Bin C:\WINNT\Microsoft.NET\Framework\v1.0.3705

SQL Server Unused Indexes

http://beyondrelational.com/justlearned/posts/727/get-list-of-ununsed-index-and-remove-it.aspx USE PerformancePortal GO DECLARE @dbid INT SELECT @dbid = DB_ID(DB_NAME()) SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID) , INDEXNAME = I.NAME , I.INDEX_ID FROM SYS.INDEXES I JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1 AND I.INDEX_ID NOT IN ( SELECT S.INDEX_ID FROM SYS.DM_DB_INDEX_USAGE_STATS S WHERE S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID AND DATABASE_ID = @dbid ) ORDER BY OBJECTNAME , I.INDEX_ID , INDEXNAME ASC GO

T-SQL Try - Catch Error Messaging

ALTER FUNCTION portal.fnGetErrorTryCatch () RETURNS VARCHAR (MAX) AS BEGIN /************************************************************************* Joe Kelly 2011-10-12 17:18:44.707 Standard error response for when using try/catch blocks that stays in scope Syntax: BEGIN TRY SELECT 2/0 END TRY BEGIN CATCH SELECT portal.fnGetErrorTryCatch () END CATCH *************************************************************************/ DECLARE @error VARCHAR (MAX) SELECT @error = 'ERROR_LINE: ' + CAST (ISNULL (ERROR_LINE(), 0) AS VARCHAR (MAX)) + ' | ERROR_MESSAGE: ' + ISNULL (ERROR_MESSAGE(), 0) + ' | ERROR_NUMBER: ' + CAST (ISNULL (ERROR_NUMBER(), 0) AS VARCHAR (MAX)) + ' | ERROR_PROCEDURE: ' + ISNULL (ERROR_PROCEDURE(), 0) + ' | ERROR_SEVERITY: ' + CAST (ISNULL (ERROR_SEVERITY(), 0) AS VARCHAR (MAX)) + ' | ERROR_STATE: ' + CAST (ISNULL (ERROR_STATE(), 0) AS VARCHAR (MAX)) ; RETURN @error END

SSMS Regex Replace

http://www.simple-talk.com/sql/sql-training/regex-based-finding-and-replacing-of-text-in-ssms/

/n @/n
/n

SQL SERVER Table and Index Sizes

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

SQL Server UPDATE STATISTICS

-- Whole DB
EXEC sp_updatestats

-- Single table
UPDATE STATISTICS table_or_indexed_view_name

SQL Server Version

Select @@version

t-sql SQL Server Table Sizes

http://www.novicksoftware.com/Articles/sql-server-table-space-reporting-sp_spaceused.htm

-- EXEC [dbo].[dba_SpaceUsed] 'Budget', 'S'


CREATE PROC [dbo].[dba_SpaceUsed]
@SourceDB varchar ( 128 ) = null -- Optional database name
-- If omitted, the current database is reported.
, @SortBy char(1) = 'S' -- N for name, S for Size
-- T for table name
/* Returns a table with the space used in all tables of the
* database. It's reported with the schema information unlike
* the system procedure sp_spaceuse.
*
* sp_spaceused is used to perform the calculations to ensure
* that the numbers match what SQL Server would report.
*
* Compatible with sQL Server 2000 and 2005
*
* Example:
exec dbo.dba_SpaceUsed null, 'T'
*
* © Copyright 2007 Andrew Novick http://www.NovickSoftware.com
* This software is provided as is without warrentee of any kind.
* You may use this procedure in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* procedure either in print or electronically.
******************************************************************/
AS
SET NOCOUNT ON
DECLARE @sql nvarchar (4000)
IF @SourceDB IS NULL BEGIN
SET @SourceDB = DB_NAME () -- The current DB
END
--------------------------------------------------------
-- Create and fill a list of the tables in the database.
CREATE TABLE #Tables ( [schema] sysname
, TabName sysname )
SELECT @sql = 'insert #tables ([schema], [TabName])
select TABLE_SCHEMA, TABLE_NAME
from ['+ @SourceDB +'].INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ''BASE TABLE'''
EXEC (@sql)
---------------------------------------------------------------
-- #TabSpaceTxt Holds the results of sp_spaceused.
-- It Doesn't have Schema Info!
CREATE TABLE #TabSpaceTxt (
TabName sysname
, [Rows] varchar (11)
, Reserved varchar (18)
, Data varchar (18)
, Index_Size varchar ( 18 )
, Unused varchar ( 18 )
)
---------------------------------------------------------------
-- The result table, with numeric results and Schema name.
CREATE TABLE #TabSpace ( [Schema] sysname
, TabName sysname
, [Rows] bigint
, ReservedMB numeric(18,3)
, DataMB numeric(18,3)
, Index_SizeMB numeric(18,3)
, UnusedMB numeric(18,3)
)
DECLARE @Tab sysname -- table name
, @Sch sysname -- owner,schema
DECLARE TableCursor CURSOR FOR
SELECT [SCHEMA], TabNAME
FROM #tables
OPEN TableCursor;
FETCH TableCursor into @Sch, @Tab;
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @sql = 'exec [' + @SourceDB
+ ']..sp_executesql N''insert #TabSpaceTxt exec sp_spaceused '
+ '''''[' + @Sch + '].[' + @Tab + ']' + '''''''';
Delete from #TabSpaceTxt; -- Stores 1 result at a time
EXEC (@sql);
INSERT INTO #TabSpace
SELECT @Sch
, [TabName]
, convert(bigint, rows)
, convert(numeric(18,3), convert(numeric(18,3),
left(reserved, len(reserved)-3)) / 1024.0)
ReservedMB
, convert(numeric(18,3), convert(numeric(18,3),
left(data, len(data)-3)) / 1024.0) DataMB
, convert(numeric(18,3), convert(numeric(18,3),
left(index_size, len(index_size)-3)) / 1024.0)
Index_SizeMB
, convert(numeric(18,3), convert(numeric(18,3),
left(unused, len([Unused])-3)) / 1024.0)
[UnusedMB]
FROM #TabSpaceTxt;
FETCH TableCursor into @Sch, @Tab;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
-----------------------------------------------------
-- Caller specifies sort, Default is size
IF @SortBy = 'N' -- Use Schema then Table Name
SELECT * FROM #TabSpace
ORDER BY [Schema] asc, [TabName] asc
ELSE IF @SortBy = 'T' -- Table name, then schema
SELECT * FROM #TabSpace
ORDER BY [TabName] asc, [Schema] asc
ELSE -- S, NULL, or whatever get's the default
SELECT * FROM #TabSpace
ORDER BY ReservedMB desc
;
DROP TABLE #Tables
DROP TABLE #TabSpaceTxt
DROP TABLE #TabSpace

SQL Server Upgrade to 2k8 R2

(since we do this kind of thing sooo regularly … )

Preparation:

Update server (OS & SQL to latest SP – not ‘necessary but appears to be a common practice*)
Db -> single user mode
(user) Db -> full backups
Db -> script users/permissions/jobs et. al.
Record the account under which each service runs
Update engine
(should not have to) Restore user Db’s
(should not have to) Restore users and permissions, jobs et. al. ‘should’ still be there
Test …

* Adam – 2k8 R2 from 2k5 requires 2k5 SP2 or greater

Errors & causes: “The database cannot be opened because it is version 661. This server supports version 662 and earlier. A downgrade path is not supported.”
 Resolution is supposed to be to set the engine instance to the correct sp level but even when I tried all three (RTM, 1, 2) it did not work.

“cannot create db … “
 Check permission on target ldf & mdf dirs for sql server user

“process cannot access *.dll”
 Use “Process Explorer” to find who has a lock on the dll and kill the PID. MOM is one of the likely culprits




From: Kelly, Joe
Sent: Tuesday, September 27, 2011 12:35 PM
To: Joe, Adam; Kelly, Joe
Subject: FYI - Error Attaching Db's

Per our con, this is the error that I was receiving when trying to reattach the db’s:

“The database cannot be opened because it is version 661. This server supports version 662 and earlier. A downgrade path is not supported.”

Resolution is supposed to be to set the engine instance to the correct sp level but even when I tried all three (RTM, 1, 2) it did not work.

Lesson learned:

Db -> single user mode
(user) Db -> full backups
Db -> script users/permissions/jobs et. al.
Update engine
Restore user Db’s
Restore users and permissions, jobs et. al. ‘should’ still be there

C# Sleep Routine

int i = 0;
string retVal = "";
string sSQL2 = "EXEC dbo.isJobRunning2 'TSI_PlugX';";
//string sSQL2 = "EXEC dbo.isJobRunning2 'TSI_Plug';";
while (i == 0)
{
retVal = DataUtilities.utilSQL.DBExecScalarString(sSQL2, "TSI");
tOut.Text += retVal;
if (retVal == "0")
{
break;
}
else
{
System.Threading.Thread.Sleep(1000);
}
}

WAITFOR

WAITFOR DELAY '00:00:10.000'

SQL Agent - Find Running Jobs

EXEC msdb.dbo.sp_help_job @execution_status = 1

Find computer by IP

nslookup ...

GRANT EXEC - Start SQL Agent Job

GRANT EXEC ON [sp_start_job] TO [someUser]

http://msdn.microsoft.com/en-us/library/ms186757.aspx

Permissions

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

* SQLAgentUserRole
* SQLAgentReaderRole
* SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users. Members of sysadmin can start all local and multiserver jobs.

How to Get/Crack a Lost Excel Macro PW? - 2003 and below

http://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project


Yes there is, as long as you are using a .xls format spreadsheet (the default for Excel up to 2003). For Excel 2007 onwards, the default is .xlsx, which is a fairly secure format, and this method will not work.

As Treb says, it's a simple comparison, so one method is simply to swap out the password entry in the file using a hex editor (see What is a good Windows hex editor?). Step by step example:

1. Create a new simple excel file.
2. In the VBA part, set a simple password (say - 1234).
3. Save the file and exit. Then check the file size - see Stewbob's gotcha
4. Open the file you just created with a hex editor.
5.

Copy the lines starting with the following keys:

CMG=....
DPB=...
GC=...

6.

FIRST BACKUP the excel file you don't know the VBA password for, then open it with your hex editor, and paste the above copied lines from the dummy file.
7. save the excel file and exit.
8. Now, open the excel file you need to see the VBA code in. The password for the VBA code will simply be 1234 (as in the example I'm showing here).

Proper Case t-sql

ALTER FUNCTION ProperCase(@Text VARCHAR (8000))
RETURNS VARCHAR(8000)
AS
BEGIN
/***************************************************

2011-08-24 10:44:23.503

Source: http://weblogs.sqlteam.com/jeffs/archive/2007/03/09/60131.aspx
***************************************************/


DECLARE @Reset BIT
DECLARE @Ret VARCHAR(8000)
DECLARE @i INT
DECLARE @c CHAR (1)

SELECT @Reset = 1, @i=1, @Ret = '';

WHILE (@i <= LEN (@Text))
SELECT @c=
SUBSTRING (@Text,@i,1)
, @Ret = @Ret
+ CASE
WHEN @Reset=1
THEN UPPER(@c)
ELSE LOWER(@c)
END
, @Reset = CASE
WHEN @c LIKE '[a-zA-Z]'
THEN 0
ELSE 1
END
, @i = @i +1
RETURN @Ret

END

Undocumented strored procedure sp_MSgetversion will gives version information of sql server

EXEC master..sp_MSgetversion

http://beyondrelational.com/justlearned/posts/496/about-the-undocumented-strored-procedure-spmsgetversion.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2011Aug19

FYI - Fn for Tuning Excel Dates into T-SQL Dates (ISO 8601 standard format)

=TEXT(B5,"yyyy-mm-dd")

i.e.


07/14/11 becomes 2011-07-14
07/22/11 becomes 2011-07-22
07/28/11 becomes 2011-07-28
08/05/11 becomes 2011-08-05
08/11/11 becomes 2011-08-11
08/19/11 becomes 2011-08-19
08/25/11 becomes 2011-08-25
09/02/11 becomes 2011-09-02



Credit: http://michiel.wordpress.com/2010/03/26/convert-excel-date-value-to-sql-date/

DB Mail - profile name is not valid

Use this parameterized syntax:

EXEC msdb.dbo.sp_send_dbmail @recipients = @To
, @profile_name = 'Decision Support Services'
, @subject = @subject
, @body = @body
, @body_format = 'HTML'

Read Only DB User with DDL per Schema

USE [DB]
GO
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [domain\user]
GO

Read Only DB User with DDL

USE myDB
GO
GRANT VIEW DEFINITION DATABASE::myDB TO [DOMAIN\USER]

Cannot resolve collation conflict for equal to operation.

COLLATE DATABASE_DEFAULT

as in ...

AND jk.UOM_Act COLLATE DATABASE_DEFAULT = r.UOM_Act COLLATE DATABASE_DEFAULT

Is Job Running? SQL Server Agent Jobs

CREATE PROC isJobRunning (@jobName VARCHAR (256))
AS
BEGIN
/*****************************************************************************
Joe Kelly
2011-06-27 17:14:43.770

FROM: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/da57aedb-096c-40fb-936f-0f727fe3f605/

Determines whether a job is already running or not.

Returns: 0 - no, 1 - yes

EXEC isJobRunning N'WFX Agency Data Import'

*****************************************************************************/

SET NOCOUNT ON

DECLARE @xp_results TABLE
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

DECLARE @job_owner sysname SET @job_owner = SUSER_SNAME()
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner

DECLARE @IsJobRunning BIT

SELECT @IsJobRunning = x.running
FROM @xp_results x
INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = x.job_id
WHERE sj.name = N'WFX Agency Data Import'
--Insert your job's name between the single quotes

PRINT @IsJobRunning

END

Reading ASP.NET Application Settings From Web.config Using Classic AS

http://www.asp101.com/articles/john/readsettings/default.asp

"...Introduction

Whether you're trying to share settings between your ASP.NET and legacy classic ASP apps or are simply looking for a way to make your eventual migration to ASP.NET easier, this piece of code might be just the ticket.

The code came about because I recently found myself tasked with the annoying job of migrating a database server from an old machine to a newer piece of hardware. The database server was acting as the backend data store for a web site that's been around for quite a few years and was running a mix of classic ASP and ASP.NET applications.

Thankfully both the classic ASP and ASP.NET applications were relatively careful about keeping their connection strings centralized, but I was still needed to change the setting in both the ASP and ASP.NET applications. The switch ended up going quite smoothly, but as I was looking around to make sure I'd found everything, I started to realize just how many settings and how much information was duplicated between the legacy and new applications. It seemed like there had to be a better way.

Since pretty much all new development is being done on .NET, the obvious solution was to find an easy way for classic ASP applications to be able to read their settings from the same source as the ASP.NET applications: the web.config file.


The Code

As we're all aware, the data in the web.config file is stored in an XML-based format. Now I'll admit that my XML skills probably aren't up to par with those of you who use it on a regular basis, but I did manage to hack together a couple short functions to facilitate reading connection strings and application settings from the site's root web.config file.


readsettings.asp

<%@ Language="VBScript" %>
<%
Option Explicit

Function GetAppSetting(strAppSettingKey)
Dim xmlWebConfig
Dim nodeAppSettings
Dim nodeChildNode
Dim strAppSettingValue

Set xmlWebConfig = Server.CreateObject("Msxml2.DOMDocument.6.0")
xmlWebConfig.async = False
xmlWebConfig.Load(Server.MapPath("/Web.config"))

If xmlWebConfig.parseError.errorCode = 0 Then
Set nodeAppSettings = xmlWebConfig.selectSingleNode("//configuration/appSettings")
For Each nodeChildNode In nodeAppSettings.childNodes
If nodeChildNode.getAttribute("key") = strAppSettingKey Then
strAppSettingValue = nodeChildNode.getAttribute("value")
Exit For
End If
Next
Set nodeAppSettings = Nothing
End If
Set xmlWebConfig = Nothing

GetAppSetting = strAppSettingValue
End Function

Function GetConnectionString(strConnStringName)
Dim xmlWebConfig
Dim nodeConnStrings
Dim nodeChildNode
Dim strConnStringValue

Set xmlWebConfig = Server.CreateObject("Msxml2.DOMDocument.6.0")
xmlWebConfig.async = False
xmlWebConfig.Load(Server.MapPath("/Web.config"))

If xmlWebConfig.parseError.errorCode = 0 Then
Set nodeConnStrings = xmlWebConfig.selectSingleNode("//configuration/connectionStrings")
For Each nodeChildNode In nodeConnStrings.childNodes
If nodeChildNode.getAttribute("name") = strConnStringName Then
strConnStringValue = nodeChildNode.getAttribute("connectionString")
Exit For
End If
Next
Set nodeConnStrings = Nothing
End If
Set xmlWebConfig = Nothing

GetConnectionString = strConnStringValue
End Function
%>
< !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
< html xmlns="http://www.w3.org/1999/xhtml" >
< head>
< title>Reading ASP.NET Application Settings From Web.config Using Classic ASP< /title>
< /head>
< body>

< p>
The following values are all read from the web.config
file located in the root of the web site.
< /p>

< p>
Welcome Message: <%= GetAppSetting("WelcomeMessage") %>
< /p>

< p>
Sample Connection String: <%= GetConnectionString("SampleConnString") %>
< /p>

< p>
Thank You Message: <%= GetAppSetting("ThankYouMessage") %>
< /p>

< /body>
< /html>

As you can see they're quite easy to use both functions are quite similar. The only real difference between the two are related to the location of the settings in the XML tree and the syntax differences between the application settings and connection strings sections.

Speaking of the settings, if you use the functions as written, you'll be retrieving settings by using the connection string's name or the key associated with an application setting. These values are case sensitive, so if you're having trouble, make sure you've got everything spelled exactly the same... case and all.

Although you'll most likely be using your own existing web.config file, I'm including a simple one here for illustration.
web.config

< ?xml version="1.0"?>
< !--
Comments and whitespace shouldn't cause any problems.
-->
< configuration>
< appSettings>
< add key="WelcomeMessage" value="Welcome to our site." />
< add key="ThankYouMessage" value="Thanks for visiting... please come back soon." />
< /appSettings>
< connectionStrings>
< add name="SampleConnString"
providerName="System.Data.OleDbClient"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Inetpub\wwwroot\App_Data\test.mdb;"
/>
< /connectionStrings>
< system.web>
< compilation debug="false" />
< /system.web>
< /configuration>

That's all there is to it. A quick and easy way for you to enable your classic ASP applications to read application settings and connection strings from ASP.NET's web.config file..."