Lost In Space

--How to check SQL Server log
--Hot to Debug T-SQL in SQL Server
a)

SELECT execution_count, st.text

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

WHERE st.text like '%FETCH NEXT FROM outerCursor INTO%'

b) How to: Debug T-SQL Using the SQL Server Management Studio

still from Visual Studio (Pro/Team, not Standard or Express)

c) from Debugging SQL Server 2005 Stored Procedures in Visual Studio

• Direct Database Debugging - from Visual Studio's Server Explorer, right-click on a database object
  and choose to step into the object. For example, when right-clicking on a stored procedure, the context menu includes a menu option titled "Step Into Stored Procedure."
• Application Debugging - with application debugging you can set breakpoints within a database object. When the associated ASP.NET application is debugged and the database object invoked, Visual Studio's debugger pauses the application's execution as the breakpoint it hit, allowing us to step through the object's statements one at a time.
• Debugging from a SQL Server Project - Visual Studio offers a SQL Server Project type. This project can include both T-SQL and managed database objects and these objects can be debugged by debugging the SQL Server Project itself.

• local database
• remove database

to do a Application Debugging

• Add breakpoints to the database object(s) that you want to debug. A database object will only be debugged if it contains a breakpoint. For example, you cannot "Step Into" a stored procedure from application code that calls the stored procedure. Rather, you must explicitly set a breakpoint within the stored procedure itself.
• Configure the application to debug SQL Server objects. For 'DataTest NoExcel', Properties, Debug, Enable Debuggers, check box 'Enable SQL Server debugging'; for ASP.NET, Property Pages, Start Options, Debuggers, check ASP.NET and SQL Server.
  
• Update the connection string to disable connection pooling. Connection pooling is a performance enhancement that allows an application to connect to a database from an existing pool of connections. This feature, if enabled, does not correctly construct the debugging infrastructure needed on the connection taken from the pool. Since connection pooling is enabled by default, we must update our connection string to disable connection pooling during the timeframe that application debugging
  is being used. (After you've completed debugging the SQL Server objects via application debugging be sure to reinstate connection pooling.) To accomplish this simply tack on the attribute Pooling=false to you existing connection string

--How to error handling in T-SQL in SQL Server

a) @@ERROR
b) T-SQL Error Handling in SQL Server 2005
deadlock
try ... catch
TRANSACTION

--How to avoid cursor in T-SQL
a) WHILE in TSQL Ref: How Developers Can Avoid Transact-SQL Cursors
WHILE: 9 sec CURSOR: 17 sec
b) ref:
Performance tunning in SQL Server TSQL Cursors
Use WHILE LOOPS Use temp tables Use derived tables Use correlated sub-queries Use the CASE statement Perform multiple queries
c) Increase your SQL Server performance by replacing cursors with set operations d) Robyn Page's SQL Server Cursor Workbench
--Deadlocks

--Misc

a). temptable in SQL Server

TABLE variable type

b) SQL Server, Server roles:

bulkadmin

dbcreator

diskadmin

processadmin

public

securityadmin

serveradmin

setupadmin

sysadmin

c) store database connection string encrypted,

Data protection API(DPAPI)

d) All input is evil

HttpUtility.Htmlencode

e) no sa

SELECT-only account for data retrieval

EXECUTE-only account for stored procedures

| Next »

| Next »

| Next »

| Next »

| Next »

| Next »

| Next »

| Next »

| Next »