Search

SQL Server - SHOWPLAN permission denied in database - Error - Msg 262, Level 14, State 4, Line 1

When a User with minimum privilege try to view the execution plan for a query, below error will throwed:

Msg 262, Level 14, State 4, Line 1
SHOWPLAN permission denied in database 'AdventureWorks2012'.



To resolve the error:

SQL Server - Subquery - Be careful in passing column names in the subqueries

If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error.
SQL Server implicitly qualifies the column in the subquery with the table name in the outer query.
Examples:

T-SQL - Storing and retrieving multi lingual data

In SQL Server, to store and retrieve multi lingual data :

*) We should define the column data type as NVARCHAR

*) and while inserting multi lingual data into column, column value should be prefixed with N'

For example:

SSIS Logging- sysssislog table not getting populated


In SSIS, if logging is enabled with provider type as SQL Server,

sysssislog table :
Contains one row for each logging entry that is generated by packages or their tasks and containers at run time.

This table is created in the msdb database when you install Microsoft SQL Server Integration Services.

If you configure logging to log to a different SQL Server database, a sysssislog table with this format is created in the specified database.

In SSIS, if sysssislog table is not getting populated after package execution, then make sure :

i)   Logging is enabled
ii)  OLEDB connection for Configuration - Pointing to correct database
iii) You have sufficient permission : 


GRANT EXECUTE ON SP_SSIS_ADDLOGENTRY TO [DOMAIN\USER]


SSRS Delete Dataset Cache Files

After designing the report and when the report is Previewed , you will notice DATA file getting created in the project folder of report .

To know more about DATA file : DATAFILE (rdl.data)

To delete dataset cache files :

SQL Server - Calender details

Below T-SQL script using common table expression will provide the calender details:


DECLARE @StartDate DATE = '2014-01-01', @EndDate DATE = '2014-12-31' 
;With CTE
AS
(
SELECT @StartDate dt 
UNION ALL 
SELECT DATEADD(DAY, 1, dt) FROM Cte
WHERE dt < @EndDate
)
SELECT 
FORMAT ( dt, 'D', 'en-US' ) Calender
,DATENAME(dayofyear, dt) DayNumber_Year
,DATEPART(day, dt) DayNumber_Month
,DATEPART(weekday, dt) DayNumber_Week
,DATENAME(weekday, dt) DayName
,DATEPART(WEEK,dt) WeekNumber
,IIF ( DATEPART(weekday, dt) IN (1,7) , 'Weekend', 'Weekday' ) Day
,DATENAME(month, dt) MonthName
,DATEPART(month, dt) MonthNumber
,DATENAME(year, dt) Year
,EOMONTH (dt) MonthLastDate
FROM cte
OPTION (MAXRECURSION 366);



SQL Server - Dynamic SQL - SQL Injection - EXEC [ QUOTENAME() , REPLACE() , EXECUTE AS ] - Sp_executesql - [ RECOMPILE ]

Recently I had mess up with dynamic SQL , So whats next !!! , I started to explore on this topic and post it here , when I googled to gather some information , but what I found was , this topic has been already drilled to the core , carved and with the sculpture mounted on the walls of SQL Server by some of the great SQL Server guru's , much before an year I started to work with SQL Server .

For my regular readers , I will definitely share the good links with the abstract from the same .

To execute a string , we can make use of sp_executesql or EXEC - “Dynamic String Execution”  (DSE)

As mentioned in the BOL :