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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

How to create linked Server to connect from SQL Server 2008 to SQL Server 2000

Posted By:      Posted Date: October 05, 2010    Points: 0   Category :Sql Server
 

How to create linked Server to connect from SQL Server 2008 to SQL Server 2000.

I following the steps to create the linked server in the GUI but failed with the following error message.

I used the full computer network name as the 'Linked Server' name

 OLE DB provider "SQLNCLI10" for linked server "my_server_network_name" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "my_server_network_name" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "my_server_network_name". The provider supports the interface, but returns a failure code when it is used.

Thanks a lot for your help.

Tom




View Complete Post


More Related Resource Links

Unable to connect Excel 2003 from linked server in sql 2008 64 bit

  
Hi ALL,

I was unable to connect to excel 2003 with linked server created in sql server 2008 64 bit. This was working fine with sql server 2005 32 bit.

I know that there no 64 bit drivers for JET drivers, but i am looking for some alternative ways to do the same job.

I dont want to migrate to Excel 2007.

Thanks in advance for all your help.

Thanks,
Loknath

Metadata: Create a Database Schema Repository with Meta Data Services in SQL Server 2000

  

SQL Server 2000 Meta Data Services is a repository technology that stores and manages metadata for SQL Server. Instead of building database schemas over and over, Meta Data Services allows you to freeze an entire schema for use in other projects. You can also use these schemas for training, testing, or debugging. In this article, the authors will review the various components of Meta Data Services and show how it can be programmed using a Visual Basic client, XML, and XSLT. They will also show you how to manage and manipulate your metadata by generating a simple database schema using a SQL Server repository.

Alok Mehta and Ricardo Rodriguez

MSDN Magazine May 2003


How to connect to local instance of SQL Server 2008 Management Studio Express ?

  

I just installed SQL Server 2008 Management studio Express on my Vista machine.

i tried connecting to local machine using :

.\sqlexpress, (local)\sqlexpress, or .\SQLExpress, MyPCNAME\SQLExpress

nothing is working, getting this error:

-----------------------------
Cannot connect to PC-NAME\SQLEXPRESS. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
----------------------------


Can't Create ODBC to Local SQL Server Express 2008

  
Hello. I have SQL Server Express 2008 SP1 LOCALLY on my Windows XP Professional SP3 box. I have databases already created in Access 2007 that I imported into SQL. Now I want to keep the front end Access forms for data entry but have them connect to the databases in SQL. What I can't seem to do is connect from Access to the SQL db's because I can't create the ODBC connection. When I try to connect, I get the following two 'Connection failed' errors: 1. SQLState: '08001' SQL Server Error: 10061 [Microsoft][SQL Server Native Client 10.0]TCP Provider: No connection could be made because the target machine actively refused it. 2. SQLState: 'HYT00' SQL Server Error: 0 [Microsoft][SQL Server Native Client 10.0]Login timeout expired Additionally, this information may help. 1. Windows firewall is turned off. 2. In SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for SQLEXPRESS, Shared Memory, Named Pipes and TCP/IP are all 'Enabled'. The order is Shared Memory (1), Named Pipes (2) and TCP/IP (3). 3. In SQL Server Configuration Manager > SQL Native Client 10.0 Configuration > Client Protocols, the above three are also 'Enabled'. The order is Shared Memory (1), Named Pipes (2) and TCP/IP (3). Double-clicking TCP/IP tells me that the default port is 1433. 4. Running "netstat -ano", I see no entries for [myIPaddress]:1433.

SQL Server 2008 Powershel trying to access SQL Server 2000

  
Below is a script from my SQL server 2008 PowerShell trying to connect a SQL Server 2000 instance. PS SQLSERVER:\SQL\SS2K8\DEFAULT\databases> cd \ PS SQLSERVER:\> cd sql PS SQLSERVER:\sql> cd MySS2KMachine PS SQLSERVER:\sql\MySS2KMachine> cd default PS SQLSERVER:\sql\MySS2KMachine\default> cd Databases PS SQLSERVER:\sql\MySS2KMachine\default\Databases> dir and I see a list of all databases on the default (SS2K) instance. I also see al instances on the machine: PS SQLSERVER:\sql\MySS2KMachine\default> cd .. PS SQLSERVER:\sql\MySS2KMachine> dir Instance Name ------------- DEFAULT MSSQL2005 As you can see from the above, there is also a SQL Server 2005 instance installed on the MySS2KMachine. Now, if I try the same thing for a Sql Server 2000 instance on another server (Windows 2003 R2 with SP1) with no SQL Server 2005 or higher installed on the machine, I get the error below: PS SQLSERVER:\> cd sql PS SQLSERVER:\sql> cd MyPureSS2KMachine WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'MyPureSS2KMachine' failed with the following error: SQL Server WMI provider is not available on MyPureSS2KMachine. --> Invalid namespace Set-Location : Cannot find path 'SQLSERVER:\sql\MyPureSS2KMachine' because it does not exist. At line:1 char:3 + cd <<<< MyPureSS2KMachine PS SQLSERVER:\sql> So it see

Why upgrade is required from SQL Server 2000 to SQl Server 2008

  
Hi all Can some body share with information about "Why upgrade is required from SQL Server 2000 to SQl Server 2008?"   This question was raised by my Boss why upgrade is required.   I stared saying   My: there is no more   support for SQL Server 2000 from Microsoft   Boss: what you will do  as DBA, Why Required Microsoft support   My: 2008 having very good features where 2000 not have...like Compression, resource governing. .Etc..   Boss: do you mean Database backup compression take full backup and compresse it using ZIP   Finally He (Boss) told me that give document proof why I have go for SQL Server 2008 and what down time you required?   Some body help me why really SQLServer 2008 is required and what are benefits   I have a link; please provide your greatest ideas so that I can convince my Boss.   http://www.mssqltips.com/tip.asp?tip=1462   share your greate Ideas please....   Thanks in Advance     SNIVAS

unable to create new table in sql server 2000 enterprise manager on windows7(Operating system)

  
i have successfuly installed sql server 2000 on windows7 and i am able to browse all the tables and edit aswell(on enterprise manager) but i have a problem while creating new tables it says ' MMC has detected an error in a snap-in' and provides options to either continue with the error or send a report to microsoft ,   Please help regarding this compatibility issue   Thanks in advance !

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

Unable to create Web Folders in Windows Server 2008 R2

  
Hi, We are unable to create web folder from a Windows Server 2008 R2 system as we do in Windows XP. The web url cannot be identified. The web url is an oracle repository application built in 32-bit OS and now we need this to be added in Windows Server 2008 R2 machine. Kindly help.  

Cannot Create Database Diagrams - SQL Server 2008 Management Studio

  
I cannot create a database diagram with my client-side SQL Server Management Studio.  I can do this if I am on the actual db server itself.  Please help.

(SQL Server 2008) Exporting entire databases to SQL CREATE/INSERT statements

  
Hi All, I'm looking for a way to export SQL Server 2008 (Express or otherwise) databases to CREATE/INSERT statements.  I've seen a million threads (give or take) with instructions for creating large (often illegible) TSQL scripts for the task.  However, I'm hoping that this isn't the only way by which to do it.  I have need to regularly back up the data from existing MS SQL Server DBs (which are used by some of our standard apps) to various other MySQL and PostgreSQL databases (which are used by PHP and Rails web front ends).  I need to export them from MS SQL Server 2008 to a general format (i.e. INSERT statements) and perform regex edits on the file as necessary. FYI, I've written a few applications that connect to the various DBs over ODBC, etc. and transfer the data row by row.  You can image how painful this process is... I realize that there are many tools that do this.  However, most of them approach the issue from the standpoint of migration, and not that of regularly scheduled backups.  Also, this tool will need to eventually be distributed so having to purchase a license for the migration app for each person/group simply isn't feasible. Anyway, if you guys have a quality, straightforward solution to this issue, it would certainly be appreciated. Best.

Can not connect to user instance of SQL Server 2008 Express with Advance Services in Visual Studio 2

  
I used SQL Server 2008 Express with Advanced Services (not SQL Server 2008 Express) in Visual Studio 2010 because it is the fast and easy way to developed and managed data-driven applications with powerful built-in reporting and full-text search functionality. I received Sql Server 2008 Service Pack 1 with Windows Update. It's ok if I connect to instance of SQL Server 2008 Express with Advance Services (with SP1) through SQL Server Management, but if I try to attach a database in Server Explorer (Visual Studio 2010), I get the following error: "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." I'm stuck at this point. Is there a fix or a workaround? I've already tried deleting the 'C:\Users\Administrtor\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS' directory as mentioned in other posts, with no luck. The error.log at 'C:\Users\Administrator\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log' has the following relevant entries: 2010-09-01 14:44:10.47 Server      Error: 17156, Severity: 16, State: 1. 2010-09-01 14:44:10.47 Server      initeventlog: Could not initiate the EventLog Service for the key '3677F6D3-17C7-4F', last error code is 2. 2010-09-01 14:44:10.47 Server &

data migrations from sql server 2000 to 2008

  
Hi,   Can any one please help me by giving some good links / tutorials  for migrating data from sql server 2000 to 2008 using SSIS     Thanks In Advance    Padmaja 

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

Problems with linked server to Analysis Services (SQL 2008)

  
I have problem with creating linked server from SQL database to Analsis services. BOth services are running on same machine. Operating system is Windows 2008. I create linked server (I use windows authentication and I am administrator on AS):  EXEC sp_addlinkedserver @server= 'OLAP_PRETOKI', @srvproduct = '', @provider='MSOLAP', @datasrc='localhost', @catalog='DWDatabase'  But when I try to test connection I get error (in the event log) and in the error log/dump I get this: 2010-09-03 13:48:28.41 Server Error: 17310, Severity: 20, State: 1. 2010-09-03 13:48:28.41 Server A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory. 2010-09-03 13:48:32.53 spid58 Open of fault log C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\log\exception.log failed. 2010-09-03 13:48:32.65 spid58 Using 'dbghelp.dll' version '4.0.5' 2010-09-03 13:48:32.66 spid58 SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 2010-09-03 13:48:32.66 spid58 * ******************************************************************************* 2010-09-03 13:48:32.66 spid58 * 2010-09-03 13:48:32.66 spid58 * BEGIN STACK DUMP: 2010-09-03 13:4

please help me to create database in sql server 2008

  
hi,    i am new to asp.net. i like to learn asp.net and c#. when i try to connect to sql server, it asks server name. i don't know server name and it asks database name also. how to create database and where can i access like phpmyadmin. in phpmyadmin i can create database and tables. i am new to this. please help me.thanks.  

Problems with linked server to Analysis Services (SQL 2008)

  
I have problem with creating linked server from SQL database to Analsis services. BOth services are running on same machine. Operating system is Windows 2008. I create linked server (I use windows authentication and I am administrator on AS):  EXEC sp_addlinkedserver @server= 'OLAP_PRETOKI', @srvproduct = '', @provider='MSOLAP', @datasrc='localhost', @catalog='DWDatabase'  But when I try to test connection I get error (in the event log) and in the error log/dump I get this: 2010-09-03 13:48:28.41 Server Error: 17310, Severity: 20, State: 1. 2010-09-03 13:48:28.41 Server A user request from the session with SPID 57 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory. 2010-09-03 13:48:32.53 spid58 Open of fault log C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\log\exception.log failed. 2010-09-03 13:48:32.65 spid58 Using 'dbghelp.dll' version '4.0.5' 2010-09-03 13:48:32.66 spid58 SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. 2010-09-03 13:48:32.66 spid58 * ******************************************************************************* 2010-09-03 13:48:32.66 spid58 * 2010-09-03 13:48:32.66 spid58 * BEGIN STACK DUMP: 2010-09-03 13:4
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