USE master
CREATE LOGIN [$(UserName)] FROM WINDOWS WITH DEFAULT_DATABASE=[$(DatabaseName)] GO USE [$(DatabaseName)] GO CREATE USER [$(UserName)] FOR LOGIN [$(UserName)] GO EXEC sp_addrolemember N'$(RoleName)', N'$(UserName)' GO
NT Login permissions trump NT Group
For any login in an NT group, if you want to reduce, or increase more permissions, just add the login by itself. The individual’s permissions wins.
Quickly import EXCEL data to tempdb table and script out field descriptor
USE tempdb
GO
SELECT *
INTO MyEXCELSheet
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="E:\atest.xls";Extended properties=Excel 5.0'
)...Sheet1$
--
-- Use Query Analyzer to script out the table in tempdb.
--
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MyEXCELSheet]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[MyEXCELSheet]
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MyEXCELSheet]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[MyEXCELSheet] (
[CustomerID] [nvarchar] (255) NULL ,
[EmployeeID] [float] NULL ,
[Freight] [float] NULL ,
[OrderDate] [datetime] NULL ,
[OrderID] [float] NULL ,
[RequiredDate] [datetime] NULL ,
[ShipAddress] [nvarchar] (255) NULL ,
[ShipCity] [nvarchar] (255) NULL ,
[ShipCountry] [nvarchar] (255) NULL ,
[ShipName] [nvarchar] (255) NULL ,
[ShippedDate] [datetime] NULL ,
[ShipPostalCode] [float] NULL ,
[ShipRegion] [nvarchar] (255) NULL ,
[ShipVia] [float] NULL
) ON [PRIMARY]
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[MyEXCELSheet]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[MyEXCELSheet]
GO
Determine leap year SQL function
Here's one way, as inspired by Anatoly Lubarsky.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fn_IsLeapYear (@dtDate DATETIME)
RETURNS BIT
AS
BEGIN
DECLARE @dtFeb28 DATETIME,
@nMonthDay tinyint
--
-- Add a day to the 28th of February and see if it is the 1st or the 29th.
SELECT @dtFeb28 = CONVERT(VARCHAR(4), YEAR(@dtDate)) + '0228',
@nMonthDay = DATEPART(DAY, DATEADD(DAY, 1, @dtFeb28))
IF (@nMonthDay = 29)
RETURN 1
RETURN 0
-- Usage:
-- SELECT dbo.fn_IsLeapYear (GETDATE())
-- SELECT dbo.fn_IsLeapYear (dateadd(year,1,GETDATE()))
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO