Exec SQL Task: Capture return code of stored proc not working

I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog

EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0

PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure

CREATE PROCEDURE CheckCatLog
@.OutSuccess INT
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @.RowCountCAT INT
DECLARE @.RowCountLOG INT

these totals should match
SELECT @.RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @.RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @.RowCountCAT
--PRINT @.RowCountLOG
BEGIN
IF @.RowCountCAT <> @.RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @.OutSuccess = 1
END
GO

Thanks in advance

Dave

Set ResultSet=None.

If OutSuccess is an OUTPUT parameter, you have to modify the second line in SP to "@.OutSuccess INT OUTPUT". If it is not an OUTPUT parameter, you have to modify the mapping direction in your task.

Also, if you are returning a value from the SP, you have to add a parameter (mapping direction: ReturnValue) to get the return value.
|||

Thanks for quick reply--opps I have fixed the SPROC see bold and set the result set in SSIS to none and still get the error--what I want to do is return the output from the Exec SQL task into a variable giBatchID Int32 and then connect to a script task and if value of giBatchID = 1 then fail the task and connect to an SMTP task to send an alert to the customer. The showstopper is the Execute SQL task with OLEDB connection to SQL server 2005 table--it simply does not work, there is no way that it will pick up a single row output from a mple OLEDB connection: Any ideas guys?

CREATE PROCEDURE CheckCatLog
@.OutSuccess INT OUTPUT
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @.RowCountCAT INT
DECLARE @.RowCountLOG INT

these totals should match
SELECT @.RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @.RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @.RowCountCAT
--PRINT @.RowCountLOG
BEGIN
IF @.RowCountCAT <> @.RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @.OutSuccess = 1

RETURN @.OutSuccess
END
GO

|||You can try Parameter Mapping, set direction to Output and map the parameter name 0 to the user variables.

Also set a default to @.OutSuccess to 0 instead of null
|||I do not understand why you would want to make OutSuccess an OUTPUT parameter and the return value.

My suggestion would be to make it just an OUTPUT paramter and not the return value. So, leave the second line in SP as it is, but comment out RETURN part. Set ResultSet=None in your task. Set SQLStatement="CheckCatLog ? OUTPUT". Then add an output parameter (variablename=giBatchID, Direction=Output, Type=Int32 and Parameter Name=0).

EXEC @SQLString with Output Results

Hello, I have been working around this issue, but couldn't yet find any solution.
I have a stored procedure that calls a method to do a certain repetitive work.
In this function, I have a dynamic query, which means, that I am concatinating commands to the query depending on the input of the function.
for example, there is an input for a function called "Id"
Inside the function,
if Id = 111
I need to add " and ID <> 1" and if Id has another value I need to add " and ID = c.ID" something like that.
Now, inside the function, I need to return a value by executing the above @.SQLString as follows:
EXEC @.SQLString
When I need is something like
EXEC @.SQLString, @.Total Output
Return (@.Total)
Are there any ideas ?
regardsProblem Solved
regardssql

EXCHANGETYPE not working

I added the option -exchangetype 2 to the agent and any changes i make at the
subscriber are being pushed back to the publisher...
I am terribly confused...
After more research i realised that i was changing the snap shot agent and
not the merge agent.
Secondly i also figured out when i subscribe via SQL Server Mobile, the
Merge Agent is not editable meaning on SQL2k when i right click on the Merge
Agent and ->Agent Properties it asks for a user name and password and tries
to connect to a database that has the name of my subscription.
So what i am wondering is, can i configure a merge agent that is
created/iniated by SQL Server Mobile?
|||Not really, you need to code your merge replication sync in vbscript, csharp
or vb.net. Use the ExchangeType Property setting for this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ming Yeung" <MingYeung@.discussions.microsoft.com> wrote in message
news:29250BBC-E9F1-4478-B5B3-45F31036FE30@.microsoft.com...
>I am terribly confused...
> After more research i realised that i was changing the snap shot agent and
> not the merge agent.
> Secondly i also figured out when i subscribe via SQL Server Mobile, the
> Merge Agent is not editable meaning on SQL2k when i right click on the
> Merge
> Agent and ->Agent Properties it asks for a user name and password and
> tries
> to connect to a database that has the name of my subscription.
> So what i am wondering is, can i configure a merge agent that is
> created/iniated by SQL Server Mobile?
|||Thanks for the response...
Do you have a link on how to do the above with C#?
Regards and Thanks In Advance
"Hilary Cotter" wrote:

> Not really, you need to code your merge replication sync in vbscript, csharp
> or vb.net. Use the ExchangeType Property setting for this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ming Yeung" <MingYeung@.discussions.microsoft.com> wrote in message
> news:29250BBC-E9F1-4478-B5B3-45F31036FE30@.microsoft.com...
>
>
|||try this:
http://support.microsoft.com/kb/319646
Note that you would need to specify a value for
SQLMERGXLib.ISQLMerge.DynamicSnapshotLocation.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ming Yeung" <MingYeung@.discussions.microsoft.com> wrote in message
news:FDEC4E9E-BA7D-481F-B95D-347987439BCD@.microsoft.com...[vbcol=seagreen]
> Thanks for the response...
> Do you have a link on how to do the above with C#?
> Regards and Thanks In Advance
> "Hilary Cotter" wrote:

EXCEPTION_ACCESS_VIOLATION error

hi,
I am working on sql 2000 server. Jobs kept randomly
failing on one of my servers with the following error, and
the next runs will be successful randomly, any thought on
this?
'SqlDumpExceptionHandler: Process 72 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process. [SQLSTATE HY000] (Error 0).'
many thanks,
JJ
In this case, you may have to call SQL Server support. SQL Server
has choked on something.
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
> hi,
> I am working on sql 2000 server. Jobs kept randomly
> failing on one of my servers with the following error, and
> the next runs will be successful randomly, any thought on
> this?
> 'SqlDumpExceptionHandler: Process 72 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process. [SQLSTATE HY000] (Error 0).'
> many thanks,
> JJ
|||There should be a sqldumpxxx.txt file in the LOG folder under the MSSQL
folder. This file should show you which processes were running and which
function call choked. You might be cleaver and figure something out;
however, I'm with Armando and agree you should put in a call to MS PSS.
Sincerely,
Anthony Thomas
"Armando Prato" wrote:

> In this case, you may have to call SQL Server support. SQL Server
> has choked on something.
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
>
>
|||I am also facing the same problem.
I am able to insert a row using raw jdbc connection.But i am getting above problem when i tried to insert a row using datasource (was5)
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

EXCEPTION_ACCESS_VIOLATION error

hi,
I am working on sql 2000 server. Jobs kept randomly
failing on one of my servers with the following error, and
the next runs will be successful randomly, any thought on
this?
'SqlDumpExceptionHandler: Process 72 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process. [SQLSTATE HY000] (Error 0).'
many thanks,
JJ
In this case, you may have to call SQL Server support. SQL Server
has choked on something.
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
> hi,
> I am working on sql 2000 server. Jobs kept randomly
> failing on one of my servers with the following error, and
> the next runs will be successful randomly, any thought on
> this?
> 'SqlDumpExceptionHandler: Process 72 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process. [SQLSTATE HY000] (Error 0).'
> many thanks,
> JJ
|||There should be a sqldumpxxx.txt file in the LOG folder under the MSSQL
folder. This file should show you which processes were running and which
function call choked. You might be cleaver and figure something out;
however, I'm with Armando and agree you should put in a call to MS PSS.
Sincerely,
Anthony Thomas
"Armando Prato" wrote:

> In this case, you may have to call SQL Server support. SQL Server
> has choked on something.
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
>
>

EXCEPTION_ACCESS_VIOLATION error

hi,
I am working on sql 2000 server. Jobs kept randomly
failing on one of my servers with the following error, and
the next runs will be successful randomly, any thought on
this?
'SqlDumpExceptionHandler: Process 72 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process. [SQLSTATE HY000] (Error 0).'
many thanks,
JJIn this case, you may have to call SQL Server support. SQL Server
has choked on something.
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
> hi,
> I am working on sql 2000 server. Jobs kept randomly
> failing on one of my servers with the following error, and
> the next runs will be successful randomly, any thought on
> this?
> 'SqlDumpExceptionHandler: Process 72 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process. [SQLSTATE HY000] (Error 0).'
> many thanks,
> JJ|||There should be a sqldumpxxx.txt file in the LOG folder under the MSSQL
folder. This file should show you which processes were running and which
function call choked. You might be cleaver and figure something out;
however, I'm with Armando and agree you should put in a call to MS PSS.
Sincerely,
Anthony Thomas
"Armando Prato" wrote:
> In this case, you may have to call SQL Server support. SQL Server
> has choked on something.
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
> > hi,
> >
> > I am working on sql 2000 server. Jobs kept randomly
> > failing on one of my servers with the following error, and
> > the next runs will be successful randomly, any thought on
> > this?
> >
> > 'SqlDumpExceptionHandler: Process 72 generated fatal
> > exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> > is terminating this process. [SQLSTATE HY000] (Error 0).'
> >
> > many thanks,
> > JJ
>
>

EXCEPTION_ACCESS_VIOLATION Error

Hi all,
One of our clients is using SQL7/SP4 and their database is working correctly
with our application and not producing any errors. To help locate an
intermittent problem with our application I did a backup of the database and
returned it to our office.
The backup went fine and was verified and the restore to our SQL/SP4 Server
in the office went without any errors.
However if you try and look at any Tables, SP's or Views in EM you receive
the following error msg:
SqlDumpExceptionHandler: Process 9 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
The error log shows:
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 03/24/04 08:02:31 spid 9
*
* Exception Address = 0051ACE7 (CObjectScan::LParentid + 10)
* Exception Code = c0000005 E
* Access Violation occurred reading address 00000000
* Input Buffer 1428 bytes -
* s e l e c t s 1 = o . n a m e , s 2 = u s e r _ n a m e ( o
* . u i d ) , o . c r d a t e , o . i d , N ' S y s t e m O b j ' =
* ( c a s e w h e n ( O B J E C T P R O P E R T Y ( o . i d , N ' I
* s M S S h i p p e d ' ) = 1 ) t h e n 1 e l s e O B J E C T P R
* O P E R T Y ( o . i d , N ' I s S y s t e m T a b l e ' ) e n d ) ,
* o . c a t e g o r y , 0 , O b j e c t P r o p e r t
* y ( o . i d , N ' T a b l e H a s A c t i v e F u l l t e x t I n d e
* x ' ) , O b j e c t P r o p e r t y ( o . i d , N ' T a b l e F u l
* l t e x t C a t a l o g I d ' ) , N ' F a k e T a b l e ' = ( c a
* s e w h e n ( O B J E C T P R O P E R T Y ( o . i d , N ' t a b l
* e i s f a k e ' ) = 1 ) t h e n 1 e l s e 0 e n d ) ,
* ( c a s e w h e n ( O B J E C T P R O P E R T Y ( o . i d ,
* N ' I s Q u o t e d I d e n t O n ' ) = 1 ) t h e n 1 e l s e
* 0 e n d ) , ( c a s e w h e n ( O B J E C T P R O P E R T Y ( o
* . i d , N ' I s A n s i N u l l s O n ' ) = 1 ) t h e n 1 e l s
* e 0 e n d ) f r o m d b o . s y s o b j e c t s
* o , d b o . s y s i n d e x e s i w h e r e O B J E C T P R O P
* E R T Y ( o . i d , N ' I s T a b l e ' ) = 1 a n d i . i d
* = o . i d a n d i . i n d i d < 2 a n d o . n a m e n o
* t l i k e N ' # % ' o r d e r b y s 1 , s 2
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 008d2fff 004d3000
* ntdll 77f60000 77fbefff 0005f000
* KERNEL32 77f00000 77f5efff 0005f000
* ADVAPI32 77dc0000 77dfefff 0003f000
* USER32 77e70000 77ec1fff 00052000
* GDI32 77ed0000 77efbfff 0002c000
* RPCRT4 77e10000 77e66fff 00057000
* ole32 77b20000 77bd0fff 000b1000
* OLEAUT32 65340000 653dafff 0009b000
* VERSION 77a90000 77a9afff 0000b000
* SHELL32 77c40000 77d7afff 0013b000
* COMCTL32 71710000 71793fff 00084000
* LZ32 779c0000 779c7fff 00008000
* opends60 41060000 41085fff 00026000
* ums 41090000 4109cfff 0000d000
* MSVCRT 78000000 78043fff 00044000
* sqlsort 04000000 0408efff 0008f000
* MSVCIRT 780a0000 780b1fff 00012000
* sqlevn70 410a0000 410a6fff 00007000
* rpcltc1 77bf0000 77bf6fff 00007000
* COMNEVNT 410b0000 410fefff 0004f000
* ODBC32 012b0000 012e4fff 00035000
* comdlg32 77d80000 77db1fff 00032000
* SQLWOA 41100000 4110bfff 0000c000
* odbcint 013f0000 01405fff 00016000
* NDDEAPI 75a80000 75a87fff 00008000
* WINSPOOL 77c00000 77c17fff 00018000
* SQLTrace 41130000 4117dfff 0004e000
* NETAPI32 4ca00000 4ca40fff 00041000
* NETRAP 77840000 77848fff 00009000
* SAMLIB 777e0000 777ecfff 0000d000
* WSOCK32 776d0000 776d7fff 00008000
* WS2_32 776b0000 776c3fff 00014000
* WS2HELP 776a0000 776a6fff 00007000
* WLDAP32 77950000 77978fff 00029000
* SSNMPN70 41190000 41195fff 00006000
* SSMSSO70 411a0000 411aafff 0000b000
* SSMSRP70 411b0000 411b7fff 00008000
* ENUdtc 69140000 69156fff 00017000
* XOLEHLP 69360000 69368fff 00009000
* MTXCLU 69790000 6979cfff 0000d000
* ADME 69120000 69132fff 00013000
* DTCUtil 69000000 69009fff 0000a000
* DTCTRACE 68ff0000 68ff6fff 00007000
* CLUSAPI 7f230000 7f23cfff 0000d000
* RESUTILS 7f250000 7f259fff 0000a000
* msafd 77660000 7766efff 0000f000
* wshtcpip 77690000 77698fff 00009000
* rpclts1 77e00000 77e06fff 00007000
* RpcLtScm 74fa0000 74faafff 0000b000
* MSWSOCK 77670000 77686fff 00017000
* rnr20 74ff0000 74ffdfff 0000e000
* RpcLtCcm 74fc0000 74fcefff 0000f000
* security 76e70000 76e81fff 00012000
* msapsspc 24900000 24910fff 00011000
* MSVCRT40 779d0000 779e4fff 00015000
* schannel 77400000 7741dfff 0001e000
* MSOSS 5e380000 5e3a4fff 00025000
* CRYPT32 5cf00000 5cf74fff 00075000
* MSASN1 24930000 2493ffff 00010000
* digest 24940000 2494ffff 00010000
* msnsspc 716d0000 716eefff 0001f000
* SQLRGSTR 411c0000 411c4fff 00005000
* sqlmap70 415b0000 415c5fff 00016000
* ntwdblib 73320000 73363fff 00044000
* MAPI32 6fa90000 6fb6afff 000db000
* MPR 77720000 77730fff 00011000
* contab32 6eaf0000 6eb08fff 00019000
* EMSABP32 626f0000 62713fff 00024000
* EMSUI32 625d0000 625f0fff 00021000
* WMSUI32 6def0000 6e017fff 00128000
* GAPI32 6fbf0000 6fc07fff 00018000
* xpsqlbot 41820000 41825fff 00006000
* sqlboot 417f0000 417f7fff 00008000
* xpstar 411d0000 41200fff 00031000
* SQLWID 412f0000 412f5fff 00006000
* SQLSVC 415e0000 415f8fff 00019000
* odbcbcp 25510000 25516fff 00007000
* SQLRESLD 41320000 41325fff 00006000
* W95SCM 41210000 41217fff 00008000
* SQLSVC 42480000 42485fff 00006000
* sqlimage 72a00000 72a2cfff 0002d000
*
* Edi: 256CF9D4: 00100009 005f92d5 256cfa44 00000000 0078eecb 256cfb50
* Esi: 256CF944: 00000000 00000000 00000000 00000000 0000002a 1520fbc8
* Eax: 00000000:
* Ebx: 00000007:
* Ecx: 00000009:
* Edx: 0000002C:
* Eip: 0051ACE7: abe85030 c0830081 e128a150 57fc458d 5651ec8b 55c3008b
* Ebp: 256CF9E0: 00000007 1548d1f8 0bdc9e2e 00100009 005f92d5 256cfa44
* SegCs: 0000001B:
* EFlags: 00010246: 0020006d 00610072 0067006f 00720050 005c003a 0043003b
* Esp: 256CF8EC: 00000000 00000008 005f9290 256cfa10 256cfa10 005ecc26
* SegSs: 00000023:
****************************************
************************************
***
The Short Stack Dump is empty.
I've tried restoring the DB to several SQL 7 Servers but with the same
result. I have taken other backups from the clients SQL 7 Server but all
the restores to our Servers give the same result. Restores to our clients
PC work correctly.
DBCC CheckDB at our office produces the following:
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:325) with latch type SH. sysobjects failed.
Server: Msg 8944, Level 16, State 1, Line 1
Table Corrupt: Object ID 1, index ID 0, page (1:325), row 62. Test (nVarCols
&& (hdr->r_tagA & VARIABLE_COLUMNS)) failed. Values are 0 and 32.
DBCC results for 'JagTrain'.
CHECKDB found 0 allocation errors and 1 consistency errors in table
'sysobjects' (object ID 1).
CHECKDB found 0 allocation errors and 1 consistency errors in database
'JagTrain'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (JagTrain ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The commands:
exec sp_dboption 'JagTrain', 'single user', 'on'
GO
DBCC CHECKDB ('JagTrain', repair_allow_data_loss)WITH NO_INFOMSGS
Produces:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The database is now single user.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:325) with latch type SH. sysobjects failed.
Server: Msg 8944, Level 16, State 1, Line 1
Table Corrupt: Object ID 1, index ID 0, page (1:325), row 62. Test (nVarCols
&& (hdr->r_tagA & VARIABLE_COLUMNS)) failed. Values are 0 and 32.
CHECKDB found 0 allocation errors and 1 consistency errors in table
'sysobjects' (object ID 1).
CHECKDB found 0 allocation errors and 1 consistency errors in database
'JagTrain'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (JagTrain repair_allow_data_loss).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
If anyone has any ideas as to what might be causing this problem it would be
appreciated.
Thanks in advance,
Greg
--
greghines@.bigfoot.com.NOSPAM
Remove NOSPAM when replyingHi Greg,
Judging from your DBCC CHECKDB output, it appears that your sysobjects
table has been corrupted. Unfortunately this is probably one of the most
important system tables in the database as it contains information for each
object in the database. The Repair option most likely will not fix the
corruption.
If the backup exhibits same problem, that means the database has been
backed up with corruption. You may have to find other ways to regenerate
your data, or go to an even older backup.
Yih-Yoon Lee
On Wed, 24 Mar 2004 08:24:39 +1100, Greg Hines wrote:

> DBCC CheckDB at our office produces the following:
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:325) with latch type SH. sysobjects faile
d.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 1, index ID 0, page (1:325), row 62. Test (nVarCo
ls
> && (hdr->r_tagA & VARIABLE_COLUMNS)) failed. Values are 0 and 32.
> DBCC results for 'JagTrain'.
> CHECKDB found 0 allocation errors and 1 consistency errors in table
> 'sysobjects' (object ID 1).
> CHECKDB found 0 allocation errors and 1 consistency errors in database
> 'JagTrain'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (JagTrain ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> The commands:
> exec sp_dboption 'JagTrain', 'single user', 'on'
> GO
> DBCC CHECKDB ('JagTrain', repair_allow_data_loss)WITH NO_INFOMSGS
> Produces:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> The database is now single user.
> Server: Msg 8966, Level 16, State 1, Line 1
> Could not read and latch page (1:325) with latch type SH. sysobjects faile
d.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table Corrupt: Object ID 1, index ID 0, page (1:325), row 62. Test (nVarCo
ls
> && (hdr->r_tagA & VARIABLE_COLUMNS)) failed. Values are 0 and 32.
> CHECKDB found 0 allocation errors and 1 consistency errors in table
> 'sysobjects' (object ID 1).
> CHECKDB found 0 allocation errors and 1 consistency errors in database
> 'JagTrain'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (JagTrain repair_allow_data_loss).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> If anyone has any ideas as to what might be causing this problem it would
be
> appreciated.
> Thanks in advance,
> Greg|||Hi Yih-Yoon Lee,
You maybe correct but DBCC CheckDB on our clients computer (ie where the
backup came from) comes up clean. And I've done the backup and restore
several times all with the same result. Plus all backups pass verification.
Any other information you can provide would be appreciated.
Greg
--
greghines@.bigfoot.com.NOSPAM
Remove NOSPAM when replying
"Yih-Yoon Lee" <lee@.yihyoon.com> wrote in message
news:1avgpv091zsvt.1gwr1xbhp2fh7.dlg@.40tude.net...
> Hi Greg,
> Judging from your DBCC CHECKDB output, it appears that your sysobjects
> table has been corrupted. Unfortunately this is probably one of the most
> important system tables in the database as it contains information for
each
> object in the database. The Repair option most likely will not fix the
> corruption.
> If the backup exhibits same problem, that means the database has been
> backed up with corruption. You may have to find other ways to regenerate
> your data, or go to an even older backup.
> Yih-Yoon Lee
> On Wed, 24 Mar 2004 08:24:39 +1100, Greg Hines wrote:
>
failed.
(nVarCols
by
failed.
(nVarCols
by
would be

EXCEPTION_ACCESS_VIOLATION error

hi,
I am working on sql 2000 server. Jobs kept randomly
failing on one of my servers with the following error, and
the next runs will be successful randomly, any thought on
this?
'SqlDumpExceptionHandler: Process 72 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process. [SQLSTATE HY000] (Error 0).'
many thanks,
JJIn this case, you may have to call SQL Server support. SQL Server
has choked on something.
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
> hi,
> I am working on sql 2000 server. Jobs kept randomly
> failing on one of my servers with the following error, and
> the next runs will be successful randomly, any thought on
> this?
> 'SqlDumpExceptionHandler: Process 72 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process. [SQLSTATE HY000] (Error 0).'
> many thanks,
> JJ|||There should be a sqldumpxxx.txt file in the LOG folder under the MSSQL
folder. This file should show you which processes were running and which
function call choked. You might be cleaver and figure something out;
however, I'm with Armando and agree you should put in a call to MS PSS.
Sincerely,
Anthony Thomas
"Armando Prato" wrote:

> In this case, you may have to call SQL Server support. SQL Server
> has choked on something.
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
>
>

Exception of type System.OutOfMemoryException was thrown

Hi,
Once in a while I get this error when working in reporting services
I am unable to save the changes made if I get this error
I increased PF Usage Memory size, but that does not work
can someone help me with this
Thanks
PonnurangamHi Ponnurangam,
I have seen the same issue. I believe that the MS Development Environment
for Reporting Services has a memory leak. I was cutting and pasting items
and watched the resident memory of the program grow by over 100 megs.
I work around this by occasionally shutting down the MSDE and coming back in.
Not sure if a fix is in the works. Hopefully so!
"Ponnurangam" wrote:
> Hi,
> Once in a while I get this error when working in reporting services
> I am unable to save the changes made if I get this error
> I increased PF Usage Memory size, but that does not work
> can someone help me with this
> Thanks
> Ponnurangam
>
>

exception in in connection with SQL server

Hi all,
I am using OdbcConnection for coonectivity with SQL Server db. My code
is working fine with windows application but in ASP.NET or in
webservice its raising following exception -
"ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL
Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver]
[DBNETLIB]ConnectionOpen (Connect())."
at System.Data.Odbc.OdbcConnection.Open()
Same code is working fine for Orace DSN.
My code:
OdbcConnection conn = new
OdbcConnection("dsn=MyDsn;uid=sa;pwd=stars;");
conn.Open();
Please help me to sort out this problem
Thanks
Dharmendra
Check the SQL Server log for login failures. How is your web
app authenticating? How is IIS configured? What are you
using in your config files for the application?
You need to see what login is failing depending on how you
have things configured.
-Sue
On 14 May 2007 23:03:36 -0700, tomar
<dharmendratomar2000@.gmail.com> wrote:

>Hi all,
>I am using OdbcConnection for coonectivity with SQL Server db. My code
>is working fine with windows application but in ASP.NET or in
>webservice its raising following exception -
> "ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL
>Server does not exist or access denied.
>ERROR [01000] [Microsoft][ODBC SQL Server Driver]
>[DBNETLIB]ConnectionOpen (Connect())."
> at System.Data.Odbc.OdbcConnection.Open()
>Same code is working fine for Orace DSN.
>My code:
>OdbcConnection conn = new
>OdbcConnection("dsn=MyDsn;uid=sa;pwd=stars;");
>conn.Open();
>
>Please help me to sort out this problem
>Thanks
>Dharmendra

exception in in connection with SQL server

Hi all,
I am using OdbcConnection for coonectivity with SQL Server db. My code
is working fine with windows application but in ASP.NET or in
webservice its raising following exception -
"ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]
SQL
Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver]
[DBNETLIB]ConnectionOpen (Connect())."
at System.Data.Odbc.OdbcConnection.Open()
Same code is working fine for Orace DSN.
My code:
OdbcConnection conn = new
OdbcConnection("dsn=MyDsn;uid=sa;pwd=stars;");
conn.Open();
Please help me to sort out this problem
Thanks
DharmendraCheck the SQL Server log for login failures. How is your web
app authenticating? How is IIS configured? What are you
using in your config files for the application?
You need to see what login is failing depending on how you
have things configured.
-Sue
On 14 May 2007 23:03:36 -0700, tomar
<dharmendratomar2000@.gmail.com> wrote:

>Hi all,
>I am using OdbcConnection for coonectivity with SQL Server db. My code
>is working fine with windows application but in ASP.NET or in
>webservice its raising following exception -
> "ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLI
B]SQL
>Server does not exist or access denied.
>ERROR [01000] [Microsoft][ODBC SQL Server Driver]
>[DBNETLIB]ConnectionOpen (Connect())."
> at System.Data.Odbc.OdbcConnection.Open()
>Same code is working fine for Orace DSN.
>My code:
>OdbcConnection conn = new
>OdbcConnection("dsn=MyDsn;uid=sa;pwd=stars;");
>conn.Open();
>
>Please help me to sort out this problem
>Thanks
>Dharmendra

Exception Error then Crashes

hello
during working on the report layout the tool shows the
following error then it crashes;
EXCEPTION OF TYPE SYSTEM.OUTOFMEMORYEXCEPTION WAS THROWN.
please advice
Regards
Ahmad Al-khatib
technical support engineerCould you please send detailed steps to reproduce the issue? Also please
include the version information of OS, VS, and Reporting Services.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<anonymous@.discussions.microsoft.com> wrote in message
news:2089301c459b4$0acfea80$a301280a@.phx.gbl...
> hello
> during working on the report layout the tool shows the
> following error then it crashes;
> EXCEPTION OF TYPE SYSTEM.OUTOFMEMORYEXCEPTION WAS THROWN.
> please advice
> Regards
> Ahmad Al-khatib
> technical support engineer
>

Exception error in snapshot agent

Greetings,
We are using SQLServer 2000 and
we are replicating one server to another using merge replication... It has
been working fine
but all of the sudden, we are getting "An exception occurred in the Snapshot
subsystem"...
I've tried re-creating the publication a number of times but always the same
error with the Snapshot...
I've looked through all the log files but can't find any additional info
about the error.
Does anyone have any suggestion as to where or what to look for?
Thanks in advance.
Hi Steve,
From what you described below, it looks like the replication subsystem for
launching the snapshot agent from SQLServerAgent is crashing. You may want to
try stopping and restarting the SQLServerAgent service and see if that
resolves the problem.
If you are running a version of SQL2000 < SP4, the crash may also be due to
an inproperly handled COM error in the replication subsystem of
SQLServerAgent. As such, upgrading your SQL2000 instance to SP4 may provide
you with a more descriptive error of the underlying problem. If you do get a
more descriptive error after upgrading to SP4, you may be able to find a
solution by searching for the error string on the Microsoft Support web site.
HTH
-Raymond
"Steve Stoenner" wrote:

> Greetings,
> We are using SQLServer 2000 and
> we are replicating one server to another using merge replication... It has
> been working fine
> but all of the sudden, we are getting "An exception occurred in the Snapshot
> subsystem"...
> I've tried re-creating the publication a number of times but always the same
> error with the Snapshot...
> I've looked through all the log files but can't find any additional info
> about the error.
> Does anyone have any suggestion as to where or what to look for?
> Thanks in advance.
>
>

Exception comes from database,

Hi All,

Currently i am working on some web application, and facing some exceptions. Actually I am throwing exception from DB functions in some of cases, and then displaying error.aspx (custom error page). It is working fine.

But when i change language of browser from english to any other language, first time my custom error page comes, but afterwards, following error page comes:

Server error in '/sampletest' application
Exception of Project.module.myException is thrown.

Please help if any idea for same.

Please also let me know whether i have to kept lots of aspx files for different types of exception or messages.

Thanks In Advance
Arnold

If you give a sample of code then its easier to find where issue is...

EXCEPT Operator with UNION in SQL Server 2005

i was trying the new EXCEPT operator of sql server 2005 to get the rows from first table which are not there in the second table. its working fine. this is my scenario...suppose i have two table of identical schema and the data would look something like this :-

CREATE TABLE dbo.t1(col1 int, col2 int);
GO
CREATE TABLE dbo.t2(col1 int, col2 int);
GO

INSERT INTO dbo.t1 SELECT 1, 1;
INSERT INTO dbo.t1 SELECT 2, 2;
INSERT INTO dbo.t1 SELECT 3, 3;

INSERT INTO dbo.t2 SELECT 1, 1;
INSERT INTO dbo.t2 SELECT 2, 2;
INSERT INTO dbo.t2 SELECT 6, 7;
GO

SELECT * FROM dbo.t1 EXCEPT SELECT * FROM dbo.t2 -- THis statement will return 3rd row from table one which is quite obevious

SELECT * FROM dbo.t2 EXCEPT SELECT * FROM dbo.t1 -- This statment will return the 3rd row from Table 2 which is also quite normal

Now i want to get both rows (from both tables) and i am using union. But what i get is only the row from the table T2. is this a normal behaviour ?

--How can we interpret this behaviour
SELECT * FROM dbo.t1 EXCEPT SELECT * FROM dbo.t2
union
SELECT * FROM dbo.t2 EXCEPT SELECT * FROM dbo.t1
GO

Madhu

Madhu,

I think that has to do with the 'order of precedence'. UNION takes PRECEDENCE over EXCEPT, so the way the query is executing is really:

Code Snippet

SELECT * FROM dbo.t1
EXCEPT
SELECT * FROM dbo.t2 UNION SELECT * FROM dbo.t2
EXCEPT
SELECT * FROM dbo.t1

To accomplish your stated goal, use parenthesis to control the order of precedence. Such as:

Code Snippet

(SELECT * FROM dbo.t1 EXCEPT SELECT * FROM dbo.t2)
UNION
(SELECT * FROM dbo.t2 EXCEPT SELECT * FROM dbo.t1)

Just like using arithmetic, JOINS follow Rules of Precedence. It is a good idea to always use parentheses to state your JOIN intentions.

|||

thanks a lot Arnie... i got the point... i forgot the very basics... i was just trying all the new operators in sql server 2005.... i got struck in this... thanks a lot again....

Madhu

|||Not a problem. Experimenting is how we all 'keep up' with the rapidly changing 'sea of knowledge'.

EXCEPT not working

TIA. Here is my situation: I have two tables that I need to find the perform an EXCEPT op on.

Table1: ToBeAddedCodes
CodeID - varchar(14)

Table2: ExistingCodes
ExistingCodeID - varchar(14)
DateIssued - datetime
Active - bit
...&c

I perform the following command, to no avail:

select CodeID
from ToBeAddedCodes
intersect
select ExistingCodeID
from ExistingCodes

Specifically, the following error appears:

Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'intersect'.

I don't understand what the issue is... Please help. Thanks.

The syntax should be valid if you are on SQL Server 2005, any version prior 2005 won′t support the Intersect keyword. I think thats your problem. Seems that you are connected to a SQL Server 2000 instance.

HTH, Jens SUessmeyer.

-
http://www.sqlserver2005.de
-

|||Or the database is running in 8.0 compatibility level.|||The new set operators will work in 80 and other compatibility modes also. So that is not the problem. User is either running on a older version of SQL Server or old CTP releases of SQL Server 2005. The set operators EXCEPT/INTERSECT was added late in the development cycle only.|||Please post the version of SQL Server (@.@.version) that you are running this code against.|||

Thanks for the replies ppl. Here's the requested information:

Information obtained from Help/About (about indicates "MS SQL Server 2005":

Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Information obtained from "select @.@.version":

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

|||

>> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation

>> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

The server version is 8.* which means it is SQL Server 2000. You are running SQL Server Management Studio though which ships with SQL Server 2005. The client doesn't have anything to do with the server language features. So you need to create your tables on a SQL Server 2005 server and try the EXCEPT query.

|||Did you happen to install SQL Server 2005 on a machine with an existing SQL Server 2000 installed? You might have mistaken the installation to be an upgrade (just like what I did a couple of months back )|||Well, I have no control over the infrastructure or anything in fact... I am just stepping in with the .NET development. Unfortunately, it does seem as if they are a bit out of date. Yes, on my client machine I am using 2005 but the database is in 2000.

except command not working

When I use EXCEPT in sql server 2005 (like union, union all), I am getting the following error. Did any body used this command in sql server 2005. (select * from t1 except select * from t2)

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'EXCEPT'.

Quote:

Originally Posted by sajithamol

When I use EXCEPT in sql server 2005 (like union, union all), I am getting the following error. Did any body used this command in sql server 2005. (select * from t1 except select * from t2)

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'EXCEPT'.

Select * from table1 except select * from table2 works perfectly fine for me, on SQL 2005