Le forum (ô combien francophone) des utilisateurs de Powerbuilder.
Pages: 1
Environnement
PB11.5.1 B4608/PB2017 B1666
MSSQL 2008R2 SP3
Windows 10
J'ai une procédure stockée, s'exécutant sans problème dans le SQL manager.
Dans l'ide un retrieve de la datawindow (une grid) retourne les valeurs sans problèmes.
En exécution, j'ai le message d'erreur suivant
erreur SQL = (102)
Object : dd_tcharge_test
Select Error: SQLSTATE = 42000
Microsoft SQL Server Native Client 10.0
Syntaxe incorrecte vers '!'.
----------------------------------------
J'ai vérifié les droits sur les tables et la procédure stockée et il n'y a rien qui cloche.
J'ai l'erreur avec la version PB11.5 et la version 2017.
Là je tourne en rond.
Merci
Yann
Procédure stockée
CREATE PROCEDURE [dbo].[proc_chargelib] -- Add the parameters for the stored procedure here (@pol int,@pod int,@tier int,@agence int,@catid int,@ilang integer) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- ============================================= -- Declare and using a READ_ONLY cursor -- ============================================= Create table #libtemp (tchargeId varchar(12) ,name varchar(50) ,intname varchar(50) ,intname2 varchar(50) ,intname3 varchar(50) ,intname4 varchar(50) ,computebase decimal(18,10) ,fixedbase bit ,curname varchar(50) ,unname varchar(100) ,forfait int) DECLARE cur_charge CURSOR READ_ONLY FOR SELECT typecharge.tcharge_id, typecharge.name, typecharge.intname, typecharge.intname2, typecharge.intname3, typecharge.intname4, typecharge.computebase, typecharge.fixedbase, currency.name, unit.name, (select count(*) from typecharge_repart where tcharge_id =typecharge.tcharge_id) as forfait FROM typecharge, currency, unit WHERE ( currency.currency_id = typecharge.currency_id ) and ( unit.unit_id = typecharge.unit_id ) and ( typecharge.inuse = 1 ) AND (isnull(typecharge.cattchg_id,@catid) = @catid OR isnull(@catid,0) = 0) --AND ISNULL(@chg,tcharge_id)=tcharge_id DECLARE @tchargeId varchar(12),@name varchar(50),@intname varchar(50),@intname2 varchar(50),@intname3 varchar(50),@intname4 varchar(50), @defname varchar(50),@defintname varchar(50),@defintname2 varchar(50),@defintname3 varchar(50),@defintname4 varchar(50) ,@compute decimal(18,10),@fixe bit,@curname varchar(50),@unname varchar(100),@forfait int OPEN cur_charge FETCH NEXT FROM cur_charge INTO @tchargeID,@name,@intname,@intname2,@intname3,@intname4,@compute,@fixe,@curname,@unname,@forfait WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN set @defname=null set @defintname=null set @defintname2=null set @defintname3=null set @defintname4=null SELECT top (1) @defname=lab.name, @defintname=lab.intname, @defintname2=lab.intname2, @defintname3=lab.intname3, @defintname4=lab.intname4 FROM tcharge_label lab WHERE lab.agency_id=@agence AND lab.tcharge_id=@tchargeId and ( ( --correspondance sur les trois isnull(@tier,0) =lab.tier_id and ISNULL(@pol,0)=lab.pol and ISNULL(@pod,0)=lab.pod ) or ( --correspondance sur tier/pol isnull(@tier,0) =lab.tier_id and ISNULL(@pol,0)=lab.pol and lab.pod=0 ) or ( --correspondance sur tier/pod isnull(@tier,0) =lab.tier_id and ISNULL(@pod,0)=lab.pod and lab.pol=0 ) or ( --correspondance sur pol,pod ISNULL(@pol,0)=lab.pol and ISNULL(@pod,0)=lab.pod and lab.tier_id=0 ) or ( --correspondance sur le pol ISNULL(@pol,0)=lab.pol and lab.tier_id=0 and lab.pod=0 ) or ( --correspondance sur le pod ISNULL(@pod,0)=lab.pod and lab.tier_id=0 and lab.pol=0 ) or ( --correspondance sur le tiers lab.pod=0 and isnull(@tier,0)=lab.tier_id and lab.pol=0 ) ) order by lab.tier_id desc,lab.pod desc,lab.pol desc insert into #libtemp (tchargeId,name,intname,intname2,intname3,intname4,computebase,fixedbase,curname,unname,forfait) values (@tchargeId,ISNULL(@defname,@name),ISNULL(@defintname,@intname),ISNULL(@defintname2,@intname2),ISNULL(@defintname3,@intname3),ISNULL(@defintname4,@intname4),@compute,@fixe,@curname,@unname,@forfait) END FETCH NEXT FROM cur_charge INTO @tchargeID,@name,@intname,@intname2,@intname3,@intname4,@compute,@fixe,@curname,@unname,@forfait END CLOSE cur_charge DEALLOCATE cur_charge select IsNull( ( CASE @ilang WHEN 1 THEN name WHEN 2 THEN intname WHEN 3 THEN intname2 WHEN 4 THEN intname3 WHEN 5 THEN intname4 ELSE name END ) ,name) as chgname, tchargeId,name,intname,intname2,intname3,intname4,computebase,fixedbase,curname,unname,forfait from #libtemp END
Code Datawindow
release 11.5; datawindow(units=0 timer_interval=0 color=1073741824 brushmode=0 transparency=0 gradient.angle=0 gradient.color=8421504 gradient.focus=0 gradient.repetition.count=0 gradient.repetition.length=100 gradient.repetition.mode=0 gradient.scale=100 gradient.spread=100 gradient.transparency=0 picture.blur=0 picture.clip.bottom=0 picture.clip.left=0 picture.clip.right=0 picture.clip.top=0 picture.mode=0 picture.scale.x=100 picture.scale.y=100 picture.transparency=0 processing=1 HTMLDW=no print.printername="" print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yes print.prompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overrideprintjob=no print.collate=yes print.background=no print.preview.background=no print.preview.outline=yes hidegrayline=no showbackcoloronxp=no picture.file="" grid.lines=0 ) header(height=72 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" ) summary(height=0 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" ) footer(height=0 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" ) detail(height=72 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" ) table(column=(type=char(50) updatewhereclause=yes name=chgname dbname="chgname" ) column=(type=char(12) updatewhereclause=yes name=tchargeid dbname="tchargeId" ) column=(type=char(50) updatewhereclause=yes name=name dbname="name" ) column=(type=char(50) updatewhereclause=yes name=intname dbname="intname" ) column=(type=char(50) updatewhereclause=yes name=intname2 dbname="intname2" ) column=(type=char(50) updatewhereclause=yes name=intname3 dbname="intname3" ) column=(type=char(50) updatewhereclause=yes name=intname4 dbname="intname4" ) column=(type=decimal(10) updatewhereclause=yes name=computebase dbname="computebase" ) column=(type=number updatewhereclause=yes name=fixedbase dbname="fixedbase" ) column=(type=char(50) updatewhereclause=yes name=curname dbname="curname" ) column=(type=char(100) updatewhereclause=yes name=unname dbname="unname" ) column=(type=long updatewhereclause=yes name=forfait dbname="forfait" ) procedure="1 execute dbo.proc_chargelib;1 @pol = :pol, @pod = :pod, @tier = :tier, @agence = :agence, @chg = :chg, @catid = :catid, @ilang = :ilang" arguments=(("pol", number),("pod", number),("tier", number),("agence", number),("chg", string)("catid", number),("ilang", number),) ) text(band=header alignment="2" text="#" border="6" color="33554432" x="9" y="4" height="60" width="411" html.valueishtml="0" name=tchargeid_t visible="1" font.face="Tahoma" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="67108864" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" ) text(band=header alignment="2" text="Type de charge" border="6" color="33554432" x="430" y="4" height="60" width="1381" html.valueishtml="0" name=chgname_t visible="1" font.face="Tahoma" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="67108864" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" ) text(band=header alignment="2" text="Base" border="6" color="33554432" x="1824" y="4" height="60" width="279" html.valueishtml="0" name=computebase_t visible="1" font.face="Tahoma" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="67108864" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" ) text(band=header alignment="2" text="Currency" border="6" color="33554432" x="2117" y="4" height="60" width="389" html.valueishtml="0" name=curname_t visible="1" font.face="Tahoma" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="67108864" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" ) text(band=header alignment="2" text="Unit" border="6" color="33554432" x="2514" y="4" height="60" width="507" html.valueishtml="0" name=unname_t visible="1" font.face="Tahoma" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="67108864" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" ) column(band=detail id=2 alignment="0" tabsequence=32766 border="6" color="33554432" x="9" y="8" height="60" width="411" format="[general]" html.valueishtml="0" name=tchargeid visible="1" edit.limit=12 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes font.face="Tahoma" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="0" background.color="67108864~tif( forfait >0,20866303,67108864)" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" ) column(band=detail id=1 alignment="0" tabsequence=32766 border="0" color="33554432" x="430" y="8" height="60" width="1381" format="[general]" html.valueishtml="0" name=chgname visible="1" edit.limit=50 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes font.face="Tahoma" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" ) column(band=detail id=8 alignment="1" tabsequence=32766 border="0" color="33554432" x="1824" y="8" height="60" width="279" format="[general]" html.valueishtml="0" name=computebase visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes font.face="Tahoma" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" ) column(band=detail id=10 alignment="0" tabsequence=32766 border="0" color="33554432" x="2117" y="8" height="60" width="389" format="[general]" html.valueishtml="0" name=curname visible="1" edit.limit=50 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes font.face="Tahoma" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" ) column(band=detail id=11 alignment="0" tabsequence=32766 border="0" color="33554432" x="2514" y="8" height="60" width="507" format="[general]" html.valueishtml="0" name=unname visible="1" edit.limit=100 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes font.face="Tahoma" font.height="-8" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" ) htmltable(border="1" ) htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" encodeselflinkargs="1" netscapelayers="0" pagingmethod=0 generatedddwframes="1" ) xhtmlgen() cssgen(sessionspecific="0" ) xmlgen(inline="0" ) xsltgen() jsgen() export.xml(headgroups="1" includewhitespace="0" metadatatype=0 savemetadata=0 ) import.xml() export.pdf(method=0 distill.custompostscript="0" xslfop.print="0" ) export.xhtml()
Dernière modification par ydl (24-07-2017 09:21:44)
Hors ligne
la fatigue de la semaine sans doute, dans la fenêtre hébergeant la datawindow, une fonction modifiait le code SQL avec un ajout sur le Where.
Fonctionne effectivement très mal avec l'appel d'une procédure stockée
Hors ligne
Pages: 1