blocks hangup database when inserting in 1 specific table

Hi,

We use a database with about 40 related tables. Some tables contain as
much as 30.000 records. We use Access97 as an interface to the
database. Now recently we have the problem that when we want to insert
a row in one specific table (alwasy the same) the database makes
blocks.

Details:
- about 10% of the data was inserted using copying from Excel, before
this action there was no problem, though there is no evidence that
this causes the problem.
- inserting rows via the Query Analyzer works fine, via Access causes
trouble.
- the tempdb lofile has grown to 48Mb.

Has anyone ideas about what is going on and what I can do to solve the
problem?

TAV,
Jan WillemsJan Willems (jwillems@.xs4all.nl) writes:
> We use a database with about 40 related tables. Some tables contain as
> much as 30.000 records. We use Access97 as an interface to the
> database. Now recently we have the problem that when we want to insert
> a row in one specific table (alwasy the same) the database makes
> blocks.

"Makes blocks"? You mean that the INSERT operation is blocked, and you
have to cancel the operation to continue?

When the situation occurs, use sp_who from Query Analyzer, and see if
any process has a non-zero value in the Blk column. In such case, the
process listed in Blk, blocks the spid of that row.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Blocking a specific host

here's one... we have a rogue system on our network. Every 8months or so,
someone flips the "on" switch to the computer and brings it up. Aparently, it
has an application that uses a generic login (used by other applications) to
access SQL Server. The problem is, what ever file that once was there is no
longer there, so when the startup routine trys connecting to the db and
application, it gets connected to the db, but hangs trying to get connected
to the app. Our network team has no idea where the offending system is, but
obviously it's booted up every once in a blue moon.
My question is, can one block access from a Host to a SQL Server box? If
not, does anyone have ideas on how to capture the ip address or something
that could identify this rogue system by means other than User (remember,
it's a generic userid) or Host?
Thanks a ton for any idea presented!!
Andy wrote:
> here's one... we have a rogue system on our network. Every 8months or
> so, someone flips the "on" switch to the computer and brings it up.
> Aparently, it has an application that uses a generic login (used by
> other applications) to access SQL Server. The problem is, what ever
> file that once was there is no longer there, so when the startup
> routine trys connecting to the db and application, it gets connected
> to the db, but hangs trying to get connected to the app. Our network
> team has no idea where the offending system is, but obviously it's
> booted up every once in a blue moon.
> My question is, can one block access from a Host to a SQL Server box?
> If not, does anyone have ideas on how to capture the ip address or
> something that could identify this rogue system by means other than
> User (remember, it's a generic userid) or Host?
> Thanks a ton for any idea presented!!
You can get the MAC address and Host Name from the sysprocesses table in
the master database once the rogue system in connected. Your network
admin should be able to trace that back to the PC that's causing the
problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Blocking a specific host

here's one... we have a rogue system on our network. Every 8months or so,
someone flips the "on" switch to the computer and brings it up. Aparently, it
has an application that uses a generic login (used by other applications) to
access SQL Server. The problem is, what ever file that once was there is no
longer there, so when the startup routine trys connecting to the db and
application, it gets connected to the db, but hangs trying to get connected
to the app. Our network team has no idea where the offending system is, but
obviously it's booted up every once in a blue moon.
My question is, can one block access from a Host to a SQL Server box? If
not, does anyone have ideas on how to capture the ip address or something
that could identify this rogue system by means other than User (remember,
it's a generic userid) or Host?
Thanks a ton for any idea presented!!Andy wrote:
> here's one... we have a rogue system on our network. Every 8months or
> so, someone flips the "on" switch to the computer and brings it up.
> Aparently, it has an application that uses a generic login (used by
> other applications) to access SQL Server. The problem is, what ever
> file that once was there is no longer there, so when the startup
> routine trys connecting to the db and application, it gets connected
> to the db, but hangs trying to get connected to the app. Our network
> team has no idea where the offending system is, but obviously it's
> booted up every once in a blue moon.
> My question is, can one block access from a Host to a SQL Server box?
> If not, does anyone have ideas on how to capture the ip address or
> something that could identify this rogue system by means other than
> User (remember, it's a generic userid) or Host?
> Thanks a ton for any idea presented!!
You can get the MAC address and Host Name from the sysprocesses table in
the master database once the rogue system in connected. Your network
admin should be able to trace that back to the PC that's causing the
problem.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Blocking a specific host

here's one... we have a rogue system on our network. Every 8months or so,
someone flips the "on" switch to the computer and brings it up. Aparently, i
t
has an application that uses a generic login (used by other applications) to
access SQL Server. The problem is, what ever file that once was there is no
longer there, so when the startup routine trys connecting to the db and
application, it gets connected to the db, but hangs trying to get connected
to the app. Our network team has no idea where the offending system is, but
obviously it's booted up every once in a blue moon.
My question is, can one block access from a Host to a SQL Server box? If
not, does anyone have ideas on how to capture the ip address or something
that could identify this rogue system by means other than User (remember,
it's a generic userid) or Host?
Thanks a ton for any idea presented!!Andy wrote:
> here's one... we have a rogue system on our network. Every 8months or
> so, someone flips the "on" switch to the computer and brings it up.
> Aparently, it has an application that uses a generic login (used by
> other applications) to access SQL Server. The problem is, what ever
> file that once was there is no longer there, so when the startup
> routine trys connecting to the db and application, it gets connected
> to the db, but hangs trying to get connected to the app. Our network
> team has no idea where the offending system is, but obviously it's
> booted up every once in a blue moon.
> My question is, can one block access from a Host to a SQL Server box?
> If not, does anyone have ideas on how to capture the ip address or
> something that could identify this rogue system by means other than
> User (remember, it's a generic userid) or Host?
> Thanks a ton for any idea presented!!
You can get the MAC address and Host Name from the sysprocesses table in
the master database once the rogue system in connected. Your network
admin should be able to trace that back to the PC that's causing the
problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Bit more specific, Can one pass an NVARCHAR to IF UPDATED()?

Question is pretty much in the thread title.

I'm still plunking away on a history trigger that requires the evaluation of whether or not each column has been updated. If it has,t hen I'll be tossing a record into a history table.

Basically I need something to function along the same logic as this:

IF UPDATED(@.s)
BEGIN
--some code
END

Any thoughts you folks may have are greatly appreciated.About the closest I can get is the Columns_Updated() and maybe some fancy dancing with syscolumns.

-PatP|||About the closest I can get is the Columns_Updated() and maybe some fancy dancing with syscolumns.

-PatP

At least it's something to go on. I'll take a look at it tomorrow.

Much obliged good sir.