Professional Web Applications Themes

Trigger question ... - Microsoft SQL / MS SQL Server

We have about 50 customers in hosted environment, each running databases withi identical schema but different names. Part of the application traps errors and records them in a log table. I want to consolidate these logs into a single database so we can easily view errors without having to look at each customers log individually. Perhaps there is a better way, but I want to put a trigger on each customer's log table that inserts a copy of new entries into the central error database. This entry in the central database needs to include the name of the originating database, ...

  1. #1

    Default Trigger question ...

    We have about 50 customers in hosted environment, each running databases
    withi identical schema but different names. Part of the application traps
    errors and records them in a log table. I want to consolidate these logs
    into a single database so we can easily view errors without having to look
    at each customers log individually. Perhaps there is a better way, but I
    want to put a trigger on each customer's log table that inserts a copy of
    new entries into the central error database. This entry in the central
    database needs to include the name of the originating database, but I can't
    find a T-SQL command that will tell me the current database context.

    I also suspect that their might be some permissions gotchas, but that's part
    of the fun, right?


    Thanks

    Bob Castleman
    SuccessWare Software


    Bob Castleman Guest

  2. #2

    Default Re: Trigger question ...

    Thanks, Jed.

    "Jed Ozone" <jed [email]ozone[/email]> wrote in message
    news:errVpfKQDHA.3236TK2MSFTNGP10.phx.gbl...
    > check out DB_NAME()
    >
    > "Bob Castleman" <nomailhere> wrote in message
    > news:es6pVUKQDHA.3088TK2MSFTNGP10.phx.gbl...
    > > We have about 50 customers in hosted environment, each running databases
    > > withi identical schema but different names. Part of the application
    traps
    > > errors and records them in a log table. I want to consolidate these logs
    > > into a single database so we can easily view errors without having to
    look
    > > at each customers log individually. Perhaps there is a better way, but I
    > > want to put a trigger on each customer's log table that inserts a copy
    of
    > > new entries into the central error database. This entry in the central
    > > database needs to include the name of the originating database, but I
    > can't
    > > find a T-SQL command that will tell me the current database context.
    > >
    > > I also suspect that their might be some permissions gotchas, but that's
    > part
    > > of the fun, right?
    > >
    > >
    > > Thanks
    > >
    > > Bob Castleman
    > > SuccessWare Software
    > >
    > >
    >
    >

    Bob Castleman Guest

  3. #3

    Default Re: Trigger question ...

    It's a lot simpler to create a distibuted view in a management database to
    combine all the customer log tables, for example:

    CREATE VIEW all_log_tables AS
    SELECT 'DELL' AS customer, <column_list> FROM dell..log_table
    UNION ALL
    SELECT 'Microsoft' AS customer, <column_list> FROM microsoft..log_table
    UNION ALL
    SELECT 'IBM' AS customer, <column_list> FROM ibm..log_table

    of course you have to maintain this view, but you can quite easily create a
    script that loops through sysdatabases (or sysservers if all your customers
    have their own server and you have set them up as linked servers) and
    recreate the view automatically.

    You can get the current database name in SQL Server with SELECT DB_NAME()
    btw.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Bob Castleman" <nomailhere> wrote in message
    news:es6pVUKQDHA.3088TK2MSFTNGP10.phx.gbl...
    > We have about 50 customers in hosted environment, each running databases
    > withi identical schema but different names. Part of the application traps
    > errors and records them in a log table. I want to consolidate these logs
    > into a single database so we can easily view errors without having to look
    > at each customers log individually. Perhaps there is a better way, but I
    > want to put a trigger on each customer's log table that inserts a copy of
    > new entries into the central error database. This entry in the central
    > database needs to include the name of the originating database, but I
    can't
    > find a T-SQL command that will tell me the current database context.
    >
    > I also suspect that their might be some permissions gotchas, but that's
    part
    > of the fun, right?
    >
    >
    > Thanks
    >
    > Bob Castleman
    > SuccessWare Software
    >
    >

    Jacco Schalkwijk Guest

  4. #4

    Default Re: Trigger question ...

    I was thinking the same thing, Jacco is on the right track.

    You could create an executive/manager dashboard web page from the view. I'd add
    a db or region code so that exec's/mgrs in the respective db could view their
    own errors or performance statistics.

    HTT

    JeffP...

    "Bob Castleman" <nomailhere> wrote in message
    news:ebkM8rKQDHA.1072TK2MSFTNGP10.phx.gbl...
    > Hmmm ...
    >
    > A view might be better for a few reasons. We do use a standard naming
    > convention so it wouldn't be hard to write a script that re-generates the
    > view. And I don't have to worry about permissions. Gives me food for
    > thought.
    >
    > Thanks.
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:utxhxgKQDHA.2460TK2MSFTNGP10.phx.gbl...
    > > It's a lot simpler to create a distibuted view in a management database to
    > > combine all the customer log tables, for example:
    > >
    > > CREATE VIEW all_log_tables AS
    > > SELECT 'DELL' AS customer, <column_list> FROM dell..log_table
    > > UNION ALL
    > > SELECT 'Microsoft' AS customer, <column_list> FROM microsoft..log_table
    > > UNION ALL
    > > SELECT 'IBM' AS customer, <column_list> FROM ibm..log_table
    > >
    > > of course you have to maintain this view, but you can quite easily create
    > a
    > > script that loops through sysdatabases (or sysservers if all your
    > customers
    > > have their own server and you have set them up as linked servers) and
    > > recreate the view automatically.
    > >
    > > You can get the current database name in SQL Server with SELECT DB_NAME()
    > > btw.
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Bob Castleman" <nomailhere> wrote in message
    > > news:es6pVUKQDHA.3088TK2MSFTNGP10.phx.gbl...
    > > > We have about 50 customers in hosted environment, each running databases
    > > > withi identical schema but different names. Part of the application
    > traps
    > > > errors and records them in a log table. I want to consolidate these logs
    > > > into a single database so we can easily view errors without having to
    > look
    > > > at each customers log individually. Perhaps there is a better way, but I
    > > > want to put a trigger on each customer's log table that inserts a copy
    > of
    > > > new entries into the central error database. This entry in the central
    > > > database needs to include the name of the originating database, but I
    > > can't
    > > > find a T-SQL command that will tell me the current database context.
    > > >
    > > > I also suspect that their might be some permissions gotchas, but that's
    > > part
    > > > of the fun, right?
    > > >
    > > >
    > > > Thanks
    > > >
    > > > Bob Castleman
    > > > SuccessWare Software
    > > >
    > > >
    > >
    > >
    >
    >

    JDP@Work Guest

  5. #5

    Default Re: Trigger question ...

    It does seem to make more sense to use a view, or perhaps a combo of SP's
    and views, depending on the amount of flexibility they want.

    Thanks,

    Bob


    Bob Castleman Guest

  6. #6

    Default Re: Trigger question ...

    After trying to make a SP to maintain a view, I have run into a problem with
    the 4000 char limit of the nvarchar required by sp_exececutesql. Even using
    EXEC to get 8000 chars hits the limit. The problem is that there are over
    100 databases to pull data from and each UNION ALL clause is about 100
    chars, thus the query itself is 10,000+ characters.

    Is there any way around this?

    Thanks,

    Bob


    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:utxhxgKQDHA.2460TK2MSFTNGP10.phx.gbl...
    > It's a lot simpler to create a distibuted view in a management database to
    > combine all the customer log tables, for example:
    >
    > CREATE VIEW all_log_tables AS
    > SELECT 'DELL' AS customer, <column_list> FROM dell..log_table
    > UNION ALL
    > SELECT 'Microsoft' AS customer, <column_list> FROM microsoft..log_table
    > UNION ALL
    > SELECT 'IBM' AS customer, <column_list> FROM ibm..log_table
    >
    > of course you have to maintain this view, but you can quite easily create
    a
    > script that loops through sysdatabases (or sysservers if all your
    customers
    > have their own server and you have set them up as linked servers) and
    > recreate the view automatically.
    >
    > You can get the current database name in SQL Server with SELECT DB_NAME()
    > btw.
    >
    > --
    > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > Database Administrator
    > Eurostop Ltd.
    >
    >
    > "Bob Castleman" <nomailhere> wrote in message
    > news:es6pVUKQDHA.3088TK2MSFTNGP10.phx.gbl...
    > > We have about 50 customers in hosted environment, each running databases
    > > withi identical schema but different names. Part of the application
    traps
    > > errors and records them in a log table. I want to consolidate these logs
    > > into a single database so we can easily view errors without having to
    look
    > > at each customers log individually. Perhaps there is a better way, but I
    > > want to put a trigger on each customer's log table that inserts a copy
    of
    > > new entries into the central error database. This entry in the central
    > > database needs to include the name of the originating database, but I
    > can't
    > > find a T-SQL command that will tell me the current database context.
    > >
    > > I also suspect that their might be some permissions gotchas, but that's
    > part
    > > of the fun, right?
    > >
    > >
    > > Thanks
    > >
    > > Bob Castleman
    > > SuccessWare Software
    > >
    > >
    >
    >

    Bob Castleman Guest

  7. #7

    Default Re: Trigger question ...

    While you are are looping through the databases, PRINT the statement you
    have created instead of appending it to that one large sql string. You can
    then copy the generated script out of the results pane and run it in a
    different window.

    You can also declare a number of varchar(8000) variables and then
    concatenate them in EXEC like:

    DECLARE sql1 varchar(8000)
    DECLARE sql2 varchar(8000)
    DECLARE sql3 varchar(8000)
    DECLARE sql_intermediate varchar(8000)

    SET sql1 = ''
    SET sql2 = ''
    SET sql3 = ''

    -- loop through tables and create the statement for each table in
    sql_intermediate
    -- SET sql_intermediate = 'UNION ALL SELECT ......' etc

    IF LEN(sql1) + LEN(sql_intermediate) <= 8000
    SET sql1= sql1 + sql_intermediate
    ELSE IF (sql2) + LEN(sql_intermediate) <= 8000
    SET sql2= sql2 + sql_intermediate
    -- etc

    -- At the end:
    EXEC (sql1+sql2+sql3)

    Or another option if you want to do it automatically is to run the creation
    script with osql (-i filename), _SELECT_ all the parts of the final CREATE
    VIEW statement, redirect the output to a file with the -o switch, and in the
    next jobstep run that output file through osql.

    hth

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Bob Castleman" <nomailhere> wrote in message
    news:ezY$ZrWQDHA.3192tk2msftngp13.phx.gbl...
    > After trying to make a SP to maintain a view, I have run into a problem
    with
    > the 4000 char limit of the nvarchar required by sp_exececutesql. Even
    using
    > EXEC to get 8000 chars hits the limit. The problem is that there are over
    > 100 databases to pull data from and each UNION ALL clause is about 100
    > chars, thus the query itself is 10,000+ characters.
    >
    > Is there any way around this?
    >
    > Thanks,
    >
    > Bob
    >
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    > news:utxhxgKQDHA.2460TK2MSFTNGP10.phx.gbl...
    > > It's a lot simpler to create a distibuted view in a management database
    to
    > > combine all the customer log tables, for example:
    > >
    > > CREATE VIEW all_log_tables AS
    > > SELECT 'DELL' AS customer, <column_list> FROM dell..log_table
    > > UNION ALL
    > > SELECT 'Microsoft' AS customer, <column_list> FROM microsoft..log_table
    > > UNION ALL
    > > SELECT 'IBM' AS customer, <column_list> FROM ibm..log_table
    > >
    > > of course you have to maintain this view, but you can quite easily
    create
    > a
    > > script that loops through sysdatabases (or sysservers if all your
    > customers
    > > have their own server and you have set them up as linked servers) and
    > > recreate the view automatically.
    > >
    > > You can get the current database name in SQL Server with SELECT
    DB_NAME()
    > > btw.
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Bob Castleman" <nomailhere> wrote in message
    > > news:es6pVUKQDHA.3088TK2MSFTNGP10.phx.gbl...
    > > > We have about 50 customers in hosted environment, each running
    databases
    > > > withi identical schema but different names. Part of the application
    > traps
    > > > errors and records them in a log table. I want to consolidate these
    logs
    > > > into a single database so we can easily view errors without having to
    > look
    > > > at each customers log individually. Perhaps there is a better way, but
    I
    > > > want to put a trigger on each customer's log table that inserts a copy
    > of
    > > > new entries into the central error database. This entry in the central
    > > > database needs to include the name of the originating database, but I
    > > can't
    > > > find a T-SQL command that will tell me the current database context.
    > > >
    > > > I also suspect that their might be some permissions gotchas, but
    that's
    > > part
    > > > of the fun, right?
    > > >
    > > >
    > > > Thanks
    > > >
    > > > Bob Castleman
    > > > SuccessWare Software
    > > >
    > > >
    > >
    > >
    >
    >

    Jacco Schalkwijk Guest

  8. #8

    Default Re: Trigger question ...

    > -- At the end:
    > EXEC (sql1+sql2+sql3)
    >
    >
    DUH!

    Sometimes I'm such a rookie :)


    Bob Castleman Guest

Similar Threads

  1. SQL Server Trigger question
    By ego-adam in forum Coldfusion Database Access
    Replies: 3
    Last Post: October 14th, 01:46 AM
  2. An trigger question
    By ppl in forum Informix
    Replies: 1
    Last Post: November 12th, 05:30 PM
  3. question about trigger
    By Piotr in forum IBM DB2
    Replies: 7
    Last Post: September 17th, 01:49 PM
  4. trigger question
    By Newbie in forum Microsoft SQL / MS SQL Server
    Replies: 25
    Last Post: August 26th, 06:13 AM
  5. Simple trigger question
    By Kurt in forum Oracle Server
    Replies: 3
    Last Post: January 14th, 02:07 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139