We're looking to iron out issues in our different dev/test/prod environments. Currently we have to remember to change the name of linked servers in stored procedures when we migrate from UAT into Production. For example, in Production, a sproc in SMOLDB calls across a linked server to LS_AUTH.AuthenticationDB.dbo.SomeSproc because Aut...
Trying to update a table on a linked server (SQL 2000/2005) but my server name will not be known ahead of time. I'm trying this: DECLARE @Sql NVARCHAR(4000) DECLARE @ParamDef NVARCHAR(4000) DECLARE @SERVER_NAME VARCHAR(35) SET @Sql = 'UPDATE @server_name_param.dba_sandbox.dbo.SomeTable SET SomeCol=''data''' SET @ParamDef = N'@server_n...
Hi I am using MS SQL Server 2000 and have a link from a test db to a live db which is in replication. The link seems to work fine and I can select from any of the tables using any of the fields apart from the field with the constraints on creating ids. So if I run select * from person where firstname like 'john' this works fine, but th...
I have a WebTrends ODBC source on server1 and a SQL Server 2005 on server2. I want to connect to this ODBC from SQL Server on server2. So far, I managed to connect to this ODBC from SQL Server which is on same server, using following definition for linked server: EXEC sp_addlinkedserver @server = N'WT_ODBC', @provider=N'MSDASQL', @da...
When running a SQL statement or a stored procedure on a database, can you connect to an external database and pull data from there? something like: SELECT a.UserID, b.DataIWantToGet FROM mydb.Users as a, externaldb.Data as b ...
Based on the answer I received on this question (http://stackoverflow.com/questions/1911969/...) I have set up a Linked Server on SQL Server 2008 to a Pervasive database. Since both databases are on the same physical server, what would be the recommended method to create the linked server, using the IP Address of the server or the Serve...
I'm working on a C# application that queries a SQL Server 2008 Express instance on a remote server (LAN). When I am at my workstation/dev machine, I can open SQL Server Management Studio and connect to the SQL instance through Windows Authentication, and for all intents and purposes the experience is identical to working in SSMS "on" th...
I have run into a strange problem using SQL Server 2000 and two linked server. For two years now our solution has run without a hitch, but suddenly yesterday a query synchronizing data from one of the databases to the other started timing out. I connect to a server in the production network, which is linked to a server containing order...
Hi, Does anyone know of a way to capture an error message returned from a linked server? As an example, if i run the following command in SQL Server management studio: BEGIN TRY exec ('select * from xxx') at my_linked_server END TRY BEGIN CATCH print 'ErrorNumber...'+ CAST(ERROR_NUMBER() as varchar) print 'ErrorSeverity...'+ ...
Hello, I'm developing a web app in a testing environment with test sql servers and databases. I'm currently adding a linked server and accessing the databases and tables in the linked server using [Servername].[DatabaseName].dbo.[TableName]. But after pushing it to production the DB servers name will change. So should i need to open ea...
Hello all, I have tried to make use of linked servers in SQL Server 2008 by doing the following to access a Microsoft Access 2003 Table. EXEC sp_addlinkedserver access1t, 'OLE DB Provider for Jet', 'Microsoft.Jet.OLEDB.4.0', 'C:\tester.mdb' EXEC sp_addlinkedsrvlogin access1t, FALSE, Null, Admin, Null GO CREATE VIEW TI001APCE1265 AS SEL...
This query originally came from a VB6 program accessing MS Access tables which are linked to external databases through ODBC. It takes about 3:30 to run. Now I've setup a SQL Server 2008 Express box to evaluate how we can migrate to a better database system. So I setup a linked server to the external server (we call it DWPROD) and when ...
I'm doing some fairly complex queries against a remote linked server, and it would be useful to be able to store some information in temp tables and then perform joins against it - all with the remote data. Creating the temp tables locally and joining against them over the wire is prohibitively slow. Is it possible to force the temp tab...
I'm having a problem running this query: DELETE FROM [IRPROD]..[BUDGET_USER].[GL_EXP] WHERE FISCAL_YEAR = 2010 IRPROD is a linked server to an Oracle 10g database. It's linked using the Oracle OleDB provider. There are ~79000 records that need to be deleted. Running this query it deletes 54. Running it again gives me this error messag...
I have used sp_addlinkedserver to access the remote machines db now i am writing queries explicitly on database like, select * from [server\instance].database.owner.tablename Now with this, [Server\instance] : this has to be provided explicitly [database] : can we find databases on specified instance using query like ms_ForEachDB ? ...
Hi, I am trying to set up a linked server in SQL Server 2008 to a MS Access 2003 database that is protected by both a workgroup username and password, as well as a database password. It also uses its own system database (system.mdw) I used the SQL Server template, which is pretty much identical to the procedure I find by Googling and o...
Trying to run a cross-server update: UPDATE cmslive.CMSFintrac.dbo.lsipos SET PostHistorySequencenNmber = ( SELECT TransactionNumber FROM Transactions WHERE Transactions.TransactionDate = cmslive.CMSFintrac.dbo.lsipos.TransactionDate) Gives the error: Server: Msg 117, Level 15, State 2, Line 5 The number name ...
Hello, I have a table called Table with columns: ID (int, primary key, clustered, unique index) TEXT (varchar 15) on a MSSQL linked server called LS. Linked server is on the same server computer. And: When I call: SELECT ID, TEXT FROM OPENQUERY(LS, 'SELECT ID, TEXT FROM Table') It takes 400 ms. When I call: SELECT ID, TEXT F...
I have the following scenario: 2 DB servers (Linked to each other) DB1 has a (large) table with transaction records DB2 has a (not quite as large yet) table with transaction records (of a similar nature but without some of the data as it is a different system) There are a bunch of reports that pull records out of the transaction table ...
After much fiddling, I've managed to install the right ODBC driver and have successfully created a linked server on SQL Server 2008, by which I can access my PostgreSQL db from SQL server. I'm copying all of the data from some of the tables in the PgSQL DB into SQL Server using merge statements that take the following form: with mbRem...