queries not returning all results

M

mdeemer

Perch
folks,

ive got an exact copy of my SQL database on my home server. However, when i run a query on my home server i get all the results i should do, but when i run the exact same one on MSSQL1 i dont get all the results.

The query is exactly the same, but i dont get all results displayed.

Its been bugging me all night.
 
are you 100% sure you have all your records?

If you're running MSDE on your home server, there can be issues with copying data to an MSSQL2K server.

try the queries in EM also
 
it shows all the records from my home server, but not when i query MSSQL1.

For example, i have a table as such...

ID,fname,sname

Data might like like this...
1 | John | Smith
2 | John | Jones
3 | John | White
4 | Joe | Bloggs

Now, if i open the table on either server i get all the results. BUT, if I run a query such as "WHERE fname='John'" on MSSQL1 i might get 2 results instead of the expected 3, but if i run the same view on my test server then I get all 3 results.

Both databases are exactly the same.

Is there a limitation for records returned in a query using MSSQL1?
 
Did i say that if I FILTER a table rather than query it then i get all the results I should do!

very confused

?(
 
I've never noticed such a limitation.
Depending on the method you use to receive these results, a server may return all results as a single bulk or in multiple rowsets.

Perhaps something like this is going on? For example, if you use the ODBC API and you forget to call SQLFetch until it returns SQL_NO_DATA instead of SQL_SUCCESS, you won't have all the rows :)
 
lol, that means nothing to me.

Dont know what the method is, but its between WIN8 and MSSQL1 again (think its win8 tho im not sure).

If i use the exact same Access Project file to access my test server then it works fine, but on MSSQL1 its not - like its limiting it (Gets to the same record each time).

????
 
mdeemer said:
BUT, if I run a query such as "WHERE fname='John'" on MSSQL1 i might get 2 results instead of the expected 3
Click to expand...
Try "WHERE fname LIKE '%John%'"

Maybe there's a rogue space or other non-displaying character.
 
its actually using a foreign key the query, so its loading all linked records.

Works fine on my test server too with the same data, running MS SQL 2000 and IIS.
 
i have, and it does return them using the SQL server on my PC but not MSSQL1.

The tables are linked in the query.

Give you an example.

Table1:- IDtbl1, fname, sname
Table1:- IDtbl2, IDtbl1, col1, col2

In my query, im linking IDtbl1 in both tables. Im running a search on sname for example in table1 and showing results from col1 and col2 in table 2.

Not all results get displayed though. As i said, if i load the recordset as tho whole of table2 I can filter it and get the correct records, but not if i run WHERE in a query.

???
 
Stuff like that works fine on MSSQL1 and I don't see how the webserver would mess it up either..

I don't think we're likely to find out what's going on without specifics.
 
You must log in or register to reply here.
Back
Top