Hello,
I'm trying to create a trigger that will log to the eventviewer whenever
there is an update, delete, or insert statement made to the employee table.
However, I'm UNABLE to run any INSERT or DELETE statement into the employee
table when I have the severity level from 10-16 from the RAISERROR function.
I can run update just fine with the same severity level. Any idea what could
be causing this?
Thx
CREATE TRIGGER dbo.Audit ON dbo.Employee
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @.@.USERNAME varchar(30)
SET @.@.USERNAME = CURRENT_USER
RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
with log
END
I don't think RAISERROR is the right function for your requirements.
Consider using xp_logevent. More info on this at:
http://msdn.microsoft.com/library/de...aa-sz_6dmc.asp
With RAISERROR, you are unnecessarily raising an error for no apparent
reason. All you wanted was to log that an insert/update/delete occured.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>
|||I would also suggest changing the name of your local variable from
@.@.username ( which implies global, but is not) to @.username...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>
Showing posts with label wrong. Show all posts
Showing posts with label wrong. Show all posts
Sunday, February 12, 2012
anything wrong with this trigger?
Hello,
I'm trying to create a trigger that will log to the eventviewer whenever
there is an update, delete, or insert statement made to the employee table.
However, I'm UNABLE to run any INSERT or DELETE statement into the employee
table when I have the severity level from 10-16 from the RAISERROR function.
I can run update just fine with the same severity level. Any idea what could
be causing this?
Thx
CREATE TRIGGER dbo.Audit ON dbo.Employee
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @.@.USERNAME varchar(30)
SET @.@.USERNAME = CURRENT_USER
RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
with log
ENDI don't think RAISERROR is the right function for your requirements.
Consider using xp_logevent. More info on this at:
http://msdn.microsoft.com/library/d.../>
sz_6dmc.asp
With RAISERROR, you are unnecessarily raising an error for no apparent
reason. All you wanted was to log that an insert/update/delete occured.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>|||I would also suggest changing the name of your local variable from
@.@.username ( which implies global, but is not) to @.username...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>
I'm trying to create a trigger that will log to the eventviewer whenever
there is an update, delete, or insert statement made to the employee table.
However, I'm UNABLE to run any INSERT or DELETE statement into the employee
table when I have the severity level from 10-16 from the RAISERROR function.
I can run update just fine with the same severity level. Any idea what could
be causing this?
Thx
CREATE TRIGGER dbo.Audit ON dbo.Employee
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @.@.USERNAME varchar(30)
SET @.@.USERNAME = CURRENT_USER
RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
with log
ENDI don't think RAISERROR is the right function for your requirements.
Consider using xp_logevent. More info on this at:
http://msdn.microsoft.com/library/d.../>
sz_6dmc.asp
With RAISERROR, you are unnecessarily raising an error for no apparent
reason. All you wanted was to log that an insert/update/delete occured.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>|||I would also suggest changing the name of your local variable from
@.@.username ( which implies global, but is not) to @.username...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>
anything wrong with this trigger?
Hello,
I'm trying to create a trigger that will log to the eventviewer whenever
there is an update, delete, or insert statement made to the employee table.
However, I'm UNABLE to run any INSERT or DELETE statement into the employee
table when I have the severity level from 10-16 from the RAISERROR function.
I can run update just fine with the same severity level. Any idea what could
be causing this?
Thx
CREATE TRIGGER dbo.Audit ON dbo.Employee
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @.@.USERNAME varchar(30)
SET @.@.USERNAME = CURRENT_USER
RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
with log
ENDI don't think RAISERROR is the right function for your requirements.
Consider using xp_logevent. More info on this at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_6dmc.asp
With RAISERROR, you are unnecessarily raising an error for no apparent
reason. All you wanted was to log that an insert/update/delete occured.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>|||I would also suggest changing the name of your local variable from
@.@.username ( which implies global, but is not) to @.username...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>
I'm trying to create a trigger that will log to the eventviewer whenever
there is an update, delete, or insert statement made to the employee table.
However, I'm UNABLE to run any INSERT or DELETE statement into the employee
table when I have the severity level from 10-16 from the RAISERROR function.
I can run update just fine with the same severity level. Any idea what could
be causing this?
Thx
CREATE TRIGGER dbo.Audit ON dbo.Employee
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @.@.USERNAME varchar(30)
SET @.@.USERNAME = CURRENT_USER
RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
with log
ENDI don't think RAISERROR is the right function for your requirements.
Consider using xp_logevent. More info on this at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_6dmc.asp
With RAISERROR, you are unnecessarily raising an error for no apparent
reason. All you wanted was to log that an insert/update/delete occured.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>|||I would also suggest changing the name of your local variable from
@.@.username ( which implies global, but is not) to @.username...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>
Anything wrong with this query?
For a given table, I want to know all the columns that are included in
an index. I have looked on the web and come up with this, which seems
to work, but just wanted some verification. Are there any reasons why
I should be using the metadata functions like OBJECT_NAME?
an index. I have looked on the web and come up with this, which seems
to work, but just wanted some verification. Are there any reasons why
I should be using the metadata functions like OBJECT_NAME?
Thanks
Bruce
SELECT
DISTINCT c.name
from sysusers u,
sysobjects o,
syscolumns c,
sysindexes i,
sysindexkeys k
WHERE o.uid = u.uid
AND u.name = user
AND o.name = 'ing_customer'
AND o.id = i.id
AND i.indid = k.indid
AND OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0
AND 1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics' ) ,
INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,
INDEXPROPERTY( i.id , i.name , 'IsHypothetical' ) )
AND i.indid BETWEEN 1 And 250
AND k.id = o.id
and k.colid = c.colid
and c.id = o.id
ORDER BY c.nameBruce (sandell@.pacbell.net) writes:
> For a given table, I want to know all the columns that are included in
> an index. I have looked on the web and come up with this, which seems
> to work, but just wanted some verification. Are there any reasons why
> I should be using the metadata functions like OBJECT_NAME?
object_name() is just a quick-step to sysobjects.name, which is a
documented column, so which one you use, is no big deal.
On the other hand, the information returned by objectproperty() and
indexproperty() is hidden in undocuemented columns, so in this case,
you should definitely use the metadata functions.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Thursday, February 9, 2012
Anything wrong with the connection?
Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
Ivan
On Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
Ivan
On Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA
Anything wrong with the connection?
Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Use
r
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL serve
r
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Use
r
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL serve
r
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA
Anything wrong with the connection?
Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
--
Rudi Bruchez
MCDBA
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
--
Rudi Bruchez
MCDBA
Subscribe to:
Posts (Atom)