Mateen
asked on
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_
into :ls_dlot,:ls_forder,:ls_qu
from [sdmsrv].siddiqsons.dbo.pr
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_dat
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?
case "dye_lot_no"
select a.dye_lot_no,a.fact_order_
into :ls_dlot,:ls_forder,:ls_qu
from [sdmsrv].siddiqsons.dbo.pr
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_dat
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?
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
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
ASKER
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.pr
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.
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.pr
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.
ASKER
Well
I have created this view in loomdata.looms_monitor_dat
create view v_dyelot_of_siddiqsons as
select a.dye_lot_no,a.fact_order_
from [sdmsrv].siddiqsons.dbo.pr
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>>
I have created this view in loomdata.looms_monitor_dat
create view v_dyelot_of_siddiqsons as
select a.dye_lot_no,a.fact_order_
from [sdmsrv].siddiqsons.dbo.pr
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>>
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
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
ASKER
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.
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.
ASKER
Hi dia:
I am desperately waiting 4 your comment.
I am desperately waiting 4 your comment.
hi,
workin on it... give me some time...
Cheers,
Rosh
workin on it... give me some time...
Cheers,
Rosh
ASKER
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_m
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.
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_m
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.
ASKER
I am working on your idea.......
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_
into :ls_dlot,:ls_forder,:ls_qu
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
'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_
into :ls_dlot,:ls_forder,:ls_qu
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
ASKER
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
transaction trans_siddiqsons
trans_siddiqsons = CREATE transaction
trans_siddiqsons.DBMS = "MSS Microsoft SQL Server 6.x"
trans_siddiqsons.Database = "siddiqsons"
trans_siddiqsons.ServerNam
trans_siddiqsons.DBParm = "ConnectString='DSN=loom_m
trans_siddiqsons.LogId = 'sa'
trans_siddiqsons.LogPass= f_decrypt('ëËßãéÓ')
trans_siddiqsons.AutoCommi
CONNECT USING trans_siddiqsons;
select a.fact_order_no,quality_co
into :ls_forder,:ls_quality,:ls
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.
In the Item Changed Event
I am doing this
case "fact_order_no"
ls_dlot = dw_detail.getitemstring(ll
transaction trans_siddiqsons
trans_siddiqsons = CREATE transaction
trans_siddiqsons.DBMS = "MSS Microsoft SQL Server 6.x"
trans_siddiqsons.Database = "siddiqsons"
trans_siddiqsons.ServerNam
trans_siddiqsons.DBParm = "ConnectString='DSN=loom_m
trans_siddiqsons.LogId = 'sa'
trans_siddiqsons.LogPass= f_decrypt('ëËßãéÓ')
trans_siddiqsons.AutoCommi
CONNECT USING trans_siddiqsons;
select a.fact_order_no,quality_co
into :ls_forder,:ls_quality,:ls
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.
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
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
ASKER
Very glad to accept answer.
Thanx for the points and grade !!!
grant previlages to the schema for the curent user or try loging in with the user which is having permision for that schema