Reset Identity Column with sp_IdentReSeed
Getting tired of dropping tables just re-seed the identity column? Remember, TRUNCATE TABLE doesn't work with foreign keys on tables. --EXAMPLE:DELETE FROM MyTablesp_Reseed 'MyTable'
2002-04-22
1,084 reads
Scripts
Send True SMTP Mail using CDOSys instead of CDONTS
SQL Server 2000 and Windows 2000 Only.Microsoft suggest using CDOSys rather than CDONTS. CDOSys stands for Collaboration Data Objects for Windows 2000 Subsystems. Below is a script that allows you do do just that. NO EXCHANGE REQUIRED!! Sample sends the user HTML Mail!Be sure to change @ServerIPAddr to your SMTP server.
2002-04-22
1,918 reads
sp_CHARINDEX
Here's a stored procedure version of udf_CharIndex for SQL Server 7.Sample Usage: DECLARE @iPos INT DECLARE @nRet INT EXECUTE @nRet = sp_CharIndex 'W', 'Hello World', @nPos = @iPos OUTPUT IF @iPos > 0 PRINT 'found' ELSE Print 'not found' PRINT @iPos --Only uses on character.
2002-04-22
551 reads
View/Edit ASP Script from From SQL Server 7.0/2000
Ever want to display (and edit) ASP code from the original .asp file? (like PHP's PHPInfo() function).To setup: * Just create a .asp with SQL Server connectivity. * Change @IISHostNameWWWPath to match your servername -OR- if SQL Server is on the same machine, use the next line instead. (see […]
2002-04-22
547 reads
Change the User Defined Data type
User Defined Data Types (UDF) is one of the useful concepts in SQL Server. However UDF can't be modified after they are being used. You would have to remove your User Defined Data Type from each table before you can change it. This will be a big job under certain circumstances.This procedure changes all tables […]
2002-04-19
1,037 reads
Display columns information
This procedure will return useful columns information for VB developers. It shows column name, data type, size, vbtype, checktable (refferenced table) and flags: NoNull, IsIdentity, IsPK.Calling sample: mysp_columns 'customers'
2002-04-19
711 reads
Case sensitive CHARINDEX Function
Microsoft missed the code boat on this one. Use this function to find the true index of set of characters.
2002-04-18
588 reads
Script to list all user and roles
This script list all the users defined for each database on the server, it give the database name, the login name and the role defined.Its use full to have quick view of witch users are dbo on witch databaseEnsure that you have the result in grid in query analyzer, and just cut and paste the […]
2002-04-18
961 reads
Write Reports from SQL to Disk (Even HTML!)
Ever need to write reports out to a folder? I have found that creating output files are SA-WEET! (and easy too) The sample script uses BCP to create an HTML file.This process works well for reports that need to be generated nightly and take too long to run in real time. Use an SMTP mail […]
2002-04-18
4,408 reads
Decimal to Binary Conversion for event scheduling
This procedure was written to create an event schedule where the schedule may have non-consecutive days. The input for the procedure is START DATE, END DATE, and DAYMAP The DAYMAP input parameter is a decimal number whose binary representation describes the schedule days over potentially a 4 week period (read from right to left). […]
2002-04-17
313 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