TSQL Month End

WITH DATES AS
(
SELECT GETDATE() DT
UNION ALL
SELECT DT + 1
FROM DATES
WHERE DT < GETDATE() + 3000
)
SELECT CAST(DT AS DATE) AS DATE
, MonthEnd = CASE WHEN DAY(DT + 1) = 1 THEN 1 ELSE 0 END
, QuarterEnd = CASE WHEN DATEPART(quarter, DT + 1) != DATEPART(quarter, DT) THEN 1 ELSE 0 END
, YearEnd = CASE WHEN YEAR(DT + 1) != YEAR(DT) THEN 1 ELSE 0 END
FROM DATES
OPTION (MAXRECURSION 3000)

Advertisements

SQL Server Deferred Update causes CDC problem

Microsoft introduced a new column in CDC table to address the deferred update problem.
https://support.microsoft.com/en-us/help/3030352/fix-the-change-table-is-ordered-incorrectly-for-updated-rows-after-you
https://blogs.msdn.microsoft.com/sql_server_team/cdc-functionality-may-break-after-upgrading-to-the-latest-cu-for-sql-server-2012-2014-and-2016/

Using the trace flag 8207 to resolve the problem
http://www.c-sharpcorner.com/blogs/resolve-merge-statement-issue-on-cdc-enabled-table

Discussion of CDC problems caused by Deferred Update
https://connect.microsoft.com/SQLServer/Feedback/Details/690476

Microsoft’s explanation on Deferred Update
https://support.microsoft.com/en-us/help/238254/update-statements-may-be-replicated-as-delete-insert-pairs

SQL Server Version List
https://sqlserverbuilds.blogspot.com/

cdc.fn_cdc_get_net_changes_ (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/cdc-fn-cdc-get-net-changes-capture-instance-transact-sql

Remarks:
(1) Microsoft SQL server does not perform deferred updates since SQL Server 2000. It does In-Place Updates and Not-In-Place Updates. “However, if the table has a trigger on it or is marked for replication, the update is still done in place but is recorded in the log as a delete followed by an insert (this provides the before-and-after image for the trigger that is referenced in the inserted and deleted tables).” [Microsoft SQL Server 2012 Unleashed]

Related:
http://www.differencebetween.com/difference-between-deferred-update-and-vs-immediate-update/
https://msdn.microsoft.com/en-us/library/ms715008%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396
Deferred update instead of direct

Informatica Variables are crazy

I really have to say the idea of the Informatica mapping variables are crazy. In case of using the mapping variables as normal variables to pass information to the next sessions, use non-persistent workflow variables to override them in the “pre-session variable assignment”.

http://datawarehouse.ittoolbox.com/groups/technical-functional/informatica-l/problems-with-setvariable-3283342

The is Sukumar’s comment

Workflow Variable:

The Integration Service looks for the start value of a variable in the following order:

1. Value in parameter file
2. Value saved in the repository (if the variable is persistent)
3. User-specified default value
4. Datatype default value

Mapping Variable :

The Integration Service saves the latest value of a mapping variable to the repository at the end of each successful session. During the next session run, it evaluates all references to the mapping variable to the saved value. You can override a saved value with the parameter file. You can also clear all saved values for the session in the Workflow Manager.

The Integration Service looks for the start value in the following order:

1. Value in parameter file
2. Value in pre-session variable assignment
3. Value saved in the repository
4. Initial value
5. Datatype default value

Informatica SQL Query Expression not showing in Source Qualifier

https://network.informatica.com/thread/39849

[rakesh bobbala]
to resolve this do the following:
Close Designer and run regedit .
Go to the following key:
HKEY_CURRENT_USER\Software\Informatica\PowerMa rt Client Tools\\Designer\Opt ions\Global\Editor\SQL
Where PowerCenter_Client_Version is the PowerCenter client version (8.1, 8.1.1, 8.5, 8.5.1, 8.6, etc).
Change the values of the following to “0”:
Expression Editor Position
Expression Editor Splitter Position
SQL Editor Position
SQL Editor Splitter Position