Solved

SQL Syntax issue for

Posted on 2014-09-20
7
246 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now