Stored procedure runs diffrently in 2000 and 2005

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

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Stored procedure runs diffrently in 2000 and 2005

Author

Topic

alibeirami
Starting Member

3 Posts

Posted - 2007-04-19 : 17:43:04

Hi there everybody,

I am writing a sales managing software using C# in .NET 2005.
The program was used to work properly with sql server 2000.
I decided to write a "FILE Version" of sofware using sql server express 2005 , and detached db from 2000 , attaching it to 2005 using AttachDBFileName clause in connection string.

But the problem is when program executes something like this:
EXEC [AddOrder] ...
two rows inserted instead of one.and when I use server explorer of VS2005 to launch SP, it works fine.
I should mention again that same code works correctly when I change connection string and force it to use sqlserver 2000.

Any ideas?

Kind Regards: Ali

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-19 : 18:31:45

did you change the compatibility mode to 90, re-index all the tables, update the stats when you moved the db to 2005? or you just switched the file back and forth between 2000 and 2005 and expecting to see big difference?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-04-20 : 02:32:21

Can you post the code for your AddOrder Sproc for us to comment on pls?

Kristen

alibeirami
Starting Member

3 Posts

Posted - 2007-04-20 : 14:55:44

Hi,
@dinakar:

I detached database from sql server 2000(Updated statistics prior detaching),And don't knw about other versions of 2005,but it seems that express changes the compatibality mode to 90,and there is no way back(or forth).Is it possible to reindex tables without Enterprise manager?(as you know express doesn't have) and you think that it is index problem?
thanx buddy,
@Kristen
It is just a simple Insert query and does nothing else.

Kristen
Test

22859 Posts

Posted - 2007-04-21 : 01:28:03

"Updated statistics prior detaching"

You need to do that after mounting on SQL 2005 (best to reindex first)

"Is it possible to reindex tables without Enterprise manager"

Yes, you can do it with a SQL command - see BoL for details

"It is just a simple Insert query and does nothing else"

I'll still like to see it because you say it is inserting two rows instead of one and I can't help if I can't see what the code is!

Kristen

alibeirami
Starting Member

3 Posts

Posted - 2007-04-21 : 13:35:11

quote:
Originally posted by Kristen

"Updated statistics prior detaching"

You need to do that after mounting on SQL 2005 (best to reindex first)

"Is it possible to reindex tables without Enterprise manager"

Yes, you can do it with a SQL command - see BoL for details

"It is just a simple Insert query and does nothing else"

I'll still like to see it because you say it is inserting two rows instead of one and I can't help if I can't see what the code is!

Kristen

Hi Kirsten,

CREATE PROC [dbo].[AddOrder]

@POSID int,
@PartID int,
@SubPartID int,
@OrderDate int,
@OrderTime int,
@Status smallint,
@CustomerName nvarchar(50),
@Tax float,
@Service float,
@Discount float,
@NetPrice float,
@TotalPrice float,
@PayType smallint,
@CheckoutDate int,
@CheckoutTime int,
@CashierID int,
@isDeleted bit

AS

INSERT INTO CurrentOrders(
POSID ,
PartID ,
SubPartID ,
OrderDate ,
OrderTime ,
Status ,
CustomerName ,
Tax ,
Service ,
Discount ,
NetPrice ,
TotalPrice ,
PayType ,
CheckoutDate ,
CheckoutTime ,
CashierID ,
isDeleted)
VALUES(
@POSID ,
@PartID ,
@SubPartID ,
@OrderDate ,
@OrderTime ,
@Status ,
@CustomerName ,
@Tax ,
@Service ,
@Discount ,
@NetPrice ,
@TotalPrice ,
@PayType ,
@CheckoutDate ,
@CheckoutTime ,
@CashierID ,
@isDeleted)
GO

but Problem Solved, it was ConnectionString issue.thanx buddy.

Kristen
Test

22859 Posts

Posted - 2007-04-22 : 06:26:11

"Problem Solved, it was ConnectionString issue"

Glad to hear that, I'm amazed that could muck up an Insert. If you had a moment to post some details I'd appreciate it.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources