Feeds:
Posts
Comments

Archive for February, 2014

I came across this query from one of my SQL Server developers while he was doing some  R&D in SQL Server and suddenly the SQL Server started giving him fixed number of result sets (5 rows of each table/views) as shown in the picture below.

Setrowcount.1.1png

First of all he tried all the way to fix it including the restart of development SQL Server. It seems, he had to re-install his SQL Server ;). He called me, if I can help him to fix it. I asked him few queries and started doing the troubleshooting and finally fixed the issue. Let me reproduce the same issue and fix it step by step.

Step 1 :
Open SSMS (SQL Server Management Studio) and go to Tools >> Options menu as shown in the image below.

Setrowcount.1.3

Step 2 :
Once you select options menu, an options dialogue box will pop up. Now you need to select “Query Execution” from left hand pane and make SET ROWCOUNT =0 as shown in the image below. By mistake my developer put 5 in the SET ROWCOUNT and it returns 5 number of rows in each result sets for each table and view. Once you are done press OK button.

Setrowcount.1.2

Step 3 :
Now open a new query window and execute the same queries. This time, it will give you the exact result set instead of 5 rows.

Setrowcount.1.44

Let me know if you guys came across this issue and how did you fix it ?

Read Full Post »

Sometimes, when you create a new object using SSMS (SQL Server Management Studio) and try to use that newly created object, it shows a red line underneath that object and if you move your mouse cursor on that object, it says the object does not exist, but on the other hand it manipulates the query too, as shown in the picture below.

refresh_cache.1.1

When I came across this issue earlier I thought it could be a bug. But actually it is not, so what is wrong with SQL Server ? Basically once you create a new SQL Server object, your newly created object does not get updated in the IntelliSence Local Cache and due to this, it shows red line underneath that object. So you just need to refresh SSMS IntelliSence Local Cache and once you refresh it, IntelliSence will automatically add newly created object in the cache and the red line will disappear. Now, the question is how to update it ? You can achieve in two different ways. Given below are the details.

  • Shortcut

In this method, you need to open SSMS and just need to press “Ctrl+Shift+R” and Intellisence Local Cache will be updated automatically.

  • Via Menu

In this method, you need to open SSMS then go to Edit menu and point IntelliSence and select Refresh Local Cache and once you select Refresh Local Cache, your Intellisence Local Cache will be updated automatically as shown in the picture below.

refresh_cache.1.2

Let me know, what you guys did, when you came across it first time 😉 ?

Read Full Post »

Data presentation is one of the aspects that is much more important when you need to present a report to end user. Usually, I recommend that you should do it at the presentation layer (front end). However, sometimes we need to do all the formatting at database level itself and just present it at presentation level. One of the most frequently formattings we usually come across is datetime data type formatting and to format datetime we need to convert it into varchar data type.

Given below are the solutions.

Solution 1 :
In this solution, we need to use the Traditional method using CONVERT function.

USE AdventureWorks2012
GO

SELECT PurchaseOrderID
, OrderDate
, CONVERT(VARCHAR(11),OrderDate,113) 
AS [OrderDate Using Convert]
FROM Purchasing.PurchaseOrderHeader
GO
--OUTPUT

datetimetovarchar.1.1

Solution 2 :
In this solution, we need to use one of the new conversion functions shipped with SQL Server 2012 namely TRY_CONVERT function.

USE AdventureWorks2012
GO

SELECT PurchaseOrderID
, OrderDate
, TRY_CONVERT(VARCHAR(11),OrderDate,113) 
AS [OrderDate Using Try_Convert]
FROM Purchasing.PurchaseOrderHeader
GO
--OUTPUT

datetimetovarchar.1.2

Solution 3 :
In this solution, we need to use one of the new formatting functions shipped with SQL Server 2012 namely FORMAT function.
I usually recommend this method because it gives you variety for formatting.

USE AdventureWorks2012
GO

SELECT PurchaseOrderID
, OrderDate
, FORMAT(OrderDate,'dd MMM yyyy') 
AS [OrderDate Using Format]
FROM Purchasing.PurchaseOrderHeader
GO
--OUTPUT

datetimetovarchar.1.3

Read Full Post »

« Newer Posts