|
|
|
Wednesday, 07 March 2012 00:32 |
Most of the time we forget the password which we set to limit the access from particular data. In MS SQL, if we forgot the master password for access, we no need to delete the entire database instead by running this below SQL DB script, you can set the new password for your database with the data remains as same as before
Run the below script in SQL Server Management Studio to set new password for SQL database (sa)
GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'NewPassword' MUST_CHANGE
GO
This is what the way in which I have done the password change for some of the applications we use around Predictive Dialler Software Read more: |
|
|
Saturday, 28 January 2012 01:36 |
// function for getting comma seperated values in sql server
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
-- This function splits a variable-length parameter array (actually a string
-- with comma as a delimiter) and stored the values into the table.
-- CHARINDEX() function is used to identify the position of the first delimiter
-- in the text and SUBSTRING() function is used to set the 'element' column.
ALTER FUNCTION [dbo].[GetCSVValues](
@string varchar(550) -- '1,2,3,5,6,7'
)
RETURNS @table TABLE(element int)
AS
BEGIN
DECLARE @tempvarchar(550),
@delimPos AS tinyint
SET @delimPos = 0
SET @temp= LTRIM(RTRIM(@string))
WHILE CHARINDEX(',',@temp) > 0
BEGIN
SET @delimPos = CHARINDEX(',',@temp)
INSERT INTO @table(element) VALUES (CAST((LEFT(@temp,@delimPos-1)) AS smallint))
SET @temp= RTRIM(LTRIM(SUBSTRING(@temp,@delimPos+1,LEN(@temp)-@delimPos)))
END
INSERT INTO @table(element) VALUES (CAST((@temp) AS smallint))
RETURN
END
 Read more: |
|
|
Wednesday, 21 December 2011 01:34 |
Following function to format numeric values
CREATE FUNCTION dbo.com_FormatNumber(@value BIGINT) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @minus CHAR, @working VARCHAR(MAX), @result VARCHAR(MAX), @section VARCHAR(4)
-- First, handle the sign
IF (@value < 0) SET @minus = '-' ELSE SET @minus = ''
SET @working = CAST(@value AS VARCHAR)
if (@minus <> '') SET @working = SUBSTRING(@working, 2, LEN(@working)-1)
SET @result = ''
-- break apart the number into sections of 3 digits and insert commas
WHILE LEN(@working) > 3
BEGIN
SET @section = ',' + SUBSTRING(@working, LEN(@working)-2, 3)
SET @working = SUBSTRING(@working, 1, LEN(@working)-3)
SET @result = @section + @result
END
-- add the remaining and tack on that sign if needed
IF (@minus <> '')
RETURN @minus + @working + @result
RETURN @working + @result
END
Usage is
PRINT dbo.FormatNumber(123456)
PRINT dbo.FormatNumber(-123)
OUTPUT
123,456
-123 Read more: |
|
|
|
|
|
|
Page 1 of 7 |
