Creating a DTS package with a Perl Script
This script uses the DTS object model to create a simple package, execute the package, and properly check for any errors.
2005-05-19 (first published: 2005-04-01)
268 reads
Scripts
HexToSmallInt
Hans asked if it could be faster. This is about 10% faster; not much. His is admittedly more readable, and mine will act very strangely with invalid hex digits.How does it work? I'm converting the string '1234' to the value 0x31323334 (for example), then subtracting '0000' so that it is 0-based in each byte (CONVERT(INT,0x30303030) […]
2005-05-16 (first published: 2005-05-04)
151 reads
SQL Blocking and Blocked Proc + Notifi - Part2
Based on the script by Paul Delmarche ( http://www.sqlservercentral.com/scripts/contributions/1482.asp ).Updated to now include the spid, username, blocked and blocking SQL statements in the email (still logged to table).Schedule the procedure to run at defined intervals (I use 2 minutes)
2005-05-12 (first published: 2005-05-02)
1,443 reads
Table Sizes in database
This script uses the same information sp_spaceused does. It just formats it in a different way.
2005-05-10 (first published: 2005-04-26)
211 reads
Restore Multiple Backup Logs
Use This Script to restore Multiple Log backups from a Log Device file. The script will restore all the logs on the device and will leave the databse ready to use after finish. Before running this script you must verify if the database is in No recovery mode or read only state
2005-05-09 (first published: 2005-04-25)
443 reads
Grant EXECUTE permission to users
This procedure will assist in granting execute rights to a database user to all the stored procedures in a database The parameters to this procedure are the databasename and the username to whom EXECUTE rights need to be given to The assumption here is that the user already has access rights to the database mentioned […]
2005-05-05 (first published: 2005-04-19)
233 reads
HexToINT
An optimized function for converting a HEX string into a INT. Built for being faster then other HexToDec converters.Slightly slower then HexToSMALLINT.If there is any faster function to do this conversion, I am intrested of using it instead.
2005-05-04 (first published: 2005-04-19)
298 reads
Script to Move Indexes off of the Primary Filegrou
This is a stored proc that will move your indexes to two new file groups. A Cindex file group is for the clustered indexes and NCindex file group is for non clustered indexes. The parameters for this procedure are @CPATH - the path to the clustered index datafiles @NCPATH - the path to the nonclustered […]
2005-04-29 (first published: 2005-04-15)
396 reads
Get SQL code Blocking and Blocked procs + Notif
This script was created to Identify Query string that is blocked and the one that is blocking with a datetime reference. These information is stored in a history table.It also sends a mail notification without using SQL Mail
2005-04-28 (first published: 2005-04-15)
1,193 reads
Execute SQL-DMO Database method on all databases
This script allows you to specify a method and a server (or multiple servers.) It will then execute the method on every database on the server. With very minor tweaking you could have it search for the servers, then call the server db script with each one, or add another nesting to call tables or […]
2005-04-27 (first published: 2005-04-14)
726 reads
Blogs
AI Helps Me with My Sloppiness
By Steve Jones
I type fairly well. Well, I type fast, but I do wear out a...
Automatic Index Compaction
By ReviewMyDB
Index maintenance has always meant nightly jobs and a window you have to defend....
The Goldilocks problem – Materialized Views
I’m sure you’ve all heard the tale of Goldilocks and the Three Bears, but...
Forums
SQL Art, Part 4: Happy 4th of July — A British DBA's Guide to Celebrating a War We Don't Talk About
Comments posted to this topic are about the item SQL Art, Part 4: Happy...
How We Handled a Vendor Retry That Loaded Twice in Snowflake
Comments posted to this topic are about the item How We Handled a Vendor...
Cognitive Coverage
Comments posted to this topic are about the item Cognitive Coverage
Question of the Day
Getting the Average
I have this data in the dbo.Commission table in a SQL Server 2022 database.
salesperson commission Brian 12 Brian 16 Andy 7 Andy 14 Andy 21 Steve 20 Steve NULLAll the data is a varchar, and I decide to run this query to get the totals for each salesperson.
SELECT SalesPerson
, AVG(TRY_PARSE(Commission AS int)) AS TotalCommission
FROM commission
GROUP BY SalesPerson
GO
What average commission is calculated for Steve? See possible answers