Cursor with Variables in Openquery

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

SQL Server 2000 Forums

SQL Server Development (2000)

Cursor with Variables in Openquery

Author

Topic

Adriana
Starting Member

4 Posts

Posted - 2008-05-19 : 09:36:11

Hello,
I have been following some of the examples you are giving in the forums about having variables in an OPENQUERY. (trying to get some rows from an Oracle server. It works perfectly when I select the rows that I want. Unfurtunally I haven't been able to put those rows into a cursor in order to introduce those rows to a table that I have in the SQL server.
How can I put a cursor??? I have declared the variables, but I cannot declare correctly the cursor. Is it possible?? Thanks a lot

Declare @GEMA_ID varchar(20), @REMEDY_ID varchar(20), @vGEMA_ID char (20), @vID_OT char(12), @vID_EQUIPO char(18), @vPUESTO_TRABAJO char(8), @vESTADO_OT char (12), @vFEC_ACT_OT datetime, @vFEC_CREACION datetime, @vFEC_INSERT datetime

DECLARE @sql_str nvarchar (4000)

Select @GEMA_ID = (select Max(cast(GEMA_ID as numeric))From datos_Gema)

Select @REMEDY_ID = (SELECT * FROM OPENQUERY(CMPPRM01, 'select Max(cast(SERI_CHR_ENTRY_ID_RECIBIDO as numeric))From ARADMIN.SERI_VISUALIZACION_DATOS_GEMA'))

print @GEMA_ID
print @REMEDY_ID

select @sql_str=
'Select SERI_CHR_ENTRY_ID_RECIBIDO,
SERI_CHR_VOT, SERI_CHR_VEQUIPO, SERI_CHR_PUESTOTRABAJOACT, SERI_CHR_VDESORD,
TO_DATE(TO_CHAR(TO_DATE(''1/1/1970 00:00:00'',''DD/MM/YYYY HH24:MI:SS'')+(MODIFIED_DATE)/86400,''DD/MM/YYYY HH24:MI:SS''),''DD/MM/RR HH24:MI:SS''),
sysdate, TO_DATE(TO_CHAR(TO_DATE(''1/1/1970 00:00:00'',''DD/MM/YYYY HH24:MI:SS'')+(CREATE_DATE)/86400,''DD/MM/YYYY HH24:MI:SS''),''DD/MM/RR HH24:MI:SS'')
From ARADMIN.SERI_VISUALIZACION_DATOS_GEMA
WHERE SERI_CHR_ENTRY_ID_RECIBIDO > '''+@GEMA_ID+''' and
SERI_CHR_ENTRY_ID_RECIBIDO = '''+@REMEDY_ID+''''

SET @sql_str = N'select * from OPENQUERY(CMPPRM01, ''' + REPLACE(@jm, '''', '''''') + ''')'*/

print @jm
exec (@jm)

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-19 : 12:31:57

Don't use a cursor. Cursors are horrible horrible things.

Just do an insert.

An infinite universe is the ultimate cartesian product.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-19 : 13:07:45

Can you specify what your requirement is along with your tables. I'm pretty sure your reqmnt cant be achieved without use of cursor.

Adriana
Starting Member

4 Posts

Posted - 2008-05-20 : 03:42:42

It seems like I need the cursor, because depending on what I get from the Oracle table I will fill my table in SQL.
So after the cursor I have an if condition. Here I send I had before the openquery and it worked perfectly, the problem was that for Oracle I was selecting all the table without taking care of my conditions. Once I changed it to an openquery, Oracle liked more but SQL didn't that much. Well here it goes the query. And I hope you are understanding what I need.

Declare @GEMA_ID integer, @REMEDY_ID integer, @vGEMA_ID char (20), @vID_OT char(12), @vID_EQUIPO char(18), @vPUESTO_TRABAJO char(8), @vESTADO_OT char (12), @vFEC_ACT_OT datetime, @vFEC_CREACION datetime, @vFEC_INSERT datetime

Select @GEMA_ID = (select Max(cast(GEMA_ID as numeric))From datos_Gema)
Select @REMEDY_ID = (select Max(cast(SERI_CHR_ENTRY_ID_RECIBIDO as numeric))From CMPPRM01..ARADMIN.SERI_VISUALIZACION_DATOS_GEMA)
print @GEMA_ID
print @REMEDY_ID

begin tran

Declare c_Datos_Gema scroll cursor for
Select SERI_CHR_ENTRY_ID_RECIBIDO, SERI_CHR_VOT, SERI_CHR_VEQUIPO, SERI_CHR_PUESTOTRABAJOACT, SERI_CHR_VDESORD,
Dateadd(ss,MODIFIED_DATE, '1970/01/01') Modified_date,
GetDate() Fec_Insert, Dateadd(ss,CREATE_DATE,'1970/01/01') Create_Date
From CMPPRM01..ARADMIN.SERI_VISUALIZACION_DATOS_GEMA
where SERI_CHR_ENTRY_ID_RECIBIDO > @GEMA_ID and
SERI_CHR_ENTRY_ID_RECIBIDO <= @REMEDY_ID

Open c_Datos_Gema

fetch next from c_Datos_Gema into @vGEMA_ID, @vID_OT, @vID_EQUIPO, @vPUESTO_TRABAJO, @vESTADO_OT, @vFEC_ACT_OT, @vFEC_INSERT, @vFEC_CREACION

while (@@Fetch_Status<>-1)
begin
if @vID_OT in (select ID_OT from DATOS_GEMA)
update DATOS_GEMA set ID_EQUIPO = @vID_EQUIPO,
PUESTO_TRABAJO = @vPUESTO_TRABAJO, FEC_ACT_OT = @vFEC_ACT_OT,
ESTADO_OT = @vESTADO_OT,
GEMA_ID = @vGEMA_ID
where ID_OT = @vID_OT

else

insert into DATOS_GEMA (GEMA_ID, ID_OT, ID_EQUIPO,
PUESTO_TRABAJO, ESTADO_OT, FEC_ACT_OT, FEC_INSERT,
FEC_CREACION)
values (@vGEMA_ID, @vID_OT, @vID_EQUIPO, @vPUESTO_TRABAJO,
@vESTADO_OT, @vFEC_ACT_OT, @vFEC_INSERT,
@vFEC_CREACION)

fetch next from c_Datos_Gema into @vGEMA_ID, @vID_OT, @vID_EQUIPO, @vPUESTO_TRABAJO,@vESTADO_OT, @vFEC_ACT_OT, @vFEC_INSERT,@vFEC_CREACION

end

Close c_Datos_Gema
DEALLOCATE c_Datos_Gema

Commit Tran

Adriana
Starting Member

4 Posts

Posted - 2008-05-21 : 04:12:37

Hi!!!

Someone has any idea of how to do a cursor in an openquery with variables??? Need some help
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-21 : 05:23:26

WOnt this be enough?

Declare @GEMA_ID integer, @REMEDY_ID integer, 
Select @GEMA_ID = (select Max(cast(GEMA_ID as numeric))From datos_Gema)
Select @REMEDY_ID = (select Max(cast(SERI_CHR_ENTRY_ID_RECIBIDO as numeric))From CMPPRM01..ARADMIN.SERI_VISUALIZACION_DATOS_GEMA)
print @GEMA_ID
print @REMEDY_ID

begin tran

update d
set d.ID_EQUIPO = t.SERI_CHR_VEQUIPO, 
d.PUESTO_TRABAJO = t.SERI_CHR_PUESTOTRABAJOACT,
d.FEC_ACT_OT = t.SERI_CHR_VDESORD, 
other fields
FROM DATOS_GEMA d
INNER JOIN (
Select SERI_CHR_ENTRY_ID_RECIBIDO, SERI_CHR_VOT, SERI_CHR_VEQUIPO, SERI_CHR_PUESTOTRABAJOACT, SERI_CHR_VDESORD,
Dateadd(ss,MODIFIED_DATE, '1970/01/01') Modified_date, 
GetDate() Fec_Insert, Dateadd(ss,CREATE_DATE,'1970/01/01') Create_Date 
From CMPPRM01..ARADMIN.SERI_VISUALIZACION_DATOS_GEMA
where SERI_CHR_ENTRY_ID_RECIBIDO > @GEMA_ID and 
SERI_CHR_ENTRY_ID_RECIBIDO <= @REMEDY_ID

)t
ON t.SERI_CHR_VOT=d.ID_OT 



insert into DATOS_GEMA (GEMA_ID, ID_OT, ID_EQUIPO, 
PUESTO_TRABAJO, ESTADO_OT, FEC_ACT_OT, FEC_INSERT, 
FEC_CREACION)
SELECT 
FROM
(Select SERI_CHR_ENTRY_ID_RECIBIDO, SERI_CHR_VOT, SERI_CHR_VEQUIPO, SERI_CHR_PUESTOTRABAJOACT...
From CMPPRM01..ARADMIN.SERI_VISUALIZACION_DATOS_GEMA
where SERI_CHR_ENTRY_ID_RECIBIDO > @GEMA_ID and 
SERI_CHR_ENTRY_ID_RECIBIDO <= @REMEDY_ID)t
LEFT JOIN DATOS_GEMA  d
ON t.SERI_CHR_VOT=d.ID_OT
WHERE d.ID_OT IS NULL

Commit Tran

Adriana
Starting Member

4 Posts

Posted - 2008-05-21 : 07:56:04

Thank you, but it doesn't work, because of the connection
CMPPRM01..ARADMIN.SERI_VISUALIZACION_DATOS_GEMA. The problem is in this way Oracle doesn't take care of the WHERE condition and takes the whole tables, which is an inconvenient because the table has too many entries. Therefore when I use the OPENQUERY the Oracle does take consider of the WHERE conditions.
I will try to do what you have suggested me and see if it works in an openquery. I will let you know.
Thanks again..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources