sp_lock with order by and object name
A kind of sp_lock with an ORDER BY possibility. It gives also the name of the locked object (table) if executed in the same database.
2004-10-22 (first published: 2004-07-26)
735 reads
Scripts
Select from x th row next y rows
This code will show rows from 17th and then next 3 ordered by name from table authors in database pubs. Table must have primary key and of course ordered column. You can change value 'from' in line 'SET ROWCOUNT 17' and value 'next' (how many rows)in line SELECT TOP 3. It is good to show […]
2004-10-21 (first published: 2004-07-29)
225 reads
Convert int IP-address to dot-notation
integer IP-address converted to varchar dot-notationUsage SELECT dbo.IPNumberToString(-2037012288)
2004-10-20 (first published: 2004-07-29)
451 reads
Modification to Automate Audit Trigger Generation
This is a modification to Automate Audit Trigger Generation at http://www.sqlservercentral.com/scripts/contributions/1073.asp by walkerjet. The changes were made to accommodate tables using different types for their primary keys, (i.e. int, smallint, char, etc.), add the ModifiedById and DTStamp columns, exclude legacy tables that do not have a primary key defined, exclude fields of type text, ntext, […]
2004-10-19 (first published: 2004-07-29)
375 reads
Julian and Gregorian Conversion Functions
After seeing a thread in the forums about converting a Julian date to Gregorian, I decided to write these functions. There are two functions in the script, getJulian and getGregorian. getJulian accepts a datetime parameter and returns the Julian date as an integer. getGregorian accepts an integer and returns the Gregorian date as datetime. A […]
2004-10-18 (first published: 2004-08-03)
1,149 reads
From a Delimited String, fetching the nth Value
This User defined Function will provide you the facility of fetching the nth Value from a Delimited string. The Parameter for the function which you have to pass is, the Delimited String, the Delimiter of the string, nth Position of the string. In this function , you can dynamically change the Delimiter as well as […]
2004-10-15 (first published: 2004-08-04)
1,461 reads
Automatically Sets Default on Columns
If you set up a default on a column AFTER data has been entered, then this procedure will apply the default to all NULL values within that column. Limitations: It only works with numeric values right now.You will also need the INSTR function, which you can also get from this site.This is my first […]
2004-10-13 (first published: 2004-07-22)
114 reads
check rerun status of failed/cancelled jobs
There are times when you have mulitple job failures and need to find out in a quick way which jobs/steps failed and what their rerun statuses are. This script creates a stored procedure in the msdb db to help you find out the statuses of these jobs.
2004-10-12 (first published: 2004-07-21)
159 reads
Utility proc for updating (sub) sequence columns
This is a utility proc that I use a lot for datawarehouse transformation/load processing. This is a generic proc for resequencing an integer column in sorted order within a given key combination.Note that 'key' is used here in a general context and not specific, that is there doesn't have to be any keys or indexes […]
2004-10-12
100 reads
Counting occurrences of a substring in a string
Modification of the script entitled "Counting occurrences in a string" by thomasun.This version, packaged as a UDF, is not limited to searching for single characters - substrings can be counted.
2004-10-11 (first published: 2004-07-20)
308 reads
Blogs
Keeping Track of my ConEmu CLIs
By Steve Jones
I use ConEmu for my terminal interface. I’m still on Windows 10 at home,...
10 Decisions to Make Before You Create A Fabric Workspace
Creating a Fabric workspace takes about 30 seconds. Restructuring workspaces after people have built...
Flyway Tips: Object History
By Steve Jones
It’s a small change, but a handy one. Flyway Desktop (FWD) now includes the...
Forums
Adding new column with DEFAULT II
Comments posted to this topic are about the item Adding new column with DEFAULT...
Advanced Deployment Scenarios: Stairway to Reliable Database Deployments Level 7
Comments posted to this topic are about the item Advanced Deployment Scenarios: Stairway to...
You Need a DBA Pipeline
Comments posted to this topic are about the item You Need a DBA Pipeline
Question of the Day
Adding new column with DEFAULT II
Which number did the two COUNT(*) return:
DROP TABLE IF EXISTS #tmp CREATE TABLE #tmp (id INT NOT NULL) INSERT INTO #tmp (id) SELECT gs.value FROM GENERATE_SERIES(1, 5) AS gs ALTER TABLE #tmp ADD my_value INT NOT NULL CONSTRAINT df_tmp_my_value DEFAULT 1 SELECT COUNT(*) FROM #tmp AS t WHERE my_value = 1 ALTER TABLE #tmp DROP CONSTRAINT df_tmp_my_value ALTER TABLE #tmp ADD CONSTRAINT df_tmp_my_value DEFAULT 2 FOR my_value SELECT COUNT(*) FROM #tmp AS t WHERE my_value = 1See possible answers