Export Microsoft SQL Server data to SQLITE

At time you need to interact with SQLITE and normally there are tools which are not free but i have found a tool which is very useful when you need to export data from Microsoft SQL Server to SQLITE.

You need to have a login for the mentioned below site, signup and download the utility.

This tool was made by Liron Levi.

Import MYSQL Data to Microsoft SQL Server 2005

At times you my need to interact with mysql database and the method given below is very easy for Microsoft SQL Server guys to do stuff with mysql database.

1) You need to have a mysql instance configured.

2) Install the MySQL ODBC Driver for Microsoft SQL Server

3) Add MySQL Instance as a linked server to Microsoft SQL Server by issuing the mentioned below command.

4) Access and Import you MySQL Data right from Microsoft SQL Server Management Studio

Claim Unused space from table after Deletion of rows of after droppping columns

If you deleted rows and space used is same as previous then first thing you need to do is Rebuild Clustered Index.

It will release most of the unused space.

Secondly if you have dropped any variable length column then you can run the mentioned below command.

DBCC CLEANTABLE (AdventureWorks,"Production.Document", 0)

Search Suffix using Full Text Search in SQL Server 2005

A solution to search Suffix is given below as its a limitation in FTS that FTS cannot search suffix.

Its not recommended and not efficient as it requires another column but its a solution which can help you out in some cases.


1. Add a column in your table which stores the reverse of the string
like
SET NewColumnName = REVERSE(ColumnName)

Identify missing indexes in sql server 2005

SELECT DISTINCT DB_NAME(Database_ID) [Database]
,OBJECT_NAME(Object_ID) [Table]
,Equality_Columns
,Included_Columns
FROM sys.dm_db_missing_index_details mid
WHERE Database_ID = DB_ID()
ORDER BY 2

Computed Columns in SQL Server 2005

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.

For example, in the AdventureWorks sample database, the TotalDue column of the Sales.SalesOrderHeader table has the definition: TotalDue AS Subtotal + TaxAmt + Freight.

Users get blocked when access same table while getting Unique IDs

--Table which will have the Unique IDs

CREATE TABLE dbo.CounterTable

(

Counter int IDENTITY(1,1) NOT NULL

)
GO
--Procedure which Inserts unique IDs in table and returns the New ID

Simple cursor Example in Microsoft SQL Server

DECLARE @String nVARCHAR(MAX)
DECLARE @getInputBuffer CURSOR
SET @getInputBuffer = CURSOR FOR
SELECT Text FROM Table

OPEN @getInputBuffer

FETCH NEXT
FROM @getInputBuffer INTO @String

WHILE @@FETCH_STATUS = 0

Get foreign keys of a table in SQL Server

SELECT f.name AS ForeignKey

,OBJECT_NAME(f.parent_object_id) AS TableName

,COL_NAME(fc.parent_object_id

,fc.parent_column_id) AS ColumnName

,OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName

,COL_NAME(fc.referenced_object_id

,fc.referenced_column_id) AS ReferenceColumnName

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc

ON f.OBJECT_ID = fc.constraint_object_id

WHERE OBJECT_NAME (f.referenced_object_id)   = 'TableName'

ORDER BY 2

Find /Remove New Line or Carriage Return Character in String or Text in SQL Server

1)

DECLARE @NewLine char(2)

SET @NewLine=char(13)+char(10)

SELECT *

FROM TABLENAME

WHERE CHARINDEX(@NewLine,ColumnName) > 0

2)

DECLARE @NewLine char(2)

SET @NewLine=char(13)

SELECT *

FROM TABLENAME

WHERE CHARINDEX(@NewLine,ColumnName) > 0

3)

DECLARE @NewLine char(2)

SET @NewLine=char(10)

SELECT *

FROM TABLENAME

WHERE CHARINDEX(@NewLine,ColumnName) > 0

Delete Duplicate Records Using Common Tabel Expression

there are many ways to delete the duplicates in a table but sometimes the group by doesnt seem to work so you can try out the new feature of Microsoft SQL Server "Common Table Expression"

With Dups as 

(

SELECT  row_number() over (

partition by ColumnName 

order by  ColumnName ) as RowNum

FROM Table

)

DELETE 

FROM Dups 

WHERE rownum > 1

Solution for Large volume of backup storage on tape with high performance and security

Linear Tape-Open (or LTO) is a magnetic tape data storage technology originally developed in the late 1990s as an open standards alternative to the proprietary magnetic tape formats that were available at the time. Seagate, Hewlett-Packard, and IBM initiated the LTO Consortium, which directs development and manages licensing and certification of media and mechanism manufacturers. The standard form-factor of LTO technology goes by the name "Ultrium", the original version of which was released in 2000 and could hold 100 GB of data in a single cartridge. The most recent version was released in 2007 and can hold 800 GB in the same size cartridge

http://h10010.www1.hp.com/wwpc/us/en/en/WF05a/12169-304612-3446236-3446236-3446236-3454484.html

http://en.wikipedia.org/wiki/Linear_Tape-Open

Manage web sessions in database with .net 2.0

This is the utility placed in the .net framwork v2.0 folder within the windows directory.

just run the command and you will get a script to make a db and it will automatically create the required tables and procedured in it.
Just run the command and execute the sql script generated by the command on the target server.

aspnet_regsql.exe
-S servername
-U username
-P password
-ssaddd
/*just add -ssaddd parameter to tell sql that you are going to add the functionalities */
-sqlexportonly c:\sqlscript.txt
/* this will generate the script to the specified path */
-sstype p
/* in sstype paramerter specifying P will mention that you are enforcing persistant database*/

command will be like


aspnet_regsql.exe -S myserver -U username -P password -ssaddd -sqlexportonly c:\sqlscript.txt -sstype p

There are some useful articles which you must read to get some detailed knowledge.


Reference Links :
http://www.developer.com/db/article.php/10920_3595766_3
http://msdn.microsoft.com/en-us/library/ms229862(VS.80).aspx
http://msdn.microsoft.com/en-us/library/h6bb9cz9(VS.71).aspx

Windows Restart because RPC Service Restarts Unexpectedly

Dear Fellows !

This is because of a Worm attack on your system, it can be resolved if you take the mentioned below steps carefully.

1. Ensure Latest Service pack for your operating system.
2. Find MBlast.exe in your task manager and stop it
3. Go to Control Panel-> Administrative Tools -> Services and Find "Remote Procedure Call (RPC)", check properties and under Recovery Select "Take No Action" under all failure conditions.
4. Now your computer will not restart but the worm is still there you need to remove it so that your system should come to a normal position.

5. Download Patch from the mentioned link
http://www.symantec.com/content/en/us/global/removal_tool/threat_writeups/FixBlast.exe

6. Follow the instruction of Symantec for Removal of the Worm at the mentioned below link.
http://www.symantec.com/security_response/writeup.jsp?docid=2003-081119-5051-99

Database Comparison Tool

Dear Fellows !

Here is a great tool offered by StarInix which comperes Microsoft SQL Server databases for FREE !

Download Link is mentioned below:

http://dw.com.com/redir?edId=3&siteId=4&oId=3000-10254_4-10614062&ontId=10254_4&spi=4e7e60e4583edfb5928c220e5e087238&lop=link&tag=tdw_dltext&ltype=dl_dlnow&pid=10621794&mfgId=6289143&merId=6289143&pguid=CuOMGAoPjFsAAHarY6YAAAEr&destUrl=http%3A%2F%2Fdownload.cnet.com%2F3001-10254_4-10614062.html%3Fspi%3D4e7e60e4583edfb5928c220e5e087238%26part%3Ddl-StarInixD