Windows 7 User Access Control Default Setting Blocks Unsupported Applications

While testing a backup application (that I wrote in .NET) on a 64-bit Windows 7 VM today, the initial registry reads failed (although, weirdly, the writes succeeded). This app has worked well on Windows XP in the past. After a bit of Googling, I found the solution. The problem was that the default setting for the UAC (User Access Control) feature had been denying the app permission to read the registry settings. The UAC should normally notify the user when a program (or user, depending on the UAC setting) tries to make certain changes to Windows. My application, however, was not triggering a UAC notification, but its registry reads were denied nevertheless. Setting UAC to Never notify fixed it. The default UAC setting on Windows 7 is supposed to notify the user when programs attempt to make changes to Windows. The reason that a notification was not raised was because my application is not certified for Windows 7 and/or does not support UAC.

To change the UAC setting, click the Start button type ‘UAC’ in the Search box. Open the UAC and change to the desired setting.

SSPI stands for…?

The acronym SSPI stands for Microsoft’s Security Support Provider Interface.

http://technet.microsoft.com/en-us/library/bb742535.aspx

BRAD MCGEHEE ON INDEXES

Here’s a link to a great article on index implementation by an expert DBA, Brad McGehee.

http://www.sql-server-performance.com/tips/composite_indexes_p1.aspx

SQL Server 2008 Prevent Saving Changes Setting

SQL Server 2008 has a setting that PREVENTS saving certain table schema changes (such as changing column order) – and this setting is ENABLED by default.

When I first encountered the effects of this setting, I was attempting to rearrange the column order of a table. Using the GUI, I went to the table design view and moved one column in front of another.

When I hit ‘Save’, I received this message:

When a table’s schema is changed, the process that SQL Server performs drops the table and recreates it. The new setting that prevents this is called ‘Prevent saving changes that require table re-creation‘, and it’s easy enough to turn off. I’m sure it has been put in place as a safeguard to prevent unintended table changes. To turn off the setting, go to (on the menu bar) Tools/Options/Designers/Table and Database Designers, then under the Table Options section, uncheck Prevent saving changes….

Schema changes will not be blocked.  You’re welcome.

HOW TO GET CONDITIONAL SUM AGGREGATES IN A RECORDSET USING THE CASE STATEMENT

Today I was presented with a request to find a SQL Server-compatible substitute for the IIF function that MS Access uses. The IIF function is a conditional function; it evaluates a condition (test for TRUE or FALSE) and returns one configurable value or another depending on the result of the evaluation. SQL Server does not have the IIF function, but it can easily perform the same operations using a CASE statement. The scenario I was shown today used the IIF function wrapped in a SUM function, effectively adding a ‘1’ for each record having a certain value in the evaluated field.

To demonstrate how SQL Server can solve this problem, I’m going to use the AdventureWorks database and the Production.Product table. This table has a Color field. I want to find (in one query) the number of records for each respective color (or no color: NULL). Here’s a quick shot of part of the table:

I can see that there are the colors Black and Silver in the table, along with many NULLs. To get the count of records having the color Black, I can run the following:

That gives me a count of 93 records. But how many different colors are there? To find out, I’ll get all the DISTINCT Color values:

NOW I can formulate my entire query accurately, getting a total count, NULLS count, and counts for every color:

Looks good!

Access IIF statement
CASE statement

SHORTCUT TO START SQL SERVER MANAGEMENT STUDIO (SSMS)

Instead of mouse-clicking through Start/Programs/Microsoft SQL Server 2005/SQL Server Management Studio Express, just open up the Run menu by clicking Start/Run or using the keyboard shortcut Windows Key + R, then type ssmsee (for Express Edition) or ssms (for full versions).

That was easy!

There are also options for the command, such as -nosplash, which will start Management Studio without the splash screen, or -E, which will start SSMS and log you in automatically with Windows Authentication (albeit without the Object Explorer connection).

More info and options here: http://msdn.microsoft.com/en-us/library/ms162825.aspx

STORED PROCS: WHEN TO USE EXECUTE COMMAND?

While running stored procedures in SQL Server, you may have noticed that it seems to make no difference whether or not you use the EXECUTE (or EXEC) command before the procedure name. When should you use EXEC – if at all? The Books Online article for Executing Stored Procedures says that EXEC is only needed if more than one stored procedure is run in a batch. This means I can execute one sproc call with or without EXEC:

USE AdventureWorks
GO
sp_helpfile

or:

USE AdventureWorks
GO
EXEC sp_helpfile

But if I need to run more than one sproc at the same time (in a batch), I have to use:

USE AdventureWorks
GO
EXEC sp_spaceused ‘Person.Contact’, true
EXEC sp_helpfile
GO

or just:

EXEC sp_spaceused ‘Person.Contact’, true
EXEC sp_helpfile

Both statements return the dual result set below:

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

FIND ROWCOUNT FOR ALL TABLES

Need to find out which of your tables have the highest rowcounts? Pinal Dave submitted a solution on his blog (sqlauthority.com). I’ve modified it to include only user tables:

USE Northwind
GO
SELECT OBJECT_NAME(st.object_id) TableName, st.row_count
FROM sys.dm_db_partition_stats st
join sys.objects ob
on ob.object_id = st.object_id
WHERE st.index_id < 2
and ob.type = ‘U’
ORDER BY st.row_count DESC>

The above script works for SQL Server 2005. For SQL Server 2000, Afonso Mata posted compatible code on Pinal’s blog that I’ve also drawn from. Try:

USE Northwind
GO
SELECT OBJECT_NAME(si.id) TableName, si.rowcnt
FROM sysindexes si
join sysobjects ob
on ob.id = si.id
WHERE OBJECTPROPERTY(si.id,’isUserTable’)=1 AND si.indid < 2
and ob.type = ‘U’
ORDER BY si.rowcnt DESC

http://blog.sqlauthority.com/2009/01/13/sql-server-find-row-count-in-table-find-largest-table-in-database-t-sql/#comment-62037

Randomizing Result Sets with NEWID Article

My second article is published on SQLServerCentral.com: ‘Randomizing Result Sets with NEWID’.  Check it out here!

RANDOMIZING RESULTS WITH NEWID

Obtaining a random selection from SQL Server is easy using the NEWID system function. NEWID generates a globally unique identifier (GUID) that can be used for many purposes. Globally unique means that NEWID’s result is virtually guaranteed to be unique across the system (more about this later).

Let’s picture a hypothetical scenario in which you have a monthly lottery at your company to reward customers placing the largest number of orders. You want to select one customer – a random customer from the top 10 customers that have placed the largest volume of orders for the month. This random customer will be given a 5% discount on all orders for the entire month following.

First, let’s find our top 10 ordering customers for the month:

USE Northwind
GO
SELECT TOP 10 COUNT(orderid) AS order_count, customerid
FROM orders
WHERE orderdate BETWEEN ‘1998-02-01′ AND ‘1998-03-01′
GROUP BY customerid
ORDER BY 1 DESC

We have a result set that shows ties for some customers, emphasizing the need to use a random selection in this scenario. We can now use a nested SELECT statement that uses the NEWID function to randomly select 1 customer from our group of top 10 orderers, calculating the date range:

USE Northwind
GO
SELECT TOP 1 customerid, order_count
FROM
(
SELECT TOP 10 COUNT(orderid) AS order_count, customerid
FROM orders
WHERE orderdate BETWEEN ‘1998-02-01′ AND ‘1998-03-01′
GROUP BY customerid
ORDER BY 1 DESC
)x
ORDER BY NEWID()

Let’s run it again to be sure we are getting a random selection:

As expected, it returns a random customerid, along with that customer’s order count for the month. If we run the query again, a random selection is generated every time.
So what does NEWID return exactly? NEWID returns a GUID – a globally unique identifier. This is a value that is actually of SQL Server data type uniqueidentifier, and is a long hexadecimal string separated by dashes:

SELECT NEWID()

NEWID is not the only function that will generate a random number – we also have RAND. So why not use the RAND function to solve our previous scenario? At first glance, RAND may appear to offer the same randomizing operations as NEWID. Let’s try using RAND in place of NEWID:

USE Northwind
GO
SELECT TOP 1 customerid, order_count
FROM
(
SELECT TOP 10 COUNT(orderid) AS order_count, customerid
FROM orders
WHERE orderdate BETWEEN ‘1998-02-01′ AND ‘1998-03-01′
GROUP BY customerid
ORDER BY 1 DESC
)x
ORDER BY RAND()

Running the query again returns the exact same results:

Why is this? Because the RAND function does return a random number for the current session, but will always return the exact same number when run multiple times in the same query. Let’s test this by returning the all of the top customers alongside the value of RAND:

SELECT RAND(), customerid, order_count
FROM
(
SELECT TOP 10 COUNT(orderid) AS order_count, customerid
FROM orders
WHERE orderdate BETWEEN ‘1998-02-01′ AND ‘1998-03-01′
GROUP BY customerid
ORDER BY 1 DESC
)x
ORDER BY RAND()

We see that RAND returns the same value for each record. Contrast this to NEWID’s return values:

SELECT NEWID(), customerid, order_count
FROM
(
SELECT TOP 10 COUNT(orderid) AS order_count, customerid
FROM orders
WHERE orderdate BETWEEN ‘1998-02-01′ AND ‘1998-03-01′
GROUP BY customerid
ORDER BY 1 DESC
)x
ORDER BY NEWID()

NEWID’s value is different for each record.

The NEWID function can be also used to generate primary key values, or values for any numeric field requiring a unique id.
How unique is a GUID? Terms like ‘Globally Unique,’ or ‘Univerally Unique’ are invoked when GUIDs are described, implying that a generated GUID (NEWID, UUID, etc.) will be unique on a worldwide scale – and this is usually the case, but it’s not guaranteed. However, the number of GUIDs that are available is so large that the chance of encountering the same GUID is remotely small – the number of GUIDs is 2 to the power of 128 – that’s 340,282,366,920,938,463,463,374,607,431,768,211,456!

http://en.wikipedia.org/wiki/Globally_Unique_Identifier

http://msdn.microsoft.com/en-us/library/aa276822(SQL.80).aspx

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: