Friday, February 24, 2012

DBCC CONCURRENCYVIOLATION

How can I get the information from DBCC CONCURRENCYVIOLATION from a vb program?
cheers
David
hi David,
"DJS" <DJS@.discussions.microsoft.com> ha scritto nel messaggio
news:0C5F3CA5-E946-4579-9AD2-B3228322B1E4@.microsoft.com...
> How can I get the information from DBCC CONCURRENCYVIOLATION from a vb
program?
> cheers
> David
have a look at SQL-DMO COM object...
Dim oSvr As SQLDMO.SQLServer
Set oSvr = New SQLDMO.SQLServer
With oSvr
.AutoReConnect = True
.LoginTimeout = 15
.LoginSecure = True
.Connect "(local)\MSDE2k"
End With
If Not oSvr Is Nothing Then
Dim sCmd As String, sMsg As String
sCmd = "DBCC CONCURRENCYVIOLATION"
Dim oQry As SQLDMO.QueryResults
Set oQry = oSvr.ExecuteWithResultsAndMessages(sCmd, Len(sCmd), sMsg)
Set oQry = Nothing
Debug.Print sMsg
End If
Set oSvr = Nothing
--<--
[Microsoft][ODBC SQL Server Driver][SQL Server]Concurrency violations since
2004-07-15 18:10:54.217
1 2 3 4 5 6 7 8 9 10-100 >100
0 0 0 0 0 0 0 0 0 0 0
[Microsoft][ODBC SQL Server Driver][SQL Server]Concurrency violations will
be written to the SQL Server error log.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If
DBCC printed error messages, contact your system administrator.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Many thanks
Is it also possible to use an adodb command? I can execute the command but cannot get the message returned?
Many thanks David
"Andrea Montanari" wrote:

> hi David,
> "DJS" <DJS@.discussions.microsoft.com> ha scritto nel messaggio
> news:0C5F3CA5-E946-4579-9AD2-B3228322B1E4@.microsoft.com...
> program?
> have a look at SQL-DMO COM object...
> Dim oSvr As SQLDMO.SQLServer
> Set oSvr = New SQLDMO.SQLServer
> With oSvr
> .AutoReConnect = True
> .LoginTimeout = 15
> .LoginSecure = True
> .Connect "(local)\MSDE2k"
> End With
> If Not oSvr Is Nothing Then
> Dim sCmd As String, sMsg As String
> sCmd = "DBCC CONCURRENCYVIOLATION"
> Dim oQry As SQLDMO.QueryResults
> Set oQry = oSvr.ExecuteWithResultsAndMessages(sCmd, Len(sCmd), sMsg)
> Set oQry = Nothing
>
> Debug.Print sMsg
> End If
> Set oSvr = Nothing
> --<--
> [Microsoft][ODBC SQL Server Driver][SQL Server]Concurrency violations since
> 2004-07-15 18:10:54.217
> 1 2 3 4 5 6 7 8 9 10-100 >100
> 0 0 0 0 0 0 0 0 0 0 0
> [Microsoft][ODBC SQL Server Driver][SQL Server]Concurrency violations will
> be written to the SQL Server error log.
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If
> DBCC printed error messages, contact your system administrator.
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Many thanks
Is it also possible to use an adodb command rather than SQL-DMO COM and get the message returned?
Thanks
David
"Andrea Montanari" wrote:

> hi David,
> "DJS" <DJS@.discussions.microsoft.com> ha scritto nel messaggio
> news:0C5F3CA5-E946-4579-9AD2-B3228322B1E4@.microsoft.com...
> program?
> have a look at SQL-DMO COM object...
> Dim oSvr As SQLDMO.SQLServer
> Set oSvr = New SQLDMO.SQLServer
> With oSvr
> .AutoReConnect = True
> .LoginTimeout = 15
> .LoginSecure = True
> .Connect "(local)\MSDE2k"
> End With
> If Not oSvr Is Nothing Then
> Dim sCmd As String, sMsg As String
> sCmd = "DBCC CONCURRENCYVIOLATION"
> Dim oQry As SQLDMO.QueryResults
> Set oQry = oSvr.ExecuteWithResultsAndMessages(sCmd, Len(sCmd), sMsg)
> Set oQry = Nothing
>
> Debug.Print sMsg
> End If
> Set oSvr = Nothing
> --<--
> [Microsoft][ODBC SQL Server Driver][SQL Server]Concurrency violations since
> 2004-07-15 18:10:54.217
> 1 2 3 4 5 6 7 8 9 10-100 >100
> 0 0 0 0 0 0 0 0 0 0 0
> [Microsoft][ODBC SQL Server Driver][SQL Server]Concurrency violations will
> be written to the SQL Server error log.
> [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If
> DBCC printed error messages, contact your system administrator.
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi David,
"DJS" <DJS@.discussions.microsoft.com> ha scritto nel messaggio
news:4801E7F8-E8BF-4BF5-BC8F-64A94E6CD061@.microsoft.com...
> Many thanks
> Is it also possible to use an adodb command? I can execute the command but
cannot
>get the message returned?
> Many thanks David
messages are usually returned by ADO in the errors collection...
<-->
Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection
With oConn
.ConnectionString = "Provider=sqloledb;Data
Source=(local)\MSDE2k;Initial Catalog=master;Integrated Security=SSPI"
.Open
End With
If Not oConn Is Nothing Then
If oConn.State = adStateOpen Then
Dim oRS As ADODB.Recordset
Set oRS = New ADODB.Recordset
Dim oCmd As ADODB.Command
Set oCmd = New ADODB.Command
With oCmd
.ActiveConnection = oConn
.CommandText = "DBCC CONCURRENCYVIOLATION"
.CommandType = adCmdText
.Execute
End With
Set oCmd = Nothing
Dim oErr As ADODB.Error
For Each oErr In oConn.Errors
Debug.Print oErr.Description
Next
End If
End If
Set oConn = Nothing
--<---
Concurrency violations since 2004-07-16 02:42:41.387
1 2 3 4 5 6 7 8 9 10-100 >100
0 0 0 0 0 0 0 0 0 0 0
Concurrency violations will be written to the SQL Server error log.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||....
or just
Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection
With oConn
.ConnectionString = "Provider=sqloledb;Data
Source=(local)\MSDE2k;Initial Catalog=master;Integrated Security=SSPI"
.Open
End With
If Not oConn Is Nothing Then
If oConn.State = adStateOpen Then
oConn.Execute "DBCC CONCURRENCYVIOLATION"
Dim oErr As ADODB.Error
For Each oErr In oConn.Errors
Debug.Print oErr.Description
Next
End If
End If
Set oConn = Nothing
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Many thanks - I will try this
In general is it better to use the dmo object for maintenance tasks or use system store procedures?
Cheers
David
"Andrea Montanari" wrote:

> ....
> or just
> Dim oConn As ADODB.Connection
> Set oConn = New ADODB.Connection
> With oConn
> .ConnectionString = "Provider=sqloledb;Data
> Source=(local)\MSDE2k;Initial Catalog=master;Integrated Security=SSPI"
> .Open
> End With
> If Not oConn Is Nothing Then
> If oConn.State = adStateOpen Then
> oConn.Execute "DBCC CONCURRENCYVIOLATION"
> Dim oErr As ADODB.Error
> For Each oErr In oConn.Errors
> Debug.Print oErr.Description
> Next
> End If
> End If
> Set oConn = Nothing
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi David,
"DJS" <DJS@.discussions.microsoft.com> ha scritto nel messaggio
news:1304D01B-25CC-4836-A02B-77E9FB739066@.microsoft.com...
> Many thanks - I will try this
> In general is it better to use the dmo object for maintenance tasks or use
system store procedures?
> Cheers
> David
I think it really depends if you want to distribute it as well... MDAC and
ADO are quite part of the OS, nowdays, but SQL-DMO is not, and for MSDE
related installations you have to provide it yourself to the client
computers (for the server, SQL-DMO is installed as part of MSDE
installation)
you can do quite all management task via system sp, but SQL-DMO provides a
usefull object model..
I personally am used to distribute it, as my own management companion tool
requires it...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi
This worked fine - I have moved to vb.net and cannor get ado.net to do the
same thing - the datareader does not return any output.
Any help appreciated
cheers
"Andrea Montanari" wrote:

> ....
> or just
> Dim oConn As ADODB.Connection
> Set oConn = New ADODB.Connection
> With oConn
> .ConnectionString = "Provider=sqloledb;Data
> Source=(local)\MSDE2k;Initial Catalog=master;Integrated Security=SSPI"
> .Open
> End With
> If Not oConn Is Nothing Then
> If oConn.State = adStateOpen Then
> oConn.Execute "DBCC CONCURRENCYVIOLATION"
> Dim oErr As ADODB.Error
> For Each oErr In oConn.Errors
> Debug.Print oErr.Description
> Next
> End If
> End If
> Set oConn = Nothing
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
"DJS" <DJS@.discussions.microsoft.com> ha scritto nel messaggio
news:C754928F-D632-42E0-90E6-9C2C81CB3E0C@.microsoft.com
> Hi
> This worked fine - I have moved to vb.net and cannor get ado.net to
> do the same thing - the datareader does not return any output.
> Any help appreciated
> cheers
you have to trap the Connection.InfoMessage event to get that result...
so just add an eventhandler, get your result and remove it...
something like
Sub Main()
Dim Conn As New SqlClient.SqlConnection("Initial Catalog=Master;Data
Source=(local);Integrated Security=SSPI;")
Conn.Open()
Dim Command As New SqlCommand("DBCC CONCURRENCYVIOLATION", Conn)
Command.CommandType = CommandType.Text
AddHandler Conn.InfoMessage, AddressOf cn_InfoMessage
Command.ExecuteNonQuery()
RemoveHandler Conn.InfoMessage, AddressOf cn_InfoMessage
Command.Dispose()
Conn.Dispose()
End Sub
Private Sub cn_InfoMessage(ByVal sender As Object, ByVal e As
SqlClient.SqlInfoMessageEventArgs)
Debug.WriteLine("Messages")
Debug.WriteLine(e.Message)
End Sub
--<--
Messages
Concurrency violations since 2004-10-08 19:33:13.710
1 2 3 4 5 6 7 8 9 10-100 >100
0 0 0 0 0 0 0 0 0 0 0
Concurrency violations will be written to the SQL Server error log.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

No comments:

Post a Comment