Why does this simple SQL query from SQL Server 2000 have different results in SQL Server 2008?

Question

Using MS SQL 2000 it was possible to have a query such as:

SELECT (Code + ' = ' + EdiNumber + ',') AS MyResult FROM tblCountry

Which would give you a list of results like:

MyResult
========
ZW = 263,
ZA = 27,
...

However, in MS SQL 2008 that query returns:

-1 records affected

Does anyone know a) Why? and b) How to get the SQL 2000 result from SQL 2008?

UPDATE

Im just using a standard ASP.NET connection string to connect to the database using a console to post the query:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDB.mdf;Integrated Security=True;User Instance=True; Database=MyDB

Have you some security restrictions? Try to execute query from 'sa' — Dewfy, Dec 08 '11 at 09:40
`-1 records affected` is database output (metadata), not your query result. You're probably looking in the wrong window in whatever tool you're running from.. — StevieG, Dec 08 '11 at 10:10
@StevieG: When I run a `SELECT Code FROM tblCountry` the results are shown fine — Jimbo, Dec 08 '11 at 10:12

score 0 · Answer 1 · answered Dec 08 '11 at 10:25

0

PROBLEM SOLVED

-1 records affected was clearly the non-privileged result because all that was actually happening was an error in the query whilst trying to concatenate a string (Code) and an int (EdiNumber)

The correct query should have been:

SELECT (Code + ' = ' + CAST(EdiNumber AS VARCHAR) + ',') as MyResult FROM tblCountry

Once I got that correct, the query worked fine from the original console.

answered Dec 08 '11 at 10:25

Jimbo

22,379
42
117
159

1

Your code seems to bury exception messages. You don't need any particular privileges to see those. – Martin Smith Dec 08 '11 at 10:37

Why does this simple SQL query from SQL Server 2000 have different results in SQL Server 2008?

1 Answers1