Get the ASCII code of characters from an SQL SERVER NVARCHAR field

Found a very useful piece of code, How to find a hidden unicode character using SQL Server, that obtains the ASCII code of characters from an SQL SERVER NVARCHAR field.

Export binary data field into a file from Microsoft SQL Server, using BCP utility

The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format.

Start a command prompt in the place where you wish the file saved and execute the following command:

BCP "SELECT BinaryField FROM TableWithBinaryField WHERE Id = 1" queryout [OutputFileName] -S [ServerName] -d [DataBaseName] -U [Username] -P [Password]

The first parameter is the query that selects the field that needs to be exported. The italic values have to be specified by you, without the [] straight parenthesis characters around.
If you wish to use a trusted connection then replace the -U Username -P [Password] part with -T.

After executing the command, just press Enter at every question it throws at you.

Here is an examplewhen you wish to specify a username and password:
BCP "SELECT BinaryField FROM TableWithBinaryField WHERE Id = 1" queryout FileName.pdf -S MyServer -d ProductionDatabase -U sa -P secretPassword1

Here is an another example with trusted connection, where no username and password have to be specified:
BCP "SELECT BinaryField FROM TableWithBinaryField WHERE Id = 1" queryout FileName.pdf -S MyServer -d ProductionDatabase -T

"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." error

After creating a new MVC3 web application in Visual Studio 2010, running it and trying to log in (or do any operation that needs MembershipProvider access) throws the following exception/error:

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

If everything worked fine an aspnetdb.mdf file would have been created in the App_Data folder with the necessary structure.

I do not know why but the solution is deleting the

c:\Users\[Username]\AppData\Local\Microsoft\Microsoft SQL Server Data\ 

folder entirely.

Shrinking Microsoft SQL Server Log file

It works, trust me. And when I say me, I mean Microsoft :).

SQL Server 2005:
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MyDatabase_log, 2000)

SQL Server 2008:
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
GO
DBCC Shrinkfile(MyDatabase_log, 2000) 

Listing the most costly queries in SQL Server

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) AS [Query],
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

SQL Server - Automatically mapping a user to a login, creating a new login if it is required

The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that has the password B3r12-3x$098f6 if the login Mary does not exist.

USE DataBaseName;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';

How to generate INSERT scripts from SQL Server?

Download and install SQL Server Dumper, connect to the database, click Execute and generate the scripts.

• Direct download location

Sql Server 2005 - Twelve Tips For Optimizing Query Performance

• Sql Server 2005 - Twelve Tips For Optimizing Query Performance by Tony Wright

Retrieving identity columns information for tables in an SQL Server database

SELECT OBJECT_NAME(OBJECT_ID) AS [TABLE_NAME],
[NAME] AS [COLUMN_NAME],
[SEED_VALUE],
[INCREMENT_VALUE],
[LAST_VALUE],
[IS_NOT_FOR_REPLICATION]
FROM [SYS].[IDENTITY_COLUMNS]
ORDER BY [TABLE_NAME]

The result is a table with the following structure (column names shortened for space):

TN	CN	SV	IV	LV	INF
TableName	KeyColumn	1	1	NULL	0
TableName2	KeyColumn2	1	1	NULL	0

.Net select column with null value from a datatable

To select a column with null value from a DataTable use the following expression:

Isnull(Col,'NULL_COLUMN') = 'NULL_COLUMN'

where Col is the name of the column, 'NULL_COLUMN' is the default return value if the column value is null.
So basically we verify if the value returned equals the anticipated null-return-value; if it does, the column value is NULL and the row containing this column will be added to the results.

string sql = "Isnull(Col,'NULL_COLUMN') = 'NULL_COLUMN'";
string sort = "Col ASC";
DataRow[] result = myTable.Select(sql, sort);

A Visual Explanation of SQL Joins

I found a great article on Coding horror about SQL joins.

Article here.

Simple query

A few days ago we sat down with my cousin to write a simple query on his database.
"Very simple" he said. "Just two or three lines" said I.
Please enjoy the clipped version:

DECLARE @dateMin DATETIME, @dateMax DATETIME

-- Give values

DECLARE @noOfDays DECIMAL(18,2)

SET @noOfDays = DATEDIFF(DAY, @dateMin, @dateMax)

SELECT [VW_RoomDetails].[Name] [Room], [RoomType], [OccupancyCount], [Charges], [TotalCharges], [TVA], (CASE WHEN ISNULL([OccupancyCount], 0.00) = 0 THEN 0 ELSE ISNULL([Charges], 0.00)/ISNULL([OccupancyCount],0.00) END) [ADR_Net],

(CASE WHEN ISNULL([OccupancyCount], 0.00) = 0 THEN 0 ELSE ISNULL([TotalCharges], 0.00)/ISNULL([OccupancyCount],0.00) END) [ADR_Brut], ISNULL([OccupancyCount], 0.00) / ISNULL(@noOfDays,0.00) *100 [OccupancyPercent]

FROM

VW_RoomDetails

LEFT JOIN

(
SELECT [ID_Room],

SUM (DATEDIFF(DAY, (CASE WHEN [CheckInDate] > @dateMin THEN [CheckInDate] ELSE @dateMin END),

(CASE WHEN [CheckOutDate] < @dateMax THEN [CheckOutDate] ELSE @dateMax END))) [OccupancyCount]

FROM [VW_RoomStayInfoDetails]

WHERE [StayType] = 1 AND

(([CheckInDate] >= @dateMin AND [CheckOutDate] <= @dateMax)

OR ([CheckInDate] <= @dateMin AND [CheckOutDate] >= @dateMin)

OR ([CheckOutDate] <= @dateMax AND [CheckOutDate] >= @dateMax)

OR [CheckInDate] <= @dateMin AND [CheckOutDate] > @dateMax )

GROUP BY [ID_Room]) [rooms]

ON VW_RoomDetails.ID_Room = rooms.ID_Room

LEFT JOIN
(
SELECT [ID_Room], SUM([Amount]) [Charges], SUM([Amount] + [TVA]) [TotalCharges], SUM ([TVA]) [TVA] FROM [TBL_Charge]
GROUP BY [ID_Room]
) [charges]

ON [rooms].[ID_Room] = [charges].[ID_Room]