Incorrect syntax near GO and why one cannot use GO when executing dynamic sql

When dynamic sql is executed using sp_executesql,

GO

keyword cannot be used because it will produce and error "Incorrect syntax near GO". Also that SQL Server will be looking for a carriage return CHAR(13) and a line feed CHAR(10)

Example:

-- declare some variables
DECLARE @SQL VARCHAR(100)
DECLARE @dbName VARCHAR(100)-- Get the current or target database name
SET @dbName = DB_NAME()-- Build the script
SET @sql = 'USE [' + @dbName + ']' + CHAR(13) + CHAR(10)
SET @sql = @sql + 'GO' + CHAR(13) + CHAR(10)-- Print the command
PRINT (@sql)

The code above will work for printing out the statement to the screen, but it will not execute if you add EXEC sp_executesql @sql

The solution would be to remove the

GO

keyword altogether. The code below will execute without an issue:

-- declare some variables
DECLARE @SQL VARCHAR(100)
DECLARE @dbName VARCHAR(100)-- Get the current or target database name
SET @dbName = DB_NAME()-- Build the script
SET @sql = 'USE [' + @dbName + ']' + CHAR(13) + CHAR(10)
SET @sql = @sql + CHAR(13) + CHAR(10)-- Print the command
PRINT (@sql)
EXEC sp_executesql @sql

The only way to get the dynamic sql to work with

GO

is to either copy the output to another window and run from there or write it to external file and then execute from your code using

exec master..xp_cmdshell 'osql -E -i C:\Scripts\test.sql'

Page count and fragmentation

I was working on writing a maintenance script for SQL Server that would perform a rebuild on indexes with fragmentation of 30% or above and re-organize indexes with fragmentation of below 30% and found that page count should also be taken into an account. if a page count is less than 1000 then rebuilding an index will not only have no effect of fragmentation but also rebuild procedure will be more costly than fragmentation of an index with 1000 pages or less.
As a result I changed my query slightly:

SELECT OBJECT_NAME(ps.object_id) As TableName, i.name As IndexName,ps.index_type_desc As IndexType, ps.index_depth, ps.index_level, ps.avg_fragmentation_in_percent, ps.fragment_count, ps.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID('DBName'), null, null, null, 'LIMITED') as ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id 
WHERE NOT i.name IS NULL and ps.avg_fragmentation_in_percent >= 30 AND ps.page_count > 1000

SQL to get the names of the tables referenced in stored procedures

below is a simple sql to retrieve a list of all the stored procedures and the tables referenced in those procedures:

SELECT DISTINCT a.name AS [Procedure Name], b.name AS [Table Name]
FROM sysobjects as a
INNER JOIN sysdepends d ON a.id=d.id
INNER JOIN sysobjects b ON d.depid=b.id
WHERE a.xtype = 'P'
ORDER BY a.name, b.name

Query for cleaning up the history records except the last one for each item

The query below will delete all the history records for each item, except one latest record

DELETE FROM Table1
WHERE itemHistoryId NOT IN 
(SELECT t1.itemHistoryId
 FROM Table1 as t1
WHERE t1.historyDateTime = (SELECT MAX(historyDateTime) FROM Table1 WHERE ItemID=t1.ItemID)) 

The subquery

SELECT t1.itemHistoryId
 FROM Table1 as t1
WHERE t1.historyDateTime = (SELECT MAX(historyDateTime) FROM Table1 WHERE ItemID=t1.ItemID)

will return all the records with the latest history date, one per each itemID (i.e. records to be kept), and the main query will pull all the records that are not included in this subquery and delete them

Login failed for user [name] error

So you have been moving database from one server or instance to another, you backed up and restored the database, scripted all logins, did everything in the book, and suddently, when loging in with sql server login, that worked so well before, you get "Login failed for user [name]" error.

First thing to check is whether your SQL Server is configured to use Mixed mode or Windows only authentication. because if it is configured to use Windows only, any sql login will be rejected, no matter what type of permissions it has.

Right-click on the server name, go to Properties, select Security and make sure "SQL Server and Windows Authentication mode" is selected.

SQL Server 2008: Owners vs. Schemas

The other day someone asked me what is dbo, they had trouble differentiating between dbo as object owner and dbo as a schema. That conversation was what inspired this post.

Every object created in SQL Server must have an owner and most of the time the owner is dbo, which is the database owner. One can easily determine the owner of a particular database object by looking at the fully qualified name which is using the following convention:

Server.Database.ObjectOwner.DatabaseObject

Example: Server03.Northwind.dbo.Customers, where dbo is the owner of table Customers that belongs to Northwind database that resides on Server03.

Object gets its owner based on who created the object. By default the user account that creates the object will also own the object and only users in db_owner role can create objects owned by dbo. Someone in db_owner role can create an object owned by any user in the database. Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role):

•Belong to the user creating the object, not dbo.

•Are qualified with the name of the user who created the object.

A schema is a named container for database objects, which allows you to group objects into separate namespaces. It is basically a way to logically group objects such as tables, views, stored procedures etc.
You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.
The dbo schema is the default schema for a newly created database. The dbo schema is owned by the dbo user account. By default, users created with the CREATE USER Transact-SQL command have dbo as their default schema.

Users who are assigned the dbo schema do not inherit the permissions of the dbo user account. No permissions are inherited from a schema by users; schema permissions are inherited by the database objects contained in the schema.

Before SQL Server 2005, schemas used to be equivalent to database users. Now each schema is a distinct namespace that exists independently of the user who created it. It can be owned by any user and its ownership is transferable.