Professional Web Applications Themes

SQL SERVER JOB - Microsoft SQL / MS SQL Server

For ytical reports, I have to collect data from different databases resides in different servers to a central location. So my current approach is , I have a table which stores servername and dbname. Create a SP in central location and dynamically change the servername and dbname and loads data. I have to build long dynamic SQL to get data (certain joins are with 3-4 tables). Everything works fine. Just want know any better approach to handle this ?? Can this be achieved thru DTS (dynamically changing servername/dbname) ?? ** One pitfall for my current approach , if anyone of ...

  1. #1

    Default SQL SERVER JOB

    For ytical reports, I have to collect data from different databases
    resides in different servers to a central location.
    So my current approach is ,
    I have a table which stores servername and dbname.
    Create a SP in central location and dynamically change the servername and
    dbname and loads data.
    I have to build long dynamic SQL to get data (certain joins are with 3-4
    tables).
    Everything works fine.
    Just want know any better approach to handle this ??
    Can this be achieved thru DTS (dynamically changing servername/dbname) ??

    ** One pitfall for my current approach , if anyone of the server is down my
    SQL JOB (which executes the SP) fails.

    Thx In Advance
    Sh


    Shamim Guest

  2. #2

    Default Re: SQL SERVER JOB

    Thanks for the reply.
    Yes, I looked at tion. For this purpose then I have to subscribe many
    tables (some are with millions of records).
    Can you pls explain how to change connection properties dynamically
    (programmatically) inside DTS.

    Thx
    Sh


    "Allan Mitchell" <sqldts.com> wrote in message
    news:phx.gbl... [/ref]
    and [/ref]
    ?? 
    > my 
    >
    >[/ref]


    Shamim Guest

  3. #3

    Default Re: SQL SERVER JOB

    OK

    Here are some examples

    Connections
    http://www.sqldts.com/default.aspx?6,101,101,5,1


    For SQL Server

    DataSource = Server
    Catalog = Database



    --

    ----------------------------
    Allan Mitchell (Microsoft SQL Server MVP)
    MCSE,MCDBA
    www.SQLDTS.com
    I support PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org



    "Shamim" <com> wrote in message
    news:phx.gbl... 
    many [/ref][/ref]
    databases [/ref]
    > and [/ref][/ref]
    3-4 [/ref]
    > ?? [/ref][/ref]
    down 
    > >
    > >[/ref]
    >
    >[/ref]


    Allan Guest

Similar Threads

  1. Replies: 0
    Last Post: August 15th, 09:24 AM
  2. Replies: 2
    Last Post: September 12th, 10:44 PM
  3. Replies: 0
    Last Post: August 23rd, 05:27 PM
  4. deploying from Windows 2003 staging server to remote production server
    By Marc Garrett in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 22nd, 04:17 PM

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