Feeds:
Posts
Comments

Archive for February, 2014

In my earlier articles, I wrote how to insert files inside the file table and this is more than enough if you maintain few files inside one filetable. But once a filetable is crowded you should maintain the files inside the folders to manage it properly. But how to create a folder inside a filetable ?

Note : I strongly recommend that you should read given below articles to have a clear understanding about FileTables.

  1. FileTables – Prerequisites
  2. FileTables – Data Definition Language (DDL)
  3. FileTables – Data Manipulation Language (DML)

There are two methods to create a folder inside file table. Given below are the details.

  • Using SQL Server Management Studio (SSMS)
  • Using T-SQL

Let me explain each method in detail.

  • Using SQL Server Management Studio (SSMS)

This is the easiest way to create a folder inside filetable. In order to follow this method we need to achieve it step by step.

Step 1 :
First of all you should select the respective filetable in which you need to create a folder and right click on it and select “Explore filetable directory” as shown in the image below.

create_folder_inside_filetable.1.1

Step 2 :
Once you select “Explore filetable directory”, it will open that particular filetable directory folder. You can create folder here, just like in windows, as shown below.

create_folder_inside_filetable.1.2

create_folder_inside_filetable.1.3

  • Using T-SQL

This method we usually use when we need to create a folder dynamically using T-SQL. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE SampleDB
GO
INSERT INTO dbo.CVBank
(name,is_directory) VALUES ('New folder', 1);
Advertisements

Read Full Post »

Last weekend, my support engineer and I were at a client site to upgrade SQL Server 2005 to SQL Server 2012. Before proceeding with any activity, we tried to take a backup and got the given below error that I received it later by my support engineers as shown below.

error

Let me explain the error and its solution in details.

Message Number: 3023 

Severity : 16

Error Message: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

Resolution:
If you look at the error description, it seems that there is a transaction going on in the background, and due to this reason SQL Server is not allowing us to take a backup or may be the database is corrupt. So we started doing our basic testing and it seems everything is fine. Then why SQL Server is not allowing us to take a backup ?

So, I thought of executing the given below query to check whether any backup or restore operation is in progress and I found that a scheduled backup was already in progress. Ooopss……….. So we waited for a while and took the backup again.

erromessage3023.1.1

Conclusion:
Whenever you come across this error, you should check what the processes are in progress in SQL Server. It will give you a hint, why you are unable to take backup. In my case, a scheduled backup was in progress at the same time when I tried to take the backup.

Read Full Post »

Today, I came across a situation, where I had to perform an activity similar to temporary table cleaning in more than one database. So I started thinking about the shortest possible solution. After some research, I found two possible solutions and one of them was sp_MSforeachdb. It is basically an undocumented stored procedure in SQL Server and it is very handy when you need to perform / achieve some tasks in different databases at a time. However, I need to perform the above mentioed task NOT IN all databases but few of them only. So I started doing some testing to see how to achieve database filter in the sp_MSforeachdb. After couple of testings, I finally found the way to execute a set of commands on specific databases using sp_MSforeachdb.
Given below is the script.

--This Script is compatible with SQL Server 2005 and above.
EXEC sp_MSforeachdb
@command1='IF ''?''
IN (''AdventureWorks2012'',''AdventureWorks2012_test'')
BEGIN
       SELECT name,object_id,modify_date
       FROM ?.sys.tables WHERE [name]=''Person''
END'
--OUTPUT

sp_MSforeachdb.1.1

Let me know if you came across these situations and what was the solution ?

Note : It is not recommended to use sp_MSforeachdb on production databases.

Read Full Post »

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