intermittent locks

We are experiencing problems intermittently.

Users report problems of various types including timeout messages. We investigate and find a user has acquired a lock which is blocking other users.

We contact the user and they have usually completed their activity and are not always aware of any problem despite them owning a lock.

When the user logs out of the application the lock clears and the system returns to normal.

Indexes have been rebuilt, auto update statistics is on.

Does anyone have any suggestions? :cool:The first thing I'd do is start watching for locks to determine how often they occur, and ask the users if they know of any activity that causes the problems associated with locking/blocking (that may give you clues about what you need to watch).

Once you understand what you are looking for, run a trace using SQL Profiler at the same time as a Performance Monitor trace watching for locking/blocking. The PerfMon trace will show you when the problem occurs, the Profiler trace will show you what caused the problem.

When you understand the cause of the problem, you can then look at changing the application to avoid the problem.

-PatP|||We are trying to gather more information from the users to track this down.

Anecdotally users believe that they have finished their activity and are simply still logged on or are running searches.

We haven't needed to kill a session, the user simply logs off.

It's almost as though the lock has been taken but not released when the activity has finished.

Does this sound likely/possible? If so any ideas what could be causing it?|||Does this sound likely/possible? If so any ideas what could be causing it?Yes, it sounds rather likely.

I'd suspect that the problem is something that the code is doing "behind the curtains" that the user is completely unaware of, but is still causing havok. Until you can compare the two traces (or provide LOTS of additional insight into your application and server configuration), we can only guess.

-PatP

intermittent connection problems

We are having an intermittent connection problems with some DTS
packages. We get the following error about 1 out of every 15 days
Login failed for user '(null)'. Reason: Not associated
One DTS package connects to remote SQL servers with a linked server,
another package uses a OLE DB provide connection to connect to a remote
server.
Configuration:
Client network configuration TCP/IP NamePipes
Server network configuration TCP/IP NamePipes
SQLServer 2000 service pack 3
Windows 2000 Server.
Authentication is set for both SQL Server and Windows
Has anyone seen this type of problem or have any troubleshooting advice
thanksHi
If SQL Server can not authenticate the user against the Domain Controller,
it has no option but to deny the connection. So you will see this message.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<scott.yoder@.ngc.com> wrote in message
news:1112822349.200542.189700@.g14g2000cwa.googlegroups.com...
> We are having an intermittent connection problems with some DTS
> packages. We get the following error about 1 out of every 15 days
> Login failed for user '(null)'. Reason: Not associated
> One DTS package connects to remote SQL servers with a linked server,
> another package uses a OLE DB provide connection to connect to a remote
> server.
> Configuration:
> Client network configuration TCP/IP NamePipes
> Server network configuration TCP/IP NamePipes
> SQLServer 2000 service pack 3
> Windows 2000 Server.
> Authentication is set for both SQL Server and Windows
>
> Has anyone seen this type of problem or have any troubleshooting advice
> thanks
>

intermittent connection problems

We are having an intermittent connection problems with some DTS
packages. We get the following error about 1 out of every 15 days
Login failed for user '(null)'. Reason: Not associated
One DTS package connects to remote SQL servers with a linked server,
another package uses a OLE DB provide connection to connect to a remote
server.
Configuration:
Client network configuration TCP/IP NamePipes
Server network configuration TCP/IP NamePipes
SQLServer 2000 service pack 3
Windows 2000 Server.
Authentication is set for both SQL Server and Windows
Has anyone seen this type of problem or have any troubleshooting advice
thanks
Hi
If SQL Server can not authenticate the user against the Domain Controller,
it has no option but to deny the connection. So you will see this message.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<scott.yoder@.ngc.com> wrote in message
news:1112822349.200542.189700@.g14g2000cwa.googlegr oups.com...
> We are having an intermittent connection problems with some DTS
> packages. We get the following error about 1 out of every 15 days
> Login failed for user '(null)'. Reason: Not associated
> One DTS package connects to remote SQL servers with a linked server,
> another package uses a OLE DB provide connection to connect to a remote
> server.
> Configuration:
> Client network configuration TCP/IP NamePipes
> Server network configuration TCP/IP NamePipes
> SQLServer 2000 service pack 3
> Windows 2000 Server.
> Authentication is set for both SQL Server and Windows
>
> Has anyone seen this type of problem or have any troubleshooting advice
> thanks
>
sql

Intermittent connection dramas: "Cannot open user default database..."

Hi,
I've been experiencing some very strange, intermittent failures connecting
to an MS SQL database... basically at seemingly random times the server
decides that it can't open the requested database (reporting error 4062:
"Cannot open user default database. Using master database instead"). I've
so far been unable to track down any useful background to this error
message; has anyone on this group come across similar problems? Any clues
as to what might be triggering this sort of fault? More specific details
follow:
* In the majority of cases, connection is established correctly (albeit
sometimes a little slowly).
* Connections are being made to the local machine, using straightforward,
static code. The problem isn't something trivial like a mis-typed
database name, incorrect password etc. Re-running the SAME CODE will
eventually succeed.
* The server isn't particularly heavily loaded, and I can perceive no
pattern to the fault (it seems no more prevalent when the
server's busy, nor after any particular events).
* Nothing relevant is documented in the Event Viewer, nor the server logs.
Am I looking in the right place?
* We're running SQL Server 2005 Express Edition, latest service pack...
are there any connection rate limitations on this software? All
documentation I've seen indicates that there are not, but this behaviour
almost suggests that there may be...
* Connecting (from local machine) using PHP mssql module (latest stable
version).
* Although possibly symptomatic of a DoS, there's no other evidence to
suggest that this is the case (TCP to the service is firewalled).
Would be grateful for any advice on this issue.
Cheers,
Alex.
Alex Craven wrote:
> Hi,
> I've been experiencing some very strange, intermittent failures connecting
> to an MS SQL database... basically at seemingly random times the server
> decides that it can't open the requested database (reporting error 4062:
> "Cannot open user default database. Using master database instead"). I've
> so far been unable to track down any useful background to this error
> message; has anyone on this group come across similar problems? Any clues
> as to what might be triggering this sort of fault? More specific details
> follow:
> * In the majority of cases, connection is established correctly (albeit
> sometimes a little slowly).
> * Connections are being made to the local machine, using straightforward,
> static code. The problem isn't something trivial like a mis-typed
> database name, incorrect password etc. Re-running the SAME CODE will
> eventually succeed.
> * The server isn't particularly heavily loaded, and I can perceive no
> pattern to the fault (it seems no more prevalent when the
> server's busy, nor after any particular events).
> * Nothing relevant is documented in the Event Viewer, nor the server logs.
> Am I looking in the right place?
> * We're running SQL Server 2005 Express Edition, latest service pack...
> are there any connection rate limitations on this software? All
> documentation I've seen indicates that there are not, but this behaviour
> almost suggests that there may be...
> * Connecting (from local machine) using PHP mssql module (latest stable
> version).
> * Although possibly symptomatic of a DoS, there's no other evidence to
> suggest that this is the case (TCP to the service is firewalled).
>
> Would be grateful for any advice on this issue.
> Cheers,
> Alex.
>
>
Make sure the "problem" database isn't configured for auto-close, also
make sure it's not the target for log-shipping, or some other process
that might take it offline to do a restore, etc.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Do you periodically rename, remove, or detach the database?
Do you have any jobs that put the database into single-user mode or
otherwise alter the availability of the database?
|||On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
[...]
> Make sure the "problem" database isn't configured for auto-close, also
> make sure it's not the target for log-shipping, or some other process
> that might take it offline to do a restore, etc.
Hi Tracy et al,
Thanks for all your advice; so far it looks like the auto-close 'feature'
was most likely the root of my problems (or at least a major factor...);
the system seems to be performing rather more reliably with that disabled.
Cheers,
Alex.
|||Alex Craven wrote:
> On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
> [...]
>
> Hi Tracy et al,
> Thanks for all your advice; so far it looks like the auto-close 'feature'
> was most likely the root of my problems (or at least a major factor...);
> the system seems to be performing rather more reliably with that disabled.
> Cheers,
> Alex.
>
That auto-close option should NEVER be enabled for a production
database. Another one to watch out for is auto-shrink.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql

Intermittent connection dramas: "Cannot open user default database..."

Hi,
I've been experiencing some very strange, intermittent failures connecting
to an MS SQL database... basically at seemingly random times the server
decides that it can't open the requested database (reporting error 4062:
"Cannot open user default database. Using master database instead"). I've
so far been unable to track down any useful background to this error
message; has anyone on this group come across similar problems? Any clues
as to what might be triggering this sort of fault? More specific details
follow:
* In the majority of cases, connection is established correctly (albeit
sometimes a little slowly).
* Connections are being made to the local machine, using straightforward,
static code. The problem isn't something trivial like a mis-typed
database name, incorrect password etc. Re-running the SAME CODE will
eventually succeed.
* The server isn't particularly heavily loaded, and I can perceive no
pattern to the fault (it seems no more prevalent when the
server's busy, nor after any particular events).
* Nothing relevant is documented in the Event Viewer, nor the server logs.
Am I looking in the right place'
* We're running SQL Server 2005 Express Edition, latest service pack...
are there any connection rate limitations on this software? All
documentation I've seen indicates that there are not, but this behaviour
almost suggests that there may be...
* Connecting (from local machine) using PHP mssql module (latest stable
version).
* Although possibly symptomatic of a DoS, there's no other evidence to
suggest that this is the case (TCP to the service is firewalled).
Would be grateful for any advice on this issue.
Cheers,
Alex.HI Alex
Have you checked for Anti-virus or intrusion detection software in the
server, this can cause connections to fail. If you have the autoclose option
set for the database or attaching the database when you connect then if the
AV software is scanning the files you will not be able to do this.
You may also want to eliminate any particular machine or see if this occurs
on the server, to rule out general networking issues. Check firewalls are
correctly configured and if you are using group policies that they are not
interfering.
You may also want to check if you are service packed to a recent version.
John
"Alex Craven" wrote:
> Hi,
> I've been experiencing some very strange, intermittent failures connecting
> to an MS SQL database... basically at seemingly random times the server
> decides that it can't open the requested database (reporting error 4062:
> "Cannot open user default database. Using master database instead"). I've
> so far been unable to track down any useful background to this error
> message; has anyone on this group come across similar problems? Any clues
> as to what might be triggering this sort of fault? More specific details
> follow:
> * In the majority of cases, connection is established correctly (albeit
> sometimes a little slowly).
> * Connections are being made to the local machine, using straightforward,
> static code. The problem isn't something trivial like a mis-typed
> database name, incorrect password etc. Re-running the SAME CODE will
> eventually succeed.
> * The server isn't particularly heavily loaded, and I can perceive no
> pattern to the fault (it seems no more prevalent when the
> server's busy, nor after any particular events).
> * Nothing relevant is documented in the Event Viewer, nor the server logs.
> Am I looking in the right place'
> * We're running SQL Server 2005 Express Edition, latest service pack...
> are there any connection rate limitations on this software? All
> documentation I've seen indicates that there are not, but this behaviour
> almost suggests that there may be...
> * Connecting (from local machine) using PHP mssql module (latest stable
> version).
> * Although possibly symptomatic of a DoS, there's no other evidence to
> suggest that this is the case (TCP to the service is firewalled).
>
> Would be grateful for any advice on this issue.
> Cheers,
> Alex.
>
>|||Alex Craven wrote:
> Hi,
> I've been experiencing some very strange, intermittent failures connecting
> to an MS SQL database... basically at seemingly random times the server
> decides that it can't open the requested database (reporting error 4062:
> "Cannot open user default database. Using master database instead"). I've
> so far been unable to track down any useful background to this error
> message; has anyone on this group come across similar problems? Any clues
> as to what might be triggering this sort of fault? More specific details
> follow:
> * In the majority of cases, connection is established correctly (albeit
> sometimes a little slowly).
> * Connections are being made to the local machine, using straightforward,
> static code. The problem isn't something trivial like a mis-typed
> database name, incorrect password etc. Re-running the SAME CODE will
> eventually succeed.
> * The server isn't particularly heavily loaded, and I can perceive no
> pattern to the fault (it seems no more prevalent when the
> server's busy, nor after any particular events).
> * Nothing relevant is documented in the Event Viewer, nor the server logs.
> Am I looking in the right place'
> * We're running SQL Server 2005 Express Edition, latest service pack...
> are there any connection rate limitations on this software? All
> documentation I've seen indicates that there are not, but this behaviour
> almost suggests that there may be...
> * Connecting (from local machine) using PHP mssql module (latest stable
> version).
> * Although possibly symptomatic of a DoS, there's no other evidence to
> suggest that this is the case (TCP to the service is firewalled).
>
> Would be grateful for any advice on this issue.
> Cheers,
> Alex.
>
>
Make sure the "problem" database isn't configured for auto-close, also
make sure it's not the target for log-shipping, or some other process
that might take it offline to do a restore, etc.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Do you periodically rename, remove, or detach the database?
Do you have any jobs that put the database into single-user mode or
otherwise alter the availability of the database?|||On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
[...]
> Make sure the "problem" database isn't configured for auto-close, also
> make sure it's not the target for log-shipping, or some other process
> that might take it offline to do a restore, etc.
Hi Tracy et al,
Thanks for all your advice; so far it looks like the auto-close 'feature'
was most likely the root of my problems (or at least a major factor...);
the system seems to be performing rather more reliably with that disabled.
Cheers,
Alex.|||Alex Craven wrote:
> On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
> [...]
>> Make sure the "problem" database isn't configured for auto-close, also
>> make sure it's not the target for log-shipping, or some other process
>> that might take it offline to do a restore, etc.
>
> Hi Tracy et al,
> Thanks for all your advice; so far it looks like the auto-close 'feature'
> was most likely the root of my problems (or at least a major factor...);
> the system seems to be performing rather more reliably with that disabled.
> Cheers,
> Alex.
>
That auto-close option should NEVER be enabled for a production
database. Another one to watch out for is auto-shrink.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Intermittent connection dramas: "Cannot open user default database..."

Hi,
I've been experiencing some very strange, intermittent failures connecting
to an MS SQL database... basically at seemingly random times the server
decides that it can't open the requested database (reporting error 4062:
"Cannot open user default database. Using master database instead"). I've
so far been unable to track down any useful background to this error
message; has anyone on this group come across similar problems? Any clues
as to what might be triggering this sort of fault? More specific details
follow:
* In the majority of cases, connection is established correctly (albeit
sometimes a little slowly).
* Connections are being made to the local machine, using straightforward,
static code. The problem isn't something trivial like a mis-typed
database name, incorrect password etc. Re-running the SAME CODE will
eventually succeed.
* The server isn't particularly heavily loaded, and I can perceive no
pattern to the fault (it seems no more prevalent when the
server's busy, nor after any particular events).
* Nothing relevant is documented in the Event Viewer, nor the server logs.
Am I looking in the right place'
* We're running SQL Server 2005 Express Edition, latest service pack...
are there any connection rate limitations on this software? All
documentation I've seen indicates that there are not, but this behaviour
almost suggests that there may be...
* Connecting (from local machine) using php mssql module (latest stable
version).
* Although possibly symptomatic of a DoS, there's no other evidence to
suggest that this is the case (TCP to the service is firewalled).
Would be grateful for any advice on this issue.
Cheers,
Alex.Alex Craven wrote:
> Hi,
> I've been experiencing some very strange, intermittent failures connecting
> to an MS SQL database... basically at seemingly random times the server
> decides that it can't open the requested database (reporting error 4062:
> "Cannot open user default database. Using master database instead"). I've
> so far been unable to track down any useful background to this error
> message; has anyone on this group come across similar problems? Any clues
> as to what might be triggering this sort of fault? More specific details
> follow:
> * In the majority of cases, connection is established correctly (albeit
> sometimes a little slowly).
> * Connections are being made to the local machine, using straightforward,
> static code. The problem isn't something trivial like a mis-typed
> database name, incorrect password etc. Re-running the SAME CODE will
> eventually succeed.
> * The server isn't particularly heavily loaded, and I can perceive no
> pattern to the fault (it seems no more prevalent when the
> server's busy, nor after any particular events).
> * Nothing relevant is documented in the Event Viewer, nor the server logs.
> Am I looking in the right place'
> * We're running SQL Server 2005 Express Edition, latest service pack...
> are there any connection rate limitations on this software? All
> documentation I've seen indicates that there are not, but this behaviour
> almost suggests that there may be...
> * Connecting (from local machine) using php mssql module (latest stable
> version).
> * Although possibly symptomatic of a DoS, there's no other evidence to
> suggest that this is the case (TCP to the service is firewalled).
>
> Would be grateful for any advice on this issue.
> Cheers,
> Alex.
>
>
Make sure the "problem" database isn't configured for auto-close, also
make sure it's not the target for log-shipping, or some other process
that might take it offline to do a restore, etc.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Do you periodically rename, remove, or detach the database?
Do you have any jobs that put the database into single-user mode or
otherwise alter the availability of the database?|||On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
[...]
> Make sure the "problem" database isn't configured for auto-close, also
> make sure it's not the target for log-shipping, or some other process
> that might take it offline to do a restore, etc.
Hi Tracy et al,
Thanks for all your advice; so far it looks like the auto-close 'feature'
was most likely the root of my problems (or at least a major factor...);
the system seems to be performing rather more reliably with that disabled.
Cheers,
Alex.|||Alex Craven wrote:
> On Fri, 29 Sep 2006 08:14:30 -0500, Tracy McKibben uttered:
> [...]
>
> Hi Tracy et al,
> Thanks for all your advice; so far it looks like the auto-close 'feature'
> was most likely the root of my problems (or at least a major factor...);
> the system seems to be performing rather more reliably with that disabled.
> Cheers,
> Alex.
>
That auto-close option should NEVER be enabled for a production
database. Another one to watch out for is auto-shrink.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Interface for SQL Server database

Comments and ideas for software tools (ActiveX components)
to make a user interface for database.Do a google search

Madhivanan

Petteri wrote:
> Comments and ideas for software tools (ActiveX components)
> to make a user interface for database.|||Like this ;-)

http://www.google.fi/search?hl=fi&q...ogle-haku&meta=

Thank You, it helps

"Madhivanan" <madhivanan2001@.gmail.com> kirjoitti
viestiss:1149240372.946923.100540@.i39g2000cwa.goo glegroups.com...
> Do a google search
> Madhivanan
> Petteri wrote:
>> Comments and ideas for software tools (ActiveX components)
>> to make a user interface for database.|||Petteri (peter.rinne@.elperi.fi) writes:
> Comments and ideas for software tools (ActiveX components)
> to make a user interface for database.

Not really sure what the question is about, but if you work with ActiveX,
the obvious choice of client API is ADO.

But why ActiveX in this day and age? Why not .Net? Then you can use
ADO .Net, which despite the name is radically different from ADO -
and a whole lot better.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

interesting update/insert trigger problem (null issue)

Ok so here is the issue. I am thinking I somehow have to clear the old data
after executing the trigger.
So I add a user Joe Brown with his info on a users table, a trigger fires
and dumps the duplicate data into a users-dup table (for other justifiable
purposes). Update does the same basic thing.
Works fine. Here is the problem. I then add another name. Jack Black and
his info, but he has some null values... like i don't know his address. So
now the trigger fires and all of the duplicate data is carried across to the
dup table... except where there was no data (NULL) in a field... it is
adding the last "real" data set in replace of the null. SO Jack Black has
Joe Brown's address in his field... since it was the last "not null" value
entered in that column.
Looking for ideas. Figured it is something simple, I am just missing. Like
some sort of purge call.
Below is the code I am using:
for insert:
CREATE TRIGGER insertUserMrktg ON [dbo].[USERS]
FOR INSERT
AS
insert into user_marketing (greeting, fName, lName, title, compName,
address, city, provState, fk_country,
zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
emailType, addedBy, fk_userID)
select greeting, fName, lName, title, compName, address, city, provState,
fk_country,
zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
emailType, addedBy, pk_userID
FROM Inserted
for update:
CREATE TRIGGER updateUserMrktg ON [dbo].[USERS]
FOR UPDATE
AS
update a
set a.greeting=b.greeting,
a.fName=b.fName,
a.lName=b.lName,
a.title=b.title,
a.compName=b.compName,
a.address=b.address,
a.city=b.city,
a.provState=b.provState,
a.fk_country=b.fk_country,
a.zip=b.zip,
a.email=b.email,
a.phone=b.phone,
a.phoneext=b.phoneext,
a.fax=b.fax,
a.fk_language=b.fk_language,
a.fk_segment=b.fk_segment,
a.fk_job=b.fk_job,
a.emailType=b.emailType,
a.addedBy=b.addedBy
FROM user_marketing a, users b
where a.fk_userID=
(SELECT pk_userID
FROM Inserted)
Thanks!Two questions.
1 - Why do you want to do this?
2 - How can we identify the last "real" data inserted in the table?, How do
you know it is "real" and not a fake like you are trying to do?
AMB
"cheezebeetle" wrote:

> Ok so here is the issue. I am thinking I somehow have to clear the old da
ta
> after executing the trigger.
> So I add a user Joe Brown with his info on a users table, a trigger fires
> and dumps the duplicate data into a users-dup table (for other justifiable
> purposes). Update does the same basic thing.
> Works fine. Here is the problem. I then add another name. Jack Black an
d
> his info, but he has some null values... like i don't know his address. S
o
> now the trigger fires and all of the duplicate data is carried across to t
he
> dup table... except where there was no data (NULL) in a field... it is
> adding the last "real" data set in replace of the null. SO Jack Black has
> Joe Brown's address in his field... since it was the last "not null" value
> entered in that column.
> Looking for ideas. Figured it is something simple, I am just missing. Li
ke
> some sort of purge call.
> Below is the code I am using:
> for insert:
> CREATE TRIGGER insertUserMrktg ON [dbo].[USERS]
> FOR INSERT
> AS
> insert into user_marketing (greeting, fName, lName, title, compName,
> address, city, provState, fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, fk_userID)
> select greeting, fName, lName, title, compName, address, city, provState,
> fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, pk_userID
> FROM Inserted
> for update:
> CREATE TRIGGER updateUserMrktg ON [dbo].[USERS]
> FOR UPDATE
> AS
> update a
> set a.greeting=b.greeting,
> a.fName=b.fName,
> a.lName=b.lName,
> a.title=b.title,
> a.compName=b.compName,
> a.address=b.address,
> a.city=b.city,
> a.provState=b.provState,
> a.fk_country=b.fk_country,
> a.zip=b.zip,
> a.email=b.email,
> a.phone=b.phone,
> a.phoneext=b.phoneext,
> a.fax=b.fax,
> a.fk_language=b.fk_language,
> a.fk_segment=b.fk_segment,
> a.fk_job=b.fk_job,
> a.emailType=b.emailType,
> a.addedBy=b.addedBy
> FROM user_marketing a, users b
> where a.fk_userID=
> (SELECT pk_userID
> FROM Inserted)
>
> Thanks!
>|||Hi
Triggers are executed per statement, which can update multiple rows. Using
where a.fk_userID= (SELECT pk_userID FROM Inserted) will return just one
arbitrary value. You are also not relating user_marketing to users
To keep user_marketing in step try:
update a
set a.greeting=b.greeting,
a.fName=b.fName,
a.lName=b.lName,
a.title=b.title,
a.compName=b.compName,
a.address=b.address,
a.city=b.city,
a.provState=b.provState,
a.fk_country=b.fk_country,
a.zip=b.zip,
a.email=b.email,
a.phone=b.phone,
a.phoneext=b.phoneext,
a.fax=b.fax,
a.fk_language=b.fk_language,
a.fk_segment=b.fk_segment,
a.fk_job=b.fk_job,
a.emailType=b.emailType,
a.addedBy=b.addedBy
FROM dbo.user_marketing a
JOIN Inserted b ON b.pk_userID = a.fk_userID
John
"cheezebeetle" wrote:

> Ok so here is the issue. I am thinking I somehow have to clear the old da
ta
> after executing the trigger.
> So I add a user Joe Brown with his info on a users table, a trigger fires
> and dumps the duplicate data into a users-dup table (for other justifiable
> purposes). Update does the same basic thing.
> Works fine. Here is the problem. I then add another name. Jack Black an
d
> his info, but he has some null values... like i don't know his address. S
o
> now the trigger fires and all of the duplicate data is carried across to t
he
> dup table... except where there was no data (NULL) in a field... it is
> adding the last "real" data set in replace of the null. SO Jack Black has
> Joe Brown's address in his field... since it was the last "not null" value
> entered in that column.
> Looking for ideas. Figured it is something simple, I am just missing. Li
ke
> some sort of purge call.
> Below is the code I am using:
> for insert:
> CREATE TRIGGER insertUserMrktg ON [dbo].[USERS]
> FOR INSERT
> AS
> insert into user_marketing (greeting, fName, lName, title, compName,
> address, city, provState, fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, fk_userID)
> select greeting, fName, lName, title, compName, address, city, provState,
> fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, pk_userID
> FROM Inserted
> for update:
> CREATE TRIGGER updateUserMrktg ON [dbo].[USERS]
> FOR UPDATE
> AS
> update a
> set a.greeting=b.greeting,
> a.fName=b.fName,
> a.lName=b.lName,
> a.title=b.title,
> a.compName=b.compName,
> a.address=b.address,
> a.city=b.city,
> a.provState=b.provState,
> a.fk_country=b.fk_country,
> a.zip=b.zip,
> a.email=b.email,
> a.phone=b.phone,
> a.phoneext=b.phoneext,
> a.fax=b.fax,
> a.fk_language=b.fk_language,
> a.fk_segment=b.fk_segment,
> a.fk_job=b.fk_job,
> a.emailType=b.emailType,
> a.addedBy=b.addedBy
> FROM user_marketing a, users b
> where a.fk_userID=
> (SELECT pk_userID
> FROM Inserted)
>
> Thanks!
>|||Thanks John...
That worked. Duh...
"John Bell" wrote:
> Hi
> Triggers are executed per statement, which can update multiple rows. Using
> where a.fk_userID= (SELECT pk_userID FROM Inserted) will return just one
> arbitrary value. You are also not relating user_marketing to users
> To keep user_marketing in step try:
> update a
> set a.greeting=b.greeting,
> a.fName=b.fName,
> a.lName=b.lName,
> a.title=b.title,
> a.compName=b.compName,
> a.address=b.address,
> a.city=b.city,
> a.provState=b.provState,
> a.fk_country=b.fk_country,
> a.zip=b.zip,
> a.email=b.email,
> a.phone=b.phone,
> a.phoneext=b.phoneext,
> a.fax=b.fax,
> a.fk_language=b.fk_language,
> a.fk_segment=b.fk_segment,
> a.fk_job=b.fk_job,
> a.emailType=b.emailType,
> a.addedBy=b.addedBy
> FROM dbo.user_marketing a
> JOIN Inserted b ON b.pk_userID = a.fk_userID
> John
>
> "cheezebeetle" wrote:
>

Interesting SQL code editor

Please check out:
Fast SQL Editor
A new and powerfull SQL editor for the professional user.
www.activefrog.com
We are still looking for Beta Testers of this new product. Please visit the
web site and download this amazing tool.
The activeFrog.com team
"activefrog.com" <mail@.activefrog.com> schrieb im Newsbeitrag
news:%23YStFAwIFHA.1096@.tk2msftngp13.phx.gbl...
> Please check out:
> Fast SQL Editor
> A new and powerfull SQL editor for the professional user.
> www.activefrog.com
> We are still looking for Beta Testers of this new product. Please visit
the
> web site and download this amazing tool.
> The activeFrog.com team
Although this question should be forbidden in this group... :-) Do you
plan to go cross platform, i.e. support other SQL dialects as well?
Kind regards
robert
|||One question I have is why develop a new sql editor considering that the
market is flooded anyway?
activefrog.com wrote:
> Please check out:
> Fast SQL Editor
> A new and powerfull SQL editor for the professional user.
> www.activefrog.com
> We are still looking for Beta Testers of this new product. Please visit the
> web site and download this amazing tool.
> The activeFrog.com team
>
>

interesting problem..related to selection criteria..

Have report where the user should be able to enter some selection critera to EXCLUDE from the result set displayed.

I deally Iwould liek to include it in the sql parameters to avoid retunring a larger then necessary result set, but Couldn't I also use it inthe filter criteria?

Has anyone done anything like this.

ie: a drop down box where people could add exclusions.

ie: Apples, Oranges

would be excluded from the result list.

So If I entered Apples, Oranges

the resultset would eclude these from the returned dataset.

I realize using a multi param box is fruitless. WOndering if there is another way to do this

Why wouldn't multi-param work?

Select something from sometable where (@.multiparam is null or fruit not in (@.multiparam))

Does this look ok?

cheers,

Andrew

Interactive Sort

I'm using the interactive sort in a report. Can I pass in the previously
sorted column so that a user could build a sort order of their choice using
two or more columns by appending their prior selections to the sort?Hi Brent,
Thanks for your posting.
From your descriptions, I understood you would like to know whether it is
possible to specify two or more columns to make interactive sorting. If I
have misunderstood your concern, please feel free to point it out.
Based on my knowledge, we are not able to use previously sorted column. To
make priority of sorted columns, you are encouraged to Parameterized
Sorting. Specify two or more parameters and then combile them in a dynamic
query. For more detailed information, see section "Parameterized Sorting"
below
Sorting Data
http://msdn2.microsoft.com/en-us/library/ms157313.aspx
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Integrity Checks job failing

Hi,

SQl Server 7

I have Daily User DB Integrity Checks job running daily
From past 2 days i am getting below error.

[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 35 consistency errors in table 'Prod_Hist' (object ID 2098106515).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 99 consistency errors in database 'Ucatalog'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Ucatalog repair_fast).
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Please suggest..

Thanks in Advance
AdilHi,

SQl Server 7

I have Daily User DB Integrity Checks job running daily
From past 2 days i am getting below error.

[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 35 consistency errors in table 'Prod_Hist' (object ID 2098106515).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 99 consistency errors in database 'Ucatalog'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Ucatalog repair_fast).
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Please suggest..

Thanks in Advance
Adil

1. Contact your DBA with the stated information and ask for his help or
2. Fix the table and database corruption ... either by running checkdb with the repair option stated or restore from a previous backup. Checkdb is telling you that you will probably lose data by allowing it to repair the corruption ... be prepared.

integrity check error and single user mode on Project Server database

When I try to run an integrity check on my project Server
2002 database, I get the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: Check Data and Index
Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
..
You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> state cannot be changed while other users are using the
> database 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: Check Data and Index
> Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>

integrity check error and single user mode on Project Server database

When I try to run an integrity check on my project Server
2002 database, I get the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: Check Data and Index
Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
.You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
--
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> state cannot be changed while other users are using the
> database 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: Check Data and Index
> Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>|||Thanks! I changed my integrity check to not attempt to repair any minor problems, and it doesn't require single user mode to do the check. The integrity check came back fine, so I assume it didn't need to do any repairing anyway.
Regards,
Mike Walraven
"Andrew J. Kelly" wrote:
> You can use sp_who2 to see who is in a particular database but you should
> not be running this in Single User mode anyway. Single User is only
> required to fix issues not to do the initial checks.
> --
> Andrew J. Kelly SQL MVP
>
> "Mike Walraven" <mwalraven@.syncroness.com> wrote in message
> news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> > When I try to run an integrity check on my project Server
> > 2002 database, I get the following error:
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> > state cannot be changed while other users are using the
> > database 'ProjectServer'
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> > DATABASE statement failed.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > sp_dboption command failed.
> > [31] Database ProjectServer: Check Data and Index
> > Linkage...
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> > statement not processed. Database needs to be in single
> > user mode.
> >
> > This same error comes up if I try to manually change the
> > database to single user mode.
> >
> > Is there any way to see which users are using the
> > database, close their sessions and then do the integrity
> > check.
> >
> > This is the only database on my SQL server that I have
> > any issues with.
> >
> > Any help would be appreciated.
> >
> > Regards,
> > Mike Walraven
> > .
> >
> >
>
>|||That is usually the case. If there is an error you can then decide the best
approach to handle it instead of letting the wizard make those decisions.
--
Andrew J. Kelly SQL MVP
"Mike Walraven" <Mike Walraven@.discussions.microsoft.com> wrote in message
news:23F9018D-52F4-4BA4-8A23-05AD4A83D080@.microsoft.com...
> Thanks! I changed my integrity check to not attempt to repair any minor
problems, and it doesn't require single user mode to do the check. The
integrity check came back fine, so I assume it didn't need to do any
repairing anyway.
> Regards,
> Mike Walraven
> "Andrew J. Kelly" wrote:
> > You can use sp_who2 to see who is in a particular database but you
should
> > not be running this in Single User mode anyway. Single User is only
> > required to fix issues not to do the initial checks.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Mike Walraven" <mwalraven@.syncroness.com> wrote in message
> > news:2bb0601c46940$d40d2070$a601280a@.phx.gbl...
> > > When I try to run an integrity check on my project Server
> > > 2002 database, I get the following error:
> > >
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> > > state cannot be changed while other users are using the
> > > database 'ProjectServer'
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> > > DATABASE statement failed.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > > sp_dboption command failed.
> > > [31] Database ProjectServer: Check Data and Index
> > > Linkage...
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> > > statement not processed. Database needs to be in single
> > > user mode.
> > >
> > > This same error comes up if I try to manually change the
> > > database to single user mode.
> > >
> > > Is there any way to see which users are using the
> > > database, close their sessions and then do the integrity
> > > check.
> > >
> > > This is the only database on my SQL server that I have
> > > any issues with.
> > >
> > > Any help would be appreciated.
> > >
> > > Regards,
> > > Mike Walraven
> > > .
> > >
> > >
> >
> >
> >

integrity check error and single user mode on Project Server database

When I try to run an integrity check on my project Server
2002 database, I get the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC SQL Server Driver][SQL Server]Database
state cannot be changed while other users are using the
database 'ProjectServer'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_dboption command failed.
[31] Database ProjectServer: Check Data and Index
Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not processed. Database needs to be in single
user mode.
This same error comes up if I try to manually change the
database to single user mode.
Is there any way to see which users are using the
database, close their sessions and then do the integrity
check.
This is the only database on my SQL server that I have
any issues with.
Any help would be appreciated.
Regards,
Mike Walraven
.You can use sp_who2 to see who is in a particular database but you should
not be running this in Single User mode anyway. Single User is only
required to fix issues not to do the initial checks.
Andrew J. Kelly SQL MVP
"Mike Walraven" <mwalraven@.syncroness.com> wrote in message
news:2bb0601c46940$d40d2070$a601280a@.phx
.gbl...
> When I try to run an integrity check on my project Server
> 2002 database, I get the following error:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Database
> state cannot be changed while other users are using the
> database 'ProjectServer'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER
> DATABASE statement failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> sp_dboption command failed.
> [31] Database ProjectServer: Check Data and Index
> Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair
> statement not processed. Database needs to be in single
> user mode.
> This same error comes up if I try to manually change the
> database to single user mode.
> Is there any way to see which users are using the
> database, close their sessions and then do the integrity
> check.
> This is the only database on my SQL server that I have
> any issues with.
> Any help would be appreciated.
> Regards,
> Mike Walraven
> .
>

Integrated Security...

Does anyone know how to impersonate a user and then use integrated security with SQL server? Every place I've looked so far only shows how to use integrated security through IIS. For some reason, everytime I impersonate a user account, SQL server identifies me as "NT AUTHORITY\ANONYMOUS LOGON" Is this by design? or am I doing something wrong?I don't think that the problem you are seeing is due to SQLServer. This is most likely due to your impersonation configuration in IIS. It looks like the impersonation you are doing is not allowed to go out on network (it is like going LogonUser with LOGON32_LOGON_NETWORK flag), that's why when you connect to SQLserver your identity is not the one you expect to be after impersonation. I recommend reading IIS/ASP.Net documentation on that.|||

Thanks for your quick response... but one small detail. I'm not using IIS or asp.net. I'm using the traditional "logonuser" API method. Thanks anyway. And, yes... I'm using the LOGON32_LOGON_NETWORK flag.

|||

Just to conclude: the problem stems from using the LOGON32_LOGON_NETWORK flag, as Ruslan pointed out.

Thanks
Laurentiu