How to get the machine name when executing a stored procedure via a front end application

Question

How to get the machine name when executing a stored procedure via a front end application

etienne.steenkamp-external

SSC Veteran

Points: 229
More actions
August 28, 2010 at 9:48 am

#226955

I am using a front end application to execute a stored procesdure on the SQL Server.
This stored procedure extracts data to a text file and I need to save this file in a directory on the machine (host_name) from where this application is used.
This application is used on various machines and the idea is to use the host_name as a vriable to know to which machine the file has to be extracted.
Some assistance would be much appreciated.
Etienne

Viewing 8 posts - 1 through 7 (of 7 total)

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

Brian O'Leary SSChasing Mays Points: 623 More actions · Answer 1

You might be better off simply returning the result set to your application and then generating the file with that rather than have SQL Server push the file out to it's clients.

However, you could use either bcp or SSIS to do this.

MCITP SQL Server 2005/2008 DBA/DBD

Steve Newton SSCommitted Points: 1864 More actions · Answer 2

If you must have it stored on the app machine, I would have the app work this out and then pass it into the stored procedure. You can then export to a UNC path which you set by parameter.

The other option I would prefer is that create a share on the SQL server which the app machines can access via the app.

Steve.

Leo.Miller SSChampion Points: 13035 More actions · Answer 3

This doesn't sound like good practice. For the SQL Server Stored proc to be able to save the file onto the calling server's hard drive, it needs write permission on ever client were the app is installed. This means applying those permissions on a client by client bases (potentially a lot of work) or what normally happens, the SQL Server Service ends up being run under a Domain Admin account. This isn't a good idea as the account has too many privileges.

I'd rather write the stored proc to return the result set to the app, and have the app write it to a local file if you want that. This is something apps do really well and SQL isn't really the best tool for witting files. If this isn't an option, have the proc write to a shared folder or a folder on a file server. If you want you can have the file naming convention based on the client name, so the client will know which file to use, but this isn't needed as long as the names are unique.

If you use the app host name in the file name the application would need to pass on its host name to the stored proc. This is not a SQL server issue.

If you just create unique file names from the stored proc, the stored proc would need to return the file name to the app. Doing it this way has one problem, and that is there is no way of linking the file to a particular client outside of current connection.

Cheers

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

YTZ SSCommitted Points: 1707 More actions · Answer 4

I am not sure the architecure of your applications. But this script can offer the machine from SQL Server side:

SELECT 'MachineName', SERVERPROPERTY('MachineName')

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 5

I'm in favor for the options Leo.Miller provided :w00t:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Simon Facer SSCertifiable Points: 5427 More actions · Answer 6

All the options presented here are better than pushing files to client machines (I personally like the concept of pushing the file to a share that the clients can access, though as a DBA (and therefore jealous guardian of my servers), I would not put it on the SQL host server, put it on a file server like Leo Miller suggested). However, if you REALLY want to try to push the file, you can use sp_who2 and @@SPID to identify the connection source HostName. Of course, this assumes a 2-tier approach, where the client connects directly to the SQL Instance, if you have a n-tier approach (n > 2), I think you're pretty much out of luck unless you want to change the client app to pass the client hostname.

CREATE TABLE #Who2(

SPID1 CHAR(5) NULL,

Status NVARCHAR(30) NULL,

Login NVARCHAR(28) NULL,

HostName NVARCHAR(12) NULL,

BlkBy VARCHAR(5) NOT NULL,

DBName NVARCHAR(22) NULL,

Command NVARCHAR(16) NULL,

CPUTime VARCHAR(7) NULL,

DiskIO VARCHAR(6) NULL,

LastBatch VARCHAR(14) NULL,

ProgramName NVARCHAR(73) NULL,

SPID2 CHAR(5) NULL,

REQUESTID CHAR(5) NULL )

INSERT #Who2 EXECUTE SP_Who2

SELECT HostName

FROM #Who2

WHERE SPID1 = @@SPID

Though, consider one of the other options first.

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 7

Simon Facer (8/31/2010)
...
SELECT HostName
FROM #Who2
WHERE SPID1 = @@SPID[/code]
....

Keep in mind, the provided hostname is an attribute of your connection string data !

It is as accurate as ......

If it is coded well, it will be controlled and used by your developers, if not, it may contain inaccurate data.