Stop using sp_hexadecimal & sp_help_revlogin
Replace sp_hexadecimal and sp_help_revlogin with a more maintainable way to script SQL Server logins.
Continue readingBrowse the archive
Databases
Identify all tables with an identity column
Question Is there a query I can run that will show me all tables with an identity column? Trying to figure out if there are any identity based tables whose current identity is nearing upper bound of int32 or int64.
Continue readingQuestion Regarding ISDATE Function and DATENAME Functions
Question We have had an issue this morning with a report failing due to the following error: "Conversion failed when converting date and/or time from character string" The issue is that in the database we had a value (in a VARCHAR field) of: '31.07/2021'
Continue readingSQL Server 2012 equivalent of AT TIME ZONE capability
Question Is there a way in SQL Server 2012 to get what the server time zone would be on a particular date/time? In SQL Server 2016, you have AT TIME ZONE, but that's not available in 2012. I have a datetimeoffset and I need to convert it to the server's …
Continue readingWhy does SQL Server convert floats to scientific notation?
Question I came across some weird behavior: While passing a float value into a varchar column, the values are getting converted from integers into scientific notation, and it's that scientific notation that gets stored as a string. if …
Continue readingOLE DB provider "MSOLEDBSQL" with SQL Server not supported?
Question I have been using linked server with the old provider (SQLNCLI) without any issue, as recommended by Microsoft, I'm planning to switch to new provider (MSOLEDBSQL). I'm able to add linked server using following T-SQL after installing the drivers EXEC …
Continue readingWhen to partition table in SQL Server based on the amount of memory on server
Question I understand that table partitioning is mostly done for data management. I understand that table maintenance becomes more difficult for large tables, since e.g. an index rebuild cannot fit in memory or because e.g. sorts scale with O(n*log(n)) and …
Continue readingAn expression of non-boolean type specified in a context where a condition is expected, near 'AND'
Question ALTER PROCEDURE [dbo].[usp_BaseScan_CycleCount_Report] @StartDate varchar(10), @EndDate varchar(10) AS BEGIN IF @StartDate AND @EndDate IS NOT NULL SELECT * FROM dbo.CycleCountHistory WHERE RecordDate BETWEEN @StartDate AND @EndDate ELSE SELECT * …
Continue readingSQL Server Configuration - Disabling the "Allow Remote Connections to this Server" Option
Question My organization had a security audit and was told that we need to DISABLE the "allow remote connection to this server" option for all of our SQL Servers. What impact would this have on connection to the SQL Server via Management Studio (SSMS) from a …
Continue readingHow do I preserve leading zeros for varchar field?
Question When saving a number with leading zeros into a string variable or column, leading zeros are truncated. When I run this query: declare @varchar as nvarchar(50) set @varchar = 011 print @varchar The result I get is 11. Is there any way to get 011?
Continue reading