Feeds:
Posts
Comments

Archive for January, 2013

In my previous article, I wrote about Executing Stored Procedure with Result Sets. I utilized this new feature introduced in SQL Server 2012 many times and it is very handy, when it comes to manipulate stored procedure result sets.

Today, I was reading a question related to this topic in a forum where a community member tried to manipulate stored procedure result set with result sets keyword and then insert it into a temporary table. But it gives an error.

Let me create few examples to explain the problem :

Example 1 :
In this example, we will insert the result set of a stored procedure in a temporary table without using result set keyword and it works fine.

Use AdventureWorks2012
GO
Create Table #temp
(
[DepartmentID] int,
[Name] varchar(50)
)
GO
Create PROCEDURE SP_ResultSet_Example1
as
Select [DepartmentID]
,[Name]
from [HumanResources].[Department]
GO
Insert into #temp EXEC SP_ResultSet_Example1
GO
Select * from #temp order by [DepartmentID]

resultseterror1.1

Example 2 :
In this example, we will insert the result set of a stored procedure in a temporary table with result sets keyword. It gives an error .

Use AdventureWorks2012
GO
Create Table #temp
(
[DepartmentID] int,
[Name] varchar(50)
)
GO
Create PROCEDURE SP_ResultSet_Example1
as
Select [DepartmentID]
,[Name]
from [HumanResources].[Department]
GO

Insert into #temp EXEC SP_ResultSet_Example1 WITH RESULT SETS
(
([Department ID] int NOT NULL,
[Department name] Name NOT NULL));
GO
Select * from #temp order by [DepartmentID]

resultseterror1.2

Explanation :
We cannot use result sets keyword, when we need to insert any stored procedure result set into a table. The reason behind this is if we use result sets keyword we can return multiple results with different columns and with different data types from a single stored procedure and this functionality cannot be handled in a single table. As per MSDN ” WITH
Possible execute options. The RESULT SETS options cannot be specified in an INSERT…EXEC statement.”
.

Read Full Post »

I received a query from one of the community members, before discussing his query I would like to discuss his environment first. He is working in an environment where multiple users shares the same machines and the same credentials to work on SQL Server Management Studio 2005. He asked me, if I could help him to remove the recent files activity from the menu, to restrict any other user from viewing his activities.

Given below is the screen image in SQL server 2005 with the recent files history.

Recent activity 1.2

I started searching the solution in SSMS and found few solutions :

Solution 1
You need to go to Tools \ Options \ Environment \ General
Here you can change the display files in recently used list from 10 (By default it is 10) to 1.
There are two issues with this solution :

  • You cannot make it 0.
  • If you make it 10 again, it will show you the history again.

Recent activity 1.1

Solution 2
You can delete the recent file activity history through registry. Open the registry via “regedit.exe” and go to the given below path with respect to the SQL Server version you are using and delete the key related to your files as shown in below image.
SQL SERVER 2005
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\FileMRUList

SQL SERVER 2008
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\FileMRUList

SQL Server 2012
HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\FileMRUList

Recent activity 1.3

After that close the SSMS and open it again, there will be no recent files activities.

Recent activity 1.4

Let me know if you know a better solution.

Read Full Post »

sys.dm_exec_query_stats is very helpful dynamic management view when it comes to query recovery.
Sometimes we write queries to perform some operations in the database and after that close the script window without saving. Later on realize, if only had I saved it, it would have saved some time. Here the solution comes namely sys.dm_exec_query_stats.
This view can recover your query from sql server. Let me create an example to explain it.

First open a new query window and execute the given below script and close the query without saving it.

USE AdventureWorks2012
GO
Select * from Sales.vStoreWithContacts
Select * from Production.ScrapReason
Select * from Sales.vStoreWithAddresses
Select * from Purchasing.vVendorWithContacts
Select * from HumanResources.Shift
Select * from Purchasing.vVendorWithAddresses
Select * from Production.ProductCategory
Select * from Purchasing.ShipMethod
Select * from Production.ProductCostHistory
Select * from Production.ProductDescription
Select * from Sales.ShoppingCartItem

After that, just execute the given below script, it will recover your query.

SELECT Distinct qt.TEXT AS [Queries]
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 

dm_exec_query_stats1.1

The reason why we used distinct in the above query is, there are queries that we have executed multiple times that will appear as well.

Still, I am searching for a solution that can link these queries to the user who executed it so the recovery would be easier. Will update you on this in the upcoming post.

Reference : MSDN

Read Full Post »

SQL Server assigns each object (tables, view, stored procedure, functions etc) a unique ID and that ID we call as an Object_ID. In simple words, we can say that it creates uniqueness between two objects.
In this article, we will learn how it works, what is the next counter, etc.

What will be the first object_ID in the new database ?
If you create a new object in the database, the first ID will always be 2073058421 in SQL SERVER 2005 and 245575913 in SQL SERVER 2012.
The difference between both versions is due to some new internal tables introduced in SQL SERVER 2012 and got the new ID 2073058421 (filestream_tombstone_2073058421) once you create a database in 2012.

How to get this Object_ID from SQL ?
You just need to use a function OBJECT_ID, to retrieve object ID of any object. For example :

Select Object_ID('test_table');

What will be the object_ID increment counter for user defined objects ?
It will add 16000057 + Last user defined object_ID and will give you a new ID.

What will be the object_ID increment counter for system objects ?
It varies from 1 onwards.

If we drop an object and create the same object, does SQL assign the same object_ID ?
No, SQL will assign a new ID every time.

Why some object IDs are in negative and some IDs are in positive ?

select * from sys.all_objects

If you execute above query, you can see Some IDs are in positive and some are in negative. All user defined objects are assigned a positive object ID plus system tables. Apart from these objects, the rest of the system objects are assigned negative object IDs.

All the above facts and figures can vary in different versions of SQL. If you know something new about Object_ID do share with me.

Read Full Post »

Recently, I came across with a nice question in a forum, how to mass/bulk rename sql tables. The issue was, some tables were imported from some sources but their names were not as per the database standards. Therefore standardization was required.

Let me explain this with an example :

Given below are the table names after imports :

  1. dbo.test_Item
  2. dbo.test_Purchase
  3. dbo.test_Inventory
  4. dbo.test_Sales etc.

But the table name should be like this :

  1. dbo.Item
  2. dbo.Purchase
  3. dbo.Inventory
  4. dbo.Sales etc.

SOLUTION :
Given below is the solution that will dynamically make sql statements to rename the tables. And if you execute the result set of the below query it will rename all your tables having “_” in the name.
You can use replace “_” with any other character as per the requirement.

Declare @char varchar(1)
Set @char='_'
SELECT 'EXEC sp_rename ''' + NAME +  ''', ' + '''' 
+ Substring(name ,CharIndex(@char,name)+1,LEN(name)) + '''' 
As [Script]
FROM dbo.sysobjects 
WHERE xtype = 'U' And CharIndex(@char,name)>0
Order By [Name]
--OUTPUT

sp_rename1.1

Let me know if you have better solution.

Read Full Post »

In this article we will discuss a new error message (Window frame with ROWS or RANGE must have an ORDER BY clause) introduced in SQL SERVER 2012.

Let’s discuss it in detail:

Message Number: 10756

Severity : 15

Error Message: “Window frame with ROWS or RANGE must have an ORDER BY clause.”

Error Generation:
Let me create an example to generate this error:

USE AdventureWorks2012
GO
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER (PARTITION BY SalesOrderID
--ORDER BY SalesOrderID, ProductID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS 'total'
FROM Sales.SalesOrderDetail
Where SalesOrderID IN(43659,43664)
Order By SalesOrderID

--OUTPUT

Msg 10756, Level 15, State 1, Line 2
Window frame with ROWS or RANGE must have an ORDER BY clause.

Resolution:
In the above example, you can see, I used Window frame with ROWS in the OVER clause but forgot to provide order by clause. Remember, whenever you use Window frame with rows or range, make sure you use it along with order by clause to avoid this error.

Please let me know if you have other samples to regenerate such error.

Read Full Post »

DATETIMEOFFSETFROMPARTS is one of the important sql server functions required when we need to make date and time from offset and precision.

Let me explain its syntax, parameters, purpose and examples in detail.

Syntax :

    DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

Parameters :
@Year : A valid integer for year.
@Month : A valid integer for month range from 1-12.
@Day : A valid integer for day range from 1-31. (Depends upon the total number of days in a month)
@Hour : A valid integer for Hour range from 0-23.
@Minutes : A valid integer for Minutes range from 0-59.
@Seconds: A valid integer for Seconds range from 0-59.
@Fractions : A valid integer for Fractions range from 0-9999999.
@hour_offset : A valid integer for hour portion of the offset range from -14 – +14.
@minute_offset : A valid integer for minute portion of the offset should be 0 or 30
@Precision : A valid integer for Precision range from 0-7.

Purpose :
This function requires year, month, day, hour, minute, seconds, fractions,hour portion of offset, minute portion of offset & precision as a parameter (All parameters are mandatory) and returns a valid datetimeoffset as a result. If we pass any invalid date, time or offset parts, it will generate an error. Also if we pass NULL values to any of its parameters except Precision parameter, it returns NULL value.
Let me explain this with simple examples.

Example-1 : DATETIMEOFFSETFROMPARTS – With valid offset Parts

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @Hour as int=23
Declare @Minute as int=59
Declare @Second as int=59
Declare @Fraction as int=50
Declare @Hour_offset as int=12
Declare @Minute_offset as int=00


Select DATETIMEOFFSETFROMPARTS( @Year,@Month,@Day,@Hour,@Minute,@Second,@Fraction,@Hour_offset,@Minute_offset,2)
as [RESULT]
--RESULT
2012-12-31 23:59:59.50 +12:00

datetimeoffset1.1

Example-2 : DATETIMEOFFSETFROMPARTS – With Invalid offset Parts
It will generate an error because only valid offset parts are allowed as a parameter.

Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @Hour as int=23
Declare @Minute as int=59
Declare @Second as int=59
Declare @Fraction as int=50
Declare @Hour_offset as int=15 -- Invalid Hour offset
Declare @Minute_offset as int=00


Select DATETIMEOFFSETFROMPARTS( @Year,@Month,@Day,@Hour,@Minute,@Second,@Fraction,@Hour_offset,@Minute_offset,2)
as [RESULT]
--RESULT

Msg 289, Level 16, State 6, Line 12
Cannot construct data type datetimeoffset, some of the arguments have values which are not valid.

datetimeoffset1.2
Example-3 : DATETIMEOFFSETFROMPARTS- With NULL offset parts
If we pass NULL value to any of its parameter except Precision parameter, it will return NULL value

 Declare @Year as smallint=2012
Declare @Month as smallint = 12
Declare @Day as smallint =31
Declare @Hour as int=23
Declare @Minute as int=59
Declare @Second as int=59
Declare @Fraction as int=50
Declare @Hour_offset as int=NULL -- Hour offset as NULL
Declare @Minute_offset as int=00

Select DATETIMEOFFSETFROMPARTS( @Year,@Month,@Day,@Hour,@Minute,@Second,@Fraction,@Hour_offset,@Minute_offset,2)
as [RESULT]
--RESULT

NULL

datetimeoffset1.3
Reference : MSDN

Read Full Post »

Older Posts »