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 reporting from SP to .NET code, how?

Hey

ASP.NET 2.0 + MS Sql Server 2005 Express (that version which comes with VS2005)

This code below fails. The Delete method executes a stored procedure, but somehow this SP fails. The SP shown here is a short version of the actual SP. As you see from the code below, there are no way the Delete method can know if SP executed successfully. I assume I just could have returned @.@.error, but that just gives an error number, and not the description of the error??.. And how should I capture exceptions like these?? How should code this SP so it report the error??

Any suggestions??

public override void Delete(System.Guid id, int user)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("SendMessage", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.id", SqlDbType.UniqueIdentifier).Value = id;
cmd.Parameters.Add("@.user", SqlDbType.Int).Value = user;
}
}

ALTER PROCEDURE dbo.DeleteMessage @.id uniqueidentifier, @.user int
AS
BEGIN
UPDATE table_A set column_A = 1 WHERE Id = @.id;
DELETE FROM table_B WHERE Id = @.id;
IF (@.@.error = 0)
COMMIT TRAN;
ELSE
ROLLBACK TRAN;
END

there is a couple of way to report that what happened, you can use execute nonquery to get number of rows affected by last SQL command, you can return from your procedure single cell using SELECT with value 0 for success and 1 for error and finally you can use output parameters and read its values after procedure executes. See T-SQL help in SQL management studio express which is very good and you can find everything you wont.

Error recording

Hi all,

I have an application in production that use a lot of stored procedures and the application doesnt record any error if any during the operation. (There is only a RESUME NEXT sentence).

So I want to know if there is a way to make SQL Server to register an error if any during the execution of a Stored Procedure without changing any of them. Say, that I want SQL Server to register an error during an execution of an user defined Stored Procedure as an internal error.

Thanks in advance.

God Bless.Is there a problem trapping the error in the application (get rid of the resume next and handle the error) ?|||Originally posted by rnealejr
Is there a problem trapping the error in the application (get rid of the resume next and handle the error) ?

Yes, the application is in the production environment and we have to solve the problem as soon as posible. If we change the code in the application then after that we should test the app, and we dont have time.|||Off the top of my head - you could use alerts (but that would be tedious) - do you know what errors you want to trap ?

Error parsing XML SQL Command

Hi,
I'm getting some strange behavior when calling a stored proc...
It's an invoice page, so it has a list of assets, description, and $
total
You can modify this information and click on Update to update the
records with entered information. This works fine if I have one asset
in the invoice. But if I have more than one asset I get the following
error...
ParseXMLCommand->ReportEngine.Retrieval.ParseXMLCommand() [v
1.1.3]->ReportEngine.Retrieval.ExecuteQuery() [v 1.1.3] error
'80040067'
Error parsing XML SQL Command (Only one top level element is allowed in
an XML document. ).
/common/jax/applications/meter_invoices_client2.asp, line 126
Below is the code from meter_invoices_client2.asp which takes care of
this SQL operation. The commented lines are the existing query, which
is now being moved to a stored proc. with the code below. The embedded
SQL works fine under any circumstance. I don't think its a problem
with the stored procedure as it doesnt even get there yet.
'BEConn.Execute "Update InvoiceAsset SET SortTotal='" &
replace(Request.Form("SortTotal" & AssetID),"'","''") & "', " & _
' "SortOrderID='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "', " & _
' "Asset_location='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "', " & _
' "SubTotalDesc='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") & "',
" & _
' "RecordChanged='" & trim(Request.Form("Perm")) & "' " & _
' "WHERE asset_cd='" & AssetID & "'" & _
' "AND invoicenumber ='" & invoicenumber & "';"
XML_SP5 = XML_SP5 & "<sp name='p_update_sortOrders' db-code='" &
dbId & "' loc-code='1'>"
XML_SP5 = XML_SP5 & "<param name='@.asset_total' datatype='string'
index='1' value='" & replace(Request.Form("SortTotal" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.asset_orderId' datatype='string'
index='2' value='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.asset_location' datatype='string'
index='3' value='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.asset_subtotal' datatype='string'
index='4' value='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") &
"'/>"
XML_SP5 = XML_SP5 & "<param name='@.perm' datatype='string' index='5'
value='" & trim(Request.Form("Perm")) & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.assetID' datatype='string'
index='6' value='" & AssetID & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.invoiceNo' datatype='string'
index='7' value='" & invoicenumber & "'/>"
XML_SP5 = XML_SP5 & "</sp>"
Set oXMLRetrieve = Server.Createobject("ReportEngine.Retrieval")
Call oXMLRetrieve.ExecuteQuery(reRecordSet,XML_SP5)I think posting on here just helps me out...lol
I got it to work finally...if anyone is interested...the error was
occurring because it was looping through all the fields on the form to
check if it is the one to be updated...so everytime through the loop it
was invoking the query XML_SP5 but XML_SP5 was continually being
appended do so it would look like this on the 2nd iteration...
<sp name='p_update_sortOrders' db-code='2' loc-code='1'><param
name='@.asset_total' datatype='string' index='1' value='1029'/><param
name='@.asset_orderId' datatype='string' index='2' value='2'/><param
name='@.asset_location' datatype='string' index='3' value='WEXFORD, PA
900-18999'/><param name='@.asset_subtotal' datatype='string' index='4'
value=''/><param name='@.perm' datatype='string' index='5'
value='N'/><param name='@.assetID' datatype='string' index='6'
value='663018'/><param name='@.invoiceNo' datatype='string' index='7'
value='3459956'/></sp><sp name='p_update_sortOrders' db-code='2'
loc-code='1'><param name='@.asset_total' datatype='string' index='1'
value='4016'/><param name='@.asset_orderId' datatype='string' index='2'
value='43'/><param name='@.asset_location' datatype='string' index='3'
value='COLUMBIA, MD 900-19002'/><param name='@.asset_subtotal'
datatype='string' index='4' value='SPEED CODE 4016'/><param
name='@.perm' datatype='string' index='5' value='N'/><param
name='@.assetID' datatype='string' index='6' value='663021'/><param
name='@.invoiceNo' datatype='string' index='7' value='3459956'/></sp>
which makes it 2 top level elements (sp) and hence throws the error
Cheers!

Error parsing XML SQL Command

Hi,
I'm getting some strange behavior when calling a stored proc...
It's an invoice page, so it has a list of assets, description, and $
total
You can modify this information and click on Update to update the
records with entered information. This works fine if I have one asset
in the invoice. But if I have more than one asset I get the following
error...
ParseXMLCommand->ReportEngine.Retrieval.ParseXMLCommand() [v
1.1.3]->ReportEngine.Retrieval.ExecuteQuery() [v 1.1.3] error
'80040067'
Error parsing XML SQL Command (Only one top level element is allowed in
an XML document. ).
/common/jax/applications/meter_invoices_client2.asp, line 126
Below is the code from meter_invoices_client2.asp which takes care of
this SQL operation. The commented lines are the existing query, which
is now being moved to a stored proc. with the code below. The embedded
SQL works fine under any circumstance. I don't think its a problem
with the stored procedure as it doesnt even get there yet.
'BEConn.Execute "Update InvoiceAsset SET SortTotal='" &
replace(Request.Form("SortTotal" & AssetID),"'","''") & "', " & _
'"SortOrderID='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "', " & _
'"Asset_location='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "', " & _
'"SubTotalDesc='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") & "',
" & _
'"RecordChanged='" & trim(Request.Form("Perm")) & "' " & _
'"WHERE asset_cd='" & AssetID & "'" & _
'"AND invoicenumber ='" & invoicenumber & "';"
XML_SP5 = XML_SP5 & "<sp name='p_update_sortOrders' db-code='" &
dbId & "' loc-code='1'>"
XML_SP5 = XML_SP5 &"<param name='@.asset_total' datatype='string'
index='1' value='" & replace(Request.Form("SortTotal" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.asset_orderId' datatype='string'
index='2' value='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.asset_location' datatype='string'
index='3' value='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.asset_subtotal' datatype='string'
index='4' value='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") &
"'/>"
XML_SP5 = XML_SP5 &"<param name='@.perm' datatype='string' index='5'
value='" & trim(Request.Form("Perm")) & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.assetID' datatype='string'
index='6' value='" & AssetID & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.invoiceNo' datatype='string'
index='7' value='" & invoicenumber & "'/>"
XML_SP5 = XML_SP5 & "</sp>"
Set oXMLRetrieve = Server.Createobject("ReportEngine.Retrieval")
Call oXMLRetrieve.ExecuteQuery(reRecordSet,XML_SP5)
I think posting on here just helps me out...lol
I got it to work finally...if anyone is interested...the error was
occurring because it was looping through all the fields on the form to
check if it is the one to be updated...so everytime through the loop it
was invoking the query XML_SP5 but XML_SP5 was continually being
appended do so it would look like this on the 2nd iteration...
<sp name='p_update_sortOrders' db-code='2' loc-code='1'><param
name='@.asset_total' datatype='string' index='1' value='1029'/><param
name='@.asset_orderId' datatype='string' index='2' value='2'/><param
name='@.asset_location' datatype='string' index='3' value='WEXFORD, PA
900-18999'/><param name='@.asset_subtotal' datatype='string' index='4'
value=''/><param name='@.perm' datatype='string' index='5'
value='N'/><param name='@.assetID' datatype='string' index='6'
value='663018'/><param name='@.invoiceNo' datatype='string' index='7'
value='3459956'/></sp><sp name='p_update_sortOrders' db-code='2'
loc-code='1'><param name='@.asset_total' datatype='string' index='1'
value='4016'/><param name='@.asset_orderId' datatype='string' index='2'
value='43'/><param name='@.asset_location' datatype='string' index='3'
value='COLUMBIA, MD 900-19002'/><param name='@.asset_subtotal'
datatype='string' index='4' value='SPEED CODE 4016'/><param
name='@.perm' datatype='string' index='5' value='N'/><param
name='@.assetID' datatype='string' index='6' value='663021'/><param
name='@.invoiceNo' datatype='string' index='7' value='3459956'/></sp>
which makes it 2 top level elements (sp) and hence throws the error
Cheers!
sql

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 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 in OLD DB provider

I use OLE DB to call a series of stored procedures to add records to databas
e
tables within one transaction. I got the following errors:
Description: Cannot create new connection because in manual or distributed
transaction mode.
Source Description: Microsoft OLE DB Provider for SQL Server
The stored procedures are very simple. I do not try to create a new
connection explicitly. Neither do I try to use distributed transactions. The
only thing that might be a little complicate is I use ISequentialStream to
write blobs to table fields. The error msg confuses me. Any one has ideas
about this?Anothing might be interesting is that the error happens randomly and if I se
t
a break point in the code where it crashed and wait for a while before the
rest of stored procedures, you will not see the error and the save is
successful.
Roy
"Roy" wrote:

> I use OLE DB to call a series of stored procedures to add records to datab
ase
> tables within one transaction. I got the following errors:
> Description: Cannot create new connection because in manual or distributed
> transaction mode.
> Source Description: Microsoft OLE DB Provider for SQL Server
> The stored procedures are very simple. I do not try to create a new
> connection explicitly. Neither do I try to use distributed transactions. T
he
> only thing that might be a little complicate is I use ISequentialStream to
> write blobs to table fields. The error msg confuses me. Any one has ideas
> about this?|||The URL below mayhelp.
http://support.microsoft.com/defaul...&NoWebContent=1
Thomas
"Roy" wrote:

> I use OLE DB to call a series of stored procedures to add records to datab
ase
> tables within one transaction. I got the following errors:
> Description: Cannot create new connection because in manual or distributed
> transaction mode.
> Source Description: Microsoft OLE DB Provider for SQL Server
> The stored procedures are very simple. I do not try to create a new
> connection explicitly. Neither do I try to use distributed transactions. T
he
> only thing that might be a little complicate is I use ISequentialStream to
> write blobs to table fields. The error msg confuses me. Any one has ideas
> about this?

error Msg 156 and msg 170

Hi!
I am trying to loop through all the databases using cursor.
Here is my stored proc
if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
('master..temp_Assignments_file_count '))
DROP TABLE temp_Assignments_file_count
declare @.sql nvarchar(4000)
declare @.db varchar(300)
set @.db = 'master'
declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.crdate >= '2007-10-01' and sdb.name like 'client_%'
ORDER BY name
CREATE TABLE temp_Assignments_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Title] nvarchar(100),
[File Count] int,
[File Size (MB)] decimal(10,4),
)
open cDB
FETCH NEXT FROM cDB INTO @.db
while (@.@.fetch_status = 0)
begin
SET @.sql = 'SELECT @.@.SERVERNAME as ''[Server
Name]'', ' +
'''' + @.db + '''' + '
as ''[Database Name]'', ' +
'max(b.title) as ''[Title]'',' +
'count(*) as ''[File Count]'',' +
'round(cast(sum(length) as decimal)/1048576/1024,10) as
''[File Size]''' +
'from ' + @.db + '.dbo.filo_files a join (select b.id from ' +
@.db + 'dbo.filo_Matters b) on a.matterkey = b.id' +
'where a.id in (select distinct documentkey from ' + @.db +
'.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
'.dbo.filo_assignments' +
'where lastprocesskey in (select id from ' + @.db +
'.dbo.filo_processlog where task = ''Create Assignments'' and
starttime >= ''10/01/2007'' AND starttime <= ''09/30/2007'')))' +
'and a.id not in (select distinct documentkey from ' + @.db +
'.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
'.dbo.filo_assignments where lastprocesskey in' +
'(select id from ' + @.db + '.dbo.filo_processlog where task = ''Create Assignments'' and starttime < ''10/01/2007'')))'
INSERT temp_Assignments_file_count
EXEC sp_executesql @.sql
fetch cDB into @.db
end
close cDB
deallocate cDB
select * from temp_Assignments_file_count
I am getting the following error messages:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ')'.
(0 row(s) affected)
any help would be appreciated.
TGDo:
PRINT @.sql
before you try to execute what you have in the variable and you will find a lot of problems with the
query that you built. Based on that you can debug your code so it produces a valid query.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<jtammyg@.gmail.com> wrote in message news:1192645043.161544.136800@.v29g2000prd.googlegroups.com...
> Hi!
> I am trying to loop through all the databases using cursor.
> Here is my stored proc
> if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
> ('master..temp_Assignments_file_count '))
> DROP TABLE temp_Assignments_file_count
>
> declare @.sql nvarchar(4000)
> declare @.db varchar(300)
>
> set @.db = 'master'
> declare cDB cursor for
> SELECT name from master..sysdatabases sdb
> WHERE sdb.crdate >= '2007-10-01' and sdb.name like 'client_%'
> ORDER BY name
>
> CREATE TABLE temp_Assignments_file_count([Server Name]
> nvarchar(40),
> [Database Name]
> nvarchar(100),
> [Title] nvarchar(100),
> [File Count] int,
> [File Size (MB)] decimal(10,4),
> )
>
> open cDB
> FETCH NEXT FROM cDB INTO @.db
> while (@.@.fetch_status = 0)
> begin
> SET @.sql = 'SELECT @.@.SERVERNAME as ''[Server
> Name]'', ' +
> '''' + @.db + '''' + '
> as ''[Database Name]'', ' +
> 'max(b.title) as ''[Title]'',' +
> 'count(*) as ''[File Count]'',' +
> 'round(cast(sum(length) as decimal)/1048576/1024,10) as
> ''[File Size]''' +
> 'from ' + @.db + '.dbo.filo_files a join (select b.id from ' +
> @.db + 'dbo.filo_Matters b) on a.matterkey = b.id' +
> 'where a.id in (select distinct documentkey from ' + @.db +
> '.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
> '.dbo.filo_assignments' +
> 'where lastprocesskey in (select id from ' + @.db +
> '.dbo.filo_processlog where task = ''Create Assignments'' and
> starttime >= ''10/01/2007'' AND starttime <= ''09/30/2007'')))' +
> 'and a.id not in (select distinct documentkey from ' + @.db +
> '.dbo.semantica_corpora where projectkey in (select id from ' + @.db +
> '.dbo.filo_assignments where lastprocesskey in' +
> '(select id from ' + @.db + '.dbo.filo_processlog where task => ''Create Assignments'' and starttime < ''10/01/2007'')))'
>
> INSERT temp_Assignments_file_count
> EXEC sp_executesql @.sql
>
> fetch cDB into @.db
> end
> close cDB
> deallocate cDB
>
> select * from temp_Assignments_file_count
>
>
> I am getting the following error messages:
>
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'on'.
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'in'.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near ')'.
> (0 row(s) affected)
>
> any help would be appreciated.
> TG
>

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.