Error While Restoring the SQL SERVER Data base

Issues while restoring the data base in Sql server :

While restore the data base  i am getting the following error :

when i restore the sql database i am getting error

TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore failed for Server 'MAIN'. (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The backup set holds a backup of a database other than the existing 'abc' database. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ 

Answer: 

You're trying to overwrite existing database like adventure works with a backup of a different database or a backup from another server.  The default action is not to allow that to happen.

 If you're sure you're doing the right thing, add the WITH REPLACE option to the RESTORE DATABASE command  shown below image and it will proceed.  You may also need WITH MOVE options if the backup came from a database whose files were in different locations that the like  adventure work database. 

Real time scenario in ssis

One of the real time scenario: 

I am trying to come up with the best and most efficient way to copy more than 700+ tables from an IBM DB2 iSeries schema to SQL Server. I have tried the followings:

1. I have exported all the 700+ tables into csv files but I am not able to upload them all in one batch to SQL. I can upload one table at time but it too time consuming

2. Created SSIS package to directly copy table form IBM to SQL but that's gain working for only one table at a time

Any recommendations as what tool(s) or methods to use for copying/uploading multiple tables from IBM to SQL?

Display Line Numbers in Sqlserver Management Studio

Display Line Numbers in Sqlserver Management Studio:

Before explain the display  line number in sql server management studio. normally the management studio will be 

so .for this we are going to add line numbers.Let us see how to enable or disable the display of line numbers in SQL Server Management Studio's Query Window.

1. Open the SQL Server Management Studio. Click Tools -> Options from the drop down menu as shown in the below image.

2. In the Options dialog box on the left side panel expand the Text Editor option and expand Transact-SQL. Next select General option as shown in the below image.

3. In the General page's right side panel you need to select the check box "Line numbers" under the Display section as shown in the above snippet and click OK to save the changes.

4. Going forward whenever you open a new query window in SQL Server Management Studio you will see line numbers displayed. In the below snippet you will see that line numbers are displayed in the sample T-SQL code. This feature is very helpful when you need to debug an error in your T-SQL code, especially in scenarios when SQL Server mentions that error is found on a particular line number.

5. If this feature in not turned and you need to go to a particular line number press CTRL + G to open Go To Line dialog box; enter line number and click OK as shown below.

         

6. To turn off the line number feature in SQL Server Management Studio you need to navigate to Tools | Options | Text Editor | General | Display and uncheck the Line Number check box and click the OK button to save the changes so that next time you open a new query window in SSMS line numbers will not be displayed

SQL Server Cursor Example:

In some circles cursors are never used, in others they are a last resort and in other groups they are used regularly.  In each of these camps they have different reasons for their stand on cursor usage.  Regardless of your stand on cursors they probably have a place in particular circumstances and not in others.  So it boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor based processing is appropriate or not.  To get started let's do the following:

• Look at an example cursor
• Break down the components of the cursor
• Provide additional cursor examples
• Analyze the pros and cons of cursor usage

Example SQL Server Cursor

Here is an example cursor from tip Simple script to backup all SQL Server databases where backups are issued in a serial manner:

DECLARE @name VARCHAR(50) -- database name  DECLARE @path VARCHAR(256) -- path for backup files  DECLARE @fileName VARCHAR(256) -- filename for backup  DECLARE @fileDate VARCHAR(20) -- used for file name 
SET @path = 'C:\Backup\'  
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
DECLARE db_cursor CURSOR FOR  
SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')  
OPEN db_cursor   FETCH NEXT FROM db_cursor INTO @name   
WHILE @@FETCH_STATUS = 0   BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   END   

CLOSE db_cursor   DEALLOCATE db_cursor

SQL Server Cursor Components

Based on the example above, cursors include these components:

• DECLARE statements - Declare variables used in the code block
• SET\SELECT statements - Initialize the variables to a specific value
• DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
  • NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement.  This could be 1 or many variables and associated columns.
• OPEN statement - Open the cursor to begin data processing
• FETCH NEXT statements - Assign the specific values from the cursor to the variables
  • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
• WHILE statement - Condition to begin and continue data processing
• BEGIN...END statement - Start and end of the code block
  • NOTE - Based on the data processing multiple BEGIN...END statements can be used
• Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
• CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
• DEALLOCATE statement - Destroys the cursor

Conditional Split Transformation in SSIS

Conditional Split Transformation in SSIS:

Why Conditional Split Transformation Task is Use ?

Conditional Split Transformation task use when want to direct subsets of data to different locations, such as sending centrally collected data to appropriate branches or within the dataflow when we want to apply different business rules to certain subsets of the data, before merging this Dataset.

This is most useful transformation in Data Flow Task where we can traverse through different condition.

In Conditional Split Transformation incoming data can be split into multiple outputs as per the conditions specified. It has a default output too that handles all the rows that do not match to any conditions

How Conditional Split Transformation Task is look like ?

Features of Conditional Split Transformation Task

When we need to perform one to many operation we often use conditional transformation

Lets consider we need to perform various operation in same database

How to use Conditional Split Transformation Task ? and Demonstration of using Conditional Split Transformation Task ?

This is as simple as derived column transformation which have already discuss ..

Just drag out this task from Data Flow Tasks

Once we are ready to use we can configure as shown in following diagram

Various Properties For each Conditional Split Transformation Task ?

Following are various properties for Split Transformation

Reference link For Conditional Split Transformation Task

http://www.dotnetfunda.com/articles/article1214-conditional-split-in-ssis.aspx

For Each Loop Container in SSIS

For Each Loop Container in SSIS:

Why For Each Loop Container Task is Use ?

• For Each Loop container is falls under container and looping tasks
• Use containers like the For Each Loop and For Loop to execute a set of tasks multiple times.
• For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics.
• In short when we have to iteratively execute set of task we will insert all those task under For Loop Container and set the values accordingly
• The for each loop container acts as a repeating control flow in a package. Its operations are similar to work of For each keyword in any advanced programming language. We have a definite type of enumerator for each type of objects.
• Loop implementation in the For Each Loop Container is similar to the Foreach looping concept in various programming languages.

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

Enumerator is nothing but an iterator where an object that enables a programmer to traverse a container

SQL Server Integration Services provides the following enumerator types:

1. Foreach File Enumerator: It enumerates files in a folder. The plus point here is it can traverse through subfolders also.
2. Foreach Item Enumerator: It enumerates items in a collection. Like enumerating rows and columns in an Excel sheet.
3. Foreach ADO Enumerator: Useful for enumerating rows in tables.
4. Foreach ADO.NET Schema Rowset Enumerator: To enumerate through schema information about a data source. For example, to get list of tables in a database.
5. Foreach From Variable Enumerator: Used to enumerate through the object contained in a variable. (if the object is enumerable)
6. Foreach NodeList Enumerator: Used to enumerate the result set of an XML Path Language (XPath) expression.
7. Foreach SMO Enumerator: It enumerates through SQL Server Management Objects (SMO) objects.

How For Each Loop Container Task is look like ?

This is Second task in tab itself !!

Drag this out in your development plane

Features of For Each Loop Container Task

The following diagram shows a Foreach Loop container that has a File System task. The Foreach loop uses the Foreach File enumerator, and the File System task is configured to copy a file. If the folder that the enumerator specifies contains four files, the loop repeats four times and copies four files.

SSIS For Loop Container Task

SSIS For Loop Container Task:

Why For Loop Container Task is Use ?

• Name of the task itself explains most of of it !! For Loop container is falls under container and looping tasks
• Use containers like the For Each Loop and For Loop to execute a set of tasks multiple times.
• For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics. 
  In short when we have to iteratively execute set of task we will insert all those task under For Loop Container and set the values accordingly
• For loop task is the looping implementation of a task and also This task will evaluate an expression and loops through the process and until the evaluation goes to False.

The For Loop container uses the following elements to define the loop:

1. An optional initialization expression that assigns values to the loop counters.
2. An evaluation expression that contains the expression used to test whether the loop should stop or continue.
3. An optional iteration expression that increments or decrements the loop counter.
   

ssis,ssrs,ssqs engines

ssis,ssrs,ssqs engines:

Speaking about Microsoft Business Intelligence stack i.e. SSIS / SSAS / SSRS, there are different engines associated with each services. If you are ignorant about these engines, you probably are not fit to design the architecture of your solution using the respective services. The major engines that comes into consideration when you are using MS BI stack are as below:

1) SSIS Runtime Engine - This engine takes care of the administration and execution section of SSIS. In a developer language, I would consider it a Control Flow + SSMS of SSIS.

2) SSIS Data Flow Engine - This engine can be considered as the Buffer Manager of SSIS in-memory architecture.

3) SSAS Formula Engine - The engine takes care of resolving the retrieval of members on any axis of an MDX query. Tuning the performance of this engine has much to do with MDX tuning.

4) SSAS Storage Engine - This engine can be considered as the Data Manager of SSAS, which decides what data needs to be fetched from where. If you trouble Formula Engine, there is a good possibility that this would cascade to Storage Engine, which directly deals with aggregations.

5) SSRS Service Endpoint - This cannot be technically considered as an engine, as most people would argue that rendering / authentication / processing are engines, but I consider these as extensions rather than engines. This endpoint takes care of the administration part of SSRS. Anything that you can do with Reports Manager is a virtue of this endpoint.

6) SSRS Execution Endpoint - This is the endpoint that one would like to award the medal of being an engine. This endpoint takes care of executing the report right from processing the RDL till rendering the report.

You can read more about each of these in MSDN as well as different books and blogs. But until you thoroughly understand the function of these engines and you are designing the architecture, I am of the opinion that one should not feel confident about the architecture design.

SSIS Balanced Data Distributor transform

SSIS Balanced Data Distributor

Transform:

Microsoft download center has a new download available that would be of interest to many SSIS professionals. Its a new transform named Balanced Data Distributor transform, which takes a single input and distributes the incoming rows to one or more outputs uniformly via multithreading. Below mentioned is the description of this transform as mentioned on the download page:

"Microsoft® SSIS Balanced Data Distributor (BDD) is a new SSIS transform. This transform takes a single input and distributes the incoming rows to one or more outputs uniformly via multithreading. The transform takes one pipeline buffer worth of rows at a time and moves it to the next output in a round robin fashion. It’s balanced and synchronous so if one of the downstream transforms or destinations is slower than the others, the rest of the pipeline will stall so this transform works best if all of the outputs have identical transforms and destinations. The intention of BDD is to improve performance via multi-threading. Several characteristics of the scenarios BDD applies to: 1) the destinations would be uniform, or at least be of the same type. 2) the input is faster than the output, for example, reading from flat file to OleDB. "

Considering the test performed on this transform, from the results it seems that performance of this transform is a little better over other transforms like Script and Conditional Split. But it should also be taken into consideration that this transform buffer by buffer to its output ends, and the other two checks the data based on the specified logic and then divides the data. Its a nice transform to have in your existing SSIS toolbelt.

The best use of this transform that I can think of is when your input is extremely fast, and you have replicated blocks of logic to keep up the pace with the incoming data flow. In such a case BDD acts as a distributor bridge pumping data to all logic pipelines. But if data is to be dissected conditionally, Conditional Split would be the option again. The speed of distribution harnessed by this transform seems to be due to two reasons: 1) No distribution of data conditionally, just pass buffer by buffer 2) Multithreading architecture.

Certain curious questions the my mind raises are:

1) Denali is already in CTP mode and more CTPs are expected to come. Then why this transform has been released separately at this time and so silently?

2) Will this transform be a regular transform available with Denali, or would it remain a mysterious separated out transform?

3) This transform is available only for SSIS 2008 and SSIS 2008 R2, not SSIS 2005. Why ? Actually it's SSIS 2005 that needs more help with transforms like this, where it would be a value addition to customers who have already made investments in SSIS 2005 !

Let's look forward to the next CTP of Denali to checkout whether this transform would have a seat in SSIS Denali.

MSBI Developer

Next Step Services Private Limited
(posted 1 day back)


job Description

• Graduate degree or equivalent experience. (B.Tech/MCA preferred)
• Strong experience in MSBI is required.
• Expert in data analysis using Microsoft tools: Access, SSIS, SSRS, MS-SQL Developer.
• Advanced reporting capabilities.
• Advanced data ETL skills.
• Strong facilitation, critical thinking, problem solving, decision making and analytical skills.
• Excellent verbal, written communication and presentation skills

Job Posted by

• Company:Next Step Services Private Limited

Next Step Services Private Limited is a multi-disciplined recruitment consulting firm. We serve a wide range of clients throughout India, from Fortune 500 companies to Small & Medium Enterprises across a variety of industry sectors. It is our mission to provide effective recruitment consulting services which enable our clients to develop and achieve their objectives. We do this by being innovative, professional and proactive in regards to every aspect of our recruitment practice

EXPERIENCE: 3 to 5 yrs

SALARY:As per Industry Standards

LOCATION:Noida / Greater Noida

JOB FUNCTION: IT / Telecom - Software

INDUSTRY:Consulting Services

SPECIALIZATION: Application Programming , Business / Systems Analysis , Database Administration ( DBA ) , Software Engineer

QUALIFICATION: MCA / PGDCA (Computer Science )

BE / B.Tech ( Engineering ) (Computer Science )

How to pass parameter from Report Viewer Control to sub report?

How to pass parameter from Report Viewer 

Control to sub report?

Microsoft ReportViewer is the best alternative to Crystal Reports. In fact it is very easy to create RDLC report compare to crystal reports.

Here are some of the features in this source code:

1. Add subreport in addition to main report. Also called Parent and Child report.
2. Pass parameter from main report to subreport.
3. Use ID or the primary key of the main report to Fill DataSet of Subreport to speed up loading its loading time.
4. Load report programmatically.

Senior Software Engineer - MSBI

Senior Software Engineer - MSBI

(posted about 5 days back)

Designation	Senior Software Engineer - MSBI
Job Description	It takes courage to find out how good you can be. At Optum, we're using leading-edge technology to create the health care system of tomorrow.  When you join us as a . Net Applications Developer, you'll become part of a team focused on innovation and change. You'll work a little harder. You'll aim a little higher.  You'll expect more from yourself and others. And at the end of the day, you'll be doing a lot of good. Through a lot of inspired individuals, we are using technology to build a health care system that works better for more people in more ways than ever.  We are looking to reinforce our team with people who are decisive, brilliant and built for speed -- people like you. As a Developer, you will be predominantly involved in developing business solutions by creating new and modifying existing software applications.  You will be a primary contributor in designing, coding, testing, debugging, documenting and supporting all types of applications consistent with established specifications and business requirements to deliver business value.  This is more than a challenging development role. This is high performance technology. Are you up for the challege Join us and start doing your life's best work.  (SM)Primary Responsibilities: Design, implement and maintain within all phases of the Software Development Life Cycle (SDLC) Develop, test, implement and maintain application software working with established processes Communicate effectively with other engineers and QA Establish, refine and integrate development and test environment tools and software as needed Identify production and non-production application issuesIdentify opportunities to fine-tune and optimize
Desired Profile	Preferred Qualifications:- Educational Qualifications: B. Tech / MCA / Msc / MTech (Minimum 16 years of formal education, Correspondence courses are not relevant).  - experienced developers  Should have good experience in SQLServer.  - Should have written stored procedures and functions.  - Should be strong in SQL- Should be familiar with ETL concepts - Should have good experience in SSIS and data loading using SSIS - Exprience of developing ETL processes and troubleshooting jobs using SSIS.  - Experience in optimizing and improving ETL processes. - Should have very strong communication skills and ability to communicate with business directly.  Nice to Have: Experience in US Healthcare industry. Technology Careers with Optum. Information and technology have amazing power to transform the health care industry and improve people's lives. This is where it's happening.  This is where you'll help solve the problems that have never been solved. We're freeing information so it can be used safely and securely wherever it's needed. We're creating the very best ideas that can most easily be put into action to help our clients improve the quality of care and lower costs for millions. This is where the best and the brightest work together to make positive change a reality.   This is the place to do your life's best work. SMDiversity creates a healthier atmosphere: UnitedHealth Group is an Equal Employment Opportunity/ Affirmative Action employer and all qualified applicants will receive consideration for employment without regard to race, color, religion, sex, age, national origin, protected veteran status, disability status, sexual orientation, gender identity or expression, marital status, genetic information, or any other characteristic protected by law.  UnitedHealth Group is a drug-free workplace. Candidates are required to pass a drug test before beginning employment.  Requirements- Associate's Degree or higher level of education or equivalent experience - 2+ years experience with relational database management systems (e. g. . Access, SQL or Oracle, FileMaker Pro 9)- 2+ years experience performing data analytics and reporting- 2+ years Advanced level of proficiency with MS Excel- Strong problem solving and analytical skills Assets - Experience with object-oriented programming systems- Proficiency with MySQL/ PHP- Knowledge of data release precautions and HIPAA regulations Combine two of the fastest-growing fields on the planet with a culture of performance, collaboration and opportunity and this is what you get. Leading edge technology in an industry that's improving the lives of millions. Here, innovation isn't about another gadget, it's about making health care data available wherever and whenever people need it, safely and reliably. There's no room for error.  Join us and start doing your life's best work. (sm)Technology Careers with Optum. Information and technology have amazing power to transform the health care industry and improve people's lives. This is where it's happening. This is where you'll help solve the problems that have never been solved. We're freeing information so it can be used safely and securely wherever it's needed.  We're creating the very best ideas that can most easily be put into action to help our clients improve the quality of care and lower costs for millions. This is where the best and the brightest work together to make positive change a reality. This is the place to do your life's best work.  SMDiversity creates a healthier atmosphere:  UnitedHealth Group is an Equal Employment Opportunity/ Affirmative Action employer and all qualified applicants will receive consideration for employment without regard to race, color, religion, sex, age, national origin, protected veteran status, disability status, sexual orientation, gender identity or expression, marital status, genetic information, or any other characteristic protected by law. UnitedHealth Group is a drug-free workplace. Candidates are required to pass a drug test before beginning employment.
Experience	5 - 8 Years
Industry Type	Pharma / Biotech / Clinical Research
Role	Software Developer
Functional Area	IT Software - Application Programming, Maintenance
Education	UG - B.Tech/B.E. - Any Specialization PG - M.Sc - Any Specialization, MCA - Computers, M.Tech - Any Specialization Doctorate - Any Doctorate - Any Specialization, Doctorate Not Required
Compensation:	Not disclosed
Location	Hyderabad / Secunderabad
Keywords	Senior Software Engineer - MSBI Net Applications Developer designing coding testing debuggingdocumenting Software Development Life Cycle SQL MySQL PHP
Contact
Job Posted	2016-08-23 11:49:06.0
Reference	672539