Row_Number() fails to return anything

I am using SQLExpress and want to do some custom paging with a grid but can not get the sproc to produce any results. I don't get any error just a resultset of 0 rows whenever I include the row_number function.

Sample Table:
refID int identity increments by 1
refSubject nvarchar(100)
refBody nText

Sample sproc that works
create proc sp_RefListing as
select refid
, refsubject
from myTable

Sample Sproc that doesn't work:
with OrderedRefList as
(Select refid
,refsubject
,row_number() OVER (order by refsubject) as rownum
from myTable)
select refid
,refsubject
,rownum
from OrderedRefList
where rownum < 10

When I execute I get no errors or warnings during save, but I get no data.

Is there something I am doing wrong, is there a setting in SQLExpress I need to change to allow row_number?

Thanks in advance for your assistance,

Al

Did you ever get an answer to this question? I am having the same issue. The SPROC doesn't return anything when executed within Visual Studio 2005 Pro, but will return fine if the same query is put into a view, or if the SPROC is called from MS Access or SQL Management Studio. Appears to be a problem with Visual Studio 2005.sql

Row_Number() fails to return anything

I am using SQLExpress and want to do some custom paging with a grid but can not get the sproc to produce any results. I don't get any error just a resultset of 0 rows whenever I include the row_number function.

Sample Table:
refID int identity increments by 1
refSubject nvarchar(100)
refBody nText

Sample sproc that works
create proc sp_RefListing as
select refid
, refsubject
from myTable

Sample Sproc that doesn't work:
with OrderedRefList as
(Select refid
,refsubject
,row_number() OVER (order by refsubject) as rownum
from myTable)
select refid
,refsubject
,rownum
from OrderedRefList
where rownum < 10

When I execute I get no errors or warnings during save, but I get no data.

Is there something I am doing wrong, is there a setting in SQLExpress I need to change to allow row_number?

Thanks in advance for your assistance,

Al

Did you ever get an answer to this question? I am having the same issue. The SPROC doesn't return anything when executed within Visual Studio 2005 Pro, but will return fine if the same query is put into a view, or if the SPROC is called from MS Access or SQL Management Studio. Appears to be a problem with Visual Studio 2005.

row size SQL 7

Hello,
I have wrote a select stmt to return data in our sql 7 db as we are
decomissiong it but getting error:
"Cannot sort a row of size 8262, which is greater than the allowable maximum
of 8094."
help
thx
Well, the error message seems pretty straightforward to me, what is your
ORDER BY clause and what are the data types of the columns mentioned in it?
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:43E0BE09-A85A-4C09-9747-C7F6F1267B2A@.microsoft.com...
> Hello,
> I have wrote a select stmt to return data in our sql 7 db as we are
> decomissiong it but getting error:
> "Cannot sort a row of size 8262, which is greater than the allowable
> maximum
> of 8094."
> help
> thx
|||yes I know the error is straitforward, is there a way around it? the column
in question is varchar 8000
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, the error message seems pretty straightforward to me, what is your
> ORDER BY clause and what are the data types of the columns mentioned in it?
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:43E0BE09-A85A-4C09-9747-C7F6F1267B2A@.microsoft.com...
>
>
|||You can try the ROBUST PLAN hint. It isn't guaranteed to work, though, depending on what it is you
are doing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:74E17ACD-2216-4540-BB84-04DDE2C0BEE3@.microsoft.com...[vbcol=seagreen]
> yes I know the error is straitforward, is there a way around it? the column
> in question is varchar 8000
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||How about ORDER BY LEFT(YourCol,8000)
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:74E17ACD-2216-4540-BB84-04DDE2C0BEE3@.microsoft.com...[vbcol=seagreen]
> yes I know the error is straitforward, is there a way around it? the
> column
> in question is varchar 8000
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||> How about ORDER BY LEFT(YourCol,8000)
Unless the varchar(8000) values are all very similar, you could probably
achieve very similar results with simply
ORDER BY LEFT(YourCol, 32)
I am assuming there are other columns in the ORDER BY list, because of the
number reported in the error message (8262).

row size SQL 7

Hello,
I have wrote a select stmt to return data in our sql 7 db as we are
decomissiong it but getting error:
"Cannot sort a row of size 8262, which is greater than the allowable maximum
of 8094."
help
thxWell, the error message seems pretty straightforward to me, what is your
ORDER BY clause and what are the data types of the columns mentioned in it?
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:43E0BE09-A85A-4C09-9747-C7F6F1267B2A@.microsoft.com...
> Hello,
> I have wrote a select stmt to return data in our sql 7 db as we are
> decomissiong it but getting error:
> "Cannot sort a row of size 8262, which is greater than the allowable
> maximum
> of 8094."
> help
> thx|||yes I know the error is straitforward, is there a way around it? the column
in question is varchar 8000
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, the error message seems pretty straightforward to me, what is your
> ORDER BY clause and what are the data types of the columns mentioned in it?
>
> "stoney" <stoney@.discussions.microsoft.com> wrote in message
> news:43E0BE09-A85A-4C09-9747-C7F6F1267B2A@.microsoft.com...
> > Hello,
> >
> > I have wrote a select stmt to return data in our sql 7 db as we are
> > decomissiong it but getting error:
> > "Cannot sort a row of size 8262, which is greater than the allowable
> > maximum
> > of 8094."
> >
> > help
> >
> > thx
>
>|||You can try the ROBUST PLAN hint. It isn't guaranteed to work, though, depending on what it is you
are doing.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:74E17ACD-2216-4540-BB84-04DDE2C0BEE3@.microsoft.com...
> yes I know the error is straitforward, is there a way around it? the column
> in question is varchar 8000
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> Well, the error message seems pretty straightforward to me, what is your
>> ORDER BY clause and what are the data types of the columns mentioned in it?
>>
>> "stoney" <stoney@.discussions.microsoft.com> wrote in message
>> news:43E0BE09-A85A-4C09-9747-C7F6F1267B2A@.microsoft.com...
>> > Hello,
>> >
>> > I have wrote a select stmt to return data in our sql 7 db as we are
>> > decomissiong it but getting error:
>> > "Cannot sort a row of size 8262, which is greater than the allowable
>> > maximum
>> > of 8094."
>> >
>> > help
>> >
>> > thx
>>|||How about ORDER BY LEFT(YourCol,8000)
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:74E17ACD-2216-4540-BB84-04DDE2C0BEE3@.microsoft.com...
> yes I know the error is straitforward, is there a way around it? the
> column
> in question is varchar 8000
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> Well, the error message seems pretty straightforward to me, what is your
>> ORDER BY clause and what are the data types of the columns mentioned in
>> it?
>>
>> "stoney" <stoney@.discussions.microsoft.com> wrote in message
>> news:43E0BE09-A85A-4C09-9747-C7F6F1267B2A@.microsoft.com...
>> > Hello,
>> >
>> > I have wrote a select stmt to return data in our sql 7 db as we are
>> > decomissiong it but getting error:
>> > "Cannot sort a row of size 8262, which is greater than the allowable
>> > maximum
>> > of 8094."
>> >
>> > help
>> >
>> > thx
>>|||> How about ORDER BY LEFT(YourCol,8000)
Unless the varchar(8000) values are all very similar, you could probably
achieve very similar results with simply
ORDER BY LEFT(YourCol, 32)
I am assuming there are other columns in the ORDER BY list, because of the
number reported in the error message (8262).

row number.

All I am trying to do is return the row number (calculated field?)
with each row returned from an SQL query. I think I have done this in
the past but am a bit rusty after not using SQL for a while. If
anyone could help with a code snippet this would be gretly
appreciated.Here's an example from the Pubs database:

SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS rownum
,*
FROM Authors AS A

--
David Portas
SQL Server MVP
--|||
David Mackintosh wrote:
> All I am trying to do is return the row number (calculated field?)
> with each row returned from an SQL query. I think I have done this in
> the past but am a bit rusty after not using SQL for a while. If
> anyone could help with a code snippet this would be gretly
> appreciated.

There are now row numbers.

Zach|||>> All I am trying to do is return the row number (calculated field?)
with each row returned from an SQL query. <<

Since this would have to be for display purposes in the front end, you
ought to be doing in the front, not the database.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

row number

how or what function can we use in sql server 2005 or 2000 to return the
corresponding row number of a record in select statement
for example
SELECT name, xxxxx as Number
FROM TableX
xxxxx - is the function or keyword that returns the corresponding row number
of the select statement.
the result set could be..
Name Number
John Doe 0
Jane Doe 1
0, 1 are the corresponding row number..
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787http://www.aspfaq.com/2427

> John Doe 0
> Jane Doe 1|||Jose,
In SQL Server 2000, you can derive row numbers using sub query like
use pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
SQL Server 2005 has an inbuilt ROW_NUMBER function.
SELECT ROW_NUMBER() OVER (ORDER BY job_desc ASC) as rownum, * FROM jobs
For more info see
http://toponewithties.blogspot.com/...es.blogspot.com
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||Using a identity field you obtain it. If you haven't I think exists a
internal rowid or something like that which provide us the row number.
see you,
"Jose G. de Jesus Jr MCP, MCDBA" wrote:

> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row numb
er
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||In SQL Server 2005, look up the ROW_NUMBER() function.
<Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||> If you haven't I think exists a
> internal rowid or something like that
There are internal row identifiers but these are not exposed to you, so you
can't use them in display.
Typically, the best way to present row numbers is to tack them on in the
presentation layer, since that's the only place where you *have to* loop
through and handle every single row anyway. Forcing the row numbers to be
generated in the database puts unnecessary strain there and turns a simple
query into either a subquery that is evaluated per row, or a mess with
pre-population into a temp table or table variable.|||>Using a identity field you obtain it
That behavior is not guaranteed.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30C1DECC-AF8C-4662-8EB3-3A1F0E3CBE26@.microsoft.com...
> Using a identity field you obtain it. If you haven't I think exists a
> internal rowid or something like that which provide us the row number.
> see you,
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>|||http://support.microsoft.com/defaul...b;EN-US;q186133
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||If one of the columns in query is unique, the following calculates a
sequence number for each row in a resultset:
SELECT
name,
(select count(*) from TableX as x where x.name > TableX.name) as Number
FROM
TableX
ORDER BY
name
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||I disagree. While it is true that using the IDENTITY function in a SELECT
INTO with an ORDER BY clause doesn't guarantee that the order of the
IDENTITY values match the order specified in the ORDER BY clause, using an
INSERT...SELECT...ORDER BY to insert into a temporary table or table
variable with an IDENTITY column will always work correctly. See KB273586.
An obvious improvement, however, is the ROW_NUMBER() function in SQL Server
2005, which eliminates the need for the self-join or the intermediate temp
table or table variable.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:OEtZ0dKqFHA.3192@.TK2MSFTNGP10.phx.gbl...
> That behavior is not guaranteed.
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:30C1DECC-AF8C-4662-8EB3-3A1F0E3CBE26@.microsoft.com...
the
>

row limit in MSSQL

There is at present (as we accidently found) limitation of MSSQL to return per row maximally 8060 bytes. Message like this comes: "Cannot create a row of size 8279 which is greater than the allowable maximum of 8060".
My questions are :
- Is there any way how to pass it? If I split into more tables (as I have it now) and ask for result where these tables are connected over any ID the result is the same. If I use stored procedures it seems to be ok. Any other idea?
- Will be this ok in SQL server 2005?There is no way to overcome this limit in SQL 2000 other than to split the one table into multiple tables (as it seems you have already done).

Another workaround might be to take some of your longer varchar fields and convert them into text; however, you will lose some functionality (searching and indexing) if you choose this option.

Regards,

hmscott

There is at present (as we accidently found) limitation of MSSQL to return per row maximally 8060 bytes. Message like this comes: "Cannot create a row of size 8279 which is greater than the allowable maximum of 8060".
My questions are :
- Is there any way how to pass it? If I split into more tables (as I have it now) and ask for result where these tables are connected over any ID the result is the same. If I use stored procedures it seems to be ok. Any other idea?
- Will be this ok in SQL server 2005?|||Actually, the 8060 byte limit is for storing rows in a table. Result set rows can be many times larger than 8060 bytes.

-PatP|||Actually, the 8060 byte limit is for storing rows in a table. Result set rows can be many times larger than 8060 bytes.

-PatP
I seems to be different for me. I have row size in table roughly 5k but I need to connect more tables. Than (during SELECT command) this error comes.|||Does someone know if this will be fixed in MS SQL 2005 ?|||Try this one on for size, it generates lots of 16 Kb+ rows in the result set.
CREATE TABLE Sladky0 (
pk INT IDENTITY PRIMARY KEY (pk)
, junque00 CHAR(255) NOT NULL DEFAULT '00'
, junque01 CHAR(255) NOT NULL DEFAULT '01'
, junque02 CHAR(255) NOT NULL DEFAULT '02'
, junque03 CHAR(255) NOT NULL DEFAULT '03'
, junque04 CHAR(255) NOT NULL DEFAULT '04'
, junque05 CHAR(255) NOT NULL DEFAULT '05'
, junque06 CHAR(255) NOT NULL DEFAULT '06'
, junque07 CHAR(255) NOT NULL DEFAULT '07'
, junque08 CHAR(255) NOT NULL DEFAULT '08'
, junque09 CHAR(255) NOT NULL DEFAULT '09'
, junque0a CHAR(255) NOT NULL DEFAULT '0a'
, junque0b CHAR(255) NOT NULL DEFAULT '0b'
, junque0c CHAR(255) NOT NULL DEFAULT '0c'
, junque0d CHAR(255) NOT NULL DEFAULT '0d'
, junque0e CHAR(255) NOT NULL DEFAULT '0e'
, junque0f CHAR(255) NOT NULL DEFAULT '0f'
)

CREATE TABLE Sladky1 (
pk INT NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
, junque10 CHAR(255) NOT NULL DEFAULT '10'
, junque11 CHAR(255) NOT NULL DEFAULT '11'
, junque12 CHAR(255) NOT NULL DEFAULT '12'
, junque13 CHAR(255) NOT NULL DEFAULT '13'
, junque14 CHAR(255) NOT NULL DEFAULT '14'
, junque15 CHAR(255) NOT NULL DEFAULT '15'
, junque16 CHAR(255) NOT NULL DEFAULT '16'
, junque17 CHAR(255) NOT NULL DEFAULT '17'
, junque18 CHAR(255) NOT NULL DEFAULT '18'
, junque19 CHAR(255) NOT NULL DEFAULT '19'
, junque1a CHAR(255) NOT NULL DEFAULT '1a'
, junque1b CHAR(255) NOT NULL DEFAULT '1b'
, junque1c CHAR(255) NOT NULL DEFAULT '1c'
, junque1d CHAR(255) NOT NULL DEFAULT '1d'
, junque1e CHAR(255) NOT NULL DEFAULT '1e'
, junque1f CHAR(255) NOT NULL DEFAULT '1f'
)

CREATE TABLE Sladky2 (
pk INT NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
, junque20 CHAR(255) NOT NULL DEFAULT '20'
, junque21 CHAR(255) NOT NULL DEFAULT '21'
, junque22 CHAR(255) NOT NULL DEFAULT '22'
, junque23 CHAR(255) NOT NULL DEFAULT '23'
, junque24 CHAR(255) NOT NULL DEFAULT '24'
, junque25 CHAR(255) NOT NULL DEFAULT '25'
, junque26 CHAR(255) NOT NULL DEFAULT '26'
, junque27 CHAR(255) NOT NULL DEFAULT '27'
, junque28 CHAR(255) NOT NULL DEFAULT '28'
, junque29 CHAR(255) NOT NULL DEFAULT '29'
, junque2a CHAR(255) NOT NULL DEFAULT '2a'
, junque2b CHAR(255) NOT NULL DEFAULT '2b'
, junque2c CHAR(255) NOT NULL DEFAULT '2c'
, junque2d CHAR(255) NOT NULL DEFAULT '2d'
, junque2e CHAR(255) NOT NULL DEFAULT '2e'
, junque2f CHAR(255) NOT NULL DEFAULT '2f'
)

CREATE TABLE Sladky3 (
pk INT NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
, junque30 CHAR(255) NOT NULL DEFAULT '30'
, junque31 CHAR(255) NOT NULL DEFAULT '31'
, junque32 CHAR(255) NOT NULL DEFAULT '32'
, junque33 CHAR(255) NOT NULL DEFAULT '33'
, junque34 CHAR(255) NOT NULL DEFAULT '34'
, junque35 CHAR(255) NOT NULL DEFAULT '35'
, junque36 CHAR(255) NOT NULL DEFAULT '36'
, junque37 CHAR(255) NOT NULL DEFAULT '37'
, junque38 CHAR(255) NOT NULL DEFAULT '38'
, junque39 CHAR(255) NOT NULL DEFAULT '39'
, junque3a CHAR(255) NOT NULL DEFAULT '3a'
, junque3b CHAR(255) NOT NULL DEFAULT '3b'
, junque3c CHAR(255) NOT NULL DEFAULT '3c'
, junque3d CHAR(255) NOT NULL DEFAULT '3d'
, junque3e CHAR(255) NOT NULL DEFAULT '3e'
, junque3f CHAR(255) NOT NULL DEFAULT '3f'
)

DECLARE
@.loop INT
, @.pk INT

SET @.loop = 10

WHILE 0 < @.loop
BEGIN
INSERT INTO Sladky0 (junque00) VALUES (DEFAULT)
SET @.pk = @.@.identity
INSERT INTO Sladky1 (pk) VALUES (@.pk)
INSERT INTO Sladky2 (pk) VALUES (@.pk)
INSERT INTO Sladky3 (pk) VALUES (@.pk)
SELECT @.loop = @.loop - 1
END

SELECT *
FROM Sladky0
JOIN Sladky1 ON (Sladky1.pk = Sladky0.pk)
JOIN Sladky2 ON (Sladky2.pk = Sladky0.pk)
JOIN Sladky3 ON (Sladky3.pk = Sladky0.pk)-PatP|||ok , thanks|||I had a bit different SQL command in my application. I have join done over where condition and used DISTINCT in command.
But I found if I use DISTICNT keyword before that this error comes even with your offered SQL command with JOINs. So it seems to me different with DISTINCT or not - but why? So I cannot use keyword distinct for bigger result over tables - I guess.

(
SELECT DISTINCT *
FROM Sladky0
JOIN Sladky1 ON (Sladky1.pk = Sladky0.pk)
JOIN Sladky2 ON (Sladky2.pk = Sladky0.pk)
JOIN Sladky3 ON (Sladky3.pk = Sladky0.pk)
) doesn't work|||Avoid using DISTINCT this way (it implicity creates a table), use a GROUP BY instead.

-PatP|||Does it mean that it is better to mention all columns in GROUP BY statement than use DISCTINCT expression? So I have very long statements. To mention only some of them is not possible I think.|||Listing every returned column in a GROUP BY is the only was I know to avoid creating the work table that is created by DISTINCT.

-PatP|||I have tryied it - it really works fine. Thanks|||I know that it is something of a pain to have to deal with the problem by changing your code, but the designers made some trade off decisions in the query engine that work very well for 99.9% of the queries, but clobber queries like yours. It is annoying to have your code be the "lucky" one that needs to be changed, but at least the change is better than waiting for another database upgrade (at least in most cases)!

-PatP|||I agree with you. I am glad that I have solution for now for MSSQL server.
In our application I try to have as much as possible common code for MySQL (ODBC, native), MSSQL (ODBC), ORACLE(ODBC, native),... but you can imagine a lot of different parts.|||Yukon allows rows to span across multiple pages.

row delimiter from osql output

hi, i had been removed the row counts and the column spaces... but i
failed to remove the extra tabs between rows of data return from the
osql output.
how to detect the row delimiter?

i noticed i can use bcp to have a more decent output file, but i don't
want to do too much of file read-write. the programming enviroment is
quite easy to "capture" the output from the comand prompt.

secondary, char(252), 253, 254, 255 is not being read by the command
prompt, for example, 253 turns into 132 when i decode the input.

thankx.
from alan.Alan Zhong (alanchinese@.yahoo.com) writes:
> hi, i had been removed the row counts and the column spaces... but i
> failed to remove the extra tabs between rows of data return from the
> osql output.
> how to detect the row delimiter?
> i noticed i can use bcp to have a more decent output file, but i don't
> want to do too much of file read-write. the programming enviroment is
> quite easy to "capture" the output from the comand prompt.

I'm afraid that I don't understand the question really. You can use
the -s option to OSQL to change the column delimiter, but you will
still get lots of extra spaces. I don't know really what you want to
do, but it sounds like you should fo with BCP.

> secondary, char(252), 253, 254, 255 is not being read by the command
> prompt, for example, 253 turns into 132 when i decode the input.

OSQL is a command-line, and a the command-line world in Windows uses
a different character set than Windows applications. (DOS legacy.)
But OSQL is able to read and write Unicode files, and with Unicode
files you don't have this issue.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Row count on all tables in the database

Does anyone have a single T-SQL script that could be run against a database that would return the table name and row count for each table?

Code Snippet

declare @.cmd nvarchar(max)

set @.cmd=null

select @.cmd =coalesce(@.cmd +'; ','')+

N'SELECT COUNT(*) AS "'+

quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)+

N' Count" FROM '+quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)

frominformation_schema.tables

execsp_executesql @.cmd

|||

Alternate variation:

Code Snippet

declare @.cmd nvarchar(max)

set @.cmd=null

select @.cmd =coalesce(@.cmd +' union all ','')+

N'SELECT '''+

quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)+

N''' AS TableName, COUNT(*) AS "Rows" '+

N' FROM '+quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)

frominformation_schema.tables

execsp_executesql @.cmd

|||Thanks for the reply Dale. I get results when running this against my master database but not against my DSS database (which is the one I am really after). Is there a variation that would work for my DSS database?|||

The INFORMATION_SCHEMA.TABLES runs against the current database.

Issue USE DSS; in front of the rest of the code.

|||

A fair estimate can be get from:

-- 2000

use your_db

go

dbcc updateusage (0) withcount_rows

go

select

object_name([id]),

rowcnt

from

sysindexes

where

indid in(0, 1)

andobjectproperty([id],'IsUserTable')= 1

andobjectproperty([id],'IsMSShipped')= 0

go

-- 2005

select

object_name([object_id]),

sum([rows])as rowcnt

from

sys.partitions

where

objectproperty([object_id],'IsUserTable')= 1

andobjectproperty([object_id],'IsMSShipped')= 0

groupby

[object_id]

go

AMB

Rounding Up

I have the following code that retreives the current value of the item price. however it always rounds up. If I manually enter a return value like so:
return (decimal)12.47
It returns the correct value, however if I set it with an expression like this:
return(decimal)arParam[1].Value;
It rounds the number up: How can I get it to not round up when insertign a value based ona expression?

publicdecimal GetCreditPrice(string CustomerSecurityKey)

{

try

{

System.Data.SqlClient.SqlParameter prmCrnt;

System.Data.SqlClient.SqlParameter[] arParam =new System.Data.SqlClient.SqlParameter[2];

prmCrnt =new System.Data.SqlClient.SqlParameter("@.CustomerSecurityKey", SqlDbType.VarChar,25);

prmCrnt.Value = CustomerSecurityKey;

arParam[0] = prmCrnt;

prmCrnt =new System.Data.SqlClient.SqlParameter("@.Price", SqlDbType.Decimal);

prmCrnt.Direction = ParameterDirection.Output;

arParam[1] = prmCrnt;

SqlHelper.ExecuteNonQuery(stConnection, CommandType.StoredProcedure, "GetCreditPrice", arParam);

return(decimal)arParam[1].Value;

}

catch(System.Exception ex)

{

throw ex;

}

}

Try the link I posted in this post for custom String Formatting. Hope this helps.
http://forums.asp.net/887067/ShowPost.aspx

Rounding error

This should return 73.34...

However, it returns a 0 instead.

select convert(decimal(18,2),(3370)/(4595)*100)

Can you pl advise.

Either add a .0 to the end of the numbers, or explicitly convert to float.

The reason is that SQL Server, when dividing integers, returns an integer.

select convert(decimal(18,2),(3370.0)/(4595.0)*100.0) returns 73.34

BobP

|||

To add to Bob's comment:

IF both the dividend and divisor are whole numbers (int), SQL assumes you want the results as an int.

IF either of the two contains a decimal, a float will be returned. For example:

SELECT (( 3370./4595 ) * 100.0 )

--
73.3405000

|||

The datatype on the field, acctno is Int.. I use the convert statement to convert to float. but it still returns the data in INt ie it return 3370 instead of 3370.0.. Though I AM using covnert to float, data is still returned as an INT.

Select round(count(convert(float,convert(decimal(38,5),acctno))),2) from tbl1
WHERE (tbl1.balance<>0).

Any ideas how I can return The value as 3370.0

|||

Count returns an int... so there is no decimal.

Use convert(float, count(...

BobP

|||

Whoa nellie! What's with the round(), count() convert(), convert(), etc.

There is reason to convert(), round(), etc., it you are only interested in the count of rows meeting the criteria.

SELECT cast( count( AcctNo ) AS decimal(10,2))

FROM tbl1

WHERE tbl1.Balance <> 0

'should' do the trick.

ROUND function

ROUND(12.5, 0) return 12
How implement in T-SQL (SQL Server 2000) to return 13?SELECT ROUND(12.5, 0)
Returns 13 !
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Microsoft" <vali_albastroiu@.hotmail.com> wrote in message
news:OhLQppmmFHA.1416@.TK2MSFTNGP09.phx.gbl...
> ROUND(12.5, 0) return 12
> How implement in T-SQL (SQL Server 2000) to return 13?
>|||ROUND(12.5, 0, 1) returns 12
ROUND(12.5, 0, 0) returns 13
"Microsoft" <vali_albastroiu@.hotmail.com> wrote in message
news:OhLQppmmFHA.1416@.TK2MSFTNGP09.phx.gbl...
> ROUND(12.5, 0) return 12
> How implement in T-SQL (SQL Server 2000) to return 13?
>