Make your life easier with the sqlcmd tool in SQL Server 2005 that automates administration and maintenance via scripting. In this tip, you'll get five useful features and examples to execute sqlcmd commands, such as for utilizing and populating variables, improved error handling and restoring a database.
Many SQL Server environments run DB2 servers, too, and often the two servers must be linked. Here's a step-by-step process that shows how to create a SQL Server linked server using Microsoft's DB2 driver, the OLE DB provider data access tools and the AS400 version of DB2.
I've made several changes to my backup stored procedure, isp_Backup. I fixed a few things and added some much needed features. Here are all of my changes:
The last 3 changes were made to prevent job failures and for recoverability reasons.
I have not gotten a chance to add code to check if a database is being log shipped by SQL LiteSpeed. I haven't received any requests for this code nor do I have a situation like this where I work, so I've been avoiding this code change.
Let me know if you'd like me to add any other features or if you find a bug. After careful consideration of each, I'll make a decision whether or not to include them in the next version.
In the past I've embedded the code in my blog posts, however I'm now going to provide the script instead so that you get the formatted code.
To download the stored procedure, click here.
If you don't have SQL LiteSpeed installed, ignore the warnings in the output when creating this stored procedure. They are warnings and not errors. The stored procedure works with or without SQL LiteSpeed installed.
As a DBA, much of your focus is on tuning SQL Server for peak performance. But have you spent any time tuning the hardware that supports your SQL Server system? Are you using the optimal disk array configuration? Are the disk partitions aligned? This tip discusses how to get your hardware performance in top shape – whether the system is already in operation or it's a new setup.
This problem originated here
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=87&threadid=90916&enterthread=y
and I post the solution here for two reasons.
1) The forum above does not support code tags
2) The common interest is high enough
-- Prepare sample data
DECLARE @Sample TABLE
(
HoleID CHAR(8),
mFrom SMALLMONEY,
mTo SMALLMONEY,
Result SMALLMONEY,
PRIMARY KEY CLUSTERED
(
HoleID,
mFrom
),
Seq INT
)
INSERT @Sample
(
HoleID,
mFrom,
mTo,
Result
)
SELECT 'TWDD0004', 1 , 2 , 0.86
SELECT 'TWDD0004', 3 , 4 , 8.93
SELECT 'TWDD0004', 4 , 5 , 2.78
SELECT 'TWDD0004', 8 , 9 , 1.21
SELECT 'TWDD0004', 10 , 11 , 2.36
SELECT 'TWDD0004', 11 , 12 , 0.86
SELECT 'TWDD0004', 103 , 103.7 , 0.5
SELECT 'TWDD0004', 121.65, 122 , 0.5
SELECT 'TWDD0004', 130 , 131 , 3.65
SELECT 'TWDD0004', 131 , 131.5 , 1
SELECT 'TWDD0004', 132 , 133 , 2.89
SELECT 'TWDD0004', 133 , 134 , 4.02
SELECT 'TWDD0004', 134 , 135 , 0.76
SELECT 'TWDD0004', 138.3 , 139 , 3.15
SELECT 'TWDD0004', 139 , 140 , 3.12
SELECT 'TWDD0004', 140 , 141 , 3.93
SELECT 'TWDD0004', 141 , 142 , 6.48
SELECT 'TWDD0004', 142 , 143 , 0.94
SELECT 'TWDD0004', 155 , 156.15, 0.55
SELECT 'TWDD0004', 164.9 , 165.75, 2.08
SELECT 'TWDD0004', 166.9 , 167.9 , 1.27
SELECT 'TWDD0004', 167.9 , 169 , 0.58
SELECT 'TWDD0004', 170.9 , 171.25, 15
SELECT 'TWDD0004', 185 , 186 , 2.96
SELECT 'TWDD0004', 186 , 187.3 , 0.86
SELECT 'TWDD0004', 187.3 , 188 , 8.15
SELECT 'TWDD0004', 188 , 188.45, 26.1
SELECT 'TWDD0004', 188.45, 189 , 16.5
SELECT 'TWDD0004', 189 , 189.9 , 3.55
SELECT 'TWDD0004', 189.9 , 190.2 , 2.79
SELECT 'TWDD0004', 190.2 , 191 , 1.07
SELECT 'TWDD0004', 191 , 191.85, 1.3
SELECT 'TWDD0004', 191.85, 192.85, 2.22
SELECT 'TWDD0004', 192.85, 193.3 , 0.59
SELECT 'TWDD0004', 193.3 , 194 , 0.79
SELECT 'TWDD0004', 194 , 194.85, 5.98
SELECT 'TWDD0004', 194.85, 195.5 , 0.82
SELECT 'TWDD0004', 195.8 , 197 , 7.03
SELECT 'TWDD0004', 197 , 198 , 3.84
SELECT 'TWDD0004', 198 , 198.3 , 12.1
SELECT 'TWDD0004', 198.3 , 199 , 8.66
SELECT 'TWDD0004', 199 , 200 , 3.53
SELECT 'TWDD0004', 200 , 201 , 3.22
SELECT 'TWDD0004', 201 , 202.1 , 7.22
SELECT 'TWDD0004', 202.1 , 202.55, 1.07
-- Initialize user supplied parameters
DECLARE @WantedValue SMALLMONEY,
@WasteValue SMALLMONEY
SELECT @WantedValue = 4,
@WasteValue = 1
-- Prepare sequencing
DECLARE @mTo SMALLMONEY,
@Seq INT,
@HoleID CHAR(8)
-- Get initial values
SELECT TOP 1 @mTo = mTo,
@Seq = 0,
@HoleID = HoleID
FROM @Sample
ORDER BY HoleID,
mFrom
-- Update sequence
UPDATE @Sample
SET @Seq = Seq = CASE
WHEN HoleID = @HoleID AND @mTo + @WasteValue >= mFrom THEN @Seq
ELSE @Seq + 1
END,
@HoleID = HoleID,
@mTo = mTo
-- Display the desired result
SELECT HoleID,
MIN(mFrom) AS mFrom,
MAX(mTo) AS mTo,
AVG(Result) AS Result
FROM @Sample
GROUP BY HoleID,
Seq
HAVING MAX(mTo) - MIN(mFrom) >= @WantedValue
-- Initialize the search parameter
DECLARE @WantedValue INT
SET @WantedValue = 221
-- Stage the source data
DECLARE @Data TABLE
(
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
MaxItems INT,
CurrentItems INT DEFAULT 0,
FaceValue INT,
BestUnder INT DEFAULT 0,
BestOver INT DEFAULT 1
)
-- Aggregate the source data
INSERT @Data
(
MaxItems,
FaceValue
)
SELECT COUNT(*),
Qty
FROM (
SELECT 899 AS Qty UNION ALL
SELECT 100 UNION ALL
SELECT 95 UNION ALL
SELECT 50 UNION ALL
SELECT 55 UNION ALL
SELECT 40 UNION ALL
SELECT 5 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 250 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 90 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 350 UNION ALL
SELECT 450 UNION ALL
SELECT 450 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 50 UNION ALL
SELECT 50 UNION ALL
SELECT 1 UNION ALL
SELECT 10 UNION ALL
SELECT 1
) AS d
GROUP BY Qty
ORDER BY Qty DESC
-- Declare some control variables
DECLARE @CurrentSum INT,
@BestUnder INT,
@BestOver INT,
@RecID INT
-- If exact single wanted sum, select that item!
IF EXISTS (SELECT * FROM @Data WHERE FaceValue = @WantedValue)
BEGIN
SELECT 0 AS SumType,
1 AS Items,
FaceValue
FROM @Data
WHERE FaceValue = @WantedValue
RETURN
END
-- If productsum is less to the wanted sum, select all items!
IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) < @WantedValue
BEGIN
SELECT -1 AS SumType,
MaxItems AS Items,
FaceValue
FROM @Data
RETURN
END
-- If productsum is equal to the wanted sum, select all items!
IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) = @WantedValue
BEGIN
SELECT 0 AS SumType,
MaxItems AS Items,
FaceValue
FROM @Data
RETURN
END
-- Delete all unworkable FaceValues, keep one greater FaceValue because of oversum.
DELETE
FROM @Data
WHERE FaceValue > (SELECT MIN(FaceValue) FROM @Data WHERE FaceValue >= @WantedValue)
-- Update MaxItems to a proper value
UPDATE @Data
SET MaxItems = CASE
WHEN 1 + (@WantedValue - 1) / FaceValue < MaxItems THEN 1 + (@WantedValue - 1) / FaceValue
ELSE MaxItems
END
-- Update BestOver to a proper value
UPDATE @Data
SET BestOver = MaxItems
-- Initialize the control mechanism
SELECT @RecID = MIN(RecID),
@BestUnder = 0,
@BestOver = SUM(BestOver * FaceValue)
FROM @Data
-- Do the loop!
WHILE @RecID IS NOT NULL
BEGIN
-- Reset all "bits" not incremented
UPDATE @Data
SET CurrentItems = 0
WHERE RecID < @RecID
-- Increment the current "bit"
UPDATE @Data
SET CurrentItems = CurrentItems + 1
WHERE RecID = @RecID
-- Get the current sum
SELECT @CurrentSum = SUM(CurrentItems * FaceValue)
FROM @Data
WHERE CurrentItems > 0
-- Stop here if the current sum is equal to the sum we want
IF @CurrentSum = @WantedValue
BREAK
ELSE
-- Update the current BestUnder if previous BestUnder is less
IF @CurrentSum > @BestUnder AND @CurrentSum < @WantedValue
BEGIN
UPDATE @Data
SET BestUnder = CurrentItems
SET @BestUnder = @CurrentSum
END
ELSE
-- Update the current BestOver if previous BestOver is more
IF @CurrentSum > @WantedValue AND @CurrentSum < @BestOver
BEGIN
UPDATE @Data
SET BestOver = CurrentItems
SET @BestOver = @CurrentSum
END
-- Find the next proper "bit" to increment
SELECT @RecID = MIN(RecID)
FROM @Data
WHERE CurrentItems < MaxItems
END
-- Now we have to investigate which type of sum to return
IF @RecID IS NULL
IF @WantedValue - @BestUnder < @BestOver - @WantedValue
-- If BestUnder is closer to the sum we want, choose that
SELECT -1 AS SumType,
BestUnder AS Items,
FaceValue
FROM @Data
WHERE BestUnder > 0
ELSE
-- If BestOver is closer to the sum we want, choose that
SELECT 1 AS SumType,
BestOver AS Items,
FaceValue
FROM @Data
WHERE BestOver > 0
ELSE
-- We have an exact match
SELECT 0 AS SumType,
CurrentItems AS Items,
FaceValue
FROM @Data
WHERE CurrentItems > 0
For all enthusiasts out there this is how software development cycle works no matter what the project is:
More truisms like the one above can be found here.
