I have a SQL Insert query inside a stored proc, for inserting rows into a linked server table. Since the stored proc is getting called within a parent transaction, this Insert statement tries to use a DTC for inserting rows into the linked server. I would like to avoid DTC from getting involved. Is there any way I can do that (like a ...
So at work we have two servers one is running an application a lot of people use which has an sql 2000 back end. I have been free to query this for a long time but can't add anything to it such as stored procedures or extra tables. This has lead to us having a second sql server linked to the first one and me building up a library of stor...
Every morning we have a process that issues numerous queries (~10000) to DB2 on an AS400/iSeries/i6 (whatever IBM calls it nowadays), in the last 2 months, the operators have been complaining that our query locks a couple of files preventing them from completing their nightly processing. The queries are very simplisitic, e.g Select [Fie...
Hello, We've purchased a tool from Redgate called SQL Data Compare. I use it mainly for testing purposes to prove that my application is making the modifications to the database that I think it is, and not making any other changes. My current application has both "online" and "offline" editing. In online mode you are working directly...
Hi folks I've got 2 remote databases as part of a query select p.ID,p.ProjectCode_VC,p.Name_VC,v.* FROM [serverB].Projects.dbo.Projects_T p LEFT JOIN [serverA].SOCON.dbo.vw_PROJECT v on p.ProjectCode_VC = v.PROJ_CODE The problem is that serverB uses collation Latin1_General_BIN and serverB uses Latin1_General_CP1_CP_AS and the query ...
I am trying to execute a stored procedured from a linked database in MS SQL 2005. The linked database is a db2 database on a iseries server. I am using the iSeries IBMDASQL service provider. I am able to query the stored procedure without problems using a simple vbscript and ado. When I try to execute the same stored procedure in que...
Here's the situation: we have an Oracle database we need to connect to to pull some data. Since getting access to said Oracle database is a real pain (mainly a bureaucratic obstacle more than anything else), we're just planning on linking it to our SQL Server and using the link to access data as we need it. For one of our applicati...
I am Showing An example of Stored Procedure For Data Transaction using "Linked Server" Between Two System Through Internet Alter Proc [dbo].[usp_Select_TransferingDatasFromServerCheckingforExample] @RserverName varchar(100), ----- Server Name @RUserid Varchar(100), ----- server user id @RPass Varchar(100), ---...
I have a SQLServer with a linked server onto another database somewhere else. I have created a view on that linked server create view vw_foo as select [id], [name] from LINKEDSERVER.RemoteDatabase.dbo.tbl_bar I'd like to to the following alter table [baz] add foo_id int not null go alter table [baz] with check add constraint [fk1_...
How can I return a OpenQuery in SQL Server including a variable to a cursor? DECLARE curMyCursor CURSOR FOR EXEC('SELECT * FROM OPENQUERY(SYBASE, ''SELECT * FROM MyTable WHERE MyPrimaryKey=''''' + @Variable + ''''''')') OPEN @ResultCrsr ...
I added a linked server,and it is showing in the linked server list,but when i'm quering it's throwing an error with the db server name. *EXEC sp_helpserver* EXEC sp_addlinkedserver 'aa-db-dev01' Select * from openquery('aa-db-dev01','Select * from TestDB.dbo.users') Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'aa-db-de...
I am using VSTS 2008 with both the developer and database editions installed. I have the DB edition GDR installed (version 9.0.31124). We have several places in code that we reference linked servers (DB2 mainframe, iSeries, and SQL Server). One such example is this code that we have in a store procedure: SELECT VendorID INTO #tmp...
I have an Sql Server database that includes a linked server to an MS Access mdb database. The Access database has workgroup security and requires a valid user name and password. This is easily set up in the linked server dialog in sql management studio. The problem is that the Sql Server needs to be able to find the MS Access mdw file to...
What is the best way to go about doing this? I have found some INSERT statements, but they all seem to work just for inner-database copying. My rendition: INSERT INTO [PROGRAM003].[dbo].[faq] ([id], [category], [question], [answer], [tags]) SELECT ([id], [category], [question], [answer], [tags]) FROM [SQL2005_552664_gsow].[dbo].[fa...
I have a strange scenario that I am currently unable to explain. I live in hope that it's just "the Friday feeling" or that some kindly sole here will bail my brain out and save me from endless loops of "but why!?" :) Two servers, running SQL Server 2005, with DNS Entries of: 1. ServerA 2. ServerB (Well, they're not really calle...
I have a linkedserver that will change. Some procedures call the linked server like this: "[10.10.100.50].dbo.SPROCEDURE_EXAMPLE". We have triggers also doing this kind of work. We need to find all places that uses "[10.10.100.50]" to change it. In SQL Server Management Studio Express I didn't find this feature, a "find in whole databas...
I have a client who has a SQL Server 2000 database linked to an Oracle 8i database. They have dozens of views in the SQL Server 2000 database which reference the Oracle database, often with simple syntax such as: SELECT * FROM SERVER..DB.TABLE These views (and the sprocs which reference them) have worked for YEARS without issue. Sudd...
Hello, Just set up a new server. Trying to transfer some workload from one to the other. Everything looks great, except when I come to run this code: CREATE TABLE #KEYWORD5 (ITEM_MASTER_ID NUMERIC(25) NULL) INSERT INTO #KEYWORD5 exec SQL2K801.soupftidx.dbo.P_REMOTE_SQL_EXEC 'SELECT IM.ITEM_MASTER_ID FROM ITEM_MASTER IM WH...
Since I struggled a bit with this one and couldn't find a good online source with simple steps, here it is. ...
My team is evaluating tools and processes for managing database migrations/database refactoring as described by Martin Fowler, Pramod Sadalage, et. al. We're interested in automated, repeatable, testable processes, so we're not interested in techniques like manually running SQL Compare every time we deploy. We're currently using Cruise...