SQL Server 2014 :: Error Running Stored Procedure From SSIS Runs Fine In SSMS?
Mar 23, 2015
I have simple query which creates tables by passing database name as parameter from a parameter table .
SP1 --> creates databases and calls SP2--> which creates tables . I can run it fine via SSMS but when I run it using SSIS it fails with below error .The issue gets more interesting when it fails randomly on some database creation and some creates just fine .
Note** I am not passing any database of name '20'
Exception handler error :
ERROR :: 615 :: Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ---------------------------------------------------------------------------------------------------- SPID: 111 Origin: SQL Stored Procedure (SP1) ---------------------------------------------------------------------------------------------------- Could not find database ID 20, name '20'. The database may be offline. Wait a few minutes and try again. ----------------------------------------------------------------------------------------------------
Error in SSIS
[Execute SQL Task] Error: Executing the query "EXEC SP1" failed with the following error: "Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.I have sysadmin permission .
I have an SSIS package that runs fine through command pormpt although when I try to run it from a SQL Servr Agent Job CmdExec step it bombs out. Please help this has me stumped...the SSIS package uses an XML connection string so certain key settings such as connection strings and email info can be changed easily. Currently this is all on the same machine. I have not moved it beyond where I am developing.
On the command line I am using the following command...
Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 6:59:40 PM Progress: 2006-08-16 18:59:41.29 Source: Data Flow Task Validating: 0% complete End Progress Progress: 2006-08-16 18:59:41.29 Source: Data Flow Task Validating: 33% complete End Progress Progress: 2006-08-16 18:59:41.71 Source: Data Flow Task Validating: 66% complete End Progress Progress: 2006-08-16 18:59:41.73 Source: Data Flow Task Validating: 100% complete End Progress Progress: 2006-08-16 18:59:41.77 Source: Data Flow Task Validating: 0% complete End Progress Progress: 2006-08-16 18:59:41.77 Source: Data Flow Task Validating: 33% complete End Progress Progress: 2006-08-16 18:59:41.77 Source: Data Flow Task Validating: 66% complete End Progress Progress: 2006-08-16 18:59:41.77 Source: Data Flow Task Validating: 100% complete End Progress Progress: 2006-08-16 18:59:41.79 Source: Data Flow Task Prepare for Execute: 0% complete End Progress Progress: 2006-08-16 18:59:41.79 Source: Data Flow Task Prepare for Execute: 33% complete End Progress Progress: 2006-08-16 18:59:41.79 Source: Data Flow Task Prepare for Execute: 66% complete End Progress Progress: 2006-08-16 18:59:41.79 Source: Data Flow Task Prepare for Execute: 100% complete End Progress Progress: 2006-08-16 18:59:41.81 Source: Data Flow Task Pre-Execute: 0% complete End Progress Progress: 2006-08-16 18:59:41.84 Source: Data Flow Task Pre-Execute: 33% complete End Progress Progress: 2006-08-16 18:59:41.90 Source: Data Flow Task Pre-Execute: 66% complete End Progress Progress: 2006-08-16 18:59:41.90 Source: Data Flow Task Pre-Execute: 100% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Post Execute: 0% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Post Execute: 33% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Post Execute: 66% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Post Execute: 100% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Cleanup: 0% complete End Progress Progress: 2006-08-16 18:59:41.93 Source: Data Flow Task Cleanup: 33% complete End Progress Progress: 2006-08-16 18:59:41.93 Source: Data Flow Task Cleanup: 66% complete End Progress Progress: 2006-08-16 18:59:41.93 Source: Data Flow Task Cleanup: 100% complete End Progress Progress: 2006-08-16 18:59:41.95 Source: Send Mail Task The SendMail task is initiated.: 0% complete End Progress Progress: 2006-08-16 18:59:42.09 Source: Send Mail Task The SendMail task is completed.: 100% complete End Progress DTExec: The package execution returned DTSER_SUCCESS (0). Started: 6:59:40 PM Finished: 6:59:42 PM Elapsed: 1.984 seconds
When I try to use the same command within SQL Server Agent Job using a CmdExec step I get the following error...
Description: The package is attempting to configure from the XML file "S:connectionscontacts.dtsConfig". End Info Warning: 2006-08-16 18:40:03.15 Code: 0x80012012 Source: contactsPackage Description: The configuration file name "S:connectionscontacts.dtsConfig" is not valid. Check the configuration file name. End Warning Warning: 2006-08-16 18:40:03.15 Code: 0x80012059 Source: contactsPackage Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. End Warning Info: 2006-08-16 18:40:03.20 ... Process Exit Code 1. The step failed.
Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.
There is one SP with parameters varchar, int, varchar and int. It runs perfect in SSMS. Now, I've moved into a Execute SQL task with four IN parameters. In parameter mapping, data type is varchar, long, varchar, long and parameter name is 0,1,2,3. It worked before which is great. Today, I opened this pkg for another testing but failed with the following error:failed with the following error: "Incorrect syntax near ';'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,parameters not set correctly, or connection not established correctly.
I know SP is fine as tested in SSMS. I could not understand where it could go wrong in the ssis package. Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --
I have an SSIS package created from a SQL 2000 DTS using the Migration Wizard. The package imports data from a MySQL database to a SQL 2005 64-bit database running on 64-bit windows server 2003. The package runs fine when executed from SQL Server Management Studio but when I schedule it as a job it fails with:
Executed as user: [the domain admin account]. The package execution failed. The step failed.
I've tried a lot of different ways to make this work including creating a new SSIS package. Again, the package ran fine except when it was scheduled as a job.
I have created an SSIS package which, as its 2nd step, picks up a .dbf table and does a Data Flow Task of putting the .dbf table into a SQL Server 2005 table (which was just truncated in Step 1).
The Connection Manager that works on the .dbf table is Native OLE DBMicrosoft Jet 4.0 OLE DB Provider.
The entire SSIS package runs perfectly when i run it from my development environment.
I then assigned this Package to a SQL Server 2005 Job. The entire package is the sole step of the job.
But when I run the SQL Job, the Job fails immediately on Step 2.
Here is the error message in View History of the SQL Job:
Executed as user: DBASE01SQLExec. ...on 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:48:37 AM Progress: 2007-06-14 11:48:37.91 Source: DBF to ScanUS_Process Validating: 0% complete End Progress Progress: 2007-06-14 11:48:37.91 Source: DBF to ScanUS_Process Validating: 33% complete End Progress Error: 2007-06-14 11:48:37.91 Code: 0xC0202009 Source: DBF to ScanUS_Process ScanUS DBF Output File [1] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. End Error Error: 2007-06-14 11:48:37.91 Code: 0xC02020E8 Source: DBF to ScanUS_Process ScanUS DBF Output File [1] Description: Opening a rowset for "scanus_process" failed. Check that the object exists in the database. End Error Error: 2007-06-14 11:48:37.95 Code: 0xC004706B Source: DBF to ScanUS_Process DTS.Pipeline Description: "component "ScanUS... The package execution fa... The step failed.
I'm at a bit of a loss. I've written a package that has about a dozen data flow tasks and each one loads data from text files into tables in a db. It's possible that some of the fields in the text files may have data that would need to be truncated upon insert into the db.
In the data flows, I've set the fields in the data sources I know this could happen to to ignore truncation errors. When I run this within BI Studio, everything works fine. I then loaded the package into the SSIS store on the db server and if I attempt to run it from the SSIS store or if a job executes it, the package appears to be failing because of these truncation errors that I've told it to ignore.
Hi, I am using Sql database in my asp.net application. the code runs fine on the machine where i m developing but when I try to run the code on other machine it gives following error-- error is related to access to database.(sql personal server is installed on local machine) I m giving the following connection string--- "Server=localhost;UID=sa;PWD=mypassword;Database=try1" I have reinstalled the sql server using mixed mode authentication and the same sa password but still problem continues.. THE ERROR IT GIVES IS--- Server Error in '/try1' Application.
SQL Server does not exist or access denied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.Source Error:
Line 49: SqlCommand cmdSelect; Line 50: cmdSelect = new SqlCommand("select * FROM Departments", sqlConn); Line 51: sqlConn.Open(); Line 52: return cmdSelect.ExecuteReader(CommandBehavior.CloseConnection); Line 53: }Source File: c:inetpubwwwroot ry1DataAccessCode.cs Line: 51 Stack Trace:
[SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() try1.DataAccessCode.getDept() in c:inetpubwwwroot ry1DataAccessCode.cs:51 try1.WebForm1.Page_Load(Object sender, EventArgs e) in c:inetpubwwwroot ry1Login.aspx.cs:35 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain()
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
SQL Server 2012VS 2010 SQL Server data toolsFTP Connection Manager - port 21, chunk size 1kb, passive mode=false, saved plain text pwDownloads 1 csv file to local directory on SQL Server box.This always works when run from the package. And always gives a timeout error when scheduled.
Message Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Started: 11:04:59 AM Error: 2014-03-27 11:05:31.12 Code: 0xC001602A
[code]...
SQL Server Agent is set to Logon As my domain account, and in the package history it says "logged on as " my account.
When i run it from sql server data tools on the test box it runs fine. However when i run it from the ssisdb directly i get the below error.
reload table tablename1 from Excel file:Error: The "filetoload.Outputs[Excel Source Output].Columns[Product]" failed because truncation occurred, and the truncation row disposition on "filetoload.Outputs[Excel Source Output].Columns[Product]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
reload table t from Excel file:Error: There was an error with filetoload.Outputs[Excel Source Output].Columns[Product] on filetoload.Outputs[Excel Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
Running eval. edition of Sql Server Standard 2005. "Insufficient product level" error is thrown during validation phase of an OleDBCommand data flow task. This task type is not licensed in Sql Server 2005 standard? The component runs a very simple sql update statement against a one row table in Sql Server 2005.
If it works from BIDS, should it not work from dtexec.exe on the same box?
Does dtexec run under the security context of the logged in user?
I am able to select the average, max, etc of the variable in a simple select statement but when I do the following, it doesn't work. The reason I am doing the following is because I am calculating the average and such over a 5 min interval, then saving the output as one line. That is why I condense average, max, min, and stdev into one variable which I then output. why it won't run when I have it like this but will run when it is like this?
will run:
SELECT AVG(NacTemp), MAX(NacTemp),MIN(NacTemp), STDEV(NacTemp) FROM [DATABASE] WHERE [UTCDeviceTimeStamp] between DATEADD(minute, -5, GETUTCDATE()) and GETUTCDATE()
won't run but I need it to:
DECLARE @now datetime SET @now = GETUTCDATE() --Universal Time DECLARE @timeint int SET @timeint = '5' --time interval in minutes DECLARE @time datetimeSET @time = (SELECT MIN([UTCDeviceTimeStamp]) FROM [DATABASE] WHERE [UTCDeviceTimeStamp] BETWEEN DATEADD(minute, -@timeint,@now) AND @now) -- Timestamp data will be saved as DECLARE @comma varchar(4) SET @comma = ', '
I have created a lot of reports using this technique, but this is the first one that doesn't work. As there is absolutely nothing special about it, I can't figure out what the issue is.
I have one dataset that uses parameters chosen from two other dataset results. One dataset result runs just fine and returns values in reporting services, but the other returns blank in reporting services. In Visual Studio, both datasets return data.
I cannot for the life of me figure out what I've done that gives this result as I've never encountered it before and use this method of parametization quite frequently.
The dataset in question doesn't even join any tables, it's a direct select distinct field"1" from table"a".
I have a stored proceedure (which I will tag on at the end for those interested) which is taking at least 15 minutes to run when executed, but completes in 1 minute when the tsql statement is run in Query Analyser. Why is this?
I suspect that it may be connected to table indexing, but why then is this bypassed when QA is used?
Any advice appreciated.
Derek
************************************************** *********************** IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_ValidateAIGL') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE dbo.sp_ValidateAIGL GO
-- *** Global Non Program Specific Data Errors *** -- CHECK - that there are records in the DEB_IGL_PAYROLL_OUTPUT file.....none and the routine failed... IF NOT EXISTS(SELECT * FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID) INSERT INTO #IGLErrors SELECT NULL, 100, 'No records were processed by the IGL run!'
SELECT * FROM #IGLErrors
-- CHECK - search for any records where the employee's EXPENSE_CODE is NULL INSERT INTO #IGLErrors SELECT DISTINCT NULLIF(EmpNo, ''), 2, 'Employee "' + COALESCE(NULLIF(RTRIM(EmpNo), ''), '<Missing Employee>') + '" (Organisation Unit - ' + COALESCE(RTRIM(OrgUnitCode), '<No Organisation Unit>') + ') does not have a EXPENSE_CODE Code.' FROM tbl_OUT_Payroll WHERE NULLIF(ExpenseCode, '') IS NULL ANDIGLProgramID = @IGLProgramID
SELECT * FROM #IGLErrors -- CHECK - check that the BALANCE of DEBITs match the balance of CREDITs IF (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 1 AND IGLProgramID = @IGLProgramID) <> (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 0 AND IGLProgramID = @IGLProgramID) INSERT INTO #IGLErrors SELECT NULL, 3, 'The total cash value for DEBIT elements does not match the total cash for CREDIT elements.'
SELECT * FROM #IGLErrors -- *** Program 1 and 2 errors *** IF @IGLProgramID IN (1, 2) BEGIN -- CHECK - search for any records where the employee's COST_CENTRE is NULL INSERT INTO #IGLErrors SELECT DISTINCT NULLIF(EmpNo, ''), 1, 'Employee "' + NULLIF(RTRIM(EmpNo), '') + '" (Organisation Unit = ' + RTRIM(OrgUnitCode) + ') does not have a COST_CENTRE Code.' FROM tbl_OUT_Payroll WHERE NULLIF(CostCenter, '') IS NULL ANDIGLProgramID = @IGLProgramID
SELECT * FROM #IGLErrors
-- Check for EMPLOYEEs that were not transfered to the PAYROLL output (usually caused by missing ORG_UNITs or not picked up in -- the DEB_VIEW_APPOINTEE view...) INSERT INTO #IGLErrors SELECT DISTINCT EMP_NO, 11, 'Employee "' + RTRIM(EMP_NO) + '" was excluded from the summary. Check their Organisation Unit codes!' FROM PSELive.dbo.COSTING_OUTPUT WHERENOT EMP_NO IN (SELECT DISTINCT EmpNo FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID) ANDPERIOD_NO = @PeriodNo ANDTAX_YEAR = @TaxYear
SELECT * FROM #IGLErrors
-- Check that there are no ELEMENTS in the COSTING_OUTPUT table that don't exist in the tbl_IGLElements table INSERT INTO #IGLErrors SELECT DISTINCT ELEMENT, 12, 'Element "' + RTRIM(ELEMENT) + '" does not exist in the IGL Interface Elements table!' FROM PSELive.dbo.COSTING_OUTPUT WHERE ELEMENT NOT IN ( SELECT DISTINCT Element FROM tbl_IGLElements ) ANDPERIOD_NO = @PeriodNo
SELECT * FROM #IGLErrors
END
-- *** Add a error to indicate the number of errors *** IF EXISTS (SELECT * FROM #IGLErrors) INSERT INTO #IGLErrors SELECT 0, 0, 'Warning, there are ' + CAST(Count(*) AS VarChar(5)) + ' recorded errors!' FROM#IGLErrors
-- Transfer the records to the ErrorsLog table ready for the user to view... DELETE FROM tbl_SYSErrorsLog INSERT INTO tbl_SYSErrorsLog (IGLProgramID, OutputLogID, KeyField, ErrorID, Description) SELECT@ProgramLogID, @IGLPeriodID, KeyField, ErrorID, Description FROM #IGLErrors ORDER BY ErrorID
DROP TABLE #IGLErrors
SELECT * FROM tbl_SYSErrorsLog ORDER BY ErrorID
--SET NOCOUNT OFF
GO GRANT EXECUTE ON dbo.sp_ValidateAIGL TO Public GO
Hi. I've got a report with 4 different sections - the datasets coming from some tables that are populated via a stored procedure. I'd love it if the the first thing this report did was run that stored procedure and then the data would be available for the actual reporting piece. Is that possible? And if so, how do I make it work?
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],
I'm running several SSIS-packages in stored procedure using dtexec. Actually, there is a software that is running the procedure, but that's not important here.
The problem: if some of the packages failes, the whole procedure does not fail and I don't know if all the packages are successfully completed.
Here is a sample list of ssis-packages from procedure:
hi, i'm using SQL server 2000. i'm getting the below error when i run a store procedure. "Specified column precision 500 is greater than the maximum precision of 38." I have created a temporary table inside the stored procedure inserting the values by selecting the fields from other table. mostly i have given the column type as varchar(50) and some fields are numeric(50).
I have a ssis package that has multiple large lookups without memory restriction. When running the package manually from SSMS on the same server it runs on when running automatically under the job agent, the package errors out when the server memory gets depleted by the loading of the large lookup reference data. One of the messages I get is "An out-of-memory condition prevented the creation of the buffer object. "
Anyway, the package runs successfully when it runs automatically under the job agent.
I was curious as to why the above happens. Is that a bug or is the run time behavior different under these 2 environments by design.
I have a script that runs every night to pull some data from Delphi. For some strange reason the past month it has been failing on the inital running of the script but when I close off all of the error boxes and hit the script again it runs just fine.... Please find enclosed the script . Can anyone tell me whe it is wrong... thanks in advance
Hi all, I wonder if you can help me with this. Basically, Visual Web Developer doesn't like this part of my code despite the fact that the stored procedure has been created in MS SQL. It just won't accept that bold line in the code below and even when I comment it just to cheat, it still gives me an error about the Stored Procedure. Here's the line of code: // Define data objects SqlConnection conn; SqlCommand comm; // Initialize connection string connectionString = ConfigurationManager.ConnectionStrings[ "pay"].ConnectionString; // Initialize connection conn = new SqlConnection(connectionString); // Create command comm = new SqlCommand("UpdatePaymentDetails", conn); //comm.CommandType = CommandType.StoredProcedure; // Add command parameters comm.Parameters.Add("PaymentID", System.Data.SqlDbType.Int); comm.Parameters["PaymentID"].Value = paymentID; comm.Parameters.Add("NewPayment", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewPayment"].Value = newPayment; comm.Parameters.Add("NewInvoice", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewInvoice"].Value = newInvoice; comm.Parameters.Add("NewAmount", System.Data.SqlDbType.Money); comm.Parameters["NewAmount"].Value = newAmount; comm.Parameters.Add("NewMargin", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewMargin"].Value = newMargin; comm.Parameters.Add("NewProfit", System.Data.SqlDbType.Money); comm.Parameters["NewProfit"].Value = newProfit; comm.Parameters.Add("NewEditDate", System.Data.SqlDbType.DateTime); comm.Parameters["NewEditDate"].Value = newEditDate; comm.Parameters.Add("NewQStatus", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewQStatus"].Value = newQStatus; comm.Parameters.Add("NewStatus", System.Data.SqlDbType.VarChar, 50); comm.Parameters["NewStatus"].Value = newStatus; // Enclose database code in Try-Catch-Finally try { conn.Open(); comm.ExecuteNonQuery(); }
Herre is my connection string:<add name="PageEngine" connectionString="Data Source=MyServerSQLEXPRESS;Initial Catalog=PageEngine;Integrated Security=True;User ID=MyID;Password=MyPwd;" providerName="System.Data.SqlClient"/>When I run the app using WDE (Web Developer Express), Everything works fine. I can access and diplay data from the database with no problems. However, when I try to run it from a browser on the local machine or a remote machine it fails to create a datareader. I am using the Database factory from Enterprise Library. IDataReader dr = pe.DataReaderFromStoredProcedure("pe_GetApplicationInformation", null, "PageEngine");if (dr.Read() != false) (* this is the line it fails on every time){_ApplicationName = dr["ApplicationName"].ToString();_Version = dr["Version"].ToString();_Copyright = dr["Copyright"].ToString();_Owner = dr["Owner"].ToString();}dr.Close(); Here is the "DataReaderFromStoredProcedure" methodpublic IDataReader DataReaderFromStoredProcedure(string spName, peParameters parameters, string connection){Database db;if (connection != null)db = DatabaseFactory.CreateDatabase(connection);elsedb = DatabaseFactory.CreateDatabase();DbCommand dbCommand = db.GetStoredProcCommand(spName);dbCommand.CommandType = CommandType.StoredProcedure;Here is the error message I get:Object reference not set to an instance of an object. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.Source Error: Line 53: IDataReader dr = pe.DataReaderFromStoredProcedure("pe_GetApplicationInformation", null, "PageEngine"); Line 54: Line 55: if (dr.Read() != false) Line 56: { Line 57: _ApplicationName = dr["ApplicationName"].ToString();Source File: c:Public FoldersITWebSite ProjectsScotts_SandboxPageEngineWorkingApp_CodeBusiness LayerApplicationInformation.cs Line: 55 Stack Trace: [NullReferenceException: Object reference not set to an instance of an object.] PageEngine.ApplicationInformation..ctor(Page pg) in c:Public FoldersITWebSite ProjectsScotts_SandboxPageEngineWorkingApp_CodeBusiness LayerApplicationInformation.cs:55 login.GetTitle() in C:Public FoldersITWebSite ProjectsScotts_SandboxPageEngineWorkinglogin.aspx.vb:45 login.Page_Load(Object sender, EventArgs e) in C:Public FoldersITWebSite ProjectsScotts_SandboxPageEngineWorkinglogin.aspx.vb:13 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061 Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42 I have tried so many things found on the forums, I don't know what to do anymore![:'(]
We try to run a stored procedure in management studio - and we see the following error - " Executed as user: "Some User". Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
[SQLSTATE HY000] (Error 0) The log for database 'Some-database' is not available.
Check the event log for related error messages.
Resolve any errors and restart the database.
[SQLSTATE HY000] (Error 9001) During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2343114:16096:197).
Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
[SQLSTATE HY000] (Error 3314) During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2342777:81708:1).
Typically, the specific failure is logged previously as an error in the Windows Event Log service.
Restore the database or file from a backup, or repair the database. [SQLSTATE HY000] (Error 3314). The step failed.
I have a 7 step SSIS package that manipulates some data on a DB2 database. The package executes perfectly in Business Intelligence Development Studio. I save the package to my SSIS store and then point my scheduled task to it and it fails after about 9 seconds everytime. I have an identical job that works with a different DB2 database that works without any problem. The only difference is the database it's pointing to.
The package is executing as the same user who created it, which has sysadmin to both the SSIS store and the SQL instance the package is executing on. When I saved the package I selected "Rely on server storage roles for access control" for the protection level.
This one is driving me crazy, can't figure it out. Any idea's?
I was facing some issue on MP. Yesterday I changed the SQL services to use the local admin account. That didn't help my MP issue. But it may have created another issue. When I run the package manually/locally it runs fine. But the scheduled job run and fails with the Title string. --------------------------------- Executed as user: IL06EDM00SYSTEM. ...tart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259 (80004005) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 18452 (4814) Error string: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147467259 (80004005) Error string: Login failed for user '(null)'. Reason: Not associated with ... Process Exit Code 2. The step failed. --------------------------------------------------------------
When i execute a stored procedure it generally takes about half a second to run but sometimes it takes 20 to 30 seconds. I am the only one using the server so I know it is not due to other traffic. I have looked at Profiler and nothing looks out of the ordinary. Another observation is that the slow ones are always near eachother. I will have about 10 fast executions and then 3 slow ones and then back to fast ones. Has anyone seen anything like this before?
We have a customer table and an address table. Each customer can have 1 or more addresses in the address table.
The task is to synchronize customer information from 2 entirely separate systems. I have a stored procedure that reads a text file (exported from the 'master' system) and updates all the customer records in the second system (which is in the sql server db). The queries themselves work. The data is accurate. My issue is with how long it takes the stored procedure to run. With over 11,000 records in the text file, the stored procedure sometimes takes over 3 hours to run.
That seems excessive to me. (and probably to those of you snickering in the back)
As an overview: my stored procedure does the following.
1) creates a temp table 2) dumps the data from the text file into the temp table 3) updates the address table with data from the temp table (records already there) 4) inserts records into the address table from the temp table (records not already there) 5) updates the customer table with an address id 6) drops the temp table
Any help/suggestions is appreciated. If you need more info, let me know.
The other day I was asked to build a SQL Server process to terminate blocking sessions that could be safely destroyed in order not to drain necessary instance resources. The solution is made of below stored procedure and by a SQL Server Agent jobs that runs every 3 minutes just to invoke the sproc. The T-SQL code should be easy to read and has plenty of remarks.
USE [<<yourDBAgoodStuffDatabase...>>] GO SET ANSI_NULLS ON GO
