When to use 32-bit debugger and when to use 64-bit debugger

Live User mode debugging
-------------------------------
Processes on 64 bit machine (irrespective of 32/64-bit) : use 64 bit Windbg
Others - 32 bit windbg
http://stackoverflow.com/questions/1342099/lost-in-windbg-with-64-bit-dump-on-32-bit-machine

Dump analysis
----------
1.Use 32-bit tools (debugdiag, procdump,adplus+cdb) to capture dumps of 32-bit processes (whether they are on 32-bit machine (or) in WOW64 mode on 64-bit macine)
Use 32-bit windbg to analyze these dumps

2.Use 64-bit tools to capture 64-bit process dump and use 64-bit windbg to analyze.
http://blogs.msdn.com/b/tess/archive/2010/09/29/capturing-memory-dumps-for-32-bit-processes-on-an-x64-machine.aspx

32 bit process

Increasing Query timeout in .Net C#

SqlCommand command = new SqlCommand(qry, conn);
 command.CommandTimeout = 60 * 60 * 2; //2 hours

By Default CommandTimeout is 60 seconds. If the query is taking more than 60seconds, sqlcommand exits with an error.

The request filtering module is configured to deny a request that exceeds the request content length

HTTP Error 400 Bad request 
Unable to upload larger/bigger files on to Web Server (IIS)

Include the following setting in the application's Web.Config file.

<system.webServer>
        <security>
            <requestFiltering>
                <requestLimits maxAllowedContentLength="2000000000" />
            </requestFiltering>
        </security>
    </system.webServer>

This setting allows you to upload a file of size up to 2000MB .

The same can be achieved by the following command too.
%windir%\system32\inetsrv\appcmd set config "Default Web Site/<Your WebSite>" -section:requestFiltering -requestLimits.maxAllowedContentLength:2000000000

TABLE HINTS SQL SERVER

SQL server doesn't allow to specify index hints on the table that is being updated in an UPDATE statement.

RENAMING COLUMN/TABLE IN SQL SERVER

Renaming a column
sp_rename 'TABLENAME.[COLUMNNAME]','NewColumnName','COLUMN'

Renaming a table/stored procedure any other object

sp_rename 'oldObjectName', 'newObjectName'

Bulk Inserting/Loading data from Flat files

Following are the possible ways in which one can bulk insert data from flat/text files into SQL Server.
1. Using Command line "bcp" utility
2. Using Transact-SQL "Bulk insert" command
3. Using SSIS/DTS packages

DTS packages have certain programmability limitations and don't always offer the best performance.

BULK INSERT gives optimal performance over DTS/SSIS packages.

BULK INSERT
----------------
Bulk insert doesn't create the table automatically. It expects the table to be existed already.

Format file structure














For more details on format file, visit the below page
http://msdn.microsoft.com/en-us/library/ms179250.aspx

Scenario 1: n - # columns in table, m - #columns in text file (1:1)
assume n=m = 3
Then format file

9.0
3
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       100      "\t"    2     Col2         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Scenario 2:
n>m, To skip a table column
n=3, m=2

Approach 1:

9.0
2
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

Notice that row for "Col2" is missing.

Approach 2:

9.0
3
1       SQLCHAR       0       7       "\t"     1     Col1         ""
2       SQLCHAR       0       0        ""      0     Col2         ""
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS

For more details on skipping a database column, visit the below page
http://msdn.microsoft.com/en-us/library/ms179250.aspx

Scenario 3:
n<m, 9.0 4 1 SQLCHAR 0 7 "," 1 col1 "" 2 SQLCHAR 0 100 "," 0 ExtraField SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 100 "," 2 col2 SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 100 "\r\n" 3 col3 SQL_Latin1_General_CP1_CI_AS For more details on skipping a data filed, visit the below page http://msdn.microsoft.com/en-us/library/ms187908.aspx  


Scenario 4: n=m=4 , order of the table columns is not the same as data fields

9.0
4
1       SQLCHAR       0       100     ","     3     Col3               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100     ","     2     Col2               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       7       ","     1     Col1               ""
4       SQLCHAR       0       100     "\r\n"  4     Col4               SQL_Latin1_General_CP1_CI_AS

For more information on this scenario, visit the below page
http://msdn.microsoft.com/en-us/library/ms191175.aspx

Cannot bulk load because the file could not be read. Operating system error code (null) SQL Server

Possible reasons
1. Format file expects an end of line after the last row. i.e last row should be empty
2. Column order flat file/data table should be correct. i.e content of the data file should be correct.

Model db in SQL Server

"model" db is created by SQL Server default installation. This db works as a template for any new User databases created.

i.e if you set "Initial file size" attribute of "model" db to 100MB, any new databases created hence forth will have a default value of 100MB for data file size. Similarly any other attributes of database will be inherited from this "model" db.

Running Command line commands from T-SQL

xp_cmdshell can be used to execute command line commands from T-SQL / SQL procedures.
This command can be used if you would like to use "bcp" utility from T-SQL or procedures and if you would like to do any mx record validation, etc...
eg:
xp_cmdshell "dir"

By default "xp_cmdshell" will be disabled.

How to enable
---------------
EXEC sp_configure 'show advanced options', 1
GO

RECONFIGURE
GO

-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO

-- To update the currently configured value for this feature.
RECONFIGURE
GO

For more details on how to enable, refer to the below page.
http://msdn.microsoft.com/en-us/library/ms175046.aspx