Unable to Restore SQL Server 2000 Backup on SQL Server 2012?

If you are trying to restore SQL Server 2000 backup on SQL Server 2008 you would be facing error like:

Msg 3169, Level 16, State 1, Line 1

The database was backed up on a server running version 8.00.2055. That version is incompatible with this server, which is running version 11.00.2100. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

Restoring SQL Server 2000 backup to SQL Server 2012 is not supported.

Why?
According to MSDN In SQL Server 2012, you can restore a user database from a database backup that was created by using SQL Server 2005 or a later version.

How to restore then?
Fix/resolution:
Use SQL Server 2008 R2 as an intermediate source.

Steps:
1. First restore the SQL Server 2000 backup to SQL Server 2008 r2.
2. Then backup the database from SQL Server 2008 r2 and restore it on SQL Server 2012.

If you do not have SQL Server 2008 r2, You can download a trial version of SQL Server 2008 r2 from the below links.
http://msdn.microsoft.com/en-us/evalcenter/ff459612
http://www.microsoft.com/sqlserver/en/us/editions/previous-versions.aspx

Alternatively you can use SQL Server 2005 (or) SQL Server 2008 also as an intermediate source.

Following the above steps should help solve the problem.

Also verify the database backup on each target server before restoring: Verify SQL Server Database Backup

If you are sharing single database server with multiple users you might many times end up your process being killed and an error message shown below comes up.

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)

You might be interested in policing as to who killed your process?
Was it done intentionally or some server side error occurred?

In this post let us analyze a technique to sport the user who killed your process:

Using SQL Server error Log:
SQL Server logs errors to the sql server error log, It can be found that it also logs the killing of processes in its error log.

So first query the latest error log for the word kill.

EXEC sys.xp_readerrorlog 0,1,'kill'

If you cannot find any results then continue with the next error log

EXEC sys.xp_readerrorlog 1,1,'kill'

You can find the logdate,the killed SPID ,Host name and host processid that killed the process in the result

Now find out the record for your processid in the result.

To find the user who killed your process run the below queries replacing the host process id with the value that you found out in the above step.

select login_name,* from sys.dm_exec_sessions where host_process_id = '[host process ID]'

The login_name in the result gives you the login name of the user who killed your process.

Hope this post helps..
Applicable to sql server 2005,sql server 2008,sql server 2008 r2,sql server 2012
Also read: Read SQL Server Error Log Using TSQL Query

Do Comment if you wanted to discuss further or have any issues.

In order to group by month you can use the SQL Server in built funtion Month().

GROUP BY MONTH([datetimecolumn])

TO group data by year you can use the in built funtion YEAR().

GROUP BY YEAR([datetimecolumn])

In order to group by month and year you can use bith YEAR() and MONTH().

GROUP BY YEAR([datetimecolumn])

Example code:

CREATE TABLE #Sales
(
    Name VARCHAR (100),
    SalesDateTime DATETIME
)

GO
INSERT INTO #Sales
SELECT 'Product1',
       '2010-04-01 00:00:00.000'
UNION ALL
SELECT 'Product2',
       '2010-04-02 00:00:00.000'
UNION ALL
SELECT 'Product3',
       '2011-04-02 00:00:00.000'
UNION ALL
SELECT 'Product4',
       '2011-04-03 00:00:00.000'
UNION ALL
SELECT 'Product5',
       '2011-04-03 00:00:00.000'
UNION ALL
SELECT 'Product1',
       '2012-04-30 00:00:00.000'
UNION ALL
SELECT 'Product1',
       '2012-04-30 00:00:00.000'
UNION ALL
SELECT 'Product1',
       '2012-05-02 00:00:00.000'
UNION ALL
SELECT 'Product5',
       '2012-05-02 00:00:00.000'
UNION ALL
SELECT 'Product5',
       '2012-05-02 00:00:00.000'

GO
--GROUP BY Month
SELECT   count(*) AS SalesCount, MONTH(SalesDateTime) Month
FROM #Sales
GROUP BY MONTH(SalesDateTime)

--GROUP BY YEAR
SELECT   count(*) AS SalesCount, YEAR(SalesDateTime) YEAR
FROM #Sales
GROUP BY YEAR(SalesDateTime)

--GROUP BY Month and Year
SELECT   count(*) AS SalesCount, YEAR(SalesDateTime) YEAR,MONTH(SalesDateTime) Month
FROM #Sales
GROUP BY YEAR(SalesDateTime),MONTH(SalesDateTime)

GO
DROP TABLE #Sales

sql server 2005,2008,2008 r2,2012

How to Group by Day in sql server?

Grouping By Day Of month:
In order to group the data by day you can use sql server in built DAY() funtion.

Grouping By Day Of week:
For this you can use DATEPART(weekday,[date column])

Grouping By week:
For this you can use DATEPART(week,[date column])

Grouping By DAY of Year:
For this you can use DATEPART(dayofyear,[date column])

Let us suppose the column which contains date/datetime values is solddatetime.

To group by day of month you can use:

GROUP BY DAY(SalesDateTime)

To group by Day of week you can use:

GROUP BY DATEPART(weekday,SalesDateTime)

To group by week you can use:

GROUP BY DATEPART(week,SalesDateTime)

To group by Day of year you can use:

GROUP BY DATEPART(dayofyear,SalesDateTime)

Complete example:

CREATE TABLE #Sales
(
    Name VARCHAR (100),
    SalesDateTime DATETIME
)

GO
INSERT INTO #Sales
SELECT 'Product1',
       '2012-04-01 00:00:00.000'
UNION ALL
SELECT 'Product2',
       '2012-04-02 00:00:00.000'
UNION ALL
SELECT 'Product3',
       '2012-04-02 00:00:00.000'
UNION ALL
SELECT 'Product4',
       '2012-04-03 00:00:00.000'
UNION ALL
SELECT 'Product5',
       '2012-04-03 00:00:00.000'
UNION ALL
SELECT 'Product1',
       '2012-04-30 00:00:00.000'
UNION ALL
SELECT 'Product1',
       '2012-04-30 00:00:00.000'
UNION ALL
SELECT 'Product1',
       '2012-05-02 00:00:00.000'
UNION ALL
SELECT 'Product5',
       '2012-05-02 00:00:00.000'
UNION ALL
SELECT 'Product5',
       '2012-05-02 00:00:00.000'

GO
--GROUP BY DAY of Month
SELECT   count(*) AS SalesCount, DAY(SalesDateTime) Day
FROM #Sales
GROUP BY DAY(SalesDateTime)

--GROUP BY DAY of Week
SELECT   count(*) AS SalesCount, DATEPART(weekday,SalesDateTime) Day
FROM #Sales
GROUP BY DATEPART(weekday,SalesDateTime)

--GROUP BY Week
SELECT   count(*) AS SalesCount, DATEPART(week,SalesDateTime) Day
FROM #Sales
GROUP BY DATEPART(week,SalesDateTime)

--GROUP BY DAY of Year
SELECT   count(*) AS SalesCount, DATEPART(dayofyear,SalesDateTime) Day
FROM #Sales
GROUP BY DATEPART(dayofyear,SalesDateTime)

GO
DROP TABLE #Sales

In many real time scenarios data is needed grouped by the date range for date (or) datetime columns.

This post explains in detail as to how to group data over date range in sql server.

Steps to group over different date ranges.

1. Identify the different date ranges

For example:

Data can be grouped by the below date ranges.(In YEAR-MONTH-DATE)

2011-01-01 TO 2011-05-31 Call it range 1

2011-01-01 TO 2011-12-31 Call it range 2

2012-01-01 TO 2012-05-31 Call it range 3

Other date ranges – Call it range 4

2. Build case statements for each date range

CASE
   WHEN DateTimeSold BETWEEN CAST ('2011-01-01 00:00:00.000' AS DATETIME) AND CAST ('2011-05-31 00:00:00.000' AS DATETIME) THEN 1
   WHEN DateTimeSold BETWEEN CAST ('2011-06-01 00:00:00.000' AS DATETIME) AND CAST ('2011-12-31 00:00:00.000' AS DATETIME) THEN 2
   WHEN DateTimeSold BETWEEN CAST ('2012-01-01 00:00:00.000' AS DATETIME) AND CAST ('2012-03-31 00:00:00.000' AS DATETIME) THEN 3
   ELSE 4
END AS DateRange

3. Use the case statements in group by clause to group over different ranges.

GROUP BY CASE
  WHEN DateTimeSold BETWEEN CAST ('2011-01-01 00:00:00.000' AS DATETIME) AND CAST ('2011-05-31 00:00:00.000' AS DATETIME) THEN 1
  WHEN DateTimeSold BETWEEN CAST ('2011-06-01 00:00:00.000' AS DATETIME) AND CAST ('2011-12-31 00:00:00.000' AS DATETIME) THEN 2
  WHEN DateTimeSold BETWEEN CAST ('2012-01-01 00:00:00.000' AS DATETIME) AND CAST ('2012-03-31 00:00:00.000' AS DATETIME) THEN 3
  ELSE 4
END

Below example (sample) query does the complete task in detail:

CREATE TABLE #TestSales
(
    CustomerName VARCHAR (100),
    Product      VARCHAR (100),
    DateTimeSold DATETIME
)

GO
INSERT INTO #TestSales
SELECT 'X',
       'Product1',
       '2011-05-01 00:00:00.000'
UNION ALL
SELECT 'X',
       'Product2',
       '2011-06-01 00:00:00.000'
UNION ALL
SELECT 'X',
       'Product3',
       '2011-06-01 00:00:00.000'
UNION ALL
SELECT 'X',
       'Product1',
       '2012-01-01 00:00:00.000'
UNION ALL
SELECT 'X',
       'Product1',
       '2012-02-01 00:00:00.000'
UNION ALL
SELECT 'X',
       'Product3',
       '2012-02-01 00:00:00.000'
UNION ALL
SELECT 'X',
       'Product1',
       '2012-03-01 00:00:00.000'
UNION ALL
SELECT 'X',
       'Product2',
       '2012-03-01 00:00:00.000'
UNION ALL
SELECT 'X',
       'Product2',
       '2012-03-01 00:00:00.000'
UNION ALL
SELECT 'X',
       'Product2',
       '2012-04-01 00:00:00.000'

GO
SELECT   count(*) AS NumberOfSales,
         CASE
WHEN DateTimeSold BETWEEN CAST ('2011-01-01 00:00:00.000' AS DATETIME) AND CAST ('2011-05-31 00:00:00.000' AS DATETIME) THEN 1
WHEN DateTimeSold BETWEEN CAST ('2011-06-01 00:00:00.000' AS DATETIME) AND CAST ('2011-12-31 00:00:00.000' AS DATETIME) THEN 2
WHEN DateTimeSold BETWEEN CAST ('2012-01-01 00:00:00.000' AS DATETIME) AND CAST ('2012-03-31 00:00:00.000' AS DATETIME) THEN 3 ELSE 4
END AS DateRange
FROM     #TestSales
GROUP BY CASE
WHEN DateTimeSold BETWEEN CAST ('2011-01-01 00:00:00.000' AS DATETIME) AND CAST ('2011-05-31 00:00:00.000' AS DATETIME) THEN 1
WHEN DateTimeSold BETWEEN CAST ('2011-06-01 00:00:00.000' AS DATETIME) AND CAST ('2011-12-31 00:00:00.000' AS DATETIME) THEN 2
WHEN DateTimeSold BETWEEN CAST ('2012-01-01 00:00:00.000' AS DATETIME) AND CAST ('2012-03-31 00:00:00.000' AS DATETIME) THEN 3 ELSE 4
END

sql server 2000,2005,2008,2008 r2,2012

This error occurs when the lock cannot be applied on a specific table at a specified granularity.

It occurs for two granularity levels:
ROW
PAGE

For more information on ROW granularity locking error follow this link:
Cannot use the ROW granularity hint on the table because locking at the specified granularity is inhibited.

For more information on PAGE granularity locking error follow this link:
Cannot use the PAGE granularity hint on the table because locking at the specified granularity is inhibited.

Error:

Msg 651, Level 16, State 1, Line 1
Cannot use the PAGE granularity hint on the table [table name] because locking at the specified granularity is inhibited.

This error could occur due to creation of indexes on the table with ALLOW_PAGE_LOCKS = OFF

Check the indexes on the table to see if there is any index with ALLOW_PAGE_LOCKS off.

Query:

SELECT * FROM sys.indexes
WHERE  ALLOW_PAGE_LOCKS = 0 and object_name(object_id) = [table name]

Replace the [table name] with the table name in the error and check if there are any indexes with ALLOW_PAGE_LOCKS disabled.

Fix:
1. Disable the index or change the index to enable page locks.
2. Use row locks or table locks instead.

Error:

Msg 651, Level 16, State 1, Line 1
Cannot use the ROW granularity hint on the table because locking at the specified granularity is inhibited.

This error could occur due to creation of indexes on the table with ALLOW_ROW_LOCKS = OFF

Check the indexes on the table to see if there is any index with ALLOW_ROW_LOCKS off.

Query:

SELECT * FROM sys.indexes
WHERE  allow_row_locks = 0 and object_name(object_id) = [table name]

Replace the [table name] with the table name in the error and check if there are any indexes with allow_row_locks disabled.

Fix:
1. Disable the index or change the index to enable row locks.
2. Use page locks or table locks instead.

How to truncate all tables sql server?

Following script truncates all the tables in SQL Server:

DECLARE @tablename AS VARCHAR (1000)

DECLARE @sql AS VARCHAR (1000)

IF OBJECT_ID('tempdb.dbo.#tables') IS NOT NULL
    DROP TABLE #tables

SELECT *
INTO   #tables
FROM   sys.tables

WHILE EXISTS (SELECT *
              FROM   #tables)
    BEGIN
        SELECT @tablename = name
        FROM   #tables
        SELECT @sql = 'truncate table ' + @tablename;
        PRINT @sql
        EXECUTE (@sql)
        DELETE #tables
        WHERE  name = @tablename;
    END

Works if the tables do not have foriegn key constrains or schema binding relations with other tables/objects in the database.

Error:

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Reason:
The select statements in the clause do not have the same number of columns.

Fix:
Make sure that all the starements using UNION, INTERSECT or EXCEPT operator have same number of expressions(columns).
Use column names instead of using * in the select list.

Sample example code

Before:

SELECT 1
UNION
SELECT 2,3

After:

SELECT 1,NULL
UNION
SELECT 2,3