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();

Android: Dial a phone number

import android.app.Activity;

import android.content.Intent;

import android.net.Uri;

import android.os.Bundle;

import android.view.KeyEvent;

import android.view.View;

import android.widget.Button;

import android.widget.EditText;

import android.widget.LinearLayout;

public class DialANumber extends Activity {

      EditText mEditText_number = null;

      LinearLayout mLinearLayout_no_button = null;

      Button mButton_dial = null;

     

      @Override

      public void onCreate(Bundle savedInstanceState) {

            super.onCreate(savedInstanceState);

            mLinearLayout_no_button = new LinearLayout(this);

           

            mEditText_number = new EditText(this);

            mEditText_number.setText("5551222");

            mLinearLayout_no_button.addView(mEditText_number);

            mButton_dial = new Button(this);

            mButton_dial.setText("Dial!");

            mLinearLayout_no_button.addView(mButton_dial);

            mButton_dial.setOnClickListener(new View.OnClickListener() {

                  public void onClick(View v) {

                             performDial();

                  }

            });

           

            setContentView(mLinearLayout_no_button);

      }

     

      public boolean onKeyDown(int keyCode, KeyEvent event) {

            if (keyCode == KeyEvent.KEYCODE_CALL) {

                  performDial();

                  return true;

            }

            return false;

      }

     

      public void performDial(){

            if(mEditText_number!=null){

                  try {

                        startActivity(new Intent(Intent.ACTION_CALL, Uri.parse("tel:" + mEditText_number.getText())));

                  }

                  catch (Exception e)

                  {

                        e.printStackTrace();

                  }

            }

      }

}