Testing Linked Server Connection
Jan 16, 2001
I have a server with several linked servers. Before I execute sql against any of these linked servers I want to check to make sure the connection is active.
I created a small stored procedure that takes the server name. I am trying to get it to run a simple select statement. If the select statement runs without errors (meaning the linked server is active) I want to return a value for success. If the select statement fails I want to return a value for a failure.
The problem is that I am having trouble with the error. When I shut down the linked server and run the select statement I get the following error returned:
Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.
The stored procedure I have only returns this message and does not send the return value that I set. How do I get my procedure to return a failure value instead of the following error above? Is there a better way for me to check for this type of error?
Thanks
View 1 Replies
ADVERTISEMENT
Sep 18, 2007
What is the syntax to telnet a database?I triedtelnet <hostname> 1433 - but get this error:telnet ws1234 1433Connecting To ws1234...Could not open connection to the host, on port 1433: Connect failedMy computer name is ws1234.comHow do I test a connection to a sql server database instance?Thanks,VVvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvWW
View 2 Replies
View Related
Jul 23, 2005
Hi,[color=blue]>From my vb.net application I want to test a connection to a server and[/color]a database, given a user name, password, server name and database name.What is the best way to test if the connection string is valid or not ?Would a connection to the Master DB be enough to say it is valid or not?Thx
View 3 Replies
View Related
Sep 15, 2005
I am testing out the ODBC connection manager, as part of my beta testing from my company. I created a new conn manager on the palate (dsn pointing to local sql server). But I am not sure how this one can be used. I can not use any of the Data flow sources which can use this ODBC connection manager. Using the said conn manager in a Datareader source shows up the error "Can not acquire a managed connection from the run-time connection manager".
View 11 Replies
View Related
Feb 23, 2008
I have an application that is using ADO connection strings from multiple clients. One site with about 20 users was recently upgraded to a newer version of my application, and the number of connections to the SQL Server I'm told went from about 20 to spiking over 100 from time to time. There were myriad changes to my app over the course of the last year since their prior version of my app. I need to nail down which version introduced a change in connection pooling (?) and I was wondering what tools I should use to monitor that.
Any ideas?
View 1 Replies
View Related
Oct 1, 2007
What is the correct way to set a linked server pointing to a DB2 database?
I've been trying for hours with "Microsoft OLE DB Provider for ODBC Drivers" [MSDASQL] and "IBM OLE DB Provider for DB2" [DB2OLEDB] and I still cant setup a correct connection.
I already have a ODBC connection pointing succesfully to my DB2 database, but I can't make MSSQL to use it.
Should I created the connection using sp_addlinkedserver OR the "new Linked Server" under "linked Servers" on Management Studio?
Any help is welcome.
[]s
View 2 Replies
View Related
Mar 11, 2004
Has anyone had problems using an OLEDB linked server connection to Sybase ASE 12.5? I'm having major performance problems when I use string criteria in the where clause. Queries that return 1 row and execute in less than a second using a native Sybase connection take 40 seconds to run using the linked server OLEDB connection. If I use ints in the where clause performance is almost exactly the same between native and linked connections.
Any ideas???
View 9 Replies
View Related
Mar 19, 2008
I am trying to establish a OLEDB connection for a db2 server from SSMS adn some one tell me the settings. I haver tried different options but failed.
This is my connection string from SSIS
"Data Source=DB2W;User ID=myid;Provider=IBMDADB2.1;Persist Security Info=True;"
I gave
Linked Server: DB2W
Provider: IBM OLEDB provider for DB2
Product name: ???
DataSource: ???
Provider string:???
Location: ???
View 3 Replies
View Related
Jan 17, 2008
Hi Gurus,
In my code, I need to update the table on the linked server according to the XML input. A table variable is used to store the value from XML.
But the collation of the linked database doesn't match with the default collation used by the table variable, so the table join fails.
I am thinking about to open the connection to the linked server directly, and use the target database. So the table variable collation should be as same as the target database collation.
Is it possible to open the connection to the linked server in c# ?
Thanks and best regards,
Jennifer Zhao
View 5 Replies
View Related
Apr 22, 2008
What is the connection string for access a database of a linked server in sqlserver
Thanks
I add a linked server (another sqlserver server2) to my sqlserver(server1) , I need make a sqlconnection to the linked server connecting my sqlserver (server1)
View 7 Replies
View Related
May 11, 2000
Hi:
No matter how I configure my link going thru EM I get a 7399 or 7303 error. First I tried to link to an Access 2000 db on a Windows 98 station. Next I installed Access 2000 on the test server with SS7, SP 2 (Windows NT 4, SP 5) and still the same errors. Is it possible to set up Access 2000 as a linked server on SS7? Can you give me the brainless man's walk-thru so I can get over this hump?
Thanks in advance,
Gary
View 3 Replies
View Related
Aug 7, 2006
Hello,
I am currently migrating from SQL 7.0 to SQL2005. So far everything is going well except for one thing, my linked server to a Pervasive 8.6 server database. I've done the same configurations as with the SQL 7.0 server to create the linked server. I've created an Pervasive ODBC Client DSN system connection on the server called PervasiveDB pointing to the DataDB catalog, using TCP/IP as transport and the server name "PervasiveDB" as the server address. From this point, on the SQL2005 server, the DSN connection is successfull and I can see the different catalog names of the PervasiveDB server. When I create the Linked server on the SQL2005, I create exactly as done on the SQL7.0 which is :
SQL7.0 server : OS is Windows 2000 all service packs applied for both OS and SQL7.0
SQL2005 server : OS is Windows2003 standard edition all service packs applied for both OS and SQL2005.
Linked server name : TestPervasive
Provider : Microsoft OLE DB Provider for ODBC Drivers
Product name: Pervasive
Data source: PervasiveDB
Location: PervasiveDB
Catalog : DataDB
I get the following error when I try to query the data to test if the linked server I just created is successful:
query : SELECT * FROM testpervasive.DataDB..schedule
error message:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "TestPervasive" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TestPervasive".
Anybody can help me on this one? I would greatly appreciate it !!
View 3 Replies
View Related
Sep 24, 2007
Hi,
I am trying to connect to a sql 2005 SP2 windows auth server through linked server from a sql 2005 SP2 mixed mode. I am using the "use current users credentials" option for authentication. I am getting the error below. Any thoughts to why this is happening would be appreciated.
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
View 4 Replies
View Related
Feb 27, 2007
I€™m trying to resolve an issue and wonder if anybody has ever run into and possibly had a resolution for.
I wrote a custom conflict resolvers to manage data at the column level. What I needed to do to achieve this was to use a remote query to the mobile subscriber that raised the conflict. I€™m trying to put in a rule that if there are any issues connecting to the subscriber I want to use the default conflict resolver and move forward. Here is my issue, it seems there is no way I can test a connection without getting a critical error that I can€™t seem to trap. Example
I was trying to do
Select * from [linkedserver].[database].[dbo].[incident]
If @@error <> 0
use default conflict resolver
What I did to test this was I changed the linked server authentication to an incorrect password to get the following error that there is no way to trap.
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.
Does anybody have an idea how to trap this? In SQL 2005 there is a stored procedure
sp_testlinkedserver to handle this but I need this to work in SQL 2000 also, it seems many people have ran into this issue on the web and I tried to get the one where sqldmo is used to test connection but that will not work since I wont always know the password. I would like to just check the @@error value after trying to run a remote query.
Thank you,
Pauly C
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
Apr 24, 2008
We have a SQL2005 server install running on a Win2003 Server. It has a linked server to iHistorian using the iHOLEDB provider for iHistorian. Randomly and after a few execution to this provider, we start getting errors
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "IhOLEDB.iHistorian" for linked server "fspheafhs1".I created a data source in VS2005 for BI on the SQL server using the iholedb provider. This was successful. However, all queries or OPENQUERY statements to the linked server fail. This seems to continue until SQL Server is restarted. Does anyone know how to reset a provider in SQL Server or tell if the SQL Server is having trouble with the provider? There are no message in the SQL Error Log. Again the provider continues to work outside of SQL Server, but no longer can be accessed from within SQL Server. Thanks in advance.
View 3 Replies
View Related
Sep 5, 2006
We have a stored proc on Server B called:
my_sp_server_b it takes 1 parameter a text field as a parameter, with default set to NULL
this proc calls:
my_sp_server_a through a linked server (which happens to be the same server, different DB), it has two parameters: my_id int, my_text text w/ my_text having a default set to NULL
This second stored procedure just selects back an ID that is passed to it (to keep things simple).
If we pass any string value to my_sp_server_b we get the appropriate hardcoded ID passed to my_sp_server_a. If we pass NULL to my_sp_server_b we get the following error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
If we remove the linked server, and just reference my_sp_server_a via the scoped DB, we do not get an error. If we change the data type in both procs to varchar(50) we do not get an error. If we change the data type to nText we still get an error. If we put IF logic into stored procedure: my_sp_server_b to check for NULL in the input parameter and if it true then to pass NULL explicitly to my_sp_server_a we do not get an error.
It seems to be a combination of using a linked server and trying to pass a text (or nText variable) with a NULL value to stored procedure. Sometimes the error changes based on which scenario I described above - but we consistantly receive an error unless we do some of the workarounds described above.
Any ideas?
View 2 Replies
View Related
Mar 14, 2007
Hello all.
I upgraded a SQL 2000 installation to SQL 2005 this past weekend. Minimal issues overall, but one that did crop up relates to a linked server to an Oracle database. The linked server worked before the upgrade, and stopped working immediately after.
We're running on Windows 2003 x64, latest service pack. The linked server is set up using the MSDAORA provider. The error I'm getting post-upgrade is:
=====================
OLE DB provider "MSDAORA" for linked server "PROD" returned message "ORA-06413: Connection not open.".
Msg 7303, Level 16, State 1, Procedure RDM_GET_REP_LIST_SP, Line 12
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "PROD".
=====================
A tnsping from the server works fine. I've also tried recreating the linked server. Any help would be appreciated. Let me know what other information would be useful.
Thanks,
Adam
View 3 Replies
View Related
Aug 21, 2007
Hello, everyone!
I'm currently having an issue with a linked server. Here's the surrounding information:
A) I have a clustered SQL Server 2005 Instance (A) and a SQL Server 2000 instance (B).
B) There is a linked server on A to B. When I set it up, I did run the fix to ensure A could talk to B (There was an issue with communication between 2005 and 2000 servers). It has been there since I installed A, and has worked fine.. Until last week.
C) This linked server uses static credentials to connect to B.
D) Named Pipes are enabled on both servers to listen to both connections on both A and B in cliconfg. So A has a named pipe listening for B, and B has a named pipe listening for A.
E) A has a view that looks at a table on B - It's a table view, very simple, just pulls in all the data from the table on B.
So, I go to do a select statement from the view, connected as a user other then 'sa'. I then get the error:
Named Pipes Provider: Could not open a connection to SQL Server Linked Server
Error Source: ncli Client (Paraphrasing, didn't copy that down, but it was the ODBC connector)
Well, that's odd. It's been working fine for months now..
I then go and connect as sa on A to query B. It works! Mind you, absolutely NO QUERY from A to B will run again until I run SOME kind of query has been run as sa on A.
Mind you, this is not a credential issue. Every user who connects to the linked server to B uses a stored credential that is DBO to the database on B. The same User Name and Password exists on both A and B.
Also of note, I check the activity monitor. There's a process that is "dormant" every time I run a query against the view. The details of this connection are:
sp_reset_connection;1
My question is, why is it resetting the connection on B when being queried from A? Why is it "all of a sudden" a problem? Are there any changes that coudl ahve been made that would cause this?
Any help with this confusing issue would be appreciated.
Thanks!
View 1 Replies
View Related
Aug 16, 2011
We are running Reporting Services 2008 R2 on a Windows Server 2008 Standard 64-Bit server. I have a user that has full access to Reporting Services at all folder levels but IS NOT a local administrator on the 2008 server.
This user can create data source connections but when he tries to test the connection by clicking on the 'Test Connection' button, he gets the following error "The permissions granted to user <username> are insufficient for performing this operation.A user that has administrator priveleges on the server can test the connection fine.Â
I don't want to make this user an administrator on the server.Â
This is an extract from the log file:
ibrary!ReportServer_0-24!3478!08/16/2011-13:45:37:: Call to TestConnectForDataSourceDefinitionAction().
library!ReportServer_0-24!3478!08/16/2011-13:45:37:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: , Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user <username>
are insufficient for performing this operation.;
[code]....
View 10 Replies
View Related
Dec 28, 2006
Creating a web application. Running a simple query "SELECT username FROM vwCustomer"
vwCustomer is a view built on top of an Access DB which is set up as a linked server. Within SQL Server that view responds immediately. But when I try to access it from an ASP page it takes over 20 seconds to respond.
Used the following to create the linked server:
EXEC sp_addlinkedserver 'DBName', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:databasesdatabase.mdb'
GO
EXEC sp_addlinkedsrvlogin 'DBName', FALSE, NULL, 'Admin', ''
GO
The view is simply(this works fine in SQL Server):
SELECT * FROM DBName.db.dbo.customer
The ASP page:
SELECT * FROM vwCustomer
Can anyone give me a hint as to where the bottleneck is? Or how I can figure that out using SQL Profiler or something?
Please help!
View 3 Replies
View Related
Oct 1, 2015
I have a linked server setup in dev environment which is pointing to ORACLE server of a third-party. I m on  sql server 2008 r2 and  using BIDS 2008 for building an SSIS package which would pull data from oracle and do a look up in SQL server table to get a final  extract. Now the query which pulls data from a single table from linked server is like the one below
select * from [oracle linked server ]..dbo.tablename a
left join  mySQLSERVERtable b
on a.id=b.idÂ
Note:I can execute this query on SSMS as  the linked server is setup -[oracle linked server ] and I have sql  oledb connection manager for dev environment already configured in my ssis package .
My questions are do I need to create a SSIS CONNECTION manager specifically for linked server in my ssis package ?
Are there any best practices which we follow when using linked servers in this context ? I am thinking will the join suffer performance issue and what kind of exceptions I need to write when the linked server connection is broken.
View 4 Replies
View Related
Mar 25, 2002
Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Thanks
View 5 Replies
View Related
Apr 24, 2015
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
   @server = N'Machine123Instance456',
   @srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'Â Â
View 6 Replies
View Related
Jan 5, 2001
We are looking for a software program that will allow us to test our SQL server. We are looking for the most thorough tool that is available. I was asked to post a message, so I do not have too many details.
Thanks!
Jeff
View 1 Replies
View Related
Mar 31, 2003
My previous experience is with php and MySQL.
I currently have Apache, MySQL and php running on my local machine to enable me to test php code on localhost
I've been asked by a colleague if I'll work on an SQL 2000 database they're having trouble with. They said they will provide Visual.net, SQL 2000 and any other software I need.
I'm completely in the dark on this, but have some starting questions:
To test my code, will I need to set up a sever on localhost?
Will I need other software than Visual.net and SQL 2000? I'm using DW MX for my web stuff.
Whre can I find a basic introduction to ASP / SQL 2000 in the form of a tutorial?
How fast does this combination run? The database has 25,000 rows and 6 columns. What would be a reasonal expect time for a result?
Any help appreciated
H
View 3 Replies
View Related
Jun 30, 2006
Is there a way to test to see if there is a connection to a database in SQL through programming or dos commands?
View 4 Replies
View Related
Jan 28, 2005
What do you recommend for stress-testing the performance of key stored procedures (they have been identified) for our application? The parameters can be programatically selected, for example:
Select 'exec my_proc @id = ' + Cast(id As varchar)
From myTable
Where foo = 'bar'
I have the Support Tools Available For Stress Testing & Performance Analysis (http://www.microsoft.com/downloads/details.aspx?FamilyId=5691AB53-893A-4AAF-B4A6-9A8BB9669A8B&displaylang=en) from Microsoft's site and they are pretty good.
Recommendations appreciated, and thanks in advance!
View 2 Replies
View Related
Jul 18, 2006
Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
View 4 Replies
View Related
Feb 23, 2015
I Run All checks for Validation cluster.I get Error On Disk Lists And Validation failed.With This error : Failed to prepare storage for testing on node "server name" The security account manager (SAM) or local security authority (LSA) server was in the wrong state to perform the security operation.
View 2 Replies
View Related
Jan 9, 2004
I'm going to be starting a web application this week. I have quite a bit of experience with Access Databases, and I've also used MySQL (back in my PHP days, thank GOD for ASP.NET).
The server we use to host our websites allows us to use SQL Server databases, so I want to move in that direction, but I'm not really sure where to start.
If I download MSDE, and start a database with it on a site I will be developing locally (on my XP box), will it be difficult for me to transfer the database (or layout of the database) to a production server running SQL Server. I know they're basically the same database, but I'm just afraid to start with an MSDE database and then get the site nearly complete, and then not know how to transfer it to a SQL Server DB.
I'm particularly interested to know if the majority of the database creation will be done through queries (similar to MySQL) because I figure if it is, then I can use the same queries to create the database on both the local and production server.
I just want to make sure that downloading MSDE will be a step in the right direction.
Perhaps links or tutorials or some information on things to watch out for as I take on this process would be so greatly appreciated.
Thanks very much.
View 2 Replies
View Related
Apr 7, 2004
Hi Group,
I'm trying to test an ASP application in 'isolation' and seem to be having problems with connecting to the Server.
The setup is EXACTLY like the production environment except for the fact that I am using the Beta version of SQL.
The error refers to SQL Server not existing. I get this error also if I try to open the Query Analyzer without FIRST starting the Services via the Service Manager - so I think it is probably something wrong with the Beta Installation rather than using the BETA version as a back-end.
I've tried using the Computer name as the Server to connect to as well as 127.0.0.0 - neither seem to want to connect.
I can connect manually and see the Database objects. I've even connected, left the connection open and tried running the application again. I still get the same error.
Has anyone experienced this before? i.e not being able to connect to a BETA version of SQL to test an ASP application locally.
TIA
View 3 Replies
View Related
Apr 7, 2004
Hi,
I'm new to the whole MS SQL server thing, have been using Access in past developments via the development platform in Dreamweaver.
Now I'm making the leap to MS SQL and still want to use Dreamweaver to handle some of the development aspects, does anyone know how or what i would need to do to have a direct connection with the server in dreamweaver?
I've setup an ODBC connection with my hosting providers already.
THANKS!
View 3 Replies
View Related
