Powerbuilder 7 and Linked Server of Sql Server 2000

In the itemchanged event I am trying this

case "dye_lot_no"
     select a.dye_lot_no,a.fact_order_no,quality_code,fabric_type,colour_code,left_right_tag,length_mtr,count(*)
     into   :ls_dlot,:ls_forder,:ls_quality,:ls_fabric,:ls_colour,:ls_lr_tag,:ldec_length,:ll_count
     from [sdmsrv].siddiqsons.dbo.prd_dye_lot_det a
     where dye_lot_no = '4661'
     and company_code = '01'
     group by a.dye_lot_no,
              a.fact_order_no,
              a.quality_code,
              a.fabric_type,
              a.colour_code,
              a.left_right_tag,
              a.length_mtr;

powerbuilder does not compile this syntax and gives the folowing message.

<< Could not find server 'sdmsrv' in sysservers. Execute sp_addlinked server to add the server
to sysservers>>
When run by ignoring this message then at runtime we have error
<< Could not find server 'sdmsrv' in sysservers. Execute sp_addlinked server to add the server
to sysservers>>

My SQLSERVER scenario.

We have a group [loomdata] and it has a database looms_monitor_data.

When the application is run the user (currently me with all rights) is connected to the database  
loomdata.looms_monitor_data

We have another group [sdmsrv] and it has a database siddiqsons.

In group [loomdata] I have created a linked server(the creation is correct) sdmsrv.

The query runs fine in query analyser.

It seems that power builder is unable to recognize Linked Server.

Please note that I tried to resolve this problem resolved creating a view in group loomdata for necessary coloumns of table in database siddiqsons in group sdmsrv and using this view as a local object. The errors then seem to be more dangerous. Something like hetrogeneous query .............

What can be done?


MateenAsked:
Who is Participating?
 
diasroshanCommented:
hi,

how abt trying a work around...

create a transaction object and connect to
 Server Group: sdmsrv
 database:siddiqsons

i guess ur currect transaction object or SQLCA is connecting to
 Server Group: loomdata
 database: looms_monitor_data

after creating a new transaction object u can write ur query as  
  select a.dye_lot_no,a.fact_order_no,quality_code,fabric_type,colour_code,left_right_tag,length_mtr,count(*)
     into   :ls_dlot,:ls_forder,:ls_quality,:ls_fabric,:ls_colour,:ls_lr_tag,:ldec_length,:ll_count
     from prd_dye_lot_det a
     where dye_lot_no = '4661'
     and company_code = '01'
     group by a.dye_lot_no,
              a.fact_order_no,
              a.quality_code,
              a.fabric_type,
              a.colour_code,
              a.left_right_tag,
              a.length_mtr
using it_trans;  //where it_trans is an instance of ur new transaction object connecting to the other server.

im sure u know what im suggesting...
http://www.experts-exchange.com/Programming/Programming_Languages/PowerBuilder/Q_21037794.html?query=+it_trans+&topics=92

give it a try and let me know if it helps or if u need more assistance...

Cheers,
Rosh
0
 
gajender_99Commented:
you don't have that scehma linked to the current user.
grant previlages to the schema for the curent user or try loging in with the user which is having permision for that schema
0
 
diasroshanCommented:
hi,

i guess the idea abt creatin a view and using it is the best option and  i wud also have suggested u to do the same especially when it comes to workin across db servers...

now what we can try is to eliminate the errors that are comin...

first,
does the view work alright in query analyzer...(try runnin ur DW sql in query analyzer)...

second,
if u want to still go abt using the linked server check ur privilleges and linking... i guess u are missing something...

Cheers,
Rosh
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MateenAuthor Commented:
Hi dia:

I think u r little mistaken.

Whether or not I use view I cannot escape link server because
 The user is connected to (through powerscript )
    Server Group: loomdata
    database: looms_monitor_data
and since the data I want to get lies in another group
   Server Group: sdmsrv
   database:siddiqsons
   table: prd_dye_lot_det

1) When the connection is made, the user is standing in loomdata group (Currently me and I have unlimited rights)
therefore I must create linked server in group loomdata ( I gave the linked server name as sdmsrv)

When I write select * from [sdmsrv].siddiqsons.dbo.prd_dye_lot_det in query analyzer
then sql server understand that [sdmsrv] is a linked server and give me all the rows.

While, I think, Powerbuilder is not looking for [sdmsrv] as  linked server box.



0
 
MateenAuthor Commented:
Well
I have created this view in loomdata.looms_monitor_data

create view  v_dyelot_of_siddiqsons as
     select a.dye_lot_no,a.fact_order_no,quality_code,fabric_type,colour_code,left_right_tag,length_mtr,company_code
     from [sdmsrv].siddiqsons.dbo.prd_dye_lot_det a

Now I opened powerbuilder and clicked database button. Then I went to view objects
and clicked v_dyelot_of_siddiqsons ->edit->data
it says
<< Hetrogeneous query requires the ANSI Nulls and ANSI warnings options to be set for the connection . This ensures consistent query semantics. Enable these options and then reissue yur query>>
0
 
diasroshanCommented:
hi,

if u have a dsn then there are 2 options, one for ANSI Nulls and the other for ANSI warnings... just check these checkboxes and see if the error persists....

or, try
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

Cheers,
Rosh
0
 
MateenAuthor Commented:
Hi dia:

There is some success.

When I am connected natively MSS MS Sql Server 6.x and try to view data in v_dyelot_of_siddiqsons by clicking database button of powerbuilder
it says ansi_nulls and ansi_warnings should be reset. And in the itemchanged event
powerbuilder does not compile the script. << Could not find server....>>

When I am connected throug ODBC then I am able to view data of v_dyelot_of_siddiqsons and the
pb compilation is also error free but then at runtime the error is same as << Could not find server
I have checked dsn ansi_nulls and ansi_warnings are on.

Please note that this application is supposed to be run through native driver and not odbc.
0
 
MateenAuthor Commented:
Hi dia:

I am desperately waiting 4 your comment.
0
 
diasroshanCommented:
hi,

workin on it... give me some time...

Cheers,
Rosh
0
 
MateenAuthor Commented:
Thanks
0
 
MateenAuthor Commented:
I understand the idea and should be quite workable.

My current connection script is
SQLCA.DBMS = "MSS Microsoft SQL Server 6.x"
SQLCA.Database = "looms_monitor_data"
SQLCA.ServerName = "loomdata"
SQLCA.DBParm = "ConnectString='DSN=loom_monitor'"
SQLCA.LogId = 'sa'
SQLCA.LogPass= f_decrypt('ëËßãéÓ')
SQLCA.AutoCommit = False
connect;

What is the syntax of creating new transaction and where should I write this.
Where Should I write second connection script.
When I would connect to second transaction then will the user be disconnected
from default transaction.
0
 
MateenAuthor Commented:
I am working on your idea.......
0
 
diasroshanCommented:
hi,

'What is the syntax of creating new transaction and where should I write this.
Where Should I write second connection script.'.... for this refer to the link above... i guess uve done this before so u wudnt have any problems with it....

'When I would connect to second transaction then will the user be disconnected
from default transaction. '...... NO... u need to refer to the new transaction with the new name Eg. it_trans...
Eg.
select a.dye_lot_no,a.fact_order_no,quality_code,fabric_type,colour_code,left_right_tag,length_mtr,count(*)
     into   :ls_dlot,:ls_forder,:ls_quality,:ls_fabric,:ls_colour,:ls_lr_tag,:ldec_length,:ll_count
     from prd_dye_lot_det a
     where dye_lot_no = '4661'
     and company_code = '01'
     group by a.dye_lot_no,
              a.fact_order_no,
              a.quality_code,
              a.fabric_type,
              a.colour_code,
              a.left_right_tag,
              a.length_mtr
using it_trans;


'using it_trans' //dont forget to add this to use the other transaction object...

ur default will always be SQLCA...

try it out.... id help if u r stuck somewhere....

Cheers,
Rosh
0
 
MateenAuthor Commented:
I have worked on your idea and it is working perfectly.

In the Item Changed Event
I am doing this

case "fact_order_no"

ls_dlot = dw_detail.getitemstring(ll_row_det,"dye_lot_no")

transaction trans_siddiqsons
trans_siddiqsons = CREATE transaction
trans_siddiqsons.DBMS = "MSS Microsoft SQL Server 6.x"
trans_siddiqsons.Database = "siddiqsons"
trans_siddiqsons.ServerName = "sdmsrv"
trans_siddiqsons.DBParm = "ConnectString='DSN=loom_monitor'"
trans_siddiqsons.LogId = 'sa'
trans_siddiqsons.LogPass= f_decrypt('ëËßãéÓ')
trans_siddiqsons.AutoCommit = False
CONNECT USING trans_siddiqsons;


select      a.fact_order_no,quality_code,fabric_type,colour_code,left_right_tag,length_mtr,count(*)
     into   :ls_forder,:ls_quality,:ls_fabric,:ls_colour,:ls_lr_tag,:ldec_length,:ll_count
     from    prd_dye_lot_det a
     where dye_lot_no = :ls_dlot
        and   fact_order_no = :data
     and company_code = '01'
     group by a.dye_lot_no,
              a.fact_order_no,
              a.quality_code,
              a.fabric_type,
              a.colour_code,
              a.left_right_tag,
              a.length_mtr
            using trans_siddiqsons;

I want to write connection script in my login window.
If I remove connection script lines from item changed event and write in
my login window then in item changed event the compiler say variable trans_siddiqsons not found.
How to make trans_siddiqsons global.

I must say, this new idea, has opened a world for us.
We did not use multiple transaction before and now realize that there were many situations to use.

Thank you very much.



0
 
diasroshanCommented:
hi,

create a standard class userobject of type transaction.....

the details are in the link that ive posted earlier...

u can access this userobject throughout ur application...


Cheers,
Rosh
0
 
MateenAuthor Commented:
Very glad to accept answer.
0
 
diasroshanCommented:
Thanx for the points and grade !!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.