Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

June 2008 Blog Posts

How to get authentication mode in SQL Server

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

posted @ Thursday, June 19, 2008 5:10 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

How to change authentication mode in SQL Server

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...

posted @ Thursday, June 19, 2008 5:04 PM | Feedback (8) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Some SQL Server network properties

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)...

posted @ Thursday, June 19, 2008 3:57 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Horizontal partitioning, Enterprise style

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  

posted @ Thursday, June 12, 2008 4:18 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Administration SQL Server 2005 ]

Bug found in SQL Server 2005 sp2

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,      ...

posted @ Thursday, June 05, 2008 10:30 AM | Feedback (1) | Filed Under [ Administration SQL Server 2005 SQL Server 2000 ]

Powered by:
Powered By Subtext Powered By ASP.NET