SQL SP to compare.... SP`s

• danielfountain

  SSCarpal Tunnel

  Points: 4229

  More actions

  May 4, 2012 at 2:08 am

  #257511

  Hey all,

  I am trying to create a SP that will compare the output of given SP`s on given servers. Basically when we update a SP on DEV - it would be lovely to easily check the output hasnt changed (or has changed correctly - depending what we are doing).

  So the code i have so far (not in SP form at the moment) is

  DECLARE @Server1 VARCHAR(MAX) = 's1',

  @Server2 VARCHAR(MAX) = 's2'

  DECLARE @Conn1 VARCHAR(MAX) = 'Server=' + @Server1 + ';Trusted_Connection=yes;'

  DECLARE @Conn2 VARCHAR(MAX) = 'Server=' + @Server2 + ';Trusted_Connection=yes;'

  DECLARE @SP1 VARCHAR(MAX) = 'EXEC adwmi.dbo.SP1Test 546668691'

  DECLARE @SP2 VARCHAR(MAX) = 'EXEC adwmi.dbo.SP1Test 546668691'

  DECLARE @SQL VARCHAR(MAX)

  DROP TABLE #MyTempTable

  DROP TABLE #MyTempTable2

  SET @SQL = '

  SELECT *

  FROM

  OPENROWSET(''SQLNCLI'','''+@Conn1+''', '''+@SP1+''')

  '

  EXECUTE(@SQL)

  SET @SQL = '

  SELECT *

  INTO #MyTempTable2

  FROM

  OPENROWSET(''SQLNCLI'','''+@Conn2+''', '''+@SP2+''')

  '

  EXECUTE(@SQL)

  Now my current problem is that neither of the temp tables actually then exist. @Conn1 is to a remote server - so i am guessing the openrowset causes the temp table to be created remotely. Could the same happen from the local server (@conn2).

  Any ways i can get round this? Any ideas gratefuly recieved.

  Dan

• William Melcher

  SSC-Addicted

  Points: 425

  More actions

  May 29, 2012 at 3:34 am

  #1494050

  Dan, If you create the temp table first, your dynamic SQL statement will be able put records into it and you will be able to access them after the statement is run:

  create table #MyTempTable(...);

  declare @SQL varchar(max);

  SET @SQL = '

  SELECT *

  INTO #MyTempTable

  FROM

  OPENROWSET(''SQLNCLI'','''+@Conn2+''', '''+@SP2+''')

  '

  EXECUTE(@SQL);

  select *

  from #MyTempTable

• Sony Francis @EY

  SSCommitted

  Points: 1539

  More actions

  May 29, 2012 at 3:52 am

  #1494060

  Create the temp table first . Use INSERT query inside the dynamic query

  For Ex

  ALTER PROCEDURE#SP

  AS

  BEGIN

  CREATE TABLE #Emp(NAme varchar(100), age int)

  declare @SQL varchar(max);

  SET @SQL = 'INSERT INTO #Emp

  SELECT * FROM EMP'

  EXECUTE @SQL

  SELECT * FROM #Emp

  END

• danielfountain

  SSCarpal Tunnel

  Points: 4229

  More actions

  May 29, 2012 at 4:05 am

  #1494066

  Thanks for that.

  I still have an issue - maybe i wasnt clear with my definition.

  I want this to compare two SP results on different servers. Not two specific SP`s.

  So i cant define the table as i dont know what the columns will be until i run the query.

  I think i am asking too much of SQL.

  🙂 Thanks anyway 🙂

• toddasd

  SSCertifiable

  Points: 6298

  More actions

  May 29, 2012 at 7:46 am

  #1494181

  Maybe you don't need to create temp tables? Something like this will show the differences:

  SET @SQL = '

  SELECT *

  FROM

  OPENROWSET(''SQLNCLI'','''+@Conn1+''', '''+@SP1+''')

  EXCEPT

  SELECT *

  FROM

  OPENROWSET(''SQLNCLI'','''+@Conn2+''', '''+@SP2+''')

  '

  EXECUTE(@SQL)

  ______________________________________________________________________________
  How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

• danielfountain

  SSCarpal Tunnel

  Points: 4229

  More actions

  May 29, 2012 at 9:40 am

  #1494250

  That is brilliant.

  I tried everything.... except that. No idea why i didnt try it.

  When i get a second i am going to sort this into a stored proc to compare results of SP`s.

  Thanks so much!

  Dan