Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Administration

Code Audit - The Beginning

For the next few months, I will be involved in an interesting project for a mission critical application that our company have outsourced to a well-known and respected consulting company here Sweden. My part will be the technical details of the forecasting application now when our former DBA has left our company. Today I took a brief look at the smallest building blocks; the Functions. No function is inline so I can assume some of the performance issues are derived from these. One function I stumled across is very simple. All it does is to add a timepart from current execution time to...

posted @ Thursday, July 21, 2011 8:44 AM | Feedback (2) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 ]

Some datatypes doesn't honor localization

This bug has haunted me for a while, until today when I decided to not accept it anymore. So I filed a bug over at connect.microsoft.com, https://connect.microsoft.com/SQLServer/feedback/details/636074/some-datatypes-doesnt-honor-localization, and if you feel the way I do, please vote for this bug to be fixed. Here is a very simple repro of the problem DECLARE  @Sample TABLE          (              a DECIMAL(38, 19),              b FLOAT          ) INSERT   @Sample          (              a,              b          ) VALUES   (1E / 7E, 1E / 7E) SELECT   * FROM     @Sample Here is the actual output.                                       a                      b --------------------------------------- ----------------------                   0.1428571428571428400      0,142857142857143   I think that both columns should have the same decimal separator, don't you? //Peter

posted @ Friday, January 14, 2011 9:09 AM | Feedback (12) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

How to determine if you should use full or differential backup?

Or ask yourself, "How much of the database has changed since last backup?". Here is a simple script that will tell you how much (in percent) have changed in the database since last backup. -- Prepare staging table for all DBCC outputs DECLARE @Sample TABLE         (             Col1 VARCHAR(MAX) NOT NULL,             Col2 VARCHAR(MAX) NOT NULL,             Col3 VARCHAR(MAX) NOT NULL,             Col4 VARCHAR(MAX) NOT NULL,             Col5 VARCHAR(MAX)         )   -- Some intermediate variables for controlling loop DECLARE @FileNum BIGINT = 1,         @PageNum BIGINT = 6,         @SQL VARCHAR(100),         @Error INT,         @DatabaseName SYSNAME = 'Yoda'   -- Loop all files to the very end WHILE 1 = 1     BEGIN         BEGIN TRY             -- Build the...

posted @ Thursday, October 21, 2010 4:34 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms Administration ]

Bug in SQL Server Management Studio

When I try to rename a node name such as a table or column in Management Studio, I cannot use DELETE key to remove previous characters. However, I can use BACKSPACE key. Please vote here https://connect.microsoft.com/SQLServer/feedback/details/570758/cannot-use-delete-key-in-ssms-and-object-explorer to fix this little, but annoying, issue. //Peso

posted @ Friday, June 25, 2010 10:13 AM | Feedback (0) | Filed Under [ SQL Server 2008 Administration ]

SQL Server 2008 Compression

Hi! Today I am going to talk about compression in SQL Server 2008. The data warehouse I currently design and develop holds historical data back to 1973. The data warehouse will have an other blog post laster due to it's complexity. However, the server has 60GB of memory (of which 48 is dedicated to SQL Server service), so all data didn't fit in memory and the SAN is not the fastest one around. So I decided to give compression a go, since we use Enterprise Edition anyway. This is the code I use to compress all tables with PAGE compression. DECLARE @SQL VARCHAR(MAX)   DECLARE curTables CURSOR FOR        ...

posted @ Thursday, June 17, 2010 2:16 PM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Administration ]

Simple script to get logins and default database names

SELECT     u.name,            l.dbname FROM       sys.sysusers AS u INNER JOIN sys.syslogins AS l ON l.sid = u.sid

posted @ Monday, June 14, 2010 1:27 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Simple script to get referenced table and their column names

-- Setup user supplied parameters DECLARE @WantedTable SYSNAME   SET     @WantedTable = 'Sales.factSalesDetail'   -- Wanted table is "parent table" SELECT      PARSENAME(@WantedTable, 2) AS ParentSchemaName,             PARSENAME(@WantedTable, 1) AS ParentTableName,             cp.Name AS ParentColumnName,             OBJECT_SCHEMA_NAME(parent_object_id) AS ChildSchemaName,             OBJECT_NAME(parent_object_id) AS ChildTableName,             cc.Name AS ChildColumnName FROM        sys.foreign_key_columns AS fkc INNER JOIN  sys.columns AS cc ON cc.column_id = fkc.parent_column_id                 AND cc.object_id = fkc.parent_object_id INNER JOIN  sys.columns AS cp ON cp.column_id = fkc.referenced_column_id                 AND cp.object_id = fkc.referenced_object_id WHERE       referenced_object_id = OBJECT_ID(@WantedTable)   -- Wanted table is "child table" SELECT      OBJECT_SCHEMA_NAME(referenced_object_id) AS ParentSchemaName,             OBJECT_NAME(referenced_object_id) AS ParentTableName,             cc.Name AS ParentColumnName,             PARSENAME(@WantedTable, 2) AS ChildSchemaName,             PARSENAME(@WantedTable, 1) AS ChildTableName,             cp.Name AS ChildColumnName FROM        sys.foreign_key_columns AS fkc INNER JOIN  sys.columns AS cp ON cp.column_id =...

posted @ Tuesday, June 08, 2010 4:02 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

How to get current connection settings

SELECT  name AS Setting,         CASE             WHEN @@OPTIONS & number = number THEN 'ON'             ELSE 'OFF'         END AS Value FROM    master..spt_values WHERE   type = 'SOP'         AND number > 0 Or this SELECT  * FROM    sys.dm_exec_sessions WHERE   session_id = @@SPID Or this SELECT  * FROM    sys.dm_exec_request WHERE   session_id = @@SPID

posted @ Saturday, March 20, 2010 9:53 PM | Feedback (6) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Get client IP address

Up until now, I have used convuluted approaches to get the current user client IP-address. This weekend I browsed Books Online for SQL Server 2008 R2 (November CTP) and found this new cool function! SELECT  CONNECTIONPROPERTY('net_transport') AS net_transport,         CONNECTIONPROPERTY('protocol_type') AS protocol_type,         CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,         CONNECTIONPROPERTY('local_net_address') AS local_net_address,         CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,         CONNECTIONPROPERTY('client_net_address') AS client_net_address Or this   SELECT  * FROM    sys.dm_exec_connections WHERE   session_id = @@SPID

posted @ Saturday, March 20, 2010 9:48 PM | Feedback (6) | Filed Under [ SQL Server 2008 Administration ]

Is it possible to have multiple sets of key columns in a table?

Filtered indexes is one of my new favorite things with SQL Server 2008. I am currently working on designing a new datawarehouse. There are two restrictions doing this It has to be fed from the old legacy system with both historical data and new data It has to be fed from the new business system with new data When we incorporate the new business system, we are going to do that for one market only. It means the old legacy business system still will produce new data for other markets (together with historical data for all markets)...

posted @ Tuesday, February 16, 2010 2:26 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration ]

First take on Azure

I created a small table with about 150,000 records in order to do some aggregations. There are some prerequisites to comply for, but that is easily done with replace function. With Azure, it took almost 4 times as long time as SQL Server Express on my old development machine to complete the query. With this simple test, I find the use for Azure very limited, as of today. It is excellent for small companies who is not hosting their own environment themself. For companies on a web hosting company which doesn't support Microsoft SQL Server it will do just fine. But today, almost...

posted @ Saturday, December 12, 2009 12:26 AM | Feedback (3) | Filed Under [ SQL Server 2008 Administration Miscellaneous ]

Problem with SQL Server service restart

The few last days, our hosting company have  updated their VMware environment a number of times and thus have forced equal number of restarts for our database servers. The problem with this is that one of the databases, Yoda, needed 1.5-2.0 hours to start up due to "In Recovery" status. I asked around what could be the cause of this and also read some excellent posts by Kimberley Tripp http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx I saw the undocumented DBCC LogInfo command and decided to give it a go. To my surprise, there were 33,636 records returned. As a general rule of thumb, you should have about 8-16 VLF...

posted @ Wednesday, December 09, 2009 12:05 PM | Feedback (4) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Lesson learned by Trial and Error

Yesterday, I decided to install Microsoft SQL Server 2008 R2 (November CTP) on my local machine. I already had SQL Server Express 2008 installed so the first step was to uninstall Express, since there seemed to be no upgrade path. Uninstalling went great. I then installed 2008 R2 and rebooted my machine. I attached my sample databases, and my test database. To my surprise, the Test database was now corrupted! File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test.ldf" may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred...

posted @ Wednesday, November 18, 2009 11:46 AM | Feedback (4) | Filed Under [ SQL Server 2008 Administration ]

How to tell if you are running on a virtual environment

This is a piece of code I often use to see if the database server is running in a virtual environment. That is not always obvious or known by the developers. DECLARE @Result TABLE         (             LogDate DATETIME,             ProcessInfo NVARCHAR(MAX),             Text NVARCHAR(MAX)         )   INSERT  @Result EXEC    sys.xp_readerrorlog 0, 1, 'System Manufacturer', 'VMware'   IF EXISTS (SELECT * FROM @Result)     SELECT 'It seems you are running on VMware.' AS Msg ELSE     SELECT 'It seems you are not running on VMware.' AS Msg If you test this in your environment and found other virtual manufacturers, please let me know so I can add them in the code above.

posted @ Thursday, November 12, 2009 3:27 PM | Feedback (10) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

CHAR(0) is not that innocent you may think

Some days ago I posted a solution for a simple problem on a forum about to delete multiple spaces in one statement (not using loop). My suggestion was declare @s varchar(100) set @s = 'xxxx                                  yyyyy          zzzzzz' SELECT REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(2)), CHAR(2) + ' ', ''), CHAR(2), '') I used CHAR(2) because that is not commonly used in normal texts. I then thought I could use CHAR(0) to be on the "safe" side, and now strange things begun to happen. Run this on your own risk, as you will see soon. Select  q,         len(q) from    (             SELECT    REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(0)), CHAR(0) +...

posted @ Sunday, August 23, 2009 9:33 AM | Feedback (1) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 Miscellaneous ]

Change collation

Yesterday I hade the unfortenate task to change the database collation for a particular database. Not only the default database collation should be changed, but also all columns! After some investigating about how to do this, I noticed that check constraints and foreign key constraints must be removed before changing a collation. Also indexes needed to be removed for this operation to succeed. I did it manually since it was my first time, but I wrote down the steps and today I gathered the notes into a beta-script, just in case I need to do this again in the future Why a script? Because most...

posted @ Wednesday, August 19, 2009 2:07 PM | Feedback (3) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Curiosity about SQL Server 2008 R2 installation

I installed SQL Server Enterprise 2008 R2 on my laptop as a new instance. I also have SQL Server Developer 2008 as default instance. For the default instance, I have a database named Test on a separate partition of my laptop harddrive. After installing R2, I tried to attach the Test database, and failed, because I didn't detach the database from the default instance. So now I detached the Test database from the default instance but didn't make it because it was marked as suspect, even if the icon displayed good. So I took the Test database Offline and then detached it (there...

posted @ Wednesday, August 12, 2009 9:04 AM | Feedback (1) | Filed Under [ SQL Server 2008 Administration ]

Script out your foreign keys

This is a simple query for creating a script for your foreign keys in your database. It may need tweaking for composite keys. If that's the case, see here how to concatenate http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254 ;WITH Yak AS (     SELECT      ROW_NUMBER() OVER (ORDER BY o.[schema_id]) AS RowID,                 QUOTENAME(o.name) AS CONSTRAINT_NAME,                 QUOTENAME(SCHEMA_NAME(po.[schema_id])) AS FOREIGN_TABLE_SCHEMA,                 QUOTENAME(po.name) AS FOREIGN_TABLE_NAME,                 QUOTENAME(rccu.COLUMN_NAME) AS FOREIGN_COLUMN_NAME,                 QUOTENAME(SCHEMA_NAME(ro.[schema_id])) AS PRIMARY_TABLE_SCHEMA,                 QUOTENAME(ro.name) AS PRIMARY_TABLE_NAME,                 QUOTENAME(rc.name) AS PRIMARY_COLUMN_NAME,                 CASE fk.is_disabled                     WHEN 0 THEN 'CHECK'                     ELSE 'NOCHECK'                 END AS [ENABLED]     FROM        sys.foreign_keys AS fk     INNER JOIN sys.objects AS o ON o.[object_id] = fk.[object_id]     INNER JOIN sys.objects AS po ON po.[object_id] = fk.parent_object_id     INNER JOIN sys.objects AS ro ON ro.[object_id]...

posted @ Friday, August 07, 2009 12:43 PM | Feedback (1) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Manipulate XML data with non-xml columns and not using variable

Some time ago, I displayed how to work with XML data when searching for data stored in a XML column. Here Some XML search approaches and here Updated XML search (test case with variables). Today I thought I should demonstrate how to insert and update XML columns with data from other columns and not using variables. Well, I do have some examples of using variables in here because I wanted to display the core difference. And this is my first blog post (not counting the previous announcement) since I become a MVP, I thought this blog post should be about...

posted @ Friday, July 03, 2009 3:57 PM | Feedback (4) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Composable DML

With the arrival of SQL Server 2005 the new OUTPUT operator was introduced. The OUTPUT operator works much like a "local trigger" on the current statement. The drawback is that there is no way to filter the returned resultset directly. You have to insert the resultset in a staging table and work from there. With SQL Server 2008 you now have a tool named Composable DML. What is then Composable DML? Well, with this tool you can have a statement of UPDATE, DELETE and even MERGE as a data source for your query! In this example I am showing you how to audit certain...

posted @ Wednesday, April 08, 2009 12:24 PM | Feedback (5) | Filed Under [ Optimization SQL Server 2008 Administration ]

@@ERROR, BEGIN TRY/CATCH and XACT_ABORT

Today I am showing you the difference between @@ERROR, BEGIN TRY/CATCH and XACT_ABORT.  The three alternatives to error handling works little different and let us intercept the error handling in different stages in the process. First I am going to show you the ordinary @@ERROR check which most of you are used to.   IF OBJECT_ID('uspTest_2000') IS NOT NULL ...

posted @ Tuesday, April 07, 2009 9:53 AM | Feedback (5) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Get all your databases and their sizes

SELECT      @@SERVERNAME AS SqlServerInstance,             db.name AS DatabaseName,             SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE f.size / 128.0E END) AS DatabaseSize,             SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize,             SUM(af.size / 128.0E) AS TotalSize FROM        master..sysdatabases AS db INNER JOIN  master..sysaltfiles AS af ON af.[dbid] = db.[dbid] WHERE       db.name NOT IN ('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb') -- System databases             AND db.name NOT IN ('Northwind', 'pubs', 'AdventureWorks', 'AdventureWorksDW')   -- Sample databases GROUP BY    db.name

posted @ Thursday, February 12, 2009 11:20 AM | Feedback (3) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

How to tell who did a backup when

SELECT      db.name AS DatabaseName,             bf.logical_name AS LogicalName,             CASE bs.[type]                         WHEN 'D' THEN 'Database'                         WHEN 'I' THEN 'Differential database'                         WHEN 'L' THEN 'Log'                         WHEN 'F' THEN 'File or filegroup'                         WHEN 'G' THEN 'Differential file'                         WHEN 'P' THEN 'Partial'                         WHEN 'Q' THEN 'Differential partial'                         ELSE 'Unknown'             END AS BackupType,             CASE bf.file_type                         WHEN 'D' THEN 'SQL Server data file'                         WHEN 'L' THEN 'SQL Server log file'                         WHEN 'F' THEN 'Full text catalog'                         ELSE 'Unknown'             END AS FileType,             bs.user_name AS UserName,             bs.backup_start_date AS StartDate,             bs.backup_finish_date AS FinishDate,             CAST(bs.software_major_version AS VARCHAR(11)) + '.'            ...

posted @ Thursday, February 12, 2009 10:36 AM | Feedback (2) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Strange happening

I had a strange scenario today and I can't reproduce it. I changed current database to adventureworks and ran following code DECLARE     @SQL NVARCHAR(200) SET         @SQL = 'SELECT  DB_NAME()' EXEC        sp_executesql @SQL EXEC        (@SQL) The sp_executesql statement returned "master" and exec statement returned "adventureworks". Anyone knows why?

posted @ Thursday, December 04, 2008 4:58 PM | Feedback (6) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

How many logical and physical processors do you have?

SELECT cpu_count AS [Logical CPUs],         cpu_count / hyperthread_ratio AS [Physical CPUs] FROM   sys.dm_os_sys_info

posted @ Tuesday, December 02, 2008 5:08 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

An alternative to IDENTITY column

Lately I have seen a number of questions regarding incremental update of some sort of counter placed in a central table. The original posters will for some reason not use an IDENTITY column which has a minimum overhead and use of system resources. And today I saw this type of question again (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114970) and I started to think if there actually were an alternative to IDENTITY column for their cases. I found a solution. I can't tell if it's elegant or not, but it work 100%. If you are using SQL Server 2005 or later, you can stop reading here because Michael Valentine Jones has...

posted @ Friday, November 28, 2008 8:48 AM | Feedback (9) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Run jobs synchronously

If you use sp_start_job you have noticed that the code will continue ro run and the job you started is run asynchrously. What if you want to wait for the job to finished? Try this CREATE PROCEDURE dbo.usp_Start_And_Wait_For_Job (        @jobName SYSNAME ) AS   SET NOCOUNT ON   DECLARE       @jobID UNIQUEIDENTIFIER,        @maxID INT,        @status INT,        @rc INT   IF @jobName IS NULL       BEGIN             RAISERROR('Parameter @jobName have no value.', 16, 1)             RETURN -100       END   SELECT @jobID = job_id FROM   msdb..sysjobs WHERE name = @jobName   IF @@ERROR <> 0       BEGIN             RAISERROR('Error when returning jobID for job %s.', 18, 1, @jobName)             RETURN -110       END   IF @jobID IS NULL       BEGIN             RAISERROR('Job %s does not exist.', 16, 1, @jobName)             RETURN -120       END   SELECT @maxID...

posted @ Thursday, November 27, 2008 2:48 PM | Feedback (5) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

SQL Server 2008 with MERGE and triggers

I come across a question today if SQL Server 2008 MERGE command call table triggers one per hit, or one per statement as it normally does. The short conclusion is that the MERGE command splits the source data into three “streams” and internally executes INSERT, UPDATE and DELETE statements. According to Books Online, there is no sure way to guarantee the order of "streams" to execute, but it seems SQL Server favors INSERT / UPDATE / DELETE order.   CREATE TABLE tTemp               (                      i INT,                      j INT               )   INSERT tTemp        (               i,               j        ) SELECT 1, 0 UNION ALL SELECT 2, 0 UNION ALL SELECT 3, 0 UNION ALL SELECT...

posted @ Monday, November 24, 2008 2:38 PM | Feedback (3) | Filed Under [ SQL Server 2008 Administration ]

How to script out all your objects one per file

/*******************************************************************************  Initialize communication variables *******************************************************************************/ SET NOCOUNT ON DECLARE @pathProc VARCHAR(255),         @pathFunc VARCHAR(255),         @pathTrig VARCHAR(255),         @pathView VARCHAR(255),         @cmd NVARCHAR(4000),         @pathBase VARCHAR(256) SELECT  @pathBase = '\\Archive\Documents\Projects\Peso\Code\',         @pathProc = @pathBase + 'Stored Procedures\',         @pathFunc = @pathBase + 'Functions\',         @pathTrig = @pathBase + 'Triggers\',         @pathView = @pathBase + 'Views\' SET     @cmd = 'md "' + @pathProc + '"' EXEC    master..xp_cmdshell @cmd, no_output SET     @cmd = 'md "' + @pathFunc + '"' EXEC    master..xp_cmdshell @cmd, no_output SET     @cmd = 'md "' + @pathTrig + '"' EXEC    master..xp_cmdshell @cmd, no_output SET     @cmd = 'md "' + @pathView + '"' EXEC    master..xp_cmdshell @cmd, no_output /*******************************************************************************  Stage all existing relevant code *******************************************************************************/ CREATE TABLE  TempDB..CodeOut               (                      spID INT,                      [uID] INT,                      colID INT,                      codeText NTEXT,                      isProc TINYINT,                      isFunc TINYINT,                      isTrig...

posted @ Friday, October 24, 2008 2:04 PM | Feedback (10) | Filed Under [ Administration ]

Get the job name for current context

Today I had to write some code to dynamically get the job name currently running. DECLARE @SQL NVARCHAR(72),         @jobID UNIQUEIDENTIFIER,         @jobName SYSNAME SET     @SQL = 'SET @guid = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)' EXEC    sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @guid = @jobID OUT SELECT  @jobName = name FROM    msdb..sysjobs WHERE   job_id = @jobID

posted @ Monday, October 13, 2008 10:32 AM | Feedback (3) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Keep track of all your jobs schedules

This is a piece of code I use to create a resultset from and display in Outlook calendar. CREATE PROCEDURE dbo.uspGetScheduleTimes (        @startDate DATETIME,        @endDate DATETIME ) AS /*     This code is blogged here     http://weblogs.sqlteam.com/peterl/archive/2008/10/10/Keep-track-of-all-your-jobs-schedules.aspx */ SET NOCOUNT ON   -- Create a tally table. If you already have one of your own please use that instead. CREATE TABLE #tallyNumbers               (                      num SMALLINT PRIMARY KEY CLUSTERED               )   DECLARE       @index SMALLINT   SET    @index = 1   WHILE @index <= 8640        BEGIN               INSERT #tallyNumbers                      (                            num                      )               VALUES (                            @index                      )                 SET    @index = @index + 1        END   -- Create a staging table for jobschedules CREATE TABLE #jobSchedules               (                      rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,                      serverName SYSNAME NOT NULL,                     ...

posted @ Friday, October 10, 2008 5:07 PM | Feedback (6) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Excerpt from The Compression Session

This tuesday I had the opportunity to meet Kalen Delaney and hear her talk about the new compression algorithms in SQL Server 2008. For those of you that never have met Kalen, I can tell she is a sweet lady, knowledgable and interesting to listen to. With SQL Server 2005 SP2, the new VARDECIMAL datatype arrived, with a few stored procedures to calculate eventual savings. This turned out to be a subset for the ROW and PAGE compressions available in SQL Server 2008, Enterprise Edition. You can have ROW compression only if you want, but if you choose PAGE compression you...

posted @ Thursday, October 09, 2008 8:39 AM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms Administration ]

How to get IP address

DECLARE       @Interfaces TABLE        (               RowID INT IDENTITY(0, 1),               Interface CHAR(38),               IP VARCHAR(15)        )   INSERT @Interfaces        (               Interface        ) EXEC   master..xp_regenumkeys     N'HKEY_LOCAL_MACHINE',                            N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces'   DECLARE       @RowID INT,        @IP VARCHAR(15),        @Key NVARCHAR(200)   SELECT @RowID = MAX(RowID) FROM   @Interfaces   WHILE @RowID >= 0        BEGIN               SELECT @Key = N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces\' + Interface               FROM   @Interfaces               WHERE RowID = @RowID                 EXEC   master..xp_regread   N'HKEY_LOCAL_MACHINE',                                          @Key,                                          N'DhcpIPAddress',                                          @IP OUTPUT                 IF @IP <> '0.0.0.0'                      UPDATE @Interfaces                      SET    IP = @IP                      WHERE RowID = @RowID                 SET    @RowID = @RowID - 1        END                                   SELECT IP FROM   @Interfaces WHERE IP IS NOT NULL

posted @ Wednesday, July 16, 2008 1:18 PM | Feedback (6) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Firewall strategy

When Windows Server 2008 is more common, you will be surprised how the Firewall is blocking all versions of SQL Server when upgrading the OS. Here are some links to remedy the problems http://msdn.microsoft.com/en-us/library/cc646023(SQL.100).aspx http://technet.microsoft.com/en-us/network/bb545423.aspx

posted @ Monday, July 14, 2008 3:48 PM | Feedback (0) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

How to get authentication mode in SQL Server

CREATE FUNCTION dbo.fnGetSQLServerAuthenticationMode ( ) RETURNS INT AS   BEGIN       DECLARE @InstanceName NVARCHAR(1000),             @Key NVARCHAR(4000),             @LoginMode INT         EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',                         N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',                         N'MSSQLSERVER',                         @InstanceName OUTPUT         IF @@ERROR <> 0 OR @InstanceName IS NULL             RETURN NULL         SET   @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'         EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',                         @Key,                         N'LoginMode',                         @LoginMode OUTPUT         RETURN @LoginMode END

posted @ Thursday, June 19, 2008 5:10 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

How to change authentication mode in SQL Server

CREATE PROCEDURE dbo.uspSetSQLServerAuthenticationMode (        @MixedMode BIT ) AS   SET NOCOUNT ON   DECLARE @InstanceName NVARCHAR(1000),        @Key NVARCHAR(4000),        @NewLoginMode INT,        @OldLoginMode INT   EXEC master..xp_regread    N'HKEY_LOCAL_MACHINE',                      N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',                      N'MSSQLSERVER',                      @InstanceName OUTPUT   IF @@ERROR <> 0 OR @InstanceName IS NULL        BEGIN               RAISERROR('Could not read SQL Server instance name.', 18, 1)               RETURN -100        END   SET    @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'   EXEC master..xp_regread    N'HKEY_LOCAL_MACHINE',                      @Key,                      N'LoginMode',                      @OldLoginMode OUTPUT   IF @@ERROR <> 0        BEGIN               RAISERROR('Could not read login mode for SQL Server instance %s.', 18, 1, @InstanceName)               RETURN -110        END   IF @MixedMode IS NULL        BEGIN               RAISERROR('No change to authentication mode was made. Login mode is %d.', 10, 1, @OldLoginMode)               RETURN -120        END   IF...

posted @ Thursday, June 19, 2008 5:04 PM | Feedback (8) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Some SQL Server network properties

DECLARE       @Stage TABLE               (                      RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,                      Data VARCHAR(90),                      Section INT               )   INSERT @Stage               (                      Data               ) EXEC   xp_cmdshell 'ipconfig /all'   DECLARE       @Section INT   SET    @Section = 0   UPDATE @Stage SET    @Section = Section = CASE WHEN ASCII(LEFT(Data, 1)) > 32 THEN @Section + 1 ELSE @Section END   SELECT        MAX(CASE WHEN x.minRowID IS NULL THEN NULL ELSE s.Data END) AS Header,               MAX(CASE WHEN s.Data LIKE '%Host Name%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS HostName,               MAX(CASE WHEN s.Data LIKE '%Media State%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS MediaState,               MAX(CASE WHEN s.Data LIKE '%Description%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END)...

posted @ Thursday, June 19, 2008 3:57 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Horizontal partitioning, Enterprise style

CREATE PARTITION FUNCTION   pfOrderDate ( DATETIME ) AS RANGE RIGHT FOR VALUES  ( '20000101', '20010101', '20020101' ) GO   CREATE PARTITION SCHEME    psYak AS PARTITION               pfOrderDate ALL TO                     ([PRIMARY]) GO   CREATE TABLE Orders               (                      OrderID INT NOT NULL,                      CustomerID VARCHAR(15) NOT NULL,                      OrderDate DATETIME NOT NULL               ) ON            psYak(OrderDate) GO   CREATE CLUSTERED INDEX      IX_OrderID ON                          Orders ( OrderID ) CREATE NONCLUSTERED INDEX  IX_OrderDate ON                         Orders ( OrderDate ) INCLUDE                    ( OrderID, CustomerID ) GO   INSERT Orders        (               OrderID,               CustomerID,               OrderDate        ) SELECT 1, 'Peso', '20011225' UNION ALL SELECT 2, 'Jennie', '20020314'   SELECT OrderID,        OrderDate FROM   Orders WHERE  OrderDate = '20011225'   SELECT OrderID,        OrderDate FROM   Orders WHERE  OrderID = 1   DROP TABLE                 Orders DROP PARTITION SCHEME      psYak DROP PARTITION FUNCTION    pfOrderDate  

posted @ Thursday, June 12, 2008 4:18 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Administration SQL Server 2005 ]

Bug found in SQL Server 2005 sp2

For this to work, disconnect and reconnect the current query window in SSMS and then copy, paste and run the code below. Books Online says IDENT_CURRENT "Returns the last identity value generated for a specified table or view in any session and any scope.". So how can the IDENT_CURRENT() return 1 for a newly created table with no inserted records? @@IDENTITY and SCOPE_IDENTITY works as expected. CREATE TABLE      #Temp                   (                         RowID INT IDENTITY(1, 1),                         theValue INT                   )   SELECT      * FROM       #Temp   SELECT      @@IDENTITY,             IDENT_CURRENT('#Temp'),             SCOPE_IDENTITY()   INSERT      #Temp SELECT      99   SELECT      * FROM       #Temp   SELECT      @@IDENTITY,             IDENT_CURRENT('#Temp'),             SCOPE_IDENTITY()   INSERT      #Temp SELECT      123456   SELECT      * FROM       #Temp   SELECT      @@IDENTITY,      ...

posted @ Thursday, June 05, 2008 10:30 AM | Feedback (1) | Filed Under [ Administration SQL Server 2005 SQL Server 2000 ]

SQL Injection

Every now and then I see sites where commands are concatenated and sent to database server. The author must really trust the user inputs! For every system built this way, you can expect at least one attack with SQL injection. In some cases you might not be aware of the attack, and sometimes you are aware. Here is an example of a "friendly" attack, that just promotes a site and when you click the link you execute a javascript who knows do what? In this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102737 and this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101673 there are examples of SQL injection attacks. As I wrote in the first topic, "What if the...

posted @ Wednesday, May 14, 2008 11:14 AM | Feedback (5) | Filed Under [ Administration ]

Index pages

SQL Server 2005 introduced the new DMV views. They are great and a real improvement to debug and optimize queries. I find sys.dm_db_index_physical_stats very useful and often write this type of code  SELECT  index_id,         page_count FROM    sys.dm_db_index_physical_stats(DB_ID('MyDB'), OBJECT_ID('MyTable'), NULL, NULL, NULL)   to find out if the query optimizer has choosen the "right" index for the query.   This can be done in SQL Server 2000 too!   Use the DBCC SHOWCONTIG command. Maybe most of you have only used this with tables too see table fragmentation? Well, you can use it for indexes too.   Use   DBCC SHOWCONTIG ('MyDB..MyTable') WITH ALL_INDEXES, TABLERESULTS

posted @ Wednesday, April 30, 2008 9:38 AM | Feedback (0) | Filed Under [ Administration SQL Server 2000 ]

SP3 for Microsoft SQL Server 2005

http://blogs.technet.com/dataplatforminsider/archive/2008/04/15/sql-server-2005-sp3-coming-soon.aspx  

posted @ Tuesday, April 22, 2008 4:07 PM | Feedback (0) | Filed Under [ Administration SQL Server 2005 ]

Cumulative update package 7 for SQL Server 2005 Service Pack 2 available now

Find it here here http://support.microsoft.com/default.aspx/kb/949095/

posted @ Thursday, April 17, 2008 10:18 PM | Feedback (0) | Filed Under [ Administration SQL Server 2005 ]

Cumulative update package 7 for SQL Server 2005 Service Pack 2

Soon available here http://support.microsoft.com/default.aspx/kb/949095/

posted @ Friday, March 28, 2008 8:14 AM | Feedback (1) | Filed Under [ Administration SQL Server 2005 ]

Search all code for specific keyword

This is an updated version for SQL 2005 and later to search all code for a specific keyword SELECT p.RoutineName, 'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec] FROM ( SELECT OBJECT_NAME(so.ID) AS RoutineName, (SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body FROM SYSOBJECTS AS so WHERE so.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X') ) AS p WHERE p.Body LIKE '%YourKeyWordHere%' The types are C = CHECK constraint D = Default or DEFAULT constraint FN = Scalar function IF = In-lined table-function...

posted @ Friday, March 14, 2008 2:05 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 ]

Change schema for all tables

I just helped a guy here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98346 with schema and thought that someone could benefit from this code exec     sp_MSforeachtable "PRINT '? modify'; ALTER SCHEMA new_schema TRANSFER ?; IF @@ERROR = 0 PRINT '? modified'; PRINT ''"

posted @ Wednesday, March 05, 2008 1:31 AM | Feedback (1) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Finding table reference levels and simulating cascading deletes

I worked with this topic recent weekend and posted the final functions here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454   The general idea is to have a generic purge functionality.

posted @ Saturday, February 16, 2008 7:32 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Curiosity found, the wrap

After a good nights sleep when almost all pieces fit together here weblogs.sqlteam.com/peterl/archive/2008/02/06/Curiosity-found.aspx I realized this morning that this behaviour also explains why there are gaps in identity sequences when inserting a record that violates a contraint. It seems that identity values are assigned to complete insert-set before checking for constraints such us unique indexes. DECLARE @Items TABLE (i INT IDENTITY(1, 1), j INT PRIMARY KEY) INSERT  @Items SELECT  1 UNION ALL SELECT  2 SELECT  * FROM    @Items INSERT  @Items SELECT  1 INSERT  @Items SELECT  2 INSERT  @Items SELECT  3 SELECT  * FROM    @Items

posted @ Thursday, February 07, 2008 9:15 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Getting errors when working with Excel and SQL Server

In this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 I gave a suggestion how to get a more detailed error message when working with OPENROWSET. But the same thinking is applicable for LINKED SERVER and other "outer referenced" data retreival methods. The OPENROWSET syntax with Jet provider does not give you full error description when problem occurs, such as permission errors. -- Using this code for a file with no appropriate permissions throws a general error SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 5.0;HDR=No;IMEX=0;Database=\\datastorage\excel\book2.xls', 'select * from [Sheet1$a1:q50]') If you have some error and you don't understand why, try using MSDASQL provider temporarily. SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\datastorage\excel\book2.xls', ...

posted @ Wednesday, October 24, 2007 8:01 AM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

New SQL Server 2005 OUTPUT operator

  Let’s play with the new OUTPUT operator! -- Setup TableA & TableB CREATE TABLE          #TableA                       (                                  i INT                       )   CREATE TABLE          #TableB                       (                                  i INT                       )   CREATE TABLE          #TableC                       (                                  iOld INT,                                  iNew INT                       )   -- Check TableA and TableB SELECT 'A' AS [Table], * FROM #TableA UNION ALL SELECT 'B' AS [Table], * FROM #TableB   -- Insert into TableA INSERT     #TableA OUTPUT     inserted.i INTO       #TableB SELECT     1 UNION ALL SELECT     2 UNION ALL SELECT     3   -- Check TableA and TableB SELECT 'A' AS [Table], * FROM #TableA UNION ALL SELECT 'B' AS [Table], * FROM #TableB   -- Delete from TableA DELETE     a OUTPUT     10 * deleted.i + 49 INTO       #TableB FROM       #TableA AS a WHERE      i = 2   -- Check TableA and TableB SELECT 'A'...

posted @ Wednesday, October 03, 2007 6:44 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 ]

SQL Server 2005 too smart?

I was involved in a discussion today about the ISNUMERIC() function Someone proposed a nested solution like this SELECT Column_Name from    (               SELECT Column_Name                FROM    (                                select 'staff' as Column_Name union all                                select '234000' as Column_Name union all                                select '12d1' as Column_Name union all                                select '45e0' as Column_Name union all                                select '$123.45' as Column_Name union all                                select '$12,345' as Column_Name                         ) as Table_Name                WHERE   ISNUMERIC(Column_Name) = 1         ) as d where    CAST(Column_Name AS INT) <= 1000000 But is does not work. I suggested an alternative method that seems to work. SELECT d.Column_Name from    (                SELECT x.Column_Name                FROM    (                                select 'staff' as Column_Name union all                                select '234000' union all                                select '12d1' union...

posted @ Thursday, September 27, 2007 3:53 PM | Feedback (6) | Filed Under [ Algorithms Administration ]

Insert binary data like images into SQL Server without front-end application

I came across this solution for some months ago on another forum. It is very handy, so I thought i should share it. CREATE TABLE myTable(Document varbinary(max)) INSERT INTO myTable(Document) SELECT * FROM OPENROWSET(BULK N'C:\Image1.jpg', SINGLE_BLOB)

posted @ Wednesday, September 26, 2007 12:35 PM | Feedback (82) | Filed Under [ Administration ]

Powered by:
Powered By Subtext Powered By ASP.NET