How to get the windows current user currently logged from SQL Server

Hi I am using exec master..xp_cmdshell "ECHO %USERNAME%" to get the windows
user name currently logged in. But it gives me the NULL value. I even tried
exec master..xp_cmdshell "ECHO SET %USERNAME%" but it doesnt help me.
Does any body have idea how to get the windows user name who is currently lo
gged from SQL Server.
Thanks for you help.
HemanthTry suser_sname()
Ray Higdon MCSE, MCDBA, CCNA
--
"Hemanth" <kamishetty@.hotmail.com> wrote in message
news:8843EE8B-D217-4DA1-A47F-D7D19804C819@.microsoft.com...
> Hi I am using exec master..xp_cmdshell "ECHO %USERNAME%" to get the
windows user name currently logged in. But it gives me the NULL value. I
even tried exec master..xp_cmdshell "ECHO SET %USERNAME%" but it doesnt
help me.
> Does any body have idea how to get the windows user name who is currently
logged from SQL Server.
> Thanks for you help.
> Hemanth
>sql

How to get the value of System::TaskName of a higher up level?

Hi there,

I was wondering how can I get the value of System::TaskName of a higher scope when I have a Master Package that have several sequence task, data flow tasks and execute package tasks. For each task inside this Master Package on the Post-Execute event handler I have a script task that logs the execution of each task.

After running this master package I saw in my db that I have a row for every single tasks executed in the process and not only the tasks that exist in the master package. For instance, for simplicity let's say my master package looks like this:

SQL Task Script
|
Exec Package 2
|
Exec Package 3
|
Exec Package 4

Package 2,3 and 4 have a SQL Task Script with name Execute SQL Task Package 2, Execute SQL Task Package 3, Execute SQL Task Package 4.

Here's what I got in my db:

Execute SQL Task
Execute SQL Task Package 2
Package2
Execute Package 2
Execute SQL Task Package 3
Package3
Execute Package 3
Execute SQL Task Package 4
Package4
Execute Package 4

I see two TaskName variables in the Variable window, one with OnPostExec scope and the other with Execute Package 2 for instance. I want to get the value of System::TaskName with Execute Package 2 scope.

I want to see in my db only the tasks in bold. Any ideas of how can I do this? I hope you understand what I'm trying to achieve.

Thanks!

Each eventhandler has a variable called @.[System:ropogate] which should be all that you need.

System Variables

(http://msdn2.microsoft.com/en-us/library/ms141788.aspx)

I could tell you how to use it but you'll probably learn more by fiddling with it yourself. its fairly self-explanatory.

-Jamie

|||Hi Jamie,

I knew about the Propagate variable and I tried setting it to false for every post-execute event handler in my master package. I still got multiple records in my table.

Then I tried with False at the package level on every subpackage my master package is calling but I got the same results.

Finally, I remove all my post-execute event handlers from my master package and leave just one, at the master package level and set the Propagate to false and I still got all the subtasks logged into my table.

Could you tell me how to use this variable?

Thanks!|||

You need to set it on the eventhandlers that are scoped to the tasks for which you don't want to do any logging. So, if you have a task called "Ricardo's Execute SQL Task" in a child package that you don't want to capture information for, set that task's eventhandlers to ahve System:ropogate=FALSE.

-Jamie

|||Hi Jamie,

Well, I guess I'm doing something wrong because I set to False the Propagate variable on the Execute SQL Task of my first child package and also at the package level. So now, the Post-Execute event handler for these two objects have nothing but I changed the value of Propagate from True to False.

In my parent package, I disabled all the post-execute event handlers except for the Execute Package Task that calls Package 2.

I got 3 rows inserted in my table:

Taskname = Script Task Post-Exec Package 2 - SourceName = Execute SQL Task Package 2
Taskname = Script Task Post-Exec Package 2 - SourceName = Package2
Taskname = Script Task Post-Exec Package 2 - SourceName = Execute Package 2

the values from above are from System::TaskName and System:ourceName

Thanks!

How to get Full-Text to not ingore special characters

We just implemented a full-text index on our product master table,
however the users are now screaming because they cannot search on some
of the special characters that are commonly found in our product
descriptions, specifically the #, %, and period (.)

These characters are not in the Noise file, so no luck in just
deleting them from there, but somehow, the full-text is automatically
ignoring those characters, and we would like for the full-text to not
ignore these characters.

Any insight or help would be appreciated.

Thanks"dotnetprogrammer" <tim_60173@.yahoo.com> wrote in message
news:e856daff.0309050524.703c112@.posting.google.co m...
> We just implemented a full-text index on our product master table,
> however the users are now screaming because they cannot search on some
> of the special characters that are commonly found in our product
> descriptions, specifically the #, %, and period (.)
> These characters are not in the Noise file, so no luck in just
> deleting them from there, but somehow, the full-text is automatically
> ignoring those characters, and we would like for the full-text to not
> ignore these characters.

You're probably better off asking in
news:microsoft.public.sqlserver.fulltext

The experts there can help you.

Off the top of my head, I don't believe you can search on those, but they
will be able to tell you for sure.

> Any insight or help would be appreciated.
> Thanks

how to get disk total space info?

hi all,
I know that master..xp_fixeddrives can give you the free space info for each
drive on your sql server.
but is there any proc that can return the total disk space?
also, anyone know how to see the code inside of master..xp_fixeddrives?
many thanks!!
JJI don;t knoiw of any way for SQL t odo that... But...
Don't you know how big the disks are ?
These values will not change often.
Create a table and put this data in it...
"JJ Wang" wrote:

> hi all,
> I know that master..xp_fixeddrives can give you the free space info for ea
ch
> drive on your sql server.
> but is there any proc that can return the total disk space?
> also, anyone know how to see the code inside of master..xp_fixeddrives?
> many thanks!!
> JJ|||xp_fixeddrives is an extended stored proc. written in C/C++ and compiled.
It most likely calls the GetDiskFreeSpaceEx API function to get the free
space. You could always write your own extended stored proc to make the
same call, but utilize the other information returned like total disk size.
Here's more info. on that:
http://support.microsoft.com/defaul...b;en-us;231497.
It might be easier just to do it from your client app - most languages have
easier ways to get at this particular info., and I think I read that SQL
2005 will also offer easier methods to access this - but someone else will
need to give you specifics on that.
"JJ Wang" <JJ Wang@.discussions.microsoft.com> wrote in message
news:B388A868-9058-45D9-A99F-38BD00B8B0B4@.microsoft.com...
> hi all,
> I know that master..xp_fixeddrives can give you the free space info for
> each
> drive on your sql server.
> but is there any proc that can return the total disk space?
> also, anyone know how to see the code inside of master..xp_fixeddrives?
> many thanks!!
> JJ|||CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
/* Returns the total capacity for any drive letter */
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13),
@.drv INTEGER, @.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
David Portas
SQL Server MVP
--|||thank you guys so much for all your useful tips!!
Dave,
thank you so much for the function of yours, it works like a charm!!
have you been using this in your production servers? is there anything I
should look out in this function?
many thanks!!
JJ
"JJ Wang" wrote:

> hi all,
> I know that master..xp_fixeddrives can give you the free space info for ea
ch
> drive on your sql server.
> but is there any proc that can return the total disk space?
> also, anyone know how to see the code inside of master..xp_fixeddrives?
> many thanks!!
> JJ|||I have used this in production - not as something to be called
regularly but just as required to refresh a table of drive sizes. I
agree with CBretana that it makes sense to retain this information in a
table but in a large SAN environment with many drive arrays (we were
managing about 15 terabytes across 6 servers) it can be a difficult to
keep that information accurate and up-to-date.
You need to be wary about memory and resource leaks when calling COM
objects with sp_OACreate. I haven't had problems with this one (that's
under SP3, which I assume you are at) but do test it out in your
environment.
David Portas
SQL Server MVP
--|||hi Dave,
thanks again for the wonderful info!
yes, our sql servers are at the highest sp.
how long have you been using this function on your prod servers? and how
often do you refresh your drive size table? Sounds like 'daily' is a bad
idea to run the com objects, huh? :-)
how much memory and cpu in avg do your servers have(which you run this
function on)?
thanks Dave!
JJ
"David Portas" wrote:

> I have used this in production - not as something to be called
> regularly but just as required to refresh a table of drive sizes. I
> agree with CBretana that it makes sense to retain this information in a
> table but in a large SAN environment with many drive arrays (we were
> managing about 15 terabytes across 6 servers) it can be a difficult to
> keep that information accurate and up-to-date.
> You need to be wary about memory and resource leaks when calling COM
> objects with sp_OACreate. I haven't had problems with this one (that's
> under SP3, which I assume you are at) but do test it out in your
> environment.
> --
> David Portas
> SQL Server MVP
> --
>