Database

Mycroft Holmes wrote:

the query designer

1. Using the SQL Server Configuration Manager, create an alias[^] pointing to the correct Server2 for the current environment. The alias name should be the same in all environments.
2. In SQL Server Management Studio, create a linked server[^] pointing to the alias you just set up.
3. In your stored procedure, load the data from the linked server.

select distinct xxid FROM CedarsLinkServer.CEDARS.dbo.SomeTestDemographic where recEndDate is null

Msg 7202, Level 11, State 2, Line 1
Could not find server 'CedarsLinkServer' 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.

--connects to and disconnects from the mysql online database
--example call to connect:
--dbo.LinkedServerConnect_SP @connect = 1
--example call to disconnect:
--dbo.LinkedServerConnect_SP @connect = 0

CREATE PROC dbo.LinkedServerConnect_SP 
(@connect as int)
AS 
BEGIN 

if @connect = 1
begin

	if not exists(select name from sys.servers where name = 'yadayada') 
  	begin	
		
		EXEC sp_addlinkedserver 'yadayada', 'MySQL', 'MSDASQL', Null, Null, 'Driver={MySQL ODBC 3.51 Driver};DB=yadayada;SERVER=mysql.yadayada.com;option=512;uid=yadayada;pwd=yadayada'

	end

end

if @connect = 0
begin

	if exists(select name from sys.servers where name = 'yadayada') 
  	begin	
		
		EXEC sys.sp_dropserver @server = 'yadayada', @droplogins = 'droplogins'; 

	end

end

end

GO

Mycroft Holmes wrote:

that lives in a stored proc that does what it is told when it is told and everyone knows where to find it.

Richard Deeming wrote:

inserted

Richard Deeming wrote:

deleted

• sql trigger magic tables[^]: 413,000 results;
• sql trigger virtual tables[^]: 1,680,000 results;

SELECT
   A.ID As OriginalID,
   B.ID As DuplicateID
FROM
   -- Every pair of master records:
   MasterTable As A
   CROSS JOIN
   MasterTable As B
WHERE
   -- Only those pairs where A is earlier than B:
   A.ID < B.ID
And
   -- All detail lines for A exist for B:
   Not Exists
   (
      SELECT
         (relevant columns)
      FROM
         DetailTable
      WHERE
         MasterID = A.ID
      
      EXCEPT
      
      SELECT
         (relevant columns)
      FROM
         DetailTable
      WHERE
         MasterID = B.ID
   )
And
   -- All detail lines for B exist for A:
   Not Exists
   (
      SELECT
         (relevant columns)
      FROM
         DetailTable
      WHERE
         MasterID = B.ID
      
      EXCEPT
      
      SELECT
         (relevant columns)
      FROM
         DetailTable
      WHERE
         MasterID = A.ID
   )