AFAIK this is a result of not closing current result set and trying to open another.

Multiple result sets are only allowed when MARS is activated using sql client driver.

Then your trigger should be

CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders

AFTER INSERT, UPDATE

AS

IF UPDATE(TradingDate)

BEGIN

UPDATE c

SET c.ZLastSale = i.TradingDate

FROM inserted i

JOIN dbo.Customers c ON c.UniqueID = i.AccountID

END

This will update ZLastSale (text column!!!)...

Is UniqueID the correct key to link TRANSHEADERS to CUSTOMERS ?

Your problem is CUSTOMERS UniqueID is char(17) and TRANSHEADERS UniqueID is int

SQL Server is trying to implicitly convert CUSTOMERS UniqueID to int to match against TRANSHEADERS UniqueID but at...

Use SQL Server Management Studio to connect to the database, find the database and tables in question. Right click on each table and select Script table as, CREATE, to new...

To try to figure out what the error you are getting we need the SQL used to create the...

Not sure if that was what you wanted but this might be

;WITH cte (patnt_no,patnt_refno,admit_date,discharge_date,min_discharge_date) AS (

SELECT patnt_no,patnt_refno,admit_date,discharge_date,

MIN(discharge_date) OVER (PARTITION BY patnt_no)

FROM patient_table)

SELECT patnt_no,patnt_refno,admit_date,discharge_date

FROM cte

WHERE admit_date <= DATEADD(month,6,min_discharge_date)

OR (admit_date <= DATEADD(Year,2,min_discharge_date)

AND...

cte (patnt_no,patnt_refno,admit_date,discharge_date,min_discharge_date) AS (

SELECT patnt_no,patnt_refno,admit_date,discharge_date,

MIN(discharge_date) OVER (PARTITION BY patnt_no)

FROM patient_table),

cte2 (patnt_no,patnt_refno,admit_date,discharge_date) AS (

SELECT patnt_no,patnt_refno,admit_date,discharge_date

FROM cte

WHERE admit_date <= DATEADD(month,6,min_discharge_date))

SELECT patnt_no,patnt_refno,admit_date,discharge_date

FROM cte2

UNION ALL

SELECT patnt_no,patnt_refno,admit_date,discharge_date

FROM cte

WHERE admit_date <= DATEADD(Year,2,min_discharge_date)

AND patnt_no IN (SELECT a.patnt_no...

Eirikur Eiriksson (5/11/2014)

---

David Burrows (5/11/2014)

---

In that case use Eirikur's trigger although you could simplify it to

CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders

AFTER INSERT, UPDATE

AS

IF UPDATE(TradingDate)

BEGIN

UPDATE c

SET c.ZLastDate = i.TradingDate

FROM inserted i

JOIN dbo.Customers...

Please post DDL for the two tables and the SQL you used to test with.

In that case use Eirikur's trigger although you could simplify it to

CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders

AFTER INSERT, UPDATE

AS

IF UPDATE(TradingDate)

BEGIN

UPDATE c

SET c.ZLastDate = i.TradingDate

FROM inserted i

JOIN dbo.Customers c ON c.UniqueID =...

The trigger looks OK apart from

ZLastSale, ZLastdate and ZLastSales (which is the correct name)

It only processes updates to Transheaders

What about inserts to Transheaders?

no problem 🙂

It always annoys me when MS keeps changing config files and locations and why oh why try to obfuscate it in a binary file :crazy:

For 2008 the list is actually in a binary file

%AppData%\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

Removing this file will clear the list BUT WILL ALSO REMOVE ALL OTHER SSMS SETTINGS

You could use an expression in column 1 of the tablix to test for Big and another in column 2 to test for Small

This would give you

Big

Big

Big

...