Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

SQL Server DTS: The "DTS Dorian Question": How can a package detect the server hosting itself?

1) How can a DTS package detect the server it is running on while it is running?  
2) How can a DTS Pkg determine what environment/server it is attempting to run in.  
3) How can I create a Task (most likely a VBScript or an ActiveX Script Task) to capture what server is hosting the package itself?

I believe these three questions are all asking the same thing.

BTW, I'm not asking how to determine properties of an existing connection object.  I know roughly how to do that.  For example...

Option Explicit
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
      Dim oPkg
      Dim strMsg
      Dim oConn
      Set oPkg = DTSGlobalVariables.Parent
      For Each oConn In oPkg.Connections
            strMsg = strMsg & oConn.Name & vbCrLf
            strMsg = strMsg & oConn.DataSource & vbCrLf
            strMsg = strMsg & oConn.Catalog & vbCrLf
            strMsg = strMsg & oConn.ProviderID & vbCrLf            
            strMsg = strMsg & vbCrLf & vbCrLf
      Next
      MsgBox strMsg
      
      Main = DTSTaskExecResult_Success
End Function


Also, I already tried looping through all 27 package properties.  For example:

Option Explicit
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
      Dim oPkg
      Dim i
      Dim strMsg
      Set oPkg = DTSGlobalVariables.Parent
      For i = 1 to 27
            strMsg = strMsg & oPkg.Properties(i).Name & " = " & oPkg.Properties(i).Value & vbCrLf
      Next
      MsgBox strMsg
      
      Main = DTSTaskExecResult_Success
End Function

But a hosting server name or DNS is not one of the properties.

What I (and Dorian) are asking is how to detect the actual SQL Server 2000 instance (or even just the DNS) that the DTS package is on.


NOTE:  I am posing this as the "DTS Dorian" question because some guy named Dorian has asked this before.  Google search the string...
DTS "detect which server" running
...and you will see that his question is posted on a number of boards but with no responses.

If your answer is "It cannot be done!" then as a heads up I will reduce and only award 250 for whoever can prove to me that it cannot be done.
0
GronkeyKong
Asked:
GronkeyKong
  • 5
  • 4
  • 3
1 Solution
 
twolCommented:
Will you have a connection that has the (local) in the connection string? If so, can't you just open it and query

select @@SERVERNAME
0
 
GronkeyKongAuthor Commented:
Hmm...that's a good question.  A (local) connection is not going to work for me during DTS construction as (local) would mean my actual laptop (correct?).  But you might be on to something because when this DTS package gets scheduled as a Job via SQL Server Agent, my guess is that (local) might actually work.  I'm waiting on a Windows NT/2000 login we asked the DBA on the DEV box to create (or make available) so that we can schedule the DTS Package as a Job via SQL Server Agent.  When I get the login I will test with a (local) connection.  If that works, I will be a happy man and can move on to my next headache.  I hope you are right!
0
 
Anthony PerkinsCommented:
I could never find a solution to this.  At least the VBScript options were not sufficient.  The connection properties (or @@SERVERNAME) do not cut it as you can execute a DTS package from any workstation, it does not have to be on a server let alone a SQL Server.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
Actually, on second thoughts this should do it:
Dim WshNetwork
Set WshNetwork = CreateObject("WScript.Network")
MsgBox WshNetwork.ComputerName
Set WshNetwork = Nothing
0
 
twolCommented:
Did you get it to work then?
0
 
Anthony PerkinsCommented:
That was copied from some Production DTS Package (sans MsgBox of course).  So yes, it does what it is supposed to do.  Try it out for yourself, it is pretty simple:
1. Copy and paste into a text file.
2. Save the text file as Test.vbs
3. Run it.
0
 
twolCommented:
I thought you wanted the SQL server name, sorry. I think you can also do

Select Host_Name() as Computer_Name

0
 
GronkeyKongAuthor Commented:
acperkins--

I think this is going to work.  II get my laptop machine name when run from my laptop and I get the Windows Server 2000 machine name when executing while remotely logged into server via a Remote Desktop Connection session.  My final ActiveX Script Task that assigns a value to my DTS Global Variable is:

Option Explicit
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
'      Dim WshNetwork

      Set WshNetwork = CreateObject("WScript.Network")
      DTSGlobalVariables("strMachine").Value = CStr(WshNetwork.ComputerName)
      Set WshNetwork = Nothing

      Main = DTSTaskExecResult_Success
End Function


What I like about this solution is that it does not require SQL which means I do not have to establish any connections which means I can use the return value to determine what dynamic connections to set up (at run-time I guess you could say).  

I want to use the returned Machine Name to determine whether to use Development environment connections or Production environment connections (and there are multiple connections that reference different databases as well as different network folders).  I'm hoping that I can develop and test in DEV and push to PROD without having to change a thing.

Great solution.!

I'm still having problems making the DTSGlobalVariables("MachineName") available to an Execute SQL Task using the question mark (?) and clicking "Parameters..." method.  I'm almost at my wits end but I have one more thing to test before I post as a new question.  If I do post I'll call it:

"SQL Server DTS: DTSGlobalVariables visibility to Exectue SQL Task using question mark (?) and click Parameters... method?  Syntax error or accee violation / An error occured while parsing the SQL statement for parameters..."
0
 
Anthony PerkinsCommented:
First make sure that the Stored Procedure compiles and runs from SSMS.
Next make sure the correct server/database is selected in the DTS package.
Finally make sure you can execute the Stored Procedure with the same DTS user.  In other words if they are not the database owner than at least you have done:
GRANT EXECUTE ON usp_YourStoredProcedure TO YourDTSUser
0
 
GronkeyKongAuthor Commented:
I posted new question concerning the Question Mark(?) issue:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/DTS/Q_27422077.html
0
 
GronkeyKongAuthor Commented:
twol--

The (local) thing did not work for me but its possible I was doing something wrong.  

1.  I opened Enterprise Manager (EM) logged on as myself and used explicit SQL server connections (i.e., Server\Instance) and saved DTS package.  In other words, I did not user the (local) method yet.

2.  I opened EM (using the Run As...) and logged on under a System account that had sysadmin rights.  I successfully scheduled a Job to be executed by SQL Server Agent on the said Server\Instance.  (This was a long draw out and painful experience that I won't go into here.)

3.  I tested and confirmed that SQL Server Agent Job was Successfully executing as desired.

4.  I opened EM logged on as myself and using the Disconnected Edit feature manually replaced the DataSource value of the local connections with (local) minus the double quotes.   Then I saved package and closed it.

5.  The SQL Server Agent Job executing then failed.
0
 
GronkeyKongAuthor Commented:
twol--

But even if I got this to work.  I don't think the (local) connection with SELECT @@SERVERNAME statement is ideal for my situation as the only way it would connect successfully (when running DTS in Degug/Design mode) is if I was logged into the base Server machine itself via a Remote Desktop Connection session.  And I don't want to use an On Fail workflow that directs to an explicit Server\Instance connection when connecting as my laptop as I need "Fail package on first error" and "Commit on successful package completion" both to be set to true because it makes rollback distributed transactions more manageable.  

Feel free to post back if this did not make sense or just for discussions sake.  As I will try to respond.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now