SQL Anywhere ultralite - Synching with MS Sequel Server

Author

Synching with MS Sequel Server

Lucas Deby

2005-04-11, 8:24 pm

I recently ported my Sybase DB to MS Sequel server and I am trying to sync.
All my sync scripts work except those that run stored procedures such as
this:

ALTER PROCEDURE "AWADMIN"."asi_SelectTaskList"(@LastModified datetime,
@LoginID varchar(20))
AS
BEGIN
DECLARE @ResourceID varchar(20)
Select @ResourceID = id from auser where loginID = @LoginID
Select FS_Task_List.ID, EST_TIME
from FS_Task_List inner join FS_WO_Job on FS_Task_List.ID =
FS_WO_Job.Task_List_ID inner join fs_WO_tech on FS_WO_Job.WO_ID =
FS_WO_Tech.WO_ID
inner join FS_WO on FS_WO_Job.WO_ID = FS_WO.ID
where (FS_Task_List.Last_Modified>=@LastModified or
FS_WO_Job.Last_Modified >= @LastModified or
FS_WO_Tech.Created_On>=@LastModified)
and FS_WO_Tech.Resource_ID = @ResourceID and FS_Task_List.Deleted=0 and
FS_WO_Job.Deleted=0
and FS_WO_Tech.Deleted=0 and FS_WO.Deleted=0 and
FS_WO.IsRemovedFromHH=0
union
Select FS_Task_List.ID, EST_TIME
from FS_Task_List inner join FS_WO_Job on FS_Task_List.ID =
FS_WO_Job.Task_List_ID inner join fs_WO_tech on FS_WO_Job.WO_ID =
FS_WO_Tech.WO_ID
inner join FS_WO_Status_History
on FS_WO_Job.WO_ID =
FS_WO_Status_History
.WO_ID
inner join FS_WO on FS_WO_Job.WO_ID = FS_WO.ID
where FS_WO_Tech.Resource_ID = @ResourceID and FS_Task_List.Deleted=0
and FS_WO_Job.Deleted=0 and FS_WO_Tech.Deleted=0 and Trans_Date >=
@LastModified and old_status = 'NEW' and FS_WO.Deleted=0
and FS_WO.IsRemovedFromHH=0
END

Here is the error:

E. 04/11 16:51:00. <1.20> [1001]: Error: [-10002] ODBC: [Microsoft][ODBC SQL
Server Driver]Syntax error or access violation (ODBC State = 42000, Native
error code = 0)
E. 04/11 16:51:00. <1.20> [1001]: Error: Unable to open download_cursor
I. 04/11 16:51:00. <1.20> [1001]: Error Context:
I. 04/11 16:51:00. <1.20> [1001]: User Name: 1001
I. 04/11 16:51:00. <1.20> [1001]: Modified User Name: 1001
I. 04/11 16:51:00. <1.20> [1001]: Transaction: download
I. 04/11 16:51:00. <1.20> [1001]: Table Name: FS_Task_List
I. 04/11 16:51:00. <1.20> [1001]: Script Version: FieldServiceV2
I. 04/11 16:51:00. <1.20> [1001]: Script: {call asi_SelectTaskList(?,?)}
I. 04/11 16:51:00. <1.20> [1001]: End of Error Context

It seems that the procedure returns once it hits the first select in the
stored procedure ie) Select @ResourceID = id from auser where loginID =
@LoginID even though this select is just a variable assignment.

If I comment out the first select and leave only the one select then the
statement will execute without error.

Is there a solution to this issue?

Thank you
Lucas Deby

MobiLink Synchronization 9 9.0.2.2551

Reg Domaratzki $iAnywhere Solutions$

2005-04-12, 9:23 am

Be sure to put "SET NOCOUNT ON" at the start of all stored procedures and
batches that are executed via ODBC.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Lucas Deby" <Lucas_Deby@ApexSI.com> wrote in message
news:425aed6a$1@foru
ms-1-dub...
>
> I recently ported my Sybase DB to MS Sequel server and I am trying to
sync.
> All my sync scripts work except those that run stored procedures such as
> this:
>
> ALTER PROCEDURE "AWADMIN"."asi_SelectTaskList"(@LastModified datetime,
> @LoginID varchar(20))
> AS
> BEGIN
> DECLARE @ResourceID varchar(20)
> Select @ResourceID = id from auser where loginID = @LoginID
> Select FS_Task_List.ID, EST_TIME
> from FS_Task_List inner join FS_WO_Job on FS_Task_List.ID =
> FS_WO_Job.Task_List_ID inner join fs_WO_tech on FS_WO_Job.WO_ID =
> FS_WO_Tech.WO_ID
> inner join FS_WO on FS_WO_Job.WO_ID = FS_WO.ID
> where (FS_Task_List.Last_Modified>=@LastModified or
> FS_WO_Job.Last_Modified >= @LastModified or
> FS_WO_Tech.Created_On>=@LastModified)
> and FS_WO_Tech.Resource_ID = @ResourceID and FS_Task_List.Deleted=0
and
> FS_WO_Job.Deleted=0
> and FS_WO_Tech.Deleted=0 and FS_WO.Deleted=0 and
> FS_WO.IsRemovedFromHH=0
> union
> Select FS_Task_List.ID, EST_TIME
> from FS_Task_List inner join FS_WO_Job on FS_Task_List.ID =
> FS_WO_Job.Task_List_ID inner join fs_WO_tech on FS_WO_Job.WO_ID =
> FS_WO_Tech.WO_ID
> inner join FS_WO_Status_History
on FS_WO_Job.WO_ID =
> FS_WO_Status_History
.WO_ID
> inner join FS_WO on FS_WO_Job.WO_ID = FS_WO.ID
> where FS_WO_Tech.Resource_ID = @ResourceID and FS_Task_List.Deleted=0
> and FS_WO_Job.Deleted=0 and FS_WO_Tech.Deleted=0 and Trans_Date >=
> @LastModified and old_status = 'NEW' and FS_WO.Deleted=0
> and FS_WO.IsRemovedFromHH=0
> END
>
>
> Here is the error:
>
> E. 04/11 16:51:00. <1.20> [1001]: Error: [-10002] ODBC: [Microsoft][ODBC
SQL
> Server Driver]Syntax error or access violation (ODBC State = 42000, Native
> error code = 0)
> E. 04/11 16:51:00. <1.20> [1001]: Error: Unable to open download_cursor
> I. 04/11 16:51:00. <1.20> [1001]: Error Context:
> I. 04/11 16:51:00. <1.20> [1001]: User Name: 1001
> I. 04/11 16:51:00. <1.20> [1001]: Modified User Name: 1001
> I. 04/11 16:51:00. <1.20> [1001]: Transaction: download
> I. 04/11 16:51:00. <1.20> [1001]: Table Name: FS_Task_List
> I. 04/11 16:51:00. <1.20> [1001]: Script Version: FieldServiceV2
> I. 04/11 16:51:00. <1.20> [1001]: Script: {call asi_SelectTaskList(?,?)}
> I. 04/11 16:51:00. <1.20> [1001]: End of Error Context
>
>
> It seems that the procedure returns once it hits the first select in the
> stored procedure ie) Select @ResourceID = id from auser where loginID =
> @LoginID even though this select is just a variable assignment.
>
> If I comment out the first select and leave only the one select then the
> statement will execute without error.
>
> Is there a solution to this issue?
>
> Thank you
> Lucas Deby
>
> MobiLink Synchronization 9 9.0.2.2551
>
>
>
>

Lucas Deby

2005-04-12, 11:24 am

Hello Reg,

Excellent!! This worked, I was able to sync all my tables. I would like to
keep my sybase and sql Server stored procedures identical, can I add this
statement ie) "SET NOCOUNT ON" to my sybase stored procedures as well to
keep them consistent or will this cause problems.

Thanks again,

Lucas

"Reg Domaratzki (iAnywhere Solutions)" < Spam_bad_rdomarat@ia
nywhere.com>
wrote in message news:425bc6c5$1@foru
ms-1-dub...
> Be sure to put "SET NOCOUNT ON" at the start of all stored procedures and
> batches that are executed via ODBC.
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL
>
> "Lucas Deby" <Lucas_Deby@ApexSI.com> wrote in message
> news:425aed6a$1@foru
ms-1-dub...
> sync.
> and
FS_Task_List. Deleted=0[color=dark
red]
> SQL
Native[color=darkred
]
>
>

Lucas Deby

2005-04-12, 11:24 am

I just checked and 'SET NOCOUNT ON' is not a valid sybase command. I will
maintaing 2 versions of my scripts.

Thanks,
Lucas

"Lucas Deby" <Lucas_Deby@ApexSI.com> wrote in message
news:425be104$1@foru
ms-1-dub...
> Hello Reg,
>
> Excellent!! This worked, I was able to sync all my tables. I would like
to
> keep my sybase and sql Server stored procedures identical, can I add this
> statement ie) "SET NOCOUNT ON" to my sybase stored procedures as well to
> keep them consistent or will this cause problems.
>
> Thanks again,
>
> Lucas
>
>
>
> "Reg Domaratzki (iAnywhere Solutions)" < Spam_bad_rdomarat@ia
nywhere.com>
> wrote in message news:425bc6c5$1@foru
ms-1-dub...
and[color=darkred]
> http://www.ianywhere.com/developer/product_manuals
as[color=darkred]
FS_Task_List. Deleted=0[color=dark
red]
> FS_Task_List.Deleted=0
[Microsoft][ODBC[col
or=darkred]
> Native
download_cursor[colo
r=darkred]
asi_SelectTaskList(?,?)}[color=darkred]
the[color=darkred]
=[color=darkred]
the[color=darkred]
>
>

Sponsored Links