If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > An unexpected token "." was found following "". Expected tokens may include: ", FRO

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2007
Posts: 29
An unexpected token "." was found following "". Expected tokens may include: ", FRO

I'm a little bit lost on this one. I use a SQL 2000 datbase and have been running a stored procedure for several years that hits a DB2 database. The DB2 server is linked. I need to make a small modification to the procedure that will pull in an additional date value to a temp table. What I thought would be very simple has me lost. The procedure is as follows and I've highlighted the area I added.

CREATE PROCEDURE sp_ivSqlAcquireCWMM_With_15K

@claim_nbr varchar(30)
AS
/* Declare variables */
declare @insert_sql varchar(1000)
declare @openquery_sql varchar(1000)
declare @fields_sql1 varchar(1000)
declare @from_sql varchar(1000)
declare @where_sql varchar(1000)
declare @claim_nbr_2 char(30)
declare @pri_con_type char(4)
--declare @Where_Por Char(30)

CREATE TABLE #tCaseContact
(
CLAIM_NBR char(30),
CASE_NBR CHAR(11),
INJ_SSN CHAR(9),
INJ_FNAME CHAR(20),
INJ_MID_INIT CHAR(1),
INJ_LNAME char(30),
INJ_NME_SUF CHAR(3),
TPLOC_NAME CHAR(60),
TPLOC_POLICY_NBR CHAR(20),
DT_OF_INJ DATETIME,
CASE_STS CHAR(4),
TEAM_NBR CHAR(4),
USER_KEY char(16),
TPA_ID CHAR(9),
CON_TYPE char(4),
CON_ID int,
PRIMARY_IND char(1),
EMP_Tier char(1),
Non_UM char(1),
POR char(42) NULL,
Cont_Type char(4),
Thousand_Date datetime)


/* Assert Claim Number from @claim_nbr parameter */
set @claim_nbr_2 = cast(@claim_nbr as char(30))

set @insert_sql = 'INSERT INTO #tCaseContact '
set @openquery_sql = ' ' +
' SELECT * FROM OPENQUERY( CWMM,''SELECT'
set @fields_sql1 = ' ' +
'a.twccs_claim_nbr, ' +
'a.twccs_case_nbr, ' +
'a.twccs_inj_ssn, ' +
'a.twccs_inj_fname, ' +
'a.twccs_inj_mid_init, ' +
'a.twccs_inj_lname, ' +
'a.twccs_inj_nme_suf, ' +
'mcdb.polloc_cw.tploc_name, ' +
'mcdb.polloc_cw.tploc_policy_nbr, ' +
'a.twccs_dt_of_inj, ' +
'a.twccs_case_sts, ' +
'mcdb.polloc_cw.tploc_team_nbr, ' +
'c.tctyp_user_key, ' +
'a.twccs_tpa_id, ' +
'b.twcon_con_type, ' +
'b.twcon_con_id_pers, ' +
'b.twcon_primary_ind, ' +
'e.tpoli_prem_tier, ' +
'e.tpoli_non_um, ' +
'f.NAME, ' +
'b.twcon_con_type, ' +
'a.twccs_1000_lmt_dt'


set @from_sql = '' +
'FROM ' +
'MCDB.WCCASE_CW AS a ' +
'INNER JOIN MCDB.WCCONTT AS b ON ' +
'(a.TWCCS_CASE_NBR = b.TWCON_CASE_NBR) ' +
'INNER JOIN MCDB.CONTYPT AS c ON ' +
'(b.TWCON_CON_ID_PERS = c.TCTYP_CON_ID) ' +
'INNER JOIN MCDB.POLLOC_CW AS d ON ' +
'(a.TWCCS_POLICY_NBR = d.TPLOC_POLICY_NBR) ' +
'INNER JOIN MCDB.POLICY_CW AS e ON ' +
'(d.TPLOC_POLICY_NBR = e.TPOLI_POLICY_NBR) ' +
'Left Outer JOIN PRODSYS.CWPV001_PROV_MASTER as f ON ' +
'(a.TWCCS_POR_NO = f.PROV_NO)'



SET @where_sql = '' +
'WHERE ' +
'b.TWCON_CON_TYPE IN (''''MCS'''', ''''MCN'''', ''''RRS'''') ' +
'AND ' +
'c.TCTYP_CON_TYPE = ''''USER'''' ' +
'AND ' +
'b.TWCON_STS = ''''A'''' ' +
'AND ' +
'a.TWCCS_CLAIM_NBR = ''''' + @claim_nbr_2 + '''''' +
''')'



exec( @insert_sql + @openquery_sql + @fields_sql1 +
@from_sql + @where_sql )

/*
SELECT * FROM #tCaseContact
*/
if (exists
(SELECT CLAIM_NBR
FROM #tCaseContact
WHERE CON_TYPE = 'MCN' AND PRIMARY_IND = '1'))
set @pri_con_type = cast('MCN' as char(4))
if (exists
(SELECT CLAIM_NBR
FROM #tCaseContact
WHERE CON_TYPE = 'MCS' AND PRIMARY_IND = '2'))
set @pri_con_type = cast('MCS' as char(4))
if (exists
(SELECT CLAIM_NBR
FROM #tCaseContact
WHERE CON_TYPE = 'MCS' AND CAST(PRIMARY_IND AS VARCHAR(4)) = '') AND
(exists
(SELECT CLAIM_NBR
FROM #tCaseContact
WHERE CON_TYPE = 'RRS' AND PRIMARY_IND = '4')))

set @pri_con_type = cast('RRS' as char(4))
if @pri_con_type is null set @pri_con_type = cast('MCS' as char(4))
/*sets the MCS as the Primary Contact if the primary contact is returned as null*/

/* if (exists
(Select POR
FROM #tCaseContact set POR=f.
where POR is not null))
if Por='' or Por is null
Else set POR=null
*/



SELECT
TOP 1
a.CLAIM_NBR ,
a.CASE_NBR ,
a.INJ_SSN ,
a.INJ_FNAME ,
a.INJ_MID_INIT ,
a.INJ_LNAME ,
a.INJ_NME_SUF ,
a.TPLOC_NAME ,
a.TPLOC_POLICY_NBR ,
convert(varchar(10),a.DT_OF_INJ, 101) as TWCCS_DT_OF_INJ,
a.CASE_STS ,
a.TEAM_NBR ,
(
SELECT top 1 a1.USER_KEY FROM #tCaseContact AS a1 WHERE a1.CON_TYPE = @pri_con_type
) as USER_KEY ,
a.TPA_ID,
a.EMP_TIER,
a.NON_UM,
a.POR,
(
SELECT top 1 a1.Con_Type FROM #tCaseContact AS a1 WHERE a1.CON_TYPE = @pri_con_type
) as Cont_Type,
convert(varchar(10),a. Thousand_Date,101) as twccs_1000_lmt_dt
FROM
#tCaseContact AS a
GO

Last edited by dadyswat; 11-19-07 at 11:38.
Reply With Quote
  #2 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,057
Code:
declare @openquery_sql varchar(1000)
Up the varchar limit to 2000 and give it a whirl. I think the variable is truncating.
__________________
George
Home | Blog
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2007
Posts: 29
I still get the same error. Just out of curiousity, does SQL have a problem where the column name has numeric values in it? The reason I'm wondering is when I first added the Thousand_Date to the Temp table I called it K_1000 and SQL basically told me the it was an invalid value, when I chenged it a=it accepted it OK. The DB2 column is twccs_1000_lmt_dt in the @fields_sql1 portion and I was wondering if that may have something to do with it.


Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][CLI Driver][DB2] SQL0104N An unexpected token "MCDB" was found following "". Expected tokens may include: ", FROM INTO". SQLSTATE=42601
]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

Last edited by dadyswat; 11-19-07 at 14:09.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,479
This looks like the DB2 server is having trouble with the query. Can you print out the select query that is being generated, and see if that runs via DB2's query tools?
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2007
Posts: 29
The only problem I have is my access to the DB2 database is limited to a view in a linked server. It's a remote database and they have it locked down. I don't have any DB2 utilities except for the OBDC conntection.

Last edited by dadyswat; 11-19-07 at 15:08.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,479
Time to get to know someone with the tools, then.

You may not even have to go that far, though. Even if you spit out the query from all the dynamic string concat functions, you may see the problem right off.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Aug 2007
Posts: 29
I'll see what I can do.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Aug 2007
Posts: 29
Just a quick follow up in case anyone else runs into a similar situation. The problem turned out to be a problem with the DB2 date format. One of the other SQL Admins ran into it when he was creating our data warehouse. to resolve the problem the following code had to be inserted into the open query statement.

' CASE ' +
' WHEN CAST(TWCCS_1000_LMT_DT AS DATE) < ''''1753-01-01''''' +

' THEN DATE(''''1753-01-01'''')' +

' ELSE CAST(TWCCS_1000_LMT_DT AS DATE)' +

' END ' +

' AS TWCCS_1000_LMT_DT '
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jun 2004
Location: Long Island
Posts: 696
Quote:
Originally Posted by dadyswat
Just a quick follow up in case anyone else runs into a similar situation. The problem turned out to be a problem with the DB2 date format. One of the other SQL Admins ran into it when he was creating our data warehouse. to resolve the problem the following code had to be inserted into the open query statement.

' CASE ' +
' WHEN CAST(TWCCS_1000_LMT_DT AS DATE) < ''''1753-01-01''''' +

' THEN DATE(''''1753-01-01'''')' +

' ELSE CAST(TWCCS_1000_LMT_DT AS DATE)' +

' END ' +

' AS TWCCS_1000_LMT_DT '

With that date is it a "Pilgrim Tracking System" ?

Happy turkey day.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On