Change Password of SA Login Using Management Studio - SQL SERVER

Change Password of SA Login Using Management Studio - SQL SERVER

Step1: Login into SQL Server using Windows Authentication.

Step2: In Object Explorer, open Security folder, open Logins folder. Right Click on SA account and go to Properties.


Step3: Change SA password, and confirm it. Click OK.

Make sure to restart the SQL Server and all its services and test new password by log into system using SA login and new password.

Read More

User Defined Functions - Informatica

User Defined Functions - Informatica

Informatica User Defined Functions are similar to Built-in Functions, where these functions need to be created once and execute multiple times

Transformation Logic implemented without User Defined Functions:

Validation “IIF( ISNULL(INPUT),’TRUE’,’FALSE’)” is being performed in multiple ports.

The disadvantage with this approach is any changes to this validation need to be done in all the ports.

This can be addressed by creating a User Defined function and have the logic incorporated there.

Steps to Create User Defined Functions
Step 1 : Right-click on the User-Defined Functions folder in a repository folder in the Designer.
Click on “New”

Step 2: In Editor add the transformation logic / validation that needs to be performed.
Click ok and validate the UDF.

Read More

The easy way to Get table and index DDL script

The easy way to Get table and index DDL script:

The dbms_metadata utility helps to display DDL directly from the data dictionary. We see the small example.
Initial setup:SQL> conn scott/tiger;
Connected.
SQL> create table GET_TAB_SCRIPT
2 (
3 c1 varchar2(10),
4 c2 number
5 );

Table created.

SQL> insert into get_tab_script values ('A',10);

1 row created.

SQL> commit;

Commit complete.

SQL> create index idx_1 on GET_TAB_SCRIPT (c1);

Index created.
To get table script:

SQL> select dbms_metadata.get_ddl('TABLE','GET_TAB_SCRIPT') from dual;
DBMS_METADATA.GET_DDL('TABLE','GET_TAB_SCRIPT')
------------------------------------------------

CREATE TABLE "SCOTT"."GET_TAB_SCRIPT"
( "C1" VARCHAR2(10),
"C2" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"
To get index script:

SQL> select dbms_metadata.get_ddl('INDEX','IDX_1') from dual;

DBMS_METADATA.GET_DDL('INDEX','IDX_1')
----------------------------------------

CREATE INDEX "SCOTT"."IDX_1" ON "SCOTT"."GET_TAB_SCRIPT" ("C1")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"

Read More

Order by based on the parameter passed in SQL server:

Order by based on the parameter passed in SQL server:
Dynamically ordering a result set based on the parameter passed is a powerful technique for ordering your data. The below example shows that and see the issues which we faces.


Initial setup:
create table dept
(
deptid int,
deptname varchar(10)
)

insert into dept values (1,'Sales')
insert into dept values (4,'Production')
insert into dept values (3,'fiance')
insert into dept values (2,'HR')
The following SQL script shows how you might try to use a CASE expression to dynamically order a result set:

DECLARE @OrderByOption int
SET @OrderByOption = 1 SELECT deptid
,deptname
FROM dept
ORDER BY
CASE
WHEN @OrderByOption = 1 THEN deptid
WHEN @OrderByOption = 2 THEN deptname
END

deptid deptname
----------- ----------
1 Sales
2 HR
3 fiance
4 Production

(4 row(s) affected)


The script works well if the value of @OrderByOption is set to 1. We change the value as 2 and try.

DECLARE @OrderByOption int
SET @OrderByOption = 2 SELECT deptid
,deptname
FROM dept
ORDER BY
CASE
WHEN @OrderByOption = 1 THEN deptid
WHEN @OrderByOption = 2 THEN deptname
END
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'Sales' to data type int.
Conceptually, the query offers the ability to order by either the deptid column or the deptname column based on the current value of @OrderByOption. The above statement attempts to order by deptname, but produces the error, " Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the varchar value 'Sales' to data type int "


Why the query works when the value for @OrderByOption is set to 1 but doesn't work when the value is set to 2, we need to recognize that the two THEN conditions of the CASE statement reference expressions of different data types. In this case, SQL Server implicitly converts the data type for the entire CASE expression to the data type in the THEN clause that has the highest order of data-type precedence.

In this example, the CASE statement has two possible values that follow a THEN clause: deptid, which is an integer data type, and deptname, which is a varchar data type. The integer data type has a higher precedence than the varchar data type, so SQL Server attempts to cast the deptname expression as an integer if you try to order by that column. Such a conversion isn't allowed, so SQL Server generates the above error.
We can work around this problem by using multiple CASE statements, as the following example shows:

DECLARE @OrderByOption int
SET @OrderByOption = 2 SELECT deptid
,deptname
FROM dept
ORDER BY
CASE WHEN @OrderByOption = 1 THEN deptid END,
CASE WHEN @OrderByOption = 2 THEN deptname END
deptid deptname
----------- ----------
3 fiance
2 HR
4 Production
1 Sales

Read More

Schema Binding in SQL server

Schema Binding :
Creating a view with the SCHEMABINDING option locks the tables being referred by the view and prevents any changes that may change the table schema.

Notice two important points while creating a view with SCHEMABINDING OPTION:
1. The objects should be referred to by their owner names (two part name - [ownername.objectname] ).
2. SELECT * is not permitted.

We see that using below scripts.
create table schema_binding_table
(
id int,
msg varchar(10)
)

insert into schema_binding_table values
(1,'msg1'),
(2,'msg2'),
(3,'msg3')

select * from schema_binding_table

id msg
----------- ----------
1 msg1
2 msg2
3 msg3

(3 row(s) affected)

Chance for erros while creating view with schema binding:
The below error messgae clearly mention that SELECT * is not permitted whille creating the view with schema binding.
CREATE VIEW schema_binding_VIEW WITH SCHEMABINDING AS
SELECT * FROM schema_binding_table

Msg 1054, Level 15, State 6, Procedure schema_binding_VIEW, Line 2
Syntax '*' is not allowed in schema-bound objects.

Be aware that if you do not use the schema name, (dbo) in this case, then you will get the following error while creating the view.

CREATE VIEW schema_binding_VIEW WITH SCHEMABINDING AS
SELECT id,msg FROM schema_binding_table

Msg 4512, Level 16, State 3, Procedure schema_binding_VIEW, Line 2
Cannot schema bind view 'schema_binding_VIEW' because name 'schema_binding_table' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Successful syntax:
CREATE VIEW schema_binding_VIEW WITH SCHEMABINDING AS
SELECT id,msg FROM dbo.schema_binding_table

Command(s) completed successfully.
We know that without schema binding there was no issue to alter or drop the base table without any type of warning or error. Now if we try to alter the table structure or drop the table, we are going to get this error message.

1. Drop table:

drop table schema_binding_table
Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'schema_binding_table' because it is being referenced by object 'schema_binding_VIEW'.
2. Alter table:alter table schema_binding_table alter column msg varchar(20)
Msg 5074, Level 16, State 1, Line 1
The object 'schema_binding_VIEW' is dependent on column 'msg'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN msg failed because one or more objects access this column.

Read More