Hello,I was wondering if there were any built-in objects to handle error handling, in any of the SQL Server objects, or just to use event log or trace or debug or something like that. Or is it not recommended at all to use try.. catch, for whatever reason.Thanks a lot.
View 4 RepliesHi
We have a database that has over a 1000 stored procedures. Most of them are used but not all of them. Because it would take to much time to investigate which are used or not, I want to log each execution of a stored procedure for a month. This way I can see which procedures are used and which are not.
Is there a way to do this???
I thought of using a DML trigger that fires after an 'exec' statement but it seems that it can be only triggered after a create, alter or drop statement.
Thanks!
Hi All
Not sure if this has been asked but quite honestly, I have tried several searches and too many hits are returned.
I use 3 separate stored procs for a process in a .net program where sp1 inserts the header. Then, looping through the details, I use sp2 to insert the detail records and then for each detail, I loop through other sub-records and insert with sp3.
I would like to place the whole update within a transaction so that an error on sp3 will rollback the entire thing.
My Problem: Each of sp1, 2 and 3 call another stored proc, say spLog which logs the process and the error messages. When I roll back, how can I prevent my logs from rolling back?
Any help will be appreciated
Can anyone tell me how to turn logging on to get statistics on execution times for stored procedures
Thanks
Dear group,is it possible in SQL-Server to see when a stored procedure wasexecuted ?I would say it is only possible with some traces but not with thestandard settings.For a short answer on that matter i'd be thankful.RegardsUli
View 4 Replies View RelatedHi all,
In my code, I will call a stored procedure which is stored on the SQL server. However, I got errors on the stored procedure (The multi-part identifier "Datetime.Today" could not be bound.). Anyone know how to fix it. Thanks in advance.
ALTER PROCEDURE [dbo].[spr_getScheduleJob]( @ToMobNum varchar(16), @OADC varchar(11), @MsgScheduleTime smalldatetime, @MsgTxt varchar(160), @CompUserName varchar(20) )
asset nocount on
Declare @TodayJobs as smalldatetimeSet @TodayJobs = Datetime.Todayselect * from Bulk_ScheduledOutgoing where @TodayJobs <= @MsgScheduleTime
I was wondering if anyone knows how to suppress error messages that get returned from a stored procedure. For example, in a stored procedure I have a convert statement that takes some dates and substrings them together into a new date format:
select convert(datetime(substring(col1,1,2)+ '/' +.......etc.
Some of the data I am working with is not guaranteed to be good. For example, sometimes the users had entered 2/2/97 - which is invalid - no leap year in 1997. So, the convert to datetime fails.
The typical error message about the convert pops up on the screen. Can I shut that off.....and still catch @@error <> 0....?
Thanks!
Dean
Hello,
The export menu in DTS is a bit confusing for me. I have some stored procedures in one SQL Server and want to copy them to another. The username is the same for both SQL Servers. When I try copying the stored procedure with defaul settings, I get a progress bar (24 percent ccompleted, 62 percent completed...), then when its done it says "failed to copy objects from sql server to sql server". When I double click the error for details it says "User or role '[username]' already exists in the current database". Then I try again, unchecking "use default options" for my export. I go in to alter the options, uncheck "copy database users and database groups" (I've also tried unchecking "copy object-level permissions"), run it, get progress bar (like above), and then get another error that says "There is no such user or group '[username]'".
My user name is correct, I am able to access both Sql Servers with this username, and I should have all of the appropriate permissions.
I think maybe there is a checkbox I'm not unchecking or something stupid like that.
Thanks in advance for any feedback.
is there an error handler in sql stored procedures? For example if i want to do a drop something and there is a lock, i want to try return if it droped it or not and if not then try again later
View 1 Replies View RelatedOur DB has around 30 SProcs - I need to move them into a script, so that it can be easily added to another server. The way I'm creating the script is to highlight all the SProcs, then copy - in my notepad screen, I paste, which gives me one script, which includes all the individual creation scripts for the Sprocs.
However, I'm getting an error when I create the script -
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'SP_MySproc'. The stored procedure will still be created.
So - what's a good, and/or easy way to structure the script, so that I can easily find WHERE to put Which SProc Script, in the list?
Hi all,
I tried to use the SSMS-EE Query to execute the following code statements:
--- SQLQuery.sql---
USE testDB
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType
varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType
varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO
//////////////////////////////////////////////////////////////////////////////////////////////////
I got the following error messages:
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10
Incorrect syntax near the keyword 'PROC'.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11
Incorrect syntax near the keyword 'PROC'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14
Incorrect syntax near 'EXE'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20
Incorrect syntax near 'updateRecord'.
/////////////////////////////////////////////////////////////////////////////////////////////////////
Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem.
Thanks,
Scott Chang
I've been researching this problem for weeks and I haven't gotten very far with it so I was hoping to get some help here.
Here's the error information we get:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
This particular error comes from a .net application, but we've seen similar "query timeout errors" from a vb6 application roughly 6 months ago.
Here are the facts we've narrowed down:
1) The timeout happens at seemingly random times (very sporadic).
2) It's currently only happening on a few stored procedures (if requested, I'll provide one of the stored procedures, but it's basically a complex search procedure).
3) To resolve the timeout error, we've found 2 temporary solutions:
A) have all clients exit the program thus closing all active connections (less than 10 connection in a 4-5 user setup)
B) I run the following script when the timeout occurs and then the stored procedure runs smoothly:
Code Block
sp_configure 'remote query timeout', 0
reconfigure with override
sp_configure 'remote query timeout', 600
reconfigure with override
4) Running the stored procedure from our application and from SQL Management studio express, is the same, except management studio doesn't time out and actually runs as long as it takes (roughly 1 minute 20 seconds or under 1 second after the script above runs).
We're pretty stumpted and it's happened at 5 different client sites with little in common. One of our sites is even running SQL express off the workstation with nothing else running and it still occurs.
I'm open to trying practically anything at this point, but unfortuntenly we have not been able to reproduce this behavior in our testing enviroment so I can't give much information for others to reproduce.
Thanks in advance!
Hi all,
I tried to use the SQL Server Managemet Studio-Express Edition to execute the following code statements:
--- SQLQuery.sql---
USE testDB
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType
varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType
varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO
//////////////////////////////////////////////////////////////////////////////////////////////////
I got the following T-SQL error messages:
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10
Incorrect syntax near the keyword 'PROC'.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11
Incorrect syntax near the keyword 'PROC'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14
Incorrect syntax near 'EXE'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20
Incorrect syntax near 'updateRecord'.
/////////////////////////////////////////////////////////////////////////////////////////////////////
Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem.
Thanks,
Scott Chang
Hi all,
I tried to use the SSMS-EE Query to execute the following code statements:
--- SQLQuery.sql---
USE testDB
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType
varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType
varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO
//////////////////////////////////////////////////////////////////////////////////////////////////
I got the following error messages:
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10
Incorrect syntax near the keyword 'PROC'.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11
Incorrect syntax near the keyword 'PROC'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14
Incorrect syntax near 'EXE'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20
Incorrect syntax near 'updateRecord'.
/////////////////////////////////////////////////////////////////////////////////////////////////////
Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem.
Thanks,
Scott Chang
Hi all,
I tried to use the SQL Server Managemet Studio-Express Edition to execute the following code statements:
--- SQLQuery.sql---
USE testDB
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType
varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType
varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO
//////////////////////////////////////////////////////////////////////////////////////////////////
I got the following T-SQL error messages:
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10
Incorrect syntax near the keyword 'PROC'.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11
Incorrect syntax near the keyword 'PROC'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14
Incorrect syntax near 'EXE'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20
Incorrect syntax near 'updateRecord'.
/////////////////////////////////////////////////////////////////////////////////////////////////////
Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem.
Thanks,
Scott Chang
Hi all,
I tried to use the SQL Server Management Studio-Express Edition to execute the following code statements:
--- SQLQuery.sql---
USE testDB
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType
varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType
varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO
//////////////////////////////////////////////////////////////////////////////////////////////////
I got the following T-SQL error messages:
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10
Incorrect syntax near the keyword 'PROC'.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11
Incorrect syntax near the keyword 'PROC'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14
Incorrect syntax near 'EXE'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20
Incorrect syntax near 'updateRecord'.
/////////////////////////////////////////////////////////////////////////////////////////////////////
Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem.
Thanks,
Scott Chang
Hi all,
I tried to use the SQL Server Managemet Studio-Express Edition to execute the following code statements:
--- SQLQuery.sql---
USE testDB
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType
varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType
varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO
//////////////////////////////////////////////////////////////////////////////////////////////////
I got the following T-SQL error messages:
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10
Incorrect syntax near the keyword 'PROC'.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11
Incorrect syntax near the keyword 'PROC'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14
Incorrect syntax near 'EXE'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20
Incorrect syntax near 'updateRecord'.
/////////////////////////////////////////////////////////////////////////////////////////////////////
Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem.
Thanks,
Scott Chang
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View Relatedthis error comes up when I run a form to supmit information to the DB. .
"Procedure or function 'aspnet_Users_CreateUser' expects parameter '@Lname', which was not supplied"
I have it declared in the procedure. so why is it telling me it was not supplied.
Here is what I have:
ALTER PROCEDURE [dbo].aspnet_Users_CreateUser
@ApplicationId uniqueidentifier,
@UserName nvarchar(256),
@IsUserAnonymous bit,
@LastActivityDate DATETIME,
@Fname varchar(50) OUTPUT,
@Lname nvarchar(50) OUTPUT,
@Address nvarchar(50) OUTPUT,
@City varchar(50) OUTPUT,
@State nchar(2) OUTPUT,
@Zip nchar(5) OUTPUT,
@RefNum nchar(10) OUTPUT,@UserId uniqueidentifier OUTPUTASBEGIN IF( @UserId IS NULL ) SELECT @UserId = NEWID() ELSE BEGIN IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId ) ) RETURN -1 ENDINSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate, Fname, Lname, Address, City, State, Zip, RefNum) VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate, @Fname, @Lname, @Address, @City, @State, @Zip, @RefNum) RETURN 0END
Thanks in advance
I have one main stored procedure. It calls other 10 stored procedures by giving input parameters.
Do I want to implement Begin Try/Begin catch in each sub procedures or in main procedure only.
I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored procedures. The VBA passes two values from excel to the stored proc., which is then supposed to pass these "parameters" to the package to use as a variable within the package.
@Cycle sql_variant = 2
WITH EXECUTE AS 'USER_ACCOUNT' - account that signs on using windows authentication
AS
BEGIN
SET NOCOUNT ON;
declare @execution_id bigint
[code]....
When I try to execute the package, from SQL Server or Excel using the Macro I built, I get the following error:"The parameter '[User::Cycle]' does not exist or you do not have sufficient permissions." I have given the USER_ACCOUNT that runs executes the stored procedure permission to read/write to the database and the SSIS project folder.
when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help
Microsoft OLE DB Provider for ODBC Drivers error
'80004005'
[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from
TDS data stream
sometime it returns error messge like, TDS Buffer Length Too Large
or
Unknown token received from SQL Server
or
Protocol error in TDS stream
or
Bad token from SQL Server: Datastream processing out of sync.
or
Invalid cursor state
or
TDS Buffer Link Too Large
or
Function sequence error
Many thanks, Please help, appreciated
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
We use SQL Server 2005 and we use lots of stored procedures. Each time a stored procedure run we log the name, execution time and username so we can identify trends and long-running procedures.
I would like to add the actual CPU and IO cost of execution to this log. Is there a way to get it? This type of information is viewable in SQL Server's performance tools - so there must be some way to get it.
Can someone point me in the right direction please?
Thanks!
Hello all,
I have a big stored procedure which is going to alter many tables,insert data, basically lot of changes.
So, i want to have a text file (or) any log file which will display, what all the changes does the stored procedure has done ( They dont want profiler output )
Can anybody know how to log the results of execution of stored procedure to a text file.
Thanks.
I click on menu item SSIS and enable logging to my SQL Database (SSIS log provider for SQL Server). When I run the package it runs fine for the first time. Upon subsequent runs I get the following error.
[Log provider "SSIS log provider for SQL Server"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "There is already an object named 'sysdtslog90' in the database.".
It seems like I need to drop the table every time to avoid this error. Is there a way I can log all package logs to single table historically? Any weblink or tip is appreciated.
Thanks!
Palak Mody
I'm looking for a facility similar to NT audit facility except for SQL Server 6.5.
Is there a tool or facility that will track when users log in (using SQL Server security), and either which stored procedures they execute or which tables they select?
Hi,
I am new to SQL Server and hence asking this.....
I have a requirement to catch any problem within my code and log it into a table with structure:
CREATE TABLE ERROR_LOG
(MSG varchar(1000),
ERROR_CODE varchar(1000)
)
As an example:
declare @test int
begin
--deliberately assigning a char into an int variable
set @test='ABC'
end
This, as expected throws an error like:
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'ABC' to data type int.
I want to catch the first line to ERROR_CODE field and the second line to MSG field in ERROR_LOG table
I also need to do it such that this proc seems SUCCEDED with logging into error log
How can I do this in SQL Server?
Please suggest.......
[From Oracle background, actually I am speaking about EXCEPTION Block in Pl/SQL]
Best Regards,
Ayan
Greetings,
I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.
Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.
Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?
Thanks,
BCB
Hi all,
I am getting the following error when I log into SQL server using MS SQL Server Management Studio.
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) (.Net SqlClient Data Provider)".
I tried to allow remote connections using Surface Area Configuration, but it is not allowing me to do so.
I am not sure what I need to do. Anybody, please advice.
Thanks & Regards
Hey all
I'm using the Execute SQL Task to import error messages into a table. I'm using the following:
"INSERT INTO SSISLog (EventDescription)
VALUES (" +@[System::ErrorDescription]+")"
I've got an import task with deliberate errors in the file to be imported to test the error logging task. When I execute it, I get the following messages:
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". )" failed with the following error: "The name 'An' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (There was an error with input column "IssueNo" (94) on input "Destination Input" (68). The column status returned was: "The value could not be converted because of a potential loss of data.". )" failed with the following error: "The name 'There' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (The "input "Destination Input" (68)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (68)" specifies failure on error. An error occurred on the specified object of the specified component. )" failed with the following error: "The name 'The' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (The ProcessInput method on component "Destination - tbl_clients" (55) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. )" failed with the following error: "The name 'The' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDescription) VALUES (Thread "WorkThread0" has exited with error code 0xC0209029. )" failed with the following error: "The name 'Thread' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any ideas?
Thank you in advance
Rupa
What happens if any of the extended stored procs below can't connect to the Exchange server? Is the failure logged? Does it back off and try again later?
xp_startmail
xp_sendmail
xp_findnextmsg
xp_readmail
xp_deletemail
sp_processmail
xp_stopmail