Adding Logons

I have transfered databases from one server to another.
All my users are showing up in my databases but there is nothing in the
"Login Name" column. I tried adding in the user in EM in Logins under the
Security folder and that works also. But when I create the user and give it
access to the database, I get an error that says the user already exists.
Now the user is in the Logins and Users section but still no Login Name
showing and you can't logon to the database. But when I drop the user from
the database and add it to the Login User again, it works fine. Do I have
to do this for each user? This is a problem as we have about 15 databases
and about 40 users.
Is there a better way to deal with this?
Thanks,
TomOn Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> I have transfered databases from one server to another.
> All my users are showing up in my databases but there is nothing in the
> "Login Name" column. I tried adding in the user in EM in Logins under the
> Security folder and that works also. But when I create the user and give
it
> access to the database, I get an error that says the user already exists.
> Now the user is in the Logins and Users section but still no Login Name
> showing and you can't logon to the database. But when I drop the user fro
m
> the database and add it to the Login User again, it works fine. Do I have
> to do this for each user? This is a problem as we have about 15 databases
> and about 40 users.
> Is there a better way to deal with this?
> Thanks,
> Tom
You must have restored the database to new server. The reason you're
having this problem is the SID for login and SID for the user in the
database are different. You have Orphaned Users. Since you have the
login in the server and user in the database you can keep doing what
you're doing remove the user and add the user again. Or you can use
sp_change_users_login. Check the books online for detailed usage of
the system stored proc or you can read the article for how to fix your
problem. Look into Chadmat's last post that has the tsql for
sp_fixusers procedure.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Good day,
Bulent|||"Bulent" <bulentgucuk@.gmail.com> wrote in message
news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
> On Apr 19, 6:59 pm, "tshad" <t...@.home.com> wrote:
> You must have restored the database to new server. The reason you're
> having this problem is the SID for login and SID for the user in the
> database are different. You have Orphaned Users. Since you have the
> login in the server and user in the database you can keep doing what
> you're doing remove the user and add the user again. Or you can use
> sp_change_users_login. Check the books online for detailed usage of
> the system stored proc or you can read the article for how to fix your
> problem. Look into Chadmat's last post that has the tsql for
> sp_fixusers procedure.
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12615
Yes, I did use a new server.
I just installed Sql Server on the new machine (Jupiter). I backed up the
couple of databases I have on my old Server (Venus) and then restored them
on Jupiter.
Can I just backup Master from Venus and restore it to Jupiter. I know that
Master keeps the Logins in its tables. Would this work or would it cause
other problems?
Thanks,
Tom
> Good day,
> Bulent
>|||"tshad" <t@.home.com> wrote in message
news:%23WQxfu2gHHA.2368@.TK2MSFTNGP04.phx.gbl...
> "Bulent" <bulentgucuk@.gmail.com> wrote in message
> news:1177032504.814702.279020@.n76g2000hsh.googlegroups.com...
This SP did work great, BTW.
But I am still curious about the Master and whether I can or even need to
move it to the new server. As well as what problems I could run into such
as incompatabilities (assuming that the new Sql Server was installed exactly
the same as the old one).
Thanks,
Tom[vbcol=seagreen]
> Yes, I did use a new server.
> I just installed Sql Server on the new machine (Jupiter). I backed up the
> couple of databases I have on my old Server (Venus) and then restored them
> on Jupiter.
> Can I just backup Master from Venus and restore it to Jupiter. I know
> that Master keeps the Logins in its tables. Would this work or would it
> cause other problems?
> Thanks,
> Tom
>

Adding logins via SSEUtil compared to SQL Server Management Studio

Hello all,

I am currently in the process of setting up an SQL Server Express installation that comes packaged with an application I have written. My problem is that I want to use SQL Server user management (not just windows users) which work fine if I set them up manually. I started writing a script that I have SSEUtil execute once the application is fully installed (a step in my installation script) which sets up the users and passwords etc. The script is similar to the following:

USE [DBName]
GO

EXEC sp_DropUser 'user1'
EXEC sp_DropUser 'user2'
EXEC sp_DropUser 'user3'
EXEC sp_DropUser 'user4'
GO

USE [master]
GO

EXEC sp_DropLogin 'user1'
EXEC sp_DropLogin 'user2'
EXEC sp_DropLogin 'user3'
EXEC sp_DropLogin 'user4'
GO

CREATE LOGIN user1 WITH Password = 'user1', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user2 WITH Password = 'user2', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user3 WITH Password = 'user3', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user4 WITH Password = 'user4', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [DBName]
GO

EXEC sp_AddUser 'user1'
EXEC sp_AddUser 'user2'
EXEC sp_AddUser 'user3'
EXEC sp_AddUser 'user4'
GO

ALTER USER user1 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user2 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user3 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user4 WITH DEFAULT_SCHEMA = MySchema
GO

REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 FROM MyRole
GO

EXEC sp_DropRole 'MyRole'
EXEC sp_AddRole 'MyRole'
GO

GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 TO MyRole
GO

EXEC sp_AddRoleMember 'MyRole','user1'
EXEC sp_AddRoleMember 'MyRole','user2'
EXEC sp_AddRoleMember 'MyRole','user3'
EXEC sp_AddRoleMember 'MyRole','user4'
GO

Now if I run this script from within SQL Server Management Studio it executes perfectly. The logins add, the role is added, each user is added to the database logins and assigned to the role, the schema is set correctly on each user.

Then when I try to run the exact same script from the SSEUtil application (SSEUTIL -s PCNAME\Instance -run USERS.SQL), it processes everything, except the Logins.

This is frustrating as it means to install for a client I would need to either get them to open the management console and run the script from there, or I have to go to site just to setup users.

Am I on the right track? Or is there another way to automate the adding of Logins?

Thanks in advance,

DSXC

Just an update.

I did a check within my database and found that the sys.syslogins has the users (when I do a select from the view) but they just don't work. The only difference I can see between my scripted login I created and the SA user is the flag for sysadmin, but thats understandable as these users are not to be sysadmins.

Is there another table that actually enables the login?

DSXC

|||

I noticed a lot of people using the SQLCMD.EXE instead of the SSEUtil.EXE I was using so I thought I'd give it a try.

Lo and behold... it works!

Talk about crazy... oh well. Just so everyone knows, use the SQLCMD.EXE over SSEUtil.exe... gah!

EDIT: I didn't mention the command line to run it...

SQLCMD.EXE -i MYSCRIPT.SQL

Hope that helps.

DSXC

|||

Hi DSXC,

Sorry to have missed this thread earlier, I can shed some light on what you're seeing.

SSEUtil.exe is an unsupported tool primarily used for troubleshooting User Instance problems, it is not meant to be used as a general solution for runing scripts, nor is it licensed to be deployed with your application. Additionally, the default mechanism of SSEUtil works against the User Instance, not the parent instance, so my guess is it was not working the way you think it was.

SQLCmd is the general scripting utility that is installed with all copies of SQL 2005 and is exactly the tool you should be using for what you wish to accomplish, but you've already discovered that.

Mike

|||

Hi Mike,

Thanks for your response. It makes a bit more sense now.

I found the SSEUtil app with a search on attaching the database to my SQL Server so I guessed it would have worked running scripts against it also. I just noticed that the SSEUtil doesn't attach my database correctly either so I've now transferred over to using another SQL script.

Again thanks for your response.

DSXC

Adding logins through Query Analyzer

Ok, this one is way out there so I'm not sure anyone will have witnessed this
before.
On several occasions, I have noticed that if I set up a SQL Server level
login and grant permissions to this login (either server-level or database
roles, doesn't matter which) through Query Analyzer and then don't use the
account right away (say for a couple of weeks or more), if I then try to use
this login it fails due to authentication failure. If I reset the password,
it works fine. If I use the account to connect right after I set it up, it
works fine.
Has ANYONE ever witnessed this? It's the weirdest thing.
Thanks.
Also, if I go into Query Analyzer and run sp_password to change the password
again, it will only accept the old password as being NULL.
|||Passwords usally dont change or expire on their own (In SQL Server they
will apply to domain policies, but not in 2000) ?
If you are logged in as a sa, you can reset the password without knowing the
old one by passing NULL to the SP.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Ana" <Ana@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AF003637-D917-4F54-800E-A36EEE7B8FF0@.microsoft.com...
> Also, if I go into Query Analyzer and run sp_password to change the
> password
> again, it will only accept the old password as being NULL.
|||Yes, I know that being logged in with sa will allow me to change the password
without knowing the old one...I am having to do this now. My point was that
this is the only way it will allow me to change the password. I was just
verifying that SQL Server seriously doesn't know what the original password
is. I can't login with it, and I can't change the password with the original
password.
|||> SQL Server seriously doesn't know what the original password
> is. I can't login with it,
Well, if it has changed, this makes sense. If it has not, then surely you
are spelling it wrong, or transcribing from the wrong yellow sticky on your
monitor.
In summary, one of the following is true:
(a) the password has changed
(b) you are not using the correct password
SQL Server doesn't just forget what the password is.

> I can't change the password with the original password.
Why do you need the original password to change it? Log in as a local
administrator and issue:
EXEC sp_password NULL, 'oldPassword', 'sa'
Now try logging in as sa again, with the old password that SQL Server
"doesn't know"... a penny says it works.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||Ok, I guess I need to spell this out...
First of all, I said this has happened on several occasions; it's not just a
one-time slip up of either the fingers or the "yellow sticky".
Second, we are a large company with several SQL Servers and from time to
time, we have the need to set up a uniform service account on all of our
servers that one application can use. To do this, we use sp_addlogin and
sp_addsrvrolemember or sp_adduser/sp_addrolemember statements and run the
exact same set of statements on each server. Oftentimes, due to project
timelines and delays on someone else's end, we set up these service accounts
and they don't get used for weeks. This is the ONLY time we see the issue of
not being able to login with the documented information (when the account has
sat for an extended period of time without ever being used).
Third, when we set something like a service account up on multiple servers,
the first server is always tested to make sure that the documented login
information works to authenticate to the server. Then the remainder of the
accounts are set up, using the same statements that were used to create the
account on the first server.
Fourth, we are a Sarbanes-Oxley affected operation. What this means is
there are only two people in our company (myself and the other SQL Server
DBA) that have sysadmin accounts to all the servers and no one besides us
knows the passwords to any of dbo service accounts (of which there are few).
What this means is, only we would be able to change the passwords for the
service accounts and we have far more important things to do than to sabotage
ourselves.
I hope this offers a more complete explanation of what we're seeing.
|||> we have far more important things to do than to sabotage ourselves.
I wasn't suggesting this. But your explanation of this just doesn't add up.
Your server suddenly doesn't know its own SA password?

> I hope this offers a more complete explanation of what we're seeing.
This doesn't explain to me how SQL Server suddenly "forgets" the SA
password. As I said before, Sarbanes-Oxley, perfect people, what have you,
one or both of the following is true:
(a) the password has changed
(b) you are not using the correct password
|||Where did I ever say that it was forgetting the SA password?
"On several occasions, I have noticed that if I set up a SQL Server level
login and grant permissions to this login (either server-level or database
roles, doesn't matter which) through Query Analyzer and then don't use the
account right away (say for a couple of weeks or more), if I then try to use
this login it fails due to authentication failure."
|||I'm guessing if you'd read the original posting and then followed the chain
through, maybe you wouldn't have been so confused. I didn't mention any
account names because this has happened several times to more than one
account.
In any case, my question to your comment "SQL Server doesn't just lose
passwords, something else is causing this" is: Have you ever tried doing
what I've described as our process (create a SQL Server level login and grant
it permissions to whatever through Query Analyzer, DON'T USE THAT ACCOUNT for
at least a few weeks, then try to use it to connect to SQL Server after
several weeks have passed) and can verify that this definitely would never
happen?
Also, I'm telling you quite explicitly, that we have not changed the
password and we are using the password that was set.
Yes, I know this sounds extrordinary, but that's why I've put a posting out
about it.
|||"Maybe if you had mentioned the actual login name you were using"
Sheesh! It's always entertaining to watch people respond to posts without
reading the OP's message. Lest I'm on drugs I couldn't see anywhere where Ana
specified the SA account, indeed the phrase used was "set up a SQL Server
level login". If you want to assume the OP meant SA then that's your problem
laddy'o... And you know what they say about assumption: makes an XXX out of u
and me

Adding logins through Query Analyzer

Ok, this one is way out there so I'm not sure anyone will have witnessed thi
s
before.
On several occasions, I have noticed that if I set up a SQL Server level
login and grant permissions to this login (either server-level or database
roles, doesn't matter which) through Query Analyzer and then don't use the
account right away (say for a couple of weeks or more), if I then try to use
this login it fails due to authentication failure. If I reset the password,
it works fine. If I use the account to connect right after I set it up, it
works fine.
Has ANYONE ever witnessed this? It's the weirdest thing.
Thanks.Also, if I go into Query Analyzer and run sp_password to change the password
again, it will only accept the old password as being NULL.|||Passwords usally dont change or expire on their own (In SQL Server they
will apply to domain policies, but not in 2000) ?
If you are logged in as a sa, you can reset the password without knowing the
old one by passing NULL to the SP.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Ana" <Ana@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AF003637-D917-4F54-800E-A36EEE7B8FF0@.microsoft.com...
> Also, if I go into Query Analyzer and run sp_password to change the
> password
> again, it will only accept the old password as being NULL.|||Yes, I know that being logged in with sa will allow me to change the passwor
d
without knowing the old one...I am having to do this now. My point was that
this is the only way it will allow me to change the password. I was just
verifying that SQL Server seriously doesn't know what the original password
is. I can't login with it, and I can't change the password with the origina
l
password.|||> SQL Server seriously doesn't know what the original password
> is. I can't login with it,
Well, if it has changed, this makes sense. If it has not, then surely you
are spelling it wrong, or transcribing from the wrong yellow sticky on your
monitor.
In summary, one of the following is true:
(a) the password has changed
(b) you are not using the correct password
SQL Server doesn't just forget what the password is.

> I can't change the password with the original password.
Why do you need the original password to change it? Log in as a local
administrator and issue:
EXEC sp_password NULL, 'oldPassword', 'sa'
Now try logging in as sa again, with the old password that SQL Server
"doesn't know"... a penny says it works.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Ok, I guess I need to spell this out...
First of all, I said this has happened on several occasions; it's not just a
one-time slip up of either the fingers or the "yellow sticky".
Second, we are a large company with several SQL Servers and from time to
time, we have the need to set up a uniform service account on all of our
servers that one application can use. To do this, we use sp_addlogin and
sp_addsrvrolemember or sp_adduser/sp_addrolemember statements and run the
exact same set of statements on each server. Oftentimes, due to project
timelines and delays on someone else's end, we set up these service accounts
and they don't get used for weeks. This is the ONLY time we see the issue o
f
not being able to login with the documented information (when the account ha
s
sat for an extended period of time without ever being used).
Third, when we set something like a service account up on multiple servers,
the first server is always tested to make sure that the documented login
information works to authenticate to the server. Then the remainder of the
accounts are set up, using the same statements that were used to create the
account on the first server.
Fourth, we are a Sarbanes-Oxley affected operation. What this means is
there are only two people in our company (myself and the other SQL Server
DBA) that have sysadmin accounts to all the servers and no one besides us
knows the passwords to any of dbo service accounts (of which there are few).
What this means is, only we would be able to change the passwords for the
service accounts and we have far more important things to do than to sabotag
e
ourselves.
I hope this offers a more complete explanation of what we're seeing.|||> we have far more important things to do than to sabotage ourselves.
I wasn't suggesting this. But your explanation of this just doesn't add up.
Your server suddenly doesn't know its own SA password?

> I hope this offers a more complete explanation of what we're seeing.
This doesn't explain to me how SQL Server suddenly "forgets" the SA
password. As I said before, Sarbanes-Oxley, perfect people, what have you,
one or both of the following is true:
(a) the password has changed
(b) you are not using the correct password|||Where did I ever say that it was forgetting the SA password'
"On several occasions, I have noticed that if I set up a SQL Server level
login and grant permissions to this login (either server-level or database
roles, doesn't matter which) through Query Analyzer and then don't use the
account right away (say for a couple of weeks or more), if I then try to use
this login it fails due to authentication failure."|||Sorry, I was having trouble deciphering this:
"Yes, I know that being logged in with sa will allow me to change the
password
without knowing the old one...I am having to do this now. My point was that
this is the only way it will allow me to change the password. I was just
verifying that SQL Server seriously doesn't know what the original password
is. I can't login with it, and I can't change the password with the
original
password."
Maybe if you had mentioned the actual login name you were using, there would
have been less confusion over which login was actually affected.
In any case, change my previous post from "the SA" to "any", and my point
remains the same. SQL Server doesn't just lose passwords, something else is
causing this.|||I'm guessing if you'd read the original posting and then followed the chain
through, maybe you wouldn't have been so confused. I didn't mention any
account names because this has happened several times to more than one
account.
In any case, my question to your comment "SQL Server doesn't just lose
passwords, something else is causing this" is: Have you ever tried doing
what I've described as our process (create a SQL Server level login and gran
t
it permissions to whatever through Query Analyzer, DON'T USE THAT ACCOUNT fo
r
at least a few weeks, then try to use it to connect to SQL Server after
several weeks have passed) and can verify that this definitely would never
happen?
Also, I'm telling you quite explicitly, that we have not changed the
password and we are using the password that was set.
Yes, I know this sounds extrordinary, but that's why I've put a posting out
about it.

Adding logins through Query Analyzer

Ok, this one is way out there so I'm not sure anyone will have witnessed this
before.
On several occasions, I have noticed that if I set up a SQL Server level
login and grant permissions to this login (either server-level or database
roles, doesn't matter which) through Query Analyzer and then don't use the
account right away (say for a couple of weeks or more), if I then try to use
this login it fails due to authentication failure. If I reset the password,
it works fine. If I use the account to connect right after I set it up, it
works fine.
Has ANYONE ever witnessed this? It's the weirdest thing.
Thanks.Also, if I go into Query Analyzer and run sp_password to change the password
again, it will only accept the old password as being NULL.|||Passwords usally don´t change or expire on their own (In SQL Server they
will apply to domain policies, but not in 2000) ?
If you are logged in as a sa, you can reset the password without knowing the
old one by passing NULL to the SP.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Ana" <Ana@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AF003637-D917-4F54-800E-A36EEE7B8FF0@.microsoft.com...
> Also, if I go into Query Analyzer and run sp_password to change the
> password
> again, it will only accept the old password as being NULL.|||Yes, I know that being logged in with sa will allow me to change the password
without knowing the old one...I am having to do this now. My point was that
this is the only way it will allow me to change the password. I was just
verifying that SQL Server seriously doesn't know what the original password
is. I can't login with it, and I can't change the password with the original
password.|||> SQL Server seriously doesn't know what the original password
> is. I can't login with it,
Well, if it has changed, this makes sense. If it has not, then surely you
are spelling it wrong, or transcribing from the wrong yellow sticky on your
monitor.
In summary, one of the following is true:
(a) the password has changed
(b) you are not using the correct password
SQL Server doesn't just forget what the password is.
> I can't change the password with the original password.
Why do you need the original password to change it? Log in as a local
administrator and issue:
EXEC sp_password NULL, 'oldPassword', 'sa'
Now try logging in as sa again, with the old password that SQL Server
"doesn't know"... a penny says it works.
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Ok, I guess I need to spell this out...
First of all, I said this has happened on several occasions; it's not just a
one-time slip up of either the fingers or the "yellow sticky".
Second, we are a large company with several SQL Servers and from time to
time, we have the need to set up a uniform service account on all of our
servers that one application can use. To do this, we use sp_addlogin and
sp_addsrvrolemember or sp_adduser/sp_addrolemember statements and run the
exact same set of statements on each server. Oftentimes, due to project
timelines and delays on someone else's end, we set up these service accounts
and they don't get used for weeks. This is the ONLY time we see the issue of
not being able to login with the documented information (when the account has
sat for an extended period of time without ever being used).
Third, when we set something like a service account up on multiple servers,
the first server is always tested to make sure that the documented login
information works to authenticate to the server. Then the remainder of the
accounts are set up, using the same statements that were used to create the
account on the first server.
Fourth, we are a Sarbanes-Oxley affected operation. What this means is
there are only two people in our company (myself and the other SQL Server
DBA) that have sysadmin accounts to all the servers and no one besides us
knows the passwords to any of dbo service accounts (of which there are few).
What this means is, only we would be able to change the passwords for the
service accounts and we have far more important things to do than to sabotage
ourselves.
I hope this offers a more complete explanation of what we're seeing.|||> we have far more important things to do than to sabotage ourselves.
I wasn't suggesting this. But your explanation of this just doesn't add up.
Your server suddenly doesn't know its own SA password?
> I hope this offers a more complete explanation of what we're seeing.
This doesn't explain to me how SQL Server suddenly "forgets" the SA
password. As I said before, Sarbanes-Oxley, perfect people, what have you,
one or both of the following is true:
(a) the password has changed
(b) you are not using the correct password|||Where did I ever say that it was forgetting the SA password'
"On several occasions, I have noticed that if I set up a SQL Server level
login and grant permissions to this login (either server-level or database
roles, doesn't matter which) through Query Analyzer and then don't use the
account right away (say for a couple of weeks or more), if I then try to use
this login it fails due to authentication failure."|||Sorry, I was having trouble deciphering this:
"Yes, I know that being logged in with sa will allow me to change the
password
without knowing the old one...I am having to do this now. My point was that
this is the only way it will allow me to change the password. I was just
verifying that SQL Server seriously doesn't know what the original password
is. I can't login with it, and I can't change the password with the
original
password."
Maybe if you had mentioned the actual login name you were using, there would
have been less confusion over which login was actually affected.
In any case, change my previous post from "the SA" to "any", and my point
remains the same. SQL Server doesn't just lose passwords, something else is
causing this.|||I'm guessing if you'd read the original posting and then followed the chain
through, maybe you wouldn't have been so confused. I didn't mention any
account names because this has happened several times to more than one
account.
In any case, my question to your comment "SQL Server doesn't just lose
passwords, something else is causing this" is: Have you ever tried doing
what I've described as our process (create a SQL Server level login and grant
it permissions to whatever through Query Analyzer, DON'T USE THAT ACCOUNT for
at least a few weeks, then try to use it to connect to SQL Server after
several weeks have passed) and can verify that this definitely would never
happen?
Also, I'm telling you quite explicitly, that we have not changed the
password and we are using the password that was set.
Yes, I know this sounds extrordinary, but that's why I've put a posting out
about it.|||"Maybe if you had mentioned the actual login name you were using"
Sheesh! It's always entertaining to watch people respond to posts without
reading the OP's message. Lest I'm on drugs I couldn't see anywhere where Ana
specified the SA account, indeed the phrase used was "set up a SQL Server
level login". If you want to assume the OP meant SA then that's your problem
laddy'o... And you know what they say about assumption: makes an ass out of u
and me|||Hey laddy'o, why don't you look at the thread and see where I came in.
If you have some useful advice for the OP, or even a useful contribution to
the thread, why don't you submit it, instead of calling people names.
Sheesh is right.
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Scrat" <Scrat@.discussions.microsoft.com> wrote in message
news:E8C30EA4-E3C9-4595-B1FD-8C7C66D277BE@.microsoft.com...
> "Maybe if you had mentioned the actual login name you were using"
> Sheesh! It's always entertaining to watch people respond to posts without
> reading the OP's message. Lest I'm on drugs I couldn't see anywhere where
> Ana
> specified the SA account, indeed the phrase used was "set up a SQL Server
> level login". If you want to assume the OP meant SA then that's your
> problem
> laddy'o... And you know what they say about assumption: makes an ass out
> of u
> and me|||Okay, good luck finding the ghost in the machine.
I'll be looking forward to seeing the actual solution. (Never mind your
admission that, yes, somebody DID change the password or re-create the login
after all.)
Heard of profiler? Might want to have it scan for events like sp_addlogin,
sp_droplogin, and sp_password.
A
"Ana" <Ana@.discussions.microsoft.com> wrote in message
news:3F2CDB76-49F8-4BE9-996A-4FEF93B0C49D@.microsoft.com...
> I'm guessing if you'd read the original posting and then followed the
> chain
> through, maybe you wouldn't have been so confused. I didn't mention any
> account names because this has happened several times to more than one
> account.
> In any case, my question to your comment "SQL Server doesn't just lose
> passwords, something else is causing this" is: Have you ever tried doing
> what I've described as our process (create a SQL Server level login and
> grant
> it permissions to whatever through Query Analyzer, DON'T USE THAT ACCOUNT
> for
> at least a few weeks, then try to use it to connect to SQL Server after
> several weeks have passed) and can verify that this definitely would never
> happen?
> Also, I'm telling you quite explicitly, that we have not changed the
> password and we are using the password that was set.
> Yes, I know this sounds extrordinary, but that's why I've put a posting
> out
> about it.|||Hey, you wanna jump in a thread mid point and start making assumptions then
go right ahead. Just makes you look kinda foolish is all...
Given how short the thread is it behooves one to read the initial post - and
thus save a lot of to-ing and fro-ing with the OP.
Normally I'd have said nowt but your arrogance annoyed me somewhat. A simple
apology to the OP would have helped 100%.
So, to add to the thread, I think what Ana's getting at is that this *is*
weird behavior & she mentioned that it was a couple of weeks later before the
problem transpired. You meant to leave Profiler running 24x7 to find it? Even
the blackbox prolly don't help since it'll rollover every 10MB.
Rather than picking at the OP, trying reading and helping. Or does being an
MVP put you above us mere-mortals?|||"AB - MVP" wrote:
> Okay, good luck finding the ghost in the machine.
> I'll be looking forward to seeing the actual solution. (Never mind your
> admission that, yes, somebody DID change the password or re-create the login
> after all.)
> Heard of profiler? Might want to have it scan for events like sp_addlogin,
> sp_droplogin, and sp_password.
> A
I give up. This is precisely why I never post anything; no one wants to
actually help the person having issues, but they all want to try to look
smarter than everyone else.
AB, you seem to have a fairly closed-minded approach to helping those with
sincere questions. This is clearly demonstrated by the fact that you
couldn't even answer my last question and the fact that you thought it was ok
for you to waste my time by not reading the entire thread before you started
pointing fingers. You are arrogant and rude and how you ever EARNED a title
such as MVP is beyond me.|||> Rather than picking at the OP, trying reading and helping. Or does being
> an
> MVP put you above us mere-mortals?
Of course, no matter how hard I try to help, there's some ignorant a-hole
for whom it's not good enough.
Ciao.|||Ana,
I did some googling for anything close to your problem. Dunno if this helps,
but...:
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/4ebde7376a0d78a5/8e40673af9b17e87?q=sp_addlogin+query+analyzer&rnum=3&hl=en#8e40673af9b17e87|||Hi Ana,
I have read your post.
Two quick suggestions (2 is the only one I expect to be relevant)
1) I have seen sometimes when a backup is restored that the users can get
'orphaned'. They appear in both the master and the 'restored' db but the
login does not work. Slim chance that this is the case but who knows?
2) The other thing I thought could be helpful is to place a trigger on the
password columns to log to a new table if any changes do actually occur
during the two week schedule and who did it or if somehow there is some bug
in SQL (not that they ever happen of course).
Good luck I wish it was a better suggestion but maybe it will help.
Mike LVP ;-)|||Mike,
Thanks for your response. The first suggestion doesn't really apply here
because we're not restoring users from another database, we're simply
creating new users. The second option might work. I'll discuss this with my
fellow DBA and see what he thinks.
Luckily, we don't do these "mass single user creations" all the time.
Thanks again!
Ana

Adding logins and permissions

Hi,
I am at a company with 18 employees and I have 11-12 databases in SQL server. I can't seem to give logins and permissions to groups. Is there a simpler way, or do I have to add every single employee to each database and give permissions?
You may create roles (it's a group analogue) within databases and then assign your employees to these groups. There are no serverwide groups except of builtin server roles.

Adding login to SQL SERVER

I am trying to add a new login using sp_addlogin but I
get message:
'User X does not have permission to perform this action'.
I executed
sp_addsrvrolemember 'User X', 'sysadmin' and
sp_grantlogin 'domain\user X '
but when I login as an user X IS_SRVROLEMEMBER tells me
that 'Current user's login is NOT a member of the
sysadmin role'.
I executed sp_helpsrvrolemember and I see that
domain/user X is a member of sysadmin but is not a member
of securityadmin.What do you get when you run the following command:
SELECT SUSER_SNAME()?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Alex" <anonymous@.discussions.microsoft.com> wrote in message
news:082801c3b902$833d6d60$a001280a@.phx.gbl...
I am trying to add a new login using sp_addlogin but I
get message:
'User X does not have permission to perform this action'.
I executed
sp_addsrvrolemember 'User X', 'sysadmin' and
sp_grantlogin 'domain\user X '
but when I login as an user X IS_SRVROLEMEMBER tells me
that 'Current user's login is NOT a member of the
sysadmin role'.
I executed sp_helpsrvrolemember and I see that
domain/user X is a member of sysadmin but is not a member
of securityadmin.

Adding logging to a Data Extension

Hello,
I am attempting to troubleshoot why my custom Data Extension is
failing. To do so, I'd like to add some (log4net) logging to the code
to see what is being accessed and what is going wrong.
Do I need to add special permissions for the extra log4net assembly to
run? The documentation seems very sparse on how to debug these
extensions.
I'm able to attach the VS process and step through the code, but that
isn't really helping - Report Designer only shows a generic "The query
could not be loaded, Verify your connection string" error. My extension
(for testing purposes) isn't making use of either string.A followup question..
If I want to specify appSettings for my Data Processing Extension, what
should the name of the config file be?
I've tried assemblyname.config; but I'm going to guess that isn't
working because the Extension code is being run through another
assembly...

Adding locationID's to detail records using SQL syntax

SQLserver 2K
Table A (Customer Master)
CustID
CustName
Table B (Customer Location)
CustID
LocationID
LocationOrderID
LocationName
What's the syntax to automatically add LocationOrderID in increment of 1 for
table B where B.CustID = A.CustID ?
For example,
A.CustID = 100
and there are 5 B records having B.CustID = 100
I need to insert LocationOrderID starting with 1 to B.LocationOrderID based
on the order of B.LocationName
Any help is greatly appreciated.
BillTry,
update tableB
set LocationOrderID = (select count(*) from tableB as a where a.CustID =
tableB.CustID and a.LocationName <= tableB.LocationName)
go
AMB
"Bill Nguyen" wrote:

> SQLserver 2K
> Table A (Customer Master)
> CustID
> CustName
> Table B (Customer Location)
> CustID
> LocationID
> LocationOrderID
> LocationName
> What's the syntax to automatically add LocationOrderID in increment of 1 f
or
> table B where B.CustID = A.CustID ?
> For example,
> A.CustID = 100
> and there are 5 B records having B.CustID = 100
> I need to insert LocationOrderID starting with 1 to B.LocationOrderID base
d
> on the order of B.LocationName
>
> Any help is greatly appreciated.
> Bill
>
>|||Alejandro;
This works great!
Thanks
Bill
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9E0D3718-1900-409C-BAFB-885F32212EB9@.microsoft.com...
> Try,
> update tableB
> set LocationOrderID = (select count(*) from tableB as a where a.CustID =
> tableB.CustID and a.LocationName <= tableB.LocationName)
> go
>
> AMB
> "Bill Nguyen" wrote:
>

Adding Linked server

Hi guys,
My linked server (from one SQL Server 2000 to another SQL Server 2000) is
not working. My settings are as follows:
Checked other data source
Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
Under Security tab:
Be made using the logins current security context is checked.
Please help!
When you say it's not working , do you mean "insufficient permissions!? or
are you getting some othe rmessage?
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David" <David@.discussions.microsoft.com> wrote in message
news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> Hi guys,
> My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> not working. My settings are as follows:
> Checked other data source
> Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
> Under Security tab:
> Be made using the logins current security context is checked.
>
> Please help!
>
|||It says, SQL Server does not exist or Access denied. Thanks
"Jack Vamvas" wrote:

> When you say it's not working , do you mean "insufficient permissions!? or
> are you getting some othe rmessage?
> --
> Jack Vamvas
> __________________________________________________ ________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "David" <David@.discussions.microsoft.com> wrote in message
> news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
>
>

Adding Linked server

Hi guys,
My linked server (from one SQL Server 2000 to another SQL Server 2000) is
not working. My settings are as follows:
Checked other data source
Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=
TE
ST;
Under Security tab:
Be made using the logins current security context is checked.
Please help!When you say it's not working , do you mean "insufficient permissions!? or
are you getting some othe rmessage?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David" <David@.discussions.microsoft.com> wrote in message
news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> Hi guys,
> My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> not working. My settings are as follows:
> Checked other data source
> Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=
TEST;
> Under Security tab:
> Be made using the logins current security context is checked.
>
> Please help!
>|||It says, SQL Server does not exist or Access denied. Thanks
"Jack Vamvas" wrote:

> When you say it's not working , do you mean "insufficient permissions!? or
> are you getting some othe rmessage?
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "David" <David@.discussions.microsoft.com> wrote in message
> news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
>
>

Adding Linked server

Hi guys,
My linked server (from one SQL Server 2000 to another SQL Server 2000) is
not working. My settings are as follows:
Checked other data source
Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
Under Security tab:
Be made using the logins current security context is checked.
Please help!When you say it's not working , do you mean "insufficient permissions!? or
are you getting some othe rmessage?
--
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David" <David@.discussions.microsoft.com> wrote in message
news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> Hi guys,
> My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> not working. My settings are as follows:
> Checked other data source
> Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
> Under Security tab:
> Be made using the logins current security context is checked.
>
> Please help!
>|||It says, SQL Server does not exist or Access denied. Thanks
"Jack Vamvas" wrote:
> When you say it's not working , do you mean "insufficient permissions!? or
> are you getting some othe rmessage?
> --
> Jack Vamvas
> __________________________________________________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "David" <David@.discussions.microsoft.com> wrote in message
> news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> > Hi guys,
> >
> > My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> > not working. My settings are as follows:
> >
> > Checked other data source
> > Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
> >
> > Under Security tab:
> > Be made using the logins current security context is checked.
> >
> >
> > Please help!
> >
>
>

Adding line sequence numbers

hi all

I am stuck with something that seems easy but im obviously clueless as how to execute the idea.

I have a custom table that houses invoices on the details level. So for example i have:

InvcNo
00000001
00000001
00000001
00000002
00000002
00000003

and so forth

What I am wanting to do in another column is keep track of the sequence number for each distinct invoice like:

SeqNo
1
2
3
1
2
1

I am working in a stored proc and i cant get past adding the numbers up at each line as a whole and not reseting when the next invoice number is present. Any help would be so greatly appreciated.

ThanksWill this help?
http://www.sqlservercentral.com/columnists/mcoles/sequentialordering.asp|||Thanks...but I dont have 2005 (

Is it possible to pull the example that is given in 2005 section in a prior version of SQL?|||Can you uniquely identify each record for a specific InvcNo?|||Yes, I have an InvcKey column set up|||create table #t1 (InvcKey int, InvcNo int)
insert into #t1 select
1,1 union all select
2,1 union all select
3,1 union all select
4,2 union all select
5,2 union all select
6,3

select count(*) 'SeqNo', a.InvcKey, a.InvcNo from #t1 a, #t1 b
where a.InvcNo=b.InvcNo
and a.InvcKey>=b.InvcKey
group by a.InvcKey, a.InvcNo

drop table #t1|||Ok, Ive got it...I should have played around some before asking further

SELECT COUNT(*) AS [SeqNo], c.InvcNo, c.InvcKey
FROM table c, table d
WHERE d.InvcKey <= c.InvcKey AND d.InvcNo = cInvcNo
GROUP BY c.InvcNo, c.InvcKey
ORDER BY c.InvcKey

Thanks for the help!

tibor|||You should probably use a JOIN.
Using

FROM table1, table2, ... , tableN

Is not advised.|||Being a self join, would I really need JOIN?|||Being a self join, would I really need JOIN?It is semantics. The affect it the same but the style you used is considered old fashioned in many quarters. Whether or not it is a self join is irrelevent.

Rather excitingly - you will have created a Theta Join when you have finished :)|||Rather excitingly - you will have created a Theta Join when you have finished :)

neat! normally you'd have to pay $360k to get to that level (http://en.wikipedia.org/wiki/Operating_Thetan)!

Adding Line after a Group

Hi all,
I am not able to add a line break after each Group. When I use (Add PageBreak after Group) property, each group is displaying in seperate pages, but i need to have groups continous with a line break between Groups.I greatly appreciate for the help.
Thanks in advance...

Hi,

Have you checked 'Page break at start' or 'Page break at end' in Group Property Dialog ?

Best Regards

|||Hi.....
What ever the Group Items text box...Row...First Complete Row..Then Property Window.....Border Style ......Select Bottom-Solid....
Ok Ur Prblm may Solve without any Page Break....

Best Regads...

Adding Leading Zeros to a char value - TSQL Question

Hi all:

I have save stored procedure which does an insert and update. Before the insert or update statements, I have the following SQL to adding leading zeroes to in my input params:

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + '123',6))

SELECT @.SchoolCode_Modified AS Col1

The problem is that this works on by itself on SSMS, but when incorporated in the SPROC, it doesnt add the leading zeros. The datatype for both of the colum is char(6) as well. If I run the above query in SQL Server Management Studio, it displays Col1 with 000123 in it, which is what I want, from the sproc, but that doesnt happen. Why? Can someone please help me? The following is how it is implemented in the SPROC:

CREATE PROCEDURE [LAF].[uspSaveLoanApplication]

...more params here

@.SchoolCode char(6),

@.BranchCode char(2),

@.PK int OUTPUT,

@.PreviousLoanApplicationStatus char(3) OUTPUT

AS

SET NOCOUNT OFF

SET @.PreviousLoanApplicationStatus = 0

IF EXISTS(SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

SET @.PreviousLoanApplicationStatus = (SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

SELECT @.PreviousLoanApplicationStatus PreviousLoanApplicationStatus

END

-- Add Leading Zeroes to School Code and Branch Code to conform with CLIPS Formatting

DECLARE @.SchoolCode_Modified char(6)

DECLARE @.BranchCode_Modified char(2)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + @.SchoolCode,6))

SET @.BranchCode_Modified = (SELECT Right(Replicate('0',2) + @.BranchCode,2))

IF EXISTS(SELECT [LoanApplicationID] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

UPDATE [LAF].[LoanApplication] SET

....more here

[SchoolCode] = @.SchoolCode_Modified,

[BranchCode] = @.BranchCode_Modified,

...more here

WHERE

[LoanApplicationID] = @.LoanApplicationID

AND [UpdatedOn] = @.LastUpdated

SELECT @.LoanApplicationID PK

SET @.PK = @.LoanApplicationID

END

ELSE

BEGIN

INSERT INTO [LAF].[LoanApplication] (

.....more here

[SchoolCode],

[BranchCode],

.....more here

) VALUES (

....more here

@.SchoolCode_Modified,

@.BranchCode_Modified,

....more here

)

SET @.PK = SCOPE_IDENTITY()

SELECT @.PK PK

END

With a quick scan, your code appears correct.

How is it that you are finding that the values of @.SchoolCode_Modified and @.BranchCode_Modified do NOT have the leading zeros?

|||

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

|||

DanR1 wrote:

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

Good catch...

Actually, I don't think that it would be ' 123', but instead 'should' be '123 '. In either case though, adding preceding characters and then taking the rightmost 6 characters would not effect any change in the value.

|||

Arnie,

Yes, you are right about the trailing blanks, instead of leading blanks as I suggested. (I checked on it after I made my post, and figured it wasn't worth the trouble to make the edit.)

So it seems like "ltrim(rtrim(@.SchoolCode))" is what should be preceded by "000000" before taking the 6 rightmost characters.

Dan

|||

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = '123'

SET @.SchoolCode_Modified = REPLICATE('0',6 -LEN(@.SchoolCode_Modified))+ @.SchoolCode_Modified

SELECT @.SchoolCode_Modified AS Col1

adding leading zeros in select

Hi all,

I'm trying to select a bigint field and format it with leading zeros. I've tried the convert function but it does not seem to support this basic feature. There is no reference in the document on CONVERT or CAST, it only refers to formatting dates.

example:
table contents
123
456
789

desired select result:
000123
000456
000789

Who has a solution for this problem?select
replicate('0', 6-datalength(convert(varchar,NumericField)))
from MyTable

Or, better:

create function LZero(@.MyNum int, @.MyLength int)
returns varchar
as
begin
declare @.MyStr varchar
set @.MyStr = convert(varchar,@.MyNum)
if @.MyLength>datalength(@.MyStr)
set @.MyStr = replicate('0',(@.MyLength-datalength(@.MyStr)))+@.MyStr
return @.MyStr
end
go
select dbo.LZero(NumericField,6) from MyTable|||Thanx for your reply.

The first solution you offer works fine and suits my needs. I can't get the second to work properly however. The query only returns '0' for every row.

But this will get me where I want. Thank you very much|||Ok.
I have no SQL Server near me to debug it, so go for the 1st option.|||Originally posted by kukuk
Ok.
I have no SQL Server near me to debug it, so go for the 1st option.

OK, I will, thanx again|||the reason you only get '0' is that no length for the returning varchar is specified. Change it e.g. to varchar(50) and you will get a correct result.|||Originally posted by jora
the reason you only get '0' is that no length for the returning varchar is specified. Change it e.g. to varchar(50) and you will get a correct result.

Thanx for your reply, I already found it out and fixed it.

Adding Leading Zeros

I need to make sure that numbers returned form a SELECT have at least one digit before the decimal point. Right now I have the following SQL statement in a DTS

SELECT PIN, ROUND (SUM(AREA/43560), 2) AS Expr1
FROM table GROUP BY PIN ORDER BY PIN

This produces lines like this ...

1-0005 -01-001,6250.410000
1-0008 -01-001,940.810000
1-0010 -01-001,9.230000
1-0010 -01-001A,.730000
1-0010 -01-002,73.520000
1-0010 -01-003,.680000

I need the output to look like this (check lines 4 and 6) ...

1-0005 -01-001,6250.410000
1-0008 -01-001,940.810000
1-0010 -01-001,9.230000
1-0010 -01-001A,0.730000
1-0010 -01-002,73.520000
1-0010 -01-003,0.680000

Any ideas?

DavidHello,

when you use Oracle you can convert the sum to a char with a special format mask

SELECT PIN, TO_CHAR(ROUND (SUM(AREA/43560), 2), '0.99' AS Expr1

Hope that helps ?

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Oops. Forgot to mention that I'm running SQLServer 7. Haven't found a comparable function yet.

David|||Hello again,

that is important :)

SELECT PIN, CAST(ROUND (SUM(AREA/43560), 2) AS MONEY) AS Expr1

I am using the enterprise manager and it look ok ...

Hope this help ?

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||When I tried

CAST(ROUND (SUM(AREA/43560), 2) AS MONEY)

I still got the following results

6250.4100
940.8100
9.2300
.7300
73.5200
.6800

:confused:

However, taking your lead, I tried

CAST(ROUND (SUM(AREA/43560), 2) AS CHAR)

and got

6250.410000
940.810000
9.230000
0.730000
73.520000
0.680000

:)

Works for me! Thanks!

David

Adding large amounts of text

this may seem like a simple question, but I have a report/lease agreement I need to put together and wanted to know the simpliest way to add large amounts of text. Basically its all the legal stuff most leases include in the amount of some 14 pages.

Should this be just one long string-- or does ssrs have another way to format this

thanks as always

KM

Have them all in one textbox and set the width of the textbox to the maximum required and set CanGrow=true to allow increase in height.

Then you can type your long string in the textbox and do som formatting in it. You can use Chr() function to do some formatting like carriage return, line feed, tab and many more.

Shyam

|||

thanks will give it a try and let ya know

km

Adding Integration Services to the existing Sqlserver 2005 - Question

Hello to everybody,

Installed the Integration Services to the existing Sqlserver 2005. But I am not seeing the 'Integration Services' in the

SQL Server Management Studio, like the 'SQL Server Agent' in the server. I am not sure what the next step is after installing the 'Integration Services'. Appreciate any help on this. Thanks for your time in advance.

Thanks,
Gopal.

It's a different server type, like Analysis Services. When you start a new connection, choose Integration Services instead of Database Engine.

|||

Also please notice that you should be doing most SSIS work in BIDS (Business Intelligence Dev Studio), not in SSMS. I recommend starting with Books Online or the Guided Tour: http://www.microsoft.com/sql/technologies/integration/tours.mspx, in particular Visual Development Environment Guided Tour.

Adding Integration Services Node in Server Management Studio

Hi,
Am just getting started with SSIS. Based on an article I saw online, I would expect to find an "Integration Services" node in Server Management Studio, but I don't see this node. I'm trying to create and execute a simple package.
Running...
Microsoft SQL Server 2005 - 9.00.1116 (Intel X86)
Apr 9 2005 20:56:37
Copyright (c) 1988-2004 Microsoft Corporation
Beta Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on a named instance.
Thanks,
krog
Have you tried in Object Explorer

Connect | Integration Services

Allan|||There it is. Thank you!|||I am testing this on Windows XP and when I run SSIS, I can connect to server but I don't see a way to connect to Integration Services. I see in the Providers folder DTSPackage but how do I execute this assuming this is what I need. I am new to SQL Express and I am trying to import a DBF file and create a database.

Neil|||I am trying to connnect through Object Browser, but I am getting Access Denied error. Please note I am not the Admin on the box. What kind of permission I need to register Integration Services connection on my Management studio? Finally I want to import some packages I developed on msdb.

Thanks|||Hi,

I am also experiencing Access Denied errors when using object explorer (on my workstation) to connect to SSIS.

I have searched and tried some tips but I am still not able to connect. I am a local administrator on the sql server, i have added myself to db_dtsadmin role in the msdb database.

Can anyone please shed some light on how to connect remotely to SSIS.

Thanks,
Jason|||SSIS only comes with Standard edition and above. SQL Express does not include SSIS.|||

Any additional information about this?

I also recieve "Access denied" when attempting to add an Integration Service connection through the object browser. It works fine on a co-worker's workstation. I have disabled my firewall. I'm on the same subnet as my co-worker who is able to connect.

Best regards,

Shad

Adding Integration Services Node in Server Management Studio

Hi,
Am just getting started with SSIS. Based on an article I saw online, I would expect to find an "Integration Services" node in Server Management Studio, but I don't see this node. I'm trying to create and execute a simple package.
Running...
Microsoft SQL Server 2005 - 9.00.1116 (Intel X86)
Apr 9 2005 20:56:37
Copyright (c) 1988-2004 Microsoft Corporation
Beta Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on a named instance.
Thanks,
krog
Have you tried in Object Explorer

Connect | Integration Services

Allan
|||There it is. Thank you!
|||I am testing this on Windows XP and when I run SSIS, I can connect to server but I don't see a way to connect to Integration Services. I see in the Providers folder DTSPackage but how do I execute this assuming this is what I need. I am new to SQL Express and I am trying to import a DBF file and create a database.

Neil|||I am trying to connnect through Object Browser, but I am getting Access Denied error. Please note I am not the Admin on the box. What kind of permission I need to register Integration Services connection on my Management studio? Finally I want to import some packages I developed on msdb.

Thanks|||Hi,

I am also experiencing Access Denied errors when using object explorer (on my workstation) to connect to SSIS.

I have searched and tried some tips but I am still not able to connect. I am a local administrator on the sql server, i have added myself to db_dtsadmin role in the msdb database.

Can anyone please shed some light on how to connect remotely to SSIS.

Thanks,
Jason|||SSIS only comes with Standard edition and above. SQL Express does not include SSIS.|||

Any additional information about this?

I also recieve "Access denied" when attempting to add an Integration Service connection through the object browser. It works fine on a co-worker's workstation. I have disabled my firewall. I'm on the same subnet as my co-worker who is able to connect.

Best regards,

Shad

Adding Integration Services Node in Server Management Studio

Hi,
Am just getting started with SSIS. Based on an article I saw online, I would expect to find an "Integration Services" node in Server Management Studio, but I don't see this node. I'm trying to create and execute a simple package.
Running...
Microsoft SQL Server 2005 - 9.00.1116 (Intel X86)
Apr 9 2005 20:56:37
Copyright (c) 1988-2004 Microsoft Corporation
Beta Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on a named instance.
Thanks,
krog
Have you tried in Object Explorer

Connect | Integration Services

Allan|||There it is. Thank you!|||I am testing this on Windows XP and when I run SSIS, I can connect to server but I don't see a way to connect to Integration Services. I see in the Providers folder DTSPackage but how do I execute this assuming this is what I need. I am new to SQL Express and I am trying to import a DBF file and create a database.

Neil|||I am trying to connnect through Object Browser, but I am getting Access Denied error. Please note I am not the Admin on the box. What kind of permission I need to register Integration Services connection on my Management studio? Finally I want to import some packages I developed on msdb.

Thanks|||Hi,

I am also experiencing Access Denied errors when using object explorer (on my workstation) to connect to SSIS.

I have searched and tried some tips but I am still not able to connect. I am a local administrator on the sql server, i have added myself to db_dtsadmin role in the msdb database.

Can anyone please shed some light on how to connect remotely to SSIS.

Thanks,
Jason|||SSIS only comes with Standard edition and above. SQL Express does not include SSIS.|||

Any additional information about this?

I also recieve "Access denied" when attempting to add an Integration Service connection through the object browser. It works fine on a co-worker's workstation. I have disabled my firewall. I'm on the same subnet as my co-worker who is able to connect.

Best regards,

Shad

Adding Integration Services Node in Server Management Studio

Hi,
Am just getting started with SSIS. Based on an article I saw online, I would expect to find an "Integration Services" node in Server Management Studio, but I don't see this node. I'm trying to create and execute a simple package.
Running...
Microsoft SQL Server 2005 - 9.00.1116 (Intel X86)
Apr 9 2005 20:56:37
Copyright (c) 1988-2004 Microsoft Corporation
Beta Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on a named instance.
Thanks,
krog
Have you tried in Object Explorer

Connect | Integration Services

Allan|||There it is. Thank you!|||I am testing this on Windows XP and when I run SSIS, I can connect to server but I don't see a way to connect to Integration Services. I see in the Providers folder DTSPackage but how do I execute this assuming this is what I need. I am new to SQL Express and I am trying to import a DBF file and create a database.

Neil|||I am trying to connnect through Object Browser, but I am getting Access Denied error. Please note I am not the Admin on the box. What kind of permission I need to register Integration Services connection on my Management studio? Finally I want to import some packages I developed on msdb.

Thanks|||Hi,

I am also experiencing Access Denied errors when using object explorer (on my workstation) to connect to SSIS.

I have searched and tried some tips but I am still not able to connect. I am a local administrator on the sql server, i have added myself to db_dtsadmin role in the msdb database.

Can anyone please shed some light on how to connect remotely to SSIS.

Thanks,
Jason|||SSIS only comes with Standard edition and above. SQL Express does not include SSIS.|||

Any additional information about this?

I also recieve "Access denied" when attempting to add an Integration Service connection through the object browser. It works fine on a co-worker's workstation. I have disabled my firewall. I'm on the same subnet as my co-worker who is able to connect.

Best regards,

Shad

Adding Integers

The code below has this line
SET @.SOGallons = @.ODTGallons

I need it to add the Current value of @.SOGallons to the newly selected value of @.ODTGallons and set that as the new value of @.SOGallons.

I've tried
SET @.SOGallons = @.SOGallons + @.ODTGallons

SET @.SOGalTemp = @.SOGallons
SET @.SOGallons= @.SOGalTemp + @.ODTGallons

Neither Worked

<CODE>
FROM [CSITSS].[dbo].[Orderdt] as ODT LEFT OUTER JOIN [CSITSS].[dbo].[Orddtcom] as OCOM
ON ODT.[Companydiv] = OCOM.[Companydiv] AND ODT.[OrderNumber] = OCOM.[OrderNumber] AND
ODT.[Sequence] = OCOM.[Sequence] WHERE ODT.[Companydiv]= 'GLPC-TRANS' AND ODT.[OrderNumber] = @.OrdNum AND
([LineType] = 'IP' OR [LineType] = 'SO' OR [LineType] = 'DL' OR [LineType] = 'PU')

OPEN TC1

FETCH NEXT FROM TC1 INTO @.LT, @.ODTGallons, @.ODTComm
WHILE @.@.FETCH_STATUS=0
BEGIN
IF @.LT = 'SO'
BEGIN
SET @.SplitTest = 1
SET @.SOGallons = @.ODTGallons
IF @.SOGallons > 0
BEGIN
SET @.SOGalTest = 1
END
ELSE
BEGIN
SET @.SOGalTest = 0
END
IF @.SplitTest <> @.SOGalTest
BEGIN
SET @.SOGalTest = 0
END
END
ELSE
BEGIN
SET @.SOGalTest = 1
END
FETCH NEXT FROM TC1 INTO @.LT, @.ODTGallons, @.ODTComm
END
CLOSE TC1
DEALLOCATE TC1</CODE>LineType Gallons Commodity
DL 4000 #2 ULSD DYED
IP 7000 87 NL / ETH
PU 4000 #2 ULSD DYED
SO 7000 87 NL / ETH

There may be multiple lines of any of the above line types.

IP = Initial Pickup
PU = Additional Pickup
SO = Stop Off
DL = Final Delivery

I need to know if all of the gallons that were picked up where delivered

IP + PU = SO + DL

I'm doing a check on the validity of the commodity type as well but with the forums helps we figured that one out yesterday.|||First, your issue is NULL issue.
You said
I've tried
SET @.SOGallons = @.SOGallons + @.ODTGallons

SET @.SOGalTemp = @.SOGallons
SET @.SOGallons= @.SOGalTemp + @.ODTGallons

But before you use @.SOGallons, you need to initialise it. Otherwise it stays as NULL. And whatever value added to it becomes NULL. This is why it failed. Say, insert this line "SET @.SOGallons = 0" before "SET @.SOGallons = @.SOGallons + @.ODTGallons".

Next, in your WHILE Loop, put "SET @.SplitTest = 1" before the loop started as it is a constant.|||Hey,

Thanks.

I know VB fairly well but am completely new to SQL as of about 3 weeks ago. I always seem to know what I want to do but am continually making small syntax errox that trip me up.

Adding information into one field that depends what is in another

Hi,

Im kind of stuck how to do this, i just tried using "when" and "then" commands in sql and its wiped my data.

My table is called "customer_info" and i have a field called "retailer". Retailer is dependant on a field called "clas".

Basically i would like to insert "4" into retailer where clas is equal to "glass".
And then reuse this command to do several more statements that are pretty much the same but just the values are changing.

Thanks in advance!Ha, i managed to sort it with a simple update query! No help needed now.

Cheers

Adding indexes to tables

I have a database on Server A and i copy the database over to Server B. But since the data on the database is transformed (I add new columsn to eacht able in the db) during the transfer, I simply transfer the data and nothing else.

Once the transfer is complete I want to add all the indexes on each table in the original db. How would i do that?Why don't you transfer the entire tables, with indexes, and then add your columns?

blindman|||Originally posted by blindman
Why don't you transfer the entire tables, with indexes, and then add your columns?

blindman

Because the indexes i want to add must include the columns i'm adding.|||Originally posted by vmlal
Once the transfer is complete I want to add all the indexes on each table in the original db. How would i do that?

Well then your statement isn't true..they're new indexes and yo need to build them...create a sql script modify them and run...

don't know of an automated way...|||How can the indexes from the tables in the original DB include the columns you are adding? That makes no sense.

You are going to need to write a script that adds whatever indexes you want, and then run the script after you modify your unindexed tables.

blindman|||Yeah i have to add new indexes incl the new columns but all the other indexes excl. the new columns need to be added. i have a scripts to add the new indexes.

Sum of the existing indexes have to be dropped before the new indexes can be added. But is there a way to aleast get the old indexes back?|||If you can come up with a logical way to tell SQL Server what indexes you want to keep and what indexes you don't, then you MAY be able to do this programmatically. Otherwise, SQL Server doesn't have a clue what you want to do.

Write a script. It is the best way to handle this.

blindman

Adding Indexes to Table

I'm looking for information on how to add indexes to a table in a SQL Server 2000 Database, why add them etc? Any source of good information on the web regarding this?

Hi,

I've been looking for this kind of info too. I have a feeling, and I think I have read somewhere, that the indexing is more or less automatic in SqlServer 2000 and that's why one can't find out how to do it. However, someone else would need to verify this...

Pettrer

|||

...indexes to a table ... why add them?

Lookup speed. Imagine using a dictionary that wasn't in alphabetical order...

|||And how could i use Enterprise Manager to add them to a table?|||

Hi again,

As I suspected, these are being set automatically for me (in SqlServer Management Studio Express) and are displayed in the "column properties" section. I guess the same goes for Enterprise manager.

Pettrer

|||

pettrer:

Hi again,

As I suspected, these are being set automatically for me (in SqlServer Management Studio Express) and are displayed in the "column properties" section. I guess the same goes for Enterprise manager.

Pettrer

I suspect this is a non clustered index. I was looking at how to explicitly set an index based on the execution plan.

|||

Hi,

To create index, you will need to use CREATE INDEX statement.

The syntax can be found from

http://msdn2.microsoft.com/en-us/library/ms188783.aspx

Adding index to published table

Is this possible?
How would I do it?
Rob
Rob,
if it's just to exist on the publisher then you can just go ahead and add it
using EM or TSQL. This index won't be propagated to the subscriber, and even
in SQL 2005 with the option to replicate schema changes this is true. To be
sure to get the index to all subscribers, you can use sp_addscriptexec.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
So can I also manaully add /modify /drop the same indexes at the subscriber
using EM and T-SQL as well? Planning to do some major performance tuning in
my application soon.
TQ in advance.
"Paul Ibison" wrote:

> Rob,
> if it's just to exist on the publisher then you can just go ahead and add it
> using EM or TSQL. This index won't be propagated to the subscriber, and even
> in SQL 2005 with the option to replicate schema changes this is true. To be
> sure to get the index to all subscribers, you can use sp_addscriptexec.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Yes - index alterations are not seen as schema changes in that regard. The
same is true in SQL Server 2005 where there is the option of automatic
schema alterations being replicated - ie indexes aren't.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

adding index NETS "Deadlock"

Can you guys help me understand the logic here.
have a very small jobQueue table
JobId
StartTime
EndTime
JobTypeId
ParentJobId
a few other trivial columns
table gets smacked all day long (thousands and thousands of times)
one stored procedure (spGetNextJob) is doing table scans based on no index
placed on StartTime. Taking 1,000+ ms to execute which is clearly
unacceptable.
I add a nonclustered index on StartTime and get Deadlocks in Production.
My confusion is that Indexes dont cause deadlocks, accessing objects in
inconsistent order causes deadlocks....
HOW, can adding an index on the table create a deadlock scenario.
(Table is constantly truncated, so never really has more than a couple
hundred records)
Greg Jackson
PDX, OregonJaxon wrote:

> My confusion is that Indexes dont cause deadlocks, accessing objects
> in inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
Try enabling trace flag T1204. When this flag is enabled, the server will
print out detailed information about any deadlocks.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Add a RowID BigINT Identity(1,1) to the beginning of the table.
Clustered UNIQUE index on RowID.
Noncluster index on Startime
Write stored procedures to do inserts, updates, and deletes.
Make developers use those procedures ONLY for accessing the table.
Watch the system run very fast.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
> Can you guys help me understand the logic here.
> have a very small jobQueue table
> JobId
> StartTime
> EndTime
> JobTypeId
> ParentJobId
> a few other trivial columns
> table gets smacked all day long (thousands and thousands of times)
> one stored procedure (spGetNextJob) is doing table scans based on no index
> placed on StartTime. Taking 1,000+ ms to execute which is clearly
> unacceptable.
> I add a nonclustered index on StartTime and get Deadlocks in Production.
> My confusion is that Indexes dont cause deadlocks, accessing objects in
> inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
> (Table is constantly truncated, so never really has more than a couple
> hundred records)
>
> Greg Jackson
> PDX, Oregon
>|||From Geoff's post, If you will not have that many rows, you might get away
with using an INT data type rather than a bigint
Ray Higdon MCSE, MCDBA, CCNA
--
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23z$JyxuAEHA.2480@.TK2MSFTNGP11.phx.gbl...
> Add a RowID BigINT Identity(1,1) to the beginning of the table.
> Clustered UNIQUE index on RowID.
> Noncluster index on Startime
> Write stored procedures to do inserts, updates, and deletes.
> Make developers use those procedures ONLY for accessing the table.
> Watch the system run very fast.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
> news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
index
>|||SQL Profiler has Deadlock and Deadlock Chain events.
Couple this with looking at the statements being issued, you can get a very
good idea of what's happening, especially which processes are involved.
I think you will find that you are getting deadlocks on only one table. Ther
e are a couple of causes of this that I've had to deal with.
1. You have a trigger on the table. The trigger joins the INSERTED or DELETE
D tables onto itself. This trigger query can result in a Share Table lock. S
o you have two process, both aquire a row level eXclusive lock. They then de
adlock each other attemptin
g to get the Share Table lock. This is an escalation deadlock. This type of
problem foxed my company for 3 years until SQL Server 7.0 can in with the De
adlock and Deadlock Chain events to track it down. It then took 2 hours to f
ind and fix.
2. Two query are both using the same table, and locking a lot of rows. But o
nly one is using an index, or they are using different indexes. The rows are
locked in the order given by the indexes. The rows are then locked out of s
equence. In your case, it s
ounds as if the purge is still scanning the table, so locking all rows.
I hope this helps|||currently, the PKEY and Clustered Index are on JobId Which is a FREAKING
GUID (not my choice).
Currently there is no index on StartTime but when I ADD a nonclustered
there, I get deadlocks.
I've already analyzed the crap out of the deadlock situation.
deadlock is on the job table and is ocurring due to the two sprocs listed
above.
the sprocs SUCK (use cursors, etc etc etc) I'll fix them later.
I just dont understand how adding a nonclustered to the timeStart Column
results in Deadlocks and removing the index alleviates them.
Sure, I will fix the sprocs later (Like a 3 week task including QA, etc)
Could it just be that with the index added, it makes inserts that much
slower such that some transaction is being heald open a little longer
resulting in deadlock ?
In other words, is the index just the straw that is breaking my camels back
?
this entire job queue crap is an architectural nightmare.....the rewrite is
coming in the near future, believe me.
GAJ|||sorry, I didnt mention the sprocs...
the culprits are "spCheckJob" and "spGetNextJob"
they are both quite ugly you dont want me to send the definitions for these
believe me.
GAJ

adding index NETS "Deadlock"

Can you guys help me understand the logic here.
have a very small jobQueue table
JobId
StartTime
EndTime
JobTypeId
ParentJobId
a few other trivial columns
table gets smacked all day long (thousands and thousands of times)
one stored procedure (spGetNextJob) is doing table scans based on no index
placed on StartTime. Taking 1,000+ ms to execute which is clearly
unacceptable.
I add a nonclustered index on StartTime and get Deadlocks in Production.
My confusion is that Indexes dont cause deadlocks, accessing objects in
inconsistent order causes deadlocks....
HOW, can adding an index on the table create a deadlock scenario.
(Table is constantly truncated, so never really has more than a couple
hundred records)
Greg Jackson
PDX, OregonJaxon wrote:
> My confusion is that Indexes dont cause deadlocks, accessing objects
> in inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
Try enabling trace flag T1204. When this flag is enabled, the server will
print out detailed information about any deadlocks.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Add a RowID BigINT Identity(1,1) to the beginning of the table.
Clustered UNIQUE index on RowID.
Noncluster index on Startime
Write stored procedures to do inserts, updates, and deletes.
Make developers use those procedures ONLY for accessing the table.
Watch the system run very fast.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
> Can you guys help me understand the logic here.
> have a very small jobQueue table
> JobId
> StartTime
> EndTime
> JobTypeId
> ParentJobId
> a few other trivial columns
> table gets smacked all day long (thousands and thousands of times)
> one stored procedure (spGetNextJob) is doing table scans based on no index
> placed on StartTime. Taking 1,000+ ms to execute which is clearly
> unacceptable.
> I add a nonclustered index on StartTime and get Deadlocks in Production.
> My confusion is that Indexes dont cause deadlocks, accessing objects in
> inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
> (Table is constantly truncated, so never really has more than a couple
> hundred records)
>
> Greg Jackson
> PDX, Oregon
>|||From Geoff's post, If you will not have that many rows, you might get away
with using an INT data type rather than a bigint
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23z$JyxuAEHA.2480@.TK2MSFTNGP11.phx.gbl...
> Add a RowID BigINT Identity(1,1) to the beginning of the table.
> Clustered UNIQUE index on RowID.
> Noncluster index on Startime
> Write stored procedures to do inserts, updates, and deletes.
> Make developers use those procedures ONLY for accessing the table.
> Watch the system run very fast.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
> news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
> > Can you guys help me understand the logic here.
> >
> > have a very small jobQueue table
> >
> > JobId
> > StartTime
> > EndTime
> > JobTypeId
> > ParentJobId
> > a few other trivial columns
> >
> > table gets smacked all day long (thousands and thousands of times)
> >
> > one stored procedure (spGetNextJob) is doing table scans based on no
index
> > placed on StartTime. Taking 1,000+ ms to execute which is clearly
> > unacceptable.
> >
> > I add a nonclustered index on StartTime and get Deadlocks in Production.
> >
> > My confusion is that Indexes dont cause deadlocks, accessing objects in
> > inconsistent order causes deadlocks....
> >
> > HOW, can adding an index on the table create a deadlock scenario.
> >
> > (Table is constantly truncated, so never really has more than a couple
> > hundred records)
> >
> >
> > Greg Jackson
> > PDX, Oregon
> >
> >
>|||SQL Profiler has Deadlock and Deadlock Chain events.
Couple this with looking at the statements being issued, you can get a very good idea of what's happening, especially which processes are involved
I think you will find that you are getting deadlocks on only one table. There are a couple of causes of this that I've had to deal with
1. You have a trigger on the table. The trigger joins the INSERTED or DELETED tables onto itself. This trigger query can result in a Share Table lock. So you have two process, both aquire a row level eXclusive lock. They then deadlock each other attempting to get the Share Table lock. This is an escalation deadlock. This type of problem foxed my company for 3 years until SQL Server 7.0 can in with the Deadlock and Deadlock Chain events to track it down. It then took 2 hours to find and fix
2. Two query are both using the same table, and locking a lot of rows. But only one is using an index, or they are using different indexes. The rows are locked in the order given by the indexes. The rows are then locked out of sequence. In your case, it sounds as if the purge is still scanning the table, so locking all rows
I hope this helps|||currently, the PKEY and Clustered Index are on JobId Which is a FREAKING
GUID (not my choice).
Currently there is no index on StartTime but when I ADD a nonclustered
there, I get deadlocks.
I've already analyzed the crap out of the deadlock situation.
deadlock is on the job table and is ocurring due to the two sprocs listed
above.
the sprocs SUCK (use cursors, etc etc etc) I'll fix them later.
I just dont understand how adding a nonclustered to the timeStart Column
results in Deadlocks and removing the index alleviates them.
Sure, I will fix the sprocs later (Like a 3 week task including QA, etc)
Could it just be that with the index added, it makes inserts that much
slower such that some transaction is being heald open a little longer
resulting in deadlock ?
In other words, is the index just the straw that is breaking my camels back
?
this entire job queue crap is an architectural nightmare.....the rewrite is
coming in the near future, believe me.
GAJ|||sorry, I didnt mention the sprocs...
the culprits are "spCheckJob" and "spGetNextJob"
they are both quite ugly you dont want me to send the definitions for these
believe me.
GAJ

Adding INDEX causes DEADLOCKS?

I have a very small table (50 rows, 50 columns).
I have 4 servers using the table:
1 server updates the table ("UPDATE table SET col1='', col2='',
LastUpdate=GetDate()").. about 20 rows updated a second.
3 servers pull data ("SELECT * FROM table Where
LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
times a second each.
Prior to last week, i did not have the lastupdate column. but it
obviously made sense to me to add it (why pull everything every 100ms
when you can only pull what you need via the LastUpdate, maybe 3-4
rows at a time). so i add the lastupdate column and make an index on
it.
now as soon as i run it, every 2-4 minutes i get a deadlock (!).
execution path is showing a bookmark lookup with index seek because i
have two conditions in the where clause: "lastupdate>(time
oflastrequest) and COL3>0". i have tried making an index based upon
both lastUpdate,Col3 but the results are the same (lots of deadlocks)
I have removed the index, kept the lastupdate column, same queries,
and everything works fine -- except, of course, that it is doing a
table scan for every lookup. the table will be growing soon and i
will be in trouble. so i need some help here, any ideas?
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
You update the whole table every time? Why?
|||"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.co m...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
Easiest way to solve these problems is with more locking. I know, it sounds
weird, but more locking will separate the deadlocking processes and properly
serialize them. I suggest in your Update statement add a TABLOCKX hint.
That will exclusively lock the entire table for the duration of the update.
This will also give you more predictable results as the table will only be
read when it is in a completely consistent state between updates.
David
|||The deadlock is due to that for the 3 server pulling data, it needs to get
page/row lock on the non-clustered index first, then get page/row lock on
the base table(while holding the lock on the non-clustered index), while the
update gets lock on base table first, then the lock on page/row in the
non-clustered index. To break the circle, you can either create a clustered
index on the LastUpdate field instead of a non-clustered index, or either
specify TABLOCKX hint for the update or specify readpast lock hint for the
reader.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve cabello" <basistrdr@.hotmail.com> wrote in message
news:4d625a1.0405240841.4c3a0ba9@.posting.google.co m...
> I have a very small table (50 rows, 50 columns).
> I have 4 servers using the table:
> 1 server updates the table ("UPDATE table SET col1='', col2='',
> LastUpdate=GetDate()").. about 20 rows updated a second.
> 3 servers pull data ("SELECT * FROM table Where
> LastUpdate>(lastrequest) and COL3>0") the 3 servers pull data about 6
> times a second each.
> Prior to last week, i did not have the lastupdate column. but it
> obviously made sense to me to add it (why pull everything every 100ms
> when you can only pull what you need via the LastUpdate, maybe 3-4
> rows at a time). so i add the lastupdate column and make an index on
> it.
> now as soon as i run it, every 2-4 minutes i get a deadlock (!).
> execution path is showing a bookmark lookup with index seek because i
> have two conditions in the where clause: "lastupdate>(time
> oflastrequest) and COL3>0". i have tried making an index based upon
> both lastUpdate,Col3 but the results are the same (lots of deadlocks)
> I have removed the index, kept the lastupdate column, same queries,
> and everything works fine -- except, of course, that it is doing a
> table scan for every lookup. the table will be growing soon and i
> will be in trouble. so i need some help here, any ideas?
|||"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:u4Gn6SuQEHA.2408@.tk2msftngp13.phx.gbl...
> The deadlock is due to that for the 3 server pulling data, it needs to get
> page/row lock on the non-clustered index first, then get page/row lock on
> the base table(while holding the lock on the non-clustered index), while
the
> update gets lock on base table first, then the lock on page/row in the
> non-clustered index. To break the circle, you can either create a
clustered
> index on the LastUpdate field instead of a non-clustered index, or either
> specify TABLOCKX hint for the update or specify readpast lock hint for the
> reader.
> --
> Gang He
> SQL Server Storage Engine Development
Good info. Thanks for the post Gang.
David