June 26, 2013 at 7:16 am
We have a SPROC that when i try to execute i receive the following error message of "Syntax error line 7 at or after token <AS>. [10179] " Below is the actual code of the SPROC. Any suggestions?
USE [ConstarOLAP_PROPHIX_FactDb]
GO
/****** Object: StoredProcedure [dbo].[ActualPurchasePriceExport] Script Date: 06/26/2013 08:37:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =======================================================
-- Author:Tom Stagliano, Constar
-- Create date: June 25, 2013
-- Description:Prophix Actual Purchase Price Export SPROC
-- =======================================================
ALTER PROCEDURE [dbo].[ActualPurchasePriceExport]
(
@Start_Date DATETIME = NULL,
@Part_Type_MP VARCHAR(1000) = ''
)
AS
SET @Part_Type_MP = ',' + @Part_Type_MP + ','
SET @Start_Date = ISNULL(@Start_Date, DATEADD(yy, 50, GETDATE()))
SET @Start_Date = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@Start_Date), 0)))
DECLARE
@sqlquery NVARCHAR(MAX),
@finalQuery NVARCHAR(MAX),
@q CHAR(1) = ''''
CREATE TABLE #POLINEKEY
(
PART_NO VARCHAR(100),
LINE_ITEM_KEY INT
)
SET @sqlquery = 'SELECT
PLK.Part_no,
PLK.line_item_key
FROM
(SELECT p.part_no, MAX(PLI.Line_Item_Key)
FROM Purchasing _v_Line_Item_e AS PLI
JOIN Part_v_Part_e AS P
ON p.plexus_customer_no = pli.plexus_customer_no
AND p.part_key = pli.part_key
WHERE pli.add_date <= @Start_Date
AND (@Part_Type_MP = ,, OR (CHARINDEX(, + CAST(P.Part_Type as VARCHAR(50)) + ,, @Part_Type_MP) >0))
GROUP by p.part_no
) AS "PLK"
'
SET @finalQuery = 'SELECT
p.part_no AS "Part",
pli.unit_price AS "ActualCost"
FROM OPENQUERY (PLEXREPORTSERVER, ' + @q + @sqlquery + @q + ')'
INSERT INTO #POLINEKEY
(
PART_NO,
LINE_ITEM_KEY
)
EXEC(@finalquery)
EXEC sp_executesql @finalquery
INSERT INTO dbo.tblActualPrice
select * from #POLINEKEY
June 26, 2013 at 7:33 am
well, syntax wise, it's fine for SQL2008; i was able to create it without a problem.
on a SQL2005 or compatibility 90 database, i get an error because SQL2005 did not allow inline assignments of variables, so i got this error :, i did not get the same error as you did, however.
Msg 139, Level 15, State 1, Procedure ActualPurchasePriceExport, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Procedure ActualPurchasePriceExport, Line 49
Must declare the scalar variable "@q".
i couldn't execute the proc, because i don't have teh dependant tables (ie Purchasing _v_Line_Item_e),
but my first guess would be to remove the quotes around PLK in the query that is being generated:
AS "PLK"
Lowell
June 26, 2013 at 7:36 am
were you able to execute the SPROC? i receive no errors when i create the SPROC but when i go and right click on the SPROC and select execute, that is where i receive the errors.
June 26, 2013 at 7:38 am
also, this looks incorrect to me, there's commas, double commas but no values:
'AND (@Part_Type_MP = ,, OR (CHARINDEX(, + CAST(P.Part_Type as VARCHAR(50)) + ,, @Part_Type_MP) >0))'
is it the single quotes got converted to commas from some text editor?
Lowell
June 26, 2013 at 7:39 am
tstagliano (6/26/2013)
DECLARE
@sqlquery NVARCHAR(MAX),
@finalQuery NVARCHAR(MAX),
@q CHAR(1) = ''''
Try changing this to:
DECLARE
@sqlquery NVARCHAR(MAX),
@finalQuery NVARCHAR(MAX)
DECLARE @q CHAR(1) = ''''
or
DECLARE
@sqlquery NVARCHAR(MAX),
@finalQuery NVARCHAR(MAX),
@q CHAR(1)
SET @q = ''''
Warm Regards,Greg Wilsonsolidrockstable.com
June 26, 2013 at 7:45 am
Still referencing line 7 where the token <AS> is being used.
June 26, 2013 at 7:53 am
can you change this:
EXEC(@finalquery)
to this, and paste the results after running the procedure?
PRINT @finalquery
EXEC(@finalquery)
also, is there any reason you are rrunning it twice in the same proc?
EXEC(@finalquery)
EXEC sp_executesql @finalquery
Lowell
June 26, 2013 at 7:59 am
adding the PRINT @finalquery statement throws an incorrect syntax near the keyword 'PRINT' error message.
We have it listed twice so i sp_executesql will run.
June 26, 2013 at 1:08 pm
From the looks of it...the sp_executesql will do nothing other than return that recordset.
You're also trying to use variables in dynamic sql but you're not telling sp_executesql what they are. I would change the code to what's below. I haven't tested this since I don't have your data structure in place. You're also going to need to fix the comma issue that lowell mentioned above. I think those might need to be single quotes but it's tough to say for sure. Let me know if this works or not.
ALTER PROCEDURE [dbo].[ActualPurchasePriceExport]
(
@Start_Date DATETIME = NULL,
@Part_Type_MP VARCHAR(1000) = ''
)
AS
SET @Part_Type_MP = ',' + @Part_Type_MP + ','
SET @Start_Date = ISNULL(@Start_Date, DATEADD(yy, 50, GETDATE()))
SET @Start_Date = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@Start_Date), 0)))
DECLARE
@sqlquery NVARCHAR(MAX),
@finalQuery NVARCHAR(MAX)
SET @sqlquery = 'SELECT
PLK.Part_no,
PLK.line_item_key
FROM
(SELECT p.part_no, MAX(PLI.Line_Item_Key)
FROM Purchasing _v_Line_Item_e AS PLI
JOIN Part_v_Part_e AS P
ON p.plexus_customer_no = pli.plexus_customer_no
AND p.part_key = pli.part_key
WHERE pli.add_date <= @Start_Date
AND (@Part_Type_MP = ,, OR (CHARINDEX(, + CAST(P.Part_Type as VARCHAR(50)) + ,, @Part_Type_MP) >0))
GROUP by p.part_no
) AS "PLK"
'
SET @finalQuery = 'INSERT INTO dbo.tblActualPrice ' + CHAR(10)
SET @finalQuery = @finalQuery + 'SELECT
p.part_no AS "Part",
pli.unit_price AS "ActualCost"
FROM OPENQUERY (PLEXREPORTSERVER, ''' + @sqlquery + ''')'
--Print statement left for debug purposes
PRINT @finalQuery
EXEC sp_executesql @finalquery, '@StartDate DATETIME, @PartTypeMP VARCHAR(1000)',@StartDate = @Start_Date, @PartTypeMP = @Part_Type_MP
June 26, 2013 at 1:27 pm
The SPROC was able to execute but i cannot see the table of dbo.tblActualPrice anywhere.
June 26, 2013 at 1:34 pm
So...are you saying the records you were expecting to see in tblActualPrice aren't there? Do you know for sure that your query returns results?
Once you execute the proc, go to the messages tab, copy and paste the SQL code to a new query window and start debugging it. Take the insert out and run the select by itself. Do you get a resultset back? If not you can't, continue breaking the select apart to figure out why.
June 26, 2013 at 1:42 pm
When i go into my tables, i do not see the tblActualPrice table. The SPROC when it was executing did return a value.
When i copied the sql code from the message tab and removed the insert line, i received the same message of syntax error line 7 at or after token <AS>
June 26, 2013 at 1:50 pm
actually received this error at the end of the message when the SPROC was executed
Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.
June 26, 2013 at 2:02 pm
Doh...I seem to always forget that. Change the last line to this:
EXEC sp_executesql @finalquery, N'@StartDate DATETIME, @PartTypeMP VARCHAR(1000)',@StartDate = @Start_Date, @PartTypeMP = @Part_Type_MP
June 26, 2013 at 2:05 pm
When executing the SPROC, i still receive this error message about line 7 at or after the <AS> token
OLE DB provider "MSDASQL" for linked server "PLEXREPORTSERVER" returned message "[Plex][ODBC ODBC Report Data Source driver][OpenAccess SDK SQL Engine]Syntax error in SQL statement. syntax error line 7 at or after token <AS>.[10179]".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT
PLK.Part_no,
PLK.line_item_key
FROM
(SELECT p.part_no, MAX(PLI.Line_Item_Key)
FROM Purchasing _v_Line_Item_e AS PLI
JOIN Part_v_Part_e AS P
ON p.plexus_customer_no = pli.plexus_customer_no
AND p.part_key = pli.part_key
WHERE pli.add_date <= @Start_Date
AND (@Part_Type_MP = ,, OR (CHARINDEX(, + CAST(P.Part_Type as VARCHAR(50)) + ,, @Part_Type_MP) >0))
GROUP by p.part_no
) AS "PLK"
" for execution against OLE DB provider "MSDASQL" for linked server "PLEXREPORTSERVER".
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply