Thursday, June 20, 2013

Taking backup of all the SQL server databases using a single TSQL Command

Is it possible to take the backup of all the SQL server databases in one single command?

Yes, It is possible to take the backup of SQL server databases in one single command. But it is an undocumented command so needless to say it is "AS-IS".
The command I used was sp_msforeachdb. The command given below takes the backup of all the databases including the system databases.

sp_msforeachdb 'backup database ? to disk="D:\\BACKUPLocation\?_dev.bak"'

You may see error in the output message about TEMPDB but that is fine. This cannot be tweaked further to take only user databases.     

Please leave a command if you like this tip.

Tuesday, June 4, 2013

Error: "Incorrect syntax near ')'." When Passing function to a Stored procedure

Let us say I have a requirement to pass the current date time to a stored procedure. 
For example let us consider the following SP
CREATE PROCEDURE [dbo].[DisplayDate](@Curr_Date DATETIME)

AS SELECT @Curr_Date
When I try to execute it with the parameter as getdate(). I would receive the following error

exec [DisplayDate] getdate()

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

Theorically it should have worked. There is a Limitation on the way we can use it with a stored procedures.

According to the Books online article on
Parameters (Database Engine)

"When a stored procedure or function is executed, input parameters can either have their value set to a constant or use the value of a variable. Output parameters and return codes must return their values into a variable. Parameters and return codes can exchange data values with either Transact-SQL variables or application variables."

So it is clear that we cannot use any functions directly with an stored procedure.
So we have to look at other alternatives.

For example
The above stored procedure can be rewritten as
CREATE PROCEDURE [dbo].[DisplayDate]

AS SELECT Getdate()

So we need to consider the fact that we cannot pass the function directly to the stored procedure instead we have to either use it in variable or use the function directly.