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
@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
 
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])
}

Leave a Reply

Your email address will not be published. Required fields are marked *