.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
satyapriyanayak
SP
abhays
Sasi Prabhu
Post New Web Links

Instructions for setting up a Sybase ASE Server as a SQL Server 2005 Linked Server?

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server
 

I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005.  The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67.  I have already installed the Sybase client software on the server.

I also created a SystemDSN on the SQL Server to connect to the Sybase server.  I tested the connection and it was able to connect.

I ran the following code to create the linked server:

<code>

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'Sybase System DSN'


View Complete Post


More Related Resource Links

Linked Server to DB2 from SQL 2005 IA64BIT computer

  
Has anyone created a linked server to DB2 on AS/400 from SQL Server 2005 IA64 BIT? We were hoping to use the new Microsoft OLEDB Provider for DB2...The problem is the driver looks like it will only work on x64 computers."The single setup program will install the provider and tools on both x86 and x64 computers" We were able to install the driver on the IA64 computer and were able to successfully create a data source from the Data Access Tool. Oddly enough when we attempted to create a linked server in SSMS we received an error, we also noticed that the DB2OLEDB provider is not listed in SSMS. This is becoming a real problem so any ideas on how we can get this driver to work or suggestions on an alternative would be greatly appreciated.   thx.art.  

Importing Data from Oracle 10g to Sql Server 2005 using Linked Server

  
Hi,   I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :   OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error". OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.   Please let me know.   Thanks

SQL Server 2005 X64 Linked Server error

  
We use Windows 2003 Server (64) on AMD64 and SQL Server 2005 Developer Edition x64 + SP1  Problem: I can not execute any sql on a linked server using the native Provider (SQLCLNI) Example (create a linked server on the same machine, other database (msdb) and try to execute any simple select using OPENQUERY  sp_addlinkedserver @server='ls',@srvproduct='SQLNCLI',@provider='SQLNCLI' ,@provstr='Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=localhost;'  Select * from OPENQUERY(ls,'Select * from Sysobjects')  Msg 7356, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI" for linked server "ls" supplied inconsistent metadata for a column. The column "name" (compile-time ordinal 1) of object "Select * from Sysobjects" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.

Issue with Connection of Sybase in SSIS and Linked Server in SQL Server

  
Iam having problem connecting with Sybase from SSIS or Link Server of SQL Server 2008.

 I have installed Sybase 12.5.1 OLE DB Driver and have also registered by running command:

 C:\sybase\DataAccess\OLEDB\dll>regsvr32 sybdrvoledb.dll

 We are also able to create System DSN from ODBC Administrator in Windows 7. I am also able to import data in the MS Access. Here is the System DSN I created, successfully.

 I would really appreciate you can help us out.


Linked server from SQL Server 2008 R2 to SQL Server 2005 not working

  

I'm trying to create a linked server from SQL Server r2 to SQL Server 2005 (both 64 bit Enterprise) using the following script

 EXEC master.dbo.sp_addlinkedserver
 @server = N'TestLS'
 ,@srvproduct=''
 ,@provider='SQLOLEDB.1'
 ,@datasrc=N'dw_prod'
 ,@provstr='Integrated Security=SSPI;';
 
 GO

SELECT
    *
 FROM
    [TestLS].[I_OLAP].[dbo].[FACT_AUDIT]
I verified that the Kerberos is working.

When I try to run a four part query as below it gives me the error

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
OLE DB provider "SQLNCLI10" for linked server "TestLS" returned message "Invalid connection string attribute".


If I try to set up the linked server against localhost (i.e. SQL 2008 R2, it works just fine).


Thanks,
GB


GBM

SQL Server 2005 rebuild indexes find corruption or cross linked files

  

Does anyone know if this is a known issue? What do you do when this happens? We had to go to a good backup from 3 days prior.

From the application log it appears there were cross linked files. Is this caused by the OS or Sql Server?

Pat M

Reno


PatM Reno

linked server on SQL 2005 to database on AS400

  

We have a linked server on SQL 2005 to database on AS400. the linked server is made using Microsoft DB2OLEDB provider.
when we are using the linked server, we are getting the following error:
OLE DB provider "DB2OLEDB" for linked server "AS400PROD" returned message "".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "AS400PROD".
 
When we restart  the SQL server machine, the linked server works fine for about 5 minutes then we receive the same error as described before,
 
The script of the linked server is :

EXEC master.dbo.sp_addlinkedserver @server = N'AS400PROD', @srvproduct=N'AS400', @provider=N'DB2OLEDB', @datasrc=N'S6595D7F', @provstr=N'Provider=DB2OLEDB;Password=password;Persist Security Info=True;User;Initial Catalog=S6595D7FSD;Data Source=AS400;Network Address=192.1.1.50;Package Collection=KFILFNBSA;DBMS Platform=DB2/AS400;Process Binary as Character=True'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AS400PROD',@useself=N'False',@locallogin=NULL,@rmtuser=N'LOGINRPORT',@rmtpassword='########'

How to migrate linked server configuration SQL 2000 and 2005?

  

I plan to migrate a SQL 2000 instance with everything in it to another SQL2000 server, and eventually in the future to SQL 2005.

I've pretty much figured out how to migrate everything execept the linked server configuration (there're many linked servers configured in the old server with different security credentials).  I'm wondering if there's way I can save the linked server configuration and restore it to the other server.

Thanks much.

Ed


Linked server issue on test box, post upgrade 2005 to 2008 R2

  

In an effort to do some testing prior to upgrading our environment to 2008 R2, I made a test instance on our Dev box.  2005 instance, copied as many things as I could think of from various other instances.  Made a basic linked server to our main cluster, had a repeating job to email me results of a query across that link every few hours.

 

Everything was working fine until the upgrade finished.  It completed at ~7pm.  The email at 6PM came through fine (while the upgrade was in progress), the email at 8pm didn't come through.  Checked various things (DB mail was still working, tested fine).  It couldn't access the data across the linked server.  I tried deleting that link and remaking it.  Errors out.  Tried running the same scripts we use to create our standard linked servers, error out.  The only ones that I can set up and function are links to other instances on the same box.

I've looked around at other fixes for this error message and none seem to make any difference.  Log in with Domain cred's, log in with the sa account, no difference.  I can connect from other instances & servers back to this one, just not outbound from this one.  And it worked prior to the upgrade I applied to it.  All other instances on the box are 2005 as well.

Here is the message I ge

Issue with linked server 2008 (and R2) not an issue in 2005

  

Hi,

I have been using SQL 2005 successfully to connect to tables in a 10g Oracle data warehouse. I have been using the 11g client.

I have tried several times to get this working in SQL Server 2008 (and R2) and have tried the 10g and 11g clients...

I do a query like Select top 100 * from DWD..DW.IC_TRAN_PND and I get the error: 

"Invalid data for type "numeric".

 

I have read about the reasons for this but is there a way to make it work like it does in SQL server 2005?


Cannot update Excel 2007 spreadsheet as linked server within SQL 2005 or SQL 2008 via ADO

  
Greetings!

I am having difficulty updating an Excel worksheet via the ACE.OLEDB.12.0
provider.

I have a worksheet defined as a linked server in SQL Server via this
provider, and all attempts to update the lone worksheet in this file as a
linked server results in the following:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "linked_excel"
returned message "Bookmark is invalid.".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider
"Microsoft.ACE.OLEDB.12.0" for linked server "linked_excel".

The query:
update linked_excel...sheet1$ set error_col='hithere' where
)='G'

However, when I try to perform precisely the same update against the same
source via openrowset, it works, to-wit:

update openrowset('Microsoft.ACE.OLEDB.12.0','Excel
12.0;HDR=yes;Database=f:\path_to_file\filename.xlsx','select * from
[sheet1$]')
set error_col='hithere'
where
='G'

SELECT's performed against either version work properly.

The linked server behavior is consistent across SQL 2005 and 2008
installations.

I am concerned that this problem is an artifact of an OLEDB provider update that purposely disabled update b

SQL 2005 Linked Server to Oracle 10g - Very Slow

  

Hi,

 

Anyone have some recommendations for a slow linked server from SQL 2005 to Oracle 10g?

 

Here is the situation.  We have an application that runs on SQL 2005 - Win 2003, and in certain forms it makes calls to an Oracle 10g database running on Sun Unix.  This database was upgraded from SQL 2000 Win 2000.  We have a test system mirroring production and have the same problems.

 

- All indexes are being used.

- Server configuration is correct.

- Changed the Oracle 10g driver from the main driver to the Instant Client - much smaller but same result.

- Read an old post about same issue but the person was connecting to 8i so they were using a different driver.

- I have been running profiler and the problem seems to be a remote scan on the Oracle side.  We are using the OPENQUERY call from the application.

- All other parts of the app are running fine, just the outside connection.

- We are using the Windows Firewall and all DCOM components are set as Microsoft recommends.  I don't think this is an issue because the test server is not using Windows Firewall and we have the same issue on that server.

 

Any suggestions would be greatly appreciated.  I have run into the wall :-)

 

thanks

Matt


SQL Server 2005 Try and Catch Exception Handling

  
I'm pretty excited to see that there is some real error handling for T-SQL code in SQL Server 2005. It's pretty painful to have your wonderfully architected .NET solution tainted by less-than-VBScript error handling for stored procedures in the database. The big difference being the addition of TRY..CATCH blocks. Let's take a look:

sql server 2000 vs 2005

  

i would like to ask what the difference between sql server 2000 and 2005 


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend