Visual Studio SQL forum only returns 100 values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Visual Studio SQL forum only returns 100 values

Author

Topic

flextera
Yak Posting Veteran

50 Posts

Posted - 2014-09-09 : 14:46:44

I have a Visual Studio 2013 program running that gets it's data from a SQL dataset. But, when I fill in my start date and end date text boxes and click get results it only shows 100 results, not EVERY result. Any help would be appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-09 : 15:33:02

How about running your query in Management Studio instead?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

flextera
Yak Posting Veteran

50 Posts

Posted - 2014-09-09 : 15:41:35

Management Studio does the same thing. Pulls 100 records.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-09 : 15:44:23

If Management Studio is showing the same thing, then the issue is with the query. Post the query, sample data and expected result set.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

flextera
Yak Posting Veteran

50 Posts

Posted - 2014-09-09 : 15:50:25

I dont just want to change a query, I want to make my whole Database move away from this 100 row limitation.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-09 : 15:54:38

The database does not have this limitation. You can return as many rows as you want from SQL Server.

Something is "wrong" with your query or the objects it is calling (such as view that is using TOP 100).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

flextera
Yak Posting Veteran

50 Posts

Posted - 2014-09-09 : 16:23:49

But it's doing this for EVERY query, even if I have no limitations in the query. In the properties it says rows returned "100" every time. I have a feeling that the view is using the TOP 100. How can I stop this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-09 : 16:29:18

But are you referencing any views? If not, then you've got something set in your client that is limiting the number of rows. In Management Studio, click Tools..Options..Query Execution. What does SET ROWCOUNT show?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-09 : 16:35:47

You updated your last reply after I replied. So you are using a view in your query? Have you looked at the view's definition?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

flextera
Yak Posting Veteran

50 Posts

Posted - 2014-09-09 : 17:08:10

ROWCOUNT is set to 0

flextera
Yak Posting Veteran

50 Posts

Posted - 2014-09-09 : 17:11:05

I believe this database has all the "views" locked in someway; only allowing the user to get 100 rows. Completely confused.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-09 : 17:38:56

I would contact the DBA to determine if the views are using TOP 100 or if there are LOGON triggers in place to limit the row count to 100.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

flextera
Yak Posting Veteran

50 Posts

Posted - 2014-09-10 : 09:27:54

I feel that this could be the only option. Are there workarounds for this type of thing? Where would I find if there is some sort of locked rights? This is an old database and the creator is long since gone.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-10 : 12:26:48

quote:
Originally posted by flextera

I feel that this could be the only option. Are there workarounds for this type of thing? Where would I find if there is some sort of locked rights? This is an old database and the creator is long since gone.

Limiting the number of records in every view to 100 is not a normal or routine thing that people do. And because of that there are no best practices or well-known techniques for doing that. So you have to find out what is causing it.

If you have enough permissions, do a simple test as shown below:

CREATE TABLE test(x FLOAT);
GO

INSERT INTO test SELECT RAND();
GO 120

SELECT * FROM test; -- how many rows does this return?
GO

CREATE VIEW dbo.viewTest
AS
SELECT * FROM test;
GO

SELECT * FROM dbo.viewTest; -- how many rows does this return?
GO

DROP VIEW viewTest;
DROP TABLE test;

If this test returns 120 rows from the view and the table, then the issue has to be in the existing views. Open up the code for the views and see what it is doing to limit the number of records and fix that (in each view - there is no other way).

flextera
Yak Posting Veteran

50 Posts

Posted - 2014-09-10 : 12:32:08

Msg 111, Level 15, State 1, Line 4
'CREATE VIEW' must be the first statement in a query batch.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.viewTest'.
Msg 3701, Level 11, State 5, Line 2
Cannot drop the view 'viewTest', because it does not exist or you do not have permission.

flextera
Yak Posting Veteran

50 Posts

Posted - 2014-09-10 : 12:32:28

That is the error code I get when I try to run your test.

I have a red squiggle under the "120" and under the "CREATE VIEW dbo.viewtest"

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-10 : 12:46:47

Edited the script in my previous posting. See in RED.

flextera
Yak Posting Veteran

50 Posts

Posted - 2014-09-10 : 12:51:31

THanks. I still have red under the "120" But it queried okay and came back with 120 results. How would I get to the code of these views?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-10 : 13:02:38

quote:
Originally posted by flextera

THanks. I still have red under the "120" But it queried okay and came back with 120 results. How would I get to the code of these views?

You can use sp_helptext to see the view's code:

exec sp_helptext 'viewnamegoeshere'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

flextera
Yak Posting Veteran

50 Posts

Posted - 2014-09-10 : 13:14:21

I see the code for my view. It's only four lines and looks too simple. Also, there is NOTHING about limiting the rows. Where else could I look for a certain limitation on rows regarding views, tables, permissions when querying?

flextera
Yak Posting Veteran

50 Posts

Posted - 2014-09-10 : 13:17:40

BTW. It says it is selecting from [INSQL] is that normal? and what is INsql?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources