Painting Blue Code: 2013

2013-12-31

TSQL - SSMS intellisense refresh

Go to a query window in SQL Server Management Studio
Press CTRL + SHIFT + R

PS. This works at least on SQL Server 2012.

2013-12-30

TSQL - Dynamic SQL execution results into variable

declare @RecordCount as int;

set @RecordCount = 0;

declare @SQL as varchar(2000);

set @SQL = 'select @x=FIELD_NAME from TABLE1;';

exec sp_executesql @SQL, N'@x int out', @RecordCount out;

TSQL - List of all linked servers

SELECT * FROM sysservers;

2013-12-12

Apache Web Server PHP - How to enable PHP in MacOS embedded apache web server

(1) Locate this file, and open it.
/private/etc/apache2/httpd.conf

(2) Look for this line and uncomment it (remove the starting # sign)
# LoadModule php5_module libexec/httpd/libphp5.so

(3) Restart the apache web server
sudo apachectl -k restart

2013-12-10

SharePoint - Configure managed accounts error "object reference not set to an instance of an object"

Open SharePoint 2010 Management Shell (Programs > Microsoft SharePoint...)

$ma=Get-SPManagedAccount -Identity "\"
$ma.AutomaticChange=$false
$ma.Update()

Original source...
http://social.technet.microsoft.com/Forums/sharepoint/en-US/406f1817-d687-49d7-9691-26e487fb5577/configure-managed-accounts-object-reference-not-set-to-an-instance-of-an-object-error?forum=sharepointadminprevious

2013-10-18

TSQL - Object creation and modification date

SELECT type, name, create_date, modify_date

FROM sys.objects

;

2013-09-27

TSQL - Adding an identity column to an existing table

alter table DB.dbo.TABLE add RecordID INT IDENTITY;

TSQL - Change a column from nullable to non nullable

ALTER TABLE [DATABASE_NAME].[SCHEMA_NAME].[TABLE_NAME]

ALTER COLUMN [Column Name] varchar(8) NOT NULL;

2013-08-30

GII - Unable to write file when creating a gii controller

This is related to the PHP framework YII. If you are trying to generate a file (let's say, a controller), and get the error message "Unable to write file...", then simply change the permissions of the entire web folder, of course, provided this is in a secure development environment.

sudo chmod -R 777 /path/to/your/webapp_folder_name

2013-08-29

PHP - "It is not safe to rely on the system's timezone settings..."

If you get an error message like the one below, simply add this line to your php.ini file.

date.timezone = "America/New_York"

date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EDT/-4.0/DST' instead

APACHE - How to start, stop or restart the apache server

Just type this command in a new Terminal window:

Restart:

sudo apachectl restart

Stop:

sudo  apachectl -k stop

Start:

sudo  apachectl -k start

PHP - How to get local server's PHP configuration

Create a .php docuemnt within the server, with the following content, then simply open it. The web file will display the location of the php.ini file and every parameter related to PHP's settings in the server.

phpinfo();
?>

2013-08-08

TSQL - Function to transform a given comma-separated string value into a table with one row per element

Execution example:

getTableFromCSV('asdf,qwer,poiu');

Funcion's code:

ALTER function [dbo].[getTableFromCSV]

(

@csv VARCHAR(MAX)

)

RETURNS

@tbl TABLE( val VARCHAR(255) )

BEGIN

DECLARE @sTemp VARCHAR(255)

WHILE LEN(@csv) > 0

BEGIN

SET @sTemp = LEFT(@csv, ISNULL(NULLIF(CHARINDEX(',', @csv) - 1, -1), LEN(@csv)))

SET @csv = SUBSTRING(@csv,ISNULL(NULLIF(CHARINDEX(',', @csv), 0), LEN(@csv)) + 1, LEN(@csv))

INSERT INTO @tbl VALUES (LTRIM(RTRIM(@sTemp)))

END

-- kill the nulls/ empty strings

DELETE FROM @tbl WHERE val IS NULL OR val = ''

RETURN

END

;

TSQL - Function to get string returned as a table with one record for each word, and some basic stats

If you create the function (code below), and execute this:

select * from getWords( 'this is only a one one figurative test test ', 0, 1 );

You will get this:

The function's code:

create FUNCTION [dbo].[getWords]

(

@text nvarchar(4000)

, @ReturnOnlyMeaningful bit

, @SuppressDuplicates bit

)

RETURNS

@words

TABLE (

WordID smallint --primary key

--, Position smallint

, Word varchar(255)

, OrderOfAppearance tinyint

, AlphabeticRank tinyint

, [Length] tinyint

, LengthRank tinyint

, Frequency tinyint

, WordCount tinyint

, DistinctWordCount tinyint

, Meaningful tinyint

, MeaningfulWordCount tinyint

, MeaningfulDistinctWordCount tinyint

, OriginalText varchar(4000)

)

BEGIN

-- Parameter validation and cleanup

-- Do not process empty strings

if ltrim(rtrim(@text)) = ''

begin

return

end

-- Double quote characters if found

select REPLACE( 'this is" something', '"', '""' );

select REPLACE( 'this is'' something', '''', '''''' );

set @text = REPLACE( @text, '"', '""' );

set @text = REPLACE( @text, '''', '''''' );

-- If it is ok to display dupicates

if @SuppressDuplicates = 0

begin

insert into

@words

select

occurrence

, display_term

, dense_rank()

over(

order by occurrence asc

)

as OrderOfAppearance

, DENSE_RANK()

over(

order by display_term asc

) as AlphabeticRank

, LEN( display_term ) as [Length]

, dense_rank()

over(

partition by

case

when special_term = 'Exact Match'

then 1

else 0

end

order by LEN( display_term ) desc

) as isMeaningfulLengthRank

, CAST( 1 as tinyint ) as Frequency

, null as WordCount

, null as DistinctWordCount

, cast(

case

when special_term = 'Exact Match'

then 1

else 0

end

as tinyint

) as isMeaningful

, null as MeaningfulWordCount

, null as MeaningfulDistinctWordCount

, @text as OriginalText

from

sys.dm_fts_parser ( N'"' + @text + '"', 1033, 0, 0)

where

-- Return only meaningful, according to the parameter

(

@ReturnOnlyMeaningful = 0

or (

@ReturnOnlyMeaningful = 1

and special_term = 'Exact Match'

)

-- Do not return end of file word.

and display_term not in

(

'END OF FILE' -- Seems to be produced by hyphens

)

;

end

-- If we are to suppress duplicates

else

begin

insert into

@words

select

min( occurrence ) as occurrenceMin

, display_term

, dense_rank()

over(

order by min( occurrence ) asc

)

as OrderOfAppearance

, DENSE_RANK()

over(

order by display_term asc

) as AlphabeticRank

, LEN( display_term ) as [Length]

, dense_rank()

over(

partition by

case

when special_term = 'Exact Match'

then 1

else 0

end

order by LEN( display_term ) desc

) as isMeaningfulLengthRank

, COUNT(*) as Frequency

, null as WordCount

, null as DistinctWordCount

, cast(

case

when special_term = 'Exact Match'

then 1

else 0

end

as tinyint

) as isMeaningful

, null as MeaningfulWordCount

, null as MeaningfulDistinctWordCount

, @text as OriginalText

from

sys.dm_fts_parser ( N'"' + @text + '"', 1033, 0, 0 )

where

-- Return only meaningful, according to the parameter

(

@ReturnOnlyMeaningful = 0

or (

@ReturnOnlyMeaningful = 1

and special_term = 'Exact Match'

)

-- Do not return end of file word.

and display_term not in

(

'END OF FILE'

)

group by

display_term

, case

when special_term = 'Exact Match'

then 1

else 0

end

;

end

-- Word count

update

@words

set

WordCount = ( select SUM(Frequency) from @words )

, DistinctWordCount = ( select COUNT(*) from @words )

, MeaningfulWordCount = ( select SUM( Meaningful * Frequency ) from @words )

, MeaningfulDistinctWordCount = ( select SUM( Meaningful ) from @words )

;

-- Return

return;

end

TSQL - Text field into table with one row per word

SQL Server 2012...

select * from sys.dm_fts_parser ( '"this is a-long text file file"', 1033, 0, 0 );

For more details, read
http://technet.microsoft.com/en-us/library/cc280463.aspx

2013-07-10

TSQL - Print statement does not immediately show, how to fix

Use RAISERROR instead...

RAISERROR('YOUR TEXT GOES IN HERE', 0, 1) WITH NOWAIT;

2013-07-08

TSQL - Error catching basic structure (try/catch/rollback)

begin try

begin tran

-- YOUR STATEMENTS HERE

commit tran

end try

begin catch

-- Display error

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_MESSAGE() AS ErrorMessage

;

print('ERROR...');

print( 'Number: ' + cast(ERROR_NUMBER() as varchar(50)) );

print( 'Message: ' + cast(ERROR_MESSAGE() as varchar(max)));

-- Announce rollback

print('Rolling back!');

rollback tran

end catch

TSQL - If object exists (table for example)

if OBJECT_ID('DATABASE_NAME_HERE.SCHEMA_NAME_HERE.TABLE_NAME_HERE', 'U') is not null

begin

print('The table exists!');

end;