Search This Blog

Saving Changes is not Permitted in SQL

Saving Changes is not Permitted in SQL

The issues came when a user tried to save the table using SSMS.

Like you are changing column Name or allowing NULLS or anychange in a table from Design mode.

Below is the Error when saving the Changes.

Right click table-Design -Trying to change column Name 

Below is the Solution of above Issue.

Go to Top Menu Bar
Tools
Options
Open Designers(Right Hand Side)
Table and Database designs
Uncheck the Box-Prevent Saving Changes that require table re-creation
click ok

Screenshot 


Save the table or press ctrl S to save the table.

The table will save with all the changes made in the table.

NOTE: The above steps are same for sql 2005,sql 2008 and sql 2012.


The Permisssion Granted to 'Domain\User' are insufficient for Performing this Operation SSRS Reporting Issue in SharePoint

The Permisssion Granted to User 'Domain\User' are insufficient for Performing this Operation SSRS Reporting Issue in SharePoint.


After Migrating from  SharePoint 2010 to SharePoint 2013 while opening any of SSRS Reports or Dataconnection file we were getting the below Error.










Below are the steps to Resolve the Issue

1.Giving DB owner permission to User for  Database used by SP and Reporing.

Security-Right click- loginName-UserMapping-checkbox-DB_owner (Screenshot1)

Screenshot1.

2-Changing the Reporting Service Account to your USer(Before that it was Running with windows Account).

Services-SQL Server Reporting Services-Right click-Properties-Log on (Screenshot2).
Running SQL Server Reporting Services with that User x.

3-Final Step is giving permission to  user x for Reporting Service in SP.

SP Central Admin-Application Management-Manage Service Application-SQL 2012 Reporting Service Application –Click-Permissions(Top of Page)-Adding User x-Checkbox-Full Control (screenshot3).


Following Above steps will Resolve the Permission Issue with the Users.

Note:Above Steps are Perfomed in SQL 2012 and SharePoint 2013 Central Admin.





Sql server 2012 New date time functions

Sql server 2012 New date time functions

Below are the list of SQL Server 2012 New added functions with examples.

--Passing year month day separately
DATEFROMPARTS ( YEAR,MONTH,DAY )

select DATEFROMPARTS(2014,11,2)


 DATETIMEFROMPARTS(year, month, day, hour, minute, seconds,milliseconds )

select DATETIMEFROMPARTS(2014,11,2,11,30,12,22)



DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

select DATETIME2FROMPARTS(2014,11,2,11,30,12,76,4)


SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

select SMALLDATETIMEFROMPARTS(2014,11,2,11,3)


DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, 
             seconds, fractions, hour_offset, minute_offset, precision )

select DATETIMEOFFSETFROMPARTS(2014,11,2,11,30,12,6,4,2,2)



TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
select TIMEFROMPARTS(11,12,14,3,3)

--To calculate last date of month 
select EOMONTH (getdate())

Sub queries in SQL

Sub queries with examples in SQL

Sub queries is defined as queries with in other queries .The outermost select query whose result has to be displayed is called outer query.The outer query uses the Result of inner query.

There are two types of sub queries Self contained sub queries and Correlated sub queries.

1. A self contained query has no dependency on outer query .The outer query will display result based on inner query Result.
.If you will Run the inner sub query separetely it will display the result

2.A correlated sub query is that when the inner query is dependent on Outer query. That mean the Subquery
will use the values form outer query. It's like a Search Condition in a Sub query.

so in order to demonstrate Self contained Sub query and correlated queries we have a table script below.

CREATE TABLE [dbo].[Employee_Correlated](
 [Empid] [int]  NOT NULL,
 [EmpName] [nvarchar](50) NULL,
 [EmpSalary] [float] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 15000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'amit', 18000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'vijay', 10000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'suresh', 5000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'Mukesh', 3000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'sumit', 25000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'amit', 28000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'vijay', 17000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'suresh', 55000)
INSERT [dbo].[Employee_Correlated]  ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'Mukesh', 33000)



Case I -Self Contained Subqueries

Now we want to show Max salary from Employee table along with his name
so in this case we will use Self contained sub query.

--Self contained Self query
select * from [Employee_Correlated] where EmpSalary =(select MAX(Empsalary) from [Employee_Correlated])

Output
Empid EmpName EmpSalary
4 suresh 55000

If you will run the innner query alone it will run succesfully displaying max salary of the Table

Case II Correlated Sub queries

Now we want to show all the Employees with their Max salary
So in this case we will use Correlated sub queries.

--correlated sub queries.
select * from [Employee_Correlated] where EmpSalary in (select MAX(EmpSalary) from [Employee_Correlated] E
where E.Empid = [Employee_Correlated].EmpID)

Output

Empid EmpName EmpSalary
5 Mukesh 33000
4 suresh 55000
3 vijay 17000
2 amit 28000
1 sumit 25000

Now try to run the inner query alone.
It will throw an error

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Employee_Correlated.EmpID" could not be bound.

this shows that in correlated sub query the inner query is using the values from outer query.

NOTE:You cannot use Order by with Subquery or Inner query however you can use order by with Outer query.