Programmatically Set Linked Server Properties + SQL 2005 + RPC / RPC OUT
May 21, 2008
Hi Folks,
In SQL 2005, the linked servers are not automatically enabled for RPC / RPC OUT like they were in SQL 2000. I have set the properties for my linked server by using SQL Management Studio.
I right click on the Linked Server --> Properties --> Server Options
There are options like: Collation Compatible, Data Access, RPC, RPC OUT, Use Remote Collation
There is a drop down box to set each value to TRUE or FALSE. Setting them through management studio works great, however for the production environment I need to set these properties programmatically. Is there a stored procedure or registry setting that would allow my program to set these values for a given linked server?
Thank you.
View 3 Replies
ADVERTISEMENT
May 4, 2015
How to change the Security Properties of a Linked Server.
In tsql, I need to setup a linked server and want to set the Properties -> Security -> For a login not defined in the list above, connections will: -> Not be made But I cannot figure out how to set it to Not be made. I manually set it in the GUI, and the scripted the linked server and when I ran the script to create it was set to Be made using the login's current security context. The create script did not even set it correctly.
I have looked around and all that I have found is a column in sys.linked_logins, uses_self_credential that looks promising. But I cannot change the system catalog.
how i can set the "For a login not defined in the list above" security property in tsql?
View 4 Replies
View Related
Dec 19, 2005
Hi,When I click on the properties of a linked server, all the Generalproperties are read - only, which means that if I want to edit anygeneral properties I have to delete the linked server and recreate it.Needless to say this is a pain. Is this by design or some kind of bug?Is there a workaround?Thanks
View 8 Replies
View Related
Jul 10, 2007
Hi
I have a package which contains a foreach container. Can anyone help me in setting the properties for the enumerators programmatically??? I am trying to set the properties for the enumerator "ForEach File Enumerator"
The properties which i need to set are
1. Folder
2. File Type
3. Traverse Subfolder
4. Retrieve File Name
Thanks in Advance
Suganya
View 1 Replies
View Related
Apr 3, 2007
I'm logged in as 'sa' and I'm using Microsoft Sql Server Mgmt Studio. I've established a Linked Server using Provider MSDASQL, and I'm able to retrieve data from the linked server. However, when I attempt to set the properties for my linked server, I get the following error message.
Adhoc updates to system catalogs are not allowed. (Microsoft SQL Server, Error: 259)
I'm not directly accessing the system catalogs (as far as I can tell), I'm actually using the Linked Server Properties user dialog to change the Server Options. Any help is appreciated.
View 3 Replies
View Related
Mar 16, 2015
I have an ODBC connection string that is working fine with the following properties:
Database="XXXXXXX",Network="YYYYYY"; strangely no server is specified in the string, but it is specified in the ODBC Connection file.
I am trying to do a new server registration in SSMS for this database.However, I don't understand where the network spec is placed.
Under Registered server name I've tried:
YYYYYYXXXXX
When I browse the server for the database instance list, I receive "network path was not found".
I even tried:"XXXXXXX",Network="YYYYYY" for the registered server name.Same error message.
What am I doing wrong ?
View 1 Replies
View Related
Oct 20, 2006
Hello to everyone, I've a question about UDTs and the way I can use them to access tables and columns where they are applied in a SQL Server 2005 DB.
I've already spent 2 days googling and MSDN reading but nothing helped me to solve my problem, thats why I'm posting it here (this is the second post, maybe the last one was in the wrong Forum).
The scenario follows:
I've created a UDT called MyUDT that exposes 2 properties MyTable, MyColumn, here its the code:
[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000, Name = "MyUDT")]
public class MyUDT : INullable, IBinarySerialize
{
private string _myTable;
private string _myColumn;
....
/// <summary>
/// Set or Get the Table Name where the UDT is applied.
/// </summary>
public string MyTable {
get { return this._myTable; }
set { this._myTable = value; }
}
/// <summary>
/// Set or Get the Table's Column Name where the UDT is applied.
/// </summary>
public string MyColumn
{
get { return this._myColumn; }
set { this._myColumn = value; }
}
....
}
And here it's my question/s:
How can I expose the defined Properties (MyTable, MyColumn) in order to be directly used from
SQL Server Management Studio within the Column Properties Panel?
Pls take a look to the print screen placed below:
[img=http://img81.imageshack.us/img81/7193/untitledip1.th.jpg]
If it is not possible, is there a way for any UDT to get back from the sql server execution context
the table and the column where it is applied/used?
I need to solve that in order to later retrieve via SQL the Extended Table Properties where the UDT is used
and make some work on presented MetaData. Thanks in advance, every answer/help will be very much appreciated.
View 10 Replies
View Related
Jun 9, 2006
Hi, all here,
Would please anyone here give me any idea about how can I connect to SQL Server 2005 analysis services server and send XML request to it programmatically (with Business intelligence development studio in SQL Server 2005)? Thanks a lot.
With best regards,
Yours sincerely,
View 3 Replies
View Related
Jan 2, 2008
Dear All:
I am new to SSRS 2005.
I have created many shared datsources and reprots at http://localhost/resportserver.
I am trying to access these reports from ReportViewer in the web application. Actually I want to use these reports for different customers by just changing the datsource programitically.
Is this is a possible?
Thank you
Theju
View 2 Replies
View Related
Oct 3, 2006
Hi,
how to reschedule/schedule jobs in server 2000 and 2005 Programmatically?
thanks
sandipan
View 1 Replies
View Related
Mar 27, 2006
I have just upgraded to SQL Server 2005 from SQL Server 2000.In Microsoft SQL Server Management Studio, when I click on databaseproperties, I receive the following error:-Cannot resolve the collation conflict between"Latin1_General_CI_AS"and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.(Microsoft SQL Server, Error: 468)Some reference suggest that I can change the database collation byclicking database properties!What can I do?
View 21 Replies
View Related
Jul 13, 2007
I installed SQL Server 2005 Developer Edition. When i create a new database (using the "New Database" dialog) i cannot set the new database's compatibility level to "SQL Server 2005(90)" because this option is not in the "dropdown list". the only items shown are: "SQL Server 7.0(70)" and "SQL Server 2000(80)". I set the owner to "sa". How do i get "SQL Server 2005(90)" in my "compatibility level" drop down list? Is this an installation option that i missed? Thanks in advance for any assistance!
-chris
View 12 Replies
View Related
Nov 13, 2006
I've experienced the problem with creating a linked server from SQL 2005 64-bit to SQL 2000.
"The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
I have resolved this by upgrading the system stored procs on the 2000 machine by running instcat.sql, documented in http://support.microsoft.com/default.aspx/kb/906954/en-us . Works great.
However, I'm now experiencing the same error when linking from SQL 2005 64-bit to SQL 7. I'm concerned about applying 2000 code onto a SQL 7 machine. Any ideas on how to handle this case?
Thanks
Paul
View 1 Replies
View Related
Apr 20, 2006
I have a linked server to Sybase using MSDASQL. I can query the tables from the linked server using SQL Management Studio (windows login). When I put the same query into a job, it failed to work. The query is simple:
SELECT * from MyLinkedSvr.RemotDB.dbo.RemoteTable
The linked server setting include the login mapping, which maps my windows login and sa to the remote sybase login.
I'm using SQL 2005 9.0.1339 on Windows2000 Pro w/ SP4. SQL Agent starts as local system in the Services panel. Shared Memory/TCPIP/Named Pipes are enabled for Server/Client Network.
Thanks!
View 3 Replies
View Related
Jan 21, 2006
<<cross posted from microsoft.public.sqlserver.server>>Environment: Windows 2003 Standard Edition SP1, SQL 2005 EE RTM (bothservers)I am getting the following messages when I run this query:select * from LINKEDSERVER.databasename.dbo.tablenameOLE DB provider "SQLNCLI" for linked server "LINKEDSERVER" returnedmessage "An error has occurred while establishing a connection to theserver. When connecting to SQL Server 2005, this failure may be causedby the fact that under the default settings SQL Server does not allowremote connections.".Msg 782, Level 16, State 1, Line 0SQL Network Interfaces: No credentials are available in the securitypackageOLE DB provider "SQLNCLI" for linked server "LINKEDSERVER" returnedmessage "Client unable to establish connection".Msg 782, Level 16, State 1, Line 0SQL Network Interfaces: No credentials are available in the securitypackageLINKEDSERVER is set up as a SQL Server linked server., with no loginmapping, and connections will be made using the login's currentsecurity context.Both servers allow local and remote connections via TCP/IP, and NamedPipes is disabled on both servers. Both servers are trusted fordelegation, and the service accounts for the MSSQLSERVER service onboth servers are also trusted for delegation.We are auditing successful and failed logins, and are not seeing anyactivity on the linked server.Any help would be appreciated.Randy
View 3 Replies
View Related
Nov 16, 2007
I have a Win 2003 running SQL 2005 Workgroup on a hosted site. I have full access to all servers. I am trying to create a linked server to my old SQL 7 server on a Win 2000 server in another network. I can ping the Win 2000 server from the Win 2003 server. I can connect via VPN from the Win 2003 server to the other network firewall and create a linked server to an AS400 server at 192.168.1.50. On the internal network behind the firewall the Win 2000 server is at 192.168.2.133 and natted to 10.10.50.133 via a load balancer. I cannot ping the Win 2000 server over the VPN even if I add an entry to the hosts file.
The SQL 7 server is autostarted with agent and MSDTC autostarted under SQL/NT authority under the administrator account. I allow remote servers to connect via RPC.
I know just enough to be dangerous and have researched this to death. I need a little help with this. I keep getting "could not open a connection to SQL Server [53]" and have researched that to no end. On the SQL 2005 management studio to create a linked server I use Microsoft OLE DB provider for SQL Server and SQLNCLI. I can't determine the connection string so I leave that blank.
View 9 Replies
View Related
Sep 17, 2007
I am trying to set up the Linked 2005 Server to Teradata v2r5, and have got most of the way there. My problem is that using both methods (OPENQUERY and SELECT with 4-part naming string) seems to submit only the SELECT * part to Teradata with the rest of the SQL waiting until the data is all pulled back to SQL Server. This is a problem when working with big tables! It puts a huge strain on the network, and also runs me out of temp space in Teradata. Is there a way to get ALL the SQL passed to Teradata? I am looking to create a Report Model for Report Builder or Pro-Clarity. I have a dummy SQL Server 2005 database set up with views of the Teradata tables through the Linked Server. Any help would be greatly appreciated!
View 1 Replies
View Related
May 7, 2007
I have database in 2000 which needs to be access from SQL 2005 via linked server ? and vice - versa ?
How do we do that ?
we run select statement we get following error message.
Error 4064 : cannot open user defulat database : login failed.
We are using SQL 2000/sp4
SQL 2005/sp2
Please advice.
Thank you
View 1 Replies
View Related
Mar 24, 2008
Hi,
I've been able to make a linked server to an access document as follows and it works perfectly:
EXECUTE sp_addlinkedserver 'North', 'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0','C:Practice FilesNorthwind.mdb'
SELECT * FROM North...Customers
I have 2x sql 2005 servers at home at different machines. I'm logging on both machines by using windows authentication. How can I make a linked server between them?
Thank you.
View 3 Replies
View Related
Jul 26, 2006
With Linked server you can access DB2 resources as if it is another object in your SQL Server.
You don€™t have to establish links from all nodes to the DB2 Server.
Connection from SQL server 2005 to DB2 Server is sufficient to all SQL server users to access DB2 resources.
1. Install MicroSoft OLE DB provider For DB2 in SQL server 2005.
2. configure as follows:
Start - - -> all programs - - - > MicroSoft OLE DB provider For DB2 - - - > Data Access Tool.
Right click on data sources - - -> New Data Source
In Data Source Wizard - - > Data Source plat form = DB2/MVS or any other appropriate plat form from drop down
Net work type = TCP/IP - - > Next
Address = IP address:xx.xx.xx.xx
Port Number: xxxxxxx - - -> Next
Initial Catelogue = SAMPLE
Package Collection = DB2INST1
Default schema = DB2INST1
Default Qualifier = Leave it blank - - -> NEXT
HOST CCSID = EBCDIC- U.S./Canada[37] or appropriate one
P.C Code Page = ANSI-Latin I[1252] or appropriate one
Process Binary as character = check the box - - -> NEXT
Interactive sign on - - - > select it
Provide login name and password.
Check "Allow Password saving" - - - -> NEXT
Check appropriate boxes like read only etc. - - -> NEXT
Click Connect tab - - -> make sure "Success ful" message is appearing
Example: - "Successfully connected to data source 'DB2-server'.
Server class: DB2
Server version: 8021.00.0000"
- - - -> NEXT
Provide a data source name.(Example: DB2-Server)
Check initialization string option - - - > NEXT
Review completed task list on the window and
Select "FINISH" to complete the work.
The data source which you have added just now(DB2-Server) will be appearing on the grid now.
right click on the data source name and make a note of the path where it has formed
right click on the data source name and click "Display connection string"
cut and paste this connection string on the lower window to note pad for future use.
Start MS SQL server Management studio
Add a fresh linked server in 'SQL Server - - > Server Objects - - -> Linked Server" as follows:
Click Mouse right button on linked server and select "New Linked Server"
linked server = Provide a meaning ful, fully identifiable name for Linked server(Example"DB2-Linux")
Provider = MicroSoft OLE DB provider For DB2
Product name = DB2OLEDB
Data source = IP address
Provider string = Paste the connection string pasted to note pad earlier.
Location = Leave it blank
Catalog = SAMPLE
Click on security on the left side pane
Provide an SQL Server login from the drop down
Do not select check impersonate
Provide DB2 username and password and remote user name and password.
Click OK to save.
You will find the name of liked server appearing below the object Linked Server(Example "DB2-LINUX")
Open a new query window and execute an open query
Example:
SELECT * FROM OPENQUERY("DB2-LINUX", 'SELECT * FROM SAMPLE.DB2INST1.EMPLOYEE')
In the Example "DB2-LINUX" is the name of the linked server
"SAMPLE.DB2INST1.EMPLOYEE" is the name of the table. Sample = name of the db
DB2INST1= name of schema to which the table belongs to
Employee = name of the table
Another Example would be
SELECT empno,FIRSTNME FROM OPENQUERY("DB2-LINUX", 'SELECT * FROM SAMPLE.DB2INST1.EMPLOYEE')
View 1 Replies
View Related
Nov 13, 2007
I have a problem I have spent hours on already. I have found multiple posts, but not a solution to this problem. This is not a rights issue.
I have two SQL 2005 servers (I cannot upgrade to SP2 yet):
ServerA - ver 9.00.2153.00 (X64)
ServerB - ver 9.00.2153.00 (Intel X86)
I create a linked server on ServerB pointed to ServerA, uses 'sa' for all logins, works perfectly.
I create a linked server on ServerA pointed to ServerB, uses 'sa' for all logins, FAILS.
Run on ServerA:
SELECT *
FROM OPENQUERY([ServerB], 'SELECT [field1] FROM database.dbo.tablea')
Returns:
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "ServerB" supplied inconsistent metadata for a column. The column "field1" (compile-time ordinal 1) of object "SELECT [field1] FROM database.dbo.tablea" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.
Run on ServerA:
SELECT [field1] FROM [ServerB].database.dbo.tablea
Returns:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "ServerB" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "ServerB". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema
Any bright ideas?
View 3 Replies
View Related
Jan 15, 2007
I'm having issue in connecting to sql 2005 from sql 2000 via linked server.
Please advice what I should do to overcome the problem.
Thanks.
-EC
View 4 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
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
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
Dec 11, 2007
I NEED HELP! Does anyone know how to remove a database schema from SQL Server 2005 using C#? I have a web application that deletes users. In SQL2k, I am able to simply delete the users. In SQL2005, I must delete or alter the schema BEFORE I can delete the users. Of course, the query below works, but how can I get this to work in code for a web application?
USE EAST_USERS
DROP SCHEMA "plijsmith"
Thanks
View 4 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
Mar 7, 2008
Hi Guys,
I am trying to Query text file into SQL Server 2005 using linked server.
In SQL Server 2000 here is what I have.
Linked server configured to use following parameters
Product Name : Customer_file
Provider Name: Customer_File ( System DSN)
Here is basic file
0000003700 0000XXXXXX XX14 61J 1154-06-249
0000008600 0000XXXX11 YY17 41F 1144-12-309
0000010700 00023232323233 XX48 9DY 1145-10-299
0000015700 00001212121212 OX1 5RW 1163-08-079
0000026600 00001233333 YY1 31S 1150-12-111
0000027600 00001212121 YY15 8HU 1176-04-0891
0000038700 0000H13344 B120 8RF 1157-05-211
Now when I EXECUTE following command.
select * from openquery (TEST, 'select f1,f2,f3,f4,f5 from t#txt');
I normally get formatted output in columns..
As soon as I setup the same Linked server in SQL Server 2005, and follow same process I get all data in one column with first line as header.
I am basically concerting old import process into new one..so trying to figure out what i am missing?
Anay help?
THanks
SKR
View 1 Replies
View Related
