20 February 2017

How to backup all the user databases

The database administrator must backup periodically the databases on the Sql Servers.

The process is tedious and must be automated with a script to avoid forgetting any database.


The simple script can be scheduled on the Sql Server agent.
DECLARE @backupName VARCHAR(255) -- database backup name  
DECLARE @databaseName VARCHAR(255) -- database name  
DECLARE @path VARCHAR(256) -- Folder for the for backup files  
DECLARE @fileName VARCHAR(256) -- file name for generating the backup  
DECLARE @databaseExclusions TABLE (DatabaseName VARCHAR(255)) -- Databases to exclude the backup

-- Input parameters
SET @path = 'D:\Backup\2\'  
INSERT INTO @databaseExclusions (DatabaseName)
VALUES
('tempdb'),('master'),('model'),('msdb'),('AdventureWorks')


DECLARE database_cursor CURSOR FOR  
SELECT top 5 name
    FROM sys.databases 
    WHERE [state] <> 6 -- OFFLINE
    AND owner_sid != 1 -- User user database
    AND name NOT IN (SELECT DatabaseName FROM @databaseExclusions)
 
OPEN database_cursor   
FETCH NEXT FROM database_cursor INTO @databaseName   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @databaseName;  
   SET @backupName = @databaseName + '-Full Database Backup';

   BACKUP DATABASE @databaseName TO DISK = @fileName WITH NOFORMAT, INIT,  NAME = @backupName, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
   
   FETCH NEXT FROM database_cursor INTO @databaseName   
END   

 
CLOSE database_cursor   
DEALLOCATE database_cursor

02 June 2016

How to convert a string to a byte array in C#

C# allow to easily convert a string to a byte array using the Encoding.ASCII.GetBytes function.
A sample usage is
string sample = "This is a simple string";
byte[] content = Encoding.ASCII.GetBytes(sample);

Note that this method only works if the string is with the ASCII format.

The framework supports other encodings (UTF8, Unicode, UTF32, ...) but a more generic method is needed.
The ideal solution if to don't need to worry about the encoding if the bytes don't need to be interpreted.
A use case for this solution is to transfer a file from a web server to a browser.
        static byte[] GetBytes(string value)
        {
            byte[] bytes = null;
            using (var memoryStream = new MemoryStream())
            {
                using (var streamWriter = new StreamWriter(memoryStream, Encoding.Default))
                {
                    streamWriter.Write(value);

                    streamWriter.Flush();
                    streamWriter.Close();

                    bytes = memoryStream.ToArray();
                }
            }

            return bytes;

        }


But this solution also uses the Encoding.Default for the StreamWriter.

The Enconding can also be performed as:
string sample = "This is a simple string";
byte[] content = Encoding.Default.GetBytes(sample);

Welcome the the encondig hell!!

01 June 2016

T-SQL: How to split a path name in the path and file name

A common task in T-SQL is how to split a path name in the path and file name.

The following code will do the task:

DECLARE @file VARCHAR(200) = 'c:\temp\data\log.txt'

SELECT REVERSE(LEFT(REVERSE(@file), CHARINDEX('\', REVERSE(@file), 1) - 1)) AS [FileName]

SELECT LEFT(@file, LEN(@file) - CHARINDEX('\', REVERSE(@file), 1) + 1) AS [Path]

07 April 2016

Error when creating a Database diagram: Database owner is invalid

After restoring a database, I got an error when creating a Database diagram. Like the following image:



The error informs that the database owner is invalid.

The solution is to change to database owner using the sp_changedbowner.

To change the database owner for the sa user:
EXEC sp_changedbowner 'sa'

04 April 2016

The mistery of the slow stored procedure in the web App and fast in SSMS

Sql Server has a lot of mysteries and hidden "features".

In the troubleshooting of a web application page response time, I got a timeout executing a Stored Procedure. The query took more than 30 seconds executing on the SQL Server profiler.

I captured the query and executed it in SQL Server Management Studio (SSMS), to start analyzing the query execution plan.
For my surprise it was super-fast when executed in SSMS.

After some research I found that the applications the use ADO.Net set the ARITHABORT to OFF and SSMS set it to ON.
The setting can be disable as shown the in following image.

This setting difference makes that the SSMS will not reuse the same cache entry that the ADO.Net application uses. SQL Server will compile the stored procedure and create a new execution plan.
There are also other possibilities like parameter sniffing to cause this difference on execution times.
In my case the parameters were the same, but what only changed was the ARITHABORT setting.

Having identified that the problem was with the Stored Procedure corrupted execution plan and it's cache, I simply forced a recompile of the stored procedure using the WITH RECOMPILE.
Example usage:

ALTER PROCEDURE myStoredProcedure
(
@id INT,
@date DATETIME
)
WITH RECOMPILE
AS


SQL Server recompiled the stored procedure, generated a new plan and the timeout disappeared.

26 February 2016

T-SQL: Converting rows to columns

A common task of a database developer is to convert rows to columns from a query result set.

T-SQL has the PIVOT that facilitates the process.

A simple example:

DECLARE @myValues TABLE (Quantity INT, Category VARCHAR(100))

INSERT INTO @myValues (Quantity, Category)
VALUES 
 (10, 'Fruits'),
 (200, 'Vegetables'),
 (40, 'Meats')


SELECT *
FROM @myValues

SELECT [Fruits], [Vegetables], [Meats]
FROM @myValues
PIVOT
(
  MAX(Quantity)
  FOR Category IN ([Fruits], [Vegetables], [Meats])
) Piv

17 November 2015

How to fix visual studio 2015 error "The package did not load correctly"

When a visual studio extension is installed, a error can occur that gives the following error:

The 'zebre' package did not load correctly.

The problem may have been caused by a configuration change or by the installation of another extension. You can get more information by examining the file 'C:\Users\[user name]\AppData\Roaming\Microsoft\VisualStudio\14.0\ActivityLog.xml'.

Restarting Visual Studio could help resolve this issue.
Continue to show this error message?


The error can cause intellisense not working or other erratic issues.

A possible fix is to uninstall the extension that is causing the error.

The issued can be fixed executing the following command as an administrator:

devenv.exe /setup

If the previous solution does not work, reset the visual studio preferences by issuing the command (Execute as an administrator):

devenv.exe /resetuserdata