October 2007 Blog Posts
In this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 I gave a suggestion how to get a more detailed error message when working with OPENROWSET.
But the same thinking is applicable for LINKED SERVER and other "outer referenced" data retreival methods.
The OPENROWSET syntax with Jet provider does not give you full error description when problem occurs, such as permission errors.
-- Using this code for a file with no appropriate permissions throws a general error
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;HDR=No;IMEX=0;Database=\\datastorage\excel\book2.xls', 'select * from [Sheet1$a1:q50]')
If you have some error and you don't understand why, try using MSDASQL provider temporarily.
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\datastorage\excel\book2.xls',
...
DECLARE @Sample TABLE (Col1 VARCHAR(6), Col3 VARCHAR(200))
INSERT @Sample
SELECT '123', '125,124,126' UNION ALL
SELECT '124', '127,21,245'
--SELECT Col1,
-- Data
--FROM @Sample
--CROSS APPLY fnParseList(',', Col3)
SELECT a.Col1,
SUBSTRING(',' + a.Col3 + ',', n.Number + 1, CHARINDEX(',', ',' + a.Col3 + ',', n.Number + 1) - n.Number - 1) AS [Value]
FROM @Sample AS a
INNER JOIN master..spt_values AS n ON SUBSTRING(',' + a.Col3 + ',', n.Number, 1) = ','
WHERE n.Type = 'p'
AND n.Number > 0
AND n.Number < LEN(',' + a.Col3 + ',')
-- Prepare sample data
DECLARE @Accounts TABLE (AccountNumber CHAR(11), ParentAccountNumber CHAR(11))
INSERT @Accounts
SELECT '100-000-000', NULL UNION ALL
SELECT '100-001-000', '100-000-000' UNION ALL
SELECT '100-002-000', '100-000-000' UNION ALL
SELECT '100-002-001', '100-002-000' UNION ALL
SELECT '100-002-002', '100-002-000'
DECLARE @Transactions TABLE (AccountNumber CHAR(11), Amount MONEY)
INSERT @Transactions
SELECT '100-001-000', 1000.00 UNION ALL
SELECT '100-002-001', 500.00 UNION ALL
SELECT '100-002-002', 300.00
-- Setup staging expression
;WITH Yak (AccountNumber, Amount)
AS (
SELECT AccountNumber,
SUM(Amount) AS Amount
FROM @Transactions
GROUP BY AccountNumber
UNION ALL
SELECT a.ParentAccountNumber,
y.Amount
FROM @Accounts AS a
INNER JOIN Yak AS y ON y.AccountNumber = a.AccountNumber
)
-- Show the expected resultset
SELECT COALESCE(AccountNumber, 'All accounts') AS AccountNumber,
SUM(Amount) AS Amount
FROM Yak
WHERE AccountNumber IS NOT NULL
GROUP BY AccountNumber
ORDER BY CASE
WHEN AccountNumber...
Let’s play with the new OUTPUT operator!
-- Setup TableA & TableB
CREATE TABLE #TableA
(
i INT
)
CREATE TABLE #TableB
(
i INT
)
CREATE TABLE #TableC
(
iOld INT,
iNew INT
)
-- Check TableA and TableB
SELECT 'A' AS [Table], * FROM #TableA
UNION ALL
SELECT 'B' AS [Table], * FROM #TableB
-- Insert into TableA
INSERT #TableA
OUTPUT inserted.i
INTO #TableB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
-- Check TableA and TableB
SELECT 'A' AS [Table], * FROM #TableA
UNION ALL
SELECT 'B' AS [Table], * FROM #TableB
-- Delete from TableA
DELETE a
OUTPUT 10 * deleted.i + 49
INTO #TableB
FROM #TableA AS a
WHERE i = 2
-- Check TableA and TableB
SELECT 'A'...