MSBI

Transfer Logins Task Database Administrators can use the Transfer Logins Task to transfer logins between different instances of SQL Server . Using this task you can either transfer all the logins from one SQL Server Instance to another SQL Server Instance, transfer only specific logins, or transfer only logins which have access to a specific user database.  At the destination server, the transferred SQL Server Logins are by default disabled and are assigned a random password . Once the task is complete, the DBA needs to assign the correct password for the SQL Server account and enable the logins to allow users to access the database. However, using the Transfer Logins Task you cannot transfer “sa” logins (including renamed “sa” logins) between different instances of SQL Server. In this Article, before starting with Transfer Logins task, let us create a new login user. Remember, I’m Transferring LOGIN Credentials from NSW1041\ADMIN123 to NSW1069\ADMIN123 Here I’m cr...

There are basically 4 types of commands in SQL. They are 1) DDL (DATA DESCRIPTION LANGUAGE) 2) DML (DATA MANIPULATION LANGUAGE) 3) DCL (DATA CONTROL LANGUAGE) 4) TCL (TRANSACTION CONTROL LANGUAGE) 1) Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples: CREATE - to create objects in the database ALTER - alters the structure of the database DROP - delete objects from the database TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed COMMENT - add comments to the data dictionary RENAME - rename an object 2)  Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples: SELECT - retrieve data from the a database INSERT - insert data into a table UPDATE - updates existing data within a table DELETE - deletes all records from a table, the space for the records remain MERGE - UPSERT operation (insert or update) CALL - cal...

SQL Server Integration Services (SSIS) is a Business Intelligence tool used by Database Developers or Administrators to perform Extract Transforms and Load (ETL) Operations Initially before SSIS, it was Data Transformation Services (DTS). Evolution of DTS led to currently used SSIS. The SSIS designer will always have  three tabbed pages  1) Control Flow, 2) Data Flow, and  3) Event handlers.  An additional tab pops up during execution to show the progress.  Package Explorer: A Package Explorer tab displays the contents of the package  The Execution Results tab shows the results after execution.  Connection Managers: Since databases are at the heart of SSIS, a control tray of Connection Managers will display all the used connections and available connections for the package. Tool box: The SSIS toolbox contains the various items needed for Control Flow as well as tasks needed for maintenance plans. In SQL Server 2008...

Here is an example how to import the data which is in Flat File to SQL Server database. In my example, I kept the Flat file by the name source.txt on C:\Documents and Settings\Dhinakaran\Desktop\texting Source.txt is comma seperated value (CSV). Now i want to load this value into SAMPLE Database of SQL Server (Local system). to achieve this, Open SSMS. Select SAMPLE database. Right click, Select Task, then Import option under task and Give NEXT. New Import and Export Wizard window will open You will be guided to next window Screen and here you are asked to select the data Source type. In this case we are using Flat file and So select flat file from Drop down button under Data Source Option. After selecting, Source type, Browse for the Flat file and in my case Flat file is at C:\Documents and Settings\Dhinakaran\Desktop\texting and Next check Column Names in first row data option if u r first row of flat file indicates column name. In my example i'm check marking that opti...

Say we have a table #test with columns as follows:: I need to display rows as follows: We can use FOR XML PATH concept to accomplish this: QUERY: First we will create table #test:: CREATE TABLE #test( field1 VARCHAR(5), field2 VARCHAR(5) ) Let us first create table #test: INSERT INTO #test SELECT '001','AAA' UNION ALL SELECT '001','BBB' UNION ALL SELECT '002','CCC' UNION ALL SELECT '003','DDD' UNION ALL SELECT '004','EEE' UNION ALL SELECT '004','FFF' UNION ALL SELECT '004','GGG' For required output, the query will be SELECT field1, SUBSTRING ( (SELECT ( ', ' + field2)   FROM #test t2    WHERE t1.Field1 = t2.Field1      ORDER BY t1.Field1, t2.Field1      FOR XML PATH (' ')), 3, 1000) FROM #test t1 GROUP BY field1

SSIS Package For Each Loop Container          The For Each Loop Container defines a repeating control flow in the package. Loop implementation in the For Each Loop Container is similar to the Foreach looping concept in various programming languages. The Foreach enumerator enables looping in a package. There are several different enumerator types available.  They are:      1) Foreach File Enumerator The File Enumerator enumerates files in a folder. For example, we can get all the files which have the *.txt extension in a windows folder and its subfolders.     2) Foreach ADO Enumerator The ADO Enumerator enumerates rows in a table. For example, we can get the rows in the ADO records.     3) Foreach ADO.Net Schema Rowset Enumerator The ADO.Net Enumerator enumerates the schema information. For example, we can get the table from the database.     4) Foreach Item Enumerator The I...

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns. The syntax for the HAVING clause is: SELECT column1, column2, ... column_n, aggregate_function (expression) FROM tables WHERE predicates GROUP BY column1, column2, ... column_n HAVING condition1 ... condition_n; aggregate_function can be a function such as SUM, COUNT, MIN, or MAX. Example using the SUM function For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned. SELECT department, SUM(sales) as "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 1000; Example using the COUNT function For example, you could use the COUNT function to return the name of the department and the number of employees (in the asso...

The INSERT INTO statement is used to insert a new row in a table INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...) is the Syntax Example:  INSERT INTO emp (ename, sal, job, ID, deptno)  Values ('smitha', 100, 'manager', 1, 10), ('rajesh', 200, 'Asst manager', 2,20); Other way of inserting values into table: INSERT INTO emp  SELECT  'smitha', '100', 'manager', '1', '10' UNION ALL SELECT  'rajesh', '200', 'Asst manager', '2','20' will throw same result of inserting values into emp table

CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, .... ) is syntax for creating table in SSMS