How To Detect Whether Installed The Sqlce 3.5 RTM Or Not ?
Jan 29, 2008How to Detect whether installed the sqlce 3.5 RTM or not ?
View 7 Replies
ADVERTISEMENT
Detect Installed Versions
Apr 24, 2008
Hi Guys,
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
Thanks in advance
Detect Installed Version?
May 8, 2006Today 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?
How can I be sure the SP1 is correcty insatlled?
Stievie
Best Way To Detect If SqlServices Has Been Installed On A Given Database
Feb 2, 2008I 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.
View 1 Replies View RelatedHow To Detect If SQL Server 2005 Is Installed?
Mar 13, 2008I 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.)
View 8 Replies View RelatedHow To Detect Backward Compatibility Pack If It's Already Installed Using C#?
Aug 29, 2006We have our custom install code using C# for a huge Client desktop application. We package SQL Express 2005 along with Backward Compatibility pack as this application was using MSDE before and at 11th hour management decided to pack SQL Express and backward compatibility pack.
During installation we want to detect if the Backward compatibility pack is already installed to avoid overwrite. Can anyone suggest how to detect if Backward compatibility pack is already installed using C#? I am sure there should be some registery keys to look for but don't know which.
Thanks.
Sqlce 3.0 Vs Sqlce 2.0 Memory Issue
Feb 6, 2006I am currently upgrading our ppc app (written in .net 2003) to .net 2005 and from sqlce 2.0 to 3.0. The new application runs out memory(storage) when handling lots of data transactions (both in case of using sqlcedatareader, and dataset ). There is no memory leak issue here, sqlce 3.0 simply uses a lot more (3 times more) memory than sqlce 2.0.
Our applicaton runs fine using sqlce 2.0 and .net 2003, but fail due to memory shortage
with the upgrades(which has the same code). Anyone can shed some lights on this issue?
How Do You Use The Upgrade.exe For Sqlce 2.0 To Sqlce 3.0?
Jun 9, 2006Does anyone have any instructions on how to go about uprading a .sdf file
from sqlce 2.0 to sqlce 3.0?
I found this on an msdn website
(link: http://www.microsoft.com/sql/editions/sqlmobile/upgrading.mspx) but
as you can see its very lacking on detail. (I included the link and content
referring to upgrading the database files so you won't have to go to the link
to see the part referring to upgrading the database.) I tried to run the upgrade.exe on my device but it doesn't explain how you're supposed to point it to the .sdf that you are trying to update. Here is the content on the above web page.
Upgrading Database Files
You can upgrade a database created with an earlier version of SQL Server CE
2.0 to a database created with SQL Server Mobile by using the SQL Server
Mobile Database Upgrade tool (upgrade.exe). The Database Upgrade tool runs on
a smart device.
When you run the SQL Server Mobile Database Upgrade tool, the new SQL Server
Mobile database is created on the smart device. The new database, with the
file name extension .sdf, contains all the data that was in the source
database. You need to reinitialize the upgraded database to continue using it
for replication.
Note: If you are using replication or remote data access (RDA) as a
connectivity solution before upgrading to SQL Server Mobile, you must
synchronize the source SQL Server CE database (subscription database) with
the SQL Server database (Publisher). The reason for synchronizing before
upgrading to SQL Server Mobile is to ensure that any changes that exist in
the tables on the SQL Server CE database are updated on the SQL Server
database, because after the upgrade you must resubscribe or repull using the
new database.
SQLCE 2.0 And SQLCE 3.0, Both On Same Server
Oct 11, 2006I presently run on my server SQL SERVER CE 2.0 and want to install, in addition on SQL Server 2.0, SQL Server Mobile 2005.
Is there a procedure to have both installed and running on a computer?
Thanks!
Marco
Bought And Installed VS2005 Professional - There Is No Management Studio Got Installed In The SQL Express.!
Jan 26, 2006Hi Friends,
I have a question. At my home computer i downloaded and installed VWD. As usual it also installed Sqlexpress as part of the installation(i checked the option). Later on i downloaded Management studio separately so i am fine with my home computer as every thing is free.
But at work we decided to develop application using VS 2005 and Sqlserver 2005. So we bought and installed VS2005 professional edition at work and it also installed Sqlexpress during the installation as a defualt desktop engine. I am fine with that too. But i am not understing where do i get the Management studio? Is it some thing i could get it off the web for free or what? I am not sure we would have an option while installing the VS 2005 professional that also does install management studio and we need to check that option. I am not sure if we could get the management studion off the web for free and and evelop applications.
Please advise. Thanks -L
To Find Whether Sql Server 2005 X86 Is Installed Or X64 Is Installed?
Jul 20, 2007I want to find whether SQL Server 2005 x86 is installed or SQL Server 2005 x64 is installed programatically(Any API would do.) If anyone can recommend a registry key that I can rely on find whether it is a 32 bit or 64 bit SQL Server that would suffice.
View 5 Replies View RelatedProblem: Must Detect Db Changes
Jul 20, 2005HelloI´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
View 1 Replies View RelatedHow To Detect *.cer Association
May 29, 2007I 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?
Detect Licensing
Aug 4, 2006Hello
I am trying to find out if versions of SQL SERVER 2005 we have installed have full or Developer Licenses.
They were installed previously by someone who no longer works here, and we do not the versions on record.
We have enough full licenses, so we just want to make sure the correct ones are set up on our servers.
Anyone know how/where we can find this information?
How To Detect NULL In SQL-table With VB.net ?
Oct 29, 2005Hi,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.
View 2 Replies View RelatedWhy Bcp Cannot Detect Temporary Table ?
Jul 22, 2006hi, good day, i facing a problem
Code:
CREATE PROCEDURE [SP_TEST]
AS
SELECT *
INTO #TMP_CUSTOMER
FROM O_CUSTOMER
-------------------------------------------------------------------------
SELECT DISTINCT
COUNT(CUST_CD)
FROM
#TMP_CUSTOMER
GO
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'.
thanks for guidance
Detect And Or Delete Dups
Oct 30, 2002Hassle 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.
Example: EXEC sp_RemoveDups 'MyTable','LastName,FirstName,HomePhone'
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
AS
SET NOCOUNT ON
DECLARE @SQL nvarchar(2000)
DECLARE @SQL_DetermineUniqueTemplate nvarchar(2000)
DECLARE @TempIdentColName varchar(20)
DECLARE @HostName varchar(50)
DECLARE @ActionText varchar(10)
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
Edited by - emanaton on 10/30/2002 11:28:34
Detect Connectable Db Servers
May 16, 2006Hello,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
View 3 Replies View RelatedSQLEXPR_ADV.EXE Does Not Detect WinXP SP1
May 17, 2006Running 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.
Pat
Detect Last Insert In Trigger
Jan 11, 2008I 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?
Launch Condition To Detect SQL CE?
Jan 29, 2007I have written a .NET application that uses SQL Server Compact Edition. It's deployed by a MSI file (setup project in VS - not ClickOnce).
How do I add a SQL CE launch condition to my setup project?
From Client, How Can I Detect Which Services're Running Or Not?
Aug 4, 2000From client, how can I detect which services're running or not? Example for SQLAgent, MS DTC..
Thanks.
Querry 2 Detect Doubles In A Table
Jul 6, 1999Hi,
I have a table which contain a field called 'company' of type 'int'.
I want to make a querry which returns all doubles ... for example:
I have: after querry the return must be:
company company:
1 1
3 3
3 3
11 3
7 2
3 1
2 2
1
4
2
5
Thank you,
Sebastian
Jump Or Detect Corrupt Lines In DTS
Mar 7, 2001Hi,
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) ?
Thanks for your answers
Best Regards
Axel
Correct/detect Improper Case
May 22, 2008I 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
DECLARE
@strOut VARCHAR(255),
@i INT,
@Up BIT,
@c VARCHAR(2)
SELECT
@strOut = '',
@i = 0,
@Up = 1
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
How To Detect The SQL Express & Yukon Installation
Nov 10, 2005Hi,
I have a need to check whether SQL Express 2005 or Yukon is installed on a machine or not.
Can anybody suggest what is right method to check the existence of these installations.
If possible suggest the registry keys which can be used for this installation check.
Thanks a lot
krishna.s
Detect No Time Entered For Datetime
Jul 20, 2006I 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.
View 4 Replies View RelatedDatabase Off Line; How Do I Detect With A Query
Jul 20, 2005How do I detect with a query which database is off-line.And how do I detect with a query the recovery model of a database.ByeArno de Jong
View 2 Replies View RelatedDetect Existence Of Datetime Fields
Jul 20, 2005I'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..
View 2 Replies View Related.net Version Of Detect Anomalies In Excel
Feb 21, 2007Is there an equivalent or similar .net sample for detecting data Anomalies?
Detect Anomalies in Excel
http://zones.advisor.com/doc/14413
SQL Query To Detect Non-existence Of Certain Records
Dec 27, 2007 Here is my query:
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.
Detect Changes On Indexes In A Published Table
Jul 27, 2006Hi,
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.
Cheers,
Justin
How To Detect At The Subscriber That A Replication Is Being (re)initialized.
Feb 21, 2007Is there a method/query to discover that the subscriber's databasereplication is being reinitialized?
The problem is that the applications which is connects to this database crashes when the replication is (re)initializing.
Thanx, Ralf