Unable to debug using SSMS on Windows 7 & Windows 2008 R2 Server

Posted by on 5, April 2012 No comments

When trying to run the dubugger for T-SQL via SQL Server Managemnt Studio on Windows 7 or Windows 2008 R2 Server connecting to an instance of SQL Server 2008 R2 on the machine you may well get the error message “Unable to start T-SQL Debugging. Could not connect to the computer ‘(local)’. The requested name is valid, but no data of the requested type was found.”, this is if you use (local) as the conenction, if like may you use period ie . as the connection the error will be the same, except for the machine name.

The solution it seems is very simple, so simple it got past me for weeks!

It seems on Windows 7 and Windows 2008 R2 server you have to use the machine name to be able to debug, as simple as that.

Share

Returning a very large data string in Microsoft SQL Server Management Studio

Posted by on 24, August 2011 No comments

Working as I do on Data Warehouses which are built by a build system that uses meta data about the external source systems, I sometimes find myself writing procedures and functions that create SQL scripts as their output. Such as for instance an ETL procedure for an external system. Occasionally these generate very large scripts that exceed the 65,535 (grid) or 8,192 (text) limit for the results pane. Which can make debugging them a real pain, as the best way to debug them, I find, is have the output for a known execution go to the results tab in SSMS and then paste this into another window in SSMS to see what the script looks like.

Well there is it seems a simple answer, typically my return type will be either varchar(max) or nvarchar(max), if you cast this to XML then the maximum size returned via SSMS is much larger, up to unlimited in fact.

Share

Event 424 Analysis Services

Posted by on 9, May 2011 No comments

For reasons that are not entirely clear yet, I started to get a Event ID 424 on a SQL Server Analysis Services machine in the Windows Log/Event Viewer (Application) and Event ID 23. ID 424 is about an invalid encryption key and 23 that a cryptographic key could not be loaded. The only entry on MSD Social had suggested (until I added my reply) that a reinstall was required.

Fortunatly it is not, you just need to rename the current OLAP data directory and create a new blank one of the correct name, whilst SQL Server ANalysis Services is shut down of course. Once SQL Server Analysis Services is restarted all should be fine. Though you will need to recreate all your old cubes.

At the moment the potential root cause for the problem I saw was enabling OS level encryption on the data folder for SQL Server Analysis Services, so it may not be a good idea to try that. Once I have tested that theory I shall update this post.

Share

Compressing all tables in a SQL Server 2008 database

Posted by on 21, February 2011 9 comments

To date I have found on SQL Server 2008 R2 with a reasonable external disk array and reasonable number of processor cores that disk compression offers a significant improvemnt in terms of performance as well as the number of records you can store in any given amount of disk space. Below is a quick script I knocked up to Page compress all tables and indexes in a database.

DECLARE @InjectionSQL NVARCHAR(MAX),
   @Do_Tables BIT = 1,
   @Do_ClusteredIndexes BIT = 1,
   @Do_NonClusteredIndexes BIT = 1

DECLARE Execution_Cursor CURSOR FOR
SELECT DISTINCT ‘ALTER TABLE [' + sys.schemas.name + '].[' + sys.tables.name+ '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)’ AS InjectionSQL
   FROM
      sys.tables
         INNER JOIN sys.partitions ON sys.partitions.object_id = sys.tables.object_id
         INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
      WHERE(sys.partitions.data_compression <> 2)
         AND (index_id = 0)
UNION
SELECT DISTINCT ‘ALTER INDEX [' + sys.indexes.name + '] ON [' + sys.schemas.name + '].[' + sys.tables.name+ '] REBUILD PARTITION = ALL WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE )’
   FROM
      sys.indexes
         INNER JOIN sys.tables ON sys.indexes.object_id = sys.tables.object_id
         INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
         INNER JOIN sys.partitions ON sys.partitions.object_id = sys.indexes.object_id AND sys.partitions.index_id = sys.indexes.index_id
   WHERE(sys.partitions.data_compression <> 2)
      AND (sys.partitions.index_id <> 0)
      AND ((sys.indexes.type = 1 AND @Do_ClusteredIndexes = 1) OR @Do_ClusteredIndexes = 0)
      AND ((sys.indexes.type = 2 AND @Do_NonClusteredIndexes = 1) OR @Do_NonClusteredIndexes = 0)
OPEN Execution_Cursor
FETCH NEXT FROM Execution_Cursor INTO @InjectionSQL

WHILE @@FETCH_STATUS = 0
BEGIN
   EXECUTE (@InjectionSQL)
   FETCH NEXT FROM Execution_Cursor INTO @InjectionSQL
ENDCLOSE Execution_Cursor
DEALLOCATE Execution_Cursor

Share

How to tell which DML Operation fired a trigger

Posted by on 20, February 2011 No comments

SQL Server does not give us the ability to interrogate which DML operation caused a trigger to be fired and sometimes we may want to have broadly similar logic that varies only slightly according to the DML operation. The classic example would be data change logging where you may in the audit table want to note if the data was changed, inserted or deleted.      

There is though a very simple way around this which is to look at the contents of the INSERTED and DELETED objects within the Trigger, as follows.      

CREATE  TRIGGER dbo.TR_IUD_CodeSample ON dbo.CodeSample AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Operation NVARCHAR(10) = ‘Update’
    IF NOT EXISTS (SELECT NULL FROM INSERTED)
    BEGIN
        SET @Operation = ‘Delete’
    END
    ELSE
    IF NOT EXISTS (SELECT NULL FROM DELETED)
    BEGIN
        SET @Operation = ‘Insert’
    END
    SELECT @Operation
END

GO 

 To test this use 

INSERT INTO CodeSample(TestIntValue)VALUES(1),(2)
UPDATE CodeSample SET TestIntValue =TestIntValue
DELETE FROM CodeSample  

The results for this are

———-
Insert
Update
Delete

Share

Microsoft Support results in revenue for Apple

Posted by on 2, February 2011 No comments

Today I had to call Microsoft. Never a good idea. Having spoken to person A who put me through to Person B, who then transferred me to person C, I was told to call person A. Being at a loose end I did.

Whilst Person A went off the investigate my enquiry I was put on hold and thought ‘hey good hold music’, as I liked it so much I used Shazam on my iPhone to identify the track as a Colplay one (Viva La Vida). This I promptly brought off iTunes….

Now if only Microsoft could work out a way to watermark the music on their hold system, which Shazam would recognise then at least they could earn some affiliate fees from Apple.

Share

Hello

Posted by on 25, January 2011 No comments

This blog is not intended to be any great insight into how to use Databases, more the random thoughts of Database developer, who most of the time will be using it as a repository of his own code snippets for his own use.

Share