Solved

SQL Syntax issue for

Posted on 2014-09-20
7
336 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 49

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 49

Expert Comment

by:PortletPaul
ID: 40337939
try this please:

 SELECT * FROM OPENQUERY(santaclara,' SELECT * FROM PHMAC_ADMIN_MEDIV where drug_id  = ''(01)00305483012008')
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 49

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 49

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
In this article I will describe the Copy Database Wizard 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.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

632 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