Using Groovy to Deploy Federated (or Linked Server) views

I have several federated server / linked views in SQL Server 2000, which allows querying across multiple servers and databases simultaneously.

While very powerful, there are some limitations.

The primary database server has some linked servers defined. The primary database has a view defined in it which points to a specific linked server and linked database. Unfortunately, Linked Server aliases are not supported and the linked database name varies between environments.

Because the View has the Linked Server and Database Name hard coded into the view and they vary per environment, it breaks the federated view in the target database when a non-production server is refreshed from production.

Enter some Groovy, JDBC, and GStringTemplateEngine to solve the problem.

I use a map of database settings per environment. Then, the view can be recreated in a matter of seconds with the correct settings in each environment.
Below is an example of using Groovy, JDBC (JTDS), and Groovy GString Template Engine to re-created Federated / Linked Server views in Microsoft SQL Server.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
@Grapes(
    @Grab(group='net.sourceforge.jtds', module='jtds', version='1.3.1')
)
@GrabConfig(systemClassLoader=true)
 
import groovy.sql.Sql
import static java.sql.ResultSet.*
import groovy.text.GStringTemplateEngine as Engine
 
<a href="http://www.davidbharrison.com/wp-content/uploads/2015/04/1-lov.png"><img src="http://www.davidbharrison.com/wp-content/uploads/2015/04/1-lov.png" alt="1-lov" width="238" height="314" class="alignnone size-full wp-image-165" /></a>
 
<a href="http://www.davidbharrison.com/wp-content/uploads/2015/04/2-lov-detail.png"><img src="http://www.davidbharrison.com/wp-content/uploads/2015/04/2-lov-detail.png" alt="2-lov-detail" width="573" height="336" class="alignnone size-full wp-image-166" /></a>
def dropView(name) {
    println "dropView ${name}"
    try {
          res = sql.execute("DROP VIEW [dbo].["+name.toString()+"]")
          println "Drop result : ${res}"
    }
    catch (Exception e) {
          println "Exception on dropping view : ${e}"
    }
}
 
def getViewFromTemplate(viewName) {
    println "getViewFromTemplate ${viewName}"
    viewText = new File("./${viewName}.sql").text
    def e = new Engine()
    curTemplate = e.createTemplate(viewText).make(dbMap[env])
    return curTemplate.toString()
}
 
def createViewFromTemplate(viewName) {
    println "createViewFromTemplate ${viewName}"
    viewText = getViewFromTemplate(viewName)
    dropView(viewName)
    //println viewText
    println "createViewFromTemplate creating view ${viewName}"
    res = sql.execute(viewText)
    println "create stored proc : ${res}\n"
}
 
env = 'prd1' 
 
dbMap = [:]
dbMap['prod'] = [ db1_server:"proddb.internal.myOrg", db1_db:"appdb",     linked_db:"app2db_reporting_SNAP", linked_server:"app2ReportingDB.internal.myOrg" ]
dbMap['qa1']  = [ db1_server:"qa1db.internal.myOrg",  db1_db:"appdb_qa1", linked_db:"app2db_qa1",            linked_server:"app2NonProd.internal.myOrg" ]
dbMap['dev'] =  [ db1_server:"devdb.internal.myOrg",  db1_db:"appdb_dev", linked_db:"app2db_dev",            linked_server:"app2NonProd.internal.myOrg" ]
 
 
['qa1','dev'].each() { env -> 
    println "---------------------"
    println "Processing for ${env}"
    println "---------------------"   
    db1_server = dbMap[env]['db1_server']
    db1_db = dbMap[env]['db1_db']
    linked_db = dbMap[env]['linked_db']
    linked_server = dbMap[env]['linked_server']
 
    def jdbcURL = "jdbc:jtds:sqlserver://${db1_server}/${db1_db}"
    def user = 'YOUR_ADMIN_USER'
    def pw = 'YOUR_ADMIN_PASSWORD'
    sql = Sql.newInstance( jdbcURL, user, pw, 'net.sourceforge.jtds.jdbc.Driver' )
    sql.execute("SET ANSI_NULLS ON")
    sql.execute("SET QUOTED_IDENTIFIER ON")
 
    createViewFromTemplate('FederatedView1', dbMap[env])
    createViewFromTemplate('FederatedView2', dbMap[env])
}

SpagoBI Groovy LOV for Last Day of Last Month

SpagoBI Lookup Value (LOV) in Groovy to get the last day of last month

1
2
3
4
5
6
7
8
9
10
11
12
Calendar cal = Calendar.getInstance()
cal.add(Calendar.MONTH, -1)
cal.set(Calendar.DATE, 1)
Date firstDateOfPreviousMonth = cal.getTime()
cal.set(Calendar.DATE, cal.getActualMaximum(Calendar.DATE))
Date prevDate = cal.getTime()
 
//You pick whether you like sprintf or GString better
//String returnStr = "${(cal.get(Calendar.MONTH)+1).toString().padLeft(2,'0')}/${(cal.get(Calendar.DATE)).toString().padLeft(2,'0')}/${cal.get(Calendar.YEAR)}"
//return returnStr.toString()
String returnStr = sprintf("%02d/%02d/%4d", cal.get(Calendar.MONTH)+1, cal.get(Calendar.DATE), cal.get(Calendar.YEAR))
return returnStr

Getting the first day of the last month for a SpagoBI LOV default date value is slightly easier

1
2
3
4
5
6
7
8
9
10
Calendar cal = Calendar.getInstance()
cal.add(Calendar.MONTH, -1)
cal.set(Calendar.DATE, 1)
Date firstDateOfPreviousMonth = cal.getTime()
cal.set(Calendar.DATE, cal.getActualMaximum(Calendar.DATE))
Date prevDate = cal.getTime()
 
 
String returnStr = sprintf("%02d/01/%4d", cal.get(Calendar.MONTH)+1, cal.get(Calendar.YEAR))
return returnStr

1-lov

2-lov-detail

3-ad-detail

4-report-param

5-report-param-prompt

SQL Server – get all triggers

Here is an often used SQL statement to get a list of Triggers, in SQL Server. I’m posting this here for my own convenience and perhaps someone else will benefit.

SELECT
  [so].[name] AS sys_obj,
  so.type,
  USER_NAME([so].[uid]) AS obj_owner,
  USER_NAME([so2].[uid]) AS table_schema,
  OBJECT_NAME([so].[parent_obj]) AS table_name,
  OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS [isupdate],
  OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS [isdelete],
  OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS [isinsert],
  OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS [isafter],
  OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS [isinsteadof],
  OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS [disabled] 
 FROM sysobjects AS [so]
 INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
 WHERE [so].[type] = 'TR';