Show/Hide Excel Windows

When you want to edit those worksheets (or re-hide them….)

1.       ALT + F11

2.       Ctrl + R (this will display the Project Explorer is not already visible)

3.       F4 (this will display the Properties Window if not already visible)

4.       In the Project Explorer, click the worksheet you want to hide or show

5.       To hide:  In the Properties Window, find the Visible property and set it to 2 – xlSheetVeryHidden

6.       To show:  In the Properties Window, find the Visible Property and set it to -1  - xlsheetVisible

Test it out J

How to get only DATE portion out of DATETIME column in MSSQL ?

cast(gerdate() as date)

, DATEADD(dd, 0, DATEDIFF(dd, 0, EffectiveAsOfDate)) as EffectiveAsOfDate2

, CONVERT(varchar(8), EffectiveAsOfDate, 112) as EffectiveAsOfDate3

, cast(cast((EffectiveAsOfDate - 0.500000038580247) as int) as datetime) as EffectiveAsOfDate4

 , CONVERT(CHAR(10),GETDATE(),103) as EffectiveAsOfDate5

ALTER table syntax

ALTER TABLE [dbo].[PendingTravel]

     ALTER COLUMN 
[createdate] [datetime] NULL CONSTRAINT [DF_PendingTravel_createdate] DEFAULT (getdate()),

--##############################################

ALTER TABLE [dbo].[tblEvaluationComments]

     ALTER COLUMN CommentText varchar(2000) NULL;

ALTER TABLE dbo.tblEvaluationOverview

     ADD

     [EffDateTEST] [datetime] NULL

ALTER TABLE dbo.tblEvaluationOverview

     DROP COLUMN [EffDateTEST]

--##########################################

USE [Evaluation]

GO

/****** Object:  Table [dbo].[tblRoles]    Script Date: 03/23/2010 13:46:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

ALTER TABLE [dbo].[tblMetrics]

     ADD

     [CategoryID] [int] NULL ,

       [ContractID] [int] NULL ,

       [SDSTypeID] [int] NULL    

GO

SET ANSI_PADDING OFF

ALTER TABLE [dbo].[tblMetrics]  WITH CHECK ADD  CONSTRAINT [FK_tblMetrics_tblCategories] FOREIGN KEY([CategoryID])

REFERENCES [dbo].[tblCategories] ([CategoryID])

GO

ALTER TABLE [dbo].[tblMetrics]  WITH CHECK ADD  CONSTRAINT [FK_tblMetrics_tblContracts] FOREIGN KEY([ContractID])

REFERENCES [dbo].[tblContracts] ([ContractID])

GO

ALTER TABLE [dbo].[tblMetrics]  WITH CHECK ADD  CONSTRAINT [FK_tblMetrics_tblContracts] FOREIGN KEY([ContractID])

REFERENCES [dbo].[tblContracts] ([ContractID])

GO

--#######################################################

USE [Evaluation]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tblSDSType](

       [SDSTypeID] [int] NOT NULL,

       [DSDTypeName] [varchar(20)] NOT NULL    

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblTaskBudget]  WITH CHECK ADD  CONSTRAINT [FK_tblTaskBudget_tblTasks] FOREIGN KEY([TaskID])

REFERENCES [dbo].[tblTasks] ([TaskID])

GO

T-SQL removing CHAR(10) and CHAR(13)

--SELECT REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

declare @NewLine char(2)

set @NewLine=char(13)+char(10)

update tblMetrics

     set MetricThreshold = Replace(MetricThreshold , @NewLine,'')

WHERE MetricThreshold like '%' +@NewLine +'%'

     AND MetricID = 999999

SQL Server Shrink File

use dbname;

GO

DBCC SHRINKFILE (dbname_log, 1);

GO

EXCEL :: MS-Excel Print Preview displays the size of postage stamp

Q:   Excel Print Preview shows up the size of a postage stamp

A:   change your default printer.  If you only have a single printer installed, then install another and make it the default.  View the print preview using the new printer.  Then, you can change the default printer back to the original.

Change CSS dynamically in javascript

// this simply appends the following words

//document.getElementById("message").className += "MyClass";

                                               

// this removes the words and sets border and background to White.

document.getElementById("message").className = "message";

document.getElementById("message").innerHTML = ' ';

Re-establish Identity column in MS-SQL Server (RESEED)

USE dbname
GO
DBCC CHECKIDENT (tablename, RESEED, 0)
GO

ColdFusion elimination of double quotes

<cfset mylist = Replace(Form.chkNumberList, '#chr(44)#', "'#chr(44)#'", "All")>

                                                               

<CFQUERY name="qryMarkAsPaid" datasource="#Session.DSN#">

     UPDATE dbo.table

     SET status = 99

       , PaidDate = GetDate()

     WHERE myNumber IN ('#REReplace(mylist, "''", "'", "ALL")#')

</CFQUERY>

T-SQL JOIN Update

SQL Server

UPDATE bh 

SET bh.EndDate = t.EndDate -- SELECT *

FROM Tasks t INNER JOIN BilletHours bh 

     ON bh.Contract = t.Contract 

     AND bh.TaskNumber = t.TaskNumber

     AND bh.Subtask = t.Subtask

WHERE t.Contract = '99'

     AND t.TaskNumber IN ('IND','H','PTO')

MS-Access

UPDATE _Tbl_Comments_Import x INNER JOIN dbo_tbl_Comments_Import tci ON tci.ID = x.ID
SET x.CommentCategory = [tci].[CommentCategory]
, x.CommentReviewed = [tci].[CommentReviewed]
, x.Impactful = [tci].[Impactful]
, x.Scrubbed = [tci].[Scrubbed]
, x.Sentiment = [tci].[Sentiment]
, x.Key_Phrase_I = [tci].[Key_Phrase_I]
, x.Key_Phrase_II = [tci].[Key_Phrase_II]
, x.Key_Phrase_III = [tci].[Key_Phrase_III]
, x.Scrubbed_Comment = [tci].[Scrubbed_Comment];

UPDATE [*Tbl_Demographics_Import] x INNER JOIN dbo_tbl_Demographics_Import tci ON tci.respid = x.respid
SET x.USMarket = [tci].[USMarket]
WHERE x.USMarket = 0