Has anyone successfully created a linked server to a MAS90 database? I have
tried creating a UDL file and copying the connection string into the linked
server area, but Im running into errors when trying to access anything in
the database. I have also tried using OPENQUERY with the same connection
string from the UDL. I am able to create a linked table to MAS in access
just fine. I have emailed Sage, they gave me a sample but it didnt work.
select * from
openrowset( 'MSDASQL',
'DSN=SOTAMAS90;UID=7AB;PWD=;DESCRIPTION=MAS 90 32-Bit ODBC Driver;
Directory=\\MAS200\MAS90;Company=D94;SERVER=NotTheServer','select * from
GL5_DetailPosting' ) as A
This is the error message with OPENQUERY, which is more descriptive then
EM's error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider reported an
unexpected catastrophic failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x8000ffff: The provider reported an unexpected catastrophic
failure.].I normally use udl file if I want to create a connection string in the
asp/.net file. rather than hand type the mumbo jumbo, I can just copy
and paste into the file. But never on creating a linked server.
I don't think you can use the connection string the udl file created
(at least not entirely) to create a linked server. Particularly you
hit a problem now, it is better to create it from scratch.
Here is the syntax of OPENROWSET.
OPENROWSET ( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query' }
)
For your case (with the information above), it should be:
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=NotTheServer;UID=7AB;PWD=xxx',
GL5_DetailsPosting) AS a
For the driver name, check your ODBC datasource Administrator panel
'drivers' tag.
Melsql
Showing posts with label string. Show all posts
Showing posts with label string. Show all posts
Wednesday, March 21, 2012
Linked server help: MAS90 URGENT!
Has anyone successfully created a linked server to a MAS90 database? I have
tried creating a UDL file and copying the connection string into the linked
server area, but Im running into errors when trying to access anything in
the database. I have also tried using OPENQUERY with the same connection
string from the UDL. I am able to create a linked table to MAS in access
just fine. I have emailed Sage, they gave me a sample but it didnt work.
select * from
openrowset( 'MSDASQL',
'DSN=SOTAMAS90;UID=7AB;PWD=;DESCRIPTION=
MAS 90 32-Bit ODBC Driver;
Directory=\\MAS200\MAS90;Company=D94;SER
VER=NotTheServer','select * from
GL5_DetailPosting' ) as A
This is the error message with OPENQUERY, which is more descriptive then
EM's error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider reported an
unexpected catastrophic failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x8000ffff: The provider reported an unexpected catastrophic
failure.].I normally use udl file if I want to create a connection string in the
asp/.net file. rather than hand type the mumbo jumbo, I can just copy
and paste into the file. But never on creating a linked server.
I don't think you can use the connection string the udl file created
(at least not entirely) to create a linked server. Particularly you
hit a problem now, it is better to create it from scratch.
Here is the syntax of OPENROWSET.
OPENROWSET ( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query' }
)
For your case (with the information above), it should be:
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=NotTheServer;UID=7AB;PWD=
xxx',
GL5_DetailsPosting) AS a
For the driver name, check your ODBC datasource Administrator panel
'drivers' tag.
Mel
tried creating a UDL file and copying the connection string into the linked
server area, but Im running into errors when trying to access anything in
the database. I have also tried using OPENQUERY with the same connection
string from the UDL. I am able to create a linked table to MAS in access
just fine. I have emailed Sage, they gave me a sample but it didnt work.
select * from
openrowset( 'MSDASQL',
'DSN=SOTAMAS90;UID=7AB;PWD=;DESCRIPTION=
MAS 90 32-Bit ODBC Driver;
Directory=\\MAS200\MAS90;Company=D94;SER
VER=NotTheServer','select * from
GL5_DetailPosting' ) as A
This is the error message with OPENQUERY, which is more descriptive then
EM's error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider reported an
unexpected catastrophic failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x8000ffff: The provider reported an unexpected catastrophic
failure.].I normally use udl file if I want to create a connection string in the
asp/.net file. rather than hand type the mumbo jumbo, I can just copy
and paste into the file. But never on creating a linked server.
I don't think you can use the connection string the udl file created
(at least not entirely) to create a linked server. Particularly you
hit a problem now, it is better to create it from scratch.
Here is the syntax of OPENROWSET.
OPENROWSET ( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query' }
)
For your case (with the information above), it should be:
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=NotTheServer;UID=7AB;PWD=
xxx',
GL5_DetailsPosting) AS a
For the driver name, check your ODBC datasource Administrator panel
'drivers' tag.
Mel
Monday, March 12, 2012
Linked Server Connection to Sybase
Has anyone had problems using an OLEDB linked server connection to Sybase ASE 12.5? I'm having major performance problems when I use string criteria in the where clause. Queries that return 1 row and execute in less than a second using a native Sybase connection take 40 seconds to run using the linked server OLEDB connection. If I use ints in the where clause performance is almost exactly the same between native and linked connections.
Any ideas?Did I mention I hate sybase...
Just had a project thrown at me...never touched it before...
I know there "cousins", but man is it quirky...
Saw a developer do
SELECT Col1, MAX(Col2), SUM(Col3)
FROM Table
WHERE Col1 = 'A'
AND IT RAN!
I couldn't believe it...and it gave back complete garbage...
Can you post your statement?
I'll try and link that server up and see what happens...
(No wonder they lost market share)|||Hi, I had a similar problem with ASE6, using a linked server in SQL server 2000.
The problem was I had a very poor performance (10 secs per query), and I had to make an update in 200,000 regs using that query for every record... also I had problems in my linked server too (it worked when it wanted, i never discovered the cause...)
My solution was very desesperate: instead of connecting SQL server to the ODBC linked to the ASE server, I made a program in Visual Basic with a connection to the ODBC. I wrote the update procedure with ADO.
The results: the whole update was finished in 15 secs (remember: in SQL server each query took 10 secs, multiplied by 200,000 records...).
I dont understand why this happens, a SQL server should be faster accessing data behind an ODBC than a VB program...|||Here's the SQL:
select *
from [32tlsql2-dreamdb].dreamdb.dbo.drt_contract
where i_con_contract = '000Q019999'
and c_company = 'P00'
and c_mkt_division = 'IPD'
Here's the table DDL (please don't make jokes about the crappy design of the table. Unfortunately I got stuck with it):
/****************** CREATE ORDER ******************/
create table dbo.drt_contract
(
i_client numeric(10,0) not null ,
i_con_contract char(10) not null ,
c_company char(3) not null ,
c_mkt_division char(3) not null ,
c_level numeric(2,0) Default 1 not null ,
c_interface_company char(6) null ,
c_form char(8) not null ,
c_con_contract_type char(2) not null ,
c_agent_type char(3) null ,
c_con_status char(2) null ,
d_final_disposition datetime null ,
d_lifecom_updated datetime null ,
c_lifecom_status char(1) null ,
d_con_terminate datetime null ,
d_con_da_report_terminate datetime null ,
d_con_signed datetime null ,
d_con_effective datetime null ,
c_con_line_of_business char(1) null ,
i_con_rsm char(10) null ,
c_con_publicity char(1) null ,
c_con_uw_eligiblity char(1) null ,
c_con_premium_remission char(1) null ,
q_con_years_experience char(1) null ,
c_con_pay char(2) null ,
c_con_report char(2) null ,
c_con_review char(1) null ,
c_con_additional_aa_trailer char(8) null ,
x_con_text varchar(250) null ,
c_con_terminate_reason char(2) null ,
f_con_assign_commissions char(1) null ,
n_con_assign_commissions varchar(50) null ,
i_con_financial_owner char(10) null ,
c_con_financial_owner_level char(1) null ,
i_con_address_owner char(10) null ,
c_con_address_owner_level char(1) null ,
c_con_mail char(1) null ,
c_con_hire_type char(2) null ,
c_con_change_type char(2) null ,
c_zipcode_processing char(1) null ,
n_con_sub_name varchar(30) null ,
d_con_nasd_hire datetime null ,
c_con_nasd_status char(2) null ,
i_con_alt_rep1 char(10) null ,
i_con_alt_rep2 char(10) null ,
i_con_alt_rep3 char(10) null ,
i_con_branch char(10) null ,
c_con_rep_class char(2) null ,
i_con_old_rep_nbr varchar(10) null ,
i_con_old_branch char(10) null ,
i_user_last_change char(8) null ,
h_last_change datetime null
)
Lock Datarows on "default"
go
/****************** ALTER ORDER CONSTRAINTS ******************/
alter table dbo.drt_contract
add constraint pk_contract primary key clustered ( i_con_contract asc ,c_company asc ,c_mkt_division asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_client on dbo.drt_contract ( i_client asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_c_con_status on dbo.drt_contract ( c_con_status asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_con_financial_owner on dbo.drt_contract ( i_con_financial_owner asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_con_address_owner on dbo.drt_contract ( i_con_address_owner asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_c_mkt_division on dbo.drt_contract ( c_mkt_division asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_con_alt_rep3 on dbo.drt_contract ( i_con_alt_rep3 asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_c_company on dbo.drt_contract ( c_company asc )
go|||You want crappy design?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26547
It looks like it should use the clustered index...but it still has to go to the datapage because of SELECT *
Which, in an application aint a good idea...do you need all of the columns?
What data do you actually need?|||Yep, you're right but I'm doing some quick testing so I didn't want to type all of the column names. The select * won't be used by any application or process. Anyways, I've specified the all of the column names and also only the 5-6 columns that I care to review and it still executes in the same amount of time. Oh yeah, Sybase nor Microsoft will provide support for OLEDB linked server connections to Sybase...SWEET|||I don't know if sybase has index intersection, but did you try and create a covered index with the 6 columns that you need
Sybase should at least use that index....
Or, can you create a sproc on sybase and call that?
My guess is that it's dragging way mopre data across than it needs to and sql server is figuring out the final results...
I'm not sure...
Gotta read up on how linked servers manage the data...|||The SQL statement only returns 1 row. So you think that a larger set of data is being sent to SQL Server and it has to narrow the set down to the final row? Hmm... remember I'm only encountering the problem when I use a string in the where clause. Integers work fine. I'll let you know how the covered index works.|||Here's the solution, use openquery() to execute the sql.
select * from openquery(linked server connection, sql stetament)|||Thanks for the info!
Any ideas?Did I mention I hate sybase...
Just had a project thrown at me...never touched it before...
I know there "cousins", but man is it quirky...
Saw a developer do
SELECT Col1, MAX(Col2), SUM(Col3)
FROM Table
WHERE Col1 = 'A'
AND IT RAN!
I couldn't believe it...and it gave back complete garbage...
Can you post your statement?
I'll try and link that server up and see what happens...
(No wonder they lost market share)|||Hi, I had a similar problem with ASE6, using a linked server in SQL server 2000.
The problem was I had a very poor performance (10 secs per query), and I had to make an update in 200,000 regs using that query for every record... also I had problems in my linked server too (it worked when it wanted, i never discovered the cause...)
My solution was very desesperate: instead of connecting SQL server to the ODBC linked to the ASE server, I made a program in Visual Basic with a connection to the ODBC. I wrote the update procedure with ADO.
The results: the whole update was finished in 15 secs (remember: in SQL server each query took 10 secs, multiplied by 200,000 records...).
I dont understand why this happens, a SQL server should be faster accessing data behind an ODBC than a VB program...|||Here's the SQL:
select *
from [32tlsql2-dreamdb].dreamdb.dbo.drt_contract
where i_con_contract = '000Q019999'
and c_company = 'P00'
and c_mkt_division = 'IPD'
Here's the table DDL (please don't make jokes about the crappy design of the table. Unfortunately I got stuck with it):
/****************** CREATE ORDER ******************/
create table dbo.drt_contract
(
i_client numeric(10,0) not null ,
i_con_contract char(10) not null ,
c_company char(3) not null ,
c_mkt_division char(3) not null ,
c_level numeric(2,0) Default 1 not null ,
c_interface_company char(6) null ,
c_form char(8) not null ,
c_con_contract_type char(2) not null ,
c_agent_type char(3) null ,
c_con_status char(2) null ,
d_final_disposition datetime null ,
d_lifecom_updated datetime null ,
c_lifecom_status char(1) null ,
d_con_terminate datetime null ,
d_con_da_report_terminate datetime null ,
d_con_signed datetime null ,
d_con_effective datetime null ,
c_con_line_of_business char(1) null ,
i_con_rsm char(10) null ,
c_con_publicity char(1) null ,
c_con_uw_eligiblity char(1) null ,
c_con_premium_remission char(1) null ,
q_con_years_experience char(1) null ,
c_con_pay char(2) null ,
c_con_report char(2) null ,
c_con_review char(1) null ,
c_con_additional_aa_trailer char(8) null ,
x_con_text varchar(250) null ,
c_con_terminate_reason char(2) null ,
f_con_assign_commissions char(1) null ,
n_con_assign_commissions varchar(50) null ,
i_con_financial_owner char(10) null ,
c_con_financial_owner_level char(1) null ,
i_con_address_owner char(10) null ,
c_con_address_owner_level char(1) null ,
c_con_mail char(1) null ,
c_con_hire_type char(2) null ,
c_con_change_type char(2) null ,
c_zipcode_processing char(1) null ,
n_con_sub_name varchar(30) null ,
d_con_nasd_hire datetime null ,
c_con_nasd_status char(2) null ,
i_con_alt_rep1 char(10) null ,
i_con_alt_rep2 char(10) null ,
i_con_alt_rep3 char(10) null ,
i_con_branch char(10) null ,
c_con_rep_class char(2) null ,
i_con_old_rep_nbr varchar(10) null ,
i_con_old_branch char(10) null ,
i_user_last_change char(8) null ,
h_last_change datetime null
)
Lock Datarows on "default"
go
/****************** ALTER ORDER CONSTRAINTS ******************/
alter table dbo.drt_contract
add constraint pk_contract primary key clustered ( i_con_contract asc ,c_company asc ,c_mkt_division asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_client on dbo.drt_contract ( i_client asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_c_con_status on dbo.drt_contract ( c_con_status asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_con_financial_owner on dbo.drt_contract ( i_con_financial_owner asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_con_address_owner on dbo.drt_contract ( i_con_address_owner asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_c_mkt_division on dbo.drt_contract ( c_mkt_division asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_i_con_alt_rep3 on dbo.drt_contract ( i_con_alt_rep3 asc )
go
/****************** CREATE INDEX ******************/
create Nonclustered index ix_c_company on dbo.drt_contract ( c_company asc )
go|||You want crappy design?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26547
It looks like it should use the clustered index...but it still has to go to the datapage because of SELECT *
Which, in an application aint a good idea...do you need all of the columns?
What data do you actually need?|||Yep, you're right but I'm doing some quick testing so I didn't want to type all of the column names. The select * won't be used by any application or process. Anyways, I've specified the all of the column names and also only the 5-6 columns that I care to review and it still executes in the same amount of time. Oh yeah, Sybase nor Microsoft will provide support for OLEDB linked server connections to Sybase...SWEET|||I don't know if sybase has index intersection, but did you try and create a covered index with the 6 columns that you need
Sybase should at least use that index....
Or, can you create a sproc on sybase and call that?
My guess is that it's dragging way mopre data across than it needs to and sql server is figuring out the final results...
I'm not sure...
Gotta read up on how linked servers manage the data...|||The SQL statement only returns 1 row. So you think that a larger set of data is being sent to SQL Server and it has to narrow the set down to the final row? Hmm... remember I'm only encountering the problem when I use a string in the where clause. Integers work fine. I'll let you know how the covered index works.|||Here's the solution, use openquery() to execute the sql.
select * from openquery(linked server connection, sql stetament)|||Thanks for the info!
Friday, February 24, 2012
Linked Server - How to write a Query with condtion (string)
Hi all,
I have created a linked server from SQL 2k to Informix which is fine.
I wrote below query which also fine and got results:
select * from OPENQUERY
(myLinkServer, 'select a.emp_number,a.emp_name,a.emp_loc,a.emp_stat,
b.emp_grade,b.emp_title from employee a,title b where a.emp_number =
b.emp_number')
But, I need to add folliwng condition with above query that I tried. But
no luck getting error.
How can I incorporate belwo condition with above linked server query:
and a.join_date =' 04/09/2005' and a.emp_loc = 'LON'
Kindly note, "join_date" value and "emp_loc" values can be passed via a
variables.
Appreciate all your help.
Regards
*** Sent via Developersdex http://www.examnotes.net ***Hi
Try:
SELECT emp_number, emp_name, emp_loc, emp_stat, emp_grade, emp_title
FROM OPENQUERY
(myLinkServer, 'select a.emp_number,a.emp_name,a.emp_loc,a.emp_stat,
b.emp_grade,b.emp_title,a.join_date from employee a,title b where
a.emp_number =
b.emp_number') Q
WHERE join_date =' 04/09/2005'
AND emp_loc = 'LON'
John
"Ahmed Jewahar" <ahmed.jewahar@.dhl.com> wrote in message
news:%23y047zZPFHA.4052@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have created a linked server from SQL 2k to Informix which is fine.
> I wrote below query which also fine and got results:
> select * from OPENQUERY
> (myLinkServer, 'select a.emp_number,a.emp_name,a.emp_loc,a.emp_stat,
> b.emp_grade,b.emp_title from employee a,title b where a.emp_number =
> b.emp_number')
>
> But, I need to add folliwng condition with above query that I tried. But
> no luck getting error.
> How can I incorporate belwo condition with above linked server query:
>
> and a.join_date =' 04/09/2005' and a.emp_loc = 'LON'
>
> Kindly note, "join_date" value and "emp_loc" values can be passed via a
> variables.
> Appreciate all your help.
> Regards
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Ahmed
I suggest you create a stored procedure that accepts two parameters
DECLARE @.SQLx VARCHAR(500)
DECLARE @.var VARCHAR(20)
SET @.var = 'abcd'
SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
''EXEC pubs.dbo.sp2 '' + @.var + '')'
EXEC(@.SQLx)
"Ahmed Jewahar" <ahmed.jewahar@.dhl.com> wrote in message
news:%23y047zZPFHA.4052@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have created a linked server from SQL 2k to Informix which is fine.
> I wrote below query which also fine and got results:
> select * from OPENQUERY
> (myLinkServer, 'select a.emp_number,a.emp_name,a.emp_loc,a.emp_stat,
> b.emp_grade,b.emp_title from employee a,title b where a.emp_number =
> b.emp_number')
>
> But, I need to add folliwng condition with above query that I tried. But
> no luck getting error.
> How can I incorporate belwo condition with above linked server query:
>
> and a.join_date =' 04/09/2005' and a.emp_loc = 'LON'
>
> Kindly note, "join_date" value and "emp_loc" values can be passed via a
> variables.
> Appreciate all your help.
> Regards
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Hi Uri,
Thanks for you kind help..
Regards,
*** Sent via Developersdex http://www.examnotes.net ***|||Hi John,
Thanks for your great help. I tried the same and it has been resolved my
problem,
Regards
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
The only issue may be that the openquery may still return an excessivly
large number of rows before being reduced by the subsequent where clause.
John
"Ahmed Jewahar" <ahmed.jewahar@.dhl.com> wrote in message
news:umzwGEcPFHA.2760@.TK2MSFTNGP10.phx.gbl...
> Hi John,
> Thanks for your great help. I tried the same and it has been resolved my
> problem,
> Regards
>
> *** Sent via Developersdex http://www.examnotes.net ***
I have created a linked server from SQL 2k to Informix which is fine.
I wrote below query which also fine and got results:
select * from OPENQUERY
(myLinkServer, 'select a.emp_number,a.emp_name,a.emp_loc,a.emp_stat,
b.emp_grade,b.emp_title from employee a,title b where a.emp_number =
b.emp_number')
But, I need to add folliwng condition with above query that I tried. But
no luck getting error.
How can I incorporate belwo condition with above linked server query:
and a.join_date =' 04/09/2005' and a.emp_loc = 'LON'
Kindly note, "join_date" value and "emp_loc" values can be passed via a
variables.
Appreciate all your help.
Regards
*** Sent via Developersdex http://www.examnotes.net ***Hi
Try:
SELECT emp_number, emp_name, emp_loc, emp_stat, emp_grade, emp_title
FROM OPENQUERY
(myLinkServer, 'select a.emp_number,a.emp_name,a.emp_loc,a.emp_stat,
b.emp_grade,b.emp_title,a.join_date from employee a,title b where
a.emp_number =
b.emp_number') Q
WHERE join_date =' 04/09/2005'
AND emp_loc = 'LON'
John
"Ahmed Jewahar" <ahmed.jewahar@.dhl.com> wrote in message
news:%23y047zZPFHA.4052@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have created a linked server from SQL 2k to Informix which is fine.
> I wrote below query which also fine and got results:
> select * from OPENQUERY
> (myLinkServer, 'select a.emp_number,a.emp_name,a.emp_loc,a.emp_stat,
> b.emp_grade,b.emp_title from employee a,title b where a.emp_number =
> b.emp_number')
>
> But, I need to add folliwng condition with above query that I tried. But
> no luck getting error.
> How can I incorporate belwo condition with above linked server query:
>
> and a.join_date =' 04/09/2005' and a.emp_loc = 'LON'
>
> Kindly note, "join_date" value and "emp_loc" values can be passed via a
> variables.
> Appreciate all your help.
> Regards
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Ahmed
I suggest you create a stored procedure that accepts two parameters
DECLARE @.SQLx VARCHAR(500)
DECLARE @.var VARCHAR(20)
SET @.var = 'abcd'
SET @.SQLx = 'SELECT * FROM OPENQUERY(Server,
''EXEC pubs.dbo.sp2 '' + @.var + '')'
EXEC(@.SQLx)
"Ahmed Jewahar" <ahmed.jewahar@.dhl.com> wrote in message
news:%23y047zZPFHA.4052@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have created a linked server from SQL 2k to Informix which is fine.
> I wrote below query which also fine and got results:
> select * from OPENQUERY
> (myLinkServer, 'select a.emp_number,a.emp_name,a.emp_loc,a.emp_stat,
> b.emp_grade,b.emp_title from employee a,title b where a.emp_number =
> b.emp_number')
>
> But, I need to add folliwng condition with above query that I tried. But
> no luck getting error.
> How can I incorporate belwo condition with above linked server query:
>
> and a.join_date =' 04/09/2005' and a.emp_loc = 'LON'
>
> Kindly note, "join_date" value and "emp_loc" values can be passed via a
> variables.
> Appreciate all your help.
> Regards
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Hi Uri,
Thanks for you kind help..
Regards,
*** Sent via Developersdex http://www.examnotes.net ***|||Hi John,
Thanks for your great help. I tried the same and it has been resolved my
problem,
Regards
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
The only issue may be that the openquery may still return an excessivly
large number of rows before being reduced by the subsequent where clause.
John
"Ahmed Jewahar" <ahmed.jewahar@.dhl.com> wrote in message
news:umzwGEcPFHA.2760@.TK2MSFTNGP10.phx.gbl...
> Hi John,
> Thanks for your great help. I tried the same and it has been resolved my
> problem,
> Regards
>
> *** Sent via Developersdex http://www.examnotes.net ***
Subscribe to:
Posts (Atom)