Error: INSERT failed because the following SET options have incorrect settings

**lepotier** · 03/06/2015, 14h59

Bonjour,

Je rencontre un soucis avec les SET OPTIONS.
Voici mon erreur:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
INSERT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING, ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or f

J'ai créé une sp qui rempli plusieurs tables d'une base de données (compatibility level 80) à partir de valeurs d'une autre table sur une autre base de données mais elles sont sur le même serveur.

D'après ce que j'ai compris de la doc, je peux définir les set options au début de ma sp et j'ai donc mis

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SET NUMERIC_ROUNDABORT OFF 
GO 
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

Dans le doute, j'ai même jouer avec les ON et OFF pour être sur de bien interpréter le message d'erreur mais j'ai toujours le même message

Le plus drôle est que si je définis les valeurs dans les properties->options de ma db, là, ça fonctionne. Je n'ose pas laisser les modifications au niveau de la db car elle a été développé par une boîte externe et elle est liée à un applicatif en C#, donc je ne sais pas quel impact ça pourrait avoir.

Voici ma sp

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
 
USE [BFastTestCGK]
GO
IF OBJECT_ID('[dbo].[ODIS_Fast]') IS not NULL
    DROP PROCEDURE dbo.ODIS_Fast
GO
 
/****** Object:  StoredProcedure [dbo].[ODIS_Fast]    Script Date: 03-06-15 12:54:43 ******/
SET NUMERIC_ROUNDABORT OFF 
GO 
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
 
-- =============================================
-- Description:	input des données dans FAST
-- =============================================
CREATE PROCEDURE dbo.ODIS_Fast
	@uid VARCHAR(125), -- UNIQUE ID FROM ODIS
	@response varchar(255) OUTPUT -- RESPONSE TO HVL
AS
BEGIN	
	BEGIN TRY
		BEGIN TRANSACTION SetInFast	
 
		--VARIABLES FAST
		DECLARE @dossierNr INT = 0, @verzekerdeID INT = 0, @melderID INT = 0, @huisadresID INT = 0, @vakantieadresID INT = 0, @ziekenhuizenAdresID INT = Null, @melderAdresID INT = 0,@hulpverliningID INT = 0, @treatingdoctorID INT = Null, @familydoctorID INT = Null, @specialistID INT = Null, @polisID INT = NULL
		DECLARE @service VARCHAR(10), @hvType INT, @serviceFullName VARCHAR(25)
		DECLARE @volgnr INT
		DECLARE @timeDifference VARCHAR(5)
		DECLARE @landMelding VARCHAR(5)
		DECLARE @crossId INT
		DECLARE @melderNaam VARCHAR(25)
 
		SET DATEFORMAT dmy; -- Set date format to day/month/year
 
		SELECT @service = F.data, @hvType = 
			CASE F.data
				WHEN 'op' THEN 11
				WHEN 'pk' THEN 12
				WHEN 'dial' THEN 112
			END,
			@serviceFullName =
			CASE F.data
				WHEN 'op' THEN 'OPNAME'
				WHEN 'pk' THEN 'POLIKLINISCH'
				WHEN 'dial' THEN 'DIALYSE'
			END
		FROM ODIS.dbo.FastData AS F WHERE (F.field = 'service') AND (F.uid = @uid)
 
		SELECT @crossId= F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'cross') AND (F.uid = @uid)
		SELECT TOP (1) @landMelding = CA.LAND, @timeDifference = CL.TimeZone FROM CROSS_ADRES AS CA INNER JOIN CROSS_LANDEN AS CL ON CA.LAND = CL.LANDCODE WHERE (CA.CR_ADRESNR = @crossId) 
 
		--CREATION DU DOSSIER
		INSERT INTO BFastTestCGK.dbo.FDOSSIER(RECORDSTATUS, EmployeePrioriteit, DossierPrioriteit) VALUES (0,  4, 4)
 
		SELECT @dossierNr = @@IDENTITY
 
		-- FPERSOONSGEGEVENS
			-- VERZEKERDE
			SET @volgnr = 0
			INSERT INTO FPERSOONSGEGEVENS (FDOSSIERNR, RELATIE, VOLGNR, ACHTERNAAM, VOORLETTERS, VOORNAAM, GESLACHT, GEBDATUM, SPREEKTALEN, TELEFOONNUMMER, LANDCODE, GSMNUMMER, DOSSIER_GERELATEERDE_OPMERKINGEN) 
			SELECT TOP (1) @dossierNr, 'VERZEKERDE' AS RELATIE, @volgnr AS VOLGNR,
						 (SELECT dbo.CapitalizeFirstLetter(F.data) FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredname') AND (F.uid = @uid)) AS ACHTERNAAM,
						 (SELECT dbo.CapitalizeFirstLetter(SUBSTRING(F.data, 1, 1)) AS Expr1 FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredfirstname') AND (F.uid = @uid)) AS VOORLETTERS,
						 (SELECT dbo.CapitalizeFirstLetter(F.data) FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredfirstname') AND (F.uid = @uid)) AS VOORNAAM,
						 (SELECT UPPER(F.data) FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredgender') AND (F.uid = @uid)) AS GESLACHT,
						 (SELECT CONVERT(VARCHAR(20),F.data,126) AS Expr1 FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredbirthday') AND (F.uid = @uid)) AS GEBDATUM,
						 (SELECT UPPER(SUBSTRING(F.data, 1, 1)) AS Expr1 FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredlanguage') AND (F.uid = @uid)) AS SPREEKTALEN,
						 (SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredphonenumber') AND (F.uid = @uid)) AS TELEFOONNUMMER,
						 (SELECT UPPER(F.data) FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insurednationality') AND (F.uid = @uid)) AS LANDCODE,
						 (SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredmobilnumber') AND (F.uid = @uid)) AS GSMNUMMER,
						 (SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'remarks') AND (F.uid = @uid)) AS DOSSIER_GERELATEERDE_OPMERKINGEN
			FROM   ODIS.dbo.FastData AS FastData
			WHERE (FastData.uid = @uid)
 
			SELECT @verzekerdeID = @@IDENTITY
 
			-- MELDER
			SET @volgnr = 2
 
			INSERT INTO FPERSOONSGEGEVENS (FDOSSIERNR, RELATIE, VOLGNR, ACHTERNAAM) 
			SELECT TOP (1) @dossierNr, 'MELDER' AS RELATIE, @volgnr AS VOLGNR,
						 (SELECT dbo.CapitalizeFirstLetter(F.data) FROM ODIS.dbo.FastData AS F WHERE (F.field = @service+'-hospitalname') AND (F.uid = @uid)) AS ACHTERNAAM
			FROM   ODIS.dbo.FastData AS FastData
			WHERE (FastData.uid = @uid)
 
			SELECT @melderID = @@IDENTITY
 
			-- TREATING DOCTOR
			IF EXISTS(SELECT TOP (1) F.data FROM ODIS.dbo.FastData AS F WHERE F.field = @service+'-treatingdoctor' AND F.uid = @uid)
				BEGIN
					SET @volgnr +=1
					INSERT INTO FPERSOONSGEGEVENS (FDOSSIERNR, RELATIE, VOLGNR, ACHTERNAAM, VOORLETTERS, TELEFOONNUMMER)
					SELECT TOP(1) @dossierNr, 'TREATING DOCTOR' AS RELATIE, @volgnr AS VOLGNR,
						(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = @service+'-treatingdoctor') AND (F.uid = @uid)) AS ACHTERNAAM,
						(SELECT SUBSTRING(F.data, 1, 1) AS Expr1 FROM    ODIS.dbo.FastData AS F WHERE (F.field = @service+'-treatingdoctor') AND (F.uid = @uid)) AS VOORLETTERS,
						(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = @service+'-treatingdoctorphone') AND (F.uid = @uid)) AS TELEFOONNUMMER
					FROM   ODIS.dbo.FastData AS FastData
					WHERE (FastData.uid = @uid)
 
					SELECT @treatingdoctorID = @@IDENTITY
				END
 
			-- FAMILY DOCTOR
			IF EXISTS(SELECT TOP (1) F.data FROM ODIS.dbo.FastData AS F WHERE F.field = @service+'-familydoctor' AND F.uid = @uid)
				BEGIN
					SET @volgnr +=1
					INSERT INTO FPERSOONSGEGEVENS (FDOSSIERNR, RELATIE, VOLGNR, ACHTERNAAM, VOORLETTERS, TELEFOONNUMMER)
					SELECT TOP (1) @dossierNr, 'FAMILY DOCTOR' AS RELATIE, @volgnr AS VOLGNR,
						(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = @service+'-familydoctor') AND (F.uid = @uid)) AS ACHTERNAAM,
						(SELECT SUBSTRING(F.data, 1, 1) AS Expr1 FROM    ODIS.dbo.FastData AS F WHERE (F.field = @service+'-familydoctor') AND (F.uid = @uid)) AS VOORLETTERS,
						(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = @service+'-familydoctorphone') AND (F.uid = @uid)) AS TELEFOONNUMMER
					FROM   ODIS.dbo.FastData AS FastData
					WHERE (FastData.uid = @uid)
 
					SELECT @familydoctorID = @@IDENTITY
				END
 
			-- SPECIALIST
			IF EXISTS(SELECT TOP (1) F.data FROM ODIS.dbo.FastData AS F WHERE F.field = @service+'-specialist' AND F.uid = @uid)
				BEGIN
					SET @volgnr +=1
					INSERT INTO FPERSOONSGEGEVENS (FDOSSIERNR, RELATIE, VOLGNR, ACHTERNAAM, VOORLETTERS, TELEFOONNUMMER)
					SELECT TOP (1) @dossierNr, 'SPECIALIST' AS RELATIE, @volgnr AS VOLGNR,
						(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = @service+'-specialist') AND (F.uid = @uid)) AS ACHTERNAAM,
						(SELECT SUBSTRING(F.data, 1, 1) AS Expr1 FROM    ODIS.dbo.FastData AS F WHERE (F.field = @service+'-specialist') AND (F.uid = @uid)) AS VOORLETTERS,
						(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = @service+'-specialistphone') AND (F.uid = @uid)) AS TELEFOONNUMMER
					FROM   ODIS.dbo.FastData AS FastData
					WHERE (FastData.uid = @uid)
 
					SELECT @specialistID = @@IDENTITY
 
				END
 
		-- END FPERSOONSGEGEVENS
 
		-- FADRESGEGEVENS
			-- HUISADRES
			INSERT INTO FADRESGEGEVENS (PERSOONSNUMMER, LANDCODE, POSTCODE, TELEFOONNR1, ADRESOMS, ADRESNAAM, ADRESWPL, ADRES, EMAIL_ADRES, OrderNumber)
			SELECT TOP(1) @verzekerdeID,
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insurednationality') AND (F.uid = @UID)) AS LANDCODE,
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredzipcode') AND (F.uid = @UID)) AS POSTCODE,
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredphonenumber') AND (F.uid = @UID)) AS TELEFOONNR1, 
				'HUISADRES', 
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredname') AND (F.uid = @UID)) AS ADRESNAAM,
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredcity') AND (F.uid = @UID)) AS ADRESWPL,
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredstreet') AND (F.uid = @UID)) AS ADRES,
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredemail') AND (F.uid = @UID)) AS EMAIL_ADRES, 
				0
			FROM   ODIS.dbo.FastData AS FastData
			WHERE (FastData.uid = @uid)
 
			SELECT @huisadresID = @@IDENTITY
 
			-- VAKANTIEADRES
			INSERT INTO FADRESGEGEVENS (PERSOONSNUMMER, LANDCODE, POSTCODE, TELEFOONNR1, ADRESOMS, ADRESNAAM, ADRESWPL, ADRES, OrderNumber)
			SELECT TOP(1) @verzekerdeID,
				'XX' AS LANDCODE,
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'travelzipcode') AND (F.uid = @UID)) AS POSTCODE,
				'' AS TELEFOONNR1, 
				'VAKANTIEADRES', 
				'' AS ADRESNAAM,
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'travelcity') AND (F.uid = @UID)) AS ADRESWPL,
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'traveladdress') AND (F.uid = @UID)) AS ADRES,
				1
			FROM   ODIS.dbo.FastData AS FastData
			WHERE (FastData.uid = @uid)
 
			SELECT @vakantieadresID = @@IDENTITY
 
			-- MELDER ADRES
			INSERT INTO FADRESGEGEVENS (PERSOONSNUMMER, ADRESOMS, OrderNumber, ADRESNAAM, ADRES, LANDCODE, ADRESWPL, TELEFOONNR1, TELEFOONNR2, FAXNR, FAXNR2, OPMERKINGEN, EMAIL_ADRES)
			SELECT TOP (1) @melderID, 'ZIEKENHUIZEN', 0, NAAM AS ADRESNAAM, ADRES AS ADRES, LAND AS LANDCODE, PLAATS AS ADRESWPL, TELEFOONNR1 AS TELEFOONNR1, 
				 TELEFOONNR2 AS TELEFOONNR2, FAXNR AS FAXNR, FAXNR2 AS FAXNR2, OPMERKINGEN AS OPMERKINGEN, email AS EMAIL_ADRES
			FROM   CROSS_ADRES
			WHERE (CR_ADRESNR = @crossId)
 
			SELECT @melderAdresID = @@IDENTITY
		-- END FADRESGEGEVENS
 
		-- FHULPVERLENING
			INSERT INTO FHULPVERLENING (FDOSSIERNR,HV_VOLGNUMMER, BEGINDATUM, EINDDATUM, HULPVERLENER, DATUMTIJD_HV, HV_TYPE_ID)
			SELECT TOP(1) @dossierNr, 1, 
				(SELECT CAST(F.data AS datetime) AS Expr1 FROM ODIS.dbo.FastData AS F WHERE (F.field = @service + '-startdatetreatment') AND (F.uid = @uid)) AS BEGINDATUM, -- startdatetreatment = en cas de DIAL -> DateFirstDialyse
				(SELECT CAST(F.data AS datetime) AS Expr1 FROM ODIS.dbo.FastData AS F WHERE (F.field = @service + '-enddatetreatment') AND (F.uid = @uid)) AS EINDDATUM,		-- enddatetreatment = en cas de DIAL -> DateLastDialyse
				'SYS',
				(SELECT CAST(F.data AS datetime) AS Expr1 FROM ODIS.dbo.FastData AS F WHERE (F.field = @service + '-enddatetreatment') AND (F.uid = @uid)) AS DATUMTIJD_HV,
				@hvType AS HV_TYPE_ID
			FROM   ODIS.dbo.FastData AS FastData
			WHERE (FastData.uid = @uid)
 
			SELECT @hulpverliningID = @@IDENTITY
 
		-- FSCHADEGEGEVENS
			INSERT INTO FSCHADEGEGEVENS (FDOSSIERNR, AANTAL_PERSONEN, DATUM_VOORVAL, DATUMTIJD_MELDING, EIND_DATUM, BEGIN_DATUM, VERVOER, GETROFFENE, MELDER, LANDMELDING, REISDOEL, ongeval, AIRLINE_TOUROPERATOR,tijdsverschil)
			SELECT TOP(1) @dossierNr, 
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'numbercompanion') AND (F.uid = @uid)) AS AANTAL_PERSONEN,
				GETDATE() AS DATUM_VOORVAL,
				GETDATE() AS DATUMTIJD_MELDING,
				(SELECT CAST(F.data AS datetime) AS Expr1 FROM ODIS.dbo.FastData AS F WHERE (F.field = 'departuredate') AND (F.uid = @uid)) AS EIND_DATUM,
				(SELECT CAST(F.data AS datetime) AS Expr1 FROM ODIS.dbo.FastData AS F WHERE (F.field = 'arrivaldate') AND (F.uid = @uid)) AS BEGIN_DATUM,
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'meansoftransport') AND (F.uid = @uid)) AS VERVOER,
				@verzekerdeID AS GETROFFENE,
				@melderID AS MELDER,
				@landMelding AS LANDMELDING,
				'ONBEKEND' AS REISDOEL,
				'N' AS ongeval,
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'compagny') AND (F.uid = @uid)) AS AIRLINE_TOUROPERATOR,
				@timeDifference AS tijdsverschil
			FROM   ODIS.dbo.FastData AS FastData
			WHERE (FastData.uid = @uid)
 
			IF @hvType <> 112 -- Si pas DIAL
			BEGIN
			-- HV_MEDISCH
				INSERT INTO HV_MEDISCH (HULPVERLENINGID,REDEN, BEGINDATUM, EINDDATUM, PNR_BEH_ARTS, ADNR_ZIEKENHUIS, PNR_HUISARTSNR, PNR_SPECIALISTNR, SOORT_BEHANDELING)
				SELECT TOP(1) @hulpverliningID,
					(SELECT F.data AS Expr1 FROM ODIS.dbo.FastData AS F WHERE (F.field = @service + '-reason') AND (F.uid = @uid)) AS REDEN,
					(SELECT CAST(F.data AS datetime) AS Expr1 FROM ODIS.dbo.FastData AS F WHERE (F.field = @service + '-startdatetreatment') AND (F.uid = @uid)) AS BEGINDATUM, 
					(SELECT CAST(F.data AS datetime) AS Expr1 FROM ODIS.dbo.FastData AS F WHERE (F.field = @service + '-enddatetreatment') AND (F.uid = @uid)) AS EINDDATUM,
					@treatingdoctorID AS PNR_BEH_ARTS,
					@vakantieadresID AS ADNR_ZIEKENHUIS,
					@familydoctorID AS PNR_HUISARTSNR,
					@specialistID AS PNR_SPECIALISTNR,
					@serviceFullName AS SOORT_BEHANDELING
				FROM   ODIS.dbo.FastData AS FastData
				WHERE (FastData.uid = @uid)
			END
			ELSE
			BEGIN
			-- HV_ZUURSTOF
				INSERT INTO HV_ZUURSTOF (HULPVERLENINGID, PNR_wie, PNR_HUISARTS, PNR_SPECIALIST_BIN, PNR_LEVERANCIER, AANKOMST_VAK, VERTREK_VAK, DIAGNOSE_MTE, BEVESTIGING, adnr_vakantiebest, bijzonderheden_mte, ox_code_mte, pnr_leverancier_bin, pnr_leverancier_bui, ADNR_LEVERANCIER_BIN)
				SELECT @hulpverliningID, 
					0 AS PNR_wie, 
					ISNULL(@treatingdoctorID, 0) AS PNR_HUISARTS, 
					0 AS PNR_SPECIALIST_BIN, 
					0 AS PNR_SPECIALIST_BIN,
					(SELECT CAST(F.data AS datetime) AS Expr1 FROM ODIS.dbo.FastData AS F WHERE (F.field = @service + '-startdatetreatment') AND (F.uid = @uid)) AS AANKOMST_VAK, 
					(SELECT CAST(F.data AS datetime) AS Expr1 FROM ODIS.dbo.FastData AS F WHERE (F.field = @service + '-enddatetreatment') AND (F.uid = @uid)) AS VERTREK_VAK,
					(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'numberdialyses') AND (F.uid = @uid)) AS DIAGNOSE_MTE,
					(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insuredreservations') AND (F.uid = @uid)) AS BEVESTIGING,
					0 AS adnr_vakantiebest,
					(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'totaldialyses') AND (F.uid = @uid)) AS bijzonderheden_mte,
					'ONBEKEND' AS ox_code_mte,
					@melderID AS pnr_leverancier_bin,
					0,
					@ziekenhuizenAdresID AS ADNR_LEVERANCIER_BIN 
			END
 
		-- 	FPOLISGEGEVENS
			-- FIRST POLICY
			INSERT INTO FPOLISGEGEVENS (FDOSSIERNR, VOLGNR, BEVESTIGING, RECORDSTATUS,code_maatsch, polisnr, andere_alarmcentrale, POLISSRT_CODE)
			SELECT TOP(1) @dossierNr, 0 AS VOLGNR, 'U' AS BEVESTIGING, 1 AS RECORDSTATUS,
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insurancename') AND (F.uid = @uid)) AS code_maatsch,
				(SELECT F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'insurancenumber') AND (F.uid = @uid)) AS polisnr,
				'N' AS andere_alarmcentrale,
				(SELECT TOP (1) poliscode AS POLISSRT_CODE
				FROM   VERZEKERINGSMAATSCHAPPIJ AS V
				WHERE (MAATSCHAPPIJCODE =
								 (SELECT data
								 FROM    ODIS.dbo.FastData AS F
								 WHERE (field = 'insurancename') AND (uid = @uid))))
			FROM   ODIS.dbo.FastData AS FastData
			WHERE (FastData.uid = @uid)
 
			SELECT @polisID = @@IDENTITY
 
			-- HV_SOC_ZEKERHEID
				IF EXISTS(SELECT TOP (1) F.data FROM ODIS.dbo.FastData AS F WHERE F.field = 'insz' AND F.uid = @uid)
					BEGIN
						INSERT INTO HV_SOC_ZEKERHEID (POLISID, FDOSSIERNR, IN_REGEL, avr, insz2)
						SELECT TOP(1) @polisID, @dossierNr, 'U', 'U', (SELECT TOP (1) F.data FROM ODIS.dbo.FastData AS F WHERE F.field = 'insz' AND F.uid = @uid)
						FROM   HV_SOC_ZEKERHEID
					END
			-- END HV_SOC_ZEKERHEID
 
			-- SECOND POLICY
 
				IF EXISTS(SELECT TOP (1) F.data FROM ODIS.dbo.FastData AS F WHERE F.field = 'secondinsurancename' AND F.uid = @uid)
					INSERT INTO FPOLISGEGEVENS (FDOSSIERNR, VOLGNR, BEVESTIGING, DOSSIERNR_MAATSCH, RECORDSTATUS, POLISSRT_CODE, polisnr, SECOND_POLICY_ID)
					SELECT @dossierNr,  2, 'U', 
						(SELECT TOP (1) F.data FROM ODIS.dbo.FastData AS F WHERE F.field = 'secondinsurancename' AND F.uid = @uid) AS DOSSIERNR_MAATSCH, 
						1, 'UNKNOWN', 
						(SELECT TOP (1) F.data FROM ODIS.dbo.FastData AS F WHERE (F.field = 'filenumbersecondinsurance') AND (F.uid = @uid)) AS polisnr,
						0
				ELSE
					BEGIN
						INSERT INTO FPOLISGEGEVENS (FDOSSIERNR, VOLGNR, BEVESTIGING, RECORDSTATUS, POLISSRT_CODE, SECOND_POLICY_ID)
						SELECT @dossierNr,  2, 'U', 1, 'UNKNOWN', 0
					END
 
		-- 	END FPOLISGEGEVENS
 
		-- FAFSPRAAKGEGEVENS
			-- TASK_TEMPLATE_SERVICE
			INSERT INTO FAFSPRAAKGEGEVENS (FDOSSIERNR, AFSPRAAKCODE, MEDNR_AANMAAK, AFSPRDATUM_TIJD, AFSPROMS, AFSPRTGW, AFGEMELD, URGENT, VERSLAG, CREATION_DATE, SHOW_IN_TICKER)
			SELECT @dossierNr, t .TASK_TEMPLATE_ID AS AFSPRAAKCODE, 1121 AS MEDNR_AANMAAK, CONVERT (date, GETDATE()) AS AFSPRDATUM_TIJD, a.OMS AS AFSPROMS, 'SYS' AS AFSPRTGW, 'N' AS AFGEMELD, 'N' AS URGENT, 
				Stuff((Select CHAR(13) + CHAR(10) + IsNull( F10_VRAGEN.TEKST, '' ) FROM F10_VRAGEN LEFT OUTER JOIN AFSPRAAKSOORTEN ON AFSPRAAKSOORTEN.AFSPRAAKCODE = F10_VRAGEN.AFSPRAAKCODE WHERE t .TASK_TEMPLATE_ID = F10_VRAGEN.AFSPRAAKCODE For Xml PATH, TYPE ).value('.[1]', 'nvarchar(max)') , 1, 1, '') As VERSLAG, 
				GETDATE() AS CREATION_DATE, 'N' AS SHOW_IN_TICKER
			FROM TASK_TEMPLATE_SERVICE AS t INNER JOIN AFSPRAAKSOORTEN AS a ON t.TASK_TEMPLATE_ID = a.AFSPRAAKCODE 
			WHERE (t.SERVICE_TYPE_ID = @hvType)
 
			-- INITIAL_REPORT AFSPRAAK M1
			INSERT INTO FAFSPRAAKGEGEVENS (FDOSSIERNR, AFSPRAAKCODE, MEDNR_AANMAAK, AFSPRDATUM_TIJD, AFSPRDATUM_TIJD_TOT, AFSPROMS, AKTIEDATUM_TIJD, AFSPRTGW, AFGEMELD, URGENT, VERSLAG, CREATION_DATE, SHOW_IN_TICKER)
            SELECT @dossierNr, a.AFSPRAAKCODE AS AFSPRAAKCODE, 1121 AS MEDNR_AANMAAK, Getdate() AS AFSPRDATUM_TIJD, Getdate() AS AFSPRDATUM_TIJD_TOT, 'INITIAL_REPORT' AS AFSPROMS, GETDATE() AS AKTIEDATUM_TIJD, 'SYS' AS AFSPRTGW, 'Y' AS AFGEMELD, 'N' AS URGENT,
				Stuff(
                (
                    Select CHAR(13) + CHAR(10) + IsNull( F10_VRAGEN.TEKST, '' )
                    FROM    F10_VRAGEN LEFT OUTER JOIN AFSPRAAKSOORTEN ON AFSPRAAKSOORTEN.AFSPRAAKCODE = F10_VRAGEN.AFSPRAAKCODE
                    WHERE a.AFSPRAAKCODE = F10_VRAGEN.AFSPRAAKCODE
                    For Xml PATH, TYPE
                    ).value('.[1]', 'nvarchar(max)')
                    , 1, 1, ''
				) As VERSLAG,
	            GETDATE() AS CREATION_DATE, 'N' AS SHOW_IN_TICKER
            FROM   AFSPRAAKSOORTEN AS a 
            WHERE (a.AFSPRAAKCODE = 'M1')
 
		-- END FAFSPRAAKGEGEVENS
 
		-- LINKED FILE
			--TO DO
 
		-- END LINKED FILE
 
		SET @response = 'Dossier: ' + CAST(@dossierNr AS VARCHAR(999)) + ' staat in Fast'
 
		--DECLARE @r VARCHAR (50)
		--EXEC @r = ODIS_Mail @uid, 0
 
		SET @response = @response + @r
		SELECT @response
 
		COMMIT TRANSACTION SetInFast;
 
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0 
			ROLLBACK TRANSACTION SetInFast;
 
		SET @response = ' Error: ' + ERROR_MESSAGE() + ' LINE: ' + CAST(ERROR_LINE() AS VARCHAR(999)) + ' ERROR NUMBER: ' + CAST(ERROR_NUMBER() AS VARCHAR(999)) + ' ERROR_PROCEDURE: ' + CAST(ERROR_PROCEDURE() AS VARCHAR(999))
 
		SELECT @response
 
	END CATCH		
END

Quelqu'un pourrait m'aider ? Une idée ?

Je débute, si vous avez des propositions pour optimisé ou autre, je suis aussi preneur

Merci d'avance

EDIT: J'appelle cette sp depuis Php

**aieeeuuuuu** · 03/06/2015, 16h22

Bonjour,

Et en définissant les options dans la procédure stockée ?

**lepotier** · 03/06/2015, 16h54

Envoyé par aieeeuuuuu

Bonjour,

Et en définissant les options dans la procédure stockée ?

Bonjour,

J'ai uniquement AINSI_NULLS et QUOTED indentifier de disponible et ils sont à TRUE

**hmira** · 06/06/2015, 01h34

Lors de la création d'un index sur une colonne calculée persistante, assurez-vous que les 7 options ci-dessous sont définies comme suit

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SET ANSI_NULLS ON 
SET ANSI_PADDING ON 
SET ANSI_WARNINGS ON 
SET ARITHABORT ON 
SET CONCAT_NULL_YIELDS_NULL ON 
SET QUOTED_IDENTIFIER ON 
SET NUMERIC_ROUNDABORT OFF

(NOTE : les 6 premières options à ON et la 7ème option NUMERIC_ROUNDABORT à OFF)

En effet, SET NUMERIC_ROUNDABORT doit être impérativement désactivée (valeur OFF) lors de la création ou de la modification d'index sur des colonnes calculées ou des vues indexées. Si SET NUMERIC_ROUNDABORT est activé (ON), les instructions INSERT , UPDATE, et DELETE dans des tables comportant des index sur des colonnes calculées ou des vues indexées échouent.

Donc, ces 7 options doivent être correctement définies tel que décrit ci-dessus, et ce, au moment de la création de l'indexe sur les champs calculé persistant et non tardivement au moment de création ou de l'exécution de la procédure !

De même (Idem), ces 7 options doivent être également définies comme décrit ci-dessus lors de la matérialisation d'une Vue, c.à.d. lors de la création d'un Index Cluster sur la dite vue.

Conclusion :
Pour corriger votre problème, il vous faudra, droper puis recréer correctement certains de vos Indexes établis sur les champs calculés persistants ou sur des vues matérialisées, en positionnant préalablement et correctement les 7 options tel que décrit ci-dessus.

A+