SqlServer Title Case

Some time ago, I've published a post (HERE) where it was written hot to capitalize strings in SQL Server.

Now I want to show how to capitalize every word in a string.

This topic is more complex, it's necessary to create a User Defined Function that does the work for us:

CREATE FUNCTION dbo.CapitalizeEveryWord(@input NVARCHAR(4000)) RETURNS NVARCHAR(4000)
AS 
BEGIN
DECLARE @position INT
WHILE IsNull(@position,Len(@input)) > 1
SELECT @input = Stuff(@input,IsNull(@position,1),1,upper(substring(@input,IsNull(@position,1),1))),
@position = charindex(' ',@input,IsNull(@position,1)) + 1
RETURN (@input)
END


It's done! Now just invoke that function:

SELECT dbo.CapitalizeEveryWord (Lower(ColumName)) FROM TableName

Export from SQL Server to Excel

Is it possible to export a database table or a SQL query output directly in Excel? Maybe using a stored procedure or with a SQL command? The answer is: YES!

To start, we have to create a Stored Procedure wich does the work. This SP reads table columns and data and creates an excel file with all data and also with columns.


CREATE PROCEDURE proc_generate_excel_with_columns
(
@db_name varchar (100),
@table_name varchar (100),
@file_name varchar (100)
)
AS

--Generate column names as a recordset
DECLARE @columns varchar(8000), @sql varchar (8000), @data_file varchar (100)
SELECT 
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
FROM 
information_schema.columns
WHERE 
table_name=@table_name
SELECT @columns=''''''+replace( replace (@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
SELECT @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
SET @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c -T'''
EXEC(@sql)

--Generate data in the dummy file
SET @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -T'''
EXEC(@sql)

--Copy dummy file to passed EXCEL file
SET @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
EXEC(@sql)

--Delete dummy file 
SET @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
EXEC(@sql)


Then we have to invoke the SP passing it the Database name, the table* name and the excel file path (It isn't necessary to create it in advance).

EXEC proc_generate_excel_with_columns 'DB_NAME', 'TABLE_NAME','FILE_PATH'

WARNING:
In most systems, some system commands used in the SP are disabled by default. To enable them, use following commands instead of the one before:


EXEC master.dbo.sp_configure 'show advanced options', 1


RECONFIGURE


EXEC master.dbo.sp_configure 'xp_cmdshell', 1


RECONFIGURE

EXEC proc_generate_excel_with_columns 'DB_NAME', 'TABLE_NAME','FILE_PATH'

EXEC master.dbo.sp_configure 'xp_cmdshell', 0


RECONFIGURE

*TIP: We can use also a View or a Temporary Table instead of "TABLE_NAME"

Connect to SSEE db via Management Studio

To connect to a SSEE (SQL Server Embedded Edition) database via SqlServer Management Studio you can use the following string as Server Name:

\\.\pipe\mssql$microsoft##ssee\sql\query

jQuery modal alert in Page_Load

To insert a jQuery alert into Page_Load method of an our WebForm (maybe for a bad result of an operation) could not be simple, 'cause Page_Load method is invoked before thar the page loads included script files.

To solve it, just do this:

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

[…]

ScriptManager.RegisterClientScriptInclude(Page, Page.GetType, Guid.NewGuid().ToString(), Page.ResolveUrl("~/js/jquery-1.6.2.min.js"))

        ScriptManager.RegisterClientScriptInclude(Page, Page.GetType, Guid.NewGuid().ToString(), Page.ResolveUrl("~/js/jquery-ui-1.8.16.custom.min.js"))

        Dim sb As New StringBuilder

        sb.Append("$(function() { ")

        sb.Append(" $( '#dialog-message-error' ).dialog({")

        sb.Append("    modal: true,")

        sb.Append("    buttons: {")

        sb.Append("        Ok: function() {")

        sb.Append("               $( this ).dialog( 'close' );")

        sb.Append("        }")

        sb.Append("    }")

        sb.Append(" });")

        sb.Append("});")

        ScriptManager.RegisterClientScriptBlock(Page, Page.GetType, Guid.NewGuid().ToString(), sb.ToString, True)

End Sub

It's supposed to have jquery and jquery-ui script files into the "js" folder on application root, and that this div exists:

<div id="dialog-message-error" title="Error" style="display: none; font-size: small">

<div class="ui-state-error ui-corner-all" style="padding: 0 .7em;">

<p>

<span class="ui-icon ui-icon-alert" style="float: left; margin-right: .3em;">span>

<strong>Error:strong> Error text....

p>

div>

div>

Android: Screen dimensions and orientation

/* get display from WindowManager */

Display display = ((WindowManager) getSystemService(WINDOW_SERVICE)).getDefaultDisplay();

           

/* get infos */

int width = display.getWidth();

int height = display.getHeight();

int orientation = display.getOrientation();