KnowledgeBase: 2009

Sunday, October 18, 2009

SQL Server Compabibility Level

ALTER DATABASE [dbname]

SET compatibility_level = 90;

use master

select * from sys.Databases

T-SQL no leading zeroes

DECLARE @Sku NVARCHAR(500)

SET @Sku = '000000000000000000000000000000000000000000000000000000012335670000'

SELECT @Sku AS Original,

SUBSTRING(@Sku,PATINDEX('%[^0]%',@Sku),DATALENGTH(@Sku)) AS NoLeadingZeros

Declare @MyStr char(20)

Set @MyStr = '00004567890'

Select Convert(bigint,SubString(@MyStr, PatIndex('%[^0]%',@MyStr),Len(@MyStr) + 1 - PatIndex('%[^0]%',@MyStr)))

What this does is to find the first non-zero character in the field and then substring from there. It also works if you have a dash imbeded in the field.

Declare @MyStr char(20)

Set @MyStr = '0000-45670'

Select Convert(bigint,SubString(@MyStr, PatIndex('%[^0]%',@MyStr),Len(@MyStr) + 1 - PatIndex('%[^0]%',@MyStr)))

Thursday, July 9, 2009

Encrypt into database

if exists (select * from sysobjects where id = object_id('dbo.Reaffirm') and sysstat & 0xf = 4)

drop procedure dbo.Reaffirm

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

/*######################################################################################################

USED: reafffirmation of

FUNCTIONS USED: master.dbo.fn_encrypt_aes(CAST(@DateSubmitted AS VARBINARY(64)), 'LOCAL_test_web', NULL, 256)

DATE ID SCR# DESCRIPTION

---------- --- ----------- -----------------------------------------------------------------------

2009.07.09 wdb created.

###########################################################################################################*/

CREATE PROCEDURE dbo.Reaffirm

@SegmentID int

, @FirstName varchar(50)

, @LastName varchar(50)

, @EmployeeID int

, @Affirmed varchar(10)

, @DateSigned smalldatetime

, @DateSubmitted smalldatetime

, @IsDeleted varchar(10)

exec dbo.Reaffirm 1, 'William', 'Benson', '23609', 'yeah', '12/25/2009', '12/24/2009', 'nope'

SELECT * from Reaffirmation WHERE lastname = 'Benson'

INSERT INTO Business_Conduct_Reaffirmation.dbo.Reaffirmation (SegmentID, FirstName, LastName

, EmployeeNumber, Affirmed, DateSigned, DateSubmitted, IsDeleted)

VALUES (@SegmentID

, @FirstName

, @LastName

, @EmployeeID

, @Affirmed

, @DateSigned

, master.dbo.fn_encrypt_aes(CAST(@DateSubmitted AS VARBINARY(64)), 'LOCAL_test_web', NULL, 256)

, @IsDeleted)

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

Friday, June 12, 2009

DBCC TraceOn error

If you happen to be using Microsoft Query, you may receive the DBCC TraceOn error and the following may help.

BAD

'strCn = "Provider=MSDASQL; DRIVER={SQL Server}; SERVER=[dbservername]; UID=[userid]; Pwd=**********; APP=Microsoft® Query; DATABASE=[dbname]; Trusted_Connection=yes; "

GOOD

strCn = "DRIVER=SQL Server;SERVER=[dbservername]; APP=Microsoft® Query; DATABASE=[dbname] ;Uid=[userid];Pwd=******************;"

Tuesday, May 19, 2009

Cannot generate SSPI context

Answer: The domain account has been locked.

Tuesday, March 10, 2009

Export Access data into PowerPoint chart

http://support.microsoft.com/kb/200551

Set Pwr_Pnt = CreateObject("Powerpoint.application")
Pwr_Pnt.Activate
Set Presentation = Pwr_Pnt.Presentations.Open (Template_Name)
Pwr_Pnt.ActivePresentation.SaveAs PP_Filename
With Presentation
DoCmd.OpenForm "RTS_Chart"
Screen.ActiveForm!TheChart.Action = acOLECopy ' TheChart is the name of my chart in the form
SlideNum = SlideNum + 1
.Slides.Add SlideNum, ppLayoutTitleOnly
.Slides(SlideNum).Shapes
(1).TextFrame.TextRange.Text = "Actual vs. Projected
Expenditures"
.Slides(SlideNum).Shapes.Paste
end with
Here's how to add a slide with bulleted text:
With Presentation
SlideNum = SlideNum + 1
.Slides.Add SlideNum, ppLayoutTitle
.Slides(SlideNum).Shapes
(1).TextFrame.TextRange.Text = "Internal Issues"
.Slides(SlideNum).Shapes(1).Top = 0
.Slides(SlideNum).Shapes(1).Left = 100
.Slides(SlideNum).Shapes.AddTextbox
msoTextOrientationHorizontal, 100, 100, 200, 150
.Slides(SlideNum).Shapes
(2).TextFrame.TextRange.Text = "None"
.Slides(SlideNum).Shapes(2).Top = 100
.Slides(SlideNum).Shapes(2).Left = 20
.Slides(SlideNum).Shapes
(2).TextFrame.TextRange.ParagraphFormat.Bullet.Type =
ppBulletUnnumbered
.Slides(SlideNum).Shapes
(2).TextFrame.TextRange.ParagraphFormat.Alignment =
ppAlignLeft
end with
If you select the MIcrosoft Powerpoint 9.0 Object Library
as one of your references in VB, then you can go into the
object browser (View->Object Browser) and look at all the
classes and properties that are available to you.
I found a lot of information in the microsoft
knowledgebase articles 200551 and 209960
'##########################################################

Option Compare Database
Option Explicit
Sub cmdPowerPoint_Click()
Dim db As Database, rs As Recordset
Dim ppObj As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
On Error GoTo err_cmdOLEPowerPoint
' Open up a recordset on the Project List table.
Set db = CurrentDb
Set rs = db.OpenRecordset("Project List", dbOpenDynaset)

' Open up Powerpoint.
Set ppObj = New PowerPoint.Application
Set ppPres = ppObj.Presentations.Add
' Setup the set of slides and populate them with data from the
' set of records.
With ppPres
While Not rs.EOF
With .Slides.Add(rs.AbsolutePosition + 1, ppLayoutTitle)
.Shapes(1).TextFrame.TextRange.Text =
CStr(rs.Fields("Topic_Owner
(my:myFields/my:CBT_Topic_Owner)").Value)
.Shapes(2).TextFrame.TextRange.Text =
CStr(rs.Fields("Project_Name").Value)
End With
rs.MoveNext
Wend
End With
Exit Sub
err_cmdOLEPowerPoint:
MsgBox Err.Number & " " & Err.Description
End Sub

'#############################################
Sub cmdPowerPoint_Click()
    Dim db As Database, rs As Recordset
    Dim ppObj As PowerPoint.Application
    Dim ppPres As PowerPoint.Presentation

    On Error GoTo err_cmdOLEPowerPoint

    ' Open up a recordset on the Employees table.
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Employees", dbOpenDynaset)

    ' Open up an instance of Powerpoint.
    Set ppObj = New PowerPoint.Application
    Set ppPres = ppObj.Presentations.Add

    ' Setup the set of slides and populate them with data from the
    ' set of records.
    With ppPres
        While Not rs.EOF
            With .Slides.Add(rs.AbsolutePosition + 1, ppLayoutTitle)
                .Shapes(1).TextFrame.TextRange.Text = "Hi! Page " & rs.AbsolutePosition + 1
                .SlideShowTransition.EntryEffect = ppEffectFade
                With .Shapes(2).TextFrame.TextRange
                    .Text = CStr(rs.Fields("LastName").Value)
                    .Characters.Font.Color.RGB = RGB(255, 0, 255)
                    .Characters.Font.Shadow = True
                End With
                .Shapes(1).TextFrame.TextRange.Characters.Font.Size = 50
            End With
            rs.MoveNext
        Wend
    End With

    ' Run the show.
    ppPres.SlideShowSettings.Run

    Exit Sub

err_cmdOLEPowerPoint:
    MsgBox Err.Number & " " & Err.Description
End Sub

Thursday, March 5, 2009

T-SQL - first day of month

-- Determine the First Day of this current month. The page calcs cannot be on or after this date.

DECLARE @FirstDayOfThisMonth as datetime

SELECT @FirstDayOfThisMonth = CAST( CAST(Month(GetDate()) as nvarchar(2)) + '/01/' + CAST(Year(GetDate()) as nvarchar(4)) + ' 00:00:00.000' as datetime)

--SELECT @FirstDayOfThisMonth

Wednesday, February 25, 2009

Who has accessed my SQL Server

SELECT I.NTUserName

, I.loginname

, I.SessionLoginName

, I.databasename

, Min(I.StartTime) as first_used

, Max(I.StartTime) as last_used

, S.principal_id

, S.sid

, S.type_desc

, S.name

FROM sys.traces T CROSS Apply ::fn_trace_gettable(T.path, T.max_files) I LEFT JOIN sys.server_principals S ON CONVERT(VARBINARY(MAX), I.loginsid) = S.sid

WHERE T.id = 1 And I.LoginSid is not null