SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Restoring Accidentally deleted or Updated Rows without Backup restore – Sql Server

In Reference to my previous post : Restoring Database to a previous point of time –Recommended for all DBA’s

have been looking some thing better to avoid restoring database and recover the deleted \ updated data for given tables.

Finally got the solution for the same. Hurray!!!

Following is the link to ultimate tool designed by Mr.Imran.

Restoring Deleted Rows – Sql Server

Restoring Modified Rows – Sql Server

Happy coding 🙂

Advertisements
Leave a comment »

Update Mutiple Tables in Single Query – Sql Server

Use Views – Simple 🙂

Reference: http://weblogs.sqlteam.com/brettk/default.aspx

I must say the blog mentioned above is SUPER!!!

NOT POSSIBLE to update multiple tables.

Leave a comment »

Important tools for Sql Server

http://weblogs.sqlteam.com/mladenp/archive/2007/11/20/Free-SQL-Server-tools-that-might-make-your-life-a.aspx

SSMS Tools Pack : Highly recommended

For Formating: http://www.tsqltidy.com/tsqltidySSMSAddin.aspx

 

Leave a comment »

Restoring Database to a previous point of time –Recommended for all DBA’s

The scenario

A coworker calls you in a panic because he accidentally deleted some production data, and he wants you to restore the lost records.

Read further

http://www.techrepublic.com/blog/datacenter/restore-your-sql-server-database-using-transaction-logs/132

Preferable:

http://www.linglom.com/2009/07/03/solved-system-data-sqlclient-sqlerror-the-tail-of-the-log-for-the-database-dbname-has-not-been-backed-up/

3 Comments »

Create Comma Separated List- Sql tips and tricks

Create list of Integer type column


DECLARE @listStr VARCHAR(MAX)

SELECT @listStr = COALESCE(@listStr+',' ,'') + CAST(ColumnName AS Varchar(10))
FROM TableName
SELECT @listStr
GO

Replace ColumnName and TableName

Leave a comment »

Function to replace all special characters – Sql Sever

CREATE FUNCTION dbo.RemoveSpecialCharacter(@Str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN

	SET @Str = Replace(@str, Char(13), ' ')
--replace all enters with space
	SET @Str = Replace(@str, Char(9), ' ')
--replace all tabs with space
	SET @Str = Replace(@str, Char(10), ' ')
--replace all line feeds with space

	RETURN @Str

END

Useful function to be used for data export to excel sheet(Using SSIS) as the “Enter” makes the data to move to next line and the reports gets distoreted.

Leave a comment »

SSIS Container Good Examples

For Each Container Example

http://beyondrelational.com/modules/2/blogs/106/posts/11122/loop-through-each-record-in-a-text-file-recordset-destination.aspx

Mapping SP variable in OLEDB Source–>

Leave a comment »

Useful Special Characters For Sql Server Dynamic Queries

Char(44) — comma
Char(39) — Single Quote
char(9) — Tab
char(10) — Line feed
char(13) — Enter\Carriage return
Char(32) — Space

Go to SSMS.. Type :


Select Char(44)

and see the magic 🙂

Leave a comment »