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:
*) 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 know more about DATA file : DATAFILE (rdl.data)
To delete dataset cache files :
SQL Server - Transact-SQL eBook by TechNet Wiki Community
This e-book was created from the Transact-SQL articles published in TechNet Wiki by many authors . Download link .
See Also :
See Also :
T-SQL: Script to Find the Names of Stored Procedures that Use Dynamic SQL ( Method 2 )
First method was using dynamic management function - sys.dm_sql_referenced_entities .
Fun with system functions continues ...
Here is the second method : Using dynamic management function - sys.dm_exec_describe_first_result_set
Fun with system functions continues ...
Here is the second method : Using dynamic management function - sys.dm_exec_describe_first_result_set
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 :
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 :
Subscribe to:
Posts (Atom)