Am I dropping records?

Question

Am I dropping records?

jimgleason512

SSC Rookie

Points: 25
More actions
November 28, 2012 at 12:46 pm

#270710

Hello Forum,
I am running T-SQL on SQL Server 2008 and encountered a nagging issue. When I copy the SQL output (^A, ^C) and paste it into Excel 2010, I seem to get 2 fewer records (based on the Excel row) than returned by the query run in SQL Server Management Studio. Specifically, the server returns 193, 674 records. I paste these starting in row 2 (below the header row) and the last record appears on row 193,673. It gets curiouser. I ran a second SQL that returned 85, 290 rows. Copying this data set resulted in 85, 289 rows in Excel. Both outputs are small compared to a lot of the quanties dealt with by SQL but the number is not quite small enough to seek out the the difference manually. Any ideas, including suggestions about a mistake I might be making? Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Do you perhaps have a second select running after the main query that gives you rowcounts or the like? These rows would be added to the first 'total rowcount' you see before you click into the grid to copy/paste.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

jimgleason512 SSC Rookie Points: 25 More actions · Answer 2

Thanks, Evil Kraig. Your answer makes perfect sense for the larger SQL, which actually 'UNIONs' 3 queries use multipple table-joins, MAX(), placeholder columns and all sorts of complications.

The 2nd SQL, tho, is very basic:

"SELECT clnum, clname1, clopendt, mmatter, mdesc1, mopendt, mclosedt

FROM client, matter

WHERE clstatus='C'

AND clopendt < '01/01/2002'

AND clnum = mclient

AND mclient <> '000001'

ORDER BY mmatter, mclosedt"

I purposely ran a very simple one as sort of a control on the first one. I actually thought of an EOF marker or something similar but thought maybe that's a little too convenient. Thanks for the attention. Your help and anything further is greatly appriciated.

Jim

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

jimgleason512 (11/28/2012)
Thanks, Evil Kraig. Your answer makes perfect sense for the larger SQL, which actually 'UNIONs' 3 queries use multipple table-joins, MAX(), placeholder columns and all sorts of complications.
The 2nd SQL, tho, is very basic:
SELECT clnum, clname1, clopendt, mmatter, mdesc1, mopendt, mclosedt
FROM client, matter
WHERE clstatus='C'
AND clopendt < '01/01/2002'
AND clnum = mclient
AND mclient <> '000001'
ORDER BY mmatter, mclosedt

Just to confirm, that's the only code in the script window? That's highly odd.

I purposely ran a very simple one as sort of a control on the first one. I actually thought of an EOF marker or something similar but thought maybe that's a little too convenient.

Nah, there's no EOF markers or anything like that included into the rowcount, at least not for Results to Grid. To confirm, are you using Results to Grid or Results to Text? Also to confirm, you're reading the rowcount here and there's no other grids:

The general premise has me assuming that somthing's gone... odd, and I'm really just trying to make sure we're talking apples to apples and there's no extras hanging around as a gotcha.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

jimgleason512 SSC Rookie Points: 25 More actions · Answer 4

Again, a helpful response. Thanks.

I was a UNIX/Informix DBA through the 1990's. My only work with SQL Server had been data transfer but now that I'm working with a large billing system, I need to get morre into the nuts & bolts of the engine that curently predominates.

My status bar gives the record count and shows me using version 9.0, service pack 2. I wasn't even aware of options for, or the specific differences between, 'grid' and 'text' My output fits very smoothly into Excel columns. All I have to do is format the columns properly (date, text, etc.) beforehand. I guess I'm copying delimited text. I'm not even sure what features are offered by 'grid' but when I drill down on SQL's menu thru Tools, Options the 'Text' branch offers a chk-box that says to "include the header in the result set". That explains the extra 'row' in the simple SQL.

Once I have the data in Excel, I manipulate it aggressively with VBA. You've been a tremendous help in letting me feel confident in the foundations of what I'm doing. I'm sure you'll be seeing more of my tyro questions popping up in the near future. I provide custom solutions to problems my clients haven't seen before so I'll thank you once again for guiding me toward confidence (once I buttress it with some reading) that I can pass on to various audiences.

Best Regards, Jim

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

jimgleason512 (11/28/2012)
Again, a helpful response. Thanks.

My pleasure. I'm always happy to help those who are willing to put in the time first and just get stuck. We all end up there.

I wasn't even aware of options for, or the specific differences between, 'grid' and 'text' My output fits very smoothly into Excel columns. All I have to do is format the columns properly (date, text, etc.) beforehand. I guess I'm copying delimited text. I'm not even sure what features are offered by 'grid' but when I drill down on SQL's menu thru Tools, Options the 'Text' branch offers a chk-box that says to "include the header in the result set". That explains the extra 'row' in the simple SQL.

Actually, it explains the two rows. 🙂 The Row with headers and then the 'separation' row that looks like:

PortfolioBaseID PortfolioID PortfolioGroupID

--------------- ----------- ----------------

Once I have the data in Excel, I manipulate it aggressively with VBA.

If you let us know what you're doing to the data, we can probably help you out with your queries to make your results end to end faster. A lot of translating the programming into SQL is just knowing the right keywords.

You've been a tremendous help in letting me feel confident in the foundations of what I'm doing. I'm sure you'll be seeing more of my tyro questions popping up in the near future.

My pleasure, and welcome to the forums. You'll be a very welcome addition to our community.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA