RowNumber using SQL Query

Hi All,
I have following table structure,

----------------------
ChallanID ProductID PublicationDate Description Qty Amt
----------------------
43 9 4/1/2006 ABC 1 880
43 10 5/1/2006 BCA 1 930
43 11 5/1/2006 CBA 1 230

I want a sql query which select all the record with a serial number eg:

---------------------
SN# ChallanID ProductID PublicationDate Description Qty Amt
----------------------
1 43 9 4/1/2006 ABC 1 880
2 43 10 5/1/2006 BCA 1 930
3 43 11 5/1/2006 CBA 1 230use a temp table with identity column and insert your result into the temp table and select it back.|||U havent mentioned about how records to be ordered? In what order u want to generate serial No:?|||temp table shemp table...

SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName

ps. I got this example from somewhere and it is not original work. If the original author sees this and takes any offense I am will to erase from the forum.|||SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName
No, that only works if you use a unique key i.e.
select id=1,name='ccc' into #t1 union all
select 3,'bbb' union all
select 4,'aaa' union all
select 9,'bbb'

select count(*) as [ss#], name=min(a.name)
from #t1 a, #t1 b
where a.id>=b.id
group by a.id
order by 1
else use a temp table with identity column as suggested by khtan
select ss#=identity(int,1,1),name into #t2 from #t1 order by name
select * from #t2|||Problem is, either way you have no guarantee that the "serial number" for any record won't change as the contents of the table changes. Seems to me a "serial number" is expected to be static, so you really should add it as a permanent column to your table (perhaps as an identity datatype).

Row_number selecting from a complex select statement

Hi,

Code Snippet

This is difficult to explain in words, but the following code outlines what I am trying to do:

with myTableWithRowNum as
(
select 'row' = row_number() over (order by insertdate desc), myValue
from
(
select table1Id As myValue from myTable1
union
select table2Id As myValue from myTable2
)
)

select * from myTableWithRowNum

Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?

The following query might help you,

Code Snippet

;with UnionResult(myvalue,insertdate)

as

(

select table1Id As myValue,insertdate from myTable1

union

select table2Id As myValue,insertdate from myTable2

),

OrderedResult(myValue,Row)

as

(

select myValue, row_number() over (order by insertdate desc)

from UnionResult

)

select * from OrderedResult

|||

I m not sure I understand your requirment correctly,anyhow your query throws error,

Try the following

Code Snippet

;with myTableWithRowNum as

(

select 'row' = row_number() over (order by insertdate desc), myValue

from

(

select insertdate,table1Id As myValue from myTable1

union

select insertdate,table2Id As myValue from myTable2

) as temp

)

select * from myTableWithRowNum

|||Thanks that's exactly what I'm looking for.
sql

'ROW_NUMBER' is not a recognized function name.

I am getting the following error while excuting following query in sqlserver 2005:

SELECT ProductName, UnitPrice,

ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank

FROM Products

ORDER BY UnitPrice DESC

if any one know what should be done to avoid this, please let me know.

Thanks in advance,

Rajanikanth.

Check and see if the database is running in SQL Server 2000 compatibility mode. Try running these two commands:

Code Snippet

select @.@.version

exec sp_dbcmptlevel 'yourDatabaseName'

|||

As Kent stated, check that you are connecting to a SS 2005 server. You could be using the client tools shipped with 2005, but if you connect to a 2000 instance, for example, then you will not be able to use the new features from 2005. The db compatibility level does not limit you from using the new features, if it is hosted in a 2005 instance.

How to identify your SQL Server version and edition

http://support.microsoft.com/default.aspx?scid=kb;en-us;321185

AMB

sql

Row yielded no match during lookup

In SSIS. I am having trouble exporting records
that don't match from a lookup transformation. I get the following
error:

Row yielded no match during lookup.

I would really like to have a list of all records that did not match so
that I could send an email of those missing rows

Please give me solution with example

Thanks

That is happening because your are using the default error configuration of the Lookuptask "Fail Component"; it would fail if a no match occurs. You need to change that to 'Redirect row' and then use the error output of the task to send those rows to whereever you want.

Rafael Salas

|||

Thanks for email

I m new in SSIS, please suggest such error output with example.

|||

leo1 wrote:

Thanks for email

I m new in SSIS, please suggest such error output with example.

Leo,

when you configure a lookup transformation to 'redirect error' all no-matched rows are sent to the error output instead of failing the task (the error you originally received); obviously those error rows will have null in the columns that the lookup transformation added. Then, based in your requirements, you can decide what to do with those errors. e.g. for a data warehouse your may want to replace the nulls by default values and insert them to the destination table; and/or you can decide to send them to an custom error table.

Rafael Salas

|||

thanks for reply.

I want to find out all such distinct rows or lookup id and send an email of all such non-matching items via email to the team.

Can you please suggest me (Steps) or example how to do this.

thanks

|||

Use a Flat File Destination Adapter to push that data into a file. You can then send that file using the Send mail Task.

-jamie

|||

I have got all such rows in the file using the flat file destination in data flow

Kindly let me know how to send an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

Should we use the send email task on eventhandler, if yes, how to check for error and invoke send email task.

Kindly suggest possibly by example or steps.

|||I use a lookup often for different purposes.

For example currently i'm using it to pull "Open House" information for Properties.
Only a few of those have open house schedules - so what i do is i have two outs from Lookup - and they both go to Union All transform.

Row yielded no match during lookup

In SSIS. I am having trouble exporting records
that don't match from a lookup transformation. I get the following
error:

Row yielded no match during lookup.

I would really like to have a list of all records that did not match so
that I could send an email of those missing rows

Please give me solution with example

Thanks

That is happening because your are using the default error configuration of the Lookuptask "Fail Component"; it would fail if a no match occurs. You need to change that to 'Redirect row' and then use the error output of the task to send those rows to whereever you want.

Rafael Salas

|||

Thanks for email

I m new in SSIS, please suggest such error output with example.

|||

leo1 wrote:

Thanks for email

I m new in SSIS, please suggest such error output with example.

Leo,

when you configure a lookup transformation to 'redirect error' all no-matched rows are sent to the error output instead of failing the task (the error you originally received); obviously those error rows will have null in the columns that the lookup transformation added. Then, based in your requirements, you can decide what to do with those errors. e.g. for a data warehouse your may want to replace the nulls by default values and insert them to the destination table; and/or you can decide to send them to an custom error table.

Rafael Salas

|||

thanks for reply.

I want to find out all such distinct rows or lookup id and send an email of all such non-matching items via email to the team.

Can you please suggest me (Steps) or example how to do this.

thanks

|||

Use a Flat File Destination Adapter to push that data into a file. You can then send that file using the Send mail Task.

-jamie

|||

I have got all such rows in the file using the flat file destination in data flow

Kindly let me know how to send an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

Should we use the send email task on eventhandler, if yes, how to check for error and invoke send email task.

Kindly suggest possibly by example or steps.

|||I use a lookup often for different purposes.

For example currently i'm using it to pull "Open House" information for Properties.
Only a few of those have open house schedules - so what i do is i have two outs from Lookup - and they both go to Union All transform.

Row to Column?

All:
Is there a function in MS SQL so that I can archieve the following in
SQL statement? Or do I need to loop through the record set and doing
some array element movement on client side?
Table
Item Color
1 red
1 blue
2 red
2 yellow
3 red
I want the result looks like:
Item Color_red Color_blue Color_yellow
1 red blue null
2 red null yellow
3 red null null
thanks a lotCheck out RAC @.
www.rac4sql.net
A very easy and powerful pivoting/xtab utility.
No sql coding required.|||here's a couple ways, e.g.
declare @.x table (item int, color varchar(6))
insert @.x
select 1, 'red' union all
select 1, 'blue' union all
select 2, 'red' union all
select 2, 'yellow' union all
select 3, 'red'
-- either sql 2000/2005
select item,
max(case when color='red' then 'red' end) as color_red,
max(case when color='blue' then 'blue' end) as color_blue,
max(case when color='yellow' then 'yellow' end) as color_yellow
from @.x
group by item
-- sql2005 only [new PIVOT clause]
-- note in the pivot clause, those are columns, not values (strings)
select item, [red] as color_red, [blue] as color_blue, [yellow] as
color_yellow
from
(select item, color from @.x) x
pivot
(
max(color)
for color in ([red],[blue],[yellow])) as pvt
order by item
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||If this has to be done in SQL, you could try outer joining to the table
multiple times, once for each column on your output. If you have the option
of using a tool to process the data outside of SQL, thaqt may be easier.
if tblColor is the name of your table...
select item, rcolor, bcolor, ycolor
from
(Select distinct item from tblColor) as Main
left outer join (select distinct item as ritem, color as rcolor from
tblColor where color = 'red') as red
on item = ritem
left outer join (select distinct item as bitem, color as bcolor from
tblColor where color = 'blue') as blue
on item = bitem
left outer join (select distinct item as yitem, color as ycolor from
tblColor where color = 'yellow') as yellow
on item = yitem
OR, if you dont like inline queries, this is slightly more readable:
select Main.item, red.color, blue.color, yellow.color
from
(Select distinct item from tblColor) as Main
left outer join tblColor as red
on Main.item = red.item and red.color = 'red'
left outer join tblColor as blue
on Main.item = blue.item and blue.color = 'blue'
left outer join tblColor as yellow
on Main.item = yellow.item and yellow.color = 'yellow'
I think you are stuck with the inline query to select the distinct items
regardless. I can't think of a way to avoid this, but you should be able to
make the rest work. The performance on something like this is surprisingly
good, even when you have thousands of rows in your table and 20 collumns.
As you add more columns and more filters on the data it can get a bit out of
hand.
Hope this helps.
<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>
Ugly.
Of course, you need to know what possible colors can exist in advance.
set nocount on
create table #col (ident int, col varchar(10))
insert #col select 1, 'red'
insert #col select 1, 'blue'
insert #col select 2, 'red'
insert #col select 2, 'yellow'
insert #col select 3, 'red'
select ident,
case when exists (select C.col from #col C where C.col = 'red' and C.ident =
#col.ident) then 'red' end as color_red,
case when exists (select C.col from #col C where C.col = 'blue' and C.ident
= #col.ident) then 'blue' end as color_blue,
case when exists (select C.col from #col C where C.col = 'yellow' and
C.ident = #col.ident) then 'yellow' end as color_yellow
from #col
group by ident
drop table #col|||If you are using SQL2K5, you can use this:
CREATE TABLE Colors
(Item int not null
,Color varchar(50) not null
)
INSERT INTO COLORS VALUES (1,'red')
INSERT INTO COLORS VALUES (1,'blue')
INSERT INTO COLORS VALUES (2,'red')
INSERT INTO COLORS VALUES (2,'yellow')
INSERT INTO COLORS VALUES (3,'red')
GO
SELECT Item, "red" AS Color_red, "blue" AS Color_blue, "yellow" AS
Color_Yellow
FROM (
SELECT Item, Color
FROM Colors
) p PIVOT (
MIN(Color)
FOR Color IN ("red","blue","yellow")
) pvt
ORDER BY Item
GO
DROP TABLE Colors
GO
If you are using SQL2K or below, then google for SQL Server and PIVOT.
HTH,
Gert-Jan
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:%239zKmuVFGHA.516@.TK2MSFTNGP15.phx.gbl...
>.
> -- sql2005 only [new PIVOT clause]
> -- note in the pivot clause, those are columns, not values (strings)
> select item, [red] as color_red, [blue] as color_blue, [yellow] as
> color_yellow
> from
> (select item, color from @.x) x
> pivot
> (
> max(color)
> for color in ([red],[blue],[yellow])) as pvt
> order by item
And to think you only had to wait 5 years for this!
Are we both being factious? :)
MS is doing its best to keep RAC around.
If you can't top it......:)
www.rac4sql.net|||Some people are dragged to the funny farm,
others JOIN it :)
"Jim Underwood" <james.underwood@.fallonclinic.com> wrote in message
news:%23Y%23HV3VFGHA.984@.tk2msftngp13.phx.gbl...
> If this has to be done in SQL, you could try outer joining to the table
> multiple times, once for each column on your output. If you have the
option
> of using a tool to process the data outside of SQL, thaqt may be easier.
> if tblColor is the name of your table...
> select item, rcolor, bcolor, ycolor
> from
> (Select distinct item from tblColor) as Main
> left outer join (select distinct item as ritem, color as rcolor from
> tblColor where color = 'red') as red
> on item = ritem
> left outer join (select distinct item as bitem, color as bcolor from
> tblColor where color = 'blue') as blue
> on item = bitem
> left outer join (select distinct item as yitem, color as ycolor from
> tblColor where color = 'yellow') as yellow
> on item = yitem
> OR, if you dont like inline queries, this is slightly more readable:
> select Main.item, red.color, blue.color, yellow.color
> from
> (Select distinct item from tblColor) as Main
> left outer join tblColor as red
> on Main.item = red.item and red.color = 'red'
> left outer join tblColor as blue
> on Main.item = blue.item and blue.color = 'blue'
> left outer join tblColor as yellow
> on Main.item = yellow.item and yellow.color = 'yellow'
> I think you are stuck with the inline query to select the distinct items
> regardless. I can't think of a way to avoid this, but you should be able
to
> make the rest work. The performance on something like this is
surprisingly
> good, even when you have thousands of rows in your table and 20 collumns.
> As you add more columns and more filters on the data it can get a bit out
of
> hand.
> Hope this helps.
>
> <rockdale.green@.gmail.com> wrote in message
> news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
>|||Guys, Thanks for all your reply. The pivot table is interesting. I
didnot know that SQL2k5 has this functionality.
But I decided to do this convertion in client side. Because how many
colour we have is stored in another table. I can not hard code say
color_red.. etc. I know that I can dynamic generate the sql statement
in store procedure. But that is kind of overkill.
Anyway, thanks a lot.|||Hi, all
I am back to this problem since now I have more time to test it out.
I guess Raymond's solution is a neat one but it does not solve a more
complex problem like following,
based on cid column then show content in col column.
Notice that I have to add col in my group by clause, but that cause the
problem. THe result is
1 red red NULL
1 blue blue NULL
2 red NULL red
2 yellow NULL yellow
3 red NULL NULL
Which not what I want. Any Idea?
---
set nocount on
create table #col (ident int,cid int, col varchar(10))
insert #col select 1,1, 'red'
insert #col select 1,2, 'blue'
insert #col select 2,1, 'red'
insert #col select 2,3, 'yellow'
insert #col select 3,1, 'red'
select ident,
case when exists (select C.col from #col C where C.cid = 1 and C.ident
=
#col.ident) then col end as color_red,
case when exists (select C.col from #col C where C.cid = 2 and C.ident
= #col.ident) then col end as color_blue,
case when exists (select C.col from #col C where C.cid = 3 and
C.ident = #col.ident) then col end as color_yellow
from #col
group by ident,cid, col
----
drop table #col

Row Size Limit?

The following works on my server:
UPDATE tb_tmp
SET sm_PublicText = CONVERT(NVARCHAR(2000), @.data)
WHERE sm_pk = @.id
BUT, at my client's server it does NOT - no errors given, just it is left
NULL. To work I have to change the 2000 (in convert) to 200.
Does he has some row size limit. Where can I look?
Evanpermissions for client?
"Evan Camilleri" wrote:

> The following works on my server:
> UPDATE tb_tmp
> SET sm_PublicText = CONVERT(NVARCHAR(2000), @.data)
> WHERE sm_pk = @.id
>
> BUT, at my client's server it does NOT - no errors given, just it is left
> NULL. To work I have to change the 2000 (in convert) to 200.
> Does he has some row size limit. Where can I look?
>
> Evan
>
>|||The row size limit in 2000 and below is 8060 bytes (including overhead). In
2005, you can overflow
the regular variable length datatypes.
Either way, the max row size does not include text, ntext, image, and the ne
w varchar(max),
nvarchar(max) and varbinary(max).
If it were an overflow problem, SQL Server would return an error message. Pe
rhaps the client
application suppresses this error? Did you execute the UPDATE from Query ana
lyzer, or? Or perhaps
there's a trigger on the table which silently modifies the column value?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Evan Camilleri" <e70mt@.yahoo.co.uk.nospam> wrote in message
news:%23hTaOk4kGHA.3924@.TK2MSFTNGP03.phx.gbl...
> The following works on my server:
> UPDATE tb_tmp
> SET sm_PublicText = CONVERT(NVARCHAR(2000), @.data)
> WHERE sm_pk = @.id
>
> BUT, at my client's server it does NOT - no errors given, just it is left
NULL. To work I have to
> change the 2000 (in convert) to 200.
> Does he has some row size limit. Where can I look?
>
> Evan
>|||I do not think permissions have to do with it since CONVERT(NVARCHAR(200),
@.data) works (with 200 it works, with 2000 it does not)
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:41715CFE-A538-46A4-B17E-9BBC8B383342@.microsoft.com...
> permissions for client?
>
> "Evan Camilleri" wrote:
>|||Check the table definition, also look for any triggers.
ML
http://milambda.blogspot.com/|||
Thanks for your reply. We used Query Analyzer. There is no triggers.
Before this sp is executed I DROP the table and recreate it in the sp
itself... so there is no trigger.
DROP TABLE tb_tmp
CREATE TABLE tb_tmp (
[sm_pk] [int] NOT NULL,
[sm_Ref] [nvarchar] (50) NULL ,
[sm_PublicText] text NULL
)
ALTER TABLE tb_tmp WITH NOCHECK ADD CONSTRAINT [PK_tb_SM] PRIMARY
KEY CLUSTERED ([sm_pk])
INSERT INTO tb_tmp (sm_pk, sm_PublicText)
VALUES (@.id, '')
.......................................SET @.data here
--PRINT @.data ....................prints correctly
-- Save
UPDATE tb_tmp
SET sm_PublicText = CONVERT(NVARCHAR(4000), @.data)
WHERE sm_pk = @.id
As I said this works on my server BUT DOES NOT on the server of the client.
It leaves the row NULL. If I change the 4000 to 500 it works (obviously
truncating my records)
Evan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23K3%23I64kGHA.3596@.TK2MSFTNGP05.phx.gbl...
> The row size limit in 2000 and below is 8060 bytes (including overhead).
> In 2005, you can overflow the regular variable length datatypes.
> Either way, the max row size does not include text, ntext, image, and the
> new varchar(max), nvarchar(max) and varbinary(max).
> If it were an overflow problem, SQL Server would return an error message.
> Perhaps the client application suppresses this error? Did you execute the
> UPDATE from Query analyzer, or? Or perhaps there's a trigger on the table
> which silently modifies the column value?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Evan Camilleri" <e70mt@.yahoo.co.uk.nospam> wrote in message
> news:%23hTaOk4kGHA.3924@.TK2MSFTNGP03.phx.gbl...
>|||this is the SP:
DROP TABLE tb_tmp
CREATE TABLE tb_tmp (
[sm_pk] [int] NOT NULL,
[sm_Ref] [nvarchar] (50) NULL ,
[sm_PublicText] text NULL
)
ALTER TABLE tb_tmp WITH NOCHECK ADD CONSTRAINT [PK_tb_SM] PRIMARY
KEY CLUSTERED ([sm_pk])
INSERT INTO tb_tmp (sm_pk, sm_PublicText)
VALUES (@.id, '')
.......................................SET @.data here
--PRINT @.data ....................prints correctly
-- Save
UPDATE tb_tmp
SET sm_PublicText = CONVERT(NVARCHAR(4000), @.data)
WHERE sm_pk = @.id
As I said this works on my server BUT DOES NOT on the server of the client.
It leaves the row NULL. If I change the 4000 to 500 it works (obviously
truncating my records)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3F67A2FD-1B7B-40C2-AF32-4524C35961CA@.microsoft.com...
> Check the table definition, also look for any triggers.
>
> ML
> --
> http://milambda.blogspot.com/|||Try casting the value as the actual column's data type:
CONVERT(text, @.data)
ML
http://milambda.blogspot.com/

Row size in SS7

Hello,
I created a table and got the following:
The total row size (15676) for table 'MyTable' exceeds the maximum number of
bytes per row (8060). Rows that exceed the maximum number of bytes will not
be added.
I have a 5 fields that are set to varchar (2000) plus some others. Should I
use the text data type? I was reading that the text data is not stored in
the table but in a separate page. Will this be a problem in doing searches
and pushing data to the web using stored procedures and ASP?
--
Thanks in advance,
StevenUsing the text datatype won't cause you any problems searching or pushing
data; the data's physical storage is of no concern to you when querying the
data.
Whether or not you SHOULD use it is an architectural question that I can't
answer without more information about what kind of database you're building
and what the columns will be used for.
"Steven K" <skaper@.troop.com> wrote in message
news:O82Zp1XpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I created a table and got the following:
> The total row size (15676) for table 'MyTable' exceeds the maximum number
of
> bytes per row (8060). Rows that exceed the maximum number of bytes will
not
> be added.
> I have a 5 fields that are set to varchar (2000) plus some others. Should
I
> use the text data type? I was reading that the text data is not stored in
> the table but in a separate page. Will this be a problem in doing
searches
> and pushing data to the web using stored procedures and ASP?
> --
> Thanks in advance,
> Steven
>|||Using varchar datatype you can have a "declared" row length of larger than
8060. As long as the total combined REAL DATA length does not exceed the
limit, you are fine -- with some composite index you may receive a warning.
However, when the real length of data for a record exceeds the limit, you
will have to use text, ntext or image type. Have a look at "Managing ntext,
text, and image Data" in BOL.
"Steven K" <skaper@.troop.com> wrote in message
news:O82Zp1XpDHA.2268@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I created a table and got the following:
> The total row size (15676) for table 'MyTable' exceeds the maximum number
of
> bytes per row (8060). Rows that exceed the maximum number of bytes will
not
> be added.
> I have a 5 fields that are set to varchar (2000) plus some others. Should
I
> use the text data type? I was reading that the text data is not stored in
> the table but in a separate page. Will this be a problem in doing
searches
> and pushing data to the web using stored procedures and ASP?
> --
> Thanks in advance,
> Steven
>sql

Row retrieval problem in small table

Dear Participant,

I face following problem by last few day, please help me for the same

My Mssql server 2000 with service pack 3 use for my lan bas users, normally they can work fine without any problem, but some time user not able to retrieve information from server. I had debugged this problem and found one small table with 50/60 records not retrieving for so long at clients machine. I open enterprise manage and trough that try to open table, but server in try mode only not show a single row after long time and give message client time out.

I open query analyzer and try to select * from table_name, it is also not retrieve a single row after long time and nothing got as a message.

Shutdown the server and restart , I am able to retrieve that table from EM, Query analyzer and from application also.

I dont understand what is the problem.

Thanks

R.MallSounds like a blocking problem to me. The next time this happens, take a look in Enterprise Manager Management->Current Activity->Process Info, and see if people are getting blocked in general. After that, you can try to narrow down the table, but I think you already have that table.|||I will see it, but I had look over the same matter on server itself, without a single user in LAN.|||Is there any jobs running in parallel... Jobs by default is a transaction... u can see once a transaction is running and if u try to get data thru enterprise manage and trough that try to open table it will not display any... but select should run in that case.....

I got a doubt what does this blocking means? is that 'lock' u guys are mentioning... If its lock u can run the below query to check if there is any lock still running,

SELECT spid, cmd, status, loginame, open_tran,
datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)|||Is there any easy going methods to resolve this problem.

Thanks

R.Mall|||Use sql profiler to watch it.|||i suggest you use SP_WHO to check the instances running and status of each instance. you would be able to see also if there are blocking...

use the Profiler if you want to know the different SQL commands being processed by the server. However, use this with caution since it might cause your system to slow down thus giving you the false impression that your script is slow.

Row Numbers for Groups

I am trying to number a group and I am using the following to do so:
=RunningValue(Fields!DBPROJECTID.Value, CountDistinct, Nothing)
However, I have one little problem. How do I clear out the value and start
over? This is what I want my report to look like:
Group 1 Header
1. Group 2 Header
Detail
2. Group 2 Header
Detail
Group 1 Header
1. Group 2 Header
Detail
Instead I get:
Group 1 Header
1. Group 2 Header
Detail
2. Group 2 Header
Detail
Group 1 Header
3. Group 2 Header
Detail
Any suggestions?Assuming your Group 1 is called "Group1", you can use a RunningValue with an
explicit scope specified:
=RunningValue(Fields!DBPROJECTID.Value, CountDistinct, "Group1")
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"wbarron" <wbarron@.discussions.microsoft.com> wrote in message
news:6018836A-2B4B-4A3B-B637-62805B443285@.microsoft.com...
>I am trying to number a group and I am using the following to do so:
> =RunningValue(Fields!DBPROJECTID.Value, CountDistinct, Nothing)
> However, I have one little problem. How do I clear out the value and
> start
> over? This is what I want my report to look like:
> Group 1 Header
> 1. Group 2 Header
> Detail
> 2. Group 2 Header
> Detail
> Group 1 Header
> 1. Group 2 Header
> Detail
> Instead I get:
> Group 1 Header
> 1. Group 2 Header
> Detail
> 2. Group 2 Header
> Detail
> Group 1 Header
> 3. Group 2 Header
> Detail
> Any suggestions?
>|||Thanks! After I posted, it dawned on me that I needed to specify the scope.
Thanks for the response.
Wendy
"Robert Bruckner [MSFT]" wrote:
> Assuming your Group 1 is called "Group1", you can use a RunningValue with an
> explicit scope specified:
> =RunningValue(Fields!DBPROJECTID.Value, CountDistinct, "Group1")
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "wbarron" <wbarron@.discussions.microsoft.com> wrote in message
> news:6018836A-2B4B-4A3B-B637-62805B443285@.microsoft.com...
> >I am trying to number a group and I am using the following to do so:
> >
> > =RunningValue(Fields!DBPROJECTID.Value, CountDistinct, Nothing)
> >
> > However, I have one little problem. How do I clear out the value and
> > start
> > over? This is what I want my report to look like:
> >
> > Group 1 Header
> > 1. Group 2 Header
> > Detail
> > 2. Group 2 Header
> > Detail
> > Group 1 Header
> > 1. Group 2 Header
> > Detail
> >
> > Instead I get:
> >
> > Group 1 Header
> > 1. Group 2 Header
> > Detail
> > 2. Group 2 Header
> > Detail
> > Group 1 Header
> > 3. Group 2 Header
> > Detail
> >
> > Any suggestions?
> >
> >
>
>

Row Locking in SQL via ADO (VB 6)

Hi,

I'm trying to use the pessimistic row locking of SQL to get following result.

When a customer form is openend, the row should be locked for writing.
This lock should be left open until the user closes the customer form.

I cannot use transactions because there can be more then 1 customer form open in the same app. In ADO a connection is IN transaction or is NOT, nested transactions are not supported.

How can I keep this row locked on SQL and this until I unlock it or the connection is broken ( in case of problems on client machine )?
And how can I see on another machine of this row ( customer ) is already locked so I can open him in read-only?

For the moment I'm using extra fields that hold the info wether the customer is locked en by whom. But that's on application level, not on DB-level.

I hope this is clear enough.I've often wondered if there is any reason that justifies pessimistic locking. So far I haven't found one. I recommend shutting down the SQL Server to get pessimistic locking... If the box is off, no other user can modify your data, and it makes the scaling problems caused by pessimistic locking less of a problem.

To answer your question more directly, yes pessimistic locking can be done using ADO. It has been a long time since I've had any reason to try to hurt myself that badly after I established that it was possible, so I'm fuzzy on the details.

-PatP|||This may be what you're looking for:

http://www34.brinkster.com/a213855/|||Pat,

I'm convinced that pessimistic locking is not the ideal solution.
How should i take care of the record-locking then?

Regards,

Sven Peeters|||Ummmm, optimistic locking (http://search.microsoft.com/search/results.aspx?qu=%22optimistic+locking%22&View=msdn&st=b&c=0&s=1&swc=0)? I especially reccomend An update on UPDATing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbdev00/html/vb00e1.asp), but there are lots of good articles to read!

-PatP

Row level trigger

Hi all

I am doing a DB porting project in which i need to translate oracle specific queries to sql server.I need to convert following trigger to sqlserver specific .Any help will be appreciated

CREATE OR REPLACE TRIGGER T_BI_R_TARGET_OBJECTIVE
BEFORE INSERT
ON TARGET_OBJECTIVE
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW WHEN (NEW.TARGET_OBJECTIVE_ID IN (NULL,0))
DECLARE
seq_id NUMBER;
BEGIN
SELECT TARGET_OBJECTIVE_ID_SEQ.NEXTVAL INTO seq_id FROM dual;
:new.TARGET_OBJECTIVE_ID := seq_id;
END;

Regards
sreenathFirst of all read thru SQL books online for TRIGGERS topic which explains the information on SQL Server.

HTH

Row IDs in resultset

Hi,
I would like to get Row IDs to number my resultset from 1 to whatever. For
example, if I have 10 records from the following statement :
select FirstName, LastName from employees
I would like to number the records from 1 to 10. How do I do it? No cursor
please.
TIAhttp://www.aspfaq.com/show.asp?id=2427
Note, this does not include any information about SQL Server 2005's
ROW_NUMBER function, which makes this whole process much easier...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"John" <someone@.microsoft.com> wrote in message
news:%23kz88y84FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to get Row IDs to number my resultset from 1 to whatever. For
> example, if I have 10 records from the following statement :
> select FirstName, LastName from employees
> I would like to number the records from 1 to 10. How do I do it? No
> cursor please.
> TIA
>|||> http://www.aspfaq.com/show.asp?id=2427
> Note, this does not include any information about SQL Server 2005's
> ROW_NUMBER function, which makes this whole process much easier...
Hey man, how many hands do you think I have? :-)|||Based on the amount of information on the site, I'd say somewhere between
four and six?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXowA684FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hey man, how many hands do you think I have? :-)
>|||Method 1:
If one of the columns in query is unique, the following calculates a
sequential 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
Method 2:
If you are using a stored procedure, you can insert your result into a
temporary table that has an identity column, and then select the final
result from that table. For example:
create table #myresult
(
[Seq] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [int] ,
[Col2] [int]
)
insert into #myresult select Col1, Col2 from MyTable
select Seq, Col1, Col2 from MyTable
drop table #MyResult
"John" <someone@.microsoft.com> wrote in message
news:%23kz88y84FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to get Row IDs to number my resultset from 1 to whatever. For
> example, if I have 10 records from the following statement :
> select FirstName, LastName from employees
> I would like to number the records from 1 to 10. How do I do it? No
> cursor please.
> TIA
>|||More like 17. :)
ML

Row failed to retrieve on last operation

Hello,

I am getting the following errors when "inserting" data into my tables using Miscrosoft SQL Server Management Studio with a SQL Server 2005 database...

"This row was successfully committed to the database. However, a problem occurred when attempting to retrieve the data back after the commit. Because of this, the displayed data in this row is read-only. To fix this problem, please re-run the query."

In the status bar:

"row failed to retrieve on last operation"

I am hoping I just have some database properties set wrong but have not been able to figure it out.

close cursor on commit is set to False, set it to True, retested, no affect on the error.

other info:

The database is used to supply data to a Microsoft Access ADP application

Are you getting any error number with this?

Anything in the SQL logs?

Does this happen on all inserts or just certain ones?

|||

There is no error number that comes with this error.

There is nothing significant in the logs...

This error occurs when inserting a new record to any table in the database. The record does insert but is not visible until I have followed the instructions of the error msg.

Error Log:

2007-07-04 19:00:42.00 Server (c) 2005 Microsoft Corporation.
2007-07-04 19:00:42.00 Server All rights reserved.
2007-07-04 19:00:42.00 Server Server process ID is xxxx.
2007-07-04 19:00:42.00 Server Authentication mode is xxxxxx.
2007-07-04 19:00:42.00 Server Logging SQL Server messages in file xxxxx.
2007-07-04 19:00:42.00 Server This instance of SQL Server last reported using a process ID of xxxx at 7/4/2007 7:00:00 PM (local) 7/5/2007 2:00:00 AM (UTC). This is an informational message only; no user action is required.
2007-07-04 19:00:42.00 Server Registry startup parameters:
2007-07-04 19:00:42.00 Server xxxxx

2007-07-04 19:00:42.00 Server xxxxxx

2007-07-04 19:00:42.00 Server xxxxxx

2007-07-04 19:00:42.01 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-07-04 19:00:42.01 Server Detected X CPUs. This is an informational message; no user action is required.
2007-07-04 19:00:42.51 Server Using dynamic lock allocation. Initial allocation of xxxx Lock blocks and xxxxx Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-07-04 19:00:42.57 Server Database mirroring has been enabled on this instance of SQL Server.
2007-07-04 19:00:42.57 spidxs Starting up database 'master'.
2007-07-04 19:00:42.95 spidxs Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2007-07-04 19:00:43.01 spidxs SQL Trace ID 1 was started by login "xxxxxx".
2007-07-04 19:00:43.04 spidxs Starting up database 'xxxxxxxxxxx'.
2007-07-04 19:00:43.06 spidxs The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2007-07-04 19:00:43.20 spidxs Starting up database 'model'.
2007-07-04 19:00:43.21 spidxs Server name is 'xxxxx\MYDB'. This is an informational message only. No user action is required.
2007-07-04 19:00:43.43 spidxs Clearing tempdb database.
2007-07-04 19:00:43.71 spidxs Starting up database 'tempdb'.
2007-07-04 19:00:43.75 spidxs The Service Broker protocol transport is disabled or not configured.
2007-07-04 19:00:43.75 spidxs The Database Mirroring protocol transport is disabled or not configured.
2007-07-04 19:00:43.76 spidxs Service Broker manager has started.
2007-07-04 19:00:44.23 Server A self-generated certificate was successfully loaded for encryption.
2007-07-04 19:00:44.25 Server Server is listening on [ xxxxxx].
2007-07-04 19:00:44.25 Server Server local connection provider is ready to accept connection on [ xxx].
2007-07-04 19:00:44.25 Server Server named pipe provider is ready to accept connection on [xxxx].
2007-07-04 19:00:44.25 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2007-07-04 19:00:44.28 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2007-07-04 19:00:44.43 spidxs Starting up database 'msdb'.

|||

Can you send the DML of the table? And do you have a trigger on the table? I assume you are doing this in the "Open table" tool.

It seems to me that you possibly are changing the key value in a trigger or something and it cannot find the row with the key you edited. I get that error when I edit the following table:

create table fred

(

fredId int primary key,

value varchar(10)

)

go

create trigger fred$insertTrigger

on fred

after insert

as

update fred

set fredId = fredId * 10000

where fredId in (select fredId from inserted)

go

|||There is a constraint for a default in the table and for some reason, when using the open table tool, the default is not being applied automatically and that is what I believe makes the error msg appear.|||That sounds plausible. Can you send the DML for the table? I will test it too.

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 Issue

How can rounding function be used so that following result is
accomplished:
rounding of 112.945 should yield 112.94
where as
rounding of 112.946 should yield 112.95
*** Sent via Developersdex http://www.examnotes.net ***>> How can rounding function be used so that following result is
Use ROUND( @.v, 2, CASE WHEN RIGHT( @.v, 1) <= 5 THEN 1 ELSE 0 END )
Anith|||Well to me in your senerio > .004 rounds up, if you want to change that
behavior, subtract .005 prior to rounding... (ick)
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:ely3YxGmFHA.708@.TK2MSFTNGP09.phx.gbl...
> How can rounding function be used so that following result is
> accomplished:
> rounding of 112.945 should yield 112.94
> where as
> rounding of 112.946 should yield 112.95
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||This should do the trick, but please do some testing :)
declare @.value numeric(10,3)
set @.value = 112.995
select cast(round(@.value - 0.0001, 2) as numeric(10,2))
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:ely3YxGmFHA.708@.TK2MSFTNGP09.phx.gbl...
> How can rounding function be used so that following result is
> accomplished:
> rounding of 112.945 should yield 112.94
> where as
> rounding of 112.946 should yield 112.95
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Thanks, but this does not seem to work.
*** Sent via Developersdex http://www.examnotes.net ***|||Right, right, you want the 6 to round up. Well, suptracting .001 should do
it then...
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:OYAO8JHmFHA.3960@.TK2MSFTNGP12.phx.gbl...
> Thanks, but this does not seem to work.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Louis..nice solution, it seems to work.
*** Sent via Developersdex http://www.examnotes.net ***

Rounding Calculated Member

I have the following calculated member that is being used to create Offline/Local cubes.

CALCULATE;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage]

AS Case

When IsEmpty( [Measures].[Event Count] )

Then 0

Else ((

[EE Event Template Type Dim].[Event Types].CurrentMember,

[Measures].[Event Count]) /

( [EE Event Template Type Dim].[Event Types].[(All)].[All],

[Measures].[Event Count]

)*100)

End,

FORMAT_STRING = "###.##%",

BACK_COLOR = 12632256 /*Silver*/ ,

FORE_COLOR = 16744576 /*R=128, G=128, B=255*/ ,

VISIBLE = 1 ;

I'm looking for a way to round the result. The FORMAT_STRING functionality does not carry over when I create the local cube so I end up with values like 28.90909097.

Any suggestions would be appreciated.

Tristan

Tristan,

Have you tried the "format" function?

Format(

Case

When IsEmpty( [Measures].[Event Count] )

Then 0

... rest of case statement from original calculation

,"###.##%")

|||That did the trick! Much Thanks!

Round funtion on entire columns in MSSQL?

Hi,

I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:

update gbkmut
set bdr_hfl = round(bdr_hfl,2)

can anyone help me?

cheers,

steveOn 5 May 2004 02:40:15 -0700, steve wrote:

>Hi,
>I'd like to round all amounts in a certain column to 2 decimals.
>I tried the following query, but eventhough the syntax is correct, it
>doesn't give any result:
>update gbkmut
>set bdr_hfl = round(bdr_hfl,2)
>can anyone help me?
>cheers,
>steve

Hi Steve,

What do you mean with "doesn't give any result"?

If you mean that no rows were returned by the update statement, then
this is expected behaviour. An UPDATE-statement will update the data,
nothing more nothing less. Use SELECT if you want to see anything.

If you mean that after executing the above UPDATE, you still have data
with more than two non-zero digits after the decimal point, I'd ask
you to post more details (table definition in the form of CREATE TABLE
statements, sample data in the form of INSERT statements, expected
output and the output you got) so that others can try if they can
reproduce this apparantly erroneous behaviour.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||stefan.van.den.steen@.exact.be (steve) wrote in message news:<79d8a1b0.0405050140.6f442694@.posting.google.com>...
> Hi,
> I'd like to round all amounts in a certain column to 2 decimals.
> I tried the following query, but eventhough the syntax is correct, it
> doesn't give any result:
> update gbkmut
> set bdr_hfl = round(bdr_hfl,2)
> can anyone help me?
> cheers,
> steve

At first glance, your UPDATE statement seems to be OK. Can you give
some more information? In particular, what is the data type of the
bdr_hfl column, and can you give some sample values, as well as your
expected result? And what does "doesn't give any result" mean?

Simon|||steve (stefan.van.den.steen@.exact.be) writes:
> I'd like to round all amounts in a certain column to 2 decimals.
> I tried the following query, but eventhough the syntax is correct, it
> doesn't give any result:
> update gbkmut
> set bdr_hfl = round(bdr_hfl,2)

There is very little information, but I would guess that your column is
of datatype float. Float is an approxamite datatype, which means that
far from all values can be stored exactly in a float. For instance,
try this:

select convert(float, 1.89)

In Query Analyzer this displays as 1.8899999999999999.

In many situations, it is possible to cope with these extra decimals at
the end; you only need some care. If you need exact numbers, you must
use the decimal type instad. The drawback is that with decimal, you
must decide from the beginning which range you handle.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

round

Hello I need to find a function tha allowed to obtain the following result:
SELECT FFFF(14.9879,2)
go
14.98
which function I must use?
What about
Select ROUND(14.98756,2,1)
Would be some numeric chopping, else you should use LEFT funtion with
CHARINDEX.
HTH, Jens Suessmeyer.
"pippo" <pippo@.discussions.microsoft.com> schrieb im Newsbeitrag
news:4934B137-6A93-4824-AA7C-DD80387A206B@.microsoft.com...
> Hello I need to find a function tha allowed to obtain the following
> result:
> SELECT FFFF(14.9879,2)
> go
> 14.98
> which function I must use?
>
|||SELECT ROUND(14.9879,2,1)
Using 1 as the third parameter causes ROUND to truncate (round down).
This won't necessarily cause your application to *display* only two
decimals. The display format of numeric values is determined by your
application, not by SQL Server.
David Portas
SQL Server MVP

round

Hello I need to find a function tha allowed to obtain the following result:
SELECT FFFF(14.9879,2)
go
14.98
which function I must use?SELECT ROUND(14.9879,2,1)
Using 1 as the third parameter causes ROUND to truncate (round down).
This won't necessarily cause your application to *display* only two
decimals. The display format of numeric values is determined by your
application, not by SQL Server.
--
David Portas
SQL Server MVP
--|||What about
Select ROUND(14.98756,2,1)
Would be some numeric chopping, else you should use LEFT funtion with
CHARINDEX.
HTH, Jens Suessmeyer.
"pippo" <pippo@.discussions.microsoft.com> schrieb im Newsbeitrag
news:4934B137-6A93-4824-AA7C-DD80387A206B@.microsoft.com...
> Hello I need to find a function tha allowed to obtain the following
> result:
> SELECT FFFF(14.9879,2)
> go
> 14.98
> which function I must use?
>