Set SysProcesses.Program_Name from OSQL or T-SQL?
-
I've searched through SQLServerCentral.com and about a zillion Google hits... I've found ways to get JDBC, ADODB, ODBC, and a couple of other drivers to register a user-defined value that will appear in Master.dbo.SysProcesses.Program_Name during connection time...
...but I can't find a method to do it from OSQL or T-SQL for a given proc or Command Shell Batch that runs OSQL...
Anyone got any ideas about that?
Yep, I am familiar with the -H (workstation name) parameter of OSQL... we just want to force the Program_Name column in SysProcesses for various batch processes that run SQL either as an input file or a call to a stored proc through OSQL, if possible.
Thanks, folks...
--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)
Intro to Tally Tables and Functions -
Cant you append it to the connection string as "Application Name=Jeffs App;" or am i missing what you are asking for?
-
Thanks for the response, Andrew. Yeah... kinda missing what I'm asking for. OSQL doesn't have a "connection string" nor a paramter for it, but the security folks want me to devise some way to populate Program_Name in the SysProcesses table as if there were. They have the bee up their nose that if I can populate the Workstation column from OSQL with a bit of custom info, I should be able to do the same from OSQL. I'm thinking NOT but if anyone knows, this forum is likely the best bet.
Sure do appreciate the try, Andrew. If I find the answer or end up making one, I'll be sure to post it.
--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)
Intro to Tally Tables and Functions -
Sorry I was missing the boat on that one.
If you felt devious you could directly update the sysprocesses table as part of the executed query =)
If your requirement was to use any command line Sql client (for scripting) i'm sure theres an alternative to OSQL that you could use.
-
I agree... so far as I'm concerned, writing directly to SysProcesses is not an option but a sub for OSQL may be what I have to find (or build). I suppose I could write a bit of VB to exec the procs using a connection but was really trying to avoid making such a change... we have about 600 scheduled jobs on the scheduling system that would need to be changed.
Heh... just had a bit of a brain fart... the scheduling system was created in Java... maybe I can get them to mod the system to use a JDBC connection to exec the procs. I think THAT's gonna be the ticket...
Thanks for helping me think about this, Andrew.
--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)
Intro to Tally Tables and Functions -
dig this if JDBC is'nt working well enough.
-
Cool... Thanks again, Andrew.
Anyone else? It would be really cool if this could be done through T-SQL without modifying SysProcesses directly... don't think it can be done but its worth asking the question if someone knows how...
--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)
Intro to Tally Tables and Functions -
Will this work for you?
Does not modify sysprocesses but lets you catch user defined name and associate it with whatever security context you deal with.
Create table "SomeTable" in pubs on your server with single column "ProgramName"
Then copy the following script and past it into command prompt.
osql -UHOSTNAME\jeffm -E -SServerName -dpubs -q""
declare @var nvarchar(250)
set @var = 'some bloody program'
print @var
insert into dbo.SomeTable (ProgramName) select @var
go
exit
Check out table dbo.SomeTable.
_____________
Code for TallyGenerator -
Sometimes, ya just can't see the forest for the trees
... of course a separate table would do... if I save the SPID as well, then I can relate that new table to SysProcesses... I can have my cake and eat it too!Thanks, ol' friend.
--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)
Intro to Tally Tables and Functions -
Happy to help you out of that tree.
Did not mention SPID because it's too obvious for such old wolves
And there are plenty of other options to choose from depending on your task requirements.
_____________
Code for TallyGenerator -
I thought the osql was a separate process and so would get its own spid. How will you know which row in the table the child process needs to get?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
I'm pretty sure that when OSQL is used, any procs called by it are given the same spid. Of course, that's why testing is important.
Thanks for the heads-up...
--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)
Intro to Tally Tables and Functions -
It will be the same SPID, because it's gonna be connected to the server via the same connection.
And dynamic SQL called from there will have the same @@SPID despite all scope isolations.
Only if you run OPENQUERY it will create new connection with another @@SPID.
_____________
Code for TallyGenerator -
>> I'm pretty sure that when OSQL is used, any procs called by it are given the same spid. <<
Yes, of course.
But the osql task itself will have a different spid than the task that started it. I thought the idea was to pass data to the osql task itself from a non-osql task, not from the osql task to a proc that was called by the osql task. It's my understanding that the non-osql task will have a different spid than the osql task.
Or did I misunderstand what's going on?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
>>Or did I misunderstand what's going on?
Just a bit, Scott... the OSQL would be executed by a scheduled "DOS Batch" process... not from SQL... that would make the SPIDs match up especially since there's no OPENQUERY or dips using xp_CmdShell...
Sure do appreciate the feedback, though...
--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)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply