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 →
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 →
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 →
This is what I pondered about today. Maybe I also will have some time to test it.
CREATE PROCEDURE dbo.uspPaginate ( @PageNumber INT, @RecordsPerPage TINYINT = 50 ) AS SET NOCOUNT ON DECLARE @MaxRows INT SET @MaxRows = @PageNumber * @RecordsPerPage SELECT SomeColumns FROM ( SELECT TOP (@RecordsPerPage) SomeColumns FROM ( SELECT TOP (@MaxRows) SomeColumns FROM YourTable ORDER BY SomeCase ASC/DESC ) ORDER BY SomeCase DESC/ASC ) ORDER BY SomeCase ASC/DESC Topic is here http://www.
Read more →
I worked with this topic recent weekend and posted the final functions here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454 The general idea is to have a generic purge functionality.
Legacy Comments
Jason
2008-02-16
re: Finding table reference levels and simulating cascading deletes I understand why some people might want this, but I think it is truly a bad idea to even do something like this.
Read more →
After a good nights sleep when almost all pieces fit together here weblogs.sqlteam.com/peterl/archive/2008/02/06/Curiosity-found.aspx
I realized this morning that this behaviour also explains why there are gaps in identity sequences when inserting a record that violates a contraint.
Read more →
CREATEFUNCTION dbo.fnSplitType ( @Data VARCHAR(200), @PartSize TINYINT ) RETURNSVARCHAR(8000) AS BEGIN DECLARE @Result VARCHAR(8000), @Alpha TINYINT, @OldPosition SMALLINT, @NewPosition SMALLINT SELECT @Result = '', @Alpha = 1, @OldPosition = 1, @NewPosition = 1 IF @Data LIKE '[0-9]%' SELECT @Result = REPLICATE(' ', @PartSize), @Alpha = 0 WHILE @NewPosition < LEN(@Data) SELECT @NewPosition = CASE @Alpha WHEN 1 THEN PATINDEX('%[0-9]%', SUBSTRING(@Data, @OldPosition, 8000)) ELSE PATINDEX('%[a-z]%', SUBSTRING(@Data, @OldPosition, 8000)) END, @NewPosition = CASE @NewPosition WHEN 0 THEN LEN(@Data) ELSE @OldPosition + @NewPosition - 2 END, @Result = @Result + CASE @Alpha WHEN 1 THEN LEFT(LTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)) + REPLICATE(' ', @PartSize), @PartSize) ELSE RIGHT(REPLICATE(' ', @PartSize) + RTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)), @PartSize) END, @Alpha = 1 - @Alpha, @OldPosition = @NewPosition + 1 RETURNRTRIM(@Result) END Here is the code to test with DECLARE@Sample TABLE (Info VARCHAR(200)) INSERT@Sample SELECT'S0C 4610' UNIONALL SELECT'S9C 113' UNIONALL SELECT'S1C 462' UNIONALL SELECT'112' UNIONALL SELECT'113' UNIONALL SELECT'MM20BC' UNIONALL SELECT'SSC 113' UNIONALL SELECT'SSC 201' UNIONALL SELECT'SSC 461' UNIONALL SELECT'SSC 4610' UNIONALL SELECT'SSC 462' UNIONALL SELECT'SSCPZ202C' UNIONALL SELECT'Z1' UNIONALL SELECT'Z100' UNIONALL SELECT'ZZ' SELECTInfo FROM@Sample ORDERBY dbo.
Read more →
I didn't read this until I noticed Mladens link. http://weblogs.sqlteam.com/jhermiz/archive/2007/12/17/What-If-The-Dream-Company.aspx
Legacy Comments
Jon
2007-12-17
re: Thanks Jon! Definately well deserved!
Read more →
Today, I was involved in an interesting discussion.
Someone asked for a moving average solution. I joined the discussion late. The previous solutions and mine were all set-based and very slow.
Read more →
In this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926I gave a suggestion how to get a more detailed error message when working with OPENROWSET.
But the same thinking is applicable for LINKED SERVER and other "outer referenced" data retreival methods.
Read more →
-- Prepare sample data DECLARE@Accounts TABLE (AccountNumber CHAR(11), ParentAccountNumber CHAR(11)) INSERT@Accounts SELECT'100-000-000', NULLUNION ALL SELECT'100-001-000', '100-000-000' UNIONALL SELECT'100-002-000', '100-000-000' UNIONALL SELECT'100-002-001', '100-002-000' UNIONALL SELECT'100-002-002', '100-002-000' DECLARE @Transactions TABLE (AccountNumber CHAR(11), Amount MONEY) INSERT@Transactions SELECT'100-001-000', 1000.
Read more →