• State Verified Answer
  • Replies 6 replies
  • Subscribers 93 subscribers
  • Views 2467 views
  • Users 0 members are here
Options
Related

Random Select mailbox database

lrigs
over 3 years ago

Hello,

Can someone tell me a better way to select random mailbox database?

We have our mailbox databases named to start with the location of the user.

Location may by AU, US, or UK.

Example:

  • US-MAILBOX-DB1
    US-MAILBOX-DB2
    US-MAILBOX-DB3
    US-MAILBOX-DB4
  • AU-MAILBOX-DB1
    AU-MAILBOX-DB2
    AU-MAILBOX-DB3
    AU-MAILBOX-DB4
  • UK-MAILBOX-DB1
    UK-MAILBOX-DB2
    UK-MAILBOX-DB3
    UK-MAILBOX-DB4

I put in a script that should do the random selection based on the DB name.

Public Function CCC_MailboxDBSelect( _
Optional ByVal location As String = Nothing) As String

Dim DB As String = Nothing

DB = Connection.GetSingleProperty("EX0MailBoxDatabase", "NAME", "NAME = (Select TOP 1 name from EX0MailBoxDatabase where name Like '" & location & "-%' ORDER BY newid())").ToString

Return DB
End Function

When I do a SQL select it always works with-out and issues and selects a random DB server based on the name starting with the location.

When I run the script with my test it does not keep the select based on the name it just selects a random DB server.

select TOP 1 NAME from EX0MailboxDatabase
Where name like 'US-%'
ORDER BY newid()

Can anyone tell me if the Connection.GetSingleProperty function can do the query? Maybe another function or way someone can suggest?

Lu

Parents
  • +1 over 3 years ago

    The first thing I would do is to check the SQL log to take a look at the generated SQL clause from your GetSingleProperty. Then, you can try to test this SQL in SQL Management Studio (or similar) to what the outcome is.

  • 0 over 3 years ago in reply to Markus Weiss-Ehlers

    Markus,

    Thank you for the suggestion I totally got this going after you suggested the logs. I'm not much of a SQL guy so it helps to know where to look.

    I don't think i would have figured it out without this suggestion. I ran the following query after running the job. 

    SELECT t.[text], s.last_execution_time
    FROM sys.dm_exec_cached_plans AS p
    INNER JOIN sys.dm_exec_query_stats AS s
    ON p.plan_handle = s.plan_handle
    CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
    WHERE t.[text] LIKE N'%EX0MailboxDatabase%'
    ORDER BY s.last_execution_time DESC;

    It turns out my if statement was wrong and applying the wrong front part of the name I needed for the mailbox database.

    I appreciate the help.

    Lu

Reply
  • 0 over 3 years ago in reply to Markus Weiss-Ehlers

    Markus,

    Thank you for the suggestion I totally got this going after you suggested the logs. I'm not much of a SQL guy so it helps to know where to look.

    I don't think i would have figured it out without this suggestion. I ran the following query after running the job. 

    SELECT t.[text], s.last_execution_time
    FROM sys.dm_exec_cached_plans AS p
    INNER JOIN sys.dm_exec_query_stats AS s
    ON p.plan_handle = s.plan_handle
    CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
    WHERE t.[text] LIKE N'%EX0MailboxDatabase%'
    ORDER BY s.last_execution_time DESC;

    It turns out my if statement was wrong and applying the wrong front part of the name I needed for the mailbox database.

    I appreciate the help.

    Lu

Children