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 status of a sql job

Hi Gurus,
can any one help me to get the job status of a sql job.
My requirement is as under
ordinary user (not SA) will query pass job name as argument to stored procedure and sp has to give me the job status.
Thanks in advanceYou can use SP_HELP_JOB and a user who is not a member of the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns.

Execute permissions default to the public role in the msdb database.

How to get the returning value of a stored procedure?

Hi everyone!
I am new to sql server 2005 and visual studio 2005.

I have the following simple stored procedure that checks if a user exists:
-------------------------------
ALTER PROCEDURE [dbo].[sp_Users_AlreadyExists]

@.UserName varchar(256)
AS
BEGIN
SET NOCOUNT ON;

IF (EXISTS (SELECT UserName FROM dbo.Users WHERE LOWER(@.UserName) = LoweredUserName ))
RETURN(1)
ELSE
RETURN(0)
END
-------------------------------

I use the following code to execute the procedure on visual studio:
-------------------------------
.
.
.
cmdobj As SqlCommand
cmdobj = New SqlCommand(sp_Users_AlreadyExists, connobj)
cmdobj.CommandType = CommandType.StoredProcedure
cmdobj.Parameters.AddWithValue("@.UserName", "blablalala")
cmdobj.ExecuteNonQuery()
cmdobj.Dispose()
connobj.Close()
.
.
.
-------------------------------

I expected that cmdobj.ExecuteNonQuery() would return 1 if the userblablab exists or 0 if the user doesnt, but it just return -1 (i thinkbecause no row was affected)

Does anyone knows how to retrieve the value that my stored procedure returns?

Thanx in advance!

cmdobj.parameters.add("@.RETURN_VALUE",sqldbtype.int).direction=returnvalue

Then check the parameter value after the execute like:

dim val as integer=cmdobj.parameters("@.RETURN_VALUE").value

sql

How to get the resulting XmlaWarningCollection in AMO Process?

Hi,

I'm trying to do an unattended cube processing using AMO, and I simply want to let the user know that warnings and errors occurred (and how many of them) without necessarily stopping the process.

Looking at the Cube.Process method overloads, you'll find out that it is possible to pass an XmlaWarningCollection that, allegedly, should return a warning collection populated with them.

When coding that, the collection is always returned as an empty one (0 items). Again, I'm suspicious that only warnings are allowed and not XmlaError objects or generic XmlaMessages, guessing that I'm misunderstanding the issue.

Alternative methods, like subscribing to a Trace looks overkill due to the roundtrips involved. Saving to a log file and parsing it sounds too much work for something apparently trivial...

Any additional info or suggestion?

TIA.

Jordi Rambla

SQL Server MVP

Hi Jordi,

I just checked and I think you are out of luck with the xmlaWarningCollection. I put an invalid record in a fact table for a test cube. When I process it through the BIDS UI I can see the warnings, but the collection in AMO was always empty. So I "cracked" open AMO using reflector and I found the following:

I started at Cube.Process(...)|||

Thanks for the testing, Darren.

I know about the two paths you suggest. They are the ones I'm hoping could be avoided.

The first one does not fit because the log file will be in the server, AFAIK.

The second one might be killing for network roundtrips if you have many errors (as happens some times).

I guess this is a wish to send to Microsoft.

Saludos,

Jordi Rambla

|||

Yes, I believe the log file would have to be stored in a drive on the server, or at least in a location that the server has access to. I honestly don't know what the network traffic is like with the tracing API, but if you don't need that "real time" feedback that you get from tracing then it is a lot of extra work just to get a list of errors/warnings.

The best place to log this is on http://connect.microsoft.com. If you log it there you could post a link to the item back to this thread so that anyone else that has the same issue can look up the item and view it's status and even vote for it to be addressed.

Cheers
Darren

|||

Filed.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=256428

Best regards,

Jordi

|||

Cool, I have added a validation and put in a vote. Even though you logged the issue you might want to add a vote too.

Cheers
Darren

sql

How to get the day and day number for a specified month

Hi Guys,

I'm trying to set up a report where the user can select a month, and the report will list the days and day numbers for that month. For example, if the user chooses August then the report will show:-

Wednesday 1st

Thursday 2nd

Friday 3rd

... etc

Can anybody help?

TIA.

Create a calendar table with the necessary data (as many years as you want. it will not be a big table even if you use the entire smalldatetime or datetime date range). You can then write a simple query that retrieves the days based on the month. You can also store other attributes like holidays, different calendars (fiscal, yearly, iso), weekends etc.

|||

If you let them choose the month and year, you can build the start date variable and use this script.

Code Snippet

DECLARE @.dateStart datetime

SET @.dateStart = '08/01/2007'

WHILE MONTH(@.dateStart) = 8

BEGIN

print(DATENAME(dw, @.dateStart) + ' ' + CAST(DAY(@.dateStart) AS VARCHAR(2)))

SET @.dateStart = @.dateStart + 1

END

This gives the output

Code Snippet

Wednesday 1

Thursday 2

Friday 3

Saturday 4

Sunday 5

Monday 6

Tuesday 7

Wednesday 8

Thursday 9

Friday 10

Saturday 11

Sunday 12

Monday 13

Tuesday 14

Wednesday 15

Thursday 16

Friday 17

Saturday 18

Sunday 19

Monday 20

Tuesday 21

Wednesday 22

Thursday 23

Friday 24

Saturday 25

Sunday 26

Monday 27

Tuesday 28

Wednesday 29

Thursday 30

Friday 31

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 Columns list to the end user?

Hello Nilay here,

How to display the coulumn list of the reports to the end user ?

Thanks,I don't understand the question. Is it

a) How to prompt the user for the columns he/she wants to display, or
b) How to display a column containing a list of reports?|||Hi, I want to show the All coumns, which are in the report, to the end user. So user can select his usefull columns and the selected coulmn should only show on the report and other should be hide.

Regards,
Nilay|||

In SQL Server 2005, you can use a multi-value parameter to prompt the user. Then you can write an expression/custom function to parse the output of the parameter. Call that expression/custom function in the visibility expression for the column in your table/matrix.

You'll need to make sure the choices in your parameter list are are kept in sync with your fields list, that's something that we don't provide by default.

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

How to get the Columns list to the end user?

Hello Nilay here,

How to display the coulumn list of the reports to the end user ?

Thanks,I don't understand the question. Is it

a) How to prompt the user for the columns he/she wants to display, or
b) How to display a column containing a list of reports?|||Hi, I want to show the All coumns, which are in the report, to the end user. So user can select his usefull columns and the selected coulmn should only show on the report and other should be hide.

Regards,
Nilay|||

In SQL Server 2005, you can use a multi-value parameter to prompt the user. Then you can write an expression/custom function to parse the output of the parameter. Call that expression/custom function in the visibility expression for the column in your table/matrix.

You'll need to make sure the choices in your parameter list are are kept in sync with your fields list, that's something that we don't provide by default.

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

sql

how to get tables in db?

How do I get the list of user tables in a database?

help plz..

There are three ways to get the list of user tables in a database. The first method is by querying the [dbo].[sysobjects] system table. The [dbo].[sysobjects] contains one row for each object, such as constraint, table, view, stored procedure, function and so on, created within a database. To determine the type of object, you will query the [xtype] column which contains the object type. For user tables the [xtype] value is 'U' which stands for user tables.

SELECT [Name] FROM [dbo].[sysobjects]

WHERE [xtype] = 'U'

The second method is by querying the [Information_Schema].[Tables] system view. The [Information_Schema].[Tables] system view contains one row for each table in the current database for which the current user has permissions. This viw is based on the [dbo].[sysobjects] system table. The [Information_Schema].[Tables] system view will also include views in the list. To filter out just the user tables, you will only output those records where the [Table_Type] is 'BASE TABLE', as can be seen from the following query:

SELECT * FROM [Information_Schema].[Tables]

WHERE [Table_Type] = 'BASE TABLE'

The third method of listing the user tables in a database is by using the sp_tables system stored procedure. The sp_tables system stored procedure returns a list of objects that can appear in a FROM clause. Since you are only concerned with user tables and not system tables or views, you must set the @.table_type parameter to "'TABLE'", as can be seen from the following query:

EXEC sp_tables @.table_type = "'TABLE'|||

in sql 2005 you have a catalog view called sys.tables

select *From sys.tables

From BOL : Returns a row for each table object, currently only with sys.objects.type = U.

Madhu

|||thx for a reply... sys.tables is a gud way|||

That's all good, but suppose I want to see all databases on a server that contain a certain table. I do that to find a candidate database , on servers where there are user databases that I don't want to SELECT from.

What system table shows me all the databases that have a specific table name?

The code below will work, but is there a better way?

CREATE TABLE ##MyTable(MyDB varchar(80));

EXEC sp_msforeachdb 'INSERT ##MyTable SELECT table_catalog

FROM ?.information_schema.tables

WHERE table_name like ''account%''';

SELECT * from ##MyTable;

how to get tables in db?

How do I get the list of user tables in a database?

help plz..

There are three ways to get the list of user tables in a database. The first method is by querying the [dbo].[sysobjects] system table. The [dbo].[sysobjects] contains one row for each object, such as constraint, table, view, stored procedure, function and so on, created within a database. To determine the type of object, you will query the [xtype] column which contains the object type. For user tables the [xtype] value is 'U' which stands for user tables.

SELECT [Name] FROM [dbo].[sysobjects] WHERE [xtype] = 'U'

The second method is by querying the [Information_Schema].[Tables] system view. The [Information_Schema].[Tables] system view contains one row for each table in the current database for which the current user has permissions. This viw is based on the [dbo].[sysobjects] system table. The [Information_Schema].[Tables] system view will also include views in the list. To filter out just the user tables, you will only output those records where the [Table_Type] is 'BASE TABLE', as can be seen from the following query:

SELECT * FROM [Information_Schema].[Tables] WHERE [Table_Type] = 'BASE TABLE'

The third method of listing the user tables in a database is by using the sp_tables system stored procedure. The sp_tables system stored procedure returns a list of objects that can appear in a FROM clause. Since you are only concerned with user tables and not system tables or views, you must set the @.table_type parameter to "'TABLE'", as can be seen from the following query:

EXEC sp_tables @.table_type = "'TABLE'|||

in sql 2005 you have a catalog view called sys.tables

select *From sys.tables

From BOL : Returns a row for each table object, currently only with sys.objects.type = U.

Madhu

|||thx for a reply... sys.tables is a gud way|||

That's all good, but suppose I want to see all databases on a server that contain a certain table. I do that to find a candidate database , on servers where there are user databases that I don't want to SELECT from.

What system table shows me all the databases that have a specific table name?

The code below will work, but is there a better way?

CREATE TABLE ##MyTable(MyDB varchar(80));

EXEC sp_msforeachdb 'INSERT ##MyTable SELECT table_catalog

FROM ?.information_schema.tables

WHERE table_name like ''account%''';

SELECT * from ##MyTable;

How to get table records position in comparison to other records based on numeric field?

Hi,

Let's say I have 1000 registered users in database table and each of them has numeric ranking value.

How can I get the position of each user in comparison to other users ranking value?

If you're using SQL Server 2005, you can use the row_number() function. Are you?

Don

|||Hi,|||

Hi,

Yes, I am. Can you post some example code?

|||maybe you can post your table DDL, some sample data and the result that you want ?|||

Hi,

Solution found! Here's the code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>"

SelectCommand="SELECT [usernickname], ROW_NUMBER() OVER(ORDER BY userrankavg DESC) AS 'position' FROM [dbusers]"
</asp:SqlDataSource>

|||

Hi,

That doesn't actually solve my problem.

The code does display position for each record when all records are selected from table, but doesn't when WHERE attribute is specified.

How can I get position for a specific record that I specify with WHERE attribute?

How to get rows user defined range?

Hello,
I need a keywork like LIMIT(in oracle) which let me to get rows, I defined.
For exaple I want to show records from 100. record to 200. record!
In oracle I could do like this
Select * from Customer limit 100, 100
I could do a complex query so it let me to get what I want, but I don't
think it has a performance.
Is there a key word like LIMIT in Sql Server?http://www.aspfaq.com/show.asp?id=2120
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"s" <ss@.hotmail.com> wrote in message
news:OUYvbqM0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need a keywork like LIMIT(in oracle) which let me to get rows, I
> defined. For exaple I want to show records from 100. record to 200.
> record!
> In oracle I could do like this
> Select * from Customer limit 100, 100
> I could do a complex query so it let me to get what I want, but I don't
> think it has a performance.
> Is there a key word like LIMIT in Sql Server?
>|||Generate a quota query with a ranking value. You can do all sorts of range
related tricks with such a value. Search the archives of this newsgroup for
some examples.
Anith|||Is LIMIT X,Y part of ANSI-SQL ?
I had a look, but I could only find it as a keyword, not actually defined
what it was for, syntax etc.
"s" <ss@.hotmail.com> wrote in message
news:OUYvbqM0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need a keywork like LIMIT(in oracle) which let me to get rows, I
defined.
> For exaple I want to show records from 100. record to 200. record!
> In oracle I could do like this
> Select * from Customer limit 100, 100
> I could do a complex query so it let me to get what I want, but I don't
> think it has a performance.
> Is there a key word like LIMIT in Sql Server?
>|||>> Is LIMIT X,Y part of ANSI-SQL ?
No, I think it is a MySQL dialect. Most prominent SQL variants have some
syntax that partially support quota queries.
Anith

How to get record count only from FTS

Hi,
Is there a way to get the FTS system to return you the count of matches
only, without passing back all the key ids.
Basically if a user doesn't find what they are looking for with their
initial multi term search, i.e '10k resistor tomatoes'.
I want to display a list of the counts for each word. 10k = 500,
resistor = 46000, tomatoes = 5. This will help them refine their
search.
select count(*) from freetexttable([MyCatName],*,'resistor') seems
quite slow where there are a large number of matches as I assume it's
passing the data back for SQL to count. Is there a way to just get the
count it came up with.
Thanks,
No, there is no way to do this using SQL FTS, some of the Microsoft Search
engines return a hitcount value which is the raw number of hits for all
search tokens.
You could maintain a count should you shred all documents in an inverted
file index - this will require a lot of work however.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Martin" <bigmarts@.hotmail.com> wrote in message
news:1169052866.809003.281110@.a75g2000cwd.googlegr oups.com...
> Hi,
> Is there a way to get the FTS system to return you the count of matches
> only, without passing back all the key ids.
> Basically if a user doesn't find what they are looking for with their
> initial multi term search, i.e '10k resistor tomatoes'.
> I want to display a list of the counts for each word. 10k = 500,
> resistor = 46000, tomatoes = 5. This will help them refine their
> search.
> select count(*) from freetexttable([MyCatName],*,'resistor') seems
> quite slow where there are a large number of matches as I assume it's
> passing the data back for SQL to count. Is there a way to just get the
> count it came up with.
> Thanks,
>
|||Hello Martin,
Is this SQL 2000 or SQL 2005. The latter is orders of magintude better at
this.
We cache keyword counts to achieve something similar
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi,
> Is there a way to get the FTS system to return you the count of
> matches only, without passing back all the key ids.
> Basically if a user doesn't find what they are looking for with their
> initial multi term search, i.e '10k resistor tomatoes'.
> I want to display a list of the counts for each word. 10k = 500,
> resistor = 46000, tomatoes = 5. This will help them refine their
> search.
> select count(*) from freetexttable([MyCatName],*,'resistor') seems
> quite slow where there are a large number of matches as I assume it's
> passing the data back for SQL to count. Is there a way to just get
> the count it came up with.
> Thanks,
>
|||Hi, it's 2005.
It's not a major issue, current method of counting the results is
actually performing ok.
Shredding inverted file indexes sounds interesting. Any pointers of
where to look for info on what you were suggesting..?
I assume you
On 18 Jan, 20:11, Simon Sabin <SimonSa...@.noemail.noemail> wrote:
> Hello Martin,
> Is this SQL 2000 or SQL 2005. The latter is orders of magintude better at
> this.
> We cache keyword counts to achieve something similar

How to get permissions on a SQL databse table for a user group defined in AD

Hi, i am trying to find permissions on SQL server database tables for a usergroup defined in Active Directory.

there is one function in SQL : “SELECT * FROM fn_my_permissions('TableName', 'OBJECT')”

This function get me the permission on TableName table for the current user. but i want that inforamtion for a user group defined in AD.

Is tehre any way to acheive that?

-Mani

The best (and probably only) way is to login as a member of the AD Group and run the query on sys.fn_my_permissions.

HTH,

-Steven Gott

SDE/T

SQL Server

How to get permissions on a SQL databse table for a user group defined in AD

Hi, i am trying to find permissions on SQL server database tables for a usergroup defined in Active Directory.

there is one function in SQL :“SELECT*FROM fn_my_permissions('TableName','OBJECT')”

This function get me the permission on TableName table for the current user. but i want that inforamtion for a user group defined in AD.

Is tehre any way to acheive that?

-Mani

The best (and probably only) way is to login as a member of the AD Group and run the query on sys.fn_my_permissions.

HTH,

-Steven Gott

SDE/T

SQL Server

How to get months

I need to get all the data based on the user selection of the months. So, I will provide a dropdown will all list of the months. When user pick the month, for example January, then I will show all the invoice on January. How to get the month of the year in the where clause in T-SQL?

TIA

...WHERE MONTH(yourDateColumn) = @.selectedvaluefromDL|||

Thank you Dinakar,

But I want to take the specific month in particular year. So, if I say month(o.InvoiceDT) = 6; it will take only June 2006.

TIA

|||Can you provide an example of what the user input will be and some sample data in your table and the expected result from the sql query?|||

The dropdown would be January - December which has value 1-12. Then, my table is Order table which has InvoiceDT = mm/dd/yyyy (ex. 11/30/2006).

Then, I want to write in my query such as: WHERE InvoiceDT = @.Month ??

Since, I only get the input from the user as Month, how can I Get the month of the current year.

Hope this helps

|||

hi ,

ideally you should also provide list of years but you can try following

select * from table where month(datefield) = dropdown.selectedvalue and year(datefield) = year(getdate())

regards,

satish.

|||Thank you satish|||

no problems,

cheers

satish

how to get minimum value from a list of user defined values

i've three variables in my stored proc. & i want get the least of them.
i know i can do that using case or if statements but i want to does
T-SQL provides any other way of doing it.
regards,
rameshHere you go
DECLARE @.num1 int
DECLARE @.num2 int
DECLARE @.num3 int
SET @.num1 = 5
SET @.num2 = 3
SET @.num3 = 7
SELECT Min(Num)
FROM
(SELECT @.num1 AS Num
UNION ALL
SELECT @.num2
UNION ALL
SELECT @.num3) T
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<rameshsaive@.gmail.com> wrote in message
news:1139377057.261785.191850@.f14g2000cwb.googlegroups.com...
> i've three variables in my stored proc. & i want get the least of them.
> i know i can do that using case or if statements but i want to does
> T-SQL provides any other way of doing it.
>
> regards,
> ramesh
>

how to get driopdown for year

how can i display the dropdown for year values in sql reporting

i want to accept the value for year as parameter & also want user to select the value from dropdown that will display only the year values.

What product are you using?

Can you give more details about your situation.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

HOW TO GET DETAILS OF DB_OWNERS ON A SQL SERVER.

Hi,
This is kiran from India. I would like to ask one question regarding the
MS-SQL 2000
"db_owner" access permission user.
On one server we have 50 databases and indivual database have there own
db_owner access assigned to WINODWS 2000 DOMAIN LOCAL USERS and Local users
and Global users .
My question is how to retirve the list of users who are members of DB_OWNERS
and WINODWS 2000 DOMAIN LOCAL USERS and Local users and Global users using
SQL script or by any other means other than going to each database and check
the users in DB_OWNERS.
Please help me ....
Kiran S
Hi
sp_helplogins
"KIRAN MAHA PATRO.S" <KIRANMAHAPATROS@.discussions.microsoft.com> wrote in
message news:C9BCE7B7-BE41-4A88-87D9-F483F6929201@.microsoft.com...
> Hi,
> This is kiran from India. I would like to ask one question regarding the
> MS-SQL 2000
> "db_owner" access permission user.
> On one server we have 50 databases and indivual database have there own
> db_owner access assigned to WINODWS 2000 DOMAIN LOCAL USERS and Local
users
> and Global users .
> My question is how to retirve the list of users who are members of
DB_OWNERS
> and WINODWS 2000 DOMAIN LOCAL USERS and Local users and Global users using
> SQL script or by any other means other than going to each database and
check
> the users in DB_OWNERS.
> Please help me ....
> Kiran S
>