Session Timeout in server

http://www.codeproject.com/KB/session/Sessiontimeoutnotworking.aspx

DOS command in T-sql (xp_cmdshell)

EXEC master..xp_cmdshell 'dir c:'
EXEC master..xp_cmdshell 'RENAME E:\"HR Reports"\"Master Sheet"*.xls "Master Sheet".xls'

T-SQL RollUp

--Sample Script

SELECT CASE WHEN (GROUPING(Data_type) = 1) THEN 'ALL'
ELSE ISNULL(Data_type, 'UNKNOWN')
END AS Data_type,
SUM(Type_id) AS Type_idSum
FROM Staging.Data_Type
GROUP BY Data_type WITH ROLLUP

Min & Max percent in T-SQL

--top 20%
SELECT min(percent_outbound_calls)
FROM (SELECT TOP 20 PERCENT percent_outbound_calls
FROM scorecard_outlier
ORDER BY percent_outbound_calls desc)A


--bottom 20%
SELECT max(percent_outbound_calls)
FROM (SELECT TOP 20 PERCENT percent_outbound_calls
FROM scorecard_outlier
ORDER BY percent_outbound_calls asc)A

percentile calculation in t-sql

select max(PERCENT_OUTBOUND_CALLS) from (
SELECT top 20 PERCENT PERCENT_OUTBOUND_CALLS FROM scorecard_outlier ORDER BY PERCENT_OUTBOUND_CALLS asc )a

SQL DB restore from MDF file

EXEC sp_attach_single_file_db @dbname = 'HealthCheck',
@physname = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\HealthCheck.mdf'

AcquireConnection error in SSIS(Remotly)

1. Click Start, click Control Panel, click Administrative Tools, and then click Component Services. Expand Component Services, and then expand Computers.

2. Right-click My Computer, and then click Properties.

3. Click the MSDTC tab, and then click Security Configuration.

4. Make sure that the following check boxes are selected:

• Network DTC Access

• Allow Remote Clients

• Allow Remote Administration

• Allow Inbound

• Allow Outbound

• No Authentication Required

5. Make sure that the DTC Logon Account is set to NT AUTHORITY\NetworkService.

6. Add MSDTC.exe as an exception in Windows Firewall.

Blogger Buzz: Blogger integrates with Amazon Associates

Blogger Buzz: Blogger integrates with Amazon Associates

SQL Shortcut keys

CTRL-SHIFT-F2 -- Clear all bookmarks.
CTRL+F2 -- Insert or remove a bookmark (toggle).
F2 -- Move to next bookmark.
SHIFT+F2 -- Move to previous bookmark.
ALT+BREAK -- Cancel a query.
CTRL+O -- Connect.
CTRL+F4 -- Disconnect.
CTRL+F4 -- Disconnect and close child window.
ALT+F1 -- Database object information.
CTRL+SHIFT+DEL -- Clear the active Editor pane.
CTRL+SHIFT+C -- Comment out code.
CTRL+C or Ctrl+Insert -- Copy
CTRL+X or Shift+Del -- Cut
SHIFT+TAB -- Decrease indent.
CTRL+DEL -- Delete through the end of a line in the Editor pane.
CTRL+F -- Find.
CTRL+G -- Go to a line number.
TAB -- Increase indent.
CTRL+SHIFT+L -- Make selection lowercase.
CTRL+SHIFT+U -- Make selection uppercase.
CTRL+V or Shift+Insert -- Paste.
CTRL+SHIFT+R -- Remove comments.
F3 -- Repeat last search or find next.
CTRL+H -- Replace.
CTRL+A -- Select all.
CTRL+Z -- Undo.
F5 or Ctrl + E -- Execute a query.
F1 -- Help for Query Analyzer.
SHIFT+F1 -- Help for the selected Transact-SQL statement.
F6 -- Switch between query and result panes.
Shift+F6 -- Switch panes.
CTRL+W -- Window Selector.
CTRL+N -- New Query window.
F8 -- Object Browser (show/hide).
F4 -- Object Search.
CTRL+F5 -- Parse the query and check syntax.
CTRL+P -- Print
CTRL+D -- Display results in grid format.
CTRL+T -- Display results in text format.
CTRL+B -- Move the splitter.
CTRL+SHIFT+F -- Save results to file.
CTRL+R -- Show Results pane (toggle).
CTRL+S -- Save
CTRL+SHIFT+INSERT -- Insert a template.
CTRL+SHIFT+M -- Replace template parameters.
CTRL+L -- Display estimated execution plan.
CTRL+K -- Display execution plan (toggle ON/OFF).
CTRL+I -- Index Tuning Wizard.
CTRL+SHIFT+S -- Show client statistics
CTRL+SHIFT+T -- Show server trace.
CTRL+U -- Use database

find sqlserver Edition lervel(like SP1)

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

SSRS deployment and open

to open Report in local
http://localhost/Reports


Deploy report with
TargetServerURL as : http://localhost/ReportServer
this we can update in Report properties window

SSIS For loop to import multiple files to SQL server

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

Example of MERGE in SQL Server 2008

Example of MERGE in SQL Server 2008

MERGE Production.ProductInventory AS [pi]
USING (
SELECT ProductID,
SUM(OrderQty) AS OrderQty
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.OrderDate = GETDATE()
GROUP BY ProductID
) AS src (ProductID, OrderQty) ON src.ProductID = [pi].ProductID
WHEN MATCHED AND src.OrderQty = 0
THEN DELETE;
WHEN MATCHED
THEN UPDATE SET [pi].Quantity = src.OrderQty
WHEN NOT MATCHED
THEN INSERT VALUES (src.ProductID, src.OrderQty)