FruitBatInShades Code

To restore a sql.bak file you first need to find the name of the database files:

RESTORE FILELISTONLY
FROM DISK = 'full path to your .bak file'

This will show you the names needed for extracting the original files to your sql server directory: Read more…

Simple function to convert a string to proper case

CREATE FUNCTION PROPERCASE
(
--The string to be converted to proper case
@INPUT VARCHAR(8000)
)
--This function returns the proper case string of varchar type
RETURNS VARCHAR(8000)
AS
BEGIN
IF @INPUT IS NULL
BEGIN
--Just return NULL if input string is NULL
RETURN NULL
END
 
 <a href="http://www.fruitbatscode.com/uncategorized/proper-case-sql-function#more-293" class="more-link">Read more...</a>

I spent all weekend trying to convert a Sql Server db to a MySql v5 db and found it quite a frustrating experience!  When you’re sytnax is incorrect MySql helpfully says ‘You have an error in your sql syntax near:’ and error 1064. No help with which command causes the error and the position it picks for the start of the problem is usually wrong.  God we’ve been spoilt by Sql Server.

Read more…

Was explaining to a friend how to cope with duplicates today so thought I’d just stick a quick note up for him to refer too. Finding duplicates in SQL is relatively easy when dealing with one field, we just need to  count the number of occurances of the field: Read more…

Just a little udf that gets midnight for a datetime.

CREATE FUNCTION dbo.Midnight (@Date datetime)
RETURNS Datetime AS
BEGIN
	return CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME)
END

Just a couple of udf to handle paths in sql server, not very solid but if your paths are valid they work well.

CREATE FUNCTION dbo.GetFileName (@Path varchar(512))
RETURNS varchar(255) AS
BEGIN
	Return    RIGHT(@Path, LEN(@Path) - dbo.LAST_INDEX(@Path, '\'))
END

Read more…