Setting Up SQL 2005 Linked Server Connecting To A DB2 Database
Oct 21, 2006
Hi,
I have setup a linked server to a DB2 database using "Microsoft OLE DB Provider for ODBC Drivers",
the connections works fine i am able to run select queries.
but the number of rows returned from the two following statements are different.
select * from linkedserver...tablename
select count(*) from linkedserver...tablename
e.g. The First statement returns 116 rows, whereas the second query returns count as 144....
I am a bit confused ?????
could anybody think of any possible reasons..
Cheers
ARAW012
View 4 Replies
ADVERTISEMENT
May 2, 2006
Hi there!
My provider does not yet support SQL Server 2005, they are still on SQL Server 2000. I put together a very basic login page, but it is crashing everytime I access it because of this problem, and sadly I am not advanced enough as yet to know how to remedy it! Can you help?
Here is the error page, some of it; - Any ideas gratefully received!
Russ.
Server Error in '/' Application.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
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.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
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
Jun 29, 2007
Hi,
I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.
Thank's for all the help/clues you can give me.
View 4 Replies
View Related
Mar 19, 2006
I have deployed a web server using server 2003 standard edition and have created a functional web site... Now I want to create a SQL data base and make a DB connection using ASP.NET using Dreamweaver as my HTML editor... This is where I get totally lost and confused...I have SQL 2005 instralled on my web server and have gone through the help files and some of the tutes but am still spinning my wheels... Can anyone please help this wanna be MSDN...
I need help configuring my sql server and then connecting to it via dreamweaver and then creating a data base for data collection from my web site...
Any help here would be awesome!
Dadeo
View 1 Replies
View Related
Nov 28, 2007
Hello everyone, I am fairly new to SQL Server Express and I have a question I was hoping someone could help with. I know this will sound very wacky but please entertain the possibility of such scenario taking place...
Is it possible to connect to an instance of an SQL Server Express 2005, using asp, without having a database created and without configuring anything such as permits or users first ?
The SQL Server Express was installed on a PC that runs a web server as well but nothing else was done, I only have the instance name and I know the SQL Server is configured for local access only.
I was hoping to create a database and begin using it with an asp file that runs SQL commands I can input on a text window, but I can't find connection strings that work without specifying a database name, and also I don't know which user and password to use because it is set to windows authentication.
I thought the code would go something like this:
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Driver={SQL Server};Server=SQLinstance;Address=localhost,port;Database=;Uid=;Pwd="
To top things out the web server is configured to send out a generic error message, so I can't see what the error I'm getting actually is...
Any thoughts ?
Thanks.
View 2 Replies
View Related
Jan 30, 2006
Environment:SQL Server 2005 Enterprise in Win2k3 Enterprise on 32(x86) bit machine
I am able to enable the Trace Flag 1400 using DBCC TRACEON (1400) GO. Only then we can able to configure for the ENDPOINTS.
I successfully configured the ENDPOINTS for both the principal server and Mirror server .And then when I started the START MIRRORING.
I got the error message. Please find the Message below.
An error occured while starting mirroring.
additional Information:
:..>Alter failed for Database 'KQDB', (Microsoft SqlServer.Smo)
:..>An exception occured while executing a Transact-SQL statement or batch
[Microsoft SqlServer.Connection.info]
:..>Database mirroring Transport is disabled in the endpoint configuration.
[Microsft SQL Server,Error: 1486]
If anyone have a solution on how to setup a database mirroring environment.Please send.
View 10 Replies
View Related
Oct 4, 2006
Hey All,
I am having a problem connecting to a database I have with a java app I am creating for my senior project. I am using the latest MS JDBC for connecting to the SQL Server.
Below is the code I am trying to run:
try {
ds.setIntegratedSecurity(true);
ds.setServerName("localhost\SQLEXPRESS");
ds.setDatabaseName("*databasename*");
con =ds.getConnection();
} catch (Exception ex) {
ex.printStackTrace();
}
The ds and con variables are declared and initialized correctly. The error I am getting now is this:
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "*databasename*" requested by the login. The login failed.
Any suggestions on what I could be doing wrong?
View 4 Replies
View Related
Jan 23, 2008
Set up linked server for windows user.please explain step by step
View 9 Replies
View Related
Jun 26, 2006
Is this possible to connect do the Database Engine (on sql server 2005 on XP platorm - file *.mdf (not mobile *.sdf)) from win CE 5.0?
I tried to do this :
ConnectionStringSQLServerCE = "Data Source=WORK_STATION\SQLEXPRESS;Initial Catalog=dbMachines;Integrated Security=False;Password=Panel;User ID=Panel";
SqlCeConnectionCE = new SqlConnection(ConnectionStringSQLServerCE);
SqlCeConnectionCE.Open();
but I catch error:
catch (PlatformNotSupportedException ex)
€žPlatformNotSupportedException€?
I noticed that I see server because when I use:
ConnectionStringSQLServerCE = "Data Source=WORK_STATION\SQLEXPRESS";
zwraca błąd typu
catch (SqlException ex)
€śLogin failed for user ''. The user is not associated with a trusted SQL Server connection.€?
I used
using System.Data.SqlClient;
from Compact Framework 2.0 under Visual Studio 2005 Device Application Windows CE 5.0
can anyone help me?
View 1 Replies
View Related
Nov 3, 2006
I want to use SQL Server to query an Access Database with about 40,000 rows of data. If possible, I don't want to upsize the database because others need acess to it in the ACC97 format. Is there a way to use ODBC to connect to the ACC97 database so that I can use the SQL query capability of SQL server to query the database.
I know access allows you to write some SQL queries but I need the power of the SQL server and now it is a matter of curiosity because I've been searching for this answer for about 8 hours.
View 1 Replies
View Related
Mar 18, 2004
I am trying to set up a linked server in MSSQL to an IBM AS400 using the IBM AS400 OLE DB Provider. The New Linked Server dialog box asks for properties Product Name and Data Source. I would like to know from someone who has successfully set up a linked server to an AS400 what the specific syntax in the properties needs to be. I have reviewed the OLB section on sp_addlinkedserver, and while this is helpful and gives many specific examples, I need an example for an AS400. Thank you to anyone who can help!
View 1 Replies
View Related
Mar 18, 2008
I have a very basic console application whose sole purpose is to query a database on the same local computer. When i run the application, i get the error message below:
Cannot open user default database. Login failed.Login failed for user 'someDomainSomeUserName'.
Below is my connection string also
myconnection_string = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=C:Reporting SystemApp_Datasafetydata.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True").
After some googling, some post pointed to deleting a user folder at :
C:Documents and SettingsmyusernameLocal SettingsApplication DataMicrosoftMicrosoft SQL Server DataSQLEXPRESS
but still deleting that folder and restarting the machine doesn't help and i also have remote connections enabled. Help is really needed.
NOTE: The same application runs on a different computer with XP but fails on the Pc with windows server 2003.
View 3 Replies
View Related
Jul 2, 2001
Does anyone know where I can get some good examples of how to set up a linked
server to an Oracle data source in SQL 2000? I can't seem to get a link
set up to my Oracle database. In particular I am wondering how the communication works between OLEDB to the Oracle Instance and how to trouble shoot issues (for example, would is the Oracle Listener what OLEDB is talking to?) Thanks!!
View 3 Replies
View Related
May 8, 2006
I have been unable to get my linked server to work when running queries against it in SQL Server 2005.
Example SQL Statement on my SQL 2005 server that I'm trying to run:
select top 100 * from [linkedservername].databsename.dbo.tablename
Error Message:
Msg 7416, Level 16, State 1, Line 1
Access to the remote server is denied because no login-mapping exists.
Print screen of the security properties of my SQL 2005 Linked Server:
http://www.webfound.net/linkedserver.jpg
The user sqladmin definitely has the right permissions to the right databases on the target linked server.
View 1 Replies
View Related
Oct 15, 2007
hi,
i am using sql 2k5 . i want to connect with linked server using IP address . but its generating error that
"
Could not find server '50.1.254.4' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers."
but when i am using name of linked sql server its generating output. can any body help me that what is wrong with my first scenario.
i am giving my both query.
1 using name of linked server
select * from [REPORTSRVRSQL2005].GLobalDB.dbo.tblMCustomer
its working
2 using ip of linked server
select * from [50.1.254.4].GLobalDB.dbo.tblMCustomer
not working
thanks in advance
Ganesh Gorla
View 4 Replies
View Related
Sep 15, 2011
I have a requirement to set up an Oracle linked server, Ive never done this before and in fact never touched Oracle so I dont really know what I am doing.
View 4 Replies
View Related
Sep 20, 2006
Has anyone managed to set up an IBM Universe database environment as a linked server to MS SQL 2005?
If so please convey how you got it done.
Thanks,
View 22 Replies
View Related
Nov 7, 2007
The following installation:
- Microsoft SQL Server 2005 Standard Edition running on Microsoft Windows Server 2003 Standard Edition
- Client Service for Netware installed.
- Access 2000 database in a Novell Directory Services (NDS) Environment
- SQL Server service running under local Administrator account.
Linked server configured as follow:
Linked server: MyDB
Provider: Microsoft Jet 4.0 OLE DB Provider
Product name: Access 2000
Data Source: UNC path to my Access database
Security: Admin with no password (default Access behaviour)
I can connect to my access database by following the next steps:
- Open Management studio on the SQL Server itself (where I'm logged in as Administrator)
- Connect using Windows Authentication (Administrator)
- Create a new query, run EXEC sp_tables_ex 'MyDB'
This will return all the tables from the Access database.
I can also make a view pointing to MyDB...MyTable.
Enough prove to me It can work....
So far so good
But now the tricky part.
When I login as 'sa', I can't connect and will receive the following message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyDB" returned message "'Full UNC to my access database' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyDB".
(In real life I want to connect to my SQL 2005 Server from a ASP.NET (2.0) application using SQL authentication, but as long as I can't get the above working this is not important for now)
View 3 Replies
View Related
Mar 7, 2007
hi,
i've currently created a linked server using sql server management studio express (it connects to an oracle database to draw data), i can query it all fine using the query analyzer with sql server etc.
how can i start using the linked server data with visual studio 2005? i can't seem to find the linked server anywhere inside the server explorer, am i suppose to import it / connect to it any specific way so i can start using the data retreived from the linked server?
thanks in advance!
View 11 Replies
View Related
Jul 20, 2005
I'm currently trying to access data from a Cache DB using MSSQL. I havelinked the Cache server through an ODBC connection. I can see in the LinkedServerexpansion all the tables in Cache for the File(?) I want to access.Here is my problem: Normally to access a linked sever I would do thefollowing:select *from ServerName.DatabaseName.dbo.TableNameI have triedselect *from ServerName.DSN.dbo.TableNameselect *from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)None have worked (error no such object...)What is the syntax to select data on a linked sever via an ODBC connection?In the Linked server set up I have also check the RPC and RPC Out options...My ultimate goal is join tables in Cache and MSSQL into MSSQL.Any help would be greatly appreciated!Thanks,-p
View 2 Replies
View Related
May 27, 2014
As a database developer, I have so many databases that I "own" scattered across various servers that it''s getting difficult to remember where all of my databases reside. It doesn't work that the DBAs have taken to some very hard to remember server naming conventions.
I was going to create aliases via the configuration manager, but it turns out the DBAs overwrite my entries each night with THEIR aliases and they won't add any for my use.
So I decided to simply add some records to my host file so that instead of having to connect to "SERVER-AD_DADF-DAFDASS" I can just use "CustomerA".
This solution seems to work until I tried to access a linked server. If I connect to the main server via it's actual name, I can hit the remote/linked server with no issue. However, if I connect to the main server using it's alias, connection to the remote/linked server fails with: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
The linked server is set up to use current security context and I'm not logging into the alias any differently that I do when using the actual server name.
View 9 Replies
View Related
Sep 28, 2015
I have a scenario where I want to make a linked server query and report using windows service account credential. I can able to do link query if I RDP into the Server where linked server established using the service account and run query successfully  but local client SSMS with my credential fails connecting linked server or querying. Looks to be a sql double hoping problem if so configuration each client domain account to enable delegation will be challenging as mentioned in the following articles instead service account only might work if possible.
View 3 Replies
View Related
Aug 19, 2015
I want to populate data in SQL server database (SQL server 2008 R2) from SSAS cube located on different server (SQL server 2012). My SQL database present on Server A and cube on Server B.
To implement I am trying to create linked server. But its giving errors related to provider and product.
View 3 Replies
View Related
Oct 18, 2007
I am using SQL 2000 (Standard Edition, SP4) and have created a linked server to access a Access MDB file. When I run a simple query against that server, I get the error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: System resource exceeded.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' ICommandText::Execute returned 0x80004005: ].
I am able to connect to this same Access MDB file from another SQL server (SQL 2000 Enterprise Edition, SP4). I have compared the two servers and they both have the same version of MDAC (2.8) and the same version of the Jet OLE DB Provider.
Any idea why this would work from one server and not from the other. Also, I was able to perform the same queries from the first server a week back.
Thanks in advance for you help.
Amir
View 2 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
Feb 14, 2008
Hi all,
In the VB 2005 Express, I can get the SqlConnection and ConnectionString of a Database "shcDB" in the Object Explorer of SQL Server Management Studio Express (SSMSE) by the following set of code:
///--CallshcSpAdoNetVB2005.vb--////
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
.......................................
etc.
///////////////////////////////////////////////////////
If the Database "shcDB" and the Stored Procedure "sp_inertNewRecord" are in the Database Explorer of VB 2005 Express, I plan to use "Data Source=local" in the following code statements to get the SqlConnection and ConnectionString:
.........................
........................
Dim connectionString As String = "Data Source=local;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
........................
etc.
Is the "Data Source=local" statement right for this case? If not, what is the right code statement for my case?
Please help and advise.
Thanks,
Scott Chang
View 6 Replies
View Related
Mar 28, 2006
Its not a big deal but enoying enought that i always have to
USE MyDatabase
-- Instead of having it set as the defualt and not adventureworks
View 3 Replies
View Related
Feb 15, 2006
I've mapped a sqlserver machine within vs2005, but when I attempt to set the database connection, I dont see place to stick the port number (even in the advanced area)... it continues to fail when I test the connection.. I can telnet into the machine on the sql server port, so I know its open to me...
Also - when I publish this to an IIS server, what is the syntax for placing the connection and port number in web.config? I've tried various settings, cant get it to work. <appSettings><add key="con" value="Server=12.123.123.123;PORT=1234;UID=*****;PWD=*****;database=mydb" /></appSettings>
Thanks in advance for the help,
Todd
View 3 Replies
View Related
Mar 3, 2004
Hi all
I am having serious problems with connecting to SQL Server via ASP.
I have created a User DSN, however when I am asked if I want to use Windows Authentication or SQL Server authentication, I feel I am having problems. If I choose SQLServer authentication, I am asked to enter a username and password which I do (does this need to be a predefined username and password?). I do this and the connection fails with an error saying that the login is not associated with SQLServer. If I choose windows authentication its fine, but when I try to connect via asp using the username and password I login to get into the machine and the DSN name I set up during the DSN setup, the same error comes up. (for windows authentication does the asp code differ from SQL Server authentication?). I really need help here
View 1 Replies
View Related
Oct 26, 2006
Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
View 1 Replies
View Related
Oct 1, 2007
Hi,
I need to create a MSRS 2005 report which needs to connect to differnt databases at runtime.
1. Can we create a report that connects to different databases at runtime and generate a report.
2. Is there any other way to implement the same using .NET 2.0.
Thanks in advance.
Regards,
Rajeev
View 5 Replies
View Related
Mar 11, 2008
Hi
I initially installed VS 2008 with no problems. It installed SQL EXPRESS. I then installed SQL Developer edition. Still No problem with connecting or using the database. I picked up problems setting up Reporting services. I could not configure the server. I then decided to unistall SQL and re-install the developer edition. Now, I cannot even connect to the server.
Akbar
View 7 Replies
View Related
