Hi,

I've successfully created a Linked server within SQL Server 2005 Management 
Studio to a DB2 database (DB2 is hosted on Windows Server 2003). If I right 
click the linked server and select 'Test Connection' and get a message 'The 
test connection to linked server succeeded'.

However, when I try to expand Catalog node I receive the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: 
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. 
(Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "SQL Server" for linked server "(null)" reported an 
error. One or more arguments were reported invalid by the provider.
Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL 
Server" for linked server "(null)". The provider supports the interface, but 
returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)

For help, click: 
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3050&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


At the time of posting this question, if I follow the link in the error 
message, there was no information on the error.

Any help much appreciated.

Thanks in advance,
Samad Khan

Samad Khan,

Please right click on the Linked Server definition and Script Linked Server 
As... \Create.   Paste the create script into a response so that the group 
can see the details of the linked server definition.

Thanks,
RLF

"samadkhan"  wrote in message 
news:C9678FEC-8150-4CC9-AD05-57BDE746D778@microsoft.com...
> Hi,
>
> I've successfully created a Linked server within SQL Server 2005 
> Management
> Studio to a DB2 database (DB2 is hosted on Windows Server 2003). If I 
> right
> click the linked server and select 'Test Connection' and get a message 
> 'The
> test connection to linked server succeeded'.
>
> However, when I try to expand Catalog node I receive the following error:
>
> TITLE: Microsoft SQL Server Management Studio
> ------------------------------
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
>
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
>
> ------------------------------
> ADDITIONAL INFORMATION:
>
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
>
> ------------------------------
>
> The OLE DB provider "SQL Server" for linked server "(null)" reported an
> error. One or more arguments were reported invalid by the provider.
> Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider 
> "SQL
> Server" for linked server "(null)". The provider supports the interface, 
> but
> returns a failure code when it is used. (Microsoft SQL Server, Error: 
> 7399)
>
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3050&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476
>
> ------------------------------
> BUTTONS:
>
> OK
> ------------------------------
>
>
> At the time of posting this question, if I follow the link in the error
> message, there was no information on the error.
>
> Any help much appreciated.
>
> Thanks in advance,
> Samad Khan
>

Linked Server Text
/****** Object:  LinkedServer [CMH]    Script Date: 07/08/2008 10:29:12 
******/
EXEC master.dbo.sp_addlinkedserver @server = N'CMH', @srvproduct=N'CMH', 
@provider=N'MSDASQL', @datasrc=N'DsnCMH'
 /* For security reasons the linked server remote logins password is changed 
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'CMH',@useself=N'False',@locallogin=NULL,@rmtuser=N'LINKITG',@rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'collation 
compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'data access', 
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'dist', 
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'pub', 
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'rpc', 
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'rpc out', 
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'sub', 
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'connect timeout', 
@optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'collation name', 
@optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'lazy schema 
validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'query timeout', 
@optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CMH', @optname=N'use remote 
collation', @optvalue=N'true'
-- 
Trying to get this done with the least amount of effort


"Russell Fields" wrote:

> Samad Khan,
> 
> Please right click on the Linked Server definition and Script Linked Server 
> As... \Create.   Paste the create script into a response so that the group 
> can see the details of the linked server definition.
> 
> Thanks,
> RLF
> 
> "samadkhan"  wrote in message 
> news:C9678FEC-8150-4CC9-AD05-57BDE746D778@microsoft.com...
> > Hi,
> >
> > I've successfully created a Linked server within SQL Server 2005 
> > Management
> > Studio to a DB2 database (DB2 is hosted on Windows Server 2003). If I 
> > right
> > click the linked server and select 'Test Connection' and get a message 
> > 'The
> > test connection to linked server succeeded'.
> >
> > However, when I try to expand Catalog node I receive the following error:
> >
> > TITLE: Microsoft SQL Server Management Studio
> > ------------------------------
> >
> > Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
> >
> > ------------------------------
> > ADDITIONAL INFORMATION:
> >
> > An exception occurred while executing a Transact-SQL statement or batch.
> > (Microsoft.SqlServer.ConnectionInfo)
> >
> > ------------------------------
> >
> > The OLE DB provider "SQL Server" for linked server "(null)" reported an
> > error. One or more arguments were reported invalid by the provider.
> > Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider 
> > "SQL
> > Server" for linked server "(null)". The provider supports the interface, 
> > but
> > returns a failure code when it is used. (Microsoft SQL Server, Error: 
> > 7399)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3050&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476
> >
> > ------------------------------
> > BUTTONS:
> >
> > OK
> > ------------------------------
> >
> >
> > At the time of posting this question, if I follow the link in the error
> > message, there was no information on the error.
> >
> > Any help much appreciated.
> >
> > Thanks in advance,
> > Samad Khan
> > 
> 
> 
>

Hi Russell, 

Thank you for responsing. I've extracted the script as follows:

/****** Object:  LinkedServer [DB2LINKSERVER]    Script Date: 07/28/2008 
11:13:59 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DB2LINKSERVER', 
@srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB', 
@datasrc=N'DB2_DATABASE_SERVER', @provstr=N'Provider=DB2OLEDB;User 
ID=db2admin;Password=db2admin;Initial Catalog=DB2DATABASENAME;Network 
Transport Library=TCP;Host CCSID=1252;PC Code Page=1252;Network 
Address=DB2_DATABASE_SERVER;Network Port=50000;Package 
Collection=DB2_STAGING;Default Schema=DB2_STAGING;Process Binary as 
Character=False;Units of Work=RUW;DBMS Platform=DB2/NT;Defer 
Prepare=False;Rowset Cache Size=0;Persist Security Info=True;Connection 
Pooling=False;Derive Parameters=False;', @catalog=N'DB2DATABASENAME'
 /* For security reasons the linked server remote logins password is changed 
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'DB2LINKSERVER',@useself=N'False',@locallogin=NULL,@rmtuser=N'db2admin',@rmtpassword='db2admin'

GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', 
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'data 
access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'dist', 
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'pub', 
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'rpc', 
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'rpc 
out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'sub', 
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'connect 
timeout', @optvalue=N'30'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', 
@optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'lazy 
schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'query 
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DB2LINKSERVER', @optname=N'use 
remote collation', @optvalue=N'true'

Any help is appricated.

Thanks,
Samad 

"Russell Fields" wrote:

> Samad Khan,
> 
> Please right click on the Linked Server definition and Script Linked Server 
> As... \Create.   Paste the create script into a response so that the group 
> can see the details of the linked server definition.
> 
> Thanks,
> RLF
> 
> "samadkhan"  wrote in message 
> news:C9678FEC-8150-4CC9-AD05-57BDE746D778@microsoft.com...
> > Hi,
> >
> > I've successfully created a Linked server within SQL Server 2005 
> > Management
> > Studio to a DB2 database (DB2 is hosted on Windows Server 2003). If I 
> > right
> > click the linked server and select 'Test Connection' and get a message 
> > 'The
> > test connection to linked server succeeded'.
> >
> > However, when I try to expand Catalog node I receive the following error:
> >
> > TITLE: Microsoft SQL Server Management Studio
> > ------------------------------
> >
> > Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
> >
> > ------------------------------
> > ADDITIONAL INFORMATION:
> >
> > An exception occurred while executing a Transact-SQL statement or batch.
> > (Microsoft.SqlServer.ConnectionInfo)
> >
> > ------------------------------
> >
> > The OLE DB provider "SQL Server" for linked server "(null)" reported an
> > error. One or more arguments were reported invalid by the provider.
> > Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider 
> > "SQL
> > Server" for linked server "(null)". The provider supports the interface, 
> > but
> > returns a failure code when it is used. (Microsoft SQL Server, Error: 
> > 7399)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3050&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476
> >
> > ------------------------------
> > BUTTONS:
> >
> > OK
> > ------------------------------
> >
> >
> > At the time of posting this question, if I follow the link in the error
> > message, there was no information on the error.
> >
> > Any help much appreciated.
> >
> > Thanks in advance,
> > Samad Khan
> > 
> 
> 
>

Samad,

Your script looks OK to me, to the extent that I can tell.  Unfortunately, I 
do not have DB2 to test against, so I can only go so far with the testing 
However, doing a little more research I came across this KB

http://support.microsoft.com/kb/222937/EN-US/  which has the following:
1. Comments on running the DB2OLEDB provider in-process.
2. The total length of the linked server initstring must be no more than 278 
characters, so it is advantageous to use the DB2OLEDB short connection 
string arguments as documented above.

Since your connection string is over 400 characters long, perhaps that is 
causing you some problem.  Also check for the in-process setting.

Some other links of interest include:
http://support.microsoft.com/kb/218590/EN-US/
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=582877&SiteID=17

RLF

Thanks Russell,

I'm trying to shortten the connection string and will let you know how I get 
on.

Enjoy,
Samad

Hi Russell,

I didn't have much joy using DB2 driver. The connection was still more than 
278 characters in length due to the configuration I had to supply. I did have 
more luck using IBMDADB2 driver. The script below will create the link server:

/****** Object:  LinkedServer [LOANIQ]    Script Date: 08/15/2008 11:28:41 
******/
IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 
AND srv.name = N'LINK_SERVER_NAME')EXEC master.dbo.sp_dropserver 
@server=N'LINK_SERVER_NAME', @droplogins='droplogins'

/****** Object:  LinkedServer [LOANIQ]    Script Date: 08/15/2008 11:28:36 
******/
EXEC master.dbo.sp_addlinkedserver @server = N'LINK_SERVER_NAME', 
@srvproduct=N'IBMDADB2', @provider=N'IBMDADB2', @datasrc=N'LINK_SERVER_NAME', 
@location=N'DB2_SERVER', @provstr=N'Data Source=DATABASE_NAME;User 
ID=DB_USERNAME;Password=DB_PASSWORD;Provider=IBMDADB2.1;Persist Security 
Info=True;Location=DB2_SERVER;'
 /* For security reasons the linked server remote logins password is changed 
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'LINK_SERVER_NAME',@useself=N'False',@locallogin=NULL,@rmtuser=N'DB_USERNAME',@rmtpassword='DB_PASSWORD'

GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'data 
access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'rpc 
out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'lazy 
schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'use 
remote collation', @optvalue=N'true'

However I couldn't view stored procedures, table data, column information. 
And within query analyser I can't return data.

I get a the error:
The OLE DB provider IBMDADB2 for linked server supplied inconsistent 
metadata for a column. The column "COLUMN_NAME" (compile-time ordinal 1) of 
object ""SCHEMA_NAME"."TABLE_NAME"" was reported to have a "DBTYPE" of 129 at 
compile time and 130 at run time.

I think it may be a collation issue but will let you know when it's sorted, 
or you have any ideas?

Thanks again,
Samad

samadkhan,

Yes, this is a problem of different storage types across different database 
software.  Here is an IBM link:
http://www-1.ibm.com/support/docview.wss?uid=swg1JR19327

Also, a similar problem linking to Oracle:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21278440.html

In both, the answer was to use OPENQUERY.

RLF

"samadkhan"  wrote in message 
news:B316E67B-76F0-479D-B7B0-0488349143C5@microsoft.com...
> Hi Russell,
>
> I didn't have much joy using DB2 driver. The connection was still more 
> than
> 278 characters in length due to the configuration I had to supply. I did 
> have
> more luck using IBMDADB2 driver. The script below will create the link 
> server:
>
> /****** Object:  LinkedServer [LOANIQ]    Script Date: 08/15/2008 11:28:41
> ******/
> IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0
> AND srv.name = N'LINK_SERVER_NAME')EXEC master.dbo.sp_dropserver
> @server=N'LINK_SERVER_NAME', @droplogins='droplogins'
>
> /****** Object:  LinkedServer [LOANIQ]    Script Date: 08/15/2008 11:28:36
> ******/
> EXEC master.dbo.sp_addlinkedserver @server = N'LINK_SERVER_NAME',
> @srvproduct=N'IBMDADB2', @provider=N'IBMDADB2', 
> @datasrc=N'LINK_SERVER_NAME',
> @location=N'DB2_SERVER', @provstr=N'Data Source=DATABASE_NAME;User
> ID=DB_USERNAME;Password=DB_PASSWORD;Provider=IBMDADB2.1;Persist Security
> Info=True;Location=DB2_SERVER;'
> /* For security reasons the linked server remote logins password is 
> changed
> with ######## */
> EXEC master.dbo.sp_addlinkedsrvlogin
> @rmtsrvname=N'LINK_SERVER_NAME',@useself=N'False',@locallogin=NULL,@rmtuser=N'DB_USERNAME',@rmtpassword='DB_PASSWORD'
>
> GO
> EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
> @optname=N'collation compatible', @optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
> @optname=N'data
> access', @optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
> @optname=N'dist', @optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
> @optname=N'pub', @optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
> @optname=N'rpc', @optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
> @optname=N'rpc
> out', @optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
> @optname=N'sub', @optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
> @optname=N'connect timeout', @optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
> @optname=N'collation name', @optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
> @optname=N'lazy
> schema validation', @optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME',
> @optname=N'query timeout', @optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
> @optname=N'use
> remote collation', @optvalue=N'true'
>
> However I couldn't view stored procedures, table data, column information.
> And within query analyser I can't return data.
>
> I get a the error:
> The OLE DB provider IBMDADB2 for linked server supplied inconsistent
> metadata for a column. The column "COLUMN_NAME" (compile-time ordinal 1) 
> of
> object ""SCHEMA_NAME"."TABLE_NAME"" was reported to have a "DBTYPE" of 129 
> at
> compile time and 130 at run time.
>
> I think it may be a collation issue but will let you know when it's 
> sorted,
> or you have any ideas?
>
> Thanks again,
> Samad

Great minds think alike :)

I just found the article myself half an hour ago and tested it. It worked!!!

I had to use Openquery method to execute any sql, but it works.

eg:
select * from OPENQUERY(SAMPLE, 'select * from EMPLOYEE')
instead of:
select * from SAMPLE..SCHEMA_NAME.EMPLOYEE

I've scripted the Linked server if anyone requires it - and save them the 
blood and sweat I weant through :)

/****** Object:  LinkedServer [LOANIQ]    Script Date: 08/15/2008 16:45:52 
******/
IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 
AND srv.name = N'LINK_SERVER_NAME')EXEC master.dbo.sp_dropserver 
@server=N'LINK_SERVER_NAME', @droplogins='droplogins'

/****** Object:  LinkedServer [LOANIQ]    Script Date: 08/15/2008 16:46:01 
******/
EXEC master.dbo.sp_addlinkedserver @server = N'LINK_SERVER_NAME', 
@srvproduct=N'IBMDADB2.1', @provider=N'IBMDADB2', 
@datasrc=N'LINK_SERVER_NAME', @location=N'DB2_SERVER', @provstr=N'Data 
Source=DB2_DATABASENAME;User 
ID=DB_USER;Password=DB_PASSWORD;Provider=IBMDADB2.1;Persist Security 
Info=True;Location=DB2_SERVER;'
 /* For security reasons the linked server remote logins password is changed 
with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'LINK_SERVER_NAME',@useself=N'False',@locallogin=NULL,@rmtuser=N'DB_USER',@rmtpassword='DB_PASSWORD'

GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'data 
access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'rpc 
out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'collation name', @optvalue=N'Latin1_General_CI_AS'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'lazy 
schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', 
@optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINK_SERVER_NAME', @optname=N'use 
remote collation', @optvalue=N'true'
GO


Thanks for all your help Russell and the quick response.

Enjoy,
Samad