Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Powerbuilder 7 and Linked Server of Sql Server 2000

Posted on 2005-05-09
17
Medium Priority
?
845 Views
Last Modified: 2013-12-26
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?


0
Comment
Question by:Mateen
  • 9
  • 7
17 Comments
 
LVL 8

Expert Comment

by:gajender_99
ID: 13957747
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
 
LVL 18

Expert Comment

by:diasroshan
ID: 13957859
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
 

Author Comment

by:Mateen
ID: 13957973
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Mateen
ID: 13957996
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
 
LVL 18

Expert Comment

by:diasroshan
ID: 13958100
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
 

Author Comment

by:Mateen
ID: 13958178
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
 

Author Comment

by:Mateen
ID: 13958552
Hi dia:

I am desperately waiting 4 your comment.
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13958911
hi,

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

Cheers,
Rosh
0
 

Author Comment

by:Mateen
ID: 13958936
Thanks
0
 
LVL 18

Accepted Solution

by:
diasroshan earned 2000 total points
ID: 13966131
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
 

Author Comment

by:Mateen
ID: 13966346
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
 

Author Comment

by:Mateen
ID: 13966655
I am working on your idea.......
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13966719
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
 

Author Comment

by:Mateen
ID: 13966764
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
 
LVL 18

Expert Comment

by:diasroshan
ID: 13966860
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
 

Author Comment

by:Mateen
ID: 13967524
Very glad to accept answer.
0
 
LVL 18

Expert Comment

by:diasroshan
ID: 13967596
Thanx for the points and grade !!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question