ISAM : Excel Linked Server In Sql Server 2005
Jun 7, 2007
Dear Friends,
I am getting error when trying to make excel file as the linked server in my sql server the details are as under:
os: windows xp and also tried on windows server 2003
server: sql server 2005 sp2
excel: office 2003 and also tried with .xls file of office 2007
tried to add the remote as well as file on the same computer as linked server, but error was:
could not find installable ISAM and error no was 7303.
Please help me up.
I got the idea to make it as linked server from microsoft article and followed the neat steps and got the errors.
Thanks,
View 1 Replies
ADVERTISEMENT
Oct 4, 2007
I have an Excel sheet that is dynamically updated (through DDE) and I want to import this data to a table in SQL Server 2005. Using SQL Server Management Studio to configure an Excel data source as a linked server.
(http://support.microsoft.com/kb/306397/EN-US/)
Following the first 5 steps should let me acces the table (but I cannot view the data in SQL Server 2005). However, I could not find how to export the data into an existing table. Does anyone know how or can give a pointer to document describing how to do this?
View 8 Replies
View Related
Aug 27, 2007
Is there a way to create a SQL 2005 x64 Linked server to an Excel (or Access) 2003/2007 file? In SQL 2005 32bit this was possible. Does anyone know of a solution or a work around?
View 3 Replies
View Related
Sep 6, 2007
Is it possible to create a linked server from an Excel spreadsheet and give it a primary key? If so, how?
Thanks,
--Stan
View 2 Replies
View Related
Oct 18, 2007
Hi,
I am trying to delete a row in excel [Sheet1$], where this data in that row is used in a pivot table in same excel [Sheet2$] which should also get deleted, when i try to delete that row using "delete ... from [Sheet1$] " it is throwing an error message "Deleting data in a linked table is not supported by this ISAM. (Microsoft Office Access Database Engine)"
Can you please guide me in overcoming this error...........
Thanks in advance,
Warm Regards,
gchanduu
View 2 Replies
View Related
Jul 25, 2007
I'm trying to quickly create a way of importing data from an excel sheet on my C drive to a table on a sql 2005 db hosted by a provider.
I set this up according to the following:
http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx
The article is in C# but I write in VB. I think I got it except I get this error:
Could not find installable ISAM.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: Could not find installable ISAM.Source Error:
Line 23: Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select ID,Data FROM [Data$]", connection)
Line 24:
Line 25: connection.Open()
Line 26:
Line 27: ' Create DbDataReader to Data Worksheet
Microsoft is not much help. A couple of folks say to contact my host. Before I do I just want to be sure there code I have is correct.
Could someone take a quick look at this.
Thanks,
Here is the code behind the button.
Protected Sub BTNImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BTNImport.Click
' Connection String to Excel Workbook
Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Book1.xls;ExtendedProperties=""Excel 8.0;HDR=YES;"""
' Connection to Excel WorkbookUsing connection As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(excelConnectionString)Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select ID,Data FROM [Data$]", connection)
connection.Open()
' Create DbDataReader to Data WorksheetUsing dr As Data.Common.DbDataReader = command.ExecuteReader()
' SQL Server Connection StringDim connectionString As String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString
'Origina from David Hyden's site - Dim sqlConnectionString As String = "Data Source=.;Initial Catalog=Test;Integrated Security=True"
' Bulk Copy to SQL ServerUsing bulkCopy As Data.SqlClient.SqlBulkCopy = New Data.SqlClient.SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = "ExcelData"
bulkCopy.WriteToServer(dr)
End Using
End Using
End UsingEnd Sub
View 6 Replies
View Related
Aug 29, 2006
I had SQLDMO at reference library.
Private Sub test()
Dim s As SQLDMO.SQLServer
Dim ls As SQLDMO.LinkedServer
Set s = New SQLDMO.SQLServer
s.Connect "Server1", "ID", "Password"
Set ls = New SQLDMO.LinkedServer
With ls
.Name = "Server2"
.ProviderName = "SQLOLEDB"
.DataSource = "Server2"
' .ProviderString = ""
End With
s.LinkedServers.Add ls
s.Close
'End Sub
I block ProviderString as i don't know what is it.
I got an error message when it's running the line "s.LinkedServers.Add ls" that
"Run-time error '-2147206257 (80043b8f)':
Automation error"
My question:
1. What is providerstring? what should be put here?
2. How to fix the error
View 1 Replies
View Related
Apr 25, 2000
I am trying to link an excel file as a linked server. I tried two methods unsuccessfully.
first: using
EXEC sp_addlinkedserver 'Ex',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:list5.xls',
NULL,
'Excel 5.0'
GO
When I run
EXEC sp_tables_ex Ex, I get the error below.
Server: Msg 7303, Level 16, State 2, Procedure sp_tables_ex, Line 20
Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
Second: I link the excel file using ODBC datasource:
EXEC sp_tables_ex mylink, works fine with mylink being the linked server name. I get the table names eg. table1$, table2$.... However when I try to query the tables, I get this error:
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
thanks
FS
View 1 Replies
View Related
Apr 5, 2006
We have been having problems getting a linked server to an excel file
working with an UNC path. If the UNC path is to the SQL server itself
it will work but not if the UNC path is to another server.
The SQL server 2000 SP 4 processes are running under a domain id and we
can logon interactively with that same login id and we can access the
excel file via the same UNC path.
We have tried it with setting up a linked server and also linking to it
'on the fly':
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\AnotherServerUNCpathexcel.xls";User
ID=Admin;Password=;Extended properties=Excel 5.0')...sheet1$
go
The message we are getting is the following:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The
provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The provider did not
give any information about the error.].
It appears from reading Microsoft's documentation and other topics that
this should be possible. Any ideas on what we are missing?
View 6 Replies
View Related
May 12, 2008
I am trying to set up a linked server in SQL Server 2005 to link to an excel spreadsheet.
-I am selecting Jet 4.0 as the provider
-Product name is Excel
-Data Source is the path on our network to the excel file: N:Devon 5403 4.0 Engineering4.01 ProcessLinelistIFCLDT Field.xls
-Provider String is Excel 8.0
-Security | Login not defined is set to Be made using the login's current security context.
The Excel file is an Excel 2003 spreadsheet. The worksheet is titled Pages
I have a query window open in SQL Server Management Studio and the following is my select statement:
SELECT * FROM DEVON_LINE_LIST...Pages$
I get the following error message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST".
I get similar error messages no matter which security settings I pick.
Any thought as to what I can try to get this to work?
View 3 Replies
View Related
Dec 8, 2006
Hello,
I have attempted to set up a linked server to an Excel 2003 workbook, and I get an OLEDB error when I attempt to query against it. Some notes about the workbook;
-It has one worksheet in it named 'Add Revenue Accts'.
-The name of the workbook is 'Revenue_to_All_Accounts.xls'
-Its location is \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_Accounts
I have the linked server configured as follows;
-Linked Server; REVENUE_TO_ALL_ACCOUNTS
-Provider; Microsoft Jet 4.0 OLE DB Provider
-Data Source; \cdnbwfin1dataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls
-Provider String; Microsoft.Jet.OLEDB.4.0;Data Source=\Cdnbwfin1DataCDunnComdataReportsReba_HolmesRevenue_All_AccountsRevenue_to_All_Accounts.xls;Persist Security Info=False
When I attempt the following query;
SELECT * FROM OPENQUERY(REVENUE_TO_ALL_ACCOUNTS, 'SELECT * FROM [Add Revenue Accts$]')
The following message appears, and no results are returned;
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
I have Googled this error, but I have not found anything that really points to what the problem might be. What could be the problem?
Thank you for your help!
cdun2
View 1 Replies
View Related
Mar 21, 2008
Hello all-
I configured an Excel document as a Linked Server importing into SQL Server using SQL Server Management Studio Express 2005. I run a query like this to set up the desired database:
Use Num1DBase
GO
SELECT * INTO Num1Table FROM LINKSERV1...[Sheet1$]
However, my production Excel document has many, many sheets with spaces in the title [Sheet 2$] that results in this error:
Msg 7314, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LINKSERV1" does not contain the table "Sheet 2$". The table either does not exist or the current user does not have permissions on that table.
All I can think of right now is manually going in and removing the spaces from the worksheet names so the query works. Is there a more programmatic way for me to deal with this?
Thanks, MHY
View 9 Replies
View Related
Oct 16, 2006
To All
I'm using the following code to create a Linked Excel server.
---
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\schuette-dcDriveHTempEDICSCNEDI.xls',
NULL,
'Excel 8.0';
GO
-----
The server create correctly, but when I try the following
SELECT * FROM ExcelSource...CSCN
I get the following error
----
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 15
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
----
I have verified that i have full access to the "Temp" directory. I have even tried creating a "Named Range" in the spreadsheet with no success.
Thanks
David Davis
View 2 Replies
View Related
Jan 18, 2007
I created a linked Excel server that is stored in a SQL2000 database.
I can run the following from the SQL server with no problem.
Select * From CSCNEDI...EDI$
When I try and run the select from my WinXP computer I get the following from both SQL2000 Query Analyzer or SQL2005 Management Studio (these are configured for client access)
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Thanks
David Davis
Schuette Inc.
View 1 Replies
View Related
May 16, 2007
I'm trying to set up a linked server as in the article
http://www.databasejournal.com/features/mssql/article.php/10894_3085211_3
I use the following SQL to set up a linked server with Excel and attempt to retrieve data. The c: empauthors.xls is downloaded from the second page of the article above.
EXEC sp_addlinkedserver 'EXCEL',
'Excel',
'Microsoft.Jet.OLEDB.4.0',
'c: empauthors.xls',
NULL,
'Excel 8.0',
NULL
GO
SELECT * FROM EXCEL...Sheet1$
GO
The query sets up the linked server OK, and retrieves the field names, but no data is returned. The results in Management Studio are a list of column names:
au_id au_lname au_fname phone address city state zip
contract
then an error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL" returned message "Unknown".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT `Tbl1002`.`au_id` AS `Col1004`,`Tbl1002`.`au_lname` AS `Col1005`,`Tbl1002`.`au_fname` AS `Col1006`,`Tbl1002`.`phone` AS `Col1007`,`Tbl1002`.`address` AS `Col1008`,`Tbl1002`.`city` AS `Col1009`,`Tbl1002`.`state` AS `Col1010`,`Tbl1002`.`zip` AS `Col1011`,`Tbl1002`.`contract` AS `Col1012` FROM `Sheet1$` `Tbl1002`" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL".
Does anyone know how to resolve this? I'm using Windows Authentication to connect to a SQL server (9.0.3054) instance on localhost, and am running from a test database query window.
Thanks!
View 2 Replies
View Related
Jul 20, 2005
Hi,Please help, I'm getting desperate. Any ideas warmly welcomed!I'm trying to read from a basic excel file (1000 or so rows fromcolumn A) but am having problems. The code I am using is:Declare @Return IntSET NOCOUNT ONExec @Return= [master]..[sp_addlinkedServer] 'READ_XLS', 'EXCEL','Microsoft.Jet.OleDB.4.0' , 'e:jsbackupRACodes.xls',NULL, 'EXCEL 8.0'print 'set up Return : ' + convert(varchar(10),@Return)--NB E: is the drive as seen oon the serverEXEC sp_addlinkedsrvlogin@rmtsrvname = 'READ_XLS',@useself = 'true'print 'login Return : ' + convert(varchar(10),@Return)When I try to read from the (one) excel sheet in the file, viaSelect * from [READ_XLS]...RACodes$or to list what tables/sheets are available, viaexec sp_tables_ex 'READ_XLS'I get the following error:OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.Authentication failed.[OLE/DB provider returned message: Cannot start your application. Theworkgroup information file is missing or opened exclusively by anotheruser.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80040e4d: Authenticationfailed.].What am I missing?*Many* thanks in advance.Andy
View 1 Replies
View Related
May 4, 2007
Hi
I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions.
I am getting the following error while accessing the linked server in management studio based on the scenario given below ;
------ Error Message Starts
OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'abc'.
------ Error Message Ends
Consider login name is abc.
Now this login abc has sysadmin rights on sql server 2005.
The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role.
I have configured the linked server using the following options;
1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 .
2. I have also tried specifying remote login / password option.
Anyone having any idea, would be of great help.
Regards,
Salman Shehbaz.
View 3 Replies
View Related
Apr 18, 2007
Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.
I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible??
View 1 Replies
View Related
Dec 28, 2005
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', @provstr=N'"Provider=Sybase.ASEOLEDBProvider;Server Name=servername,5000;Initial Catalog=databasename;User Id=username;Password=password"'
</code>
I then ran sp_tables_ex to make sure I could view the tables in the Sybase database. Here is the error message I get:
<code>
OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".
</code>
Any ideas what is happening here?
View 10 Replies
View Related
Jan 24, 2008
What: I am trying to import data from spreadsheets to SQL Server.
Where: Windows Vista, SQL Server 2005 Express, Office 2007.
How: Using linked servers following KB306397.
In SQL Management Studio Express, I created a new Linked Server as follows, with everything else at default:
Linked server: XLSX
Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider
Product name: XLSX
Data source: D:XLSX.xlsx
Provider string: Excel 12.0
The linked server was created ok.
I then followed KB321686 and ran this:
select * from OPENQUERY(XLSX, 'Select * From [Sheet1$]')
and got this:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX".
But if I ran: select * from OPENQUERY(XLSX, 'Select * From [nonexistent$]')
the error is:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "Select * From [nonexistent$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" indicates that either the object has no columns or the current user does not have permissions on that object.
It seems that there is an access rights problem if I get the sheet name correct. May I know what I must do to get this to work. I have already given read/write rights to the spreadsheet to NETWORK SERVICE and SQLServer2005MSSQLUser$servername$SQLEXPRESS.
From the same KB article, I also tried:
select * from XLSX...[Sheet1$]
but got this:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLSX".
Help help.
Thanks.
View 5 Replies
View Related
Jan 15, 2008
I was using linked servers to import Excel spreadsheets into SQL Server Express 2005. This worked fine with Windows XP and Office 2003.
I have just migrated all my stuff to Vista and Office 2007. Linked servers just can't be created:
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXX".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XXX" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
&Yes
&No
------------------------------
The parameter values I used are:
Provider: Microsoft Jet 4.0 OLE DB Provider
Product name: Excel
Data source: D:...somelocalfile.xls
Provider string: Excel 8.0
I gave full access rights to the Data source file and folder to NETWORK SERVICE and SQLServer2005MSSQLUser$MOZART$SQLEXPRESS where mozart is my PC name.
If I change Data source or Provider string to some garbage string, the exact same error message appears. So it appears the error might be in the Provider?
Help help please.
View 4 Replies
View Related
May 25, 2014
I have a 3rd party dashboard application that I can only use SQL authenticated logins to connect to the database.
I'm trying to create a query within the application that will directly access an excel file through a linked server.
As a test, I login to SSMS as the sql auth user to run the linked server query below but the following error is returned:
select *
from Corporate...[Sheet1$]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate".
When I login as a Windows auth user, I can successfully run the above query.
I noticed within the linked server's security definition that I cannot specify a windows auth user as the mapped Remote User or as the Remote login
I've tried creating a Credential object with the identity of the windows user and assign that object to the sql auth user but to no avail. I still get the same error
I am using SQL Server express so the option of an automated server agent job to import the excel file is not available.
Details:
SQL Server Express 2012
Office version: Excel 10
Provider: Microsoft.ACE.OLEDB.12.0
View 9 Replies
View Related
Aug 30, 2006
I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.
View 4 Replies
View Related
Aug 27, 2007
I receive the following error message when I run a distributed query against a loopback linked server in SQL Server 2005:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
To resolve this problem, I was told that running a distributed query against a loopback linked server is not supported in SQL Server 2005. And I am suggested to use a remote server definition (sp_addserver) instead of a linked server definition to resolve this problem. (Although this is only a temporary resolution, which will deprecate in Katmai)
However, I run into another problem when I use the remote server definition. I receive the following error message:
Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'ServerNameSQL2005' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.
Could anyone please help me out?
(I include the reproduce steps for the first error message, followed by my resolution that generates the second error message)
======
Reproduce steps for the first error message
======
On the ComputerAInstanceA instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseA
GO
USE DatabaseA
GO
CREATE TABLE TestTable(Col1 int, Col2 varchar(50))
GO
INSERT INTO TestTable VALUES (1, 'Hello World')
GO
On the ComputerBInstanceB instance, run the following statement to create a database and a table:
CREATE DATABASE DatabaseB
GO
USE DatabaseB
GO
CREATE TABLE TestTable (Col1 int, Col2 varchar(50))
GO
On the ComputerAInstanceA instance, create a linked server that links to the ComputerBInstanceB instance. Assume the name of the linked server is LNK_ServerB.
On the ComputerBInstanceB instance, create a linked server that links to the ComputerAInstanceA instance. Assume the name of the linked server is LNK_ServerA.
On the ComputerBInstanceB instance, run the following statement:
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * from LNK_ServerA.DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXEC LNK_ServerB.DatabaseB.dbo.InsertA
GO
Then I receive the first error message.
=======
My resolution that generates the second error message
=======
On the ComputerBInstanceB instance, run the following statement:
sp_addserver 'ComputerAInstanceA'
GO
sp_serveroption 'ComputerAInstanceA', 'Data Access', 'TRUE'
GO
USE DatabaseB
GO
CREATE PROCEDURE InsertA AS
BEGIN
SELECT * FROM [ComputerAInstanceA].DatabaseA.dbo.TestTable
END
GO
On the ComputerAInstanceA instance, run the following statement:
USE DatabaseA
GO
INSERT INTO TestTable
EXECUTE [ComputerBInstanceB].[DatabaseB].[dbo].[InsertA]
GO
Then I receive the second error message.
View 1 Replies
View Related
Jul 30, 2007
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
View 15 Replies
View Related
Jan 16, 2008
Hello
SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram
SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram
Symptoms
Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.
At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.
Please, any ideas?
Working
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'
Not Working (correctly anyway!)
DECALRE @Value tinyint
SET @Value = 22
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value
View 5 Replies
View Related
May 27, 2008
Hello,
I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below:
.
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "dev_server". The provider supports the interface, but returns a failure code when it is used
The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.
Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?
Thanks,
Simba
View 1 Replies
View Related
Feb 19, 2008
Actually i have linked server in SQL 2000 with Provider:Microsoft OLEDB for ODBC which is working perfectly fine. I don't know the password for this linked server too.
Now i want to create same linked server with same provider in SQL server 2005 for Microsoft oledb for ODBC but they don't have this provider in 2005. How can i make it to work.ANy expert who knows this.
and how to use same password in linkedserver in 2005
View 12 Replies
View Related
Jan 14, 2008
Hi,
I created a linked server from SQL server 2005 (cluster) to SQL server 2000. When i try to execute the query from local server i get this error
OLE DB provider "SQLNCLI" for linked server "Rem_serv" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'rem_User'.
Please help
Thanks and regards
Helen
View 3 Replies
View Related
May 24, 2006
I am trying to create a linked server in SQL Server 2005 to show tables in our AS400. I made the connection, however, the tables are not showing up under the Linked Server name.
How do you get the linked tables to display in the Linked Server folder?
David
View 4 Replies
View Related
Dec 18, 2006
Hi,
I am using SQL Express 2005 sp1
When I created a linked Server to the DB2 on iseries ( V4R5) , the SELECT statement fetches me only the first record and the following message appears.
OLE DB provider "MSDASQL" for linked server "DB2TEST" returned message "[IBM][Client Access Express ODBC Driver (32-bit)]Driver not capable.".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "DB2TEST".
I am using 'MSDASQL' which refers to the DSN setup on my machine using the ODBC "Client Access Driver 32-bit"
Strangely when I used the same DSN in SQL 2000 within the DTS package, I was able to connect and import data and worked like a charm.
Can anyone advice me where I am going wrong.
Thanks in advance
View 1 Replies
View Related
Dec 28, 2007
My problem is happening while adding a AS 400 Linked Server. I use
EXEC sp_addlinkedserver @server='TEST1',
@srvproduct='IBM AS400 OLE DB Provider',
@datasrc ='10.36.0.101',
@provider='IBMDA400',
@provstr='Initial Catalog=QGPL;CCSID=37;PCCodePage=1252'
But, it is not effective with IBM AS400 OLE Provider, It gets always time-out score.
Can Anyone Help Me
View 6 Replies
View Related
Jan 15, 2008
Hi All you good people there...
Need a big favour.
I am working on a Linked Server using SQL server 2005.
My present requirement is that I need to have distributed qurey which will fetch data from out of LAN Sql Server databases.
I need to know these things at earliest.
1. How to expose SQL server (may be data or instance itself) over internet?
2. How can I add this internet exposed SQL server as a Linked server on my local SQL server?
3. How to prevent my exposed SQL server data from hackers, as I need it only for SQL to SQL interaction?
Any help is appreciated.
Are there any good sites/links to refer??
Thanks in advance.
Gauri Shenolikar
View 8 Replies
View Related
