Wednesday 28 December 2011

Orphaned Database Snapshots prevents mds configuration manager database connection


Problem:


Orphaned database snapshots in dev and test environments can stop you being able to use the MDS Configuration Manager to connect to the database. This occurs if you restore the mdm_hub database when a database snapshot exists on the target server. Because the snapshot is orphaned it gets left in SUSPECT mode.

SELECT name,state_desc  FROM sys.databases

Solution:

Delete the database snapshot and make sure the database is in ONLINE mode.

More Info:

To check for the error you can run a profiler trace which will bring up an Exception 926 when trying to connect with configuration manager.
Books online error 926

Friday 23 December 2011

Viewing job history via T-SQL

This SQL Server script will display job history. The benefit of this script over displaying it from the GUI is that you get to see the job durations quickly. You will need to replace with the job you want to see history for.




select job_name, run_datetime, run_duration
from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select DISTINCT
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
) t
) t
WHERE job_name = ''
order by job_name, run_datetime

Great Plains Document Locks

Overview

The following code is useful to find who has a document locked in Great Plains.
For example if someone has a sales document open during fulfilment it will fail... so we need to check who is in the document to get them to come out of it.

T-SQL to find who has document locks in Great Plains



USE DYNAMICS

DECLARE curLocks CURSOR
FOR SELECT sqlSessions.login_name, LOCK.table_path_name, LOCK.row_id
FROM tempdb..DEX_LOCK LOCK
INNER JOIN tempdb.dbo.DEX_SESSION SESS
ON LOCK.session_id = SESS.session_id
INNER JOIN master.sys.dm_exec_sessions sqlSessions
ON SESS.sqlsvr_spid = sqlSessions.session_id

DECLARE @name varchar(40)
DECLARE @table_path_name varchar(80)
DECLARE @row_id int
OPEN curLocks

FETCH NEXT FROM curLocks INTO @name, @table_path_name, @row_id
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

DECLARE @sql varchar(1000)
SELECT @sql = 'select ''' + @name + ''' AS [LOGIN NAME], * from
' + RTRIM(@table_path_name) + ' where dex_row_id = ' + CAST(@row_id AS VARCHAR(50))
EXEC (@sql)

END
FETCH NEXT FROM curLocks INTO @name, @table_path_name, @row_id
END

CLOSE curLocks
DEALLOCATE curLocks
GO

Thursday 8 December 2011

Excel dates do not match SQL Server dates

Overview

There is a bug in excel that number representations for dates (ie 1 = 01/01/1900) do not match up with SQL Server number representations for dates. Apparently this is something that is known but will not be fixed as it will break to many peoples spreadsheets!


How dates work in Excel

Dates in excel are really just numbers formatted as a date. This picture shows the first column as the number representation of the date. All i have done is to copy the column across to B and reformat it to a Date column.



The Bug

This shows there is a 29 Feb 1900 when it never existed! So it adds another date to the calender, thereby ruining any sort of consistency with the number representation of the date.





In SQL Server

Dates work the same way in SQL Server except they start from 0 rather than 1.
DateID      SQL Dates
----------- -----------------------
0 1900-01-01 00:00:00.000
1 1900-01-02 00:00:00.000
2 1900-01-03 00:00:00.000
3 1900-01-04 00:00:00.000

And in SQL Server the dates are correct...

SELECT 58 AS DateID, CONVERT(DATETIME,58) AS [SQL Dates] UNION ALL
SELECT 59, CONVERT(DATETIME,59) UNION ALL
SELECT 60, CONVERT(DATETIME,60) UNION ALL
SELECT 61, CONVERT(DATETIME,61)

Returns the following

DateID      SQL Dates
----------- -----------------------
58 1900-02-28 00:00:00.000
59 1900-03-01 00:00:00.000
60 1900-03-02 00:00:00.000
61 1900-03-03 00:00:00.000

So what you get in the end that it can be complicated to link the dates to Excel if you are trying to match up Excel to SQL Server with the number representation of the date

Friday 2 December 2011

Unit Testing Framework with tSQLt and Redgate SQLTest

Overview

tSQLt allows you to implement unit tests in T-SQL. Unit testing will help us get to a stage where we are doing continuous integration, test driven development and Agile Development.

Redgate have released a user interface for tSQLt that integrates directly into SSMS
http://www.red-gate.com/products/sql-development/sql-test/

What is tSQLt?

http://tsqlt.org/
tSQLt is a database unit testing framework for Microsoft SQL Server. tSQLt is compatible with SQL Server 2005 (service pack 2 required) and above on all editions.

How to install

  1. Go to http://www.red-gate.com/products/sql-development/sql-test/
  2. Download the preview version
  3. Install on your machine where SSMS is installed (you will have to close SSMS)

Are there an server components installed?

Yes. You will be prompted when you open SSMS. These are the changes it makes to a database you are doing unit testing on..

A Hello World Example

Create a procedure you want to test




USE [tSQLt_Example]
GO
CREATE PROCEDURE [dbo].[getHelloWorld]
@pass BIT,
@HELLO VARCHAR(15) OUTPUT
AS
BEGIN

/*
Return Hello World or Goodbye depending on @pass param
*/
IF @pass = 1
SET @HELLO = 'Hello World!'
ELSE
SET @HELLO = 'Goodbye World!'

END
GO


Create your unit Test stored procedure

In SSMS in the SQL Test window, choose 'New Test...'

Depending what version you are on depends what happens here (anything prior to 2008R2 means you just have the write the sproc, i couldn't get anything else to happen)



USE [tSQLt_Example]
GO
Create PROCEDURE [AcceleratorTests].[testHelloWorld]
AS
BEGIN

/*
Return Hello World!
*/
DECLARE @ret VARCHAR(15)
EXEC getHelloWorld @pass = 1, @HELLO = @RET OUTPUT

/*
Check Hello World is returned
*/
EXEC tSQLt.AssertEqualsString 'Hello World!', @ret

END ;
GO


You will end up with a test in the SQL Test window if you choose refresh

Running your tests

Choose 'Run Tests' from the SQL Test window and there will be a tick or cross as to whether your test passed.

Fail Example

To see a failed test example alter the getHelloWorld stored procedure to pass in a 0 for the @pass parameter
eg. EXEC getHelloWorld @pass = 0, @HELLO = @RET OUTPUT

This is what the SQL Test window shows

And output in the SQL Test Messages



[AcceleratorTests].[testHelloWorld] failed: Expected: but was:

Related stuff

http://www.red-gate.com/products/sql-development/sql-test/
http://tsqlt.org/

Tuesday 29 November 2011

Understanding the IsNumeric() function in T-SQL

Overview


The isNumeric() function tells you whether or not an expression is a valid numeric data type.
It will return 1 when it is numeric and 0 when it is not.

Script Example

This can also be altered to deal with other types of functions

USE tempdb;
GO

CREATE TABLE IsNumericDemo (ID smallint identity primary key,
NumberString varchar(32),[IsNumeric] bit )

GO
INSERT IsNumericDemo(NumberString) VALUES ('3')
INSERT IsNumericDemo(NumberString) VALUES ('$')
INSERT IsNumericDemo(NumberString) VALUES ('$100')
INSERT IsNumericDemo(NumberString) VALUES ('$200.01')
INSERT IsNumericDemo(NumberString) VALUES ('-$300')
INSERT IsNumericDemo(NumberString) VALUES ('$-400')
INSERT IsNumericDemo(NumberString) VALUES ('$123,568.00')
INSERT IsNumericDemo(NumberString) VALUES ('$234.568.00')
INSERT IsNumericDemo(NumberString) VALUES ('3.E4')
INSERT IsNumericDemo(NumberString) VALUES ('FFFF')
INSERT IsNumericDemo(NumberString) VALUES ('2^10')
INSERT IsNumericDemo(NumberString) VALUES ('0000001')
INSERT IsNumericDemo(NumberString) VALUES ('1234+')
INSERT IsNumericDemo(NumberString) VALUES ('+1234')
INSERT IsNumericDemo(NumberString) VALUES ('twenty')
INSERT IsNumericDemo(NumberString) VALUES ('(40)')
INSERT IsNumericDemo(NumberString) VALUES ('-40')
GO



-- Set the IsNumeric flag

UPDATE IsNumericDemo
SET [IsNumeric] = ISNUMERIC(NumberString)



-- Check results

SELECT id,numberstring,CASE [isnumeric] WHEN 1 THEN 'yes' ELSE 'no' END AS 'isNumeric'
FROM IsNumericDemo

GO

DROP TABLE IsNumericDemo
go

Friday 25 November 2011

Analysis Services DMVs

DMV Overview

Analysis Services DMVs are a bit like the SQL DMVs. They allow you to view the inner workings and structure of an Analysis Services cube

Solution

Here's a useful link that gives quite a lot of info about them:

http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

  • You run them from an MDX query window in SSMS
  • They look like SQL statements
  • You can use a WHERE clause and and ORDER BY clause
  • You can use Instr(Column name) > 0 instead of 'LIKE'
  • You can't use '<>'

A couple of useful ones:

The Analysis Services version of sp_who2

select * from $system.discover_connections

or a similar one:

select * from $system.discover_sessions

or

select * from $system.discover_commands

How to take a look at objects in the cube:

select * from $system.discover_object_activity

To see which partitions are being processed

select mid(command_text, instr(command_text, '') + 13, 25) from $system.discover_commands where instr(command_text, 'ProcessFull') > 0

Analysis Services - Clearing the Cache

Analysis Services - Clearing the Cache

If you want to clear the analysis services cache for testing purposes you can run the following xmla command while connected to the cube in Management Studio


<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ClearCache>
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>
</Batch>

Rebuild all indexes on all tables T-SQL

The command to reindex is different for SQL2000 to SQL2005 and 2008:

  • SQL 2000 uses the DBCC REINDEX command and
  • SQL2005 uses the ALTER INDEX ALL ON REBUILD
Example:

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

-- SQL 2000
--DBCC DBREINDEX(@TableName,' ',90)

-- SQL 2005/2008
EXEC('ALTER INDEX ALL ON ' + @tablename + ' REBUILD')

FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Thursday 24 November 2011

Select sp_who2 into a temporary table

Select sp_who2 into a temporary table

The following script is useful to filter down sp_who2 results. For example if you want to find all connections to one database you can add a where clause to the following query


CREATE TABLE ##temp_sp_who2
    (
      SPID INT,
      Status VARCHAR(1000) NULL,
      Login SYSNAME NULL,
      HostName SYSNAME NULL,
      BlkBy SYSNAME NULL,
      DBName SYSNAME NULL,
      Command VARCHAR(1000) NULL,
      CPUTime INT NULL,
      DiskIO INT NULL,
      LastBatch VARCHAR(1000) NULL,
      ProgramName VARCHAR(1000) NULL,
      SPID2 INT
      , rEQUESTID INT NULL --comment out for SQL 2000 databases

    )


INSERT  INTO ##temp_sp_who2
EXEC sp_who2

SELECT  *
FROM    ##temp_sp_who2
WHERE   DBName = '<database_name>'

DROP TABLE ##temp_sp_who2

Finding Potentially redundant Jobs

Finding potentially redundant jobs

Often if there is a large lists of jobs on a server it can be hard to go through and find the jobs that are redundant or havent been run in a long time.
This script is a handy way of finding these jobs.

In this example we are finding jobs older than 2010. You can easily change this by changing the date at the bottom of the script



DECLARE @is_sysadmin INT
DECLARE @job_owner sysname
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

CREATE TABLE #xp_results (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 @is_sysadmin, @job_owner


SELECT j.NAME, t.*
FROM #xp_results t
LEFT OUTER JOIN msdb.dbo.sysjobs j ON t.job_id = j.job_id
WHERE last_run_date < 20081231
AND j.enabled = 0

DROP TABLE #xp_results

T-SQL Running a script for each database using sp_msforeachdb

Here is a quick example of using SQL server stored procedure sp_msforeachdb



exec sp_msforeachdb '

use [?]
select count(*) from sys.all_objects

'

T-SQL Dynamic SQL Variable replacement

Overview of technique
A nice way of doing SQL Injection is to:
  1. Copy the code you want dynamic into the script
  2. Put quotes on each end
  3. Replace the parameters with <parametername>
  4. Using the sql REPLACE function, switch out with the value of the parameter
  5. Query your little heart out
This T-SQL script shows an example



/*
 Declare variables
*/
DECLARE @wcSQL AS NVARCHAR(max)
DECLARE @columnWeWantToReturn VARCHAR(50)
SET @columnWeWantToReturn = 'name'

/*
 Prepare SQL Statement
*/
SET @wcSQL = 'SELECT <name> 
 FROM sys.sysobjects'
/*
 Replace parameters
*/      
SET @wcSQL = REPLACE(@wcSQL, '<name>', @columnWeWantToReturn)

/*
 Execute the dynamic sql
*/
EXEC sp_executeSQL @statement = @wcSQL


Hello World with SQLCLR

Overview

This article will show you how to create a very simple "Hello World" stored procedure using VB.NET or C# to help introduce this feature and demonstrate how to load a .Net assembly into SQL Server 2005.

.NET Code

The first thing we need to do is write our .Net code for our Stored Procedure and compile it. In this case, we are simply going to send the string "Hello World from SQLCLR !!" back to the client. We do this by using the SqlPipe.Send method of the SqlContext object. This can be thought of as the context of the spid currently executing the stored procedure. The Send method of the SqlPipe object can be used to send messages back to the client.



[C#]
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class SQLCLR{

[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld()
{
SqlContext.Pipe.Send("Hello World from SQLCLR !!");
}
}

In order for us to use this code in SQL Server 2005 we must first compile it into an assembly. We can then load it into SQL Server and create our TSQL Stored Procedure header for our .Net code. The command below demonstrates how to compile the code into an assembly. For the purposes of this article we will assume that the code has been saved in a source file called c:\HelloWorld.vb or c:\HelloWorld.cs. To compile this open a command prompt and execute the relavent lines to change to the .NET 2.0 directory and compile the code.



[C#]
cd "%SystemRoot%\Microsoft.NET\Framework\v2.0.50727"
csc /target:library c:\HelloWorld.cs

TSQL Code

Now that we have created our assembly (c:\HelloWorld.dll) we can load it into SQL Server and create our Stored Procedure. We do this by using the new CREATE ASSEMBLY command as below.We need to ensure that execution of user defined CLR code is enabled using sp_configure



exec sp_configure 'clr enabled', 1
reconfigure
go
create assembly HelloWorld from 'c:\HelloWorld.dll'
with permission_set = safe


Now that we have loaded our assembly, we can create our Stored Procedure. To do this we simply use the standard TSQL CREATE PROCEDURE syntax but instead of defining a TSQL procedure body, we specify an EXTERNAL NAME that points to our .NET Code. We specify the assembly, class and method name as shown below.



create procedure HelloWorld as external name HelloWorld.SQLCLR.HelloWorld

Now that the Stored Procedure is created, we can simply execute it and see the result.


exec HelloWorld

[Results]

Hello World from SQLCLR !!

Accessing External Resources



ALTER DATABASE SCRIBEINTERNAL
SET TRUSTWORTHY ON

CREATE assembly HelloWorld from 'c:\SQLCLR\ClassLibrary1.dll'
with permission_set = UNSAFE


References

see http://social.msdn.microsoft.com/Forums/en/sqlnetfx/thread/58b01172-1487-46a9-88bc-bd0970782d1d

Creating an Assembly

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

Supported Assemblies

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