SPs no longer working after server replacement

Question

Post reply

SPs no longer working after server replacement

Beat BUCHER

Valued Member

Points: 51
More actions
October 7, 2005 at 10:40 am

#166236

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
++++++++++

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 31672 More actions · Answer 1

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

noeld SSC Guru Points: 96590 More actions · Answer 2

noeld

SSC Guru

Points: 96590

October 7, 2005 at 2:01 pm

#596049

What does the following statement returns on the rebuilt Server?:

select serverproperty('Productlevel') lvl, serverproperty('ProductVersion') ver

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ninja's_RGR'us

SSC Guru

Points: 294069

October 7, 2005 at 2:02 pm

#596050

What Noel is getting at is "Did you put SP4 on already?"

noeld SSC Guru Points: 96590 More actions · Answer 4

noeld

SSC Guru

Points: 96590

October 7, 2005 at 2:17 pm

#596060

Correct!

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

October 7, 2005 at 2:18 pm

#596061

Now we're finishing each other's sentences... just like married peoples .

Beat BUCHER Valued Member Points: 51 More actions · Answer 6

Beat BUCHER

Valued Member

Points: 51

October 7, 2005 at 7:39 pm

#596121

Hi Noel & RGR (?),

the result of the query is :

SP4 8.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
++++++++++

Beat BUCHER Valued Member Points: 51 More actions · Answer 7

Beat BUCHER

Valued Member

Points: 51

October 7, 2005 at 7:42 pm

#596122

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
++++++++++

Beat BUCHER Valued Member Points: 51 More actions · Answer 8

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
++++++++++

Jo Pattyn SSC-Dedicated Points: 31672 More actions · Answer 9

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

noeld SSC Guru Points: 96590 More actions · Answer 10

noeld

SSC Guru

Points: 96590

October 11, 2005 at 11:55 am

#596609

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

Beat BUCHER Valued Member Points: 51 More actions · Answer 11

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
++++++++++