Openquery Insert & Exec

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....
GlassAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ee_ai_constructConnect With a Mentor Commented:
Question answered by asker or dialog deemed valuable.
Closed, 500 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0
 
OtanaCommented:
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
 
Thandava VallepalliCommented:
'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
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.

 
GlassAuthor Commented:
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
 
GlassAuthor Commented:
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
 
GlassAuthor Commented:
Figured it out....    No problem with the query it was with the EXEC.  The variable '@cmd' needed to be enclosed in parenthesis.  

Glass
0
 
Thandava VallepalliCommented:
Greate!!

itsvtk
0
All Courses

From novice to tech pro — start learning today.