KnowledgeBase: 2011

Tuesday, December 13, 2011

Linked Server

/*
EXEC sp_addlinkedserver
   @server = 'sibr',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = '\\tpadev01\[folder name]\[folder name]\[db name].mdb'
GO

*/

SQL Server t-sql money formats

http://www.sqlusa.com/bestpractices2005/moneyformat/

Monday, November 28, 2011

MS-Word Easter Egg

Open MS-Word
type =Rand(3,8)
press Enter

On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look. You can easily change the formatting of selected text in the document text by choosing a look for the selected text from the Quick Styles gallery on the Home tab. You can also format text directly by using the other controls on the Home tab. Most controls offer a choice of using the look from the current theme or using a format that you specify directly. To change the overall look of your document, choose new Theme elements on the Page Layout tab. To change the looks available in the Quick Style gallery, use the Change Current Quick Style Set command.

Both the Themes gallery and the Quick Styles gallery provide reset commands so that you can always restore the look of your document to the original contained in your current template. On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look. You can easily change the formatting of selected text in the document text by choosing a look for the selected text from the Quick Styles gallery on the Home tab. You can also format text directly by using the other controls on the Home tab. Most controls offer a choice of using the look from the current theme or using a format that you specify directly. To change the overall look of your document, choose new Theme elements on the Page Layout tab.

To change the looks available in the Quick Style gallery, use the Change Current Quick Style Set command. Both the Themes gallery and the Quick Styles gallery provide reset commands so that you can always restore the look of your document to the original contained in your current template. On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look. You can easily change the formatting of selected text in the document text by choosing a look for the selected text from the Quick Styles gallery on the Home tab. You can also format text directly by using the other controls on the Home tab. Most controls offer a choice of using the look from the current theme or using a format that you specify directly.

Thursday, October 20, 2011

Excel: Searching inside a string

VBA = instr(string to be searched, string searching for, where to start)

formula: = search()

Tuesday, October 18, 2011

Microsoft Home Use Program

If you have a valid SOCOM email address, you can take advantage of the Microsoft Home Use Program.

http://www.microsofthup.com/hupus/home.aspx?culture=en-US

Program Code: 8FC2A88309

The cost for Microsoft Office is a staggering $9.95.

SELECT from a Stored Procedure

There are multiple ways of doing this.

Method #1

if exists (select * from master..sysservers where srvname = 'loopback')
exec sp_dropserver 'loopback'
go

Method #2

exec sp_addlinkedserver @server = N'loopback', @srvproduct = N'', @provider, @datasrc = N'SQLOLEDB', = @@servername
go

Method #3

select * into #t from openquery(loopback, 'exec yourSproc')
select * from #t
drop table #t

Export to XML from SQL Server

select EmployeeID, FirstName, LastName from employees

ORDER BY EmployeeID

FOR XML AUTO, elements

select EmployeeID, FirstName, LastName from employees

ORDER BY EmployeeID

FOR XML AUTO

Upload image into SQL Server 2005

INSERT INTO myTable(Document)

SELECT * FROM OPENROWSET(

BULK N'C:\Inetpub\wwwroot\file.jpg', SINGLE_BLOB

) rs

SQL Server T-SQL table variable example

DECLARE @tblTravelRecords table (

RequestID uniqueidentifier,

TravelNumber varchar(7),

EstimatedTravelCost decimal(11,2),

[status] int

)

INSERT INTO @tblTravelRecords

SELECT pol.RequestID

, pol.PONumber

, CAST( subtotal + pol.Shipping + IsNull(pol.Discount,0) + pol.SalesTax as decimal(11,2) ) as 'EstimatedTravelCost'

, pol.Status

FROM luPOStatus lu RIGHT JOIN (PurchaseOrderLog pol LEFT JOIN @tblPOsubtotal pod ON pol.RequestID=pod.RequestID) ON lu.StatusID=pol.Status

WHERE [Contract] = @Contract

AND TaskNumber = @TaskNumber

AND SubTask = @SubTask

AND ( UseFFPFunds <> 1 OR UseFFPFunds IS NULL )

Excel Functions

http://www.contextures.com/xlFunctions04.html

Listing all constraints in SQL Server & MS-Access

SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
ORDER BY
1,2,3,4

If you want to limit it to specific tables, you can add any of the following immediately prior to the optional ORDER BY clause:

WHERE PK.TABLE_NAME='something'

WHERE FK.TABLE_NAME='something'

WHERE PK.TABLE_NAME IN ('one_thing', 'another')

WHERE FK.TABLE_NAME IN ('one_thing', 'another')

Microsoft Access

Here is some code that uses ADOX.Catalog:

<%
Set conn = CreateObject("ADODB.Connection")
Set cat = CreateObject("ADOX.Catalog")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<path to db>"
Set cat.ActiveConnection = conn

Response.Write "<table border=1><tr>" & _
"<th>Parent Table/Column</th>" & _
"<th>Child Table/Column</th>" & _
"<th>Key Name</th></tr>"

For Each tbl in cat.Tables
if left(tbl.Name, 4) <> "MSys" then
For Each key in tbl.Keys
If key.Type = 2 Then
For Each col in key.Columns
Response.Write "<tr><td>" & tbl.Name & "." & _
col.Name & "</td><td>" & _
key.RelatedTable & "." & _
col.RelatedColumn & "</td><td>" & _
key.Name & "</td></tr>"
Next
End If
Next
End If
Next

Response.Write "</table>"

Set cat = Nothing
conn.Close : Set conn = Nothing
%>

Monday, October 17, 2011

Last Modified Date in SQL Server

USE Evaluation;

SELECT name, create_date, modify_date

FROM sys.objects

WHERE type = 'P'

ORDER BY modify_date DESC

GO

select o.name, o.create_date, o.modify_date, len(m.definition)
from sys.sql_modules as m inner join sys.objects as o on m.object_id=o.object_id
where type = 'P'
order by o.modify_date DESC

SELECT o.schema_ID, s.name, o.name, o.create_date, o.modify_date, len(m.[definition]) as 'Definition_length'
from sys.schemas s INNER JOIN (sys.sql_modules m inner join sys.objects o on m.object_id=o.object_id) ON s.schema_id = o.schema_id
where type = 'P'
and m.[definition] LIKE '%2015%'
--and o.name IN ('snapshot', 'snapshotbase', 'snapshotLimit', 'snapshotlimitnonhc', 'snapshot_rel2_model_a','snapshot_rel2_model_b', 'snapshot_rel2_model_c', 'snapshotlimitnonhc')
and s.name NOT IN ('Reporting')
order by o.schema_ID, o.name, o.modify_date DESC

select * from sys.schemas

KB: IListSource does not contain any data sources

Is it possible that there is nothing in the returned dataset?

A handy way to investigate is to insert

r_DataSet.WriteXML("c:\test.xml")

When you open text.xml, you'll see what the dataset looks like.

MS-Access export to Excel

DoCmd.TransferSpreadsheet acExport, , "qryNameHere", "C:\YourFullPathHere\Book1.xls", False, "NewSheetName"

Determine if VB.NET control exists

Sub disable_ActionLinks()

Try

Dim Item1 As DataListItem ' for the first DataList

'Dim Item2 As DataListItem ' for the second DataList

'########################################################################

For Each Item1 In dlst_TEST.Items

Dim lb_edit As LinkButton = dlst_TEST.Items(Item1.ItemIndex).FindControl("edit")

If lb_edit IsNot Nothing Then

lb_edit.Visible = False

End If

'If Page.FindControl("IDofControl") IsNot Nothing Then

'Dim lb_remove As LinkButton = dlst_TEST.Items(Item1.ItemIndex).FindControl("remove")

'lb_remove.Visible = False

'dlst_Group2 = dlst_Group1.Items(Item1.ItemIndex).FindControl("dlst_Group2")

''#######################################################################

'For Each Item2 In dlst_Group2.Items

' Dim rbl_Group2 As RadioButtonList = dlst_Group2.Items(Item2.ItemIndex).FindControl("rbl_Group2")

' rbl_Group2.Enabled = False

' 'Response.Write(rbl_Group2.ID)

'Next

Catch ex As Exception

With Me

.lblMessage.Text = "error disabling controls. " & ex.Message

.Bad_Message()

End With

End Try

End Sub

Javascript: select all listbox

//<![CDATA[

function selectAllOptions(id) {

var ref = document.getElementById(id);

for(i=0; i<ref.options.length; i++)

ref.options[i].selected = true;

}

//]]>

</script>

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]

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

ALTER TABLE [dbo].[tblMetrics]

ADD

[CategoryID] [int] NULL ,

[ContractID] [int] NULL ,

[SDSTypeID] [int] NULL

SET ANSI_PADDING OFF

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

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

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

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

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

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

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

USE [Evaluation]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[tblSDSType](

[SDSTypeID] [int] NOT NULL,

[DSDTypeName] [varchar(20)] NOT NULL

) ON [PRIMARY]

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

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