SQL 2005 to delete lots of data in batches

If you want to delete lots of data (millions), Running one query causes the TransactionLog to grow with huge size.

You have 2 solutions for this problem:
1. If you can your data is offline:

• Copy the rows you want to keep to a temporary table
• Drop the original table
• Rename the temporary table to original name
• Reinstate any indexes

2. Second approach is to delete the rows in a loop. Delete a modest number each time round the loop. Keep looping until no more rows exist to delete.

• You will need to either backup the TLog frequently during this process (to stop it extending to a vast size), or change the RECOVERY MODEL to SIMPLE whilst this is running, and back to FULL again after it finished.
• If this batch works 24 hours/7 days you should also put a WAIT for 5 seconds or so inside the loop so that during each iteration other connected users get "their chance"

SQL Script to delete rows in loop:(needs local variables declaring)

SELECT @intRowsToDelete = COUNT(*) -- Number of rows to be deleted FROM dbo.MyTable WHERE ... MyDeleteCriteria ...
WHILE @intRowCount > 0 AND @intErrNo = 0 AND @intLoops > 0

BEGIN
SELECT @dtLoop = GetDate()
SELECT @strSQL =
SET ROWCOUNT @DEL_ROWCOUNT -- number of delete rows / iteration
DELETE D FROM dbo.MyTable AS D WHERE ... MyDeleteCriteria ...
SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT
SET ROWCOUNT 0 -- Reset batch size to "all"
SELECT @intRowsToDelete = @intRowsToDelete - @intRowCount,
@intLoops = @intLoops - 1
-- Debugging usage only:
PRINT 'Deleted: ' + CONVERT(varchar(20), @intRowCount)
+ ', Elapsed: ' + CONVERT(varchar(20), DATEDIFF(Second, @dtLoop, GetDate()))
+ ' seconds,' + ' remaining to delete=' + CONVERT(varchar(20), @intRowsToDelete)
+ ', Loops left=' + CONVERT(varchar(20), @intLoops)
WAITFOR DELAY '000:00:05' -- 5 seconds for other users to gain access
END

TFS Cache

If you changed schemas TFS client, will face many strange errors like {Item already exists, out of memory, cannot run query}
Cause:
Visual Studio and Team Explorer provide a caching mechanism which can get out of sync.
Solution:
For Windows Vista delete contents of this folder
C:\Users\{your account}\AppData\Local\Microsoft\Team Foundation\1.0\Cache
C:\Users\{your account}\AppData\Local\Microsoft\Team Foundation\2.0\Cache
For Windows Xp, 2003 delete contents of this folder
C:\Documents and Settings\{your account}\Local Settings\Application Data\Microsoft\Team Foundation\1.0\Cache
C:\Documents and Settings\{your account}\Local Settings\Application Data\Microsoft\Team Foundation\2.0\Cache

How do you delete a work item?

There is no permanent delete feature for Work Items in this version of the product (2005, 2008). Instead, you put the WI into a terminal state (closed, obsolete, etc.)


There is now a tool in codeplex for this, called TFS Power Pack:
KillBill - Stops a Team Build currently running on a build server.
WorkItem Terminator - Permanently deletes a work item from the TFS database.
http://www.codeplex.com/Wiki/View.aspx?ProjectName=TfsPowerPack

Or delete them via this SQL statement.

Declare @DELID int set @DELID = @WorkItemId

DELETE FROM [TfsWorkItemTracking].[dbo].[WorkItemLongTexts] WHERE ID = @DELID
DELETE FROM [TfsWorkItemTracking].[dbo].[WorkItemsAre] WHERE ID = @DELID
DELETE FROM [TfsWorkItemTracking].[dbo].[WorkItemsWere] WHERE ID = @DELID
DELETE FROM [TfsWorkItemTracking].[dbo].[WorkItemsLatest] WHERE ID = @DELID

How can you access Running instance of IE and refersh page

First you need to refer SHDocVw.dll and MSHTML.dll. In Visual Studio, go to Project, Add Reference, and then select the COM tab. select these dlls.

Csharp Code:

string myUrl = www.google.com;
SHDocVw.WebBrowser m_browser = null;
SHDocVw.ShellWindows shellWindows = new SHDocVw.ShellWindowsClass();
string filename;
foreach (SHDocVw.WebBrowser ie in shellWindows)
{
filename = Path.GetFileNameWithoutExtension(ie.FullName).ToLower();
if (filename.Equals("iexplore"))
{
m_browser = ie;
//Assign Browser Document
mshtml.IHTMLDocument2 myDoc = (mshtml.IHTMLDocument2)m_browser.Document;
//URL Location
string myLocalLink = myDoc.url;
if (myLocalLink == myUrl)
m_browser.Refresh();
//break;
}

You also can do other actions using m_browser, m_browser.Document to control content ofthis page.

How to resolve the RUP error: Applet RupPresenterApplet notinited

Problem
When publishing the RUP template from IBM® Rational Unified Process builder to IIS Web server on Windows 2003, the published site works when accessing it directly but does not work when it is accessed through the web server. The tree browser on the left hand side of the site gives the error: Applet RupPresenterApplet notinited.

Cause
IIS cannot serve some of the RUP files with special file extensions like .properties, .cfg, .layout, etc. (See below for more info.). Because of this reason the applet cannot download and initialise itself correctly.

Solution
The solution to this problem is to re-configure IIS to add more mime types, shown as follows:
1. Go to Administrative Tools -> IIS Manager, right click the site and bring up the properties dialog box as below:
2. Go to the HTTP Headers tab, and click on MIME Types... button to the following dialog box.
3. Click on the New... button to bring up repeatedly the following MIME Type dialog box and add five extension/MIME type pairs shown in the above list.
4. Close these dialog boxes and apply the changes.
5. Restart the IIS server.
6. Close all sessions of browsers and reopen a new browser to load the RUP.

Sort ascending or descending dynamics

You 2 Ways to sort ascending or descending dynamics depends on variable:
First way:
SELECT [Id]
,[Last_Update_Dt]
FROM [Tellas_PhotoGallery].[dbo].[IMAGE]
order by CAST([Last_Update_Dt] AS INT)*-1

SELECT [Id]
,[Last_Update_Dt]
FROM [Tellas_PhotoGallery].[dbo].[IMAGE]order by [Last_Update_Dt] DESC
This technique of multiplying either 1(ASC) or -1(DESC), can be used in many queries, and of course is not limited to datetimes.
Second way
DECLARE @SortOrder int
SET @SortOrder = 1
SELECT [Id]
,[Last_Update_Dt]
FROM [Tellas_PhotoGallery].[dbo].[IMAGE]
ORDER BY
CASE
WHEN @SortOrder = 1 THEN (RANK() OVER (ORDER BY [Last_Update_Dt] ASC))
WHEN @SortOrder = 2 THEN (RANK() OVER (ORDER BY [Last_Update_Dt] DESC))
END

Article below gives you ideas to create dynamic query Sorting and Where Clause without conactenate string:
http://www.sqlteam.com/article/dynamic-order-by

How can we Send SMS?

Before we talk about sending SMS we need to tell you some definitions:

SMSC (Short Message Service Centre):
is a network element in the mobile telephone network which delivers SMS messages.
When a user sends a text message (SMS message) to another user, the message gets stored in the SMSC which delivers it to the destination user when they are available. This is a store and forward op

SMS Gateway:
Average rate of sending SMS for each short number account is 1 message/sec, If you sumbit messages with high rates all messages will failed. SMS gateway application to facilitate the SMS traffic between businesses and mobile subscribers.

Protocols: The Value-added service provider (VASP) providing the content submits the message to the mobile operator's SMSC(s) using a TCP/IP protocol such as the short message peer-to-peer protocol (SMPP) or the External Machine Interface (EMI). The SMSC delivers the text using the normal Mobile Terminated delivery procedure. The subscribers are charged extra for receiving this premium content, and the amount is typically divided between the mobile network operator and the VASP either through revenue share or a fixed transport fee.

Nokia PC Suite:
is a software package used to establish an interface between Nokia mobile devices and computers that run Microsoft Windows operating system. It can be used to transfer music, photos and applications. It can also be used to send Short Message Service (SMS) messages or act as a modem to connect the computer to the Internet. A mobile phone can be connected by USB, Bluetooth, or infrared.

If you have Application and want to add SMS service to your application, You can send this message by 4 ways:
1. Use Nokia PC Suite to send Short Message Service (SMS) messages from your computer using your SIM card.

2. Use any third party which made applications to send bulk SMS,
This way needn't any knowledge of connection of the operator, It only needs to learn how can you deal with its API.
example of this third party:
http://www.clickatel.com/

3. Use a gateway to connect to SMSC Operators and use its API to send your message.
To use this gateway:
a. Make a deal with operatot to send your SMS through his SMSC.
b. You must have knowledge of mobile network Protocols to configure connection of smsc.
example of this third party:
http://www.kannel.org/ (Free)
http://www.nowsms.com/

4. Use SDK from Third Party to connect to the operator, You will use this SDK to build your gateway application.
Your application Functions:
1. Connect to SMSC
2. Manage rates of sending messages.
3. Send/Receive Message.
4. Logs any actions (Sending/Receive/Notifications/Failure/Succeed)
example of this third party:
http://www.derdack.com/
http://www.devshock.com/ (Free but only for SMPP Protocol)

Notes:
Alias:
UCP Protocol allow you to set alias per each message, While SMPP needs your operator set this alias and will be constant for all messages.

Message Message size:
The maximum single text message size is either 160 7-bit characters, 140 8-bit characters, or 70 16-bit characters. Characters in languages such as Arabic, Chinese, Korean, Japanese or Slavic languages (e.g., Russian) must be encoded using the 16-bit UCS-2 character encoding (see Unicode).Larger content (Concatenated SMS, multipart or segmented SMS or "long sms") can be sent using multiple messages, in which case each message will start with a user data header (UDH) containing segmentation information. Since UDH is inside the payload, the number of characters per segment is lower: 153 for 7-bit encoding, 134 for 8-bit encoding and 67 for 16-bit encoding. The receiving handset is then responsible for reassembling the message and presenting it to the user as one long message. While the standard theoretically permits up to 255 segments, 6 to 8 segment messages are the practical maximum, and long messages are often billed as equivalent to multiple SMS messages.