SQL2008 upgrade

Do NOT USE

  SQL:  =*, *=, TEXT column, Raiserror,

  BAT:  isql

  Centura:   DDE funtion, SqlImmediate

Attention

  SQL:  1. Update, Delete use Table aliases   

  2. Group by Should add Order by

  3. Add ISNULL function for SQL functions.

  (DATALENGTH, LEFT, LTRIM, REPLICATE, RIGHT, RTRIM,

  SPACE, SUBSTRING)

  4. (Nolock index) should have “With”

  5. ORDER BY子句 中的列别名不能以表作为前缀

 

No.	Category	Change   Guideline	Before image   sample	After image   sample	Remark
DL01	DL   - DLL	DLL   register fail on 2008. The solution is:     1. 定位到   HKEY_LOCAL_MACHINE\SOFTWARE\Classes.     2.将Classes的权限里面添加你的用户，然后权限设成允许完全控制。// No   permission     3.copy all DLL, OCX to C:\Windows\syswow64   folder. If the DLL or OCX exists already, skip the copy. // dll,ocx in installation pack?     4.regsvr32 to register the dll.			Installation   guideline, only for win2008
DL02	DL   - DLL	Change   GCRV002D dll for win2008. The   GCRV002D.dll cannot work on the win2008 environment	Change   the dll coding from     options[1].optionString = (char*)classpath.c_str();	To:     options[1].optionString =   "-Djava.class.path=.;itext-2.0.2.jar;bcprov-jdk14-137.jar";	Installation   guideline
DL03	DL   - DLL	Remove   old Centura dlls, install new gupta runtime			Installation   guideline
DL04	DL   - DLL	1.   add the MF dll folder (C:\Program Files\Micro Focus\Visual COBOL 2010\bin) in   system environment variables;     2. add the MF folder (C:\Program Files\Micro Focus\) in system environment   variables;			Installation   guideline
OD01	OD   - ODBC	The   ODBC should be set in user level instead of system level.     After set the ODBC in user level, the login can work, only for win2008 envrionment	Should   use the syswow64 folder odbcad32 exe to set it		Installation   guideline, only for win2008
CM01	CM   - SQLCommon Issue	SQL2008   update statement     The below statement will pop error     UPDATE TDEPNDENT     T1.DOB= XXXX     FROM TDEPENDENT T1     It should be     UPDATE T1     T1.DOB= XXXX     FROM TDEPENDENT T1    //Update, Delete   directly use Table aliases	UPDATE   TDEPENENT SET T1.DOB=XXXX FROM TDEPENDENT T!	UPDATE   T1 SET T1.DOB=XXXX FROM   TDEPENDENT T!	SP -- USP   list     VB, Centura, COBOL     Keyword: "UPDATE"     Uedit:Jack
CM02	CM   - SQLCommon Issue	Duplicate   column name in selected list,  order by   should use alias or 1,2,3	SELECT   CORID,CORID FROM TABLE ORDER BY CORID	SELECT CORID,CORID FROM TABLE ORDER BY 1     or     SELECT CORID as a,CORID as b FROM TABLE ORDER BY a	SP -- USP   list     VB, Centura, COBOL     Keyword: "ORDER"     Percentage:15%
CM03	CM   - SQLCommon Issue	Cannot   support *= or =* in 2008.   Should change them to left ourter join or right outer join.	Linkage		SP -- USP   list     VB, Centura, COBOL     Keyword: "*=,=*"
CM04	CM   - SQLCommon Issue	Change   the statement "(NOLOCK INDEX)" to    "with (NOLOCK INDEX=?)"	select   BenPlnCd from   TPolPdtBen z (nolock   index = 3)	select   BenPlnCd from   TPolPdtBen z with (nolock index = 3)	SP -- USP   list     VB, Centura, COBOL     Keyword: "*nolock index*"
CM05	CM   - SQLCommon Issue	'   and NULL issue	DATALENGTH('') returns 1 (''   parsed as a single space).     DATALENGTH(N'') returns 2 (N'' parsed as a single Unicode space).     LEFT('123', m) returns NULL when m = 0.     LEFT(N'123', m) returns NULL when m = 0.     LTRIM('     ') returns NULL.     LTRIM(N'     ') returns NULL.     REPLICATE('123', m) returns NULL when m = 0.     REPLICATE(N'123', m) returns NULL when m = 0.     RIGHT(N'123', m) returns NULL when m = 0.     RIGHT('123', m) returns NULL when m = 0.     RIGHT('123', m) returns NULL when m is negative.     RIGHT(N'123', m) returns NULL when m is negative.     RTRIM('     ') returns NULL.     RTRIM(N'     ') returns NULL.     SPACE(0) returns NULL.     SUBSTRING('123', m, n) returns NULL when m < length of the string or   when n = 0.     SUBSTRING(N'123', m, n) returns NULL when m > length of the string or   when n = 0.     UPDATETEXT table.textcolumn textpointer > 0 NULL NULL results in a NULL   value.	DATALENGTH('')   returns 0.      DATALENGTH(N'') returns 0.      LEFT('123', m) returns an empty string when m = 0.     LEFT(N'123', m) returns an empty string when m = 0.     LTRIM('     ') returns an empty string.  //""?     LTRIM(N'     ') returns an empty   string.     REPLICATE('123', m) returns an empty string when m = 0.     REPLICATE(N'123', m) returns an empty string when m = 0.     RIGHT('123', m) returns an empty string when m = 0.     RIGHT(N'123', m) returns an empty string when m = 0.     RIGHT('123', m) returns error when m is negative.     RIGHT(N'123', m) returns error when m is negative.     RTRIM('     ') returns an empty   string.     RTRIM(N'     ') returns an empty   string.     SPACE(0) returns an empty string.     SUBSTRING('123', m, n) returns an empty string when m < length of the   string or when n = 0.     SUBSTRING(N'123', m, n) returns an empty string when m > length of the   string or when n = 0.     UPDATETEXT table.textcolumn textpointer > 0   NULL NULL results in empty text.	Define new   function for these SQL functions     SP, VB, Centura, COBOL     Keyword: "All function" and Summary this number. Simple+
CM06	CM   - SQLCommon Issue	1.   change table column definition     2. use explicit data type conversion	declare   @RETURNPSA  DECIMAL(13, 0)     set @RETURNPSA=2010 select * from TRNWSPECODE where COMPCODE = 41 and TYPECODE ='STOPLOSS' AND RLOBCODE = @RETURNPSA    //automatic force conversion	declare   @RETURNPSA  DECIMAL(13, 0)     set @RETURNPSA=2010     select * from TRNWSPECODE where COMPCODE = 41 and TYPECODE ='STOPLOSS' AND   RLOBCODE = CAST(@RETURNPSA AS VARCHAR(5))	Clark study this point
CM07	CM   - SQLCommon Issue	Replace   Text to varchar(max)	[EXCLTEXT]   TEXT NOT NULL	[EXCLTEXT]   VARCHAR(MAX) NOT NULL	SP, VB,   Centura     Keyword: "TEXT"
CM08	CM   - SQLCommon Issue	1,Group   by not include order by     2, Field invalid in the ORDER BY clause because it is not contained in   either an aggregate function or the GROUP BY clause.	SELECT   POLNO, CLNTCODE, CERTNO FROM TABLE GROUP BY CERTNO,POLNO	SELECT   POLNO, CLNTCODE, CERTNO FROM TABLE GROUP BY CERTNO,POLNO ORDER   BY CERTNO,POLNO	GROUP BY   子句本身没有排序功能。必须显式指定 ORDER BY 子句，SQL Server 才能对任意结果集进行排序     SP, VB, Centura, COBOL     Keyword: "Group by"
CM09	CM   - SQLCommon Issue	或更高版本中，   ORDER BY子句 中的列别名不能以表作为前缀。 中的列别名不能以表作为前缀。	SELECT   FirstName AS f, LastName AS l     FROM Person.Contact p     ORDER BY p.l	SELECT   FirstName AS f, LastName AS l     FROM Person.Contact p     ORDER BY l or ORDER BY p.LastName
CM10	CM   - SQLCommon Issue	Raiserror   problem	Raiserror   12304, 'asdasdasdasdad'	Raiserror   (12304,1,1,'asdasdasdasdad')
CN01	CN   - Centura	Remove   the SalStrUpper function on the change password     login Compass/bdaemon/oprnbdmn, the password should be character   sensitive.     1. first login, the password should be same as the login to SQL   server.     2. after first login, Compass will request user to change the   password,  the changed password by   Compass is always upper characters. So all password should be upper   characters.	Remove   the SalStrUpper function on the change password		gfgc101.apl
CN02	CN   - Centura	replace   DDE with SalAppFind     Check the exe, if the same exe is open, load the pop the exe again	If   NOT SalLoadApp('GMTP001.exe',sGTemp)      Call funcDspMsg( 'E0018',           sGLang,           sNULL,           sNULL,           sGTemp )     Else      If sGFuncType = sSELECT       Set bDDEGMTP001dlgSelectActive =   TRUE      Else If sGFuncType = sNEW       Set bDDEGMTP001dlgClientActive =   TRUE      Else If sGFuncType = sAPPLACTION       Set bDDEGMTP001dlgSelectActive =   TRUE      If bDDEGMTP002Active   OR           bDDEGMTP008Active   OR           bDDEGMTP009Active   OR           bDDEGMTP010Active   OR           bDDEGMTP011Active	If SalAppFind('GMTP001.EXE', TRUE) = hWndNULL      If NOT   SalLoadApp('GMTP001.exe',sGTemp)       Call funcDspMsg( 'E0018',            sGLang,            sNULL,            sNULL,            sGTemp )     Else      Call SalAppEnable ( )	Keyword:   "DDE function"
CN03	CN   - Centura	replace   DDE close with SalQuit()     Exit the screen directly	Call   SalSendMsg( hWndForm, SAM_Close,0,0 )	Return SalQuit ()	Keyword:   "DDE function"
CN04	CN   - Centura	screen   layout issue     1. Some button and files didn't follow class     2. eWorksheet and Note picture			Manual check   screen
CN05	CN   - Centura	Setfocus   and Killfocus function,     For the provider button, the coding is Setfocus one field, enable provider   and Killfocus one field, disable provider     User always cannot click the provider button	On   SAM_KillFocus      Call SalDisableWindow( pbReqProvider   )	On   SAM_KillFocus      ! Call SalDisableWindow(   pbReqProvider )	Keyword:   "SAM_KillFocus"
CN06	CN   - Centura	cannot   find vti21.dll     replace it with vti61.dll	Change   the library to vti61.dll		Keyword:   "vti21.dll"
CN07	CN   - Centura	SqlImmediate   is obsolete	Call   SqlImmediate ( '	Call   SqlPrepareAndExecute (hSqlA, '	Keyword:   "SqlImmediate"     Percentage:30%
CN08	CN   - Centura
CN09	CN   - Centura
BF01	BF   - Batch file	isql   is not work in Win2008	isql   -U %PARMUSR% -P %PARMPSW% -S %PARMSRV% -d %PARMDB% -Q "EXEC   UspGMOS087WelcomeLtr '%PARM1%'"	osql   -U %PARMUSR% -P %PARMPSW% -S %PARMSRV% -d %PARMDB% -Q   "EXEC UspGMOS087WelcomeLtr '%PARM1%'"	Keyword:   "osql"
NE01	NE   - Net Express	Create   new project for one EXE or dll and define the output path to project   .\bin\x86\release folder.
NE02	NE   - Net Express
NE03	NE   - Net Express
SP01	SP   - Stored procedure	Incorrect   syntax near the keyword(sql server key word)	uspgacs069egimrpt.sql.   The statement ' CREATE TABLE #TErr(       filename varchar(200),       lineno varchar(20),       msg varchar(200),       chkind char(1)      )     ' has problem.     Add [] to the sql server key word	The   statement ' CREATE TABLE #TErr(       [filename] varchar(200),       [lineno] varchar(20),       msg varchar(200),       chkind char(1)      )     ' has problem.     Add [] to the sql server key word	USP list
SP02	SP   - Stored procedure	1,The text data type cannot be selected   as DISTINCT because it is not comparable.//? how   solve     2,ORDER BY items must appear in the select list if SELECT DISTINCT is   specified.     3,ORDER BY items must appear in the select list if the statement contains a   UNION, INTERSECT or EXCEPT operator.	uspguws046subrptgenexclusion,     uspguws092subrptgenexclusion     The data type TEXT can't be selected as distinct in 2008,     Need further investigation.	Refer   to CM07     //VARCHAR(MAX) NOT NULL	USP list
SP03	SP   - Stored procedure	An   object or column name is missing or empty. For SELECT INTO statements,names.   Aliases defined as "" or [] are not allowed.	uspgrns091pkgrnwlstout     Change to valid name		USP list
SP04	SP   - Stored procedure	Incorrect   syntax near the keyword 'INTO' for insert table	Insert   TABLEA select A,B into TABLEA from ….	Insert   TABLEA select A,B from ….	USP list
SP05	SP   - Stored procedure	Incorrect   syntax near the keyword 'INTO' parameter	select upl_sts into @chksts  from tclmregmas   where panel_id = @panel_id and bid = @bid	select @chksts=upl_sts  from tclmregmas   where panel_id = @panel_id and bid = @bid	USP list
SP06	SP   - Stored procedure	unsigned   Int, Should be unsignedInt     length should be len	declare   @next_id unsigned Int	declare   @next_id unsignedInt	USP list
SP07	SP   - Stored procedure	compute   and compute by are not supported, should be use ROLLUP command.	a.GPClaims,   a.SPClaims, a.HOClaims, c.MC from #tmp_tprovclms a, tpolicy b (nolock),   #tmp_Final c     where a.polno = b.polno and a.memberid = c.memberid     order by a.groupname, a.memberid     compute sum(c.MC), sum(a.gpclaims), sum(a.spclaims), sum(a.hoclaims)	Sample:     create table dbo.tb(Item varchar(10),Color varchar(10),Qty int)     insert into tb values('Table','Blue',100)                            insert into tb values('Table','Blue',200)                            insert into tb values('Table','Red ',100)                            insert into tb values('Chair','Blue',100)                              insert into tb values('Chair','Red ',200)     insert into tb values('Chair','Red ',200)             select item,color,sum(qty) qtysum from tb group by item,color with   rollup     select item,color,qty from tb order by item,color   compute sum(qty)	USP list
VB01	VB   - Visual Basic	modify   result set output from union sql statements cause error: 'Multiple-step   operation generated errors'	change   sql statements instead of modify result set in VB		Special   case
VB02	VB   - Visual Basic
VB03	VB   - Visual Basic