Capturing Mysterious Truncation/deletion Of A Table
I have a dts which creates a table which is utilized on my local
intranet. The DTS runs without error and the table is
created/populated/transfered to the appropriate db. Then it appears
that there is an action on this table which truncates it. I have been
unable to determine the culprit. Can I create a trigger that will
capture truncation? I have tried to create a trigger to capture this
information but none that I attempt seem to work on capturing a
truncation or a drop table and re-create.
Any help would be greatly appreciated.
MT.
View Complete Forum Thread with Replies
Related Forum Messages:
Capturing Record Count For A Table In Oracle And Saving It In A Table In SQL Server
I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table. I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases: Imports System.Data.SqlClient Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection. any idea? thanks
View Replies !
Mysterious ATOTransfer.log During Imports && Table Copying
Perhaps this is an Access thing, or perhaps it is sql server, I'm not sure. I'm using MSDE2000 & Access 2003 on Win2k. I've created a .adp database. The WIERD thing is, that whenever I import a csv file, or copy & paste a table in the database, a file named "ATOTransfer.log" appears on my desktop. I've searched google, support.microsoft.com and msdn.microsoft.com for this filename and some of the contents of the file, but there's litterally NO search results anywhere. I can't believe no one knows anything about this; any ideas? Here's the contents of the file when I did a copy & paste: Initialize servers ... Scripting Table : tblLockBoxTrans_Backup Creating Primary Key for table tblLockBoxTrans_Backup Bulk Copying data to table tblLockBoxTrans_Backup 2000 rows copied 3000 rows copied 4000 rows copied 4049 rows copied BCP batch imported. 2000 rows copied 3000 rows copied 4000 rows copied 4049 rows copied
View Replies !
Table Deletion Error
hi i am using sql server 2005 express edition , with asp.net i am trying to delete a table programmatically a button on a form , if the client clicked it , then a table should be dropped . but always i get an error message , that says "cannot drop table <table name> , becaust it does not exist or you do not have premissions to do that" could any body help plz thax ghassan
View Replies !
Deletion Problem In Sql Table
i am using this statement for deleting a single row in sql table. "DELETE FROM Random WHERE NewID= '" & strwinner & "'" where "strwinner" is the variable which contains the row to be deleted. the problem is that when i check the table in sql the row which was supposed to be deleted is sitll there.it does not give me any error statement or something. iam executing this statement by using ExecuteNonQuery in my .aspx page. please help
View Replies !
Deletion Of Parent And Child Table
how can we delete parent table as well as child table using a single query applied on parent table, can someone please help me onn this topic? it will be very nice of you guys. Rahul Arora 07 Batch NCCE Israna, ###################### IMPOSSIBLE = I+M+POSSIBLE
View Replies !
Data Truncation Does Not Trigger Truncation Error - Why?
Hi, I have a data file that has numeric data that looks like: 1.123456 And this column is defined as a DT_NUMERIC(18.6) in the flat file conn mgr. As an experiment, I changed the destination column to a NUMERIC(18,0) - hoping that this would throw a truncation error at the flat file task level (where I have Truncation on all columns set to "fail component"). Not a peep. It loaded the data into the table, chopping off the 6 digits after the decimal point. You would THINK that this would cause an error, but no. Why is this? The flat file task complains about all kinds of things, but this is such a gross error, you would think it would catch it! Thanks
View Replies !
Data Deletion On MS Access Table Hangs
Hello, I have not been able to locate information on the following problem. The first step I have in a packge (Execute SQL Command) is to delete the data from an MS Access database table. The package hangs at this step after all validation is complete. In the package, once the table data is deleted, it is repopulated in a later step. The deletion step and the repopulation step use the same connection manager. There is no information in the log about an error. At the time the package ran, there was a lock file on the database with about six users connected. I'm not sure what version of Access the database was created in, but I have 2003 on my machine, and I cannot open the database. Any ideas? Thank you for your help! cdun2
View Replies !
Alter Table Allowing Truncation
Is there a way to tell an ALTER TABLE statement that truncating data from a string or binary column is okay? Meaning, if I have a NVARCHAR(50) column and I want to change it to NVARCHAR(10), any strings over 10 characters can safely be truncated to 10? Or do I have to push the columns though an intermediary first?
View Replies !
Mysterious Indexes
I found several system generated indexes on one of user tables. Does anyone know what are they? It may be related to update statistics, but I can't any information in Books Online. Thanks in advance index_name index_description index_keys ---------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ------------------------ _WA_Sys_document_id_32375140 nonclustered, statistics, auto create located on PRIMARY Document_id _WA_Sys_User_PKI_Id_32375140 nonclustered, statistics, auto create located on PRIMARY User_PKI_id _WA_Sys_access_type_32375140 nonclustered, statistics, auto create located on PRIMARY Access_Type _WA_Sys_ApplID_32375140 nonclustered, statistics, auto create located on PRIMARY ApplID
View Replies !
Mysterious Changes Of Data
I have a simple invoice, inventory, billing program that is doing something strange but I can't track it down. I can't figure out what triggers it but occasionally when I add an invoice to a customer all the other invoices are added to the last customer updated. My update statement on the invoice table is UPDATE Invoices SET Date = @Date, InvoiceTotal = @InvoiceTotal, SubTotal = @SubTotal, Tax = @Tax, CustomerID = @CustomerID WHERE (CustomerID = @CustomerID) The only triggers on the table are to update the qty in Inventory and another trigger that fires when a payment is entered. Any Ideas?
View Replies !
Mysterious INSTEAD OF TRIGGERS
I tried the following, 1. Created a INSTEAD OF TRIGGER for INSERT 2. In the trigger, I have written a sql which inserts data in the same table 3. When I do the INSERT on the table the data in the trigger gets inserted 4. I was sort of expecting a recursive loop Is this the normal behavior? ------------------------ I think, therefore I am - Rene Descartes
View Replies !
Mysterious Error With SQLBulkCopy
I have encountered a very frustrating situation when trying to use SQLBulkCopy. I have two excel files that I am trying to import into two tables in an MSSQL Server 2005 Express DB. One excel file has 5,000 rows, while the other file has 500,000 rows.I was able to import the smaller file successfully using this vb.net code: Protected Sub L26ExcelToSQL() 'Declare Variables Dim sSQLTable As String = "Local26Members" Dim sExcelFileName As String = "Full Local 26 List Formatted.xls" Dim sWorkbook As String = "[Sheet1$]" 'Create connection strings Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=D:hostingmemberwolsite1l26voterreg" & sExcelFileName & ";" & _ "Extended Properties=""Excel 8.0;HDR=YES""" Dim sSqlConnectionString As String = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString.ToString 'Execute a query to erase any previous data from our destination table Dim sClearSQL = "DELETE FROM " & sSQLTable Dim SqlConn As SqlConnection = New SqlConnection(sSqlConnectionString) Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn) SqlConn.Open() SqlCmd.ExecuteNonQuery() SqlConn.Close() 'Series of commands to bulk copy data from the excel file into our SQL table Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString) Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn) OleDbConn.Open() Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader() Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSqlConnectionString) bulkCopy.DestinationTableName = sSQLTable bulkCopy.WriteToServer(dr) OleDbConn.Close() End Sub However, when I tried to import the 500,000 row excel file, I got the following error: Server Error in '/L26' Application. A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) 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: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) Source Error: Line 438:Line 439: bulkCopy.DestinationTableName = sSQLTableLine 440: bulkCopy.WriteToServer(dr)Line 441:Line 442: OleDbConn.Close() Source File: d:hostingmemberwolsite1L26DuesDefault2.aspx.vb Line: 440 Stack Trace: [SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) +556 System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) +164 System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) +34 System.Data.SqlClient.TdsParserStateObject.ReadBuffer() +44 System.Data.SqlClient.TdsParserStateObject.ReadByte() +17 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +79 System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() +1336 System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) +916 System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) +151 _Default.CSVToSQL() in d:hostingmemberwolsite1L26DuesDefault2.aspx.vb:440 _Default.ButtonTest3_Click(Object sender, EventArgs e) in d:hostingmemberwolsite1L26DuesDefault2.aspx.vb:905 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746 Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433 After I received this error message, I tried viewing my database through the MSSQL Control Panel utilized by my hosting provider (WebHost4Life). However, I was unable to connect to the database and received this error: ___________________Microsoft OLE DB Provider for SQL Server error '80040e14' Database 1496 cannot be autostarted during server shutdown or startup. /getDBinfo.asp, line 29 _____________________ Now here is the most frustrating/mysterious part. I figured that maybe the error message were a result of the large size of the second excel file, so just for testing purposes, I created a new table in my MSSQL database. The table just has two fields, both set to varchar(50). I then created a test excel file, that had one row with the word "test" in the first and second column. When I tried using the code above to import the test excel data into the test table, I got the same exact error as I did with the 500,000 row file!Please help, I'm really stumped and I am not sure when I am having so much trouble replicating the success I had the 5,000 row file. Any suggestions are much apprecaited. -Bryan
View Replies !
Mysterious Date Problem
Is there a way to either set Sql Server 2000 or ASP.net datetime fields to a standard format. The problem is that I am passing correct datetime fields using stored procedures and keep getting "Cannot convert datetime into string". It seems to me that many other developers are having that same problem. I tried alot of different methods and still have the same problem. I'm using c# and I never had a problem with datetime fields when I was using vb.net. The problem is that SQl Server is returning datetime formats that are not compatible with c#. I have code that works in other projects but when I try to use that same code I get that conversion error. How do I set the datetime in SQl Server and ASP.Net when I run queries so that the datetime output is in mm/dd/yyyy?
View Replies !
Mysterious Query Problem?
Hey all,strange problem here... query #1 displays 357 records correctly and allis well. However, when placed within query #2 as a subquery, it updatesevery single record in the lta table, what's going on here? anythoughts?1.) select *from LTA INNER JOIN new_listON lta.voy = new_list.voy ANDlta.poe = new_list.poe2.)update ltaset lta.LL_RCVD = 'N'where exists (select *from LTA INNER JOIN new_listON lta.voy = new_list.voy ANDlta.poe = new_list.poe)
View Replies !
Mysterious Rowsize Error
Hi! I get this mysterious error while running an update. SQLServer tells me that my update row is to big to fit in a row. Then I check the size of the row and and it is like 2000 bytes so it should fit without any problem at all. The really strange about it that when comments a field it works just fine. That field is always null. Could that be the problem. Anyone who have ever had a similar problem or so?
View Replies !
Mysterious Appearing Indexes
Second post for the day. I've just noticed a few indexes in our tables that have are named like such: hind_c_207_1. They seem to have been put in automatically, and are messing with our index structure. Anyone know where these come from and how I can stop them? Any help would be great.
View Replies !
Mysterious Backups Happening
I'm no guru but this one has me stumped!! Each day there is a backup occurring of a database but nothing is scheduled either as a Job or under the database backup scheduling option. Any suggestions greatly appreciated. Thanks.
View Replies !
Mysterious Connectivity Issue
I am facing a pretty strange issue running a query in SQL Server through an ODBC client. Here is what is happening - 1. I am able to connect to SQL server. The connect entry is present in ODBC log as well as SQL Server trace. Running sp_who2 active indicates the connection to be active. No problem here. 2. At this point, running a simple query (something that returns in less than 10 mts or so) comes back with the result set. No problem here. 3. However, when a complex query (that it does not return anything even after 10 mts) is run, a run of sp_who2 active or dbcc inputbuffer commands indicate that the connection created initially is not there any longer. SQL Server trace indicates a logout event for the connection. However, ODBC log indicates that the connection is still active in SQL Server and the ODBC client continues to wait for the results of the query for hours until I kill it. I use ODBC 4.2 client on AIX 5.2 and connect to SQL Server 2005. One of the theories I have is that the AIX server does not respond to more than a certain number of keep alive messages resulting in SQL Server closing the connection gracefully thinking that the client is no longer available. However, even after changing the keep alive setting for TCP/IP in Network Configuration Menu to 4 hrs, the same behaviour continues. Can someone throw some light as to what might be happening? Thanks.
View Replies !
Mysterious Loss Of Data From Mssql2k
Hi All,I'm trying to track down a mysterious problem we're experiencing inwhich updates and inserts to tables in our mssql2k server appear to be'disappearing.'To explain our situation:We have a web page (written in ASP, if that's relevant) on which weaccept enrollment information.When that page is submitted, the form data is passed to a storedprocedure on our mssql2k server, which performs several operations,all of which are wrapped in a transaction.In particular, the stored procedure performs an update operation on arecord in one table (i'll call it TableA) and an insert into anothertable (TableB).If the procedure encounters a problem (ie after each update / insertoperation in the procedure we test for IF @@Error<>0) it performs arollback, performs a select similar to the one immediately below, andthen RETURNs.SELECT '1' as error, 'Unable to update TableA' as errormsgIf the procedure doesn't fail any of the @@Error tests, thetransaction is committed, and a membership number is SELECTed to bereturned.SELECT '0' as error, @memnum as membershipnumberThe @memnum variable is populated within the transaction.Back in the ASP page we test both for the proc returning an emptyrecordset, or for it passing an explicit value in the error field, andpush the page to an error page if either of these conditions are met.If, on the other hand, none of these conditions are met, and themembershipnumber field in the recordset is populated with a validmembership number, we push to a confirmation page.This confirmation page receives the membership number in a sessionvariable, performs a SELECT against TableB (the table that receivedthe insert during the proc) using that membership number in the WHEREclause, and the resultant recordset is used to populate theconfirmation details on that page. That recordset is also then used topopulate the details of a confirmation email, which is automaticallysent by the confirmation page.And now here's our problem: we've become aware of a handfull of peoplewho have gone through the enrollment process, have received theconfirmation email containing the information they supplied asexpected, but the data appears to be entirely missing from our tables.By that I mean that the record in TableA does not appear to have beenupdated (under normal circumstances that record should have hadseveral flags set, and several other fields updated with informationsupplied by the person enrolling), and the record in TableB does notappear to have been inserted.In essence, looking at our tables, it *feels* like the transaction inthe stored procedure for that particular enrollment hit a problem andwas rolled back. However, the evidence that we have in the form of theconfirmation email argues strongly that the data must have existed inour tables (particularly in TableB), if only for an unknown period oftime.We're kind of at our wit's end to work out what is going wrong withthese enrollments. From my understanding of transactions (and I couldwell be wrong) any changes to data (ie updates, inserts etc) containedwithin are essentially 'invisible' to any other operation (ie theSELECT that happens in the confirmation page) until the transaction iscommitted, implying that the effect of the update and insert shouldhave been 'permanently' successful if no error code is received and ifa valid membership number was returned. I ask, because someone in ourteam has suggested that maybe the operations in the transaction'lasted long enough' in the tables to have been visible for the SELECTon the confirmation page to have worked, but were then subsequentlyrolled back, explaining why the confirmation email is appropriatelypopulated and why the data then appears to be missing. However, as Isaid, this doesn't match my understanding of how transactions behave.Sorry for the length of this post, but I felt it was best to explainthis as best as I could.Does anyone have any advice they can give us on this situation? ie,are there any known problems with operations in transactions 'bleedingover' into tables, but then being rolled back at some later point?Does anyone have any thoughts or suggestions on how we can furtherdiagnose this issue?Truly, any help will be immensely appreciated...Thanks in advance,M Wells
View Replies !
Mysterious Corruption Of Stored Procedures
Hi everyone,It looks like a mystery, but I hope there should be some explanation tothe issue I experience. Once in a blue moon a random stored procedurestops working the way it was designed. The stored procedure code looksunchanged. Recompiling, altering the code do not help. It looks like itis simply does not execute some part of it or does not run at all.However it returns no errors.One time a procedure entered into infinite loop and almost hang thewhole server.When I copy procedure code and save it under different name, it worksas designed. But nothing helps with existing procedure. The only wayhow to fix it is to completely drop and recreate.The problem is, that you usually have to do it in the middle of thebusiness day after you spent few hours trying to realize what wentwrong before you realize that you got another mysterious corruption. Ofcause I have no clue of how to detect such things in advance and toprevent them from occurring in the future.I can guarantee that the SQL code in those procedures was absolutelybug free, fully tested and was working fine for a long time.For the first time I thought that internal compiled code might corrupt.In this case altering or recompiling should help.I also thought about execution plan, but it should be also fixed bydoing things above.DBCC checkdb does not find any errorsThe issue never goes away until stored procedure is manually droppedand recreated with the same SQL code.So, I'm asking all if someone experienced something similar and canexplain how to prevent it, please share the knowledge. I wouldappreciate any type of help.Thank you.
View Replies !
Mysterious Access Denied Error
Yesterday afternoon I lost access to my local MSDE 2000 sp3(a?)instance. Usinig NT or SQL authentication I get an 'access denied'error when I try to connect. This happened suddenly with no changesto the server on my part. I've tinkered with the Client NetworkUtility settings, from Multiprotocol only, every combination of Multiprotocol, TCP/IP, and named pipes. I've tried connecting with mynetwork machine name, using (local), and using my IP address (both127.0.0.1 and my network IP). I've tried connecting from QueryAnalyzer on other computers. Of course I've rebooted as well, all tono avail.I know that there were network changes being made, but my domain login(which I normally use to connec to my SQL Server as a sysadmin) isstill in the local administrators group on my machine and I haven'tnoticed any problems accessing anything else.Any ideas?
View Replies !
Mysterious REDMOND Account In MSDB
Had something interesting happen just now that I thought I'd share... On a box running sql7/sp3, try running the following: select * from msdb..sysdbmaintplans Note who the owner of the job is. Please also note there are no respective entries in Enterprise Mangler to coincide with the entry in the system table. So... Does this mean we know what REDMOND account is used to build SQL? I kinda think someone should have caught this... Anyone know anything about this?
View Replies !
Mysterious Gaps On PDFs And Prints
Hello, I have a report that looks fine in preview, but when put to PDF or printed contains gaps at the right and bottom. Here is a picture of the problem: http://northeasttigers.webng.com/pdfproblem.jpg My tables are the same width as the body, 15.5cm, and the report width is 21cm. Also adding the bottom table's top location attribute to it's height gives the same height as the body. Any help much appreciated. Greg
View Replies !
Sql Server 2005 Mysterious Problem
Hi! When executing some "big" update on a Sql Server 2005 standard edition SP1 (not clustered), sometimes it stops in a middle of work with this message: The specified network name is no longer available. [SQLSTATE 08S01] (Error 64) Communication link failure [SQLSTATE 08S01] (Error 64) I was reading some articles but they were all about clustered servers. Somewhere I read that it could be a memory problem, so we put 3G RAM and separate 20G paging partition beside the other data and backup partitions. The problem stays... Funny thing is that it can sometimes happen but sometimes not with the same conditions!? The server is Windows 2003 Server standard edition with SP1. Any sugestion...help?!
View Replies !
Mysterious Login Error Message
I get the following error messages in the sql server error log Source Logon Message Error: 18456, Severity: 14, State: 11. and Source Logon Message Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. [CLIENT: 185.23.11.33] The scenario is: We set up log-shipping (LS) between a clustered sql server system (source server) and a stand-alone sql server box (target server). (SQL Server 2K5 EE + SP1), and LS goes very well, but on the target server, we found the above-mentioned error messages. BTW: the two servers are in the same domain. Did I miss something in configuration? Thanks in advance for your help.. Jeff
View Replies !
More On Mysterious Data Loss In Mssql2k - A Possible Solution?
Hi All,Further to my previous long-winded question about a situation in whichwe appear to be mysteriously losing data from our mssql2k server.We discovered an update statement, in the stored procedure we believeis at fault, after which no error check was being performed.Under certain conditions, this update is fired against the same recordin the same table as the immediately preceding update statement withinthe transaction. We are now suspecting that under some circumstances,these two updates get into a locking conflict that is eventuallyforcing the transaction to be rolled back.However, I'm still left with three questions.1) Where an update in a transaction gets locked, and an error isn'ttested immediately afterwards (ie no 'IF @@Error<>0' test is made),would the transaction proceed as normal?2) Most critically, would statements in the stored procedure thatappear after the COMMIT TRAN statement also be executed, even if anunresolved lock existed within the transaction?3) Assuming that (2) does happen, would a SELECT made on anotherconnection with a 'WITH(NOLOCK)' locking hint be able to see thechanges made in the locked transaction even if the server is set toREAD COMMITTED, and the SELECT takes place some time after the COMMITTRAN is issued? More to the point, given (2), how long would thelocked transaction survive before being rolled back after the COMMITTRAN has been issued? Is it possible that the COMMIT TRAN takes place,the transaction is flagged for potential rollback while a lockresolution is attempted, the stored procedure exists as thougheverything was fine, a subsequent SELECT (ie performed as one of thenext operations in the same application) using WITH(NOLOCK) 'sees' thechanges made by the transaction, reinforcing the impression that thetransaction succeeded, and then at some point thereafter the lock isdetermined to be unresolvable and the transaction is rolled back,making it seem as though the data disappeared, even though it had beenSELECTable via a different connection to the server?Thanks, by the way, to Simon and Erland for your advice on my previousquestions about this problem.Much warmth,M Wells
View Replies !
Ntext Vs. Nvarchar, Mysterious Data In The Database
I hope this is the right forum for this question, my apologies in advance if it isn't.... We have a web based CGI product (written in C++ VS 6) that uses ODBC and takes text from a submitted web page and stores it in a SQL Server table in a field of type "ntext". The user in question is copying and pasting this text from an MS Word 2003 document. After the initial save our app errors out trying to access the table it just wrote to, and when we look in the table we see that up to **200 carriage returns** have been mysteriously inserted into the ntext field!! (Our product has been out in the field with no such problem for several years, so we are thinking it's related to something specific the customer is doing - perhaps with using MS Word for the source text.) We have tried but cannot duplicate the problem, but the customer sees it with each attempt to modify the table in question. The only thing that I see out of the ordinary is that the field in question is of type "ntext" - which supports unicode, instead of nvarchar. Does any of this ring a bell for anybody? I'm thinking of changing the field type to nvarchar to see if that solves the problem. Thanks, Steve Bradbery
View Replies !
Using MAX, Then Capturing All Except Max
I need to first capture the max version associated with a file name here is the meat SELECT fileName, f.folderName, masterID, iterationId, iterationNumber version FROM fileIteration fi JOIN iteration i on (i.iterationID = fi.fileIterationID) JOIN vw_folderFileLink fl on (fl.fileMasterID = i.masterID) JOIN folder f on (f.folderID = fl.folderID) WHERE fileName LIKE '429020652.idw' --GROUP BY masterID, fi.FileName, f.FolderName, fileName, i.iterationID ORDER BY masterID, version DESC --*******end code ********-- that returns these results: 429020652.idwSubPumps71575217 429020652.idwSubPumps71575116 429020652.idwSubPumps71574975 429020652.idwSubPumps71574654 429020652.idwSubPumps71573983 429020652.idwSubPumps71573142 429020652.idwSubPumps71573131 First I need help just getting that top row but then I need to get all expect the top row selected.. I've tryed using MAX in several ways but I'm not getting what i want here :/ any help is appreciated
View Replies !
Capturing A Variable In ASP.NET
Hi all, I am new to .NET, after many years with classic ASP I am struggling a little with something that I am sure is really easy to do. Basically what I want to do, is execute an SQL statement, that will return a single value. I then need to store this value as a variable, so that I can pass it into another query later. It seems easy to output the record, but how do I store it as a variable !! This is my code to connect and run the SQL... all I need to do as retreive the value, and put it aganist a variable.... Function higher_manager_ein() As System.Data.IDataReader Dim connectionString As String = "server='myserver'; user id='userid'; password='pwd'; database='DB'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT distinct MEASURE FROM [CCC_MEASURE] where ein = '" & request("man_ein2") & "'" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection dbConnection.Open Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) Return dataReader End Function
View Replies !
Capturing Server Name?
Hi y'all....long time, I know... I have what OUGHT to be a simple question...or so I think (which really hurts, mind you...) I am writing a stored proc that will reside on several different databases and be used to write a row to a "wait table" that is used to control processing in the various databases... Essentially, the stored proc already exists, and writes what is essentially a note (or process semaphore) that says "Hey, Process XYZ is waiting on the completion of process 123" Problem is...process XYZ has the same name on a number of different servers, so I have to come up with a way to differentiate the process name that's waiting on a job on a single server (in other words, 5 or more XYZ's can be waiting on a single job on a single server in the network, and the wait table resides on that single server). So...my thought (again, think pain) is that I will put a process name of "SERVER.XYZ" into the wait table. The SP I will use to write the "waiting on" semaphore is a common one, so - long story short(er) I need a way to capture the name of the current server (like db_name(), only server_name() - or something like it). Any suggestions? Thanks in advance... Paul
View Replies !
Capturing Warnings?
Hi all, I know that it's fairly trivial to capture SQL error conditions (i.e. constraint violations, etc) by using code similar to: <snip> SELECT @m_error = @@Error IF @m_error <> 0 GOTO ErrorHandler SET @TraceMsg = 'Rebuilt new index for ' + CONVERT(varchar(10), @CreateDate, 101) + ' (' + @RowCount + ' component stocks added)' EXECUTE [dbo].[tracelog] 1, 'Index', 'sp_Rebuild_CurrentList', @TraceMsg RETURN ErrorHandler: DECLARE @func VARCHAR(50) SELECT @func = Object_name(@@Procid) EXECUTE [dbo].[ErrorHandler] @m_error, 'Index sp', @func GO But what about warnings? I would like to trap for warnings such as Warning: Null value is eliminated by an aggregate or other SET operation. and write when this happens to a trace file. Of course, another alternative would be to catch the problems as they are added to the database, but I don't have control over the input database, so would have to check it prior to using it, and that's a bit more effort than necessary for this particular project - since, however, I have run across the desire to capture warnings in the past, I must pose the question ;) Any ideas? Anyone have a need to capture warnings of this type before?
View Replies !
Capturing Queries
Is there any way(other than Profiler) to capture a full SQL Query statment. I've used dbcc inputbuffer and the current activity screen, both return 255 characters. I'd like to capture the whole query. Thanks Pete Karhatsu
View Replies !
Textbox - Number Format Leads To Mysterious Leading Bracket
A report is picking up some values from the body and displaying them in text boxes within the Page Header, via the ReportItems collection. The text boxes within the body have their format specified as #,###; (#,###) - so displaying negative values within brackets. If the following value is set for the Page Header text box: ="My Value" & " " & ReportItems!variance.Value the value displayed is, for example: (My Value (1,123 Hence the requested trailing bracket has been swapped to become a leading bracket. Whatever I've tried I cannot get the bracket in the correct place. Am I missing something obvious or is this a bug?
View Replies !
Deletion
Hi all, I have a table in xyz database and there is no column in table like creation_date or modified_date. The problem is I want to delete records which has been added in the table before 1st jan 2007. The size of table is 85 GB Immediate help would be appriciable. Regards, Frozen
View Replies !
Capturing Value Of Identity Column For Use Later?
This doesn't work because the first INSERT is creating multiplerecords for multiple projects. @@IDENTITY, then, contains the Identitycolumn value for the last tblWeekReportedLine record inserted.Consequently, all the hours records are then associated withthat last value.The source work table, #EstimateLines, is a pivoted representationwith a Begin/End date and some Hours for each of six periods - a lineper project that gets pushed up to the DB by some VB code.Definition below the sample coding.The "@WeekReportedID" value was successfully captured whenprevious coding inserted six records into that table: one foreach date range (i.e. column in the UI screen)Sounds like I'm approaching this wrong.Suggestions on the right way to go about it?---------------------INSERT INTO tblWeekReportedLine(WeekReportedID,RelativeLineNumber,ProjectID)SELECT@WeekReportedID1,#EstimateLines.RelativeLineNumber,#EstimateLines.ProjectIDFROM#EstimateLines;SET@CurWeekReportedLineID = @@IDENTITY;INSERT INTO tblHour(WeekReportedID,WeekReportedLineID,HoursDate,Hours,HoursTypeID,HoursType,TaxCodeID,TaxCode)SELECT@WeekReportedID1,@CurWeekReportedLineID,@BeginDate1,Estimate1,@DummyHoursTypeID,@DummyHoursType,@DummyTaxCodeID,@DummyTaxCodeFROM#EstimateLines;------------------------The #Temp table create via VB:------------------------1030 .CommandText = "CREATE TABLE #EstimateLines " & _" ( " & _" PersonID int, " & _" ProjectID int, " & _" RelativeLineNumber int, " & _" Available1 decimal(5,2) Default 0, Estimate1decimal(5,2) Default 0, BeginDate1 DateTime, EndDate1 DateTime, " & _" Available2 decimal(5,2) Default 0, Estimate2decimal(5,2) Default 0, BeginDate2 DateTime, EndDate2 DateTime, " & _" Available3 decimal(5,2) Default 0, Estimate3decimal(5,2) Default 0, BeginDate3 DateTime, EndDate3 DateTime, " & _" Available4 decimal(5,2) Default 0, Estimate4decimal(5,2) Default 0, BeginDate4 DateTime, EndDate4 DateTime, " & _" Available5 decimal(5,2) Default 0, Estimate5decimal(5,2) Default 0, BeginDate5 DateTime, EndDate5 DateTime, " & _" Available6 decimal(5,2) Default 0, Estimate6decimal(5,2) Default 0, BeginDate6 DateTime, EndDate6 DateTime, " & _" );"--------------------------PeteCresswell
View Replies !
Capturing Events In A DataGrid
I'm trying to add functionality to a VB 6 application allowingcustomer service to add a customer number to a new customer.Customers are added to the database by sales personnel, and aprospective customer may have multiple rows due to projected ordersfor multiple products. Customer numbers are assigned when a newcustomer makes their first order.I'm using a DataGrid connected to an ADO Data Control. The datacontrol is connected to a view in SQL Server using the 'distinct'directive (I know it's not updatable) to show only one line per newcustomer. What I wish to do is capture the update event (probablythrough the FieldChangeComplete routine of the data control), manuallyassign the newly entered customer number to all appropriate rows inthe database, and cancel the update event with no notifications.I'm having two problems:1. I can't capture the newly entered customer number. The Textproperty of the DataGrid returns the old value of the cell instead ofthe newly entered value. How do I get the edited value?2. Even though I set adStatus = adStatusCancel in theFieldChangeComplete routine, I get a Microsoft DataGrid Control dialogstating 'Operation was Canceled'. How do I avoid this notification?
View Replies !
Capturing ID When Executing An INSERT
I have a bit of code that executes an INSERT statement to add a record to an existing table. This table of course has an automatically incrementing ID field, and I'd like to somehow have my INSERT statement return the value of that ID field so that I can automatically show the user the record they've added. Is there a clean way to do this?
View Replies !
Capturing Row Count Of Transformation
What I'd like to do is detect the number of records processed by a data transformation in an Active Script Task set to execute on success of the transformation so that I can create some logging that's better than what Agent does. Anyone know how?
View Replies !
Problem In Capturing The String
Hello! this is prasad here. i have got a table by name MYTABLEZEN WITH 2 COLUMNS(SNAME VARCHAR(20),SKILLS VARCHAR(1000)) and the records in the table are as follows sname skillls --------- ----------------- xyz vb,oracle,asp abc vb,asp,c,c++ pqr cobol,oracle,vb using a select query i am able to the records (select name from mytablezen where skills like '%vb%') as i am a fresher i request you to help me by sending a procedure by using that procedure i can select name depending on the skills (they can be any skill or skills) please send the procedure as soon as possible. regards, prasad.
View Replies !
Capturing Execution Results
Hi All When running an SSIS Package from BIDS, we get to see the "Progress" tab which explains us the progress of the SSIS Package. During production, is there any way to capture this log. I am interested in using this as a log file for each run of my SSIS package. Thanks, S Suresh
View Replies !
Capturing Delta In SQL 2005?
I am not having luck with finding a methodology that will help me capturing delta on sql server 2005? I am researching development of a data store that will be refreshed periodically. any ideas? thanks
View Replies !
Capturing Component Throughput
[Microsoft follow-up] I've just been reading this thread by a guy asking about capturing the throughput of a dataflow. I suggested that there is no real notion of capturing throughput of a dataflow but I believe there IS a notion of capturing the throughput of a component or an execution tree. I believe all the information that one would need to capture the throughput of a component (apart from the name of the component that is) is available in the OnPipelineRowsSent event. If there were a OnPipelineRowsSent eventhandler and the OnPipelineRowsSent event contained the name of the component then I think we would be able to capture the throughput of a component. So, some questions: Why is there no eventhandler for the OnPipelineRowsSent event? Can the name of the component be added to the information in the OnPipelineRowsSent event? Following on from this... I once had a conversation here with Kirk Haselden about capturing pipeline throughput. He thought it was a good idea and suggested I raised a DCR for it which I did but that was in the old pre-Connect days and it seems as though that DCR (like SO many other things) didn't make it across to Connect. So, some more questions: Can you find any Connect DCRs relating to capturing throughput? I've found this: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=152162 that I raised 18 months ago but which hasn't even had a single comment from anyone at Microsoft. Do you think that capturing throughput would be useful? I can foresee huge advantages by capturing this in the debugger. (Note that Informatica does this and has done for years. It has a very nice GUI that shows the throughput of each destination in the mapplet.) I'd welcome any thoughts around this. Its a big ask and it fits in very nicely with my constant, nay INCESSENT, requests for debugging enhancements so maybe this is one for Darvey to have a read of??? Thanks Jamie
View Replies !
Capturing One Record In A Series
My question is this..... I inherited an application that creates reports and each report pulls data from a SQL database. The reports are for each School district in the state and pull the number of students that have Asthma conditions, simple enough. However what happens is this... Each initial report is saved with a code of 2 for initial. As long as there aren't and revisions I am fine. If a school makes a revision (Code of 4) I now have two records in the database and I need the one from the revision (Code 4). Not every record will have a revision, and sometimes there may be multiple revisions. How can I code my stored procedure to go through the database and check to see if there is a code of 4 or multiple codes of 4 and if so grab that data, if not grab the initial data? Below is the code currently being used. Frank CREATE PROCEDURE dbo.usp_Rpt_Asthma (@WhereClause VARCHAR(400)) AS SET NOCOUNT ON DECLARE @WhereClause_STR VARCHAR(400) SELECT @WhereClause_STR = CONVERT(VARCHAR(400),@WhereClause) Exec ('Select D.Code, C.Code, InstCtgyFK, C.[Description] as County, D.DistrictName, A.InstName, Sum(K4+K+G1+G2+G3+G4+G5+G6+G7+G8+G9+G10+G11+G12+UnGrSpEd+Other) as TotalADM, Asthma, Asthmatics FROM dbo.tblAnnualReports AR Inner Join dbo.tblAddresses A on A.InstitutionFK = AR.InstitutionFK Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblInstitution] I on A.InstitutionFK = InstitutionPK Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblActiveInstCtgy] AIC on A.InstitutionFK = AIC.InstitutionFK Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblCounties (PA Standard)] C on C.Code = I.DOHCountyCode Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblCommunityHealthDistricts] D on D.Code = C.CommunityHealthDistrictCode Inner Join dbo.tblAverageDailyMemberships ADM on AR.[ID] = ADM.AnnualReportID Inner Join dbo.tblChronicConditionsInjuries CC on AR.[ID] = CC.AnnualReportID Inner Join dbo.tblMedicationAdministration MA on AR.[ID] = MA.AnnualReportID Where (A.StartDate <= AR.DOHDateProcessed and (A.EndDate >= AR.DOHDateProcessed or A.EndDate is Null or A.EndDate = ''1/1/1900'')) and ReportTypeCode = 2 and IndOrdStandOrd = ''I'' ' + @WhereClause_STR + ' Group By D.Code, C.Code, InstCtgyFK, C.[Description], D.DistrictName, A.InstName, Asthma, Asthmatics ') GO
View Replies !
Capturing Filesizes For All Databases
There have been a lot of posts recently in this forum and others so I figured I'd record the script here and then point others to it in the future. If you are interested in retaining the information for posterity to chart growth in your database and then showing the numbers at say your next review to show how much they need you, you can run the initial script that will create the table. Once the table is in place you can run the script to get the data from all databases, and then query the table. If you don't need a history of the sizes and just want to run the query one time then just run the last line of the script "EXEC": TABLE CREATION:Create table DatabaseSizes (dbname varchar(255), fileid int, filename varchar(255), name varchar(255), size_mb int, reporting_date datetime) POPULATE THE TABLE:insert into DatabaseSizes(dbname, fileid, filename, name, size_mb, reporting_date) exec master.dbo.sp_MSforeachdb 'use ? select db_name() as dbname, fileid, filename, name, size/128, getdate() from sysfiles' Hope it helps, Dalton Blessings aren't so much a matter of "if they come" but "are you noticing them."
View Replies !
Restrict Deletion
What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query? Thank you in advance.
View Replies !
Deletion Of Duplicate Row
Hi Everyone,I have a table in which their is record which is exactly same.I want to delete all the duplicate keeping ony 1 record in a table.ExampleTable AEmpid currentmonth PreviousmonthSupplimentarydays basic158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00I want to delete 2 rows of above table.How can I achieve that.Any suggestion how can i do that.Thank you in advanceRichard
View Replies !
Replication Without Deletion
Hello there, We are currently setting up out production server to the following requirements: 1. Every month, delete records that haven't been changed in the last 90 days. 2. Replicate insert statements to a backup database which will keep track of all data, and act as an archive/data warehouse. The first step is easy, as it is just a script that checks the date of the last change on each row. However, the second step is a bit more tricky. We tried setting up replication between two test databases, but we ran into the following problem: Whenever old data has been deleted in the production database, the replication agent deletes it in the data warehouse database too. Is it possible to override or disable this, so data is only inserted/updated, and not deleted? No applications using the database deletes records, so database integrity should not be a problem. Thanks for your time, Ulrik Rasmussen
View Replies !
Deletion Problem
It is an option to set deletion without getting logged since I have problem to delete two years historical data and would like to keep this year data on my 80MB rows. Actually I create a new table to get copy one-year data and I truncated the old table. I am wondering if there is other better way to do this task. TIA, Stella Liu
View Replies !
|
TRACKER
