I am supporting a financial application on mssql 6.5,sp4.
Other than at month-end when activiy is high, we have at some unexpected heavy insert activlty as well as deletes at unexpected times. Other than periodically watching 'current activity' or running sp_who2, we aren't sure when this happens and by whom if we don't see it.
Does any one know how I can monitor this unexpected activity?
IS there I can run periodically to trap which spid and/or user who is running such activity?
Hi to all,I have a problem about a importation of a file *.csv with SQL Server,through a bulk insert, called in a store procedure that a c# sw calls.This is the description of the error:-----System.Data.SqlClient.SqlException è stata individuataMessage="Bulk Insert: Unexpected end-of-file (EOF) encountered indata file. OLE DB provider 'STREAM' reported an error. The providerdid not give any information about the error. OLE DB error trace[OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:The provider did not give any information about the error.]. Thestatement has been terminated."Source=".Net SqlClient Data Provider"ErrorCode=-2146232060Class=16LineNumber=1Number=4832Procedure=""Server="ets3971"State=1StackTrace:at System.Data.SqlClient.SqlConnection.OnError(SqlExc eptionexception, Boolean breakConnection)atSystem.Data.SqlClient.SqlInternalConnection.OnErro r(SqlExceptionexception, Boolean breakConnection)atSystem.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObjectstateObj)at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,SqlCommand cmdHandler, SqlDataReader dataStream,BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObjectstateObj)atSystem.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds,RunBehavior runBehavior, String resetOptionsString)atSystem.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Booleanasync)atSystem.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Stringmethod, DbAsyncResult result)atSystem.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResultresult, String methodName, Boolean sendToPipe)at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at sarbox.Default.LoadFlux_Click(Object sender, EventArgs e) inc:Inetpubwwwrootarbox2.2SoxAdminDefault.aspx .cs:line 1509---Th@nks to allAB@
create proc dbo.usp_CatchAddressException @tableName varchar(500) as -- alter proc dbo.usp_CatchAddressException @tableName varchar(500) as
begin set nocount on declare @sql varchar(700)
-- truncate table tempException set @sql = 'insert hava_dcde.dbo.STREET_EXCEPTION (id_town, id_voter, pre_value, nbr_exception, date_stamp) select a.id_town, a.id_voter, substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))), ''20'', getdate() from HAVA_DCDE.dbo.' + quoteName(@tableName)+ ' a where substring(a.ad_str1, 1, len(a.ad_str1) - charindex('' '', reverse(a.ad_str1))+1) not in (select nm_street from HAVA_DCDE.dbo.state_streets ss)' -- and a.id_town = + @tableName -- print(@sql)
exec(@sql) set nocount off end ---------------------------------------------------
when i run this SP 2 times, it will insert the values 2 times. what should i do if i don't want to insert the same values 2 times? I'm confussed, Should i use update? any input will be appreciated.
I'm using the following code to add some data to a table: Dim rand As Random = New Random Dim num As Int32 = rand.Next(10000000) Dim strConn as string = "......." Dim sql as string = "INSERT INTO tblitemid (itemid, userid, datetime, supplier, comment, commenttype, uniqueid) VALUES ('" & label1.Text & "', '" & user.identity.name & "', '"& System.DateTime.Now & "','3763' ,'" & textbox1.text & "' , 'C' ,'" & num & "')" Dim conn as New SQLConnection(strConn) Dim Cmd as New SQLCommand(sql, conn) Try conn.Open() Catch SQLExp as SQLException Response.Write ("An SQL Server Error Occurred: " & e.toString()) Finally cmd.ExecuteNonQuery conn.Close() End Try
As far as I can tell the code works fine. But for some odd reason I click the button, the code execute and the page closes as it should, but the data is never inserted into the database. I cant really seem to pick up on any paterns for why this would be happening. As a rough guess I'd say it doesnt insert the data 1 out of every 5 times or so it seems. Anyone every have any experience with this? Any comments would be helpful, cuz I'm at a loss. If youd like to see more code let me know.... Thanks, Scott
I have a table with 4869068 rows and when some one tries to insert the records into this table the database times out....Does any one know what could be the reason and from where do I start debugging. I have no problem with the disk space?
Ok, so I have a primary table that contains the count of a linked table. Since I can let the identity update itself, I should be able to insert the same values into the linked child table.
I need to put the same record in the child table 1000 (that's an arbitrary number, this will be programatically determined by the user) times.
I understand that I can do this:
INSERT INTO SomeTable (Cols) VALUES (vals) GO 1000
However, I can't make this work when doing an ADO.NET ExecuteCmd. It doesn't like the Go 1000 part of it.
Does anyone have an idea how I can do this VERY QUICKLY without having to execute a separate insert for every item (or batch them)?
The number could be as high as 250,000 in the child table.
I have two tables in my database. I want to insert date and no.of hours worked on that day for a particular project. So in a week if end user enters 7 dates and hrs for each day...... i have to insert those values into the table against one project only. Can any one please help me out how to run insert query for 7 times (in a week) with different parameters
Is there a way to configure mirroring to go from High Availability to High Protection without having to reconfigure Database Mirroring? Using the interface in Management Studio, I can change the configuration option to High Performance, but not High Protection despite both of them being Synchronous.
If not, what are the recommended steps to configure the mirror once it already has been configured? Is just like initially setting up the mirror or would there be any shortcuts I could take? If I stop the mirroring and remove the witness, will the High Protection option be available?
I realise this is a stupid quesiton but i cannot really find any confirmation of this in BOL.
If you are running High Safety with automatic failover, when failover occurs does this automatically change to High Performance mode. SInce for failover to occur something has happen with the primary , it will be impossible to commit transactions on the new primary and mirror asyncronously since 1 of them is no longer available.
So am i correct in assuming that automatic failover also automatically changes the mode to High Performacne for that session?
I am working with SQL server 6.5. DB Library display error unexpected EOF from SQL when I execute a batch Can somebody tell me what can occurs this error ?
We are trying to configure and run SSRS on a server installed at the customer data centre. The ReportServer web service is unable to start up and leaves behind this odd error.
w3wp!library!1!11/19/2007-19:21:29:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file. w3wp!library!1!11/19/2007-19:21:29:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file. w3wp!library!1!11/19/2007-19:21:29:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file. w3wp!library!1!11/19/2007-19:21:29:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file. w3wp!library!1!11/19/2007-19:21:29:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file. w3wp!library!1!11/19/2007-19:21:29:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file. w3wp!library!1!11/19/2007-19:21:29:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file. w3wp!library!1!11/19/2007-19:21:29:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file. w3wp!library!1!11/19/2007-19:21:29:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file. w3wp!library!1!11/19/2007-19:21:29:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file. w3wp!library!1!11/19/2007-19:21:29:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., unexpected SKU value; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information. We suspect that when they first installed SQL Server, the optical media that they reported to be "faulty" was replaced with another one, and ended up installing Enterprise Edition of SQL Server. They were supposed to install Standard Edition. However, we'd like to confirm if the error message really the case that somehow SSRS is expecting the Std Ed SKU but meets up with Ent Ed?
SQL Server 2000 SP4.Running the script below prints 'Unexpected':-----------------------------DECLARE @String AS varchar(1)SELECT @String = 'z'IF @String LIKE '[' + CHAR(32) + '-' + CHAR(255) + ']'PRINT 'Expected'ELSEPRINT 'Unexpected'-----------------------------If the @String variable is set to 'y' (or in fact any ANSI character otherthan 'z'), the result is 'Expected'. The comparison also evaluates asexpected if CHAR(255) is replaced with CHAR(254). The server collation, ifthat matters, is SQL_Latin1_General_CP1_CI_AS.It would be helpful to find the explanatin of this behavior. Thanks.--(remove a 9 to reply by email)
I'm having issues serializing an XMLChoiceIdentifier in SQLServer. I'm attempting to serialize a type within a larger XML message. The parent object contains and array of sub-objects. The xml I'm trying to serialize is:
public struct DocumentIdentifier : IBinarySerialize, ICloneable, IComparable, INullable
{
private bool _isNull;
private int _idNumber;
[XmlAttribute("id")]
public int ID
{
get { return _idNumber; }
set
{
_idNumber = value;
_isNull = false; }
}
.... }
The error I keep receiving in SQLServer is:
A .NET Framework error occurred during execution of user-defined routine or aggregate "DocumentRequest":
System.InvalidOperationException: There is an error in XML document (2, 2). ---> System.InvalidOperationException: <Documents xmlns=''> was not expected.
System.InvalidOperationException:
at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read156_DocumentRequest()
System.InvalidOperationException:
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)
at System.Xml.Serialization.XmlSerializer.Deserialize(TextReader textReader)
at Mitochon.Global.Messaging.Internal.DocumentRequest.Parse(SqlString s)
at Mitochon.Global.Messaging.Internal.DocumentRequest.Read(BinaryReader r)
at DocumentRequest::.Deserialize(IntPtr , DocumentRequest& )
Any have any advice on how to get rid of this error?
I'm installing SQL Server 2005 on Windows Server 2003 R2. When installing the support files for the SQL installation I receive the following error message:
"There was an unexpected failure during the setup wizard. You may review the setup logs and/or click the help button for more information"
The information I receive when clicking on the help button are as follows: LinkID: 20476 ProductName: Microsoft SQL Server Product Version: 9.00.1399.06 Message Source: setup.rll Message ID: 50000 EvtType: setupsqlsetupactions.cpp@invokeS
I tried looking on the net for probable causes, but could not find any.
when i am running bulk insert from codebehind but have a error =
"Unexpected end-of-file (EOF) encountered in data file. OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.]. The statement has been terminated."
Has anyone ever experienced the SQL Server unexpectedly rebooting?
Yesterday my SQL Server 7 installation rebooted itself and the error log seemed fine as did the restart. The only evidence I can find was a message in the event viewer's application log stating the the MSSQLServer service terminated unexpectedly. Any ideas are appreciated.
I created a few DTS packages in our Test Server saving them as file.dts. When I try to open them in from the production server I get an Unexpected error and the DTS is not opened. Do you have any fresh idea for me, please? Thanks!! :)
/*********** Script 1 **************/ declare @nr_1 as decimal (10,2) declare @nr_2 as decimal (10,2) set @nr_1=5 set @nr_2=3 select round(@nr_1/@nr_2,0)
I've got a DTS package that runs an active-x script. The script issimple - it runs a stored procedure and saves the results to a CSVfile. I kept getting this error message when trying to run it sayingthat the recordset object I was using could not be used when closed.Well, it didn't make a whole lot of sense to me as to why that washappening, and it doesn't realte to my question except to give you asense of what I'm trying to do. After spending an inordinate amount oftime on that... I decided to just create a SQL Server connectionobject and an Excel Object and then use a transformation to load thequery results. Simple enough, or so I thought. So in thetransformation object under the Source tab, I typed in the query to runthe Stored Procedure:Declare @S nvarchar(30)Declare @E nvarchar(30)SET @S = Convert(nvarchar(30), GetDate()-1, 101) + ' 12 AM'SET @E = Convert(nvarchar(30), GetDate()-1, 101) + ' 11:59:59 PM'exec CTI_REPORT_Q_ACTIVITY_DETAIL @S, @EAnd then I clicked on the preview button. I got the message that norowset was returned. In a way, that explains the issue with theActive-X script. BUT, I know darn well it returns data. It returns438 rows of data when I run this in Query Analyser.So, here's my question....how could that be? Is there some issue thatDTS packages have with temporary tables? I do use a couple in theStored Procedure. Without having to post the stored procedure andtables, etc. could someone let me know if they've run into somethinglike this before?Thanks,Jennifer
If I have an SQL query which returns an aggregate of several decimal fieldslike so:(sum(COALESCE(myDecimal1, 0)+sum(COALESCE(myDecimal2, 0)+sum(COALESCE(myDecimal3, 0)) as MyTotalI get an rounded integer in MyTotal.However, if I do the following:sum(COALESCE(myDecimal1, 0)+COALESCE(myDecimal1, 0)+COALESCE(myDecimal1, 0)) as MyTotalI get a (proper) decimal value.Does anyone know why the first case returns an Integer?- Don
I posted this to microsoft.public.sqlserver.programming, but no onecould answer my question. So I think it is a good place to re-post myquestion here.My question:I found that if you do not include any effective SQL statement betweenBEGIN/END block, SQL Server 2000 Query Analyzer will think it is anerror:Server: Msg 156, Level 15, State 1, Line 6Incorrect syntax near the keyword 'end'.if 1=1select getdate()elsebegin--select 'ok'endIs this behavior a SQL standard or simply a M$ standard glitch?
I'm trying to run what I thought was a relatively straightforward query to find all entries from one table that don't appear in another table:
select * from Search_Suggestion where Suggestion not in (select distinct C106 from Search_Log)
The Search_Suggestion table contains 4060 entries and the Search_Log table contains 142,000+ distinct entries.
From running a similar query using 'in' instead of 'not in' I find that there are 3778 matches between the two tables would logic which suggest leave 282 that don't exist in the Search_Log table.
However....when I run the above query it returns no records.
I've tried changing it around and have also tried using temp tables but each time I still get no records.
We encounter the problem, that a reportservertempdb grows extremly large... the sessiondata and snapshotdata tables are about 10gb each at the moment and keep growing and growing... The CleanupCycleMinutes Configuration is set to default=10. We are not taking snapshots of our reports. Shouldnt those tables be cleaned up every 10minutes with this setting?
What can we do to stop this database growing? is truncating those tables on a weekly base a solution? We are using SQL Server Reporting Services 2005 SP2 with Cumulative Hotfix Package 5 (Build 3215).
Anyone know why SQLNumResultCols would return a different value for ColumnCountPtr in ODBC 3.0 than in ODBC 2.0?
The only difference in our test program is that we set a different value for the SQL_ATTR_ODBC_VERSION attribute via SQLSetEnvAttr. When we set it to SQL_OV_ODBC2, SQLNumResultCols returns the expected value (10). When we set it to SQL_OV_ODBC3, SQLNumResultCols returns an unexpected value (0).
Hi,My code worked fine before i placed "SqlTransaction" command in my code. Now it is showing "Unexpected existing transaction." Please tell me where I am going wrong. My Code:
protected void Page_Load(object sender, EventArgs e) { using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Mfund_String"].ConnectionString)) { con.Open(); using (SqlConnection destinationConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Mfund_String"].ConnectionString)) { destinationConnection.Open(); using (SqlTransaction transaction = destinationConnection.BeginTransaction()) { SqlCommand DelCmd = new SqlCommand("delete from mfund_data", con); DelCmd.ExecuteNonQuery(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection)) { bulkCopy.DestinationTableName = "mfund_data"; try { bulkCopy.WriteToServer(CreateDataTableFromFile()); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); Response.Write(ex.Message); } } } destinationConnection.Close(); } con.Close(); } }Regards,Jagadeesh
I have programmatically created a SqlConnection that begins a SqlTransaction. During the first part of this SqlTransaction, the contents of a table are deleted. The next part uses the SqlBulkCopy object to copy data from another database (in the form of a DataTable). The delete goes through fine, but the SqlBulkCopy always generates a SqlException with the message "Unexpected existing transaction." I cannot think of anything I am doing wrong. The code looks at an XML file for instructions on each transaction. Each transaction is composed of tasks. Each task will pull data from a different type of database (MVR.Command is a Factory Database object). Please view the code below and tell me if you can spot what I am doing wrong: using (SqlConnection destinationConnection = new SqlConnection(MVR.ConnectionSource.GetConnectionString(destinationServiceName))) { destinationConnection.Open();
using (SqlTransaction transaction = destinationConnection.BeginTransaction(IsolationLevel.Snapshot, "Transport")) { transaction.Save("Beginning");
int totalTasks = 0; int successfulTasks = 0;
foreach (XmlNode taskNode in transactionNode.SelectNodes("Tasks/Task")) { totalTasks += 1;
try { // Prepare the destination table (delete everything) int rowsDeleted = new SqlCommand("delete from " + destinationTablename, destinationConnection, transaction).ExecuteNonQuery();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection)) { bulkCopy.DestinationTableName = destinationTablename;
// Based on the success of all tasks, either commit or rollback if (successfulTasks == totalTasks) { transaction.Commit(); } else { transaction.Rollback(); } }
I have W2K Adv, SQL 7 Enterprise in a workgroup with mixed security. The SQL installation is an almost entirely default install.
At the console, in SQL Enterprise Manager, when I select open a database, open tables, select a table, open table, and select all rows the system displays "an unexpected error happened during this operation". As far as I know this has never worked on this installation.
The error occurs with every logon account (including sa and local administrator accounts), both the "all rows or top row" options, every table, every database (including the customers database and the Northwind database).
I have tried adding administrators and accounts as users of the databases etc. and given the accounts all the permissions I can dream up.
There are no interesting messages in the event viewer. The SQL agent is running.
Technet found two documents but not related to the problem.
I can run SQL Analyzer and run "select * from table_name". That works on the Northwind and customer database tables - every time.
Colleagues with other installations do not get the error, and their systems return the rows correctly.
DECLARE @num int SET @num = 0 DECLARE @tableVariable table(ColA int, ColB decimal(18,4)) WHILE @num < 1000 BEGIN INSERT INTO @tableVariable VALUES (2, 10.56) SET @num = @num +1 ENDWhen this code is run in SQL Server 2000 Query Analyzer it commits in less than 1 second. The same code run as a SQLServerAgent job takes 16 seconds.
Similar behaviour appears if INSERT statement is substituted with UPDATE.
SELECT statement runs equally nice on both alternatives.
Has anybody got an idea what might be the reason for slow execution of INSERT and UPDATE in a job?
I'm getting this message on a replication distribution task after a successful SYNC task. The exact error message is: " Unexpected EOF encountered in BCP data-file.Failed while bulk copying into '<table name>' " I'm running SQL 6.5 Sp4. I tried recreating the article, resyncing and redistributing, with no luck. Is there anything I can do to fix this? Any help would be appreciated
I have lost the reference but I read somewhere that when running DBCC DBREINDEX against a clustered index, all the secondary indexes on the table are automatically re-indexed as well. I did a test of this on a small table and it seemed to confirm this. However, now I've put this into practice, I am finding that it doesn't seem to work this way. I noticed that having run DBCC DBREINDEX against a table's clustered index (DBCC DBREINDEX ('tablename', 'clusteredIndexName', fill_factor)), the secondary indexes were not automatically re-indexed - as born out by the fact that they remained badly fragmented.
First of all, do the dba's who read this beleive it is correct that DBCC DBREINDEX run against a clustered index will automatically rebuild the secondary indexes too? If so, why wouldn't it work in all cases?
Normally, after I use DBCC DBREINDEX, I can be sure that Scan Density on a clustered or non-clustered index is very good - eg. 99% or 100%. However, I have one database where there are a number of indexes that are not showing any improvement in Scan Density after running DBCC DBREINDEX. In on case, a clustered index, I run it on two days in succession and Scan Density actually go worse! Can anyone give me a reason for this? Can anyone suggest how to fix it?
Something weird happened tonight.. here's the deal. I have two databases exactly the same.. one is for dev, one's for live data.
Anyway, the live DB had an outdated view.. so I exported from the dev DB to the live.. just copying that one view over (using "copy objects" in the export DTS wizard). Very oddly, it actually copied over the data in the tables referenced by the view! Not good, cuz I told my coworkers I'd leave the data in the tables alone :( How do I copy a view over, but just the view definition, and NOT the actual table data??
