Linked Server SQL2000 SP
Feb 16, 2005
Brand new to linked servers and trying some stuff. I've successfully linked a server using EM (both are sql2000). I can successfully run a select statement through QA. I'm trying to test a locally-stored procedure and am having a bit of trouble. Please, no arguements about best method - I have no choice. Cannot create stored procs on remote machine.
If I run this through QA, I get an immediate return set: select * from linkedName.dbName.dbo.tblName
If I try to create a test stored proc like the following, I get: Error 7405: Heterogeneous queries require the ANSI_Nulls and ANSI_WARNINGS options to be set for the connection. This ensure consistent query semantics. Enable these options and thenreissue your query. When I looked it up in BOL, I thought I was setting these items?
CREATE PROCEDURE zp_countyListing AS
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
select * from linkedName.db.dbo.tblName
Thanks for the assistance.
View 1 Replies
ADVERTISEMENT
Mar 9, 2004
I’ve got a linked server created in SQL2000 to DB2.
I’m using Client Access ODBC Drivers, and Microsoft OLE DB provider for ODBC Drivers.
I have views created against the linked server as
ALTER VIEW BLBATDT AS
SELECT * FROM LURCH_PARADB.S102D4LM.PARADB.BLBATDT
All seem(ED) to be working fine until I ran into this issue.
Using the view
If I select count(*) from BLBATDT returned value 786
If I select * from BLBATDT I get 76 rows returned
If I select from BLBATDT where field = value I get nothing, when in fact there is a value I specified.
I modified the view creation to
Create view as
select * from openquery(LURCH_PARADB,
'select *
from S102D4LM.PARADB.BLHDR')
Performed the above simple selects, (same results)
I modified the view creation to
Create view as
select * from openquery(LURCH_PARADB,
'select field_list_of_all_fields_in_the_table
from S102D4LM.PARADB.BLHDR')
Same results
However if I modified the view creation to
Create view as
select * from openquery(LURCH_PARADB,
'select field1, field2
from S102D4LM.PARADB.BLHDR')
And limit the fields selected to a small subset, everything works fine.
I think the issue may be that the parent tables are very wide in total byte count and the number of fields is also large.
This particular tables has 120 fields with a total record length of over a 1000 bytes.
If this is the case, how do I resolve this?
Has anyone ran into this before?
View 7 Replies
View Related
Jul 20, 2005
hello,I'm trying to create a linked server from an SQL2000 to a Unify ELS(very old, odbc is version 1) database on SCO unix.The odbc driver is old but it works fine when used by applications forcreating reports.What I want to do is make a linked server from the SCO box to the win2kbox. So, I'm using enterprise manager to create the linked server.The DSN of the unify database is CORE_OFFICE1I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.Datasource name not found and no default driver specified.When I create the linked server I chose the OLE DB for ODBC drivers.I put 'CORE_OFFICE1' for the Data Source name. I also setup thesecurity with the name of a unix account for login/password.What else should I have?I'm new at this linked server stuff!Product Name:Data Source:Provider String:Location:Catalog:Thanks,Oskar
View 4 Replies
View Related
Jan 7, 2007
Linked server from SQL2005 to SQL2000
Hi
I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the tables under it so could not query anything.
----
USE [master]
EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServerNameInstanceName', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'rpc',@optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'RemoteServerNameInstanceName', @locallogin = NULL , @useself = N'False', @rmtuser = N'remoteUser', @rmtpassword = N'remotePassword'
View 5 Replies
View Related
Jul 20, 2005
Hi All,I am close before throwing the machine out of the window.I tried to establish a linked server on our SQL2000 server pointing to a DB.EXEC sp_addlinkedserver@server = 'DB2',@srvproduct = 'IBM OLE DB Provider for DB2 Servers',@provider = 'IBMDADB2',@provstr='DSN=JDE_PROD;UID=XXXX;PWD=XXX;DATABASE=J DE_PROD;'EXEC sp_addlinkedsrvlogin 'DB2', false, NULL, 'XXX', 'XXXX'If I query the LS using query analyzer or the Enterprise Manager both startto hang, never responding again.To be honest I am quite new to this topic but I read a lot and also tried alot ;)Some articles talk about SNA Servers to be installed first, others aboutsome client tools.I am confused by now...Could anybody please be so kind and tell me how to connect the DB2 as alinked server?Is my statement wrong, and if so how should it look like? Why does theapplication start to hang? Do I really need to install additional softwareto do so?Our DB2 expert also taked about software necessary to access the DB2mainframe. Any ideas?Thanks a lot in advance and belive me, any hints are highly appreciated ;)Seize the DayAndyAndreas BretlJoin Bytes!
View 4 Replies
View Related
Aug 14, 2007
I am runing Windows 2003 which has both SQL2000 and SQL2005.
The following works in 2000 but not 20005
Code Snippet
DECLARE @sql nvarchar(4000)
set @sql = 'sp_addlinkedserver @server = N''dbAccessPO'',
@provider = N''Microsoft.Jet.OLEDB.4.0'',
@srvproduct = N''OLE DB Provider for Jet'',
@datasrc = N''C:Temppopts.mdb'''
exec sp_executesql @sql
set @sql = 'sp_addlinkedsrvlogin ''dbAccessPO'', FALSE, ''sa'', ''Admin'', NULL'
exec sp_executesql @sql
Running this:
Code Snippetselect * from dbAccessPO...MyTable
Produces this error:
Code Snippet
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "dbAccessPO" returned message "Unspecified error".
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 "dbAccessPO".
I login using sa for both instances.
I could really use your help.
Thanks.
View 2 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
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
Sep 17, 2006
i have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?
View 1 Replies
View Related
Oct 12, 2006
Hi,I was hoping someone may be able to shed some light on a SQL2000 connection problem.I have an ASP.NET(1.1) application that connects to a SQL2000 instance on the localmachine, the connection details are as follows: Connection Code :try
{//connect to the provided datasource
using(SqlConnection connection = new SqlConnection(strConnectionInfo))
{
strStatus = "Pulling Data";
SqlDataAdapter sqlA = new SqlDataAdapter("SELECT * FROM NavBlocks",connection);
sqlA.Fill(dsNav.NavBlocks);
SqlDataAdapter sqlB = new SqlDataAdapter("SELECT * FROM NavItems",connection);
sqlB.Fill(dsNav.NavItems);
bNavLoaded = true;//prevent further calls
}
}The connection string is as follows: "Data Source=BOBBITSQL2000;Initial Catalog=pinnacleCMS;User Id=myUser;Password=myPass;"Now this conection has never been a problem, the intial catalog exists, the user exists and has permissions, further to this this users connection details can be used to succesfully access the database from a dsn on this machine. The server explorer finds the database fine, Visio finds the databse fine.The problem only existed after I reverse engineered the database into visio (the database was originally developed using Visio), then dropped all of the tables using Enterprise Manager, and re generated the database from within Visio (ddl script) following a few minor changes.Since the re generation I get the server not found error message from the ASP.Net application.I have tried using different network libraries, logging in using the sa account etc, it makes no difference the ASP.Net application cannot see the SQL server instance.Any ideas would be greatly appreciated.p.s. I have tried dropping the firewall with no success, no updates have been installed.Thanks
View 7 Replies
View Related
Feb 13, 2007
I recently was moved into an applications developers position here at the lawfirm I work at.
Thusfar, its been alot of straight ASP, SQL2000, and Access.
I know the need for me to know .Net is coming, and Ive decided to start playing with ASP.net so I can look a bit proactive..
Ive looked over what I need to get started, in liu of Visual Studio I know of the VS Express options. However, I currently have SQL Server 2000 installed locally on my machine along with IIS 5.1. All the VS Express information Ive found keeps referencing SQL2005 express. Can I get away with using what I already have with 2000? Or if not, can SQL2005 express be run alongside with SQL2000?
Any assistance would be greatly appreciated.
Brian
View 2 Replies
View Related
May 24, 2008
SQL Server 2005 is better than SQL2000 ..How??..Which feature is available in SQL2005 but not in SQL2000???
View 2 Replies
View Related
Nov 14, 2002
Hi,
I'am running Sql2000 Enterprise edition
on Windows 2000
When I re-name the Server for example
from TST_SVR To TST_SVR1, Sqlserver comes backup fine with the new name (TST_SVR1) , BUT I'am still able to connect to the SqlServer even with the old Server name (TSR_SVR). I do not want to connect using the old server name.
I have dropped and added server using
Sp_addserver and Sp_Dropserver, My
@@servername command returns the new server name
Any help in this matter is really appreciated
Thanks
Sanjeev
View 2 Replies
View Related
Jan 16, 2004
I have Windows server 2003 installed, and Visual Studio .NET 2003 ent.arch also. I downloaded the msde sp3 from microsoft's site. installed it. The problem occured when i rebooted my machine. The system tray icon of SQL Server Service Manager says "Not connected". When i open the manager, there are no Services nither Servers listed in the dropdown lists. Can you help with this. Thx in advance!
View 2 Replies
View Related
Apr 3, 2007
Our SQL Developer asked for a new server with a separate small hard disk for the Transaction Log alone to reside on, to increase performance. This will be hard to do, since the servers we have been looking at are low-profile rackmount, and only hold 2 SATA disks. I hate to waste our only expansion bay on a small HD. Is this really something important, or will a Quad-core processor and plenty of RAM make the performance difference negligible? We have 32-bit SQL2000 licensed per processor, and our database is only about 26GB. I was hoping to get 1 large disk and partition it into a 20GB OS partition, and the rest would be for SQL. Am I totally on the wrong track?
My 2nd question is about RAM - if we get 4GB of RAM, will it decrease the performance if we get 64-bit O/S pre-installed instead of 32-bit? I know 64-bit O/S *can use* more RAM than 4GB, but does it *need* more RAM for the same level of performance that we have now? (We are planning to expand that to at least 8-12GB whenever we upgrade to 64-bit SQL2005, but the budget does not allow it just yet.)
Thanks!
View 3 Replies
View Related
Sep 13, 2006
Hi,
I want to install SQL 2000 Server on Win-2003 STD Server and transfer the database that has been running on SQL-2000/Win 2K server.
Win-2003 Server has Apache services and TrendMicro AV Server running. After installation the SQL service starts automatically. But I am not able to see the database from Enterprise manager. It gives error 'A connection could not be established to <local>. Reason: Login failed for DOMAIN/USER. I am logged into Win2003 server as 'Administrator' for installation. SQL Server authentication in Mixed Mode and connect using Domain user account.
While applying update(SP3) also the same error comes after trying to validate password and the Sql service automatically stops. I tried using both Win pwd and SQL sa pwd. Both are giving same error.
How I can go abt it?
Shall be grateful for a reply,
View 5 Replies
View Related
Nov 3, 2006
I can restore databases one by one, setting .mdf and .ldf destination paths.
How can I restore all my Databases at the same time?
Thanks
G. Zanghi
View 7 Replies
View Related
Nov 13, 2000
I have setup SQL2000 Personal edition on my notebook.
I cannot connect to an existing SQL7 server on the network anymore. I
used to run SQL7 Desktop edition which ran just fine.
I also noticed that when I start my machine it take about 2 minutes
until the SQL service is started.
Any ideas why this is not working anymore?
Thomas Schoch
Elephant Software AG
Switzerland
View 1 Replies
View Related
Oct 24, 2002
Anyone know where I can find some documentation on how to set up a 2 node cluster? Thanks
View 5 Replies
View Related
Jan 17, 2002
Hi
What connection string should i use to connect to a remote SQL2000 server over the internet??
Thanks
View 2 Replies
View Related
Sep 6, 2001
I use 'select top 10 customerID, customerName from customers' on one
testing maching 'Test1', it works.
But after restore the database from 'Test1' to 'Test2', the same sql
select top statement does not work. Both machine are SQL2k.
appreciate your help!
-D
View 2 Replies
View Related
Jan 2, 2007
Hi,
I'd like to host a small website created using VS2005 EE with a company that offers just SQL2000 DB support.
My question, can i use the DB created by VS site manager as a DB to the site, if not, is there any alternatives.
Thanks.
View 1 Replies
View Related
Jul 20, 2005
How to restore a database dump file (.BAK) that backed up from SQL2000 server to a SQL 7.0 server?Please help.Saiyou
View 1 Replies
View Related
Dec 18, 2007
My company wants to combine our SQL2000 and SQL2005 severs onto one server. If I install SQL2000 and 2005 on one server and migrate the data bases will I have any performance issues? Is there any issues or conflicts known by putting them both on the same server?
View 4 Replies
View Related
Jul 2, 2007
I have the follwoing scenario, where the query returns an error, when we tried to upgrade oru production SQL2000 enviroment to SQL2005.
SQL2000 Env:
Microsoft SQL Server Developer Edition
Microsoft Windows NT 5.2 (3790)
8.00.2175 SP4
SQL2005 Env:
Microsoft SQL Server Developer Edition
Microsoft Windows NT 5.2 (3790)
9.00.3054.00
Notice the removal of the two RTRIMs.
Any help welcome
Gertus
Current Connection was on the SQL2005 machine:
Remote Link Server SQL2000 SP4
Working:
SELECT * FROM Entity -- on SQL2005
WHERE
(EntityRef) + Source NOT IN
(SELECT (ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY) --this is on sql2000
AND Source = 'EU'
Not-working:
SELECT * FROM Entity-- on SQL2005
WHERE
RTRIM(EntityRef) + Source NOT IN
(SELECT RTRIM(ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY)
AND Source = 'EU'
Error :: >>
OLE DB provider "SQLNCLI" for linked server "MRIEU" returned message "Cannot create new connection because in manual or distributed transaction mode.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT TOP 1 1 FROM "MRIOB"."dbo"."ENTITY" "Tbl1005"" against OLE DB provider "SQLNCLI" for linked server "MRIEU".
View 2 Replies
View Related
May 29, 2006
Hello.
I will construct client application with MSDE.
I face with a difficult question.
It's in sync with SQL2000 Server and MSDE.
Client number is 5000.
I fall in a dilemma. Which do i use, Replication or to make DB Sync application ?
jongwoo
View 1 Replies
View Related
Oct 10, 2007
Hi,
I installed vs2005 & then SQL2000 but I canot load my SQL2000 instance into the explorer. I try to 'add a server' by putting in the computer name, but I can't get it. I also have installed sql2005 express & compact edition installed but I cannot log onto express, only comact.
when I try to create a DB in vs2005, I get an error. I have set express to accept remote connections for tcpip but that didn't help
any ideas? Do i have to install VS2005 after installing sql2000? Should I remove express?
Thanks very much for any ideas
View 8 Replies
View Related
Sep 29, 2006
i realise you can have both SQL2000 & SQL2005 Client software co-existing on one (developement) machine.
but i cant seem to find a definitive answer on whether i can install both complete databases on a server??
we are currently building a new development server and would like to install both version on it if possible??
Cheers,
Craig
View 1 Replies
View Related
May 13, 2008
i am in the process of Migrating SQL 2000 to my new SQL2000 server i want to know the what would the best way for me to migrate one SQL server to another SQL server on the same network and rename the new server to the old server and bring it up for use in our ecommerce website.
View 10 Replies
View Related
May 6, 2002
My company recently purchase a NetApp FS870 and we want to move the SQL data files for out production DB. The NetApp is a NAS. Without loggining in to the SQL Server and establishing a drive mapping.
Does anyone know how to connect SQL2K to the remote machine?
We would prefer to use UNC naming to access the share, as leaving servers logged in and open with a mapped drive is a huge secutrity hole, and presents a reliability issue too.
Thanks
Greg
View 1 Replies
View Related
Oct 20, 2006
Are there any tips/techniques/issues when doing a begin tran and commit between a sql 2000 server/db and a sql 2005 server/db. Should you still use "set xact_abort on"? This will be a recordset of about 1-2000
View 1 Replies
View Related
May 30, 2001
Is there a way I can load a SQL2000 DB backup onto a SQL 7 server? Thanks.
View 1 Replies
View Related
