Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

October 2007 Blog Posts

Getting errors when working with Excel and SQL Server

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', ...

posted @ Wednesday, October 24, 2007 8:01 AM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Simulating cross apply with CSV-records in SQL Server 2000

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 + ',')

posted @ Monday, October 15, 2007 8:25 AM | Feedback (7) | Filed Under [ Algorithms SQL Server 2000 ]

Sum up a tree hierachy in SQL Server 2005

  -- 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...

posted @ Thursday, October 04, 2007 10:48 AM | Feedback (4) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

New SQL Server 2005 OUTPUT operator

  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'...

posted @ Wednesday, October 03, 2007 6:44 PM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 ]

Powered by:
Powered By Subtext Powered By ASP.NET