How do I connect to a named instance of a linked SQL server. If its not linked, I would do the following: ServerName.DatabaseName.dbo.TableName If it is linked, I would assume the following: ServerName\InstanceName.DatabaseName.dbo.TableName but SQL doesn't like the "\" What is the correct syntax ...
We are using a SQL Native Client to connect to a local SQL Server 2005 from a Boarland application. It will fine for selects, inserts, and updates. When we delete we get the error: Could not find server 'SERVERNAME\SQLEXPRESS' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored ...
I'm trying to pull in data from a remote SQL Server. I can access the remote server using SQL authentication; I haven't had any luck using the same credentials with sp_addlinkedserver. I'm trying something like this: Exec sp_dropserver 'Remote', 'droplogins' go EXEC sp_addlinkedserver @server='Remote', @srvproduct='', @pr...
I'm having an issue with a trigger that inserts a value into the same tabled on a linked server. Here is the trigger in question ... USE [localDB] GO /****** Object: Trigger [dbo].[INS_New_Row] Script Date: 05/26/2009 10:50:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -...
I'm able to execute a stored procedure which queries a Linked Server directly, but when I call the same sproc though ASP.NET then I get the following error: Access to the remote server is denied because no login-mapping exists. When I execute it through SSMS, I am connected through my Windows domain account. The site is ASP.NET ...
I wrote a trigger that updates local table and similar table on linked server. CREATE TRIGGER myTtableUpdate ON myTable AFTER UPDATE AS IF (COLUMNS_UPDATED() > 0) BEGIN DECLARE @retval int; BEGIN TRY EXEC @retval = sys.sp_testlinkedserver N'my_linked_server'; END TRY BEGIN CATCH SET @retval = sign(@@error); END CAT...
Trying to figure out why there is such a significant difference between select * from linkedserver..tablename and select * from openquery(linkedserver, select * from tablename). 4 minutes vs 13 seconds. ...
I have a DBF file on a network share that I'm trying to select into a SQL Server table. This query: SELECT * FROM OPENQUERY(MyLinkedServer, 'SELECT * FROM DP') ... throws this error: OLE DB provider "MSDASQL" for linked server "MyLinkedServer" returned message "[Microsoft][ODBC Visual FoxPro Driver]Not enough memory for file map.". ...
I have two instances of SQL Server 2005 running on two separate dedicated servers (let's call them DB1 and DB2). At the moment DB1 is very busy and DB2 is mostly idle. Is it possible to configure the instance running on DB1 to utilize the processor on DB2? For example, I connect to DB1 and execute "sp-heavy-going" and I would like DB1 ...
I don't usually work with linked servers, and so I'm not sure what I'm doing wrong here. A query like this will work to a linked foxpro server from sql 2000: EXEC('Select * from openquery(linkedServer, ''select * from linkedTable'')') However, from researching on the internet, something like this should also work: Select * from link...
I am attempting to create a linked server from a 2005 to 2008 Microsoft SQL Server. I do this regularly for 2005 instances, but this is the first step of my long journey into SQL 2008. I am able to create the linked server as any other linked server, I receive no errors, however any time I try to use the linked server for anything (a sim...
Hi, the code below works fine but it takes an absolute age to run. How can I speed this up? Background: I have linked my local server to my remote server. From my local server, I need to insert data from the remote server into my local server and then update the table on my remote server. See snippet for the exact details. DECLARE @tem...
Hello Experts I have set up 2 servers which are linked. From my LOCAL SERVER, I am executing a SP that will be run on the REMOTE SERVER. The next part of the script requires me to INSERT DATA from the changes made via the SP on the REMOTE SERVER to the LOCAL SERVER. Therefore my question is, is this process asynchronous or synchronou...
Hello All, Suppose you have two linked servers called Local and Remote respectively. Is there a syntax that compresses (and uncompresses) the data required to be sent from the Local to Remote (or vice versa). For example, if I am updating my Local database with data from my Remote database, I would do the following: INSERT INTO [Loca...
I'm trying to optimize a set of stored procedures. These stored procedures are on Server A and do the following actions: Delete data on Server B based on a parameterized query on Server A DELETE FROM B (via linked server) DO SOME JOIN ON A WHERE A.x IN (@ID1, @ID2, @ID3, @ID4) Insert data on Server B based on a parameterized query on ...
I'm getting "Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." from when trying to execute the code below. I've change all of the critical information, but you should get the idea. Are some of my parameter incorrect? The local sql admin username is correct and the remote username and password is...
Hi all, I've been connecting to an oracle 10g server using ms sql servers linked server feature and for some reason i am unable to use the with clause. Does this clause only work in in the pl/sql console or is there something i can do to make it work with linked servers as well? The error i am getting is Msg 7357, Level 16, State 2, ...
I am currently running a third-party software suite, which uses SQL Server as its database. I have a second instance of SQL Server running in a different location, and some apps that I am building in that instance SQL Server needs to access some data in the third-party software. So, I created an ODBC connection between the boxes, and s...
Hello folks, this statement I use in a sproc (where the search phrase 'reiseportal*' is a parameter) does not work with a wildcard, as I recognized: DECLARE @strSQL NVARCHAR(MAX) SELECT @strSQL= 'SELECT FileName, path, size, vpath from "GRIP-SERVER"."Web2"..SCOPE() where contains ('SELECT @strSQL=@strSQL + CHAR(39) + CHAR(39)+ 'reise...
We have an old Windows 2000 server running SQL Server 2000 Standard Edition, SP3. This has a linked server setup to connect to an Informix DB via an ODBC connection. For most of the year, running OPENQUERY against the linked server works fine without any problem. However, every few months this linked server fails, and does not work again...