Tuesday, July 31, 2012

Is Agent Job Running???

CREATE PROC portal.isJobRunning (@jobName VARCHAR (256))
AS
BEGIN

/**************************************************************************
Joe Kelly
2012-07-31 17:56:04.540

Determines if a job is running or not

EXEC portal.isJobRunning @jobName = 'Export%'

Template source:
http://weblogs.asp.net/johnbilliris/archive/2009/10/13/how-to-determine-if-a-sql-job-is-running.aspx

**************************************************************************/
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 -- BOOL
, request_source INT NOT NULL
, request_source_id sysname COLLATE database_default NULL
, running INT NOT NULL -- BOOL
, current_step INT NOT NULL
, current_retry_attempt INT NOT NULL
, job_state INT NOT NULL
)

INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'

SELECT xpr.running
, sjv.name
FROM @xp_results xpr
JOIN MSDB.dbo.sysjobs_view sjv
ON xpr.job_id = sjv.job_id
WHERE sjv.name LIKE @jobName

END

Flight Recorder OLAP CUBE Trace


Default 5MB log on OLAP installations:

http://msdn.microsoft.com/en-us/library/ms174827.aspx (search for
FlightRecorder)

http://support.microsoft.com/kb/2128005

FlightRecorder

 

The events and columns captured by the Flight Recorder are controlled by a trace definition file.  The advanced server property named Log\FlightRecorder\TraceDefinitionFile controls the definition file used by the Fligth Recorder.  By default, the property is blank, which means the Flight Recorder defaults to using the trace file definition stored in the file flightrecordertracedef.xml, which is stored in the ..\OLAP\bin directory wherever SSAS is installed.

 http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/9bcc369d-5f23-41fc-916d-448e20182430

Monday, July 30, 2012

List of Tables in Access DB


Source???

SELECT MSysObjects.Name AS table_name FROM MSysObjects WHERE (((Left([Name],1))<>"~") AND ((Left([Name],4))<>"MSys") AND ((MSysObjects.Type) In (1,4,6))) order by MSysObjects.Name

Thursday, July 19, 2012

Debugging SSCode - UCSFMC

C# as a scripting language, WebForms with no code behind, no use of app_code, a single common page inheritance for errors and the use of external include files for calling external (from the page) methods - how to debug.

a.) Remove tag referring to .\SSCode\xx.cs

b.) Take contents (functions) of xx.cs and create a file in App_Code with the calling page's name and a class wrapper with the file name

c.) In xx.aspx change the page directive references to the new class and file name.

Monday, July 2, 2012

Classic ASP Errors on Large Result Set with No Information

Check the IIS log.

I this case the IIS buffer had to be increased.

Example log message: 2012-07-02 19:46:09 W3SVC1522741043 127.0.0.1 POST /Reports/Reports_Var_CC_Rev.asp |0|ASP_0251_:_80004005|Response_Buffer_Limit_Exceeded 655 UCSFMC\kellyjoe 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+8.0;+Windows+NT+5.2;+Trident/4.0;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30;+.NET+CLR+3.0.04506.648;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729;+.NET4.0C;+.NET4.0E;+InfoPath.3) 500 0 0

http://support.microsoft.com/kb/925764

Friday, June 8, 2012

runas syntax

C:\Users\epsi>runas /user:domain\user "D:\Program Files (x86)\Microsoft SQL
Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

Displaying Table Sizes using T-SQL

For each table get table size

http://blog.securahosting.com/technical/databases/displaying-table-sizes-using-t-sql/

SELECT LEFT(OBJECT_NAME(id), 30) AS [Table],
CAST( CAST(reserved * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS 'Allocated (MB)',
CAST(CAST(dpages * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS 'Used (MB)',
CAST(CAST((reserved - dpages) * 8192 AS DECIMAL(10,1)) / 1000000.0 AS DECIMAL(10,1)) AS 'Unused (MB)',
rowcnt AS 'Row Count (approx.)'
FROM sysindexes WHERE indid IN (0, 1) AND OBJECT_NAME(id) NOT LIKE 'sys%' AND OBJECT_NAME(id) NOT LIKE 'dt%'
ORDER BY reserved DESC, LEFT(OBJECT_NAME(id), 30)

Wednesday, June 6, 2012

SQL Database Mail "NOTE: Failed to notify 'operator' via email."

Credit: http://leopard-solutions.com/blog/post/2011/06/15/SQL-Database-Mail-NOTE-Failed-to-notify-operator-via-email.aspx

If you have set up your SQL Database Mail Operators and SMTP details correctly, but keep getting a message like this: "NOTE: Failed to notify 'operator' via email.", you may not have enabled the Alert System In the SQL Server Agent.

Here's how to enable the Mail System:

Right click "SQL Server Agent" in the Object Explorer and click "Properties"

Go to Alert System, and check the "Enable mail profile" box and select the correct Mail system and Mail profile.

Wednesday, May 9, 2012

Tip to Prevent Scientific Notation in T-SQL Output

SELECT CONVERT(DECIMAL(24, 12), CONVERT(FLOAT, '1.08E+05'));

http://stackoverflow.com/questions/7473081/convert-scientific-notation-to-float-when-using-openrowset-to-import-a-csv-file

Friday, April 13, 2012

Excel compare columns

Sub Compare2Cols()

' Principals

' For x = 1 To 32 Step 4

' For y = 2 To 106

' Cost Centers

' For x = 1 To 83 Step 4

' For y = 2 To 78

' Levels

' For x = 5 To 8 Step 2

' For y = 2 To 32

For x = 5 To 8 Step 2

For y = 2 To 32

'For show ...

Cells(y, x).Select

If Cells(y, x) <> Cells(y, x + 1) Then

With Cells(y, x).Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

.PatternTintAndShade = 0

End With

With Cells(y, x + 1).Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

.PatternTintAndShade = 0

End With

Else

With Cells(y, x).Interior

.Pattern = xlNone

.TintAndShade = 0

.PatternTintAndShade = 0

End With

With Cells(y, x + 1).Interior

.Pattern = xlNone

.TintAndShade = 0

.PatternTintAndShade = 0

End With

End If

Next y

Next x

Cells(y, x).Select

End Sub

Thursday, April 12, 2012

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

Wednesday, March 28, 2012

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.*/

Friday, March 16, 2012

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

Tuesday, March 13, 2012

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'

Wednesday, February 29, 2012

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

Friday, February 17, 2012

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)

Monday, February 6, 2012

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