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 whole DDL command from EVENT_INSTANCE

Hello,
I try to save the current DDL in a table using the trigger on database ddl
events.
As usual,
DECLARE @.data XML
SET @.data = EVENTDATA()
@.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
How can I extract more then 2000 chars? Should I use a system table or
function to retrieve all the DDL command? I have SPs whith tons of chars...
Thanks,
CatalinHow about, for instance:
@.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:46BD42B9-FA1B-4697-BD7D-2E54F52F4B93@.microsoft.com...
> Hello,
> I try to save the current DDL in a table using the trigger on database ddl
> events.
> As usual,
> DECLARE @.data XML
> SET @.data = EVENTDATA()
> @.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
> How can I extract more then 2000 chars? Should I use a system table or
> function to retrieve all the DDL command? I have SPs whith tons of chars..
.
> Thanks,
> Catalin|||Thanks, but i have SPs with probably 40k chars or more... Neither varchar
(8000) is enough...
"Tibor Karaszi" wrote:

> How about, for instance:
> @.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)')
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in messag
e
> news:46BD42B9-FA1B-4697-BD7D-2E54F52F4B93@.microsoft.com...
>|||Did you try nvarchar(max)?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:989EED5F-D2F0-44EB-A43E-A2FB23BB9B5B@.microsoft.com...
> Thanks, but i have SPs with probably 40k chars or more... Neither varchar
> (8000) is enough...
> "Tibor Karaszi" wrote:
>|||Thank you, I had no ideea about (max) implementation on 2K5... (Please, don'
t
tell me that it was also available on SQL 2000...)
I am so deceived about me... After 8 years of SQL I will have to start again
from ABC... Sometimes I am so busy to find complex solutions and I am not
able to see the simplest one.
Thanks again|||> Thank you, I had no ideea about (max) implementation on 2K5... (Please, don'ted">
> tell me that it was also available on SQL 2000...)
The max datatypes are indeed new to 2005. Consider them as replacements for
the less than user
friendly text, ntext and image datatypes.

> I am so deceived about me... After 8 years of SQL I will have to start aga
in
> from ABC... Sometimes I am so busy to find complex solutions and I am not
> able to see the simplest one.
This happens to all of us. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:01DD8998-1C93-43E2-AA9A-F82694049866@.microsoft.com...
> Thank you, I had no ideea about (max) implementation on 2K5... (Please, do
n't
> tell me that it was also available on SQL 2000...)
> I am so deceived about me... After 8 years of SQL I will have to start aga
in
> from ABC... Sometimes I am so busy to find complex solutions and I am not
> able to see the simplest one.
> Thanks again

How to get the current version?

I just want to confirm if SP2 has been installed successfullly on my SQL Server Express, but I don't know where to check?

Any help would be appreciated...

Try:

SELECT SERVERPROPERTY( ProductLevel )

|||

You can also use this:

select @.@.version

sql

How to get the current User when using a general connection user

I have a VB.Net app and a SQL Server 2005 database. Users must login to use
the application, and I have an Employee table to store their details.
However, I use a common user ID to connect to the database (for reasons I
won't go into here).
My problem is, some of my triggers need to know who the current user is, and
of course I can't use the current connection information to get this as I
always get the common user ID.
Does anyone have a technique to solve this dilema? Maybe some way to set a
variable or something when I connect so that I can determine which user has
initiated the connection?You can use application roles to achieve that. Works something like this:
a) Every user has a Windows account using Windows Authentication
b) When they run the application you enable the application role
c) Inside the application's session you can look at what the username is,
including in triggers, using suser_sname()
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"David" wrote:

> I have a VB.Net app and a SQL Server 2005 database. Users must login to us
e
> the application, and I have an Employee table to store their details.
> However, I use a common user ID to connect to the database (for reasons I
> won't go into here).
> My problem is, some of my triggers need to know who the current user is, a
nd
> of course I can't use the current connection information to get this as I
> always get the common user ID.
> Does anyone have a technique to solve this dilema? Maybe some way to set a
> variable or something when I connect so that I can determine which user ha
s
> initiated the connection?
>

How to get the current member in a MDX expression?

Hi, all experts here,

Thank you very much for your kind attention.

I want to know how to get the current member in a MDX expression? (e.g, we want to get the current value'2006' for the time dimension, how can we accompalish this task?)

Hope my question is clear.

With best regards,

Yours sincerely,

There is a function called CurrentMember, but you need to ask for the current member for a given hierarchy or attribute.

eg

[Date].[Calendar].CurrentMember

If you are just after the name of the current member (as in '2006') then you would do something like the following

[Date].[Calendar].CurrentMember.Name|||

Thanks a lot.

Best regards,

How to get the current date in C# and how do I pass this date to SQL Server

I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.

So I have a question

1.) How do I get today's date in C# and how should this be passed to SQL server?

you can get the current date directly in SQL using getdate()|||Doing it in C# will return you the client system date. Getting the current date in SQL will ensure all datetime recorded in your application are in sync. If you are using the date for some sort of comparison determine the sequence of event, using the date from the client might pose some timing issue. Unless what you want is really the client's local time, you should use the Server time.|||

brgdotnet:

I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.

So I have a question

1.) How do I get today's date in C# and how should this be passed to SQL server?

to get current date in c# use DateTime.Now.ToString()......... there is also some different methods under Now... use what is appropriate for u .

if u r using a procedure it is easier.... say proc. name saveCurrentDate

String s=DateTime.Now.ToString();

String queryString="saveCurrentDate '"+s+"'"; //// or String queryString="insert int myTable(mydate) values ('"+s+"')";

hope it will hellped u

|||

Actually I am processing some records from a comma delimited .txt file. Each item seperated by a comma, will map to a field in a SQL Server database table.

The date is in the format: 20070423, that is YYMMDD

So I need to take the date value and then write it into SQL server. So this brings us to another question. Do I need to change this date format to comply with that of SQL server? If so what format does it need to be in? I need some specifics so if you could even produce a code sample for C#, that would be great.

|||You can pass in the date in format YYYYMMDD as a string. SQL Server will implicitly convert it to string. As long as you are using Universal format YYYYMMDD, it is fine. Other format like MMDDYYYY or DDMMYYYY will be depending on the language setting that you used. So stick to YYYYMMDD and you will not go wrong.|||

I am confused about one thing though? I am reading from a text file where the format is in YYMMDD. Should I store this in SQL server as a data type of datetime or smalldatetime?

If so, I definitely have a date that, I can read from the text file, but I don't have a time? What do you think? If I did use datetime, would it just append the current time to the date I entered?

|||

you should always use proper data type for the data. In this case, you should use datetime or smalldatetime to store the date.

"I can read from the text file, but I don't have a time?"
You can still use datetime data type. Just set the time to 00:00:00. For your case, as your date string is in YYYYMMDD format without time, when you insert into table, the time will be stored as 00:00:00

How to get remote server datetime

Hi All ,
I have 3 sql servers located at different time zones. Say, CST,PST,EST.
Now how can I get current time at EST,PST from the SQL server located
at CST? Is there any query to do that?
I have a stored proc located in SQL server at CST zone where I need to
query for the current date/time of the other zone sql servers.
I tried below query at CST SQL server
SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
But it always gives CST datetime.
Please reply...
Thanks
RP
Haven't tested but it should work using Openquery instead of
the 4 part name. The statement passed in the openquery is
executed on the remote server.
-Sue
On Fri, 27 Apr 2007 12:24:01 -0700, Ram
<Ram@.discussions.microsoft.com> wrote:

>Hi All ,
>I have 3 sql servers located at different time zones. Say, CST,PST,EST.
>Now how can I get current time at EST,PST from the SQL server located
>at CST? Is there any query to do that?
>I have a stored proc located in SQL server at CST zone where I need to
>query for the current date/time of the other zone sql servers.
>I tried below query at CST SQL server
>SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
>But it always gives CST datetime.
>Please reply...
>Thanks
>RP
|||Hi Sue,
It works gr8...
Thanks for the help...
"Sue Hoegemeier" wrote:

> Haven't tested but it should work using Openquery instead of
> the 4 part name. The statement passed in the openquery is
> executed on the remote server.
> -Sue
> On Fri, 27 Apr 2007 12:24:01 -0700, Ram
> <Ram@.discussions.microsoft.com> wrote:
>
>
|||Hi,
Can you please write the exact syntax you used for Openquery? I also have
requirement similar to this.
Thanks for your help.
Namwar
"Ram" wrote:
[vbcol=seagreen]
> Hi Sue,
> It works gr8...
> Thanks for the help...
>
> "Sue Hoegemeier" wrote:

how to get recordset current position ?

Hi,

I need to know how can we get the recordset current position ?

it's adodb.recordset ...

thanksExactly what do you mean by "current position"? Is there a reason you are using adodb in your ASP.NET application?|||I meant, the location of current "row" of the recordset ...
whenever I execute a movenext, it move to next "row" ...
just want to know the current data is from which "row" ...|||Well, in an ADODB recordset, you could just read the data and from there understand what data you are on. If you mean things like a "record number" then no, there is nothing I can think of (and the concept of a "record number" is really meaningless if it is a connected recordset, since rows can be added and deleted).|||ok, thanks for your answer ... have a nice day ...

How to get info in SELECT directly instead of UPDATE? Thanks.

Greetings!

I have the 3 raw data tables below, and would like to extract some
data. My current query is really cumbersome, does anyone know a better
way to get all the info (attach the right state to the lowest zip code)
in one step? Thanks a lot! Here are the details.

- Ideal Output, for each person , keep
(1) person_id (From Table A)
(2) The earliest open_date of accounts starting with 2 (From Table A)
(3) ssn (From Table B)
(4) Zip and state info (From Table C), but only keep the lowest zip and
state.

-Table A -
person_idaccountopen_date
10001220000015/15/2003
10001220000026/20/2004
10001300000012/2/2002
10002220000038/12/2004
10002220000049/15/2004
10002300000022/16/2005

-Table B -
person_idzip state
1000111111AA
1000122222CC
1000233333BB

--Table C -
person_idssn
100011234567
100022345678

-Ideal Output-
person_idmin_openssnzipstate
100015/15/2003123456711111AA
100028/12/2004234567833333BB

Here is what I did:
Select a.person_id, min(a.open_date), b.ssn, min(c.zip) as zip
,cast (0 as varchar) as state
Into output
>From TableA as a
Join TableB as b
on a.person_id=b.person_id
Join TableC as c
on a.person_id=c.person_id
Where a.account like '2%'
Group by a.person_id, b.ssn
Order by a.person_id

Update output
Set output.state=b.state
>From output as a
Join TableC as b
on a.person_id=b.person_id
and a.zip=b.zip(rong.guo@.gmail.com) writes:
> I have the 3 raw data tables below, and would like to extract some
> data. My current query is really cumbersome, does anyone know a better
> way to get all the info (attach the right state to the lowest zip code)
> in one step? Thanks a lot! Here are the details.
> - Ideal Output, for each person , keep
> (1) person_id (From Table A)
> (2) The earliest open_date of accounts starting with 2 (From Table A)
> (3) ssn (From Table B)
> (4) Zip and state info (From Table C), but only keep the lowest zip and
> state.

For these kind of these questions, it's always recommendable to post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This makes it possible to easily copy and paste into Query Analyzer to
develop a tested query. Since your sample data was not in this form,
this query is untested:

SELECT a1.person_id, a1.min_open, c.ssn, a1.min_zip, b.state
FROM (SELECT a.person_id, min_open = MIN(a.open_date),
min_zip = MIN(b.zip)
FROM table_a a
JOIN table_b b ON a.person_id = b_person_id
GROUP BY a.person_id) AS a1
JOIN table_b b ON a1.person_id = b.person_id
AND a1.min_zip = b.zip
JOIN table_c c ON a1.person_id = c.person_id

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland.

Because I am not familiar with CREATE and INSERT, I didn't use them
this time. Sorry for the inconvenience. I will certainly try it next
time.

Thanks again!

How to get FILL FACTOR

Hi,
How can i get the current "Fill Factor" for an existing
index
Thank you !
DonUse the INDEXPROPERTY function. For example:
SELECT INDEXPROPERTY(OBJECT_ID('authors'), 'UPKCL_auidind',
'IndexFillFactor')
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Donald" <Donald.huppe@.bocenor.com> wrote in message
news:0d2101c37230$66d7ef10$a501280a@.phx.gbl...
> Hi,
> How can i get the current "Fill Factor" for an existing
> index
> Thank you !
> Don|||This is a multi-part message in MIME format.
--=_NextPart_000_0166_01C3720F.D6745550
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Check out INDEXPROPERTY() in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Donald" <Donald.huppe@.bocenor.com> wrote in message =news:0d2101c37230$66d7ef10$a501280a@.phx.gbl...
Hi,
How can i get the current "Fill Factor" for an existing index
Thank you !
Don
--=_NextPart_000_0166_01C3720F.D6745550
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out INDEXPROPERTY() in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Donald" = wrote in message news:0d2101c37230$66=d7ef10$a501280a@.phx.gbl...Hi,How can i get the current "Fill Factor" for an existing =indexThank you !Don

---

--=_NextPart_000_0166_01C3720F.D6745550--