I´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 notify
somehow 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 can
exploit to get the job done? Can you suggest me the most suitable mechanism
to implement this?
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
I need to detect what version/ or versions of SQL Server are installed on a machine before I can install my software. I am currently looking for SQL Server 2000 / SQL Server 2005 /SQL Server Express Edition & SQL Server 2008 CTP (for the time). How do I find this out. Is SQL-DMO the way forward or should this be some WMI script. I would appreciate any pointers/scripts etc.
I have read a lot of people mentioning using @@version from a query window but as I need the software to do this on first start up can this work for all versions
Running SQLEXPR_ADV.EXE against Windows XP SP0 causes the setup to exit gracefully with a message indicating the the right service pack is not installed.
However, running SQLEXPR_ADV.EXE against Windows XP SP1 does not exit and continues to install. Then, when you try to run a script against the installation, you get the message:
[Microsoft][ODBC SQL Server Driver][DBMSLPCN][SQL Server does not exist or access denied].
This will become very problematic when installing SQL Server Express as the database for small installations.
The setup really needs to be updated to look for all the system requirements that it can detect. Evidently it can detect the Service Packs. The Wise Installer is not able to script that requirements unless there is some way to make the Windows Installer look for it that is not documented.
I need to make a control in a trigger, but there is a sequence of N INSERT inside oneself transaction and I should only control when it is the last register. I ignore the quantity of INSERT, it can be one or several. And in the values there is not any indicator that allows me to deduce if it is the last one. It would seem that I need a trigger for COMMIT... Is there some form of making this?
Today I installed SQL Server 2005 developer edition on a test server. Afterwards I also installed the SP1. During the installation of SP1, the installation program said the server should be restarted afterwards, which I did.
Now when I look at the Add/Remove Programs dialog I have several items which mention SP1 on the SQL Server items.
But when I request the server properties in Managemnt Studio I see the version number 9.0.2047. Shouldn't this be 9.1.XXXX?
I am currently writing an install wizard for my web application. One of my steps is Installing asp.net membership, roles, and session state to the new database. My wizard detects if this step has already been completed through the page_Load event. Currently I am writing makeshift code just to see if the tables exist.Does ASP.Net provide a way to detect what services have been installed on the database? So far I have only found Install and uninstall functionality in the System.Web.Management Namespace.I have been searching for this for a while, I am sure this question has been answered somewhere, but there is so much base information on how to initially set up membership that I cant seem to find the answer.
I import from CSV files into MSSQL tables. My hierarchy absolutely wants to keep the CSV standard format. Some string have quotes inside. With Sybase it jumps these corrupted lines. With MSSQL and DTS, it breaks all the importation.
Does anyone know how to jump (aloso is there a possibility to detect the number of corrupted lines, Sybase can do that) ?
I have been through our database and we have a culprit who has been inputting all names in full capitals which I have always found particularly annoying. I want to convert them all to proper case for which I found the code below. However how can I detect the set where they have inproper case.
CREATE FUNCTION dbo.pCase ( @strIn VARCHAR(255) ) RETURNS VARCHAR(255) AS BEGIN IF @strIn IS NULL RETURN NULL
WHILE @i <= DATALENGTH(@strIn) BEGIN SET @c = SUBSTRING(@strIn,@i,1) IF @c IN (' ','-','''') BEGIN SET @strOut = @strOut + @c SET @Up = 1 END ELSE BEGIN IF @up = 1 SET @c = UPPER(@c) ELSE SET @c = LOWER(@c)
SET @strOut = @strOut + @c SET @Up = 0 END SET @i = @i + 1 END RETURN @strOut END GO
I have a VB.NET program that displays the time extracted from a SQL Server database datetime datatype by way of a User-defined scalar function I created. However, sometimes information is entered into the system through the program that does not have a time-- only a date. SQL Server automatically assigns a time of 12:00 AM to these values (since they're a datetime). Is there any way to detect when this happens in my user-defined scalar function so that when I try to extract time values, I can instead return a message/time of my choice? I would rather not assume that all 12:00 AM values are automatically inserted by SQL Server since this might not actually be the case.
I'm looking for an efficient t-sql script to loop through all usertables in a db and determine/print the value of each row having adatetime field (including cases where there are multiple datetimefields per row)Help greatly appreciated..
SELECT dnn_Roles.RoleName, xyzUser.FirstName, xyzUser.LastName, xyzUser.Email, xyzRewardPoint.Points, xyzRewardPoint.RewardID FROM xyzRewardPoint INNER JOIN xyzUser ON xyzRewardPoint.UserID = xyzUser.UserId INNER JOIN dnn_UserRoles INNER JOIN dnn_Roles ON dnn_UserRoles.RoleID = dnn_Roles.RoleID ON xyzUser.ProviderId = dnn_UserRoles.UserID WHERE (dnn_UserRoles.RoleID = 3) OR (dnn_UserRoles.RoleID = 4) OR (dnn_UserRoles.RoleID = 6) ORDER BY dnn_UserRoles.RoleID
What I need is to extend this query to detect any users who exist in dnn_UserRoles.RoleID 3, 4 or 6 but do not have a RewardID value of '43' in the xyzRewardPoint table.
Regarding to my previous post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=553652&SiteID=1, just wondering if there's a way to detect any changes which is made on the existing indexes in a published table so that the device can determine whether reinitialization of subscrption is needed before every synchronization.
Users have asked if, in addition to the scheduled run, they can have it so that they could dump a file into the input directory and then kick-off the package immediately.
Problem is that things fail if they try to start the package when the scheduled instance of the package is still running.
Is there any way that the package could check to see if an instance if itself is currently executing and refuse to execute if there IS an instance running?
Any help here is appreciated, I work in a large software company that has many small teams. I am faced with the issue of some of the other teams are changing the structure of tables and views and even SPs and functions without letting the rest know of these changes.
My question is, is there a way of tracking these changes through a job to alert everyone else in case this situation happens.
Hi, I need to create a SSIS package for following usage:
I have a folder called c: est. Every month, I copy a file with following format testMMYY.txt. (Month Year) from a client (A) for vendor B. I have a sql table which contains filemonth(, clientname and vendorname. How can I create a SSIS package to detect any new files, if any, and populate the month and year into file month, and populate clientname and vendorname.
I need to be able to programmatically detect whether or not SQL Server 2005, anything from Express on up to the full package with all the bells and whistles, is installed on the computer regardless of which service pack (if any) is installed. I found a forum article that provides a Registry setting that I could look at, but in the reply to the original post, it recommends using WMI instead of Registry keys. Also, I don't know whether or not the Registry key given for SQL Server 2005 would be present for even the minimal package. (Our requirements are for at least the Express package, and I understand that there's an even more basic package than that.) I'm maintaining a couple of installers that use 5-year old InstallShield technology (and Visual Studio 6 for any C/C++ code that I have to create) that have to check whether or not SQL Server 2005 Express (or a fuller package) is installed. I have done no SQL Server programming, so I've never even heard of WMI. Also, because these are installers, they have to work out of the box with no requirements to install anything beforehand to allow the installer to work. (We are working on an MSI-based installer with a bootstrap executable (the latter built using Visual Studio 2005) that will replace the older installers in the next version of our software, but we still need to support the older installers for now. Therefore, an out of the box solution that works with the older technology that we are still using for the older software is imperative. As the older software supports Win2k, the SQL Server detection solution must do so, too.)
The current setting of this option can be determined by examining the page_verify_option column in the sys.databases catalog view or the IsTornPageDetectionEnabled property of the DATABASEPROPERTYEX function.
However, there is no column named page_verify_option in the view sys.databases, and DATABASEPROPERTYEX('IsTornPageDetectionEnabled') does not discriminate between the settings CHECKSUM and NONE (it returns 0 for both)!
I've been successful at installing a customized SQLexpress using setup.exe /settings template.ini.
What I'd like to do now is see if I can progammatically detect a Firewall on the SQLexpress machine and if there is one to add the exceptions for sqlservr and sqlbrowser programmatically so that the user doesn't have to do anything.
