I've installed MSDE w/no problem - but what username & password do I use (well, I know what password since I specified it in the *.ini file before install) - but username?
Cassinni or IIS web server - I can't log on and do anything w/MSDE. Do I have to edit something w/a username, make one up, use one from that's built into windows? I've tried both settings _ windows authentication and SQL Server - but nothing. Any sugge
stions?
The default user ID is "sa". If you set the server to use Windows Authentication, you cannot logon with this user ID - at least via applications. This ID must be given a strong password. You can do that with the osql utility provided with MSDE.
If this is MSDE2000A, read the Jan 2004 version of the Readme2000A.htm that comes with the bundle. Also get a hold of the updates to this file.
I also suggest the SQL Server Books Online help file. This is downloadable at no charge and must be installed.
Mark Ferguson
"steve02a" wrote:
> I've installed MSDE w/no problem - but what username & password do I use (well, I know what password since I specified it in the *.ini file before install) - but username?
> Cassinni or IIS web server - I can't log on and do anything w/MSDE. Do I have to edit something w/a username, make one up, use one from that's built into windows? I've tried both settings _ windows authentication and SQL Server - but nothing. Any sug
gestions?
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Friday, March 30, 2012
Friday, March 9, 2012
Log everything
Hi,
I've been using SQL Server for a while, but I'm fairly new to this area.
I would like to log every error that occurs in every stored procedure that I
call (actually from an ASP page, but I don't think this is relevant). One of
my stored procedures sometimes (as far as I can determine, quite
non-deterministically) fails, but from ASP all the error description I get
is this:
[Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction
completed. Either enlist this session in a new transaction or the NULL
transaction.
My stored proc. calls quite a lot other procedures, so it would be good to
be able to determine where exactly the error occurs during executing. (And I
wouldn't like to put "IF @.@.ERROR <> 0" blocks after every rows in every
stored procedure.)
So the best would be if I could see in a log which stored procedures were
called with which values and which procedure failed in which row. Is it (or
at least part of it) possible?
Thx,
Agoston
Agoston Bejo wrote:
> Hi,
> I've been using SQL Server for a while, but I'm fairly new to this
> area.
> I would like to log every error that occurs in every stored procedure
> that I call (actually from an ASP page, but I don't think this is
> relevant). One of my stored procedures sometimes (as far as I can
> determine, quite non-deterministically) fails, but from ASP all the
> error description I get is this:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction
> completed. Either enlist this session in a new transaction or the NULL
> transaction.
> My stored proc. calls quite a lot other procedures, so it would be
> good to be able to determine where exactly the error occurs during
> executing. (And I wouldn't like to put "IF @.@.ERROR <> 0" blocks after
> every rows in every stored procedure.)
> So the best would be if I could see in a log which stored procedures
> were called with which values and which procedure failed in which
> row. Is it (or at least part of it) possible?
> Thx,
> Agoston
You can use Profiler and the SQL Trace API to watch SQL execution and
see Errors/Exceptions. Tracing may be extensive, so I would recommend
you research creating a server-side trace. You'll have to trap
SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
if you have any SQL running outside a SP. You 'll also have to add
Errors and Exceptions. You're going to collect a lot of data, so if
these errors are intermittent, be prepared for very large trace files
(possibly gigabytes if the database is active). If you can limit the
trace to a specific user/application it might help to eliminate some
data collection.
David Gugick
Imceda Software
www.imceda.com
|||OK, from the trace I can see that probably an error occurs in a trigger. The
trigger does a rollback tran (and with that makes it impossible to put some
debugging info into a table, because that insert statement gets rolled back,
too - even if issued after the rollback trans statement). After that, it
calls RAISERROR with an (quite verbose) error message. I added every event
to the trace but cannot see anything from this RAISERROR statement. It would
be very good to somehow acquire the error message.
Basically, the structure of the trigger is this:
alter trigger tr_ins_upd
on table1
for insert, update
begin
[check for inconsistency]
[if there is any:]
[rollback tran]
[insert debug info into debug table] -- no effect
[raiserror(debug_info)] -- cannot see in SQL Profiler's
trace
[end if]
end
So, how can I
- get to see the error message sent by RAISERROR
- insert some info into a table that doesn't get rolled back by that
'rollback tran' statement
Thx,
Agoston
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#65ljozEFHA.1084@.tk2msftngp13.phx.gbl...
> Agoston Bejo wrote:
> You can use Profiler and the SQL Trace API to watch SQL execution and
> see Errors/Exceptions. Tracing may be extensive, so I would recommend
> you research creating a server-side trace. You'll have to trap
> SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
> if you have any SQL running outside a SP. You 'll also have to add
> Errors and Exceptions. You're going to collect a lot of data, so if
> these errors are intermittent, be prepared for very large trace files
> (possibly gigabytes if the database is active). If you can limit the
> trace to a specific user/application it might help to eliminate some
> data collection.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Hi Agoston,
On Tue, 15 Feb 2005 11:31:25 +0100, Agoston Bejo wrote:
(snip)
>So, how can I
>- get to see the error message sent by RAISERROR
Use the trace you already have to find out what command(s) are sent to SQL
Server from the ASP Page; execute those same commands from a Query
Analyser window.
>- insert some info into a table that doesn't get rolled back by that
>'rollback tran' statement
Statements executed after the ROLLBACK sould not be rolled back, unless
another ROLLBACK is issued later in the process. Use the profile trace to
find out who is issueing this second ROLLBACK.
(I don't know ASP, but I do have the experience that MS Access will
sometimes issue a rollback when unexpected messages are returned - your
RAISERROR would probably trigger MS Access to issue a rollback)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||The only comprehensive way to do this is by injecting error collection code
in your application's data tier. Profiler traces can show you when errors
occur, but the only message info you can get this way is by looking up the
error number in master..sysmessages. This only gives you the error message
template, but not the specifics you're probably after (eg the name of the
table a primary key violation occurred on).
Regards,
Greg Linwood
SQL Server MVP
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:u0nN3l0EFHA.1924@.TK2MSFTNGP14.phx.gbl...
> OK, from the trace I can see that probably an error occurs in a trigger.
> The
> trigger does a rollback tran (and with that makes it impossible to put
> some
> debugging info into a table, because that insert statement gets rolled
> back,
> too - even if issued after the rollback trans statement). After that, it
> calls RAISERROR with an (quite verbose) error message. I added every event
> to the trace but cannot see anything from this RAISERROR statement. It
> would
> be very good to somehow acquire the error message.
> Basically, the structure of the trigger is this:
> alter trigger tr_ins_upd
> on table1
> for insert, update
> begin
> [check for inconsistency]
> [if there is any:]
> [rollback tran]
> [insert debug info into debug table] -- no effect
> [raiserror(debug_info)] -- cannot see in SQL Profiler's
> trace
> [end if]
> end
> So, how can I
> - get to see the error message sent by RAISERROR
> - insert some info into a table that doesn't get rolled back by that
> 'rollback tran' statement
> Thx,
> Agoston
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:#65ljozEFHA.1084@.tk2msftngp13.phx.gbl...
>
I've been using SQL Server for a while, but I'm fairly new to this area.
I would like to log every error that occurs in every stored procedure that I
call (actually from an ASP page, but I don't think this is relevant). One of
my stored procedures sometimes (as far as I can determine, quite
non-deterministically) fails, but from ASP all the error description I get
is this:
[Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction
completed. Either enlist this session in a new transaction or the NULL
transaction.
My stored proc. calls quite a lot other procedures, so it would be good to
be able to determine where exactly the error occurs during executing. (And I
wouldn't like to put "IF @.@.ERROR <> 0" blocks after every rows in every
stored procedure.)
So the best would be if I could see in a log which stored procedures were
called with which values and which procedure failed in which row. Is it (or
at least part of it) possible?
Thx,
Agoston
Agoston Bejo wrote:
> Hi,
> I've been using SQL Server for a while, but I'm fairly new to this
> area.
> I would like to log every error that occurs in every stored procedure
> that I call (actually from an ASP page, but I don't think this is
> relevant). One of my stored procedures sometimes (as far as I can
> determine, quite non-deterministically) fails, but from ASP all the
> error description I get is this:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Distributed transaction
> completed. Either enlist this session in a new transaction or the NULL
> transaction.
> My stored proc. calls quite a lot other procedures, so it would be
> good to be able to determine where exactly the error occurs during
> executing. (And I wouldn't like to put "IF @.@.ERROR <> 0" blocks after
> every rows in every stored procedure.)
> So the best would be if I could see in a log which stored procedures
> were called with which values and which procedure failed in which
> row. Is it (or at least part of it) possible?
> Thx,
> Agoston
You can use Profiler and the SQL Trace API to watch SQL execution and
see Errors/Exceptions. Tracing may be extensive, so I would recommend
you research creating a server-side trace. You'll have to trap
SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
if you have any SQL running outside a SP. You 'll also have to add
Errors and Exceptions. You're going to collect a lot of data, so if
these errors are intermittent, be prepared for very large trace files
(possibly gigabytes if the database is active). If you can limit the
trace to a specific user/application it might help to eliminate some
data collection.
David Gugick
Imceda Software
www.imceda.com
|||OK, from the trace I can see that probably an error occurs in a trigger. The
trigger does a rollback tran (and with that makes it impossible to put some
debugging info into a table, because that insert statement gets rolled back,
too - even if issued after the rollback trans statement). After that, it
calls RAISERROR with an (quite verbose) error message. I added every event
to the trace but cannot see anything from this RAISERROR statement. It would
be very good to somehow acquire the error message.
Basically, the structure of the trigger is this:
alter trigger tr_ins_upd
on table1
for insert, update
begin
[check for inconsistency]
[if there is any:]
[rollback tran]
[insert debug info into debug table] -- no effect
[raiserror(debug_info)] -- cannot see in SQL Profiler's
trace
[end if]
end
So, how can I
- get to see the error message sent by RAISERROR
- insert some info into a table that doesn't get rolled back by that
'rollback tran' statement
Thx,
Agoston
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#65ljozEFHA.1084@.tk2msftngp13.phx.gbl...
> Agoston Bejo wrote:
> You can use Profiler and the SQL Trace API to watch SQL execution and
> see Errors/Exceptions. Tracing may be extensive, so I would recommend
> you research creating a server-side trace. You'll have to trap
> SP:Starting and SP:StmtStarting events and SQL:StmtStarting/RPC:Starting
> if you have any SQL running outside a SP. You 'll also have to add
> Errors and Exceptions. You're going to collect a lot of data, so if
> these errors are intermittent, be prepared for very large trace files
> (possibly gigabytes if the database is active). If you can limit the
> trace to a specific user/application it might help to eliminate some
> data collection.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Hi Agoston,
On Tue, 15 Feb 2005 11:31:25 +0100, Agoston Bejo wrote:
(snip)
>So, how can I
>- get to see the error message sent by RAISERROR
Use the trace you already have to find out what command(s) are sent to SQL
Server from the ASP Page; execute those same commands from a Query
Analyser window.
>- insert some info into a table that doesn't get rolled back by that
>'rollback tran' statement
Statements executed after the ROLLBACK sould not be rolled back, unless
another ROLLBACK is issued later in the process. Use the profile trace to
find out who is issueing this second ROLLBACK.
(I don't know ASP, but I do have the experience that MS Access will
sometimes issue a rollback when unexpected messages are returned - your
RAISERROR would probably trigger MS Access to issue a rollback)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||The only comprehensive way to do this is by injecting error collection code
in your application's data tier. Profiler traces can show you when errors
occur, but the only message info you can get this way is by looking up the
error number in master..sysmessages. This only gives you the error message
template, but not the specifics you're probably after (eg the name of the
table a primary key violation occurred on).
Regards,
Greg Linwood
SQL Server MVP
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:u0nN3l0EFHA.1924@.TK2MSFTNGP14.phx.gbl...
> OK, from the trace I can see that probably an error occurs in a trigger.
> The
> trigger does a rollback tran (and with that makes it impossible to put
> some
> debugging info into a table, because that insert statement gets rolled
> back,
> too - even if issued after the rollback trans statement). After that, it
> calls RAISERROR with an (quite verbose) error message. I added every event
> to the trace but cannot see anything from this RAISERROR statement. It
> would
> be very good to somehow acquire the error message.
> Basically, the structure of the trigger is this:
> alter trigger tr_ins_upd
> on table1
> for insert, update
> begin
> [check for inconsistency]
> [if there is any:]
> [rollback tran]
> [insert debug info into debug table] -- no effect
> [raiserror(debug_info)] -- cannot see in SQL Profiler's
> trace
> [end if]
> end
> So, how can I
> - get to see the error message sent by RAISERROR
> - insert some info into a table that doesn't get rolled back by that
> 'rollback tran' statement
> Thx,
> Agoston
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:#65ljozEFHA.1084@.tk2msftngp13.phx.gbl...
>
LOG Error Mssgs
I've found very little information on the following error mssg's, can someone
point me in the correct direction or offer advice. It appears this may be
related to our backup software - Veritas but I don't think this is the only
issue. Below are 4 of many error mssgs.
Thanks,
Liz
BlkHeader from strip 1 At 409e4db400 ExpectedAt 5e021000 Size 3936 PrevSize
3939
BACKUP failed to complete the command BACKUP log [Genesis] TO
VIRTUAL_DEVICE='VDI_70893368-C194-4AAA-8290-F50940D1C1E4_0',
VIRTUAL_DEVICE='VDI_70893368-C194-4AAA-8290-F50940D1C1E4_1',
VIRTUAL_DEVICE='VDI_70893368-C194-4AAA-8290-F50940D1C1E4_2' with
blocksize=65536, stats=5, maxtransfersize=72
BackupIoRequest::WaitForIoCompletion: write failure on backup device
'VDI_70893368-C194-4AAA-8290-F50940D1C1E4_0'. Operating system error 995(The
I/O operation has been aborted because of either a thread exit or an
application request.).
Internal I/O request 0x421C4048: Op: Write, pBuffer: 0x15970000, Size:
720896, Position: 1093337088, UMS: Internal: 0x20202020, InternalHigh:
0x20202020, Offset: 0x20202020, OffsetHigh: 0x46202020, m_buf: 0x15970000,
m_len: 25857, m_actualBytes: 0, m_errcode: 995, BackupFile:
VDI_70893368-C194-4AAA-8290-F50940D1C1E4_2
BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
'VDI_70893368-C194-4AAA-8290-F50940D1C1E4_0'. Operating system error 995(The
I/O operation has been aborted because of either a thread exit or an
application request.).
Hi
Looks like the backup software aborts the backup. Been having backup
failures lately?
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/
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:95070D4A-81C6-4F84-8E69-5F1F0CD73DF9@.microsoft.com...
> I've found very little information on the following error mssg's, can
someone
> point me in the correct direction or offer advice. It appears this may be
> related to our backup software - Veritas but I don't think this is the
only
> issue. Below are 4 of many error mssgs.
> Thanks,
> Liz
> BlkHeader from strip 1 At 409e4db400 ExpectedAt 5e021000 Size 3936
PrevSize
> 3939
> BACKUP failed to complete the command BACKUP log [Genesis] TO
> VIRTUAL_DEVICE='VDI_70893368-C194-4AAA-8290-F50940D1C1E4_0',
> VIRTUAL_DEVICE='VDI_70893368-C194-4AAA-8290-F50940D1C1E4_1',
> VIRTUAL_DEVICE='VDI_70893368-C194-4AAA-8290-F50940D1C1E4_2' with
> blocksize=65536, stats=5, maxtransfersize=72
> BackupIoRequest::WaitForIoCompletion: write failure on backup device
> 'VDI_70893368-C194-4AAA-8290-F50940D1C1E4_0'. Operating system error
995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
> Internal I/O request 0x421C4048: Op: Write, pBuffer: 0x15970000, Size:
> 720896, Position: 1093337088, UMS: Internal: 0x20202020, InternalHigh:
> 0x20202020, Offset: 0x20202020, OffsetHigh: 0x46202020, m_buf: 0x15970000,
> m_len: 25857, m_actualBytes: 0, m_errcode: 995, BackupFile:
> VDI_70893368-C194-4AAA-8290-F50940D1C1E4_2
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
device
> 'VDI_70893368-C194-4AAA-8290-F50940D1C1E4_0'. Operating system error
995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
>
point me in the correct direction or offer advice. It appears this may be
related to our backup software - Veritas but I don't think this is the only
issue. Below are 4 of many error mssgs.
Thanks,
Liz
BlkHeader from strip 1 At 409e4db400 ExpectedAt 5e021000 Size 3936 PrevSize
3939
BACKUP failed to complete the command BACKUP log [Genesis] TO
VIRTUAL_DEVICE='VDI_70893368-C194-4AAA-8290-F50940D1C1E4_0',
VIRTUAL_DEVICE='VDI_70893368-C194-4AAA-8290-F50940D1C1E4_1',
VIRTUAL_DEVICE='VDI_70893368-C194-4AAA-8290-F50940D1C1E4_2' with
blocksize=65536, stats=5, maxtransfersize=72
BackupIoRequest::WaitForIoCompletion: write failure on backup device
'VDI_70893368-C194-4AAA-8290-F50940D1C1E4_0'. Operating system error 995(The
I/O operation has been aborted because of either a thread exit or an
application request.).
Internal I/O request 0x421C4048: Op: Write, pBuffer: 0x15970000, Size:
720896, Position: 1093337088, UMS: Internal: 0x20202020, InternalHigh:
0x20202020, Offset: 0x20202020, OffsetHigh: 0x46202020, m_buf: 0x15970000,
m_len: 25857, m_actualBytes: 0, m_errcode: 995, BackupFile:
VDI_70893368-C194-4AAA-8290-F50940D1C1E4_2
BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
'VDI_70893368-C194-4AAA-8290-F50940D1C1E4_0'. Operating system error 995(The
I/O operation has been aborted because of either a thread exit or an
application request.).
Hi
Looks like the backup software aborts the backup. Been having backup
failures lately?
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/
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:95070D4A-81C6-4F84-8E69-5F1F0CD73DF9@.microsoft.com...
> I've found very little information on the following error mssg's, can
someone
> point me in the correct direction or offer advice. It appears this may be
> related to our backup software - Veritas but I don't think this is the
only
> issue. Below are 4 of many error mssgs.
> Thanks,
> Liz
> BlkHeader from strip 1 At 409e4db400 ExpectedAt 5e021000 Size 3936
PrevSize
> 3939
> BACKUP failed to complete the command BACKUP log [Genesis] TO
> VIRTUAL_DEVICE='VDI_70893368-C194-4AAA-8290-F50940D1C1E4_0',
> VIRTUAL_DEVICE='VDI_70893368-C194-4AAA-8290-F50940D1C1E4_1',
> VIRTUAL_DEVICE='VDI_70893368-C194-4AAA-8290-F50940D1C1E4_2' with
> blocksize=65536, stats=5, maxtransfersize=72
> BackupIoRequest::WaitForIoCompletion: write failure on backup device
> 'VDI_70893368-C194-4AAA-8290-F50940D1C1E4_0'. Operating system error
995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
> Internal I/O request 0x421C4048: Op: Write, pBuffer: 0x15970000, Size:
> 720896, Position: 1093337088, UMS: Internal: 0x20202020, InternalHigh:
> 0x20202020, Offset: 0x20202020, OffsetHigh: 0x46202020, m_buf: 0x15970000,
> m_len: 25857, m_actualBytes: 0, m_errcode: 995, BackupFile:
> VDI_70893368-C194-4AAA-8290-F50940D1C1E4_2
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
device
> 'VDI_70893368-C194-4AAA-8290-F50940D1C1E4_0'. Operating system error
995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
>
Subscribe to:
Posts (Atom)