Is there any way to prevent unimportant errors in a stored procedure from causing exceptions in my C# code? This is preventing the SqlAdapter from filling the query results into my DataSet.
The Setup:
I have a Stored Procedure in Sql Server 2000 which has a text parameter called @Xml. I send in an Xml document to process. This document contains several "records" to process. The format of the xml really isn't important.
I create a temporary table called #Results to hold the results of processing each record in the xml.
To process the xml I have a Cursor which loops over a SELECT from the xml.
For each record, the sproc attempts to make a series of INSERTs and UPDATEs inside of a transaction. Any one of these commands may fail because of constraint violations or attempts to insert NULL into non-null columns, or such. After each command I check @@ERROR. If it is not zero, I stop processing the record and rollback the transaction. The cursor loops around and tries the next record. Each time the success or failure of the transaction is recorded into the #Results table.
When the cursor is done looping I 'SELECT * FROM #Results'.
I've tested this many times in the Query Analyzer and each time, regardless of any errors, I can see the result set from the SELECT of the #Results table in the Grids tab. The Messages tab shows each of the errors that occurred.
I try to call this stored procedure using the following code:
int c = 0;
try
{
sqlAdapter.Fill( sqlDS );
}
catch( System.Data.SqlClient.SqlException )
{
c = sqlDS.Tables.Count;
}
The value of c will always be zero, if there were any errors during the execution of the stored procedure. The DataSet does not get filled, even though the stored procedure is returning a result set. This is a problem for me because I expect errors to occur, and I need to know which records from the Xml caused those errors.
Is there any way to clear the errors in my stored procedure so that they don't turn into exceptions in my code? Or, is there anyway to get the Adapter to fill the DataSet regardless of any errors that were encountered?
I've also tried this with a SqlDataReader. The reader never gets assigned to because SqlCommand.ExecuteReader() throws an exception.
hi i am running a stored procedure and i want to trap the error of that stored procedure and pass it on the user in the asp.net prog.
my stored procedure are running thru a class library using C#. i tried various options of creating a new sql connnection with out using the classlibrary, but i dont get any errors. the code s used is as follows Dim conn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("dbconnection1"))
Dim cmd As New SqlCommand("usp_ImportPlanDetails", conn)
and also tried doing this oImportPlan = New ImportPlan(System.Configuration.ConfigurationManager.AppSettings(APPSETTINGS_CONNECTION))
With oImportPlan Try .ClientID = Session("ClientId") ' .FileName = strFilePath1 .save() Catch sqlex1 As SqlException LogException(sqlex1) End Try
End With Catch sqlex As SqlException LogException(sqlex) End Try Catch ex As Exception lblErrorMsg.Text = "Error: Import Failed" + ex.Message lblErrorMsg.Visible = True
End Try but have no luck in displaying the errors, My stored procedure has raiseerrors, Please help me out
I have an SSIS package that fires an OnTaskFailed event whenever any of my tasks fail. I would like to put any SSIS task failure message into a user defined variable. Any idea how to do this? Any help appreciated. Regards, Paul.
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
Hi everyone: I need to use the "SET ROWCOUNT" statement to limit the amount of data returned to the application in a query, I know that if "SET ROWCOUNT = 0" is not specified at the end of this stored proc all the next queries will return only the amount of records specified in the initial "SET ROWCOUNT" call, so I would like to know if a I can have something like the TRY-CATCH-FINALLY statement (in SQL-92 for SQL Server 2000, not in SQL 2005) to make sure the "SET ROWCOUNT = 0" is sent at the end even if an error is raised. Can it be done? Thanks for any help.
Hi there,I am converting a large PL/SQL project into Transact-SQL and have hitan issue as follows:I have a PL/SQL procedure that converts a string to a date. Theprocedure does not know the format of the date in the string so ittries loads of formats in converting the string to a date until itsucceeds.After trying each potential format it uses the Oracle 'EXCEPTION WHENOTHERS' construct to trap the failure so it can try another format.Is it possible to do this with SQLServer ? If I do a CONVERT and it isnot one of the standard formats it fails. This is part of a backgroundscheduled process and I cannot afford the procedure to bomb out.I suspect the answer is I cannot do this and will need to impose somecontrol over the string being received (from various externalsystems!!) to ensure it is a specific known format. Even if I know itwill be one of the known SQLServer formats this will not be enoughsince if the first one I try is not correct the process will crash.Any ideas ?Thanks
create proc dbo.sp_GetInvitationsHistoryDetails(@ExecID int, @OrgID int) as IF (@OrgID = 0) BEGIN select E.EventID,E.Description as Event,E.EventDate as Date I.Attending as [Att'g],NotAttending as [Not att'g],I.Bootcamp as [Maybe] I.Attended FROM Invitations I INNER JOIN Events E on I.EventID=E.EventID WHERE I.MemberID=@ExecID and NotForStats=0 ORDER BY E.EventDate DESC END ELSE BEGIN select E.EventID,E.Description as Event,E.EventDate as Date count(*) as Invited,SUM(CONVERT(smallint,I.Attended)) AS Attended FROM Invitations I INNER JOIN Events E on I.EventID=E.EventID WHERE I.MemberID IN (select ID FROM Executives WHERE OrganisationID=OrgID GROUP BY E.EventID,E.Description,E.EventDate ORDER BY E.EventDate DESC END
and i'm getting the following syntax errors when i check it -
Msg 102, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 10
Incorrect syntax near 'I'.
Msg 102, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 19
Incorrect syntax near 'count'.
Msg 156, Level 15, State 1, Procedure sp_GetInvitationsHistoryDetails, Line 23
Incorrect syntax near the keyword 'ORDER'.
Originally i just ran this sql from C# and it worked, obviously added the @ to the variables but it's basically the same.
Hello,I have a CLR stored procedure which send some values to an external URL by using the webclient, but for some reason I am getting this error.A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_LeadSend": System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException: at System.Net.WebClient.UploadValues can anyone please advice how to resolve this one...I am really having a hard luck....... thanks.
I have a stored procedure (works form the SQL side). It is supposed to update a table, however it is not working, please help. What is supposed to happen is I have a delete statement deleting a payment from the payment table. When the delete button is pushed a trigger deletes the payment from the payment table and transfers it to the PaymentDeleted table. The stored procedure is supposed to update the PaymentDeleted table with the empID and reason for deleting, the delete and transfer work fine, however these 2 fields are not updated. Below is the sp and below that is the vb code. Thanks, Karen
ALTER PROCEDURE dbo.PaymentDeletedInfoTrail (@EmpID_WhoDeleted varchar(10), @Reason_Deleted varchar(255), @PmtDeletedID int) AS BEGIN
UPDATE dbo.PaymentDeleted SET EmpID_WhoDeleted = @EmpID_WhoDeleted WHERE PmtDeletedID = @PmtDeletedID
UPDATE dbo.PaymentDeleted SET Reason_Deleted = @Reason_Deleted WHERE PmtDeletedID = @PmtDeletedID
END
Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click Me.Validate() If Me.IsValid Then Dim DR As SqlClient.SqlDataReader
Dim strPmtID As String strPmtID = lblPmtIDDel.Text
Dim MySQL As String MySQL = "DELETE From Payment WHERE PmtID = '" & strPmtID & "'" Dim MyCmd As New SqlClient.SqlCommand(MySQL, SqlConnection1) SqlConnection1.Open() DR = MyCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection) SqlConnection1.Close()
Dim strDeletePmt As String strDeletePmt = lblPmtIDDel.Text
Dim cmd As New SqlClient.SqlCommand("PaymentDeletedInfoTrail", SqlConnection1) cmd.CommandType = CommandType.StoredProcedure
Dim myParam As SqlClient.SqlParameter myParam = cmd.Parameters.Add(New SqlClient.SqlParameter("@PmtDeletedID", SqlDbType.Int)) myParam.Direction = ParameterDirection.Input myParam.Value = lblPmtIDDel.Text
I have a stored procedure that dynamically bulk loads several tables from several text files. If I encounter an error bulk loading a table in the stored procedure, all I get is the last error code produced, but if I run the actual bulk load commands through SQL Management Studio, it gives much more usable errors, which can include the column that failed to load. We have tables that exceed 150 columns (don't ask), and having this information cuts troubleshooting load errors from hours down to minutes. Onto my question..., is there any way to capture all of the errors produced by the bulk load from within a stored procedure (see examples below)?
Running this...
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
Produces this (notice column name at the end of the first error)...
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CustId).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Running this (similar to code in my stored procedure)...
BEGIN TRY
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Produces something similar to this (which is useless)... ...Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
I currently have a stored procedure that is defined as follows:
CREATE PROCEDURE UpdateSyncLog
@TableName char(100),
@LastSyncDateTime datetime,
@ErrorState int OUTPUT
I am using an execute sql task to call this procedure. The connectiontype is ADO .NET and the SQLSourceType is DirectInput. The IsQueryStoredProcedure setting is false, and the following is my SQL Statement I have entered:
Result set is set to None, as this query returns NO results (i.e. has no select statements in it that returns results).
I have two variables in this SSIS package. CurrentDateTime, and ErrorStateVal. CurrentDateTime is of Data type DateTime, the ErrorStateVal is of type Int32
The error I am getting when running this execute sql task is as follows:
Error: 0xC001F009 at AS400 to SQL Full Repopulation Sync: The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec UpdateSyncLog 'myTestTable', @LastSyncDateTime, @ErrorState" failed with the following error: "The type of the value being assigned to variable "User::ErrorStateVal" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
This makes no sense to me, both the SSIS variable ErrorStateVal is Int32, as well as the parameter declaration in the Execute SQL task is Int32 with direction of OUTPUT, and my stored procedure definition has @ErrorState as an integer as well.
We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.
Code Snippet
[SqlFunction ( FillRowMethodName = "FillRow",
TableDefinition = "CustomerID nvarchar(MAX)",
SystemDataAccess = SystemDataAccessKind.Read,
DataAccess = DataAccessKind.Read,
IsDeterministic=false)]
public static IEnumerable GetWishlist () {
using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {
using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {
if (reader.Read ()) {
myList.Add ( reader[0] as string );
}
}
return (IEnumerable)myList;
}
}
When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I am trying to create a trap to catch one of my developers which is messing around on my Live system, where he doesn't belong. I have already created a trigger which is logging and catching the guy out, but i need to know which queries he is executing on this table.
Here is my trigger:
Code Block ALTER TRIGGER [dbo].[ctr_SQLTrapDelete] ON [dbo].[Members] AFTER DELETE AS INSERT INTO SQLTrap ([User], TYPE, DateValue, HostName, SystemUser, TableName) SELECT USER_NAME(), 'DELETE', GETDATE(), HOST_NAME(), SYSTEM_USER, 'Members'
I want to add a Query field at the end of the table with the query that the guy executed.
Will anyone be able to tell me how where i am going to find the query that he executed, i am unsure of where it will reside.
I think possible in the Master.dbo.syscomments table but i'm unsure of how i am going to track the query to insert it into the table with the timestamp.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
Hi, I have a page with only a Datasource and a Gridview that allows Delete. In the database there is a referential integrity (RI) to the one of the columns. When a user tries to delete the row, the RI stops the delete and throws an error message the way it should. But it also create a hard failure on the page. How do I trap the error message so I can display a message "Referential Integrity denied deletion of this record." rather than having it fail ?
We are running SQL 2000 sp3a on Win2003 Server with all patches and SPs applied.
I want to run a stored procedure from a SQL Agent job. I want the stored procedure to run as a TRANSACTION to force a ROLLBACK in case of failure. However, if execute the procedure and an error of severity level 20 (I think) or greater happens I get blown out of the job immediately. Therefore I cannot call RAISEERROR or use @@ERROR. So, how do I assure that I can ROLLBACK the TRANSACTION?
I have searched the web and BOL and cannot find anything that addresses this specifically.
I'm using System.Net.Mail in a script task to send HTML email via SMPT client as follows:
Imports System Imports Microsoft.SqlServer.Dts.Runtime Imports System.Net.Mail Imports System.Net
Public Class ScriptMain
Public Sub Main()
Dim myHtmlMessage As MailMessage Dim mySmtpClient As SmtpClient
myHtmlMessage = New MailMessage("<from>", _ "<to>, _ "Test HTML Message", _ Dts.Variables("HtmlText").Value.ToString) myHtmlMessage.IsBodyHtml = True mySmtpClient = New SmtpClient("<smtpservername") mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials mySmtpClient.Send(myHtmlMessage)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I need to be able to trap an error when the email address is undeliverable. Is there a way to do this in SSIS? I am validating the email address before sending, but it can still come back as undeliverable. I need to be able to catch this.
I have a system using asp pages & ADO & SQL Server 2000, which processes files, builds a SQL insert statement from the file content and then executes it.
If the insert SQL fails, I need to know whether there was something wrong with the insert SQL, or something wrong with the database (e.g. SQL Server times out), and handle those differently,
Questions ======= 1. Is there an easy way to do this without checking against a list of error codes? 2. Can anyone point me to a list of errorcodes? Can't find this anywhere
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I have a database with primary and Unique key contraints on SQL Server 2000. I'm front ending it with Access 2k.
I want to trap the error when the Unique key constraint is hit, but i can't capture the error number. All attempts return error 0.
The error i get is [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY constraint 'IX_lut_Referral_Source'. Cannot insert duplicate key in object 'lut_Referral_Source'. (#2627)[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (#3621)
I've tried the soultion given here http://support.microsoft.com/kb/q185384 but now get a Type mismatch error on errStored
SaveRecODBCErr: ' The function failed because of an ODBC error. ' Below are a list of some of the known error numbers. ' If you are not receiving an error in this list, ' add that error to the Select Case statement. For Each errStored In DBEngine.Errors
Greeting All, I have a stored proc that dynamically truncates all thetables in my databases. I use a cursor and some dynamic sql for this:......create cursorLoop through sysobjects and get all table names in my database.....exec ('truncate table ' + @TableName)Now, I want to be able to determine if an error occurred or not nad logthat error to a table in another database.However, when I try to trap the value of @@ERROR after theexec ('truncate table ' + @TableName) when an actual error occurs itfails. My error was synthetically created by placing a foreign key onthe table which precludes the option of truncation:Server: Msg 4712, Level 16, State 1, Line 1Cannot truncate table 'MyTable' because it is being referenced by aFOREIGN KEY constraint.The actual relevant code snippet is:BEGINBEGINSET @v_RowCount = (SELECT rowcntFROM sysindexesWHERE id = (SELECT idFROM sysobjectsWHERE name = @v_Name)AND indid IN (0,1))EXEC('truncate table ' + @v_Name)-- If there was an error truncating the current table.-- Write the event to the MessageLog table.IF (@@ERROR <> 0)BEGINSET @v_OutputMessage = ('There was an error ' + @v_name)INSERT INTO MessageLog (message) values (@v_outputmessage)RETURN (-1)ENDLike I was saying, when the error is generated because of the foreignkey the variable @@error is never set to 4712, in fact if I were to puta "select @@ERRROR" directly below the "exec('tru..')" statement itwould never be executed. The only thing that would show up inEnterprise Manager would be the:Server: Msg 4712, Level 16, State 1, Line 1Cannot truncate table 'MyTable' because it is being referenced by aFOREIGN KEY constraint.Any ideas as to what is going on here?Thanks, TFD.
Hello, I have the following code to iterate through each view in a SQLServer and call the "sp_refreshview" command against it. It worksgreat until it finds a view that is damaged, or otherwise cannot berefreshed. Then the whole routine stops working.Can someone please help me re-write this code so that any views thatfail the "sp_refreshview" command get skipped. I'm sure it's just amatter of putting some basic error trapping into the loop, but I've hada few goes at it and failed.Many thanks.DECLARE @DatabaseObject varchar(255)DECLARE ObjectCursor CURSORFOR SELECT table_name FROM information_schema.tables WHERE table_type ='view'OPEN ObjectCursorFETCH NEXT FROM ObjectCursor INTO @DatabaseObjectWHILE @@FETCH_STATUS = 0BEGINEXEC sp_refreshview @DatabaseObjectPrint @DatabaseObject + ' was successfully refreshed.'FETCH NEXT FROM ObjectCursor INTO @DatabaseObjectENDCLOSE ObjectCursorDEALLOCATE ObjectCursorGO
Below is the script. The problem is when I simulated the Oracle linkdrop, my SQL2K never have to a chance to head to the GOTO section as itdies with this error msg and exit. Any idea on a workround? Thanks.Server: Msg 7399, Level 16, State 1, Procedure USP_Link_Check, Line 8OLE DB provider 'MSDAORA' reported an error.[OLE/DB provider returned message: ORA-12154: TNS:could not resolveservice name]OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializereturned 0x80004005: ].----------------------------------------------ALTER PROCEDURE [USP_Link_Check] ASDECLARE @myERROR int -- Local @@ERROR, @myRowCount int -- Local @@ROWCOUNT--- Verify network connectionsselect *from openquery(OraLink,'select count(*) from Oracle.table')IF @myERROR != 0 GOTO HANDLE_ERRORHANDLE_ERROR:Print ' Error in Oracle Link'RETURN @myERROR---------------------------------------------
I am running into a null reference exception on replication with SQL Server 2005 Compact Edition. The problem occurs at repl.synchronize(). Even though the statement is inside a try-catch block, the exception is not caught, and the application quits.
Device O/S: Windows Mobile 5.0 v 5.1.195 Build 14957.2.3.1 Hardware: Dell Axim X51v, Intel PXA270 Database: SQL 2005 SP2 (no post SP2-patches)
Exception shows as "an unexpected error has occured in AppName.exe. Select Quit and then restart this program, or select Details for more information." Details: .. at NullReferenceException at AppName.Form1.Synch() .. at .... (various lines) .. at AppName.Form1.Main()
The application is based on the tutorial (Creating a Mobile Application with SQL Server Compact Edition) at http://msdn2.microsoft.com/en-us/library/ms171908.aspx.
The application was successfully working initially but then stopped working on replication after some minor modifications which were not related to the replication code, e.g. adding some buttons to the form.
Three things are puzzling me here..
1. The fact that this is a NullReferenceException and not a SqlCeException 2. Why the try-catch block doesn't work 3. Why the exception occurs
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
