This problem originated here http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=87&threadid=90916&enterthread=y and I post the solution here for two reasons. 1) The forum above does not support code tags 2) The common interest is high enough -- Prepare sample data DECLARE@Sample TABLE ( HoleID CHAR(8), mFrom SMALLMONEY, mTo SMALLMONEY, Result SMALLMONEY, PRIMARY KEY CLUSTERED ( HoleID, mFrom ), Seq INT ) INSERT @Sample ( HoleID, mFrom, mTo, Result ) SELECT'TWDD0004',1,2,0.
Read more →
SELECT Number, 1 - SIGN(Number & (Number - 1)) FROM master..spt_values WHERE Type = 'P' AND Number > 0
Other way is
CREATEFUNCTION dbo.isPowerOf2 ( @i INT ) RETURNSBIT AS BEGIN DECLARE @x FLOAT SET @x = LOG(Number) / LOG(2) RETURN CASE WHEN FLOOR(@x) = CEILING(@x) THEN 1 ELSE 0 END END
Legacy Comments
RamiReddy
2008-08-13
re: How to tell if a number is a "POWER of 2"-number Is that first query will work for the number 524288 which is 2 power 19.
Read more →
Here are all the 36 numbers between 0 and 9000 that have a different LEN than DATALENGTH. Number
Binary digits
VARBINARY
LEN
DATALENGTH
32
Read more →
DECLARE@Interfaces TABLE ( RowID INT IDENTITY(0, 1), Interface CHAR(38), IP VARCHAR(15) ) INSERT@Interfaces ( Interface ) EXECmaster..xp_regenumkeys N'HKEY_LOCAL_MACHINE', N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces' DECLARE@RowID INT, @IP VARCHAR(15), @Key NVARCHAR(200) SELECT@RowID = MAX(RowID) FROM@Interfaces WHILE@RowID >= 0 BEGIN SELECT @Key = N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces\' + Interface FROM @Interfaces WHERE RowID = @RowID EXEC master.
Read more →
I started out with typing SELECT@@VERSION and got the result as Microsoft SQL Server 2005 - 9.00.3215.00 (Intel X86) Dec 8 2007 18:51:32 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.
Read more →
When Windows Server 2008 is more common, you will be surprised how the Firewall is blocking all versions of SQL Server when upgrading the OS. Here are some links to remedy the problems http://msdn.
Read more →
CREATEFUNCTION dbo.fnGetSQLServerAuthenticationMode ( ) RETURNSINT 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.
Read more →
CREATEPROCEDURE dbo.uspSetSQLServerAuthenticationMode ( @MixedMode BIT ) AS SETNOCOUNT ON DECLARE@InstanceName NVARCHAR(1000), @Key NVARCHAR(4000), @NewLoginMode INT, @OldLoginMode INT EXECmaster..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.
Read more →
DECLARE@Stage TABLE ( RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED, Data VARCHAR(90), Section INT ) INSERT@Stage ( Data ) EXECxp_cmdshell 'ipconfig /all' DECLARE@Section INT SET@Section = 0 UPDATE @Stage SET@Section = Section = CASE WHENASCII(LEFT(Data, 1)) > 32 THEN @Section + 1 ELSE@Section END SELECTMAX(CASE WHEN x.
Read more →
CREATEPARTITION FUNCTION pfOrderDate ( DATETIME ) ASRANGE RIGHT FOR VALUES ( '20000101', '20010101', '20020101' ) GO CREATEPARTITION SCHEME psYak ASPARTITION pfOrderDate ALLTO ([PRIMARY]) GO CREATETABLE Orders ( OrderID INT NOT NULL, CustomerID VARCHAR(15) NOT NULL, OrderDate DATETIME NOT NULL ) ONpsYak(OrderDate) GO CREATECLUSTERED INDEX IX_OrderID ONOrders ( OrderID ) CREATENONCLUSTERED INDEX IX_OrderDate ONOrders ( OrderDate ) INCLUDE( OrderID, CustomerID ) GO INSERTOrders ( OrderID, CustomerID, OrderDate ) SELECT1, 'Peso', '20011225' UNIONALL SELECT2, 'Jennie', '20020314' SELECTOrderID, OrderDate FROMOrders WHEREOrderDate = '20011225' SELECTOrderID, OrderDate FROMOrders WHEREOrderID = 1 DROPTABLE Orders DROPPARTITION SCHEME psYak DROPPARTITION FUNCTION pfOrderDate
Read more →
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.
Read more →
Every now and then I see sites where commands are concatenated and sent to database server. The author must really trust the user inputs!
For every system built this way, you can expect at least one attack with SQL injection.
Read more →
The last two days I have been involved in a rather interesting discussion. The original poster wanted a fast way to get missing date ranges in a series of date pairs.
Read more →
SQL Server 2005 introduced the new DMV views. They are great and a real improvement to debug and optimize queries.
I find sys.dm_db_index_physical_stats very useful and often write this type of code
Read more →
http://blogs.technet.com/dataplatforminsider/archive/2008/04/15/sql-server-2005-sp3-coming-soon.aspx
Read more →
Find it here here http://support.microsoft.com/default.aspx/kb/949095/
Read more →
Soon available here http://support.microsoft.com/default.aspx/kb/949095/
Legacy Comments
Carlos Eduardo Teixeira de Morae
2008-04-24
re: Cumulative update package 7 for SQL Server 2005 Service Pack 2 i´m need it!
Read more →
I just played around with some different techniques to fetch relevant data from XML content.
DECLARE@XMLString XML, @Search VARCHAR(50) SELECT@XMLString = ' <Customers> <Customer> <FirstName>Kevin</FirstName> <LastName>Goff</LastName> <City type="aca">Camp Hill</City> </Customer> <Customer> <FirstName>Steve</FirstName> <LastName>Goff</LastName> <City type="acb">Philadelphia</City> </Customer> </Customers>', @Search = 'Camp Hill' -- Get all customers living in Camp Hill SELECTcust.
Read more →
This is an updated version for SQL 2005 and later to search all code for a specific keyword SELECT p.RoutineName, 'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec] FROM ( SELECT OBJECT_NAME(so.
Read more →
I just helped a guy here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98346 with schema and thought that someone could benefit from this code
exec
sp_MSforeachtable"PRINT '? modify'; ALTER SCHEMA new_schema TRANSFER ?; IF @@ERROR = 0 PRINT '?
Read more →