?
Solved

Openquery Insert & Exec

Posted on 2005-04-01
7
Medium Priority
?
970 Views
Last Modified: 2012-05-05
Have a current script which does the following on sql Server 2003 download from Oracle:

1) Create the table LTic1 with the ticketNumber being a 10 digit string with the first four digits representing the year
2) Inserts data using open query which joins two tables and only downloads the tickets with the year of ''2005'' or ''2004''

This works. What I would like to do is to make the script automatically determine the years to be downloaded.  So created and populated two  script variables: @CurrentYr and @PastYr.  They contain the proper information.

It seems one must use Exec in order to use a script variable, so @sql VARCHAR(8000) was created to hold the entire query.  

1) In the script, the table is created then the insert into new table using openquery is loaded into the @sql variable.  Next, exec @sql.  Is this allowed?
2) Have tried a variety of ways to set @sql with the very big query but am receiving the error:

Server: Msg 203, Level 16, State 2, Line 14
The name 'INSERT INTO LTic1(ST_TICKET_NUMBER ) SELECT * FROM OPENQUERY(ORAVGER,'SELECT ST_TICKET_NUMBER FROM SUMMARY_TICKET1 WHERE (substr(ST_TICKET_NUMBER,1,4) = ''2005'' or substr(ST_TICKET_NUMBER,1,4) = ''2004'') AND ST_ETE_WORK_CENTER = ''CCC'' ORDER BY t1.ST_TICKET_NUMBER') ' is not a valid identifier.

This query above reflects a simple version of the true query but even the simple version isn't working...  

Help....
0
Comment
Question by:Glass
[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
7 Comments
 
LVL 11

Expert Comment

by:Otana
ID: 13680992
When you build your @sql variable, make sure you replace all single qutoes with 2 single quotes, otherwise the string will be cut off early.

Can you show a bit more of your code? Especially where you assign to the @sql variable and how you execute it?
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13681036
'INSERT INTO LTic1(ST_TICKET_NUMBER ) SELECT * FROM OPENQUERY(ORAVGER,''SELECT ST_TICKET_NUMBER FROM SUMMARY_TICKET1 WHERE (substr(ST_TICKET_NUMBER,1,4) = ''2005'' or substr(ST_TICKET_NUMBER,1,4) = ''2004'') AND ST_ETE_WORK_CENTER = ''CCC'' ORDER BY t1.ST_TICKET_NUMBER') '
0
 

Author Comment

by:Glass
ID: 13697901
declare @cmd varchar(8000)
Below is just the select without the insert in the query analyzer.  Upon execution it  errors with the message below.  

set @cmd = 'SELECT * FROM OPENQUERY(ORAVGER,''SELECT t1.ST_TICKET_NUMBER, ' +  
' t1.ST_LAST_MODIFIED_DATE, ' +
' t2.ST_PRI_CONTACT_CLLI ' +
' FROM SUMMARY_TICKET1 t1, SUMMARY_TICKET2 t2 ' +
' WHERE (substr(t1.ST_TICKET_NUMBER,1,8) = ''''20050315''''  AND ST_ETE_WORK_CENTER = ''''CCC'''' AND ' +
' t1.ST_TICKET_NUMBER = t2.ST_TICKET_NUMBER(+)' +
' ORDER BY t1.ST_TICKET_NUMBER'') '''

exec @cmd
go

Server: Msg 203, Level 16, State 2, Line 262
The name 'SELECT * FROM OPENQUERY(ORAVGER,'SELECT t1.ST_TICKET_NUMBER,  t1.ST_LAST_MODIFIED_DATE,  t2.ST_PRI_CONTACT_CLLI  FROM SUMMARY_TICKET1 t1, SUMMARY_TICKET2 t2  WHERE (substr(t1.ST_TICKET_NUMBER,1,8) = ''20050315''  AND ST_ETE_WORK_CENTER = ''CCC'' AND  t1.ST_TICKET_NUMBER = t2.ST_TICKET_NUMBER(+) ORDER BY t1.ST_TICKET_NUMBER') '' is not a valid identifier.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:Glass
ID: 13697980
Above was missing a paren.  Still have same error message.  


declare @cmd varchar(8000)
Below is just the select without the insert in the query analyzer.  Upon execution it  errors with the message below.  

set @cmd = 'SELECT * FROM OPENQUERY(ORAVGER,''SELECT t1.ST_TICKET_NUMBER, ' +  
' t1.ST_LAST_MODIFIED_DATE, ' +
' t2.ST_PRI_CONTACT_CLLI ' +
' FROM SUMMARY_TICKET1 t1, SUMMARY_TICKET2 t2 ' +
' WHERE (substr(t1.ST_TICKET_NUMBER,1,8) = ''''20050315''''  AND ST_ETE_WORK_CENTER = ''''CCC'''' AND ' +
' t1.ST_TICKET_NUMBER = t2.ST_TICKET_NUMBER(+)) ' +
' ORDER BY t1.ST_TICKET_NUMBER'') '''

exec @cmd
go

Server: Msg 203, Level 16, State 2, Line 262
The name 'SELECT * FROM OPENQUERY(ORAVGER,'SELECT t1.ST_TICKET_NUMBER,  t1.ST_LAST_MODIFIED_DATE,  t2.ST_PRI_CONTACT_CLLI  FROM SUMMARY_TICKET1 t1, SUMMARY_TICKET2 t2  WHERE (substr(t1.ST_TICKET_NUMBER,1,8) = ''20050315''  AND ST_ETE_WORK_CENTER = ''CCC'' AND  t1.ST_TICKET_NUMBER = t2.ST_TICKET_NUMBER(+) ORDER BY t1.ST_TICKET_NUMBER') '' is not a valid identifier.
0
 

Author Comment

by:Glass
ID: 13707287
Figured it out....    No problem with the query it was with the EXEC.  The variable '@cmd' needed to be enclosed in parenthesis.  

Glass
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13707317
Greate!!

itsvtk
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 13711369
Question answered by asker or dialog deemed valuable.
Closed, 500 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

765 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