Solved

SQL Syntax issue for

Posted on 2014-09-20
7
268 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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