Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

Cannot perform alter on a function because it is an incompatible object type

If you have a Multi-Statement Table-Valued Function (MSTVF):

CREATE FUNCTION [DataAccess].[ufn_GetLastPoliciesByTZ]
( ... )
RETURNS @ret TABLE (ID INT)
AS
BEGIN
...
RETURN
END


If you will try to alter it to – with changing it to an Inline Table-Valued Function:

ALTER FUNCTION [DataAccess].[ufn_GetLastPoliciesByTZ]
(
       @ClientPolTable [DataAccess].[TT_ClientPoliciesByTZ] READONLY,
       @SugMimshak INT,
       @FromNechonutDate DATETIME = NULL, -- In case no date is selected
       @ToNechonutDate DATETIME = NULL
)
RETURNS TABLE AS
RETURN
(  
       SELECT .....
)


You will be got this error:
Cannot perform alter on 'DataAccess.ufn_GetLastPoliciesByTZ' because it is an incompatible object type.

Solution:
You can't alter a function from MSTVF to Inline. So, drop and create again the function.
CREATE FUNCTION [DataAccess].[ufn_GetLastPoliciesByTZ]() RETURNS TABLE AS RETURN (SELECT 1 AS ID)

No comments:

Post a Comment