What Server Am I Running From

Question

Post reply

What Server Am I Running From

MattieNH

SSCertifiable

Points: 7463
More actions
January 5, 2009 at 9:06 am

#71126

Is there a way from within a stored procedure to retrieve the physical location it's running from?
Thanks,
Mattie

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736342 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736342

January 5, 2009 at 9:23 am

#921232

Do you mean instance name?

@@servername

MattieNH SSCertifiable Points: 7463 More actions · Answer 2

Steve,

Not that I had tried that, but no. It would be nice to get something like MyServer.MyDomain.OurSystem, but I'd settle for 181.50.144.3. We're trying to build a dynamic path for a text file, which would look something like:

\\MyServer.MyDomain.OurSystem\TextFiles\filename.txt

Mattie

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

MattieNH (1/5/2009)
Steve,
Not that I had tried that, but no. It would be nice to get something like MyServer.MyDomain.OurSystem, but I'd settle for 181.50.144.3. We're trying to build a dynamic path for a text file, which would look something like:
\\MyServer.MyDomain.OurSystem\TextFiles\filename.txt
Mattie

Hi Mattie

This is usually quite straightforward - can you explain a little more about your scenario? For instance, if I'm writing to Excel files, I know which server to write to regardless of which server might be running the TSQL - it will always be something like \\MyServer\share$\mydirectory\myfile.xls

Likewise if I'm importing.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MattieNH SSCertifiable Points: 7463 More actions · Answer 4

Chris,

Thanks for responding. We have a test instance on one server, and a prod instance on another. We want the file produced on the prod instance to be written to \\Prod.MyDomain.OurSystem\TextFiles, and the file produced on the test instance to go to \\Test.MyDomain.OurSystem\TextFiles. We want the same stored procedure in test and prod, so it nees to be smart enough to figure out where it should be writing to.

We don't want test files going out to a production location, and vice versa.

Mattie

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Hi Mattie

This is how I've implemented your scenario:

I have a reporting db on each server instance, call it say ReportingDB. The reporting db contains sp's, functions and tables which relate solely to reporting.

A table and a function control output destination. The function, let's call it dbo.fnReportDestination, takes a single parameter - reporting area (such as Sales, Accounts) and returns the correct path. It selects the path from a table according to the reporting area. Since the table is on the relevant instance, the same function will return the correct destination path from whichever instance it's called.

So SET @Newfilename = dbo.fnReportDestination('Sales') + 'MyFunkySpreadsheet.xls' will be exactly the same in each sp which calls it, but will output the correct path for the instance.

Let me know if you need the code, but I doubt you will, it's very simple.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MattieNH SSCertifiable Points: 7463 More actions · Answer 6

Chris,

Thanks for that suggestion. It's actually similar to what we're doing now, but here's the problem with that. We have a test instance and a production instance. On a regular basis we restore prod to the test area, and every so often, somebody forgets to change the location in the test instance to the test location. So we have users creating files in test, that end up in the production location, and they create all kinds of havoc because they contain test data that bears no relationship to reality.

And by rights, I should be apologizing for even posting this question. I had thought there was just a simple function or procedure that would provide this. But as this topic

http://www.sqlservercentral.com/Forums/Topic150196-8-1.aspx

shows, there isn't, but it's very been helpful, assuming I'm willing to use cmdshell to accomplish what I need to do.

Thanks to everyone who responded.

Mattie

tosscrosby SSCoach Points: 18045 More actions · Answer 7

You could have a "config" db with identical configuration tables but with server appropriate data. Restore the reporting db and all it's data, sps, function, whatever, but never overlay your server specific configuration data. You may have other config type items that can be added to the database over time. Just my 2 cents, for what it's worth. 😀

-- You can't be late until you show up.

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 8

Why not just map a drive on each server and use the same drive letter on each server?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 9

On the flip side, you can get the machine name and maybe go from there...

SELECT HOST_NAME()

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)