How To Detect If Column Data Changed And Know Prev. And New Value
Jul 20, 2005
I have a need to insert rows into an Audit type table when values
change in certain fields in a table. I thought I could do this via a
trigger. However, on requirement is to include in the audit both the
old and new value.
Is there a "simple" way to do this? I know I could query the table
before the update and compare to what the new value is and react
accordingly.
Just wondering if there is something nifty in Sql Server that I am
missing that could help me with this.
How would I best go about changing a published table's column from smallint to int? I could not find anything about it in BOL or MS.com. I do not think EM/Replication Properties allows the change. I suspect I have to run "Alter Table/Column" on the Publisher and each Subscriber the old-fashioned way. Is that true?
Hello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
Using PowerPivot I created a connection to a view on a SQL Server database. All fields imported correctly.A column has since been added to the view. How do I get this to appear in my PowerPivot?
I have installed SQL server 2005 on Win SERVER 2003, then uninstalled and then re-installed but I have found out that the previous instances are still listed and the the previous collation too (I have changed reg setting and and language option re-installation).
It looks like that removing program does not remove completely every object, probably registry values
Do you know how to perform a good uninstallation removing everything (add/remove program does not work properly as written before) ?
I had an issue that I ran across that I got solved but it was very monotonous. Here is my issue: I have an SQL function that takes in 2 parameters (lookup category and lookup code) and with those parameters is goes out and find the lookup code description. I have this function in about 40 computed columns. It isn€™t the best way to do things but we need it for some legacy stuff. The problem occurs when I need to make a change to that function. I can€™t because it is being reference by all of the tables. So what I have to do is go in to each of the tables and remove the function from the column formula. Then change the function and then finally add that function back to the column.
Here is what I did. I went to the syscolumn table to find all of the computer columns. I went to each of those columns and removed the formula and added it to a text document. Then I changed the function. I then wrote a stored procedure that would add the column back. ( I couldn't find a way to use the ALTER COLUMN to add the function so I had to drop and then add the column )
Here are the possible scenarios that I want to see if they are possible. 1) Somehow €œturn off€? the dependency between the function and the column. That way I can just update the function. OR 2) Programmatically grab the data from the column formula and write that some place and clear out the formula. Then update the function. Then finally take all of the data that was cleared out and write it back to the column.
I am writing a db conversion for a retail grocery chain. This chain uses pricing zones to designate what stores get a certain price
Example: Cheetos Zone A: $2.79 Zone B: $2.89
The pricing data in the tables is listed by zone. However, the new product uses pricing by store.
Zone A contains stores 1,2,4,6,7.... Zone B contains stores 10,11,12,14.....
I need to be able to duplicate the rows in a manner that I can take the row containing a price for Zone A and duplicate it for each store in the zone. I have a table of stores with corresponding zones.
So I'm looking to go from:
Zone UPC Price A 1234500000 2.79 B 1234500000 2.89
I have several tables a varbinary column in a database. They have names like CSB_BLOB or OBJECT_BLOB. Now I am having intermittent success with getting the data out.
For example this query returns readable text from this data.
0x46726F6D3A20226465616E6E6167726.....etc --data as stored in the column
SELECT CAST(CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM OBJECT_BLOB
However this column has the following query results.
0x0001000000FFFFFFFF01000000000000000C....etc. --data as stored in column
--this query returns empty result
SELECT (CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM CSB_STATUS_LOG
--this query returns no change???
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), CSB_BLOB, 2), 2) FROM CSB_STATUS_LOG 0001000000FFFFFFFF01000000000000000C....etc
Obviously there is a difference between the two but I am not educated enough to interpret this difference. What do I need to learn / read so I can look at the data in one of these BLOB columns and know how to convert it to something meaningful?
Something like:
1. Try to cast as varchar to see if it is text. 2. Turn into a byte array and see if it is a jpg 3. Turn into a byte array and see if it is a pdf 4. Convert it to hex and then cast as varchar 5. etc....
I am facing the problem with datatype conversions, the scenario is using derived column transformation for add additional columns and then later i am trying to impliment Slowly changing dimensions(SCM) in my job, while mapping the columns at SCM from Input to Output it gave the error like suppose if i am using the numeric(3,0) at SRC system then i converted it into single byte unsigned at derived column and it recognized at SCD but the job fail while run the package it gave the error as task can not able to conversted given data type to target system dtata type.. ifi am not given the single byte unsigned data type at dervied column at the level of SCD mapping the input to output its not accapring this mapping and return the error as can be convert from system.decimal to system.byte..
Working on partitioning a few large tables. One of the tables included a text column and the €œTEXTIMAGE_ON [PRIMARY]€? clause which would prevent the partitioning of this table. After some research we found that the data was legacy and no longer used. We updated the column on the affected rows to NULLS and altered the column to a VARCHAR(20) When I attempted to run the ALTER TABLE SWITCH I encountered the error Msg 4947, Level 16, State 1, Line 1 ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresultsjoe' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part'. After a lot of grief and testing I determined that the message was bogus and the real issue is that the 'sys.tables' still has €œlob_data_space_id€? with a value of 1 for this table. I created a copy of the table with the text column altered to varchar and one with just the varchar to begin with. After copying data from the original table, I tried to run the alter switch. It failed once again for the text column altered to varchar table, but it worked for the varchar from the start.
Since it appears that this value is causing my issues, is there anyway to update the table in place. I know I can BCP the data out, but that would take too long and would defeat the advantage of using the alter switch method.
BOL States:
The allow updates option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported. This means we cannot update the table manually.
I want to import via DTS to big table only the records that changed last day. can i do it without a time field? because this is a key table for DWH and not a fact table. THX Inon
i have a databse on a server and i just created an image on the database. now i want to just copy the changed data in all tables withing the database not the whole database eveytime.
When attempting to update or delete data using Microsoft Access, this error often occurs. The data can be updated or deleted directly through SQL Server and the problem is not resolved by closing and restarting access. The tables accessed have primary keys.
I got 10 Tables with data in it for 100 Loans. The data can be Good and Bad .....
I had a Update Proc which Updates the 10 tables with the Good data what ever i pass...for this 100 Loans Tha Proc will update the existing data in all tables whether it is Good or Bad Data.
when the updating is completed I want to know what are the Loans that were updated where there is a Bad Data in atleast one Field of the 10tables.
I don't want to check field by field in each table.... if there is a bad data and my proc updated with a Good Data i want to know that Loan.
I am trying to edit an SSIS package due to a change in the schema of one of the source tables. When I open the package in Visual Studio, I see the exclamation mark on the object but before I can make change or go to edit anything in the package, I hear the "dumphhh" sound that indicates an error. There is no dialogue box but the program is now locked up. Then after 10 - 20 seconds the program closes.
How am i supposed to add the new column to the data source metadata if I can't edit the package?
My objective is to extract the source table data from SQL/Oracle or CSV files and load into destination table using CDC mechansim. May I know the steps required to implement in production from development.
isn't there an automatic log of some sort to check and see what exactly was changed by a given SQL command? A stored proc was ran and I need to figure out what exactly it changed in the underlying table.
I need reflecting changes of csv file in oracle DB. Suppose, Â I load single csv file in oracle DB which contains 10 rows. After some time, I have loaded another CSV file which has the modified row of the previously loaded csv file. So, how can I capture the CSV changes and how it is going to get reflected in oracle DB?There is no unique column in csv file to identify particular row.
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
I have a table with Million plus records. Due to Running Totals article, I have been able to calculate the Trial_Balance for all months.
Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I need to be able to do this for multiple account types. So the two datasets that need to be included in logic is actindx and Calendar_Month.
For actindx of 2 and Calendar_Month of 2014-01-01The Trial_Balance_Debit is 19585.46 This would make the Beginning_Balance of actindx 2 and Calendar_Month of 2014-02-01 19585.46
I am trying to do some type of self join, but not sure how to include each actindx number differently.
Table creation and data insert is below.
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[TrialBalance]( [Trial_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
HelloI´m using MS-SQL Server 2k with a custom application connecting to it.I need a quick and simple way to detect any change (insert, update, delete)to my database (not just to a single table). The purpose is to notifysomehow different instances of the application about the data changes,since they should refresh their local query results.Is there any function, stored procedure, system database entry, etc. I canexploit to get the job done? Can you suggest me the most suitable mechanismto implement this?T.I.A.AndreA
I am entering to administration of SS2005 SP1 (Windows 2003) having files mdf, ndf, ldf in C:Program FilesMicrosoft sql serverMSSQLData This dir also has two *.cer files.
Apparently no encryption is used
How can I get known what these *.cer files are for?
Hi,I want to check with VB.net whether a field in a SQL-table is NULL or not.This code doesnot work:If xxx = NULL then<statements>End IfI got the error, that NULL is not supported ?How do I code the check ???Help is appreciated, Gr.
when i run in query analyzer , using "exec SP_TEST" , it work and display result
but when run Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:TEST.TXT -c -Slocalhost -Usa -Ppassword'
it give error "SQLState = S0002, NativeError = 208" and show Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.
Hassle free locating / reporting or deletion of duplicate rows in a SQL table based on the uniqueness of a column or columns that the user provides with a few nifty hands off features. Keywords: delete unique column record records
(This code may also be viewed at http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=576&lngWId=5
/*Description: UTILITY - Locate in MASTER
Syntax: EXEC sp_RemoveDups TableName, DupQualifierFieldNameList, DeleteDups, UniqueColName, CreateIdentColIfNeeded, StoredProcedureResult Only the first two arguments are required For HELP, enter the stored procedures name without any arguments or see the PRINT statements below NO DELETION WILL OCCUR by default - only duplicate recordset returned. To delete records, pass in a 0 for the DeleteDups argument.
Purpose: Allow removal of duplicate rows where 1. We define what fields qualify the duplicate 2. We select the unique rowid or it is detected automatically else no action takes place
Method:Delete by RowID all duplicate rows except the highest RowID (in alpha-sort sequence) of each group of duplicates.
DATEBYCHANGE 09-23-2002FrankOriginal v1.0 09-23-2002FrankChanged the name from sp_RemoveDupsByRowID to sp_RemoveDups 10-8-2002Sean P. O. MacCath-MoranMade @UniqueColName optional Added logic to auto-detect RowGUID and Identity columns Added logic to check for unique value column if no RowGUID or Identity column exists Added logic to create a temporary ID field as a last resort if no unique column could be located Added HELP
*/
CREATE PROCEDURE sp_RemoveDups @TableName as varchar(50) = NULL, @DupQualifierFieldNameList as varchar(200) = NULL, @DeleteDups as bit = NULL, @UniqueColName as varchar(50) = NULL, @CreateIdentColIfNeeded as bit = NULL, @StoredProcedureResult int = NULL OUTPUT
DECLARE @SUM int DECLARE @COUNT int DECLARE @NextColumn varchar(50)
/*==================================================================================*/ /*========================VARIABLE INITIALIZATION AND SETUP========================*/ /*=================================================================================*/ /*If no unique column is located then a temporary Identity column will be created useing the name specified in this TempIdentColName string*/ SET @TempIdentColName = 'TempIdentityColXY123'
SET @SQL_DetermineUniqueTemplate = 'SELECT @COUNT = COUNT(Count), @SUM = sum(Count) from ' SET @SQL_DetermineUniqueTemplate = @SQL_DetermineUniqueTemplate + CHAR(13) + '(SELECT TOP 100 PERCENT <COLUMN_NAME>, COUNT(*) as Count FROM ' + @TableName SET @SQL_DetermineUniqueTemplate = @SQL_DetermineUniqueTemplate + CHAR(13) + ' GROUP BY <COLUMN_NAME> ORDER BY <COLUMN_NAME>) a'
/*Retrieve the Host Name. This will be used later in this SP as a test to determine if the user is making this call from within SQL Query Analyzer*/ SELECT @HostName = hostname FROM master..sysprocesses WHERE spid = @@SPID AND program_name = 'SQL Query Analyzer'
/*Set ActionText to be used in message output*/ IF (@DeleteDups IS NULL) OR (@DeleteDups = 1) SET @ActionText = 'Selection' ELSE SET @ActionText = 'Deletion'
/*If a value is specified for use by UniqueColName it cannot exist in the columns from the DupQualifierFieldNameList*/ IF CHARINDEX(@UniqueColName, @DupQualifierFieldNameList) > 0 BEGIN /*The value in UniqueColName was detected in DupQualifierFieldNameList.*/ IF NOT (@HostName IS NULL) PRINT 'The UniqueColName provided (' + @UniqueColName + ') must not exist in DupQualifierFieldNameList (' + @DupQualifierFieldNameList + '). Other solutions will be sought automatically.' SET @UniqueColName = NULL END
/*If UniqueColName is provided then perform check to ensure that all the values in that column are, in fact, unique.*/ IF NOT (@UniqueColName IS NULL) BEGIN SET @SQL = REPLACE(@SQL_DetermineUniqueTemplate,'<COLUMN_NAME>', @UniqueColName) /*Perform a check of this column to determine if all of it's values are unique*/ EXEC sp_executesql @SQL, N'@SUM as int OUTPUT,@COUNT as int OUTPUT',@SUM OUTPUT,@COUNT OUTPUT /*Test to determine if this column contains unique values*/ If @SUM <> @COUNT BEGIN /*The column specified by UniqueColName does not contain unique values.*/ IF NOT (@HostName IS NULL) PRINT 'The UniqueColName provided (' + @UniqueColName + ') does not contain unique values. Other solutions will be sought automatically.' SET @UniqueColName = NULL END END
/*==============================================================*/ /*======================HELP OUTPUT TEXT======================*/ /*==============================================================*/ IF (@TableName IS NULL) OR (@TableName = '/?') OR (@TableName = '?') OR (@DupQualifierFieldNameList IS NULL) OR (@DupQualifierFieldNameList = '/?') OR (@DupQualifierFieldNameList = '?') BEGIN IF NOT (@HostName IS NULL) BEGIN PRINT 'sp_RemoveDups ''TableName'', ''DupQualifierFieldNameList'', [''DeleteDups''], [''UniqueColName''], [''CreateIdentColIfNeeded''], <''StoredProcedureResult''>' PRINT '=====================================================================================================================================================================' PRINT 'TableName: Required - String - Name of the table to detect duplicate records in.' PRINT 'DupQualifierFieldNameList: Required - String - Comma seperated list of columns that make up the unique record within TableName.' PRINT 'DeleteDups: Optional - Bit, Set to 0 to delete duplicate records. A value of NULL or 1 will return the duplicate records to be deleted.' PRINT 'UniqueColName: Optional - Bit - A table must have a unique column value in it to perform the deletion logic. If no UniqueColName is provided then an attemp will be made to locate the RowGUID column. If that fails then an attempt will be made to locate the Identity column. If that fails then all of the columns of the table will be examined and the first one with all unique values will be selected.' PRINT 'CreateIdentColIfNeeded: Optional - Bit - By default this SP will create an identity column if no unique column can be located. Pass in a 1 here to run this feature off.' PRINT 'StoredProcedureResult: Optional - OUTPUT - Int - Returns a 3 if an error occured, otherwise returns a 0.' END SET @StoredProcedureResult = 3 RETURN END
/*========================================================================*/ /*======================DETECT USABLE UniqueColName======================*/ /*========================================================================*/ IF @UniqueColName IS NULL BEGIN /*Check for a RowGUID or Identity column in this table. If one exists, then utilze it as the unique value for the purposes of this deletion*/ IF EXISTS(SELECT * FROM SysColumns WHERE ID = Object_ID(@TableName) and ColumnProperty(ID,Name,'IsRowGUIDCol') = 1) SET @UniqueColName = 'RowGUIDCol' IF EXISTS(SELECT * FROM SysColumns WHERE ID = Object_ID(@TableName) and ColumnProperty(ID,Name,'IsIdentity') = 1) SET @UniqueColName = 'IdentityCol'
IF @UniqueColName IS NULL /*If no RowGUID or Identity column was found then check all of the columns in this table to see if one of them can be utilized as a unique value column*/ BEGIN /*Select all of the columns from the table in question...*/ DECLARE MyCursor CURSOR LOCAL SCROLL STATIC FOR SELECT name FROM syscolumns WHERE OBJECT_ID(@TableName)=ID
OPEN MyCursor FETCH NEXT FROM MyCursor INTO @NextColumn WHILE @@fetch_status = 0 BEGIN /*Create SQL string with correct column name in place.*/ SET @SQL = REPLACE(@SQL_DetermineUniqueTemplate,'<COLUMN_NAME>', @NextColumn) /*Perform a check of this column to determine if all of it's values are unique*/ EXEC sp_executesql @SQL, N'@SUM as int OUTPUT,@COUNT as int OUTPUT',@SUM OUTPUT,@COUNT OUTPUT /*Test to determine if this column contains unique values*/ If @SUM = @COUNT BEGIN /*A unique values column is detected. Use it and break out of the loop UNLESS column is specified in DupQualifierFieldNameList*/ IF CHARINDEX(@NextColumn, @DupQualifierFieldNameList) = 0 BEGIN /*NextColumn was NOT detected in DupQualifierFieldNameList, so this is the column we will use.*/ SET @UniqueColName = @NextColumn BREAK END END ELSE FETCH NEXT FROM MyCursor INTO @NextColumn END CLOSE MyCursor DEALLOCATE MyCursor
END END
/*If no UniqueColName has been found then create one UNLESS @CreateIdentColIfNeeded = 1*/ IF (@UniqueColName IS NULL) AND ( (@CreateIdentColIfNeeded IS NULL) OR (@CreateIdentColIfNeeded = 0) ) BEGIN /*Add a sequence column to the table...*/ IF NOT (@HostName IS NULL) PRINT 'Creating temporary identity column in the ' + @TableName + ' table named ' + @TempIdentColName + ' for use in this ' + LOWER(@ActionText) + ' process...' EXEC('ALTER TABLE ' + @TableName + ' ADD ' + @TempIdentColName + ' [int] IDENTITY (1, 1)') SET @UniqueColName = @TempIdentColName END
/*============================================================================*/ /*======================EXECUTE DELETION OR SELECTION======================*/ /*===========================================================================*/ IF @UniqueColName IS NULL BEGIN /*No UniqueColName was provided by the user and none were detected by the script. This deletion algorythm cannot run.*/ IF NOT (@HostName IS NULL) PRINT 'Could not perform ' + LOWER(@ActionText) + ' process. No unique columns were located and the UniqueColName flag is set to 1 (False).' SET @StoredProcedureResult = 3 RETURN END ELSE BEGIN IF NOT (@HostName IS NULL) PRINT 'Performing ' + LOWER(@ActionText) + ' utilizing the unique values in the ' + @UniqueColName + ' column as a reference...' /* Create and execute an SQL statement in the form of:
SELECT * (or DELETE) FROM TableName WHERE UniqueColName IN ( SELECT UniqueColName FROM TableName WHERE UniqueColName NOT IN ( SELECT MAX(Cast(UniqueColName AS varchar(36))) FROM TableName GROUP BY DupQualifierFieldNameList, DupQualifierFieldNameList, etc ) ) */ /*Delete all duplicate records useing @UniqueColName as a unique ID column */ IF (@DeleteDups IS NULL) OR (@DeleteDups = 1) SET @SQL = 'SELECT * ' ELSE SET @SQL = 'DELETE '
SET @SQL = @SQL + 'FROM ' + @TableName + ' WHERE ' + @UniqueColName + ' IN ' SET @SQL = @SQL + CHAR(13) + CHAR(9) + '(' + CHAR(13) + CHAR(9) SET @SQL = @SQL + 'SELECT ' + @UniqueColName + ' FROM ' + @TableName + ' WHERE ' + @UniqueColName + ' NOT IN ' SET @SQL = @SQL + CHAR(13) + CHAR(9) + CHAR(9) + '(' + CHAR(13) + CHAR(9)+CHAR(9) SET @SQL = @SQL + 'SELECT MAX(Cast(' + @UniqueColName + ' AS varchar(36))) FROM ' SET @SQL = @SQL + @TableName + ' GROUP BY ' + @DupQualifierFieldNameList SET @SQL = @SQL + CHAR(13) + CHAR(9) + CHAR(9) + ')' + CHAR(13) + CHAR(9) + ')'
EXEC (@SQL) IF @@ERROR <> 0 BEGIN IF NOT (@HostName IS NULL) PRINT @ActionText + ' process failed.' SET @StoredProcedureResult = 3 END ELSE BEGIN IF NOT (@HostName IS NULL) PRINT @ActionText + ' completed successfully with this SQL: ' + CHAR(13) + @SQL SET @StoredProcedureResult = 0 END END
IF (@UniqueColName = @TempIdentColName) AND ( (@CreateIdentColIfNeeded IS NULL) OR (@CreateIdentColIfNeeded = 0) ) BEGIN /*Remove the sequence column from the table...*/ IF NOT (@HostName IS NULL) PRINT 'Removing temporary identity column named ' + @TempIdentColName + ' from the ' + @TableName + ' table...' EXEC('ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @TempIdentColName) END GO
Hello,how I can detect via code (C++, C#) which sql servers like oracle, mssql,mysql are close to me and connectable within one or two second (timeout towait for answers should be variable)?Thank you.regardsMark
