I get really frustrated with Moodle for using int fields to store dates. They store it in the Unix (Epoch) format. Here’s how to make it more readable.
To convert it to a readable date, use
FROM_UNIXTIME(int)
You can also get more formatting control with something like:
FROM_UNIXTIME(int, '%Y %D %M %h:%i:%s %x')
Serendipity is looking in a haystack for a needle and finding a farmer’s daughter.
–Julius Comroe, Jr.
Code to search across the text of all stored procedures for a particular string.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%foobar%’
AND ROUTINE_TYPE=’PROCEDURE’
This may be obvious to some, but I had a hard time figuring it out at first. I needed to call the constructor of a parent class in the child. This is handy when you have things that happen in the constructor of all children, but one place where there is some extra stuff that needs to happen. Rather than duplicate the common things, just call the parent constructor before doing your extra stuff.
parent::Node($rTypes);
In this case it will call the parent class name Node and will pass the row record variable that was passed to the child constructor.
When you are returning longer text from a query in PHP, like from a varchar(MAX) or such you can get weird characters at the end. The problem is that the ODBC connection, which I used to query the MS SQL database, has a default field bytes limit of 4096 chracters. This is controlled by the odbc.defaultlrl setting in the php.ini file. The solution that I found was to override this setting in the page where you need the long text with a line like this.
ini_set(‘odbc.defaultlrl’,65536);
Supposedly you can use the functionodbc_longreadlen setting, but I was not able to get it to work. You can find more info here and here. The default value for this is 4096, so 65536 may be overkill, but you can find what fits. When I tried 0, which is supposed to set it to passthrough all the text it didn’t work at all.
For the curious lrl = long read length
Some quick notes on handling checkboxes (and probably radio buttons, but I haven’t tested that) in JQuery.
To find a count of all checked items with the class Testbox.
$(".Testbox:checked").size();
To get a count of all of the unchecked items use this code:
$(".Testbox:not(:checked)").size();
To test an individual item with the ID Testbox1 use this:
$('#Testbox1').is(':checked');
Search and replace all occurances of a string in all tables in a database.
From http://vyaskn.tripod.com/
--To replace all occurences of 'America' with 'USA':
EXEC SearchAndReplace 'America', 'USA'
GO
REATE PROC SearchAndReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END
SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END
Search all text fields across all tables in a database. Taken from http://vyaskn.tripod.com/ –To search all columns of all tables in Pubs database for the keyword “Computer” EXEC SearchAllTables ‘Computer’ GO
CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) )
AS BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
Often you’ll want to run Remote Desktop services on a non-standard port. Here’s the steps for changing the listening port on the “host” system. Taken from the MS Knowledge Base.
View products that this article applies to. This article was previously published under Q306759 Important This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base: 256986 Description of the Microsoft Windows Registry INTRODUCTION This article describes how to change the port that Remote Desktop listens on. Note The Remote Desktop Connection Client for the Mac supports only port 3389. 3389 is the default port. MORE INFORMATION You can use the Remote Desktop feature in Microsoft Windows XP Professional to connect to your computer from another remote computer. Warning The Remote Assistance feature in Microsoft Windows XP may not work correctly if you change the listening port. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: 307711 Remote Assistance invitation file does not contain correct port number To change the port that Remote Desktop listens on, follow these steps. Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.
HKEY_LOCAL_MACHINESystemCurrentControlSetControlTerminalServerWinStationsRDP-TcpPortNumber
Note When you try to connect to this computer by using the Remote Desktop connection, you must type the new port.304304 How to configure the Remote Desktop client to connect to a specific port when you use Windows XP
It may be simple, but this SQL will order items numerically (1,2,3,10,20) instead of alpha (1,10,2,20,3).
SELECT ItemID, ItemName, ItemNum, CASE WHEN Len(ItemNum) = 0 THEN 'Z' ELSE SPACE(10- Len(ItemNum)) + ItemNum END OrderCol
FROM table
ORDER BY OrderCol