Adding Sequence number in SQL

Being a newbie to SQL programming, was hoping someone would be able to
guide me in the right direction.
I have a table that has the following data
Center Emp_ID
11 112
11 2254
11 346
12 456
12 138
13 8761
Etc, etc
I want to add a Sequence number which resets to 1 by center. So I want
it to look like this
List_ID Center Emp_ID
1 11 112
2 11 2254
3 11 346
1 12 456
2 12 138
1 13 8761
I've done the following:
SELECT TOP 100 PERCENT FIRST_NAME, LAST_NAME,
(SELECT COUNT(*)
FROM dbo.Planning_Heads e2
WHERE e2.ACCT_CD <=
dbo.Planning_Heads.ACCT_CD) AS List_ID, ACCT_CD
FROM dbo.Planning_Heads
ORDER BY ACCT_CD
but it's no "restarting" the List_Id or incrementing it properly
Sorry for the long post, but thought it would be best to give as much
info as I couldWhich criteria can we use to tell sql server that Emp_ID = 346 goes after
Emp_ID = 2254, other than analyzing row by row?.
In db [northwind], the orders for each customer are stored in table [orders]
and the column [orderid] is an identity one that we can use to sort them
chronologically by customer.
use northwind
go
select
count(*) as rank,
a.orderid,
a.employeeid
from
dbo.orders as a
inner join
dbo.orders as b
on a.employeeid = b.employeeid
and a.orderid >= b.orderid
group by
a.employeeid,
a.orderid
order by
a.employeeid,
rank
go
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"kimmal" wrote:

> Being a newbie to SQL programming, was hoping someone would be able to
> guide me in the right direction.
> I have a table that has the following data
> Center Emp_ID
> 11 112
> 11 2254
> 11 346
> 12 456
> 12 138
> 13 8761
> Etc, etc
> I want to add a Sequence number which resets to 1 by center. So I want
> it to look like this
> List_ID Center Emp_ID
> 1 11 112
> 2 11 2254
> 3 11 346
> 1 12 456
> 2 12 138
> 1 13 8761
> I've done the following:
> SELECT TOP 100 PERCENT FIRST_NAME, LAST_NAME,
> (SELECT COUNT(*)
> FROM dbo.Planning_Heads e2
> WHERE e2.ACCT_CD <=
> dbo.Planning_Heads.ACCT_CD) AS List_ID, ACCT_CD
> FROM dbo.Planning_Heads
> ORDER BY ACCT_CD
> but it's no "restarting" the List_Id or incrementing it properly
> Sorry for the long post, but thought it would be best to give as much
> info as I could
>

Adding permissions to an AD user on a remote database

I was wondering if someone can point me in the right direction. I am lookin
g
for a way to automate (in a script format) the addition of a user to a
database on a remote server. We have an application that requires that the
user have Owner permissions on 3 databases and 1 store procedure. Manually
connectiing to 400 workstations will be a huge pain..I'm not sure what you mean by 400 workstations - if a login
needs access to databases and stored procedures, you would
set that at the server level in SQL Server and in whatever
databases.
Unless there is something in the application itself that has
to be coded which would be an application issue
If you are just trying to add the login and user to the
databases, you can script these using t-sql commands. See
books online topics for sp_grantlogin, sp_grantdbaccess,
sp_addrolemember.
-Sue
On Mon, 27 Feb 2006 13:11:27 -0800, Chad T
<ChadT@.discussions.microsoft.com> wrote:

>I was wondering if someone can point me in the right direction. I am looki
ng
>for a way to automate (in a script format) the addition of a user to a
>database on a remote server. We have an application that requires that the
>user have Owner permissions on 3 databases and 1 store procedure. Manually
>connectiing to 400 workstations will be a huge pain..|||Just to clarify... the 400 workstations have sql running on it for an
application that has an offline mode...
The previous post mentioned to use: sp_grantlogin, sp_grantdbaccess,
sp_addrolemember which got me to here <see working code below>
How can I verify or check to see a user is already a member of the role ONE
database?
Example: If I am trying to add a user to the 'db_owner' role on the DB1
database and they are already listed as a member I would like to skip the
sp_addrolemember command.
This is all I could find:
IF SUSER_SID('domain\userid') IS NULL begin
but this only seems to find out if the user has a sid, not if the user is
listed in the DB1 database as a db_owner
Any help would be greatly appreciated.
This is my code so far...
'++++++++++++++++++++++++++++++++++++++
Domain = "DDDDDDDD"
Userid = "HHHHHHHH"
RemoteSQL = "WWW"
full_login = domain & "\" & userid
strconn = "Provider='SQLOLEDB'; Data Source='"&remotesql&"'; Initial
Catalog='master'; User Id='XXXXXX'; Password='''';"
Set conn = CreateObject("adodb.connection")
conn.Open strconn
m = m & "USE DB0" & vbCrLf
m = m & "EXEC sp_grantlogin '"& full_login &"'" & vbCrLf
m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
vbCrLf
m = m & "USE DB1" & vbCrLf
m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
vbCrLf
m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
m = m & "USE DB2" & vbCrLf
m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
vbCrLf
m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
'for the store procedure
m = m & "Use DB3" & vbCrLf
m = m & "GRANT EXECUTE ON CustOrdersOrders TO ["& full_login &"]" & vbCr
Lf
conn.execute(m)
Conn.close
set conn = nothing
"Sue Hoegemeier" wrote:

> I'm not sure what you mean by 400 workstations - if a login
> needs access to databases and stored procedures, you would
> set that at the server level in SQL Server and in whatever
> databases.
> Unless there is something in the application itself that has
> to be coded which would be an application issue
> If you are just trying to add the login and user to the
> databases, you can script these using t-sql commands. See
> books online topics for sp_grantlogin, sp_grantdbaccess,
> sp_addrolemember.
> -Sue
> On Mon, 27 Feb 2006 13:11:27 -0800, Chad T
> <ChadT@.discussions.microsoft.com> wrote:
>
>|||So the last piece you are looking for is the IS_MEMBER
function. That will tell you if a user is a member of the
specified database role.
-Sue
On Mon, 27 Feb 2006 20:47:26 -0800, Chad T
<ChadT@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Just to clarify... the 400 workstations have sql running on it for an
>application that has an offline mode...
>The previous post mentioned to use: sp_grantlogin, sp_grantdbaccess,
>sp_addrolemember which got me to here <see working code below>
>How can I verify or check to see a user is already a member of the role ONE
>database?
>Example: If I am trying to add a user to the 'db_owner' role on the DB1
>database and they are already listed as a member I would like to skip the
>sp_addrolemember command.
>This is all I could find:
>IF SUSER_SID('domain\userid') IS NULL begin
>but this only seems to find out if the user has a sid, not if the user is
>listed in the DB1 database as a db_owner
>Any help would be greatly appreciated.
>
>This is my code so far...
>'++++++++++++++++++++++++++++++++++++++
>Domain = "DDDDDDDD"
>Userid = "HHHHHHHH"
>RemoteSQL = "WWW"
>full_login = domain & "\" & userid
>strconn = "Provider='SQLOLEDB'; Data Source='"&remotesql&"'; Initial
>Catalog='master'; User Id='XXXXXX'; Password='''';"
>Set conn = CreateObject("adodb.connection")
>conn.Open strconn
>m = m & "USE DB0" & vbCrLf
>m = m & "EXEC sp_grantlogin '"& full_login &"'" & vbCrLf
>m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
>vbCrLf
>m = m & "USE DB1" & vbCrLf
>m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
>vbCrLf
>m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
>m = m & "USE DB2" & vbCrLf
>m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
>vbCrLf
>m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
>'for the store procedure
>m = m & "Use DB3" & vbCrLf
>m = m & "GRANT EXECUTE ON CustOrdersOrders TO ["& full_login &"]" & vbC
rLf
>conn.execute(m)
>Conn.close
>set conn = nothing
>"Sue Hoegemeier" wrote:
>|||I am having issues with IS_Member...
"Indicates whether the current user is a member of the specified Microsoft
Windows group or Microsoft SQL Server database role. "
I did find this:
sp_helplogins 'domain\userid'
The problem with this is that the records that I want are in the second
record set.
Is there a way I can loop through the second record set instead of the first
one?
"Sue Hoegemeier" wrote:

> So the last piece you are looking for is the IS_MEMBER
> function. That will tell you if a user is a member of the
> specified database role.
> -Sue
> On Mon, 27 Feb 2006 20:47:26 -0800, Chad T
> <ChadT@.discussions.microsoft.com> wrote:
>
>|||Okay...so you actually want to know if the login exists -
not if it's a member of a database role. You can check if
the login exists before you add it using something like:
if not exists (select * from master.dbo.syslogins where
loginname = N'domain\userid')
exec sp_grantlogin N'domain\userid'
-Sue
On Tue, 28 Feb 2006 21:42:26 -0800, Chad T
<ChadT@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am having issues with IS_Member...
>"Indicates whether the current user is a member of the specified Microsoft
>Windows group or Microsoft SQL Server database role. "
>I did find this:
>sp_helplogins 'domain\userid'
>The problem with this is that the records that I want are in the second
>record set.
>Is there a way I can loop through the second record set instead of the firs
t
>one?
>
>"Sue Hoegemeier" wrote:
>|||Thank you so much for your help Sue.
I was looking at the second record set for "sp_helplogins" and it has
multiple columns (Login Name, DB Name, UserName, UserorAlias) It appears
that when I run it the columns tell me: (UserID, Database Name, Permission
Type, Member/User)
Is that right?
"Sue Hoegemeier" wrote:

> Okay...so you actually want to know if the login exists -
> not if it's a member of a database role. You can check if
> the login exists before you add it using something like:
> if not exists (select * from master.dbo.syslogins where
> loginname = N'domain\userid')
> exec sp_grantlogin N'domain\userid'
> -Sue
> On Tue, 28 Feb 2006 21:42:26 -0800, Chad T
> <ChadT@.discussions.microsoft.com> wrote:
>
>|||If you are executing this in query analyzer, there will be
two results in the results pane. You need to use the scroll
for the results pane to see the other set of results.
-Sue
On Thu, 2 Mar 2006 07:48:27 -0800, Chad T
<ChadT@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thank you so much for your help Sue.
>I was looking at the second record set for "sp_helplogins" and it has
>multiple columns (Login Name, DB Name, UserName, UserorAlias) It appears
>that when I run it the columns tell me: (UserID, Database Name, Permission
>Type, Member/User)
>Is that right?
>
>"Sue Hoegemeier" wrote:
>

Adding numerous login via script

Hi,

Can anyone point me in the direction of a script that incorporates sp_addlogin, which allows for adding 200 sql authentication logins from an excel spreadsheet, or a temporary sql table with the id info, containing the username, password, and def db?

I'm trying to avoid adding each new login one by one.

EXEC sp_addlogin 'username', 'password', 'default database'Thanks, BPH

A simple approach is to create an expression in your Excel spreadsheet that builds the Exec sp_addlogin line. Copy the formula down to all the rows, then copy/paste the value into Query Analyzer.

If you have loaded the columns into a database table, you can execute a Select statement that builds the lines. Again, copy and paste the results and execute them. Assume you have built a table call logintemp with username, pwd, and dbase columns:

Select 'Exec sp_addlogin ''' + username + ''', ''' + pwd + ''', ''' + dbase + ''''
From logintemp

You need to double the quote marks to yield a quote in the output string.

|||Thanks. I'll give that a shot.

Adding datetime fields

Hi

I'm learning SQL, stuck on a problem, and would be very grateful if someone could point me in the right direction please.

I have a table that contains employee overtime data. The table contains the employee ID number, the work week ID, basic hours, and overtime hours worked.
What i want to do is SUM(OThrs) for a particular employee to get the total OT hours worked in a given workweek.

However, as I understand it the datetime datatype stores its value as a value measured from a base date of Dec-30-1899. As it wouldn't make sense to add the datetime fields due to this, is there any way around it?

The OThrs is brought in from a csv file through a DTS package and is in the format of.... eg 07:45, 13:20, 02:12, 08:10

So if those times above were all for the same employee in the same work week, it would total 31:27

I'd be grateful for some poiters on this problem.
Thanks & Regards
MartyT

If you did not specify any date part in the datetime value, then the date portion will default to 1900-01-01. Confirm that this is the case for those values. Assuming this condition, you can do the following:

select t.EmployeeId, t.WorkWeekId,
convert(varchar(5), dateadd(minute, sum(datediff(minute, '', t.Othrs)), ''), 114) as total_ot_hours
from tbl as t
group by t.EmployeeId, t.WorkWeekId

Note that the above query only has resolution less than 24 hrs. If you need more than that, then take the minute value directly and generate the hour/minutes part yourself.|||That's a big help. Thanks for your time - much appreciated

Adding a user to only access data no adds/edits/deletes

I'm hoping someone can get me pointed in the right direction

we have SQL 2005 and I need to add a user account to let some only view the data

no edits / adds / deletes ?

can any one help

thanks

David

By default any created user and mapped user account is only member of the database role public. This role by default can′t Select, Insert, Update nor Delete anything. If you grant only Select to the user, he will only be able to select the data and will get an error message if he wants to do anything beyond that. (But don′t forget that the user will automatically inherit the permissions from every group he is a member of, including the public role)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

yeah i know how

patch a empty batch @. save (record)

open with :ext: .txt logon

patch open

with extra :internet options import

|||

Thank you can you tell me how to add & Map a user

thanks

David

|||edits