December 5, 2007 at 12:22 pm
Hi experts,
I am using an openquery() function which opens a linked server connection to a Pervasive database. The select string complains of incorrect syntax but when I run it interactively in Pervasive it works.
Here's the select string that works in Pervasive:
SELECT Loc_No,Div_No,Pay_Date,Freq,Pr_No,Spare,Summ_No,Emp_No,
Item_No,Ref_No,Check_No,Ss_No,Wcomp_1,Wcomp_2,Per_Start,
case
when Per_End like
'%-04-31%' then datename(year,Per_End)+
'-05-01'
when Per_End like '%-06-31%' then datename(year,Per_End)+'-07-01'
when Per_End like '%-09-31%' then datename(year,Per_End)+'-10-01'
when Per_End like '%-11-31%' then datename(year,Per_End)+'-12-01'
when Per_End like '2009-02-29%' then '2009-03-01'
else Per_End
end as Per_End,
Extra,Hours,Units,Yn_1,Yn_2,Yn_3,Yn_4,Yn_5,
Yn_6,Yn_7,Yn_8,Yn_9,Yn_10,Amt_1,Amt_2,Amt_3,
Amt_4,Amt_5,Amt_6,Amt_7,Amt_8,Amt_9,Amt_10,Amt_11,
Amt_12,Amt_13,Amt_14,Amt_15,Amt_16,Amt_17,Amt_18,Amt_19,
Amt_20,Fica_1,Fica_2,Fica_3,Fica_4,Fica_5,Fica_6,Fica_7,
Fica_8,Fica_9,Fica_10,Fica_11,Fica_12,Fica_13,Fica_14,Fica_15,
Medc_1,Medc_2,Medc_3,Medc_4,Medc_5,Medc_6,Medc_7,Medc_8,
Medc_9,Medc_10,Medc_11,Medc_12,Medc_13,Medc_14,Medc_15,Futa_1,
Futa_2,Futa_3,Futa_4,Futa_5,Futa_6,Futa_7,Futa_8,Futa_9,
Futa_10,Futa_11,Futa_12,No_Items,No_Deducts,Fed_Tp_No,Fed_Tp_Div,Bank_No FROM pr_inp;
---------------
Below is what doesn't work in SQL Query Analyzer It complains: Server: Msg 170, Level 15, State 1, Line 103 Line 103: Incorrect syntax near '-'. Line 103 is the line ''-05-01'' (all quotes are single quotes) which is being EXEC'd with EXEC (@sqlstring). Things will parse but not execute. Can anyone tell me why it won't execute please.
Thanks for your help.
Warm regards,
p.s. The reason for doing this is that Pervasive has allowed invalid dates to be stored.
SET @RememberValue = ''
SET @RememberValue = (SELECT MAX(ref_no) FROM pr_inp WHERE opco=25)
SET @SQLString = ''
SET @SQLString1= ''
SET @SQLString2= ''
SET @SQLString1 = 'DECLARE pr_inp25cursor CURSOR GLOBAL STATIC FOR
select cast(Loc_No as integer) as Loc_No ,
cast(Div_No as integer) as Div_No ,
cast(Pay_Date as datetime) as Pay_Date ,
cast(Freq as char(1)) as Freq ,
cast(Pr_No as integer) as Pr_No ,
cast(Spare as integer) as Spare ,
cast(Summ_No as integer) as Summ_No ,
cast(Emp_No as integer) as Emp_No ,
cast(Item_No as integer) as Item_No ,
cast(Ref_No as integer) as Ref_No ,
cast(Check_No as integer) as Check_No ,
cast(Ss_No as integer) as Ss_No ,
cast(Wcomp_1 as decimal(8,2)) as Wcomp_1 ,
cast(Wcomp_2 as decimal(8,2)) as Wcomp_2 ,
cast(Per_Start as datetime) as Per_Start ,
cast(Per_End as datetime) as Per_End ,
cast(Extra as char(16)) as Extra ,
cast(Hours as decimal(8,4)) as Hours ,
cast(Units as decimal(8,4)) as Units ,
cast(Yn_1 as char(1)) as Yn_1 ,
cast(Yn_2 as char(1)) as Yn_2 ,
cast(Yn_3 as char(1)) as Yn_3 ,
cast(Yn_4 as char(1)) as Yn_4 ,
cast(Yn_5 as char(1)) as Yn_5 ,
cast(Yn_6 as char(1)) as Yn_6 ,
cast(Yn_7 as char(1)) as Yn_7 ,
cast(Yn_8 as char(1)) as Yn_8 ,
cast(Yn_9 as char(1)) as Yn_9 ,
cast(Yn_10 as char(1)) as Yn_10 ,
cast(Amt_1 as decimal(8,2)) as Amt_1 ,
cast(Amt_2 as decimal(8,2)) as Amt_2 ,
cast(Amt_3 as decimal(8,2)) as Amt_3 ,
cast(Amt_4 as decimal(8,2)) as Amt_4 ,
cast(Amt_5 as decimal(8,2)) as Amt_5 ,
cast(Amt_6 as decimal(8,2)) as Amt_6 ,
cast(Amt_7 as decimal(8,2)) as Amt_7 ,
cast(Amt_8 as decimal(8,2)) as Amt_8 ,
cast(Amt_9 as decimal(8,2)) as Amt_9 ,
cast(Amt_10 as decimal(8,2)) as Amt_10 ,
cast(Amt_11 as decimal(8,2)) as Amt_11 ,
cast(Amt_12 as decimal(8,2)) as Amt_12 ,
cast(Amt_13 as decimal(8,2)) as Amt_13 ,
cast(Amt_14 as decimal(8,2)) as Amt_14 ,
cast(Amt_15 as decimal(8,2)) as Amt_15 ,
cast(Amt_16 as decimal(8,2)) as Amt_16 ,
cast(Amt_17 as decimal(8,2)) as Amt_17 ,
cast(Amt_18 as decimal(8,2)) as Amt_18 ,
cast(Amt_19 as decimal(8,2)) as Amt_19 ,
cast(Amt_20 as decimal(8,2)) as Amt_20 ,
cast(Fica_1 as decimal(8,2)) as Fica_1 ,
cast(Fica_2 as decimal(8,2)) as Fica_2 ,
cast(Fica_3 as decimal(8,2)) as Fica_3 ,
cast(Fica_4 as decimal(8,2)) as Fica_4 ,
cast(Fica_5 as decimal(8,2)) as Fica_5 ,
cast(Fica_6 as decimal(8,2)) as Fica_6 ,
cast(Fica_7 as decimal(8,2)) as Fica_7 ,
cast(Fica_8 as decimal(8,2)) as Fica_8 ,
cast(Fica_9 as decimal(8,2)) as Fica_9 ,
cast(Fica_10 as decimal(8,2)) as Fica_10 ,
cast(Fica_11 as decimal(8,2)) as Fica_11 ,
cast(Fica_12 as decimal(8,2)) as Fica_12 ,
cast(Fica_13 as decimal(8,2)) as Fica_13 ,
cast(Fica_14 as decimal(8,2)) as Fica_14 ,
cast(Fica_15 as decimal(8,2)) as Fica_15 ,
cast(Medc_1 as decimal(8,2)) as Medc_1 ,
cast(Medc_2 as decimal(8,2)) as Medc_2 ,
cast(Medc_3 as decimal(8,2)) as Medc_3 ,
cast(Medc_4 as decimal(8,2)) as Medc_4 ,
cast(Medc_5 as decimal(8,2)) as Medc_5 ,
cast(Medc_6 as decimal(8,2)) as Medc_6 ,
cast(Medc_7 as decimal(8,2)) as Medc_7 ,
cast(Medc_8 as decimal(8,2)) as Medc_8 ,
cast(Medc_9 as decimal(8,2)) as Medc_9 ,
cast(Medc_10 as decimal(8,2)) as Medc_10 ,
cast(Medc_11 as decimal(8,2)) as Medc_11 ,
cast(Medc_12 as decimal(8,2)) as Medc_12 ,
cast(Medc_13 as decimal(8,2)) as Medc_13 ,
cast(Medc_14 as decimal(8,2)) as Medc_14 ,
cast(Medc_15 as decimal(8,2)) as Medc_15 ,
cast(Futa_1 as decimal(8,2)) as Futa_1 ,
cast(Futa_2 as decimal(8,2)) as Futa_2 ,
cast(Futa_3 as decimal(8,2)) as Futa_3 ,
cast(Futa_4 as decimal(8,2)) as Futa_4 ,
cast(Futa_5 as decimal(8,2)) as Futa_5 ,
cast(Futa_6 as decimal(8,2)) as Futa_6 ,
cast(Futa_7 as decimal(8,2)) as Futa_7 ,
cast(Futa_8 as decimal(8,2)) as Futa_8 ,
cast(Futa_9 as decimal(8,2)) as Futa_9 ,
cast(Futa_10 as decimal(8,2)) as Futa_10 ,
cast(Futa_11 as decimal(8,2)) as Futa_11 ,
cast(Futa_12 as decimal(8,2)) as Futa_12 ,
cast(No_Items as integer) as No_Items ,
cast(No_Deducts as integer) as No_Deducts ,
cast(Fed_Tp_No as integer) as Fed_Tp_No ,
cast(Fed_Tp_Div as integer) as Fed_Tp_Div ,
cast(Bank_No as integer) as Bank_No
from openquery(summit252007,''SELECT Loc_No,Div_No,Pay_Date,Freq,Pr_No,Spare,Summ_No,Emp_No,
Item_No,Ref_No,Check_No,Ss_No,Wcomp_1,Wcomp_2,Per_Start,
case
when Per_End like
''''\%-04-31\%'''' then datename(year,Per_End)+
''-05-01''
when Per_End like ''\%-06-31\%'' then datename(year,Per_End)+''-07-01''
when Per_End like ''\%-09-31\%'' then datename(year,Per_End)+''-10-01''
when Per_End like ''\%-11-31\%'' then datename(year,Per_End)+''-12-01''
when Per_End like ''2009-02-29\%'' then ''2009-03-01''
else Per_End
end as Per_End,
Extra,Hours,Units,Yn_1,Yn_2,Yn_3,Yn_4,Yn_5,
Yn_6,Yn_7,Yn_8,Yn_9,Yn_10,Amt_1,Amt_2,Amt_3,
Amt_4,Amt_5,Amt_6,Amt_7,Amt_8,Amt_9,Amt_10,Amt_11,
Amt_12,Amt_13,Amt_14,Amt_15,Amt_16,Amt_17,Amt_18,Amt_19,
Amt_20,Fica_1,Fica_2,Fica_3,Fica_4,Fica_5,Fica_6,Fica_7,
Fica_8,Fica_9,Fica_10,Fica_11,Fica_12,Fica_13,Fica_14,Fica_15,
Medc_1,Medc_2,Medc_3,Medc_4,Medc_5,Medc_6,Medc_7,Medc_8,
Medc_9,Medc_10,Medc_11,Medc_12,Medc_13,Medc_14,Medc_15,Futa_1,
Futa_2,Futa_3,Futa_4,Futa_5,Futa_6,Futa_7,Futa_8,Futa_9,
Futa_10,Futa_11,Futa_12,No_Items,No_Deducts,Fed_Tp_No,Fed_Tp_Div,Bank_No FROM pr_inp WHERE ref_no >= '
SET @SQLString2 = ''') pr_inp'
SET @SQLString = @SQLString1 + @RememberValue + @SQLString2
EXEC (@SQLString)
December 5, 2007 at 12:59 pm
What size are your @SqlString parameters declared as?
Try printing the @SQLString
Print @SQLString and in the messages tab, copy and paste that into a new window and see if you can see an error.
Why in the world are you declaring a cursor in Dynamic SQL.
Now I have seen it all
December 5, 2007 at 1:49 pm
Below are the declarations:
DECLARE @SQLString VARCHAR(8000)
DECLARE @SQLString1VARCHAR(6000)
DECLARE @SQLString2VARCHAR(50)
I have slightly change the last part of @SQLString to read:
from openquery(summit252007,''SELECT Loc_No,Div_No,Pay_Date,Freq,Pr_No,Spare,Summ_No,Emp_No,
Item_No,Ref_No,Check_No,Ss_No,Wcomp_1,Wcomp_2,Per_Start,
case
when Per_End like ''%-04-31%'' then datename(year,Per_End) + ''-05-01''
when Per_End like ''%-06-31%'' then datename(year,Per_End) + ''-07-01''
when Per_End like ''%-09-31%'' then datename(year,Per_End) + ''-10-01''
when Per_End like ''%-11-31%'' then datename(year,Per_End) + ''-12-01''
when Per_End like ''2009-02-29%'' then ''2009-03-01''
else Per_End
end as Per_End,
Extra,Hours,Units,Yn_1,Yn_2,Yn_3,Yn_4,Yn_5,
Yn_6,Yn_7,Yn_8,Yn_9,Yn_10,Amt_1,Amt_2,Amt_3,
Amt_4,Amt_5,Amt_6,Amt_7,Amt_8,Amt_9,Amt_10,Amt_11,
Amt_12,Amt_13,Amt_14,Amt_15,Amt_16,Amt_17,Amt_18,Amt_19,
Amt_20,Fica_1,Fica_2,Fica_3,Fica_4,Fica_5,Fica_6,Fica_7,
Fica_8,Fica_9,Fica_10,Fica_11,Fica_12,Fica_13,Fica_14,Fica_15,
Medc_1,Medc_2,Medc_3,Medc_4,Medc_5,Medc_6,Medc_7,Medc_8,
Medc_9,Medc_10,Medc_11,Medc_12,Medc_13,Medc_14,Medc_15,Futa_1,
Futa_2,Futa_3,Futa_4,Futa_5,Futa_6,Futa_7,Futa_8,Futa_9,
Futa_10,Futa_11,Futa_12,No_Items,No_Deducts,Fed_Tp_No,Fed_Tp_Div,Bank_No FROM pr_inp WHERE ref_no >= '
And below is the way the openquery looks when I print it:
openquery(summit252007,'SELECT Loc_No,Div_No,Pay_Date,Freq,Pr_No,Spare,Summ_No,Emp_No,
Item_No,Ref_No,Check_No,Ss_No,Wcomp_1,Wcomp_2,Per_Start,
case
when Per_End like '%-04-31%' then datename(year,Per_End) + '-05-01'
when Per_End like '%-06-31%' then datename(year,Per_End) + '-07-01'
when Per_End like '%-09-31%' then datename(year,Per_End) + '-10-01'
when Per_End like '%-11-31%' then datename(year,Per_End) + '-12-01'
when Per_End like '2009-02-29%' then '2009-03-01'
else Per_End
end as Per_End,
Extra,Hours,Units,Yn_1,Yn_2,Yn_3,Yn_4,Yn_5,
Yn_6,Yn_7,Yn_8,Yn_9,Yn_10,Amt_1,Amt_2,Amt_3,
Amt_4,Amt_5,Amt_6,Amt_7,Amt_8,Amt_9,Amt_10,Amt_11,
Amt_12,Amt_13,Amt_14,Amt_15,Amt_16,Amt_17,Amt_18,Amt_19,
Amt_20,Fica_1,Fica_2,Fica_3,Fica_4,Fica_5,Fica_6,Fica_7,
Fica_8,Fica_9,Fica_10,Fica_11,Fica_12,Fica_13,Fica_14,Fica_15,
Medc_1,Medc_2,Medc_3,Medc_4,Medc_5,Medc_6,Medc_7,Medc_8,
Medc_9,Medc_10,Medc_11,Medc_12,Medc_13,Medc_14,Medc_15,Futa_1,
Futa_2,Futa_3,Futa_4,Futa_5,Futa_6,Futa_7,Futa_8,Futa_9,
Futa_10,Futa_11,Futa_12,No_Items,No_Deducts,Fed_Tp_No,Fed_Tp_Div,Bank_No FROM pr_inp WHERE ref_no >= 193743') pr_inp
When i copy and paste the SELECT into Pervasive's interface it works perfectly.
P.S. I'm declaring a cursor in Dynamic SQL because of all the people in my company no one can reasonable explain/figure out why this one table synchronization takes 17 hours or fails after 21 hours! There are other tables that I am synchronizing that have 50% more records and they take no more than 2.5 hours and re-indexing runs every night just before the synchronization. Anyway, the source table receives only inserts records are never modified so by declaring my cursor in this manner I can add a where clause and retrieve only the new records. You may have noticed my other post looking for a way to do the synchronization involving a table on a linked server (the Pervasive database) and do it without using cursors.
Warm regards,
December 5, 2007 at 4:38 pm
:D, Just playin about the cursor,
I don't think the error your getting has to do with the open query query, its coming from somewhere in the string.
Either your string is getting truncated (Parameter not big enough), you have not enough/too many ' (apostrophes).
Thats why I suggested print the entire string, and try to execute the entire thing manually the print statement should show you exactly what is getting executed.
December 5, 2007 at 8:00 pm
You are missing ' at the end of @SQLString1
December 6, 2007 at 3:11 pm
Ray, thank you, I did follow through on the printing and it was posted before your reply got posted. If you see anything I would appreciate it. When I cut and pasted the string it worked in Pervasive SQL GUI interface. It seems as though SQL Server is inspecting what is in the string when it shouldn't. If openquery() doesn't do variables then it should darn well not care what I put in the string! I really would like to fix the data automatically when I bring it in. My alternative is to manually fix it in Pervasive.
In the interest of getting the job done I have already manually fixed the bad dates in Pervasive but it would be nice to have this sync run automatically and not fail right after I have forgotten what to do and how to do it and don't really have the time to first remember and then fix.
Thank you and warm regards,
Hope
December 6, 2007 at 3:15 pm
Koji, thank you, the text on the web site is hard to see some things but I can assure you there is a single quote before the last parenthesis. I have had many people here looking over my shoulder. If you have any other constructs I could try they would be tried. If you see my other posts I have a manual way to clean the data in Pervasive but that's not really a solution.
Thank you and warm regards,
Hope
December 7, 2007 at 12:10 am
You are not having enough quotes in the case block of the open query statement. You need to add 4 quotes instead of two as the statement is the part of a nested and quoted statement.
--Ramesh
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply