Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Configuration Manager (sqlserverManager11.msc) Hidden after Windows 8.1 Upgrade

You may have noticed, like I did, that after upgrading-in-place from Windows 8 Enterprise to Windows 8.1 Enterprise, my local installation of SQL 2012 SP1 no longer listed SQL Server Configuration Manager under the "SQL Server 2012" list in the Start applications screen.

A picture is worth a thousand words,…

Read more

2 comments, 448 reads

Posted in SQL Tact on 22 October 2013

"SQL Server: Bolts to Buzzwords" presentation for the Hammond .NET User Group at Southeastern Louisiana University

One of my favorite groups to speak for (aside from the Baton Rouge SQL Server User Group) is the Hammond .NET User Group at Southeastern Louisiana University in Hammond, LA. A mostly undergraduate crowd of enthusiastic future developers (and perhaps some developers-turned-DBAs, like myself) make for a fun audience that…

Read more

0 comments, 104 reads

Posted in SQL Tact on 17 October 2013

Houston Tech Fest 2013 - SQL Admin Best Practices with DMV's

Here is the downloadable file with all my sample scripts and slidedeck from my SQL Admin Best Practices with DMV's presentation from the 2000+ person Houston Tech Fest 2013 at the Reliant Center in Houston!

Thanks for attending! If you attended my talk, shoot me some feedback here: http://speakerrate.com/william.assaf

Read more

0 comments, 161 reads

Posted in SQL Tact on 28 September 2013

Replicated Stored Procedure Execution

One feature that many of my clients are unaware of is that the batched execution of a stored procedure can be replicated under certain circumstances. This can result in significant improvement in performance for stored procedures on replicated databases.

This MSDN article does a good job of breaking down the…

Read more

0 comments, 464 reads

Posted in SQL Tact on 4 September 2013

Actual Emails: TL;DR: Stop using varchar(max)

Wrote this email recently to a crew of developers who were shooting themselves in the foot with a database rich in varchar(max) data types.

Hey folks-
TL;DR: Stop using varchar(max). We’re not storing books.
We need to review and avoid the varchar(max) data type in our tables. Here’s…

Read more

4 comments, 2,508 reads

Posted in SQL Tact on 3 September 2013

Pointers For SQL Saturday Attendance

I recently got an email from another SQL Saturday organizer asking for "pointers for getting more attendees" to their upcoming event. Having just topped 750 preregistered and 560 in the door for SQL Saturday Baton Rouge 2013, I was honored to be asked and to reply:
Good luck on your…

Read more

0 comments, 178 reads

Posted in SQL Tact on 20 August 2013

Cannot find NT Service\MSSQL$SQL2012?

If you cannot find the virtual account used by the default settings installation of your SQL 2012 instance (in this case running on Windows 7), try searching for:
nt service\mssql

























You must have the "Built-in security principals" object type selected, and you must be searching on your local machine name…

Read more

0 comments, 251 reads

Posted in SQL Tact on 16 August 2013

Distribution setup SQL Server Agent error: "RegCreateKeyEx() returned error 5, 'Access is denied.'"

In the Configure Distribution Wizard, the step "Configuring SQL Server Agent to start automatically" errors with the following text:
TITLE: Configure Distribution Wizard
------------------------------
An error occurred configuring SQL Server Agent.
------------------------------
ADDITIONAL INFORMATION:
RegCreateKeyEx() returned error 5, 'Access is denied.' (Microsoft SQL Server, Error: 22002)


This is a very…

Read more

0 comments, 367 reads

Posted in SQL Tact on 15 August 2013

Baton Rouge Startup Weekend

I'm spreading the word out about an event this weekend called Startup Weekend. It's an event for developers, designers, and entrepreneurs to come together and work on the ideas they have. Startup weekend is a lot crazy ideas, a little bit hackathon, and a little bit business venture incubation…

Read more

0 comments, 114 reads

Posted in SQL Tact on 13 August 2013

Recap from SQL Saturday Baton Rouge 2013

As the head of the planning committee for SQL Saturday Baton Rouge this year, I had the distinct pleasure of welcoming several hundred colleagues from around the world to Baton Rouge on August 3 for a day of free training, networking, professional development and fun.

The day-ending raffle in the…

Read more

0 comments, 182 reads

Posted in SQL Tact on 4 August 2013

SQL Saturday Baton Rouge 2013

SQL Saturday is a global event to bring Information Technology speakers and professionals together for a community-driven, community-attended free day of technical training. The Baton Rouge SQL Saturday 2013 event is sponsored by the Baton Rouge SQL Server and .net User Groups, and will be our fifth annual event on…

Read more

0 comments, 176 reads

Posted in SQL Tact on 11 June 2013

SQL Saturday Baton Rouge Call for Speakers closes June 6!

SQL Saturday Baton Rouge Call for Speakers is open through June 6 (closes June 7 at midnight).

This year's pre-conference banquet at Boudreaux and Thibodeaux's in energetic downtown Baton Rouge is going to be one to remember - our banquet is privately catered at 6pm and the house opens to…

Read more

0 comments, 184 reads

Posted in SQL Tact on 4 June 2013

Use the Default System Trace to Find Autogrowth Events

The SQL Server Management Studio built-in standard report for "Disk Usage" is very handy to view the performance-sucking autogrowth events for a database... until you want to see if autogrowth events have been occurring in any database on the server.

Autogrowth events are very problematic and can cause sudden, unpredictable…

Read more

0 comments, 319 reads

Posted in SQL Tact on 3 May 2013

Hunt Down Tables Without Clustered Indexes

This one's a gimme, but an underrated utility script when I enter a new environment and look for low-hanging fruit for easy improvements.

You'd be surprised how many vendors ship applications without a single index, much less properly aligned clustered indexes.

Heaps are bad.  You can't defragment a heap by…

Read more

8 comments, 1,536 reads

Posted in SQL Tact on 3 May 2013

TRY_CONVERT broken by WITH SCHEMABINDING in SQL Server 2012 Data Tools build

Here's a bug you might find if attempting to schemabind an object that uses the new TRY_CONVERT function.

To reproduce, run the following code in a SSDT database solution:

CREATE VIEW [dbo].[aview] WITH SCHEMABINDING 
AS 
SELECT id = TRY_CONVERT(int, id) 
FROM dbo.arealtable

The database project should build with this error:

Read more

0 comments, 353 reads

Posted in SQL Tact on 21 March 2013

Conversation History: The Continuing Case Against GUIDs

We had a cooperative relational database design exercise at the office last week as part of our regular department "Lunch and Learn" series, and inevitably one topic that came up is the use of GUIDs as the unique key for a table.

And before long, I had been goaded onto…

Read more

0 comments, 754 reads

Posted in SQL Tact on 8 March 2013

YYYYMM Math

I had a project recently where I had to do math on a number that represented a date, YYYYMM.

For example, February 2013 would be stored as an integer, 201302.

As an aside, YYYYMM makes WAY more sense than ever storing anything MMYYYY.  Why?  For one, it sorts correctly.  And…

Read more

0 comments, 220 reads

Posted in SQL Tact on 1 March 2013

"An item with the same key has already been added."

In SQL Server Reporting Services, when adding a new dataset to a report, you may see an error that looks like this:

"An item with the same key has already been added."




















Based on some quick googling, it looks like there are several potential causes for the error, but here's…

Read more

0 comments, 591 reads

Posted in SQL Tact on 28 February 2013

Script Out Foreign Keys With Multiple Keys

It's easy enough to use sys.foreign_keys and sys.foreign_key_columns to identify foreign keys.  But what if you want to script out your foreign keys (and only your foreign keys)... that have compound primary keys?

For example,
--Script 1
create table dbo.fktable1(
  id1 int not null
, id2 int not null
, id3 int…

Read more

0 comments, 381 reads

Posted in SQL Tact on 18 February 2013

Using Foreign Keys to Determine Table Insertion Order

Here's a script to determine, based on your database's foreign key relationships, what the insertion order would be for, say, a testing dataset.

with cteFK (pktable, fktable) as ( 
       select              
            pktable = o1.name 
       ,    fktable = isnull(o2.name, '')        
       from sysobjects o1       
       left join sysforeignkeys fk on o1.id = fk.fkeyid        
       left…

Read more

0 comments, 309 reads

Posted in SQL Tact on 17 February 2013

Newer posts

Older posts