Error Reporting from Stored Procedures

How do you report errors from a stored procedure? For example, if the stored
procedure is trying to insert something into a table and the database is
full, how can the stored procedure return an error string to the caller?Hi Swami,
This article might help...
http://www.sqljunkies.com/Article/5...3B6D84BF94.scuk
"Swami" wrote:

> How do you report errors from a stored procedure? For example, if the stor
ed
> procedure is trying to insert something into a table and the database is
> full, how can the stored procedure return an error string to the caller?
>|||Thanks--great article.
"Ryan Randall" wrote:
> Hi Swami,
> This article might help...
> [url]http://www.sqljunkies.com/Article/564F5D77-2F7E-41FB-91C7-353B6D84BF94.scuk[/url
]
>
> "Swami" wrote:
>

Error on Stored Procedure

I need to perform a select statement across servers... this statement works in query analyzer but not as a procedure. It tells me I need to enable the ANSI_NULLS and ANSI_WARNINGS options when put into a procedure (which it looks like i did!) I think it has something to do with this GO as the stored procedure isn't recognizing it. I tried even building it as a string and executing. Any ideas as to enable this in a procedure?

CREATE PROCEDURE TESTING AS
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
SELECT * FROM MIDSERV.DB000.dbo.tblcustomer
select * from #tableTake out GO statements after SET and re-run the SP.|||Originally posted by Satya
Take out GO statements after SET and re-run the SP.

CREATE PROCEDURE TESTING AS
SET ANSI_NULLS ON

SET ANSI_WARNINGS ON

SELECT * FROM MIDSERV.DB000.dbo.tblcustomer

I get the same error telling me to enable ansi_nulls and ansi_warnings...|||Aren't the ANSI_NULL and ANSI_WARNING options to has to be set at connection level?

i belive (thought not so sure) that you cannot use it inside a stored procedure, you just have to call it BEFORE you call your stored procedure.|||that is a GOOD CALL!!!!!!!!!!!

You wouldn't know how about to set those at connection level would you? I tried doing it in a job scheduled in EM and it didn't work too well

First transact-sql statement in job:
set ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

Second transact-sql statement in job
exec procedure1

also tried combining
set ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO|||mmm...do have already tried to remove the "GO" statement?

to set an option at connection level you just have to call it BEFORE anything else you want to execute with that options activated.

now i cannot try, but i'll make a test as soon as i get a SQL Server console under my hand :-)sql

Error on Passing parameter to stored procedure

Hi,

I have a procedure that will save to table in sql server 200 via stored procedure. When I hit the save button it alwasy give me an error saying "Procedure 'sp_AddBoard' expects parameter '@.dtmWarrantyStart', which was not supplied" even though I supplied it in the code

which is

Dim ParamdtmWarrantyStart As SqlParameter = New SqlParameter("@.dtmWarrantyStart", SqlDbType.datetime, 8)
ParamdtmWarrantyStart.Value = dtmWarrantyStart
myCommand.Parameters.Add(ParamdtmWarrantyStart)

below is the stored procedure.

create Proc sp_AddBoard(@.BrandID int,
@.strPcName varchar(50),
@.bitAccounted bit,
@.dtmAccounted datetime,
@.dtmWarrantyStart datetime,
@.dtmWarrantyEnd datetime,
-- @.strDescription varchar(500),
@.intStatus int,
@.ModelNo varchar(50),
@.intMemorySlots int,
@.intMemSlotTaken int,
@.intAgpSlots int,
@.intPCI int,
@.bitWSound bit,
@.bitWLan bit,
@.bitWVideo bit,
@.dtmAcquired datetime,
@.stat bit output,
@.intFSB int) as
if not exists(select strPcName from tblBoards where strPcName=@.strPcName)
begin
insert into tblBoards
(BrandID, strPcName, bitAccounted,
dtmAccounted, dtmWarrantyStart,
dtmWarrantyEnd, --strDescription,
intStatus,
ModelNo, intMemorySlots, intMemSlotTaken,
intAgpSlots, intPCI, bitWLan,
bitWVideo, dtmAcquired,intFSB,bitWSound)

values

(@.BrandID,@.strPcName,@.bitAccounted,
@.dtmAccounted,@.dtmWarrantyStart,
@.dtmWarrantyEnd,--@.strDescription,
@.intStatus,
@.ModelNo,@.intMemorySlots,@.intMemSlotTaken,
@.intAgpSlots,@.intPCI,@.bitWLan,
@.bitWVideo,@.dtmAcquired,@.intFSB,@.bitWSound)
end
else
begin
set @.stat=1
end

The table is also designed to accept nulls on that field but still same error occured.

Please helpjust check if there is any value in the parameter value. the way i normally do is :

myCommand.Parameters.Add(New SqlParameter("@.cusbday",SqlDbType.datetime))
If len(trim(bday.Text)) = 0 Then
myCommand.Parameters("@.cusbday").Value = SqlDateTime.null
Else
myCommand.Parameters("@.cusbday").Value = DateTime.Parse(bday.Text)
End If

also you need to import the namespace System.Data.SqlTypes to use sqldatetime.null

<%@. import Namespace="System.Data.SqlTypes" %>

hthsql

Error on Migrating to V2000 SQL from V7

I have just upgraded to SQL 2000 as per the companies allowed versions and
when trying to run an Stored Procedure I am getting the following error.
Syntax error converting the varchar value '2165.05' to a column of data type
int.
The Stored Proc just selects values into a Table, the wierd thing is this
works on v7 (same data / schema everything, but not on 2000) I can code
around it with Convert etc but was wondering if its a common problem and if
there is anything more I should know.
Cheers
Stumpy
Message posted via http://www.droptable.com
Without seeing a repro, I can only guess. It could be that datatype precedence has changed. Say, for
instance, you have a column of datatype varchar and a variable of datatype nvarchar, and then do:
WHERE col = @.a
7.0 would convert the value side to the column sides datatype. But 2000 will go by the rules
specified in Books Online "Datatype Precedence", and above would result in converting the column
side to the value sides datatype.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"stumpy_uk via droptable.com" <u15773@.uwe> wrote in message news:75df584d107f7@.uwe...
>I have just upgraded to SQL 2000 as per the companies allowed versions and
> when trying to run an Stored Procedure I am getting the following error.
> Syntax error converting the varchar value '2165.05' to a column of data type
> int.
> The Stored Proc just selects values into a Table, the wierd thing is this
> works on v7 (same data / schema everything, but not on 2000) I can code
> around it with Convert etc but was wondering if its a common problem and if
> there is anything more I should know.
> Cheers
> Stumpy
> --
> Message posted via http://www.droptable.com
>
|||MS changed the rules for implicit date type converstion between V7 and 2000.
There is nothing you can do about it.
If you use a char data type in a context in which a numeric type is
required, the char value will be converted to int, unless the context
explicitly implies (sic!) that decimal or float is requied. Since 2165.05 is
not an int, but a decimal, the conversion will fail. Since you fail to
provide the SQL code, there's no chance of my giving more details.
Use CAST (val as decimal (18, 2)) [or whatever data type you want] to
resolve. Note: CONVERT is an MS-specific function, so CAST is preferable.
"stumpy_uk via droptable.com" <u15773@.uwe> wrote in message
news:75df584d107f7@.uwe...
>I have just upgraded to SQL 2000 as per the companies allowed versions and
> when trying to run an Stored Procedure I am getting the following error.
> Syntax error converting the varchar value '2165.05' to a column of data
> type
> int.
> The Stored Proc just selects values into a Table, the wierd thing is this
> works on v7 (same data / schema everything, but not on 2000) I can code
> around it with Convert etc but was wondering if its a common problem and
> if
> there is anything more I should know.
> Cheers
> Stumpy
> --
> Message posted via http://www.droptable.com
>
|||Thanks all for your help, sorry I didn't post the DDL etc, the problem was
ownership of the Database in the end.I did a ChangeDBOwner on all the tables
and the problem went away.
Cheers for all the help..
Lee
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200707/1

Error on Migrating to V2000 SQL from V7

I have just upgraded to SQL 2000 as per the companies allowed versions and
when trying to run an Stored Procedure I am getting the following error.
Syntax error converting the varchar value '2165.05' to a column of data type
int.
The Stored Proc just selects values into a Table, the wierd thing is this
works on v7 (same data / schema everything, but not on 2000) I can code
around it with Convert etc but was wondering if its a common problem and if
there is anything more I should know.
Cheers
Stumpy
Message posted via http://www.droptable.comWithout seeing a repro, I can only guess. It could be that datatype preceden
ce has changed. Say, for
instance, you have a column of datatype varchar and a variable of datatype n
varchar, and then do:
WHERE col = @.a
7.0 would convert the value side to the column sides datatype. But 2000 will
go by the rules
specified in Books Online "Datatype Precedence", and above would result in c
onverting the column
side to the value sides datatype.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"stumpy_uk via droptable.com" <u15773@.uwe> wrote in message news:75df584d107f7@.uwe...[vbcol
=seagreen]
>I have just upgraded to SQL 2000 as per the companies allowed versions and
> when trying to run an Stored Procedure I am getting the following error.
> Syntax error converting the varchar value '2165.05' to a column of data ty
pe
> int.
> The Stored Proc just selects values into a Table, the wierd thing is this
> works on v7 (same data / schema everything, but not on 2000) I can code
> around it with Convert etc but was wondering if its a common problem and i
f
> there is anything more I should know.
> Cheers
> Stumpy
> --
> Message posted via http://www.droptable.com
>[/vbcol]|||MS changed the rules for implicit date type converstion between V7 and 2000.
There is nothing you can do about it.
If you use a char data type in a context in which a numeric type is
required, the char value will be converted to int, unless the context
explicitly implies (sic!) that decimal or float is requied. Since 2165.05 is
not an int, but a decimal, the conversion will fail. Since you fail to
provide the SQL code, there's no chance of my giving more details.
Use CAST (val as decimal (18, 2)) [or whatever data type you want] to
resolve. Note: CONVERT is an MS-specific function, so CAST is preferable.
"stumpy_uk via droptable.com" <u15773@.uwe> wrote in message
news:75df584d107f7@.uwe...
>I have just upgraded to SQL 2000 as per the companies allowed versions and
> when trying to run an Stored Procedure I am getting the following error.
> Syntax error converting the varchar value '2165.05' to a column of data
> type
> int.
> The Stored Proc just selects values into a Table, the wierd thing is this
> works on v7 (same data / schema everything, but not on 2000) I can code
> around it with Convert etc but was wondering if its a common problem and
> if
> there is anything more I should know.
> Cheers
> Stumpy
> --
> Message posted via http://www.droptable.com
>|||Thanks all for your help, sorry I didn't post the DDL etc, the problem was
ownership of the Database in the end.I did a ChangeDBOwner on all the tables
and the problem went away.
Cheers for all the help..
Lee
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200707/1sql

Error on Migrating to V2000 SQL from V7

I have just upgraded to SQL 2000 as per the companies allowed versions and
when trying to run an Stored Procedure I am getting the following error.
Syntax error converting the varchar value '2165.05' to a column of data type
int.
The Stored Proc just selects values into a Table, the wierd thing is this
works on v7 (same data / schema everything, but not on 2000) I can code
around it with Convert etc but was wondering if its a common problem and if
there is anything more I should know.
Cheers
Stumpy
--
Message posted via http://www.sqlmonster.comWithout seeing a repro, I can only guess. It could be that datatype precedence has changed. Say, for
instance, you have a column of datatype varchar and a variable of datatype nvarchar, and then do:
WHERE col = @.a
7.0 would convert the value side to the column sides datatype. But 2000 will go by the rules
specified in Books Online "Datatype Precedence", and above would result in converting the column
side to the value sides datatype.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"stumpy_uk via SQLMonster.com" <u15773@.uwe> wrote in message news:75df584d107f7@.uwe...
>I have just upgraded to SQL 2000 as per the companies allowed versions and
> when trying to run an Stored Procedure I am getting the following error.
> Syntax error converting the varchar value '2165.05' to a column of data type
> int.
> The Stored Proc just selects values into a Table, the wierd thing is this
> works on v7 (same data / schema everything, but not on 2000) I can code
> around it with Convert etc but was wondering if its a common problem and if
> there is anything more I should know.
> Cheers
> Stumpy
> --
> Message posted via http://www.sqlmonster.com
>|||MS changed the rules for implicit date type converstion between V7 and 2000.
There is nothing you can do about it.
If you use a char data type in a context in which a numeric type is
required, the char value will be converted to int, unless the context
explicitly implies (sic!) that decimal or float is requied. Since 2165.05 is
not an int, but a decimal, the conversion will fail. Since you fail to
provide the SQL code, there's no chance of my giving more details.
Use CAST (val as decimal (18, 2)) [or whatever data type you want] to
resolve. Note: CONVERT is an MS-specific function, so CAST is preferable.
"stumpy_uk via SQLMonster.com" <u15773@.uwe> wrote in message
news:75df584d107f7@.uwe...
>I have just upgraded to SQL 2000 as per the companies allowed versions and
> when trying to run an Stored Procedure I am getting the following error.
> Syntax error converting the varchar value '2165.05' to a column of data
> type
> int.
> The Stored Proc just selects values into a Table, the wierd thing is this
> works on v7 (same data / schema everything, but not on 2000) I can code
> around it with Convert etc but was wondering if its a common problem and
> if
> there is anything more I should know.
> Cheers
> Stumpy
> --
> Message posted via http://www.sqlmonster.com
>|||Thanks all for your help, sorry I didn't post the DDL etc, the problem was
ownership of the Database in the end.I did a ChangeDBOwner on all the tables
and the problem went away.
Cheers for all the help..
Lee
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200707/1

Error on inserting record on remote server

Hi,
I get this error when i insert a record from my local server to a
remote server.
Server: Msg 7391, Level 16, State 1, Procedure usp_insSO2NavAppV2, Line
14
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
pls help me
from
Doller
MSDTC might not be started on one of the servers
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"doller" <sufianarif@.gmail.com> wrote in message
news:1127785678.067089.75530@.o13g2000cwo.googlegro ups.com...
> Hi,
> I get this error when i insert a record from my local server to a
> remote server.
>
> Server: Msg 7391, Level 16, State 1, Procedure usp_insSO2NavAppV2, Line
> 14
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> pls help me
> from
> Doller
>

Error on inserting record on remote server

Hi,
I get this error when i insert a record from my local server to a
remote server.
Server: Msg 7391, Level 16, State 1, Procedure usp_insSO2NavAppV2, Line
14
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
pls help me
from
DollerMSDTC might not be started on one of the servers
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"doller" <sufianarif@.gmail.com> wrote in message
news:1127785678.067089.75530@.o13g2000cwo.googlegroups.com...
> Hi,
> I get this error when i insert a record from my local server to a
> remote server.
>
> Server: Msg 7391, Level 16, State 1, Procedure usp_insSO2NavAppV2, Line
> 14
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> pls help me
> from
> Doller
>

Error on CONVERT(Datetime,'2000-01-01')

Hello,

We have several stored procedure which convert literal strings to date in the format off: CONVERT(Datetime,'2000-01-01') or such.
Those procedures are working well on our development server as well as on several other servers we installed at our customers.
However, on an MSSQL server that was install in Germany, we get an error on those convert line. The error the MSSQL return is: "Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert au?erhalb des gültigen Bereichs." which more or less translate too:
"During the conversion of a char data type into a DATE time data type lies DATE-TIME-WORTH outside of the valid range."

I managed to manually fix this by changing the convert line to the following: CONVERT(Datetime,'20000101') , but I still have few questions:

1. I would be happy to know what is the reason for the inconsistent behavior of the CONVERT(Datetime,'2000-01-01') and if there is a way to solve it without changing the stored procedure (maybe configuring something in the MSSQL setting or regional setting of the server itself).

2. Can I be assured that the CONVERT(Datetime,'20000101') will work properly on each and every instance of MSSQL, or can this format might also fail on different machines.

Thanks in advance.

Run this in Query Analyzer

SELECT CONVERT(Datetime,'2000-12-13')

SET DATEFORMAT dmy
SELECT CONVERT(Datetime,'2000-12-13')

The second one will fail because SQL server expects a date in yyyyddmm format, un Europe they use the ddmmyyyy format

Lookup SET DATEFORMAT in BOL

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Forgot to answer you question ;-(

YYYYMMDD (no dashes) is the safe ISO dateformat to use

Denis the SQL Menace

http://sqlservercode.blogspot.com/

Error occured while reading data from the query result set

Strange error. Am able to run a stored procedure from query analyzer with ...
EXEC spRS_ShopPAP '2004-03-16', '2004-04-14'
and data returns perfectly. Try and run this from the data preview window in reporting services and you get "Error occured while reading data from the query result set. OLE DB Provider MDSASQL reported an error"
Has anyone seen this before ? Shouldn't it work in the data preview window ?
Thanks, Steve.
Would a stored proc run fine in the preview window without parameters? I'm
curious...
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Error occured while reading data from the query result set

Strange error. Am able to run a stored procedure from query analyzer with ..
.
EXEC spRS_ShopPAP '2004-03-16', '2004-04-14'
and data returns perfectly. Try and run this from the data preview window in
reporting services and you get "Error occured while reading data from the q
uery result set. OLE DB Provider MDSASQL reported an error"
Has anyone seen this before ? Shouldn't it work in the data preview window ?
Thanks, Steve.Would a stored proc run fine in the preview window without parameters? I'm
curious...
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Error number 8941

Hi All,
when I run a stored procedure thru Query Analyzer, I get this error msg.
Server: Msg 8941, Level 22, State 102, Line 5
Table error: Object ID 5810549, index ID 1, page (0:782677886). Test (offset
+ len < PAGESIZE) failed. Slot 25, offset 0x1089 is invalid.
But when I tried to find out which object is throwing error I could not find
out.
I used the following code :
Select * from sysobjects where id = 5810549
go
Select object_name(5810549)
Any thoughts...
Thanks,
PradI've not seen that error before first hand. I'd personally try running the
DBCC CHECKDB command to check the integrity of the objects within the
database. If this flags up problems, post them back and I'll try to offer a
fix for them.
Hope this helps
--
Adam J Warne, MCDBA
"Pradeep Kutty" wrote:

> Hi All,
> when I run a stored procedure thru Query Analyzer, I get this error msg.
> Server: Msg 8941, Level 22, State 102, Line 5
> Table error: Object ID 5810549, index ID 1, page (0:782677886). Test (offs
et
> + len < PAGESIZE) failed. Slot 25, offset 0x1089 is invalid.
> But when I tried to find out which object is throwing error I could not fi
nd
> out.
> I used the following code :
> Select * from sysobjects where id = 5810549
> go
> Select object_name(5810549)
> Any thoughts...
> Thanks,
> Prad
>
>
>

error no in dynamic sql

to find @.error in dynamic sql

I have a temp table in my stored procedure. I tried inserting into the temp table thro dynamic sql. upto this is fine. Now i want to find if there is any error in my sql query. How to check this.

i have added the code below

CREATE PROCEDURE USP_RULE
AS
create table #TABLE1
(
SlNo int identity(1,1), EqNum varchar(25),Pointnum varchar(25)
)
declare @.EqNum varchar(25),@.Pointnum varchar(25)
DECLARE @.STRDBNAME VARCHAR(50)
SET @.STRDBNAME = 'DB1'
EXEC('insert into '+#TABLE1+' select EQNUM,POINTNUM from '+@.STRDBNAME+'..TABLE2')

IF (@.@.ERROR > 0)
BEGIN
RAISERROR ('ERROR')
END

GOYou cannot use temporary table inside exec, try this idea:

insert into #TABLE1
EXEC('select EQNUM,POINTNUM from '+@.STRDBNAME+'..TABLE2')|||I'd suggest that you turn your EXEC into a SELECT just long enough to see what is worng (!).

-PatP|||I think jtamil2001 was referring to error checking, not that s/he is getting one already. SNAIL's suggestion is the one that the poster was after. And then, if INSERT fails, @.@.ERROR will contain the corresponding value. I'd also wrap it all into a transaction:

begin tran
insert into #TABLE1
EXEC('select EQNUM,POINTNUM from '+@.STRDBNAME+'..TABLE2')

IF (@.@.ERROR > 0) BEGIN
RAISERROR ('ERROR')
rollback tran
return (1)
END
commit tran|||Originally posted by rdjabarov
I think jtamil2001 was referring to error checking, not that s/he is getting one already. SNAIL's suggestion is the one that the poster was after. And then, if INSERT fails, @.@.ERROR will contain the corresponding value. I'd also wrap it all into a transaction:

begin tran
EXEC('insert into '+#TABLE1+' select EQNUM,POINTNUM from '+@.STRDBNAME+'..TABLE2')

IF (@.@.ERROR > 0) BEGIN
RAISERROR ('ERROR')
rollback tran
return (1)
END
commit tran

Why it needs transaction for one command?

Error Msg 7391 in distributed transaction

I have a procedure which is trying to insert data into a table on Server A.
This insert is done based ona return value from a select statement from
Server B . A linked server has been set up for Server B on server A. The
error when populating table by inserting data is
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Is there any other way to get thsi work. TIA
Message posted via http://www.webservertalk.comYou want to start with this kb for your troubleshooting.
http://support.microsoft.com/kb/306212
-oj
"ishaan99 via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:e4a371938b9f454d851da07f5c672039@.SQ
webservertalk.com...
>I have a procedure which is trying to insert data into a table on Server A.
> This insert is done based ona return value from a select statement from
> Server B . A linked server has been set up for Server B on server A. The
> error when populating table by inserting data is
> Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Is there any other way to get thsi work. TIA
> --
> Message posted via http://www.webservertalk.com

Error Msg 701(on sql2000)no insufficient system memory to run q

Hi.
When I ran a procedure in sql2000, I hit this problem: Error Msg 701,Level
17,State 106,the procedure Statistic_R401(the procedure's name),line 37(the
line number varies).
To solve this problem, I setupped sq4(the exe file name is
SQL2000-KB884525-SP4-x86-CHS.EXE), so the QD verison becomes 8.00.2039 now.
And then I tried to run this procedure again: Statistic_R401, but the server
still told me this error.
The server is a 2.40GHz Intel (R) Pentium (R) 4 CPU with 512Mb RAM. The OS
is Win2003 Enterprise.
If I wont upgrade the hardware, how will I solve it? Thank you!
yangHi,
You have very less amount of memory in you server. Probaly some other
program would have taken all the memory. Just verify the memory usage.
I suggest you to make the SQL Server memory to 350 MB and restart the SQL
Server and see.
My recommendation is try increasing the RAM to 1 GB..
Thanks
Hari
"faithdiffla" <faithdiffla@.discussions.microsoft.com> wrote in message
news:111E8747-887C-472C-A446-85EFDDEF7D6B@.microsoft.com...
> Hi.
> When I ran a procedure in sql2000, I hit this problem: Error Msg
> 701,Level
> 17,State 106,the procedure Statistic_R401(the procedure's name),line
> 37(the
> line number varies).
> To solve this problem, I setupped sq4(the exe file name is
> SQL2000-KB884525-SP4-x86-CHS.EXE), so the QD verison becomes 8.00.2039
> now.
> And then I tried to run this procedure again: Statistic_R401, but the
> server
> still told me this error.
> The server is a 2.40GHz Intel (R) Pentium (R) 4 CPU with 512Mb RAM. The OS
> is Win2003 Enterprise.
> If I wont upgrade the hardware, how will I solve it? Thank you!
> yang|||Also, it might be worth to check if someone has fiddled with the sp_configure settings.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23hA9ro1FHHA.4804@.TK2MSFTNGP03.phx.gbl...
> Hi,
> You have very less amount of memory in you server. Probaly some other program would have taken all
> the memory. Just verify the memory usage.
> I suggest you to make the SQL Server memory to 350 MB and restart the SQL Server and see.
> My recommendation is try increasing the RAM to 1 GB..
> Thanks
> Hari
> "faithdiffla" <faithdiffla@.discussions.microsoft.com> wrote in message
> news:111E8747-887C-472C-A446-85EFDDEF7D6B@.microsoft.com...
>> Hi.
>> When I ran a procedure in sql2000, I hit this problem: Error Msg 701,Level
>> 17,State 106,the procedure Statistic_R401(the procedure's name),line 37(the
>> line number varies).
>> To solve this problem, I setupped sq4(the exe file name is
>> SQL2000-KB884525-SP4-x86-CHS.EXE), so the QD verison becomes 8.00.2039 now.
>> And then I tried to run this procedure again: Statistic_R401, but the server
>> still told me this error.
>> The server is a 2.40GHz Intel (R) Pentium (R) 4 CPU with 512Mb RAM. The OS
>> is Win2003 Enterprise.
>> If I wont upgrade the hardware, how will I solve it? Thank you!
>> yang
>

Error Msg 701(on sql2000)no insufficient system memory to run q

Hi.
When I ran a procedure in sql2000, I hit this problem: Error Msg 701,Level
17,State 106,the procedure Statistic_R401(the procedure's name),line 37(the
line number varies).
To solve this problem, I setupped sq4(the exe file name is
SQL2000-KB884525-SP4-x86-CHS.EXE), so the QD verison becomes 8.00.2039 now.
And then I tried to run this procedure again: Statistic_R401, but the server
still told me this error.
The server is a 2.40GHz Intel (R) Pentium (R) 4 CPU with 512Mb RAM. The OS
is Win2003 Enterprise.
If I wont upgrade the hardware, how will I solve it? Thank you!
yangHi,
You have very less amount of memory in you server. Probaly some other
program would have taken all the memory. Just verify the memory usage.
I suggest you to make the SQL Server memory to 350 MB and restart the SQL
Server and see.
My recommendation is try increasing the RAM to 1 GB..
Thanks
Hari
"faithdiffla" <faithdiffla@.discussions.microsoft.com> wrote in message
news:111E8747-887C-472C-A446-85EFDDEF7D6B@.microsoft.com...
> Hi.
> When I ran a procedure in sql2000, I hit this problem: Error Msg
> 701,Level
> 17,State 106,the procedure Statistic_R401(the procedure's name),line
> 37(the
> line number varies).
> To solve this problem, I setupped sq4(the exe file name is
> SQL2000-KB884525-SP4-x86-CHS.EXE), so the QD verison becomes 8.00.2039
> now.
> And then I tried to run this procedure again: Statistic_R401, but the
> server
> still told me this error.
> The server is a 2.40GHz Intel (R) Pentium (R) 4 CPU with 512Mb RAM. The OS
> is Win2003 Enterprise.
> If I wont upgrade the hardware, how will I solve it? Thank you!
> yang|||Also, it might be worth to check if someone has fiddled with the sp_configur
e settings.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23hA9ro1FHHA.4804@.TK2MSFTNGP03.phx.gbl...
> Hi,
> You have very less amount of memory in you server. Probaly some other prog
ram would have taken all
> the memory. Just verify the memory usage.
> I suggest you to make the SQL Server memory to 350 MB and restart the SQL
Server and see.
> My recommendation is try increasing the RAM to 1 GB..
> Thanks
> Hari
> "faithdiffla" <faithdiffla@.discussions.microsoft.com> wrote in message
> news:111E8747-887C-472C-A446-85EFDDEF7D6B@.microsoft.com...
>

Error Msg

Hi,
If I use SQL Server Agent-Jobs to create a job agent to run a stored
procedure. Where the error message from the stored procedure will go
to? Say, I set the job from today to tomorrow, running every hour.
Thanks!
MikeOn Feb 8, 4:19 pm, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> If I use SQL Server Agent-Jobs to create a job agent to run a stored
> procedure. Where the error message from the stored procedure will go
> to? Say, I set the job from today to tomorrow, running every hour.
> Thanks!
> Mike
What I mean here Error Msg is the text I use RAISEERROR to put in the
output window. Where the text message will go if I use job agent?
Thanks,
Mike|||Michael,
If you view job history and click "Show step details", you will see the
errors and messages in the text box at the bottom of the window. There is a
limit to what you can see here if there are a lot of messages or errors. You
can also:
select message from the msdb..sysjobhistory table
(watch the date and time columns, they are funky and will have to be
converted).
If you want to store it all for later review, send the text out to a file.
Edit a job step and open the Advanced tab and you'll see a place for file
name and a check box to determine if you should append to the file or not.
-- Bill
"Michael" <michaelnx@.gmail.com> wrote in message
news:1170970322.522398.278850@.q2g2000cwa.googlegroups.com...
> On Feb 8, 4:19 pm, "Michael" <michae...@.gmail.com> wrote:
> What I mean here Error Msg is the text I use RAISEERROR to put in the
> output window. Where the text message will go if I use job agent?
> Thanks,
> Mike
>

Error Msg

Hi,
If I use SQL Server Agent-Jobs to create a job agent to run a stored
procedure. Where the error message from the stored procedure will go
to? Say, I set the job from today to tomorrow, running every hour.
Thanks!
MikeOn Feb 8, 4:19 pm, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> If I use SQL Server Agent-Jobs to create a job agent to run a stored
> procedure. Where the error message from the stored procedure will go
> to? Say, I set the job from today to tomorrow, running every hour.
> Thanks!
> Mike
What I mean here Error Msg is the text I use RAISEERROR to put in the
output window. Where the text message will go if I use job agent?
Thanks,
Mike|||Michael,
If you view job history and click "Show step details", you will see the
errors and messages in the text box at the bottom of the window. There is a
limit to what you can see here if there are a lot of messages or errors. You
can also:
select message from the msdb..sysjobhistory table
(watch the date and time columns, they are funky and will have to be
converted).
If you want to store it all for later review, send the text out to a file.
Edit a job step and open the Advanced tab and you'll see a place for file
name and a check box to determine if you should append to the file or not.
-- Bill
"Michael" <michaelnx@.gmail.com> wrote in message
news:1170970322.522398.278850@.q2g2000cwa.googlegroups.com...
> On Feb 8, 4:19 pm, "Michael" <michae...@.gmail.com> wrote:
>> Hi,
>> If I use SQL Server Agent-Jobs to create a job agent to run a stored
>> procedure. Where the error message from the stored procedure will go
>> to? Say, I set the job from today to tomorrow, running every hour.
>> Thanks!
>> Mike
> What I mean here Error Msg is the text I use RAISEERROR to put in the
> output window. Where the text message will go if I use job agent?
> Thanks,
> Mike
>

Error Messages

Does anyone know of a way to suppress the error messages while executing a stored procedure in MSSQL. Basically I don't want it yelling at me becuase the primary_key is invalid. I am using the @.@.ERROR object to do my own error catching and such. But when executing a SP from if it errors out it kills the program calling it, becuase of server error. Any help would be greatly appreciated.

ThanksGood programming practice is to check data validity before trying to insert it into a table. If a critical error occurs it will stop execution for the batch, so if you want to continue processing you need to separate batches (with GO statements) or enclose them in transactions.

blindman|||Thanks for the input. I have since added valididty checks in the code so that no critical errors should occur.

Error message with stored procedure

This error message appears when you try to call a stored procedure and
supply more parameters than those declared for that procedure. Look at the
code where you call the UpdateResources procedure and check that you pass
the correct parameter list.

HTH,

Plamen Ratchev
http://www.SQLStudio.comI see only the definition for the update parameters, not the actual data
binding. Look at the asp:BoundField tags and make sure the DataField values
are correct and there is no duplication of names. Data bound fields
automatically are added to a collection that gets submitted as parameters.
See more details here:
http://msdn2.microsoft.com/en-us/library/ms228051.aspx
Also, make sure that ConflictDetection (a property of the data source) is
not set to CompareAllValues (it is set to OverwriteChanges by default). If
you have it set to CompareAllValues then two sets of parameters are passed,
one for the updated and one for the original values.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Forgot to say - a good way to troubleshoot something like this is to use SQL
Profiler to see what SQL statements actually get submitted.

Plamen Ratchev
http://www.SQLStudio.com|||On Oct 29, 4:19 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Forgot to say - a good way to troubleshoot something like this is to use SQL
Profiler to see what SQL statements actually get submitted.
>
Plamen Ratchevhttp://www.SQLStudio.com

okay Thanks will try that.