SPs no longer working after server replacement
-
Hi all out there,
I'm sysadmin in a litle company and care one SQL server (GreatPlains 7.0). Last week we have replaced our old HW by a brand new server. Our GP VAR configured the new server with Win2k3 and SQL2k (the old one had Win2k and SQL2k). The old server had two instances running (not really usefull) whereas the new one has only one. There was linked server entries in one instance to point to the GP DB. This was rebuilt in the server too. The SP's are called out (executed ?) from legacy Delphi apps (very old), and they worked fine until the server was replaced. The message I get is :
Statement(s) could not be prepared.
[OLE/DB provider returned message: Deferred prepare could not be completed.]
plus a dozen of other lines reffering to the where clauses in the Select statment.
The content of one of the SP is :
CREATE PROCEDURE qryInvoice
AS
DECLARE @SqlString NVARCHAR(1800)
set @SqlString = 'SELECT c.ID, d.*
FROM Company c
RIGHT OUTER JOIN
OPENQUERY(GREATPLAINS,
''SELECT h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE) AS CubeMonth,
SUM(s.QTYTOINV) AS CubeQty, SUM(s.XTNDPRCE) AS CubeDollar
FROM SOP30200 h, SOP30300 s, IV00101 i
WHERE h.SOPNUMBE = s.SOPNUMBE
AND s.ITEMNMBR = i.ITEMNMBR
AND h.SOPTYPE = 3
AND h.VOIDSTTS = 0
AND i.USCATVLS_6 NOT LIKE " %"
AND i.ITMCLSCD LIKE "PF-%"
AND h.CUSTNMBR NOT LIKE "_WHSE%"
AND (h.DOCDATE >= "2005-09-01" AND h.DOCDATE < "2006-08-31")
GROUP BY h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE)
ORDER BY h.CUSTNMBR, s.ITEMNMBR, CubeMonth'') d
ON c.CustNmbr = d.CUSTNMBR'EXEC sp_executeSql @SqlString
RETURN
GO
I would really appreciate any hint to have my apps running again quickly, because my users are waiting on them :-).
TiA
PS: I've allready tried to replace the double quote by two single quotes, but get other errors++++++++++
Beat BUCHER
SysAdmin
Dynamics GP Consultant
++++++++++ -
1) the EXEC sp_executeSql @SqlString
can be dropped, no need for dynamic sql here
2)There can be a few inner joins
3) The owner of the tables should be specified for better performance
SELECT c.ID,
d.CUSTNMBR, d.ITEMNMBR, d.CubeMonth,
d.CubeQty, d.CubeDollar
FROM dbo.Company c
RIGHT OUTER JOIN
OPENQUERY(GREATPLAINS,
'SELECT h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE) AS CubeMonth,
SUM(s.QTYTOINV) AS CubeQty, SUM(s.XTNDPRCE) AS CubeDollar
FROM dbo.SOP30200 h
inner join dbo.SOP30300 s
on h.SOPNUMBE = s.SOPNUMBE
inner join dbo.IV00101 i
on s.ITEMNMBR = i.ITEMNMBR
WHERE
h.SOPTYPE = 3
AND h.VOIDSTTS = 0
AND i.USCATVLS_6 NOT LIKE ' %'
AND i.ITMCLSCD LIKE 'PF-%'
AND h.CUSTNMBR NOT LIKE '_WHSE%'
AND (h.DOCDATE >= '2005-09-01' AND h.DOCDATE < '2006-08-31')
GROUP BY h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE)
ORDER BY h.CUSTNMBR, s.ITEMNMBR, CubeMonth') d
ON c.CustNmbr = d.CUSTNMBR
-
What does the following statement returns on the rebuilt Server?:
select serverproperty('Productlevel') lvl, serverproperty('ProductVersion') ver
* Noel -
What Noel is getting at is "Did you put SP4 on already?"
-
Correct!
* Noel -
Now we're finishing each other's sentences... just like married peoples
. -
Hi Noel & RGR (?),
the result of the query is :
SP48.00.2039
I will test the modified Proc that Jo has posted and provide feedback soon as I can.
Thanks for the help.
++++++++++
Beat BUCHER
SysAdmin
Dynamics GP Consultant
++++++++++ -
I get a kind of a same result as I did with the initial SP, but modified by replacing the " by '' :
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near '%'.
Something must be wrong with those _quotes_ ...
++++++++++
Beat BUCHER
SysAdmin
Dynamics GP Consultant
++++++++++ -
Hi Jo,
I used your query in QA and after changing the single quotes against double (single) quotes, it worked. However, how do I manage to save this query as a stored proc (I'm not familiar with creating / recalling SPs 🙂 ):
++++++
SELECT c.ID,
d.CUSTNMBR, d.ITEMNMBR, d.CubeMonth,
d.CubeQty, d.CubeDollar
FROM Company c
RIGHT OUTER JOIN
OPENQUERY(GREATPLAINS,
'SELECT h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE) AS CubeMonth,
SUM(s.QTYTOINV) AS CubeQty, SUM(s.XTNDPRCE) AS CubeDollar
FROM dbo.SOP30200 h
inner join dbo.SOP30300 s
on h.SOPNUMBE = s.SOPNUMBE
inner join dbo.IV00101 i
on s.ITEMNMBR = i.ITEMNMBR
WHERE
h.SOPTYPE = 3
AND h.VOIDSTTS = 0
AND i.USCATVLS_6 NOT LIKE '' %''
AND i.ITMCLSCD LIKE ''PF-%''
AND h.CUSTNMBR NOT LIKE ''_WHSE%''
AND (h.DOCDATE >= ''2005-09-01'' AND h.DOCDATE < ''2006-08-31'')
GROUP BY h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE)
ORDER BY h.CUSTNMBR, s.ITEMNMBR, CubeMonth') d
ON c.CustNmbr = d.CUSTNMBR
++++
++++++++++
Beat BUCHER
SysAdmin
Dynamics GP Consultant
++++++++++ -
Quite simple. You can use the query analyzer for this.
use dbToCreateStoredProcedureIn /*making sure of the right database*/
CREATE PROCEDURE dbo.qryInvoice
/*all items should be owned by dbo*/
AS
SET NOCOUNT ON /*generally not needed to return the amount of affected rows, avoiding an additional roundtrip*/
SELECT c.ID,
d.CUSTNMBR, d.ITEMNMBR, d.CubeMonth,
d.CubeQty, d.CubeDollar
FROM Company c
RIGHT OUTER JOIN
OPENQUERY(GREATPLAINS,
'SELECT h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE) AS CubeMonth,
SUM(s.QTYTOINV) AS CubeQty, SUM(s.XTNDPRCE) AS CubeDollar
FROM dbo.SOP30200 h
inner join dbo.SOP30300 s
on h.SOPNUMBE = s.SOPNUMBE
inner join dbo.IV00101 i
on s.ITEMNMBR = i.ITEMNMBR
WHERE
h.SOPTYPE = 3
AND h.VOIDSTTS = 0
AND i.USCATVLS_6 NOT LIKE '' %''
AND i.ITMCLSCD LIKE ''PF-%''
AND h.CUSTNMBR NOT LIKE ''_WHSE%''
AND (h.DOCDATE >= ''2005-09-01'' AND h.DOCDATE < ''2006-08-31'')
GROUP BY h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE)
ORDER BY h.CUSTNMBR, s.ITEMNMBR, CubeMonth') d
ON c.CustNmbr = d.CUSTNMBR
-
OK all that's fine if you don't pass parameters. If you do you will need dynamic SQL because Linked server do not accept variables in their execution commands.
Cheers,
* Noel -
Thanks a lot Jo (and all others too),
it worked perfectly, and my Delphi apps are now happy again. At least there is data that is returned to the application... I'll have to check with the users if the data is what they expected :-), but I'm confident that it'll fine.
Will post again if there are other issues.
Thanks again and have a good day all.
++++++++++
Beat BUCHER
SysAdmin
Dynamics GP Consultant
++++++++++
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply