June 2008 Blog Posts
CREATE FUNCTION dbo.fnGetSQLServerAuthenticationMode
(
)
RETURNS INT
AS
BEGIN
DECLARE @InstanceName NVARCHAR(1000),
@Key NVARCHAR(4000),
@LoginMode INT
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
N'MSSQLSERVER',
@InstanceName OUTPUT
IF @@ERROR <> 0 OR @InstanceName IS NULL
RETURN NULL
SET @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
@Key,
N'LoginMode',
@LoginMode OUTPUT
RETURN @LoginMode
END
CREATE PROCEDURE dbo.uspSetSQLServerAuthenticationMode
(
@MixedMode BIT
)
AS
SET NOCOUNT ON
DECLARE @InstanceName NVARCHAR(1000),
@Key NVARCHAR(4000),
@NewLoginMode INT,
@OldLoginMode INT
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
N'MSSQLSERVER',
@InstanceName OUTPUT
IF @@ERROR <> 0 OR @InstanceName IS NULL
BEGIN
RAISERROR('Could not read SQL Server instance name.', 18, 1)
RETURN -100
END
SET @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\'
EXEC master..xp_regread N'HKEY_LOCAL_MACHINE',
@Key,
N'LoginMode',
@OldLoginMode OUTPUT
IF @@ERROR <> 0
BEGIN
RAISERROR('Could not read login mode for SQL Server instance %s.', 18, 1, @InstanceName)
RETURN -110
END
IF @MixedMode IS NULL
BEGIN
RAISERROR('No change to authentication mode was made. Login mode is %d.', 10, 1, @OldLoginMode)
RETURN -120
END
IF...
DECLARE @Stage TABLE
(
RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
Data VARCHAR(90),
Section INT
)
INSERT @Stage
(
Data
)
EXEC xp_cmdshell 'ipconfig /all'
DECLARE @Section INT
SET @Section = 0
UPDATE @Stage
SET @Section = Section = CASE
WHEN ASCII(LEFT(Data, 1)) > 32 THEN @Section + 1
ELSE @Section
END
SELECT MAX(CASE WHEN x.minRowID IS NULL THEN NULL ELSE s.Data END) AS Header,
MAX(CASE WHEN s.Data LIKE '%Host Name%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS HostName,
MAX(CASE WHEN s.Data LIKE '%Media State%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END) AS MediaState,
MAX(CASE WHEN s.Data LIKE '%Description%' THEN RTRIM(SUBSTRING(s.Data, 45, 90)) END)...
CREATE PARTITION FUNCTION pfOrderDate
(
DATETIME
)
AS RANGE RIGHT FOR VALUES (
'20000101',
'20010101',
'20020101'
)
GO
CREATE PARTITION SCHEME psYak
AS PARTITION pfOrderDate
ALL TO ([PRIMARY])
GO
CREATE TABLE Orders
(
OrderID INT NOT NULL,
CustomerID VARCHAR(15) NOT NULL,
OrderDate DATETIME NOT NULL
)
ON psYak(OrderDate)
GO
CREATE CLUSTERED INDEX IX_OrderID
ON Orders
(
OrderID
)
CREATE NONCLUSTERED INDEX IX_OrderDate
ON Orders
(
OrderDate
)
INCLUDE (
OrderID,
CustomerID
)
GO
INSERT Orders
(
OrderID,
CustomerID,
OrderDate
)
SELECT 1, 'Peso', '20011225' UNION ALL
SELECT 2, 'Jennie', '20020314'
SELECT OrderID,
OrderDate
FROM Orders
WHERE OrderDate = '20011225'
SELECT OrderID,
OrderDate
FROM Orders
WHERE OrderID = 1
DROP TABLE Orders
DROP PARTITION SCHEME psYak
DROP PARTITION FUNCTION pfOrderDate
For this to work, disconnect and reconnect the current query window in SSMS and then copy, paste and run the code below.
Books Online says IDENT_CURRENT "Returns the last identity value generated for a specified table or view in any session and any scope.". So how can the IDENT_CURRENT() return 1 for a newly created table with no inserted records? @@IDENTITY and SCOPE_IDENTITY works as expected.
CREATE TABLE #Temp
(
RowID INT IDENTITY(1, 1),
theValue INT
)
SELECT *
FROM #Temp
SELECT @@IDENTITY,
IDENT_CURRENT('#Temp'),
SCOPE_IDENTITY()
INSERT #Temp
SELECT 99
SELECT *
FROM #Temp
SELECT @@IDENTITY,
IDENT_CURRENT('#Temp'),
SCOPE_IDENTITY()
INSERT #Temp
SELECT 123456
SELECT *
FROM #Temp
SELECT @@IDENTITY,
...