ASP + ADO + SQL 2005, new column [ORD ID]?

I'm developing some queries using ASP with a componente built in Visual basic 6.0 SP6, I get the data using ADO to a SQL 2005 (version 9.0.1406) when I run a code like this:

Code Snippet

Sub ShowRs(rsAuxi)
Dim Campo
Response.Write "

"
Response.Write " Ord ID"
For Each Campo In rsAuxi.Fields
Response.Write " " & Campo.Name & ""
Next
Response.Write ""
Do While Not rsAuxi.EOF
Response.Write " "
Response.Write " " & rsAuxi.Absoluteposition & ""
For Each Campo In rsAuxi.Fields
Response.Write " " & Campo.Value & ""
Next
rsAuxi.MoveNext
Response.Write ""
loop
Response.Write "

"
end sub

the ADO execute a Store Procedure like this:

Code Snippet

CREATE PROCEDURE [dbo].[SP_TPO_REQ_HEAD]
AS
SET NOCOUNT ON
BEGIN
SELECT
[COD_REQ_HEADER],
[NUM_REQ_HEADER],
[FECHA]
FROM [dbo].[TPO_REQ_HEADER]
END

I supose that when I draw the ADO recordset in the ASP (using the frist code) I get a table with 3 columns, but I get a table with 4 fields!!! and the first column is ORDER ID, now my question is WHO IS ADDING THIS NEW COLUMN?

Thanks in advance

JJ

You must have been executing another procedure than the one mentioned above. Another attribute is not just added automagically.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

The problem is your own code, which is adding the "new colunm":

' Your code, commented

Sub ShowRs(rsAuxi)

Dim Campo

Response.Write "<table border=1><tbody><tr>"

' This adds the Heading for the Ord ID "field"

Response.Write "<td class=TipoLetra>Ord ID</td>"

' This adds the heading for the fields returned in the Stored Procedure / in your example, 3 fields

For Each Campo In rsAuxi.Fields

Response.Write "<td class=TipoLetra>" & Campo.Name & "</td>"

Next

Response.Write "</tr>"

' This iterates as many records as your recordset (returned from stored procedure)

Do While Not rsAuxi.EOF

Response.Write "<tr>"

' THIS ADDS THE "FIELD", which is just the record number in the recordset !

Response.Write "<td class=TipoLetra>" & rsAuxi.Absoluteposition & "</td>"

' This iterates as many fields in each record, as returned in the recordset (3 in your example)

For Each Campo In rsAuxi.Fields

Response.Write "<td class=TipoLetra>" & Campo.Value & "</td>"

Next

rsAuxi.MoveNext

Response.Write "</tr>"

loop

Response.Write "</tbody></table>"

end sub

|||

thanks... I guess that I need a long vacations...

ASP - Insert Multiple records

I wanna know how to insert all the records that is checked into my SQL Server.. BUT all the records inserted is the same except for one column in my SQL Server which is the 'Partner' column..All the records that is checked?
Please explain some more!|||Well... i have a webpage in ASP. It displays all the ringing tones on the particular tones.asp page. Currently there is no check box as I do not know how to implement the method..

Therefore what i mean is that all the ringtones (to insert as new record in db), I have to view one by one to insert.

What i want to know is the way on how to mass insert all the ringtones with just a single click on the button. Meaning that i checked a few check boxes, and click the button..

You know what i mean my fren..?

Hope you can help..|||BTW, its sort of like the HOTMAIL... where you checked some boxes and PUT INTO FOLDER.... bla bla bla..

But what i want now is checked my ringtones check boxes and click a button to insert as a new record in my DB..|||<%
If len(trim(Request.Form("mTones"))) > 0 Then
Dim SQLStmt, x
SQLStmt = ""
for x = 1 to request.form("mTones").count
SQLStmt = SQLStmt & "INSERT INTO kek (tone) VALUES('"request.form("mTones")(x) & "';"
next
conn.execute(SQLStmt)
end if
%>

<form methos=post action=tones.asp>
<input type=checkbox name="mTones" id="tone1">tone1
<input type=checkbox name="mTones" id="tone2">tone2
<input type=submit>
</form>sql

Asking for Column data type

This is an easy one , I need to know the way to check programatically for an SQL server 2000 column data type , if the result is a code , which codes are for the diferent datatypes ( varchar , text , smallint , etc ) , if you send me an url is ok.
thank youselect DATA_TYPE
from Information_Schema.Columns
where table_name = 'table_name'|||Thanks

Ascending Date Column Statistics "dbcc traceon( 2389 )"

I am having trouble with query performance on SQL Server 2005. The database
is a basic book ordering application. A simple one to many Orders to Order
Items. The orders table contains 80,000 rows and the Order Items table
contains 280,000 rows. The problem query contains an inner join with Orders
and Order Items with where clause on Order Date. The problem is when you
execute the query on old data the inner join is based on a hash (1 second
execution). When you execute the query on new data the inner join is based
on a nested loop (2 minute execution). The reason for this is the stats.
The stats for the order date column do not contain the new data so the
estimated rows for the given date range is 1 (when the query actually returns
1320 rows). If I update the stats for order date the query will perform as
expected (hash join). The following blog explains the issue.
http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
In order to fix this issue it appears I have to schedule a manual update of
the stats or use dbcc traceon( 2389 ). This application ran on SQL server
2000 for 4 years with no problems. Why did I not see an issue with SQL
server 2000? Does anyone have any better ideas except manually updating the
stats?First of all, have you tried a HASH JOIN hint? If the hash is working well,
force it.
I have also had a lot of statistics problems in some of the applications
I've been working on, and although I haven't had a chance to try these trace
flags yet, I have found workarounds to every issue (generally, involving
query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
not doing as well as SQL Server 2000 did, but overall I think performance is
improved in most scenarios. Hopefully as MS continues to evaluate use cases
these issues will go away and we can go back to thinking about other things!
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>I am having trouble with query performance on SQL Server 2005. The
>database
> is a basic book ordering application. A simple one to many Orders to
> Order
> Items. The orders table contains 80,000 rows and the Order Items table
> contains 280,000 rows. The problem query contains an inner join with
> Orders
> and Order Items with where clause on Order Date. The problem is when you
> execute the query on old data the inner join is based on a hash (1 second
> execution). When you execute the query on new data the inner join is
> based
> on a nested loop (2 minute execution). The reason for this is the stats.
> The stats for the order date column do not contain the new data so the
> estimated rows for the given date range is 1 (when the query actually
> returns
> 1320 rows). If I update the stats for order date the query will perform
> as
> expected (hash join). The following blog explains the issue.
> http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> In order to fix this issue it appears I have to schedule a manual update
> of
> the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> 2000 for 4 years with no problems. Why did I not see an issue with SQL
> server 2000? Does anyone have any better ideas except manually updating
> the
> stats?
>|||Thanks for the help. The HASH Join hint does work.
"Adam Machanic" wrote:
> First of all, have you tried a HASH JOIN hint? If the hash is working well,
> force it.
> I have also had a lot of statistics problems in some of the applications
> I've been working on, and although I haven't had a chance to try these trace
> flags yet, I have found workarounds to every issue (generally, involving
> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
> not doing as well as SQL Server 2000 did, but overall I think performance is
> improved in most scenarios. Hopefully as MS continues to evaluate use cases
> these issues will go away and we can go back to thinking about other things!
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "achopp" <achopp@.discussions.microsoft.com> wrote in message
> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
> >I am having trouble with query performance on SQL Server 2005. The
> >database
> > is a basic book ordering application. A simple one to many Orders to
> > Order
> > Items. The orders table contains 80,000 rows and the Order Items table
> > contains 280,000 rows. The problem query contains an inner join with
> > Orders
> > and Order Items with where clause on Order Date. The problem is when you
> > execute the query on old data the inner join is based on a hash (1 second
> > execution). When you execute the query on new data the inner join is
> > based
> > on a nested loop (2 minute execution). The reason for this is the stats.
> > The stats for the order date column do not contain the new data so the
> > estimated rows for the given date range is 1 (when the query actually
> > returns
> > 1320 rows). If I update the stats for order date the query will perform
> > as
> > expected (hash join). The following blog explains the issue.
> >
> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
> >
> > In order to fix this issue it appears I have to schedule a manual update
> > of
> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
> > server 2000? Does anyone have any better ideas except manually updating
> > the
> > stats?
> >
>
>|||Or possibly the date_correlation_optimization_option database option might help. See also
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/275fb44b-417a-426a-bf7c-f23308ed72c0.htm.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"achopp" <achopp@.discussions.microsoft.com> wrote in message
news:609F8143-0EB4-4400-B84A-F3D75FBDDE95@.microsoft.com...
> Thanks for the help. The HASH Join hint does work.
> "Adam Machanic" wrote:
>> First of all, have you tried a HASH JOIN hint? If the hash is working well,
>> force it.
>> I have also had a lot of statistics problems in some of the applications
>> I've been working on, and although I haven't had a chance to try these trace
>> flags yet, I have found workarounds to every issue (generally, involving
>> query hints and/or plan forcing). I agree, in some cases SQL Server 2005 is
>> not doing as well as SQL Server 2000 did, but overall I think performance is
>> improved in most scenarios. Hopefully as MS continues to evaluate use cases
>> these issues will go away and we can go back to thinking about other things!
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "achopp" <achopp@.discussions.microsoft.com> wrote in message
>> news:8E35C26D-A3F0-41BB-AE4B-303190B82483@.microsoft.com...
>> >I am having trouble with query performance on SQL Server 2005. The
>> >database
>> > is a basic book ordering application. A simple one to many Orders to
>> > Order
>> > Items. The orders table contains 80,000 rows and the Order Items table
>> > contains 280,000 rows. The problem query contains an inner join with
>> > Orders
>> > and Order Items with where clause on Order Date. The problem is when you
>> > execute the query on old data the inner join is based on a hash (1 second
>> > execution). When you execute the query on new data the inner join is
>> > based
>> > on a nested loop (2 minute execution). The reason for this is the stats.
>> > The stats for the order date column do not contain the new data so the
>> > estimated rows for the given date range is 1 (when the query actually
>> > returns
>> > 1320 rows). If I update the stats for order date the query will perform
>> > as
>> > expected (hash join). The following blog explains the issue.
>> >
>> > http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx
>> >
>> > In order to fix this issue it appears I have to schedule a manual update
>> > of
>> > the stats or use dbcc traceon( 2389 ). This application ran on SQL server
>> > 2000 for 4 years with no problems. Why did I not see an issue with SQL
>> > server 2000? Does anyone have any better ideas except manually updating
>> > the
>> > stats?
>> >
>>

ASC/DESC Clustered Index - will it make a difference in this scenario?

Imagine the following scenario-

Identity(1,1) column ID is primary key and only clustered index key.

Rows will be inserted regularly into this table, hundreds per day.

Queries will be mostly selecting on the most recent records.

In a year, the row will have half a million records or so and only the most recent records will be used. There will be a forward-rolling hot spot, of most recent records.

Does the direction of the ID column in the clustered index make a difference?

I'm thinking no, because query plan will go to that leaf in an index seek regardless of whether it is old or new, "bottom" or "top" of index, especially if the query is very specific on the ID.

I've read this

http://mattadamson.blogspot.com/2005/05/choosing-between-ascending-or.html

but it didn't address (or perhaps didn't need to) this sort of scenario.
You would get the hot spot regardless of the direction of the sort. I do question why you would create this key as a clustered index, though. You may find better performance in the application that uses this data if another index more appropriate to the application's access is chosen for the clustered index. (Your primary key is many times not your best choice for your clustered index.)|||

Allen White wrote:

You would get the hot spot regardless of the direction of the sort. I do question why you would create this key as a clustered index, though. You may find better performance in the application that uses this data if another index more appropriate to the application's access is chosen for the clustered index. (Your primary key is many times not your best choice for your clustered index.)

Not trying to avoid the hotspot. The primary key as IDENTITY as clustered index is very oftentimes, in my experience, the best way to go. I'm not alone, check out articles by Kim Tripp among others. I understand that in this scenario the inserts and selects are all going to be happening in the hotspot, but that's where leveraging SNAPSHOT isolation can help.

I'm pretty certain that ASC/DESC won't make a difference in this scenario but I'd welcome any more input.

ASC/DESC Clustered Index - will it make a difference in this scenario?

Imagine the following scenario-

Identity(1,1) column ID is primary key and only clustered index key.

Rows will be inserted regularly into this table, hundreds per day.

Queries will be mostly selecting on the most recent records.

In a year, the row will have half a million records or so and only the most recent records will be used. There will be a forward-rolling hot spot, of most recent records.

Does the direction of the ID column in the clustered index make a difference?

I'm thinking no, because query plan will go to that leaf in an index seek regardless of whether it is old or new, "bottom" or "top" of index, especially if the query is very specific on the ID.

I've read this

http://mattadamson.blogspot.com/2005/05/choosing-between-ascending-or.html

but it didn't address (or perhaps didn't need to) this sort of scenario.
You would get the hot spot regardless of the direction of the sort. I do question why you would create this key as a clustered index, though. You may find better performance in the application that uses this data if another index more appropriate to the application's access is chosen for the clustered index. (Your primary key is many times not your best choice for your clustered index.)|||

Allen White wrote:

You would get the hot spot regardless of the direction of the sort. I do question why you would create this key as a clustered index, though. You may find better performance in the application that uses this data if another index more appropriate to the application's access is chosen for the clustered index. (Your primary key is many times not your best choice for your clustered index.)

Not trying to avoid the hotspot. The primary key as IDENTITY as clustered index is very oftentimes, in my experience, the best way to go. I'm not alone, check out articles by Kim Tripp among others. I understand that in this scenario the inserts and selects are all going to be happening in the hotspot, but that's where leveraging SNAPSHOT isolation can help.

I'm pretty certain that ASC/DESC won't make a difference in this scenario but I'd welcome any more input.

AS400/DB2 different column names in different configurations

Hello!

I am creating a package that will be executed against different AS400/DB2 databases. (I use a Foreach Loop). The AS400/DB2 is configured a little bit differently. This results in that the same column can have different name in different AS400/DB2. For example the column nameA4￡ADG, A4$ADG and A4?ADG is the same column.

In the Data Flow I use DataReader. Does anyone know how to get past this issue without creating one package for each column name?

Best regard

Tina

I'm not an AS400 person (or DB2), so I have no idea if this will work. But, it seems like you could use an expression to set the SQL Statement on the DataReader source, and alias the differently named columns to the same name. To set an expression on the data reader, you have to select the data flow task in the control flow, go to properties, and go to expressions.|||

Hello!

Thank you for your help!

But know I have solved it. I use different Data Flow for each set of column names. Depending on the company that is executed I defined the Precedence Constraint to "Expression and Constraint" and use the Expression:

@.[User::varCompanyName]=="CDHU"

In the Data Reader I have to set ValidateExternalMetadata to False

Best regards,

Tina

as DTS in MS SQl Server what is ther in oracle

Hi All,
I used DTS tool of SQL server.using DTS we can map two tables on column
by column basis.
I want to know what tool is there to map two oracle tables on colum by
column basis.
Thanks & Regards,
PrakashOn 12 Sep 2006 04:17:56 -0700, sainiprakash@.gmail.com wrote:

>I used DTS tool of SQL server.using DTS we can map two tables on column
>by column basis.
>I want to know what tool is there to map two oracle tables on colum by
>column basis.
DTS works with Oracle, at least to some degree, if you don't find any
other solution.
Roy Harvey
Beacon Falls, CT

as DTS in MS SQl Server what is ther in oracle

Hi All,
I used DTS tool of SQL server.using DTS we can map two tables on column
by column basis.
I want to know what tool is there to map two oracle tables on colum by
column basis.
Thanks & Regards,
PrakashOn 12 Sep 2006 04:17:56 -0700, sainiprakash@.gmail.com wrote:
>I used DTS tool of SQL server.using DTS we can map two tables on column
>by column basis.
>I want to know what tool is there to map two oracle tables on colum by
>column basis.
DTS works with Oracle, at least to some degree, if you don't find any
other solution.
Roy Harvey
Beacon Falls, CT

article for subscription that does not exist ?!?

Hi There

I hope someone can help me asap.

I need to alter a column on a replicated table.

So i execute sp_dropsubscription and sp_droparticle for the table for all publication to that article in the database.

Usually i just alter the table and then execute sp_adddarticle and sp_addsubscription afterwards and everything is cool.

However in this case after i drop the subscriptions for the article, when i try alter the table it says it is being replicated.

I query sysobjects and see that the table has replinfo = 1 , this is snapshot replication, but i only have transactional replication ? I used to have snapshot but that was dropped long ago.

I then query sysarticles and i find the table, however the pubid (publication id) for the table is equal to the publication id of another databases publication ? I also have found in sysarticles articles with a publication id's equal to publications that do not exist, for example articles will have pubid of 2 , but if i run sp_helppublication on all user databases there is no publciation with an id of 2 ?

Please help, even after i drop all subscriptions to an article it seems that sql server thinks the article belongs to another databases publciation or publications that no longer exist?

Maybe there is some sort of cleanup sp i can run or something but it seems to me sql server has gotten articles confused with old deleted publications that no longer exist.

Therefore after dropping all subscriptions i still cannot alter tables as sql server think it is still being published but it is not!

Thanx

if you don't mind dropping the publication (script it out before dropping it), you can clean everything up by executing proc sp_removedbreplication. Let me know if this doesn't work.|||

Hi

Unfortunately we have too many publications and they are too big , so that is not an option.

However i deleted the problem entry from sysarticles and everything worked after that.

Thanx

Array List in DB

Hi,
Is it possible to have a column as array in SQL Server 2000- similar to Oracle's varray. If yes, how?
rgds,
Kishoreyes, it is possible, but it is not recommended, because it will usually require table scans to access the contents|||I don't see a lot of point in Array fields when subtable give you the same functionality with less hassle and more control...|||subtable? what's a subtable? BOL is totally ignorant of that terminology|||I just haven't gotten around to writing that chapter yet. So busy posting on the forum, you know...|||What are either of you 2 talking about?

The only array stuff I've seen was in Ken Hendersons Book The Guru's Guide to SQL Server Stored Procedures, XML and HTML

Chapter 23...but that dealt with Dll's, ect...all very painful AND dangerous, if you weren't careful

Why, what do you need an array for?

PS A table is like an array anyway..you could use a table variable|||i believe blindman was actually just joking around

blindman, please let us know when you do this

as for my reply in post #2, yes, you can store an array as a comma-separated list of values in a single varchar column

i would've thought this would be obvious to any frequent poster here, and also why it's not a good idea|||as for my reply in post #2, yes, you can store an array as a comma-separated list of values in a single varchar column

That is not an array|||pedant!!

a lot depends on what programming language you have sitting on the front end

i know of several languages where you can simply assign the string to an array, and voila!

what would you call an array, please? and make sure you mention which language

;)|||You are both pedants!

When I get around to writing my Books Online chapter on subtables I will clearly define whether a comma-separated list is an array. Until then, you will just have to wait to find out.

And I am SERIOUS about this! ;)|||And I am SERIOUS about this! ;)Seriously what?

-PatP|||That is not an arrayTechnically, that's an unbounded vector. What's the difference though?

-PatP|||pedant!!

Hardly

a lot depends on what programming language you have sitting on the front end

Not really

i know of several languages where you can simply assign the string to an array, and voila!

Assign the string to an array..your words...the string is not an array

what would you call an array, please? and make sure you mention which language

An array is an object...pick you language...doesn't matter...they all behave the same way...|||brett, i know you can spell pedant (at least, so far), but do you actually know what it means?

"Assign the string to an array..your words...the string is not an array"

now that's pedantic!!|||God made google didn't he?

An array is more than data...Pat, I think you need to give up your "title" for Rudy...

How do you get the information rs.0 from a string for example?

Did you read Henderson's book?|||God made google didn't he?no, She didn't, and besides, that is such a non sequitur

An array is more than data...quite right, it is data plus relationship/sequencing

Pat, I think you need to give up your "title" for Rudy...if you are referring to "curmudgeon" then sorry, but you lead that race

Did you read Henderson's book?just once

a long time ago

:)|||Mathematically, an array is simply a bounded vector. Less formally, an array (http://searchstorage.techtarget.com/sDefinition/0,,sid5_gci211596,00.html) is a group of objects with the same attributes that can be addressed individually.

I ain't gonna give up my title without a fight! I'd be quite willing to add pedant (http://dictionary.reference.com/search?q=pedant) to my title though, if only they would let us (http://www.dbforums.com/t1070319.html)!

-PatP|||LOL, dude

which of the three subtitles did you wish to have?

1. One who pays undue attention to book learning and formal rules.
2. One who exhibits one's learning or scholarship ostentatiously.
3. Obsolete. A schoolmaster.

i'll cop to the last one only, i sure ain't the first

half the stuff i know i learned from you guys!

;)|||LOL, dude

which of the three subtitles did you wish to have?

1. One who pays undue attention to book learning and formal rules.
2. One who exhibits one's learning or scholarship ostentatiously.
3. Obsolete. A schoolmaster.

i'll cop to the last one only, i sure ain't the first

half the stuff i know i learned from you guys!

;)I'll just take pedant... Leave the determination of fit as an exercise for the reader ;)

Ich spreche sehr kliene Deutsche. You couldn't have learned half of what you know from me.

-PatP|||that's right

it's the other half

:cool:|||Is that like...

"I taught him everything I know...and he STILL doesn't know anything"

that is such a non sequitur

No it's not (although I am quite fond of them)...you were refering that I wouln't know what the word meant (which I didn't)...I mean A LOT of people who post here don't use Google...I use it all the time...soo...do I need to finish that thought?

if you are referring to "curmudgeon" then sorry, but you lead that race

Really? I'm not the one calling people pedant!!|||Really? I'm not the one calling people pedant!!really

you're being pedantic

that's far worse

;) :) :) :)|||You all sound like a pack of third-graders who have just been given dictionaries.

Seriously!|||I always sound like a third grader that just discovered a dictionary. I'm the only person I know who's cell phone has a collegiate dictionary on it!

Isn't pedant the word in the Pink Panther theme song?

I always thought that Orkin should license it as "dead-ant", but that would be too confusing with the aardvark on the same show... Ooops, I'm dating myself again, aren't I?

-PatP|||Those definitions for array sound very fishy. "a group of objects..."? That's a collection, which still uses the basic array concepts of storing and referencing data, but it's a collection. Don't you all remember your Intro to Computers 101? Isn't it just a linked list (I'll take double-linked list if that's what it is ;))?

Aritmectic Operator in Formula?

Hi all,

this formula doesn't work as calculated column but WHY?

Column [MAIN]: VARCHAR(150)

VALUE : 'blabla - zobzob'

SUBSTRING([MAIN],1,CHARINDEX('-',[MAIN])) WORKS and return 'blabla -'

BUT

SUBSTRING([MAIN],1,CHARINDEX('-',[MAIN])-2) DOESN'T WORK in a Formula BUT well in a select clause...

Any Idea?

Thanks

In what way does it 'not work'...?

It seems to work ok... (SQL Server 2000)

create table #x (main varchar(20) not null)

insert #x select 'blabla - zobzob'

alter table #x add b as SUBSTRING(MAIN,1,CHARINDEX('-',MAIN)-2)

select * from #x

drop table #x

main b
-- --
blabla - zobzob blabla

/Kenneth

|||

Yes indeed, it works with a sql script but NOT manually in SQL Server 2000... Very Strange...

Tks a lot

|||

Hmm.. what do you mean by 'manually'..?

In my mind T-SQL code in QA is as 'manual' as you can get..?

/Kenneth

|||With the editor provided in SQL Server 2000...|||

You mean Enterprise Manager?

I'd suggest as a solution that you don't use it for other purposes than viewing.
For one thing it doesn't allow you any transactional control when you perform write operations, and some things it does in an awkward way, which adds up to that it takes unnecessary long time and/or large amount of resources to complete.

Make the wrong choice in EM and chances increase that you're looking for the latest good backup, instead of just typing ROLLBACK in QA, when something unexpected happened with the latest change. This ability is due to the fact that you can start your scripts with BEGIN TRAN (don't forget it) before actually performing the change. This is something that EM doesn't give you. =:o/

EM is imo not a fitting tool for database change management. (perhaps with the exception of user and roles management)

Do all your (data/schema) changes in Query Analyzer by controlled scripts instead.

/Kenneth

Arithmetic overflow error converting numeric to data type numeric for View

Hi,
When I do
Select * From V_TABLE Where Column = 1
, I get arithmetic exception. Column type is int.
When I do
Select * From V_TABLE Where Column = 10
, I get no arithmetic exception at all.
When I do
Select * From TABLE Where Column = 1
, I get no arithmetic exception at all.
So far I undestood that I get exceptions for view only and only if
expression in Where is 1. Any ideas why it happens?
SQL server is MS SQL server 2005.Sergey
What if you run SELECT * FROM V_TABLE Where Column = '1', do you still get
error?
"Skochkar" <Sergey.Kochkarev@.mail.ru> wrote in message
news:1192530234.022276.104230@.i13g2000prf.googlegroups.com...
> Hi,
> When I do
> Select * From V_TABLE Where Column = 1
> , I get arithmetic exception. Column type is int.
> When I do
> Select * From V_TABLE Where Column = 10
> , I get no arithmetic exception at all.
> When I do
> Select * From TABLE Where Column = 1
> , I get no arithmetic exception at all.
> So far I undestood that I get exceptions for view only and only if
> expression in Where is 1. Any ideas why it happens?
> SQL server is MS SQL server 2005.
>|||> So far I undestood that I get exceptions for view only and only if
> expression in Where is 1. Any ideas why it happens?
Can you please post your DDL (CREATE TABLE and VIEW)?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Skochkar" <Sergey.Kochkarev@.mail.ru> wrote in message
news:1192530234.022276.104230@.i13g2000prf.googlegroups.com...
> Hi,
> When I do
> Select * From V_TABLE Where Column = 1
> , I get arithmetic exception. Column type is int.
> When I do
> Select * From V_TABLE Where Column = 10
> , I get no arithmetic exception at all.
> When I do
> Select * From TABLE Where Column = 1
> , I get no arithmetic exception at all.
> So far I undestood that I get exceptions for view only and only if
> expression in Where is 1. Any ideas why it happens?
> SQL server is MS SQL server 2005.
>

Arithmetic overflow error - in a changed table!

Hello.
I had a table with identity column set to datatype [smallint]. I've
reached near 32800 records. That's the limit for [smallint].
So, I've changed the identity datatype to [int], that SHOULD allow me
to have more records. But the error continues.
"Arithmetic overflow error converting expression to data type
smallint".
AND there's no smallint anymore. It's weird. See by yourself:
CREATE TABLE [forum] (
[forumid] [int] IDENTITY (1, 1) NOT NULL ,
[forumdata] [smalldatetime] NOT NULL CONSTRAINT [DF_forum_forumdata]
DEFAULT (getdate()),
[forumnome] [varchar] (100) NULL ,
[forumemail] [varchar] (100) NULL ,
[forumidade] [varchar] (50) NULL ,
[forumcidade] [varchar] (100) NULL ,
[forummensagem] [varchar] (1000) NULL ,
[forumstatus] [bit] NULL CONSTRAINT [DF_forum_forumstatus] DEFAULT
(1),
[forumip] [char] (15) NULL ,
CONSTRAINT [PK_forum] PRIMARY KEY CLUSTERED
(
[forumid]
) ON [PRIMARY]
) ON [PRIMARY]
Am I missing something? Should I need to execute some kind of
"table-reseter" to make SQLServer2000 forget it's [smallint] past?I might be a variable that you are using in one of you sp where you do the
insert
"Rodrigo Volponi" <volps@.hotmail.com> wrote in message
news:5e7e88ed.0503230723.7dace12e@.posting.google.com...
> Hello.
> I had a table with identity column set to datatype [smallint]. I've
> reached near 32800 records. That's the limit for [smallint].
> So, I've changed the identity datatype to [int], that SHOULD allow me
> to have more records. But the error continues.
> "Arithmetic overflow error converting expression to data type
> smallint".
> AND there's no smallint anymore. It's weird. See by yourself:
> --
> CREATE TABLE [forum] (
> [forumid] [int] IDENTITY (1, 1) NOT NULL ,
> [forumdata] [smalldatetime] NOT NULL CONSTRAINT [DF_forum_forumdata]
> DEFAULT (getdate()),
> [forumnome] [varchar] (100) NULL ,
> [forumemail] [varchar] (100) NULL ,
> [forumidade] [varchar] (50) NULL ,
> [forumcidade] [varchar] (100) NULL ,
> [forummensagem] [varchar] (1000) NULL ,
> [forumstatus] [bit] NULL CONSTRAINT [DF_forum_forumstatus] DEFAULT
> (1),
> [forumip] [char] (15) NULL ,
> CONSTRAINT [PK_forum] PRIMARY KEY CLUSTERED
> (
> [forumid]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> --
> Am I missing something? Should I need to execute some kind of
> "table-reseter" to make SQLServer2000 forget it's [smallint] past?|||Show us how to get that error.
Rodrigo Volponi wrote:
> Hello.
> I had a table with identity column set to datatype [smallint]. I've
> reached near 32800 records. That's the limit for [smallint].
> So, I've changed the identity datatype to [int], that SHOULD allow me
> to have more records. But the error continues.
> "Arithmetic overflow error converting expression to data type
> smallint".
> AND there's no smallint anymore. It's weird. See by yourself:
> --
> CREATE TABLE [forum] (
> [forumid] [int] IDENTITY (1, 1) NOT NULL ,
> [forumdata] [smalldatetime] NOT NULL CONSTRAINT [DF_forum_forumdata]
> DEFAULT (getdate()),
> [forumnome] [varchar] (100) NULL ,
> [forumemail] [varchar] (100) NULL ,
> [forumidade] [varchar] (50) NULL ,
> [forumcidade] [varchar] (100) NULL ,
> [forummensagem] [varchar] (1000) NULL ,
> [forumstatus] [bit] NULL CONSTRAINT [DF_forum_forumstatus] DEFAULT
> (1),
> [forumip] [char] (15) NULL ,
> CONSTRAINT [PK_forum] PRIMARY KEY CLUSTERED
> (
> [forumid]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> --
> Am I missing something? Should I need to execute some kind of
> "table-reseter" to make SQLServer2000 forget it's [smallint] past?
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hi Rodrigo,
It's very strange without a doubt. One solution should be simply do the
following steps:
1)select * into forum20050323 from forum
2)truncate table forum
3)insert into forum
select (do just sp_help forum in order to get all the filed but leaving off
the identity field)
from forum20050323
truncate command is bearing on mind the original values on a identity field.
Let me know your doubts or concerns with this.
See you later,
"Denis" wrote:

> I might be a variable that you are using in one of you sp where you do the
> insert
> "Rodrigo Volponi" <volps@.hotmail.com> wrote in message
> news:5e7e88ed.0503230723.7dace12e@.posting.google.com...
>
>|||Check for triggers on the table.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rodrigo Volponi" <volps@.hotmail.com> wrote in message
news:5e7e88ed.0503230723.7dace12e@.posting.google.com...
> Hello.
> I had a table with identity column set to datatype [smallint]. I've
> reached near 32800 records. That's the limit for [smallint].
> So, I've changed the identity datatype to [int], that SHOULD allow me
> to have more records. But the error continues.
> "Arithmetic overflow error converting expression to data type
> smallint".
> AND there's no smallint anymore. It's weird. See by yourself:
> --
> CREATE TABLE [forum] (
> [forumid] [int] IDENTITY (1, 1) NOT NULL ,
> [forumdata] [smalldatetime] NOT NULL CONSTRAINT [DF_forum_forumdata]
> DEFAULT (getdate()),
> [forumnome] [varchar] (100) NULL ,
> [forumemail] [varchar] (100) NULL ,
> [forumidade] [varchar] (50) NULL ,
> [forumcidade] [varchar] (100) NULL ,
> [forummensagem] [varchar] (1000) NULL ,
> [forumstatus] [bit] NULL CONSTRAINT [DF_forum_forumstatus] DEFAULT
> (1),
> [forumip] [char] (15) NULL ,
> CONSTRAINT [PK_forum] PRIMARY KEY CLUSTERED
> (
> [forumid]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> --
> Am I missing something? Should I need to execute some kind of
> "table-reseter" to make SQLServer2000 forget it's [smallint] past?

Arithmetic Overflow error

Can someone help me with this error:

I need to change the datatype of a column from int to tinyint.

I did this :

ALTER TABLE EmailTracking ALTER COLUMN URLID tinyint

and got this error:

Msg 220, Level 16, State 2, Line 1

Arithmetic overflow error for data type tinyint, value = 74860.

The statement has been terminated.

how do i fix this?

thanks

Rookie, perhaps you could try examining the maximum value on the data. Tinyint is only up to 255. If the maximum of your data is more than 255, perhaps you would consider smallint instead, which is up to 32,000. Int on the other hand, is up to 2 billion.
Also, please check for negative values. Tinyint does not allow negative values, but smallint and int do.
Vincent

arithmetic overflow error

Hello,
I have a column of datatype int which has about 100000 rows and i am trying
to sum up the column and i am
getting the arithmetic overflow errror
This is the sql
Declare @.tot bigint
Select @.tot = sum(column1) from tab1
Select @.totHi,
TRY CAST(AS BIGINT)
> Select @.tot = sum(column1) from tab1
Select @.tot = sum(CAST(column1 AS BIGINT)) from tab1
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2003
"SCSQL" <chalasani@.mcleodusa.net> wrote in message
news:%23TYz6iPlDHA.1728@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a column of datatype int which has about 100000 rows and i am
trying
> to sum up the column and i am
> getting the arithmetic overflow errror
> This is the sql
> Declare @.tot bigint
> Select @.tot = sum(column1) from tab1
> Select @.tot
>
>

Arithabort side affects

i recently needed to add an index to a computed column. I know its risky and that the side affects can be quite a pain (the cursorlocation on an adodb recordset setting caught me out for a start!) but i am struggling with scheduled jobs.
Stored procedures that are called from my applications have either been altered to have the required quoted_identifier and/or arithabort settings changed to comply with the new index, or tha app itself calls the set statement on the connection object just prior to the call to the sproc and this seems to work OK, but any sheduled job that references the table will not execute, even a one line exec sproc statement will not execute. It happily runs in QA, but never runs as part of a job because of the arithabort settings. I've tried changing them in the sproc, deleting and re-creating the sproc, i've added them as statements as part of the job step just before the call to the sproc and nothing lets the job execute. What do I do now as i need these jobs to run and i do not want to havre to remove the index.

DaveAlter database test
set arithabort on

This works, ie the scheduled jobs will now run. Can i expect anything untoward to jump out of the closet now with this change at the database level?

Dave

Are there no "Computed" column types? For storing expressions (results of other fields

I just read something interesting here: http://www.informit.com/library/content.asp?b=STY_Sql_Server_7&seqNum=101

A column type that helds an expression, and in queries returns the results.

That sounds excelent in my database, to save some code in my client applications. E.g adding price totals, and taxes of an order.

I can't find any info about this in later versions of SQL server. Is this not possible anymore?

not really necessary. . . create a view.

create table LineItem(itemId, price, quantity)

create view LineItemWTax as select itemid, price, quantity, price*quantity*0.045 as Tax

select * from LineItemWTax

you can also create a function:

create function CalculateTax(@.price money, @.quantity int, @.rate as decimal (3,3))
returns money
as
begin
return @.price * @.quantity * @.rate
end
go
select itemid, price, quantity, dbo.CalculateTax(price, quantity, .045) from LineItem

|||Thanks, thats probably a better way i guess :)

Are text pages deleted when a column is converted to varchar

When I change a column from text to varchar using the
design view of a table within Enterprise Manager the
varchar value (less than 8000 characters) appears in the
column but does SQL Server automatically delete the text
values from their pages?

If not are they removed by routine reindex/defrag or
should I create a new table, import from the text as
varchar and drop the old table to make sure the pages
storing the original text version of the values are
deleted?
..morriszone@.hotmail.com (Steve Morris) wrote in message news:<757b6d8f.0404080820.7956cf62@.posting.google.com>...
> When I change a column from text to varchar using the
> design view of a table within Enterprise Manager the
> varchar value (less than 8000 characters) appears in the
> column but does SQL Server automatically delete the text
> values from their pages?
> If not are they removed by routine reindex/defrag or
> should I create a new table, import from the text as
> varchar and drop the old table to make sure the pages
> storing the original text version of the values are
> deleted?
> .

just try:

select * into your_table_bak from your_table

and then try your alter table statment with no risk of dataloss!
alter table *your_table_bak* :-)

if you dont like to read 8000 charakter to find out wether some data
is loss or not try the function
"getsize"

hth

Are Range Column Charts possible?

e.g.
http://www.dundas.com/products/chart/gallery/gallery.aspx?Img=Other10"penfoldtaylor@.gmail.com" wrote:
> e.g.
> http://www.dundas.com/products/chart/gallery/gallery.aspx?Img=Other10
> I think you'll find that you would need to utilise the Dundas Chart for Reporting Services Custom Report Item. Not sure that the standard chart control that comes with Reporting Services covers this.