在sql-2005中需要有关存储过程功能的帮助 [英] Need help regarding functionality of stored procedure in sql-2005

问题描述

我需要您有关sql-2005中存储过程功能的帮助，
>>我们有一个vb.net应用程序，通过该应用程序，我们可以通过ODBC连接使用Data-reader执行一个存储过程(sql-2005).
>>有时此SP无法通过vb.net应用程序执行，并给出了一些有关转换的怪异错误.(过了一段时间(2-3天)问题将自行解决)
>发生问题时，同时我创建了一个与所关注的SP相似的存储过程，但是新的SP在整个应用程序中正常执行.
>>我观察到在此期间正在进行数据库差异备份.
>可以进行数据库差异备份...阻止相关的SP，以防止其从Application(odbc connection)执行. (因为SP同时在查询窗口中执行得很好)

您能否帮助我解决此问题并了解sql server-2005数据库对象(存储过程)的功能
如果您需要任何信息，请告诉我

[从OP更新]
你好莫利，

感谢您的快速答复
请参考以下错误消息和堆栈跟踪.

I need your help regarding functionality of stored procedure in sql-2005,
>>We have one vb.net application through which we are executing one stored procedure(sql-2005) using Data-reader through ODBC connection.
>>sometimes this SP is not executing through vb.net application and giving some weird error regarding conversion.(after some time(2-3 day) issue will resolve by itself)
>>when the issue occurred, at the same time I created one stored procedure similar to concerned SP ,but new SP is executing fine through the application.
>>I observed that Database differential backup is occurring during this time.
>>can database differential backup…block concerned SP to prevent it to execute from Application(odbc connection ). (because SP is executing fine in query window at the same time)

Can you help me to resolve this issue and understand the functionality of sql server-2005 database objects(stored procedure)
If you want any information please let me know

[Update from OP]
Hello Morley,

Thanks for your quick response
Please refer the below error messages and stack trace.

[2011-07-22 14:40:58] [User:877] Conversion failed when converting the varchar value '0000193627:002' to data type int.
-------------------------------------------------------------------------------------
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at STI.DataManager.ExecuteDataTable(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)
   at STI.DataManager.ExecuteDataTable(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)
   at eCIL.BusinessLayer.Reports.Compliance.GetData(Granularity ReportGranularity, Int32 TopLevelId, Int32 SubLevel, DateTime StartTime, DateTime EndTime, String RouteIDs, String TeamIDs, TeamDetails TeamDetail, Boolean QFactorOnly)

[/从OP更新]

===================================
你好莫利，

感谢您的关注，

>>存储过程中没有任何问题

>>这是间歇性问题，
>>有时存储过程不会通过应用程序执行，问题将持续一段时间并自行解决.
>>我相信，数据库中的存储过程正在发生错误，这阻止了它从应用程序中运行.
>>但是当发生问题时，相同的存储过程在查询分析器中运行良好.

>>如上所述，在发行过程中，我创建了相同的存储过程(具有diff名称)，该存储过程可以很好地执行，但是原始存储过程不能通过应用程序来执行.

如果您需要更多信息，请让我知道.

谢谢和问候，
Chaitanya

[/Update from OP]

=====================================
Hello Morley,

Thanks for your quick attenation,

>>There is nothing wrong in the stored procedure

>>This is the intermittent issue,
>>Sometime stored procedure is not excuting through the application, issue will persist for some time and will resolve by itself.
>>I beleive,something wrong is happening with that stored procedure in the database which is preventing it to run from application.
>>But when issue occurs, same stored procedure is running fine in the query analyzer.

>>As I mentioned above, during the issue, I have created the same stored procedure(with the diff name) which was excuting fine but original one was not excuting through application.

If you want any more info please let me know.

Thanks and Regards,
Chaitanya

推荐答案

存储过程中存在一个基本问题

要重新创建，请尝试此操作.打开一个新的查询窗口，并将以下代码粘贴到...

There''s an underlying problem in your stored procedure

To recreate, try this. Open a new query window and paste the following code in...

DECLARE @TestValue AS VARCHAR(255)
DECLARE @TestInt AS INT
SET @TestValue = '0000193627:002'
SET @TestInt = CONVERT(INT, @TestValue)
PRINT @TestInt

运行它时，您会看到以下错误...

You''ll see the following error when you run it...

Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value ''0000193627:002'' to data type int.

看起来很熟悉吗?！

您不能将字符串值转换为整数，导致问题的是冒号和其后的数字

看一下您的存储过程.该值从何而来，它在您的数据中代表什么?如果您可以确定这是什么字段，则应该能够看到尝试进行转换的地方.

至于为什么它是断断续续的，我想您的数据源*有时*有数据会导致问题(我不知道您的数据有多动态)，或者传递来生成数据的参数并不总是提取违规信息

Look familiar?!

You cannot convert that string value into an Integer, it''s the colon and figures following it that are causing the issue

Have a look at your stored procedure. Where is this value coming from, what does it represent in your data? If you can determine what field this is, you should be able to see where the conversion is being attempted.

As for why it''s intermittent, I would guess that your datasource *sometimes* has the data in that causes the problem (I don''t know how dynamic your data is) or the parameters passed to generate the data dont always fetch the offending information

这篇关于在sql-2005中需要有关存储过程功能的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！