Solved

SQL Syntax issue for

Posted on 2014-09-20
7
301 Views
Last Modified: 2014-11-04
The code below works fine when the data is as it appears on 4779-4781. However the first 2 lines fail with
[SQLCODE: <-25>:<Input encountered after end of query>]
[Location: <Prepare>]
[%msg: < Input (IDENTIFIER) encountered after end of query^SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id = :%qpar(1) - :%qpar(2) - :%qpar(3) B>]".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query " SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id  = 00002-8215-17B" for execution against OLE DB provider "MSDASQL" for linked server "santaclara".


DRUG_ID                                        id
(01)00305483012008      4777
+H20619521+                      4778
00000-0000-02                   4779
00000-0000-03                      4780
00000-0000-05                      4781

 DECLARE @LastCustomerID CHAR(10) -- for auditing
Declare @table_link char (12)     -- link to ID column
DECLARE @CustomerIDToHandle varchar(100) -- trans_date
SET @LastCustomerID = 0
Set @table_link=4828
-- 340 1358 3499 4776 4777 4778 4795 4827 failed
Declare @sql varchar(500)
declare @sql2 varchar(5000)
set @sql = ''
set @sql2 = ''
 -- define the customer ID to be handled now
WHILE @table_link  IS not null
-- select the next customer to handle    
Begin
SELECT @CustomerIDToHandle = drug_id 
FROM dbo.new_admin_dates
WHERE ID = @table_link 
-- SET @CustomerIDToHandle = '"' + @CustomerIDToHandle + '"'

print 'cust id = ' + @CustomerIDToHandle + '    table_link = ' + @table_link
-- as long as we have customers......     



-- no insert in the open query, just select.

--Set @sql = @sql + ' SELECT * FROM OPENQUERY(santaclara,'' SELECT * FROM PHHST_orders where PTMEDREC  = '+@CustomerIDToHandle+''''+')'Set @sql = @sql + ' SELECT * FROM OPENQUERY(santaclara,'' SELECT * FROM PHHST_orders where PTMEDREC  = '+@CustomerIDToHandle+''''+')'
Set @sql = ' SELECT * FROM OPENQUERY(santaclara,'' SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id  = '+@CustomerIDToHandle+''''+')'


print @sql
 insert into  temp_PHMAC_ADMIN_MEDIV exec (@SQL)

 print ' start copypaste '
 -- exec copypaste 
--INSERT INTO phm_cures (TRANS_DATE, SITE, ITN, KEY4, PATIENT_LAST_NAME, PATIENT_FIRST_NAME, PTBIRTH, PTSEX, PAT_ADDRESS, PTCITY, PTSTATE, PTZIP, PHM_CURES_ID, PAT_NUM, BILLING_PAT_NUM, DR_NO, DR_NAME, PTFINCL, PTTYPE, PTMEDREC, PTNRSTA, PTCLASS, PTSERV, PAT_HEIGHT_CM, PAT_WEIGHT_KG, CALC_CREAT_CLEAR, CCRC_DATE, CCRC_TIME, PMP, ADJUST, COMPONENT_TYPE, COMP_NO, TRANS_QTY, REFILLS_ALLOWED, REFILLS_REMAINING, DAYS_SUPPLY, CDM_NO, SERVICE_DATE, UNIT_COST, DR_LICENSE, USER_1, USER_2, DRUG_CODE, NDC, PRESCRIPTION_NO, PRICE, PRIMARY_NAME, STRENGTH, DOSE, ENTERED_BY, SIG_OR_FREQUENCY, DEA_CODE, DOSAGE_FORM, DISPENSING_UNIT, CABINET, ROUTE_OR_METHOD, SCH_PRN_TKH, PRIORITY, RX_LOCATION, START_DATE, START_TIME, STOP_DATE, STOP_TIME, METH_OF_DIST, THER_CODE_1, ORD_DRNO, ORD_DRNAME, PRIMARY_NAME_TM, PTAGE, IMR, PTAGE_IN_DAYS, PTCLINIC, PTBED, ADMDT, DCH_DT, MEDICAID_NDC_UNIT_COST, JCODE_WASTE_QTY, JCODE_WASTE_COST, JCODE_DISP_QTY, JCODE_DISP_COST, MEDICAID_NDC_NODU, PERSON_NUMBER, MEDICAID_NDC_NUMBER, MEDICAID_NDC_QUANTITY, MEDICAID_NDC_UNIT_OF_MEASURE) select * FROM temp_phm_cures
INSERT INTO PHMAC_ADMIN_MEDIV (DRUG_ID , SITE , DRUG_ID_TYPE , GENERIC_NAME , SEQ , DRUG_ID_TYPE_DESC , PTMEDREC , PAT_NUM , ITN , ATTDOCNU , ATTDOCNA , PTCLASS , PTTYPE , PTNAME , NRS_STATION , BED , CLINIC , CHARTED_DATE , CHARTED_TIME , ADMIN_BY , ADM_DOSE_OVR , PMP , OCC_DATE , OCC_TIME , DOSE , NO_OF_DUS , BRAND_NAME , PRIMARY_NAME , ROUTE , IV_METHOD , DOSAGE_FORM , LABELER , ORD_DRNO , ORD_DRNAME , ORDER_NDC , DRUG_CODE , GCS , METH_OF_DIST , RX_LOCATION , COMP_NO , TRANS_DATE , TRANS_TIME , IMR , DRUG_ID_SOURCE , ADMIN_SITE , PERSON_NUMBER , LOT_NUMBER , EXPIRATION_DATE , MANUFACTURER , MVX_CODE) select * FROM temp_PHMAC_ADMIN_MEDIV

-- Insert into phhst_orders  select * from #temp_phhst_orders 
 --keep the table as you're going to re-use it.[/b]
 delete from temp_PHMAC_ADMIN_MEDIV
-- select * from PHHST_ORDERS_SS 
 
 
 
 -- exec copypaste
  print ' end copypaste '

    -- set the last customer handled to the one we just handled and increase the user ID by one; loop up 
    
    SET @LastCustomerID = @table_link 
    SET @CustomerIDToHandle = NULL
    set @table_link = @table_link + 1   
    
    
 END

Open in new window

0
Comment
Question by:hwassinger
  • 4
  • 2
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40334701
Your dynamically generated query is not producing a leading single quote

... where drug_id = (01)00305483012008')
... where drug_id = '(01)00305483012008')
                                 ^
I believe this may be the cause.

DECLARE @SQL varchar(max)
DECLARE @LastCustomerID char(10) -- for auditing
DECLARE @table_link char(12)     -- link to ID column
DECLARE @CustomerIDToHandle varchar(100) -- trans_date

SET @LastCustomerID = 0
SET @table_link = 4777 --4828


      SELECT
            @CustomerIDToHandle = drug_id
      FROM dbo.new_admin_dates
      WHERE ID = @table_link

      SET @sql = ' SELECT * FROM OPENQUERY(santaclara,'' SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id  = ''' + @CustomerIDToHandle + '''' + ')'


select @sql

Open in new window

See: http://sqlfiddle.com/#!3/92415/3

To recreate
    CREATE TABLE new_admin_dates
    	([DRUG_ID] varchar(18), [id] int)
    ;
    	
    INSERT INTO new_admin_dates
    	([DRUG_ID], [id])
    VALUES
    	('(01)00305483012008', 4777),
    	('+H20619521+', 4778),
    	('00000-0000-02', 4779),
    	('00000-0000-03', 4780),
    	('00000-0000-05', 4781)
    ;

**Query 1**:

    DECLARE @SQL varchar(max)
    DECLARE @LastCustomerID char(10) -- for auditing
    DECLARE @table_link char(12)     -- link to ID column
    DECLARE @CustomerIDToHandle varchar(100) -- trans_date
    
    SET @LastCustomerID = 0
    SET @table_link = 4777 --4828
    
    
          SELECT
                @CustomerIDToHandle = drug_id
          FROM dbo.new_admin_dates
          WHERE ID = @table_link
    
          SET @sql = ' SELECT * FROM OPENQUERY(santaclara,'' SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id  = ''' + @CustomerIDToHandle + '''' + ')'
    
    
    select @sql
    

**[Results][2]**:
    
    |                                                                                                     COLUMN_0 |
    |--------------------------------------------------------------------------------------------------------------|
    |  SELECT * FROM OPENQUERY(santaclara,' SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id  = '(01)00305483012008') |



  [1]: http://sqlfiddle.com/#!3/92415/3

Open in new window

0
 

Author Comment

by:hwassinger
ID: 40337767
This fails with

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ')'.

 SELECT * FROM OPENQUERY(santaclara,' SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id  = '(01)00305483012008')
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40337939
try this please:

 SELECT * FROM OPENQUERY(santaclara,' SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id  = ''(01)00305483012008')
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:hwassinger
ID: 40338185
OLE DB provider "MSDASQL" for linked server "santaclara" returned message "[Cache ODBC][State : 37000][Native Code 3]
[C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.MARS\MSSQL\Binn\sqlservr]
[SQLCODE: <-3>:<Closing quote (") missing>]
[Location: <Prepare>]
[%msg: < Closing quote (') missing^SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id = '(01)00305483012008>]".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query " SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id  = '(01)00305483012008" for execution against OLE DB provider "MSDASQL" for linked server "santaclara".
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40338192
Now it's complaining about no closing quote.... I'm afraid I cannot test any suggestions but each error message is trying to be helpful.

All I can suggest now is more single quotes, this time at the end

try:
SELECT * FROM OPENQUERY(santaclara, ' SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id  = ''(01)00305483012008''')

if that doesn't work:
SELECT * FROM OPENQUERY(santaclara, ' SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id  = ''(01)00305483012008'')

If neither work I think I'm out of ideas, but let me know which one you tried and any error messages for it.
0
 
LVL 4

Accepted Solution

by:
parthmalhan earned 500 total points
ID: 40338530
Hi,
There's one more syntax error in above Query.
Missing single Quote ant end before ).

Check for Following Query:

SELECT * FROM OPENQUERY(santaclara, ' SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id  = ''(01)00305483012008''');
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40338588
It is impossible for me to experiment/test this I'm afraid, you will need to do this yourself unless/until someone else joins in.

The issue is the quotation marks that surround the embedded string. These need to be adjusted at both ends until some combination works.

A typical SQL command would look like this:

SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id  = '(01)00305483012008'

Here is the documentation page:
http://msdn.microsoft.com/en-us/library/ms188427.aspx
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question