OpenQuery, Variable and Creating a Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

OpenQuery, Variable and Creating a Table

Author

Topic

dtanis
Starting Member

14 Posts

Posted - 2004-07-19 : 15:19:29

I am having trouble figuring out the syntax for the following...
I am passing a variable to an openquery using the two variable method (one for the actual "variable" and one for the query). I would like to insert the result set into a table, but no matter where I put the "into table" statement, I get the following error:
"Incorrect syntax near the keyword 'Into'."

I have tried putting in the @Query definition and also tried it with Exec @Query (i.e. Exec @Query Into Data)

Any help is welcome. Code is below. Thanks!

Here is the code:
-------------------------------------------------------------
Drop Table Data

GO

Declare @Query VarChar(8000), @Start_Dte VarChar(20)

Set @Start_Dte = '17-JUL-2004 21:00:00'

Set @Query = 'Select * From OpenQuery (CDB,
''Select
*
From
CDB.WorkOrder
Where
Last_Change_Date_And_Time > To_Date(''''' + @Start_Dte + ''''',''''DD-MM-YYYY HH24:MI:SS'''') And RowNum < 10
'')'

Exec (@Query)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-20 : 00:08:25

Try This:

Drop Table Data

GO

Declare @Query VarChar(8000)

Set @Query = 'DECLARE @Start_Dte VarChar(20)
Set @Start_Dte = ''17-JUL-2004 21:00:00''
Select * From OpenQuery (CDB,
''Select
*
From
CDB.WorkOrder
Where
Last_Change_Date_And_Time > To_Date('''''' + @Start_Dte + '''''',''''DD-MM-YYYY HH24:MI:SS'''') And RowNum < 10
'')'

Exec (@Query)

-- I hope this works :)

Duane.

dtanis
Starting Member

14 Posts

Posted - 2004-07-20 : 08:17:16

Thanks, but I am still not sure where to put the "....into TABLE..." statement?

I can get a could result set...I just can not get it into a table.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-07-20 : 12:27:19

Ok,

Goes here:
....
Set @Query = 'Select * INTO MYTABLE From OpenQuery (CDB,
....

/rockmoose

dtanis
Starting Member

14 Posts

Posted - 2004-07-21 : 07:19:44

I thought so too, but I get the following error:

-----------------------------------------------
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'Into'.
-----------------------------------------------

It does not seem to like the into not matter where I place it.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-07-21 : 11:53:23

Ok,
I tried this on a linked Sql Server 2000. And had no problems.
My example:
Declare @Query VarChar(8000)

set @query = '
Select * into ##t From OpenQuery (LNK_SRV,
''Select * from sysobjects'')'

exec(@query)
select * from ##t
go
drop table ##t
go

Maybe the CDB linked server ( Oracle? ) doesn't support the INTO keyword.
Also, is it correct to have 2 quotes here: ... To_Date(''17-JUL-2004 21:00:00'',''DD-MM-YYYY HH24:MI:SS'') ...

/rockmoose

l.pinna
Starting Member

4 Posts

Posted - 2004-07-27 : 07:13:46

Try this:


set @query = '
Insert into ##t Select * From OpenQuery (LNK_SRV,
''Select * from sysobjects'')'

Luca Pinna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources