sql 2000 and sql 2005? - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

sql 2000 and sql 2005?

Author

Topic

boyfriendalex
Starting Member

15 Posts

Posted - 2008-07-21 : 23:05:00

I work to convert the scripts from sql2005 to sql2000
by the way the following scripts is possible to do in sql 2005

INSERT INTO table1
EXEC myprocedure1 'param1'

how to do in sql 2000
Anyone help?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-21 : 23:07:53

Same way.

boyfriendalex
Starting Member

15 Posts

Posted - 2008-07-22 : 00:00:08

quote:
Originally posted by rmiao

Same way.

it doesn't work in sql 2000
error message: EXECUTE cannot be used as a source when inserting into a table variable.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2008-07-22 : 00:03:41

The error message sounds like you're trying to import into a table var (i.e. DECLARE @table1 TABLE (fields...))

You can only use temp tables or normal tables to receive the results of stored procedures in this way.

boyfriendalex
Starting Member

15 Posts

Posted - 2008-07-22 : 00:16:11

quote:
Originally posted by timmy

The error message sounds like you're trying to import into a table var (i.e. DECLARE @table1 TABLE (fields...))

You can only use temp tables or normal tables to receive the results of stored procedures in this way.

Thank you very mush for your help.
Regards
Alex

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2008-07-22 : 00:38:31

No probs.

I gotta ask - what's the inspiration behind the nickname?

Cheers,

Tim

boyfriendalex
Starting Member

15 Posts

Posted - 2008-07-22 : 00:50:16

quote:
Originally posted by timmy

No probs.

I gotta ask - what's the inspiration behind the nickname?

Cheers,

Tim

I have one more problem

UPDATE table1
SET column1 = (SELECT column1 FROM table2)

It works well in sql2005
how to do in sql2000?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-22 : 00:55:01

quote:
Originally posted by boyfriendalex

I have one more problem

UPDATE table1
SET column1 = (SELECT column1 FROM table2)

It works well in sql2005
how to do in sql2000?

Are you sure it works well in SQL 2005 ?


update t1
set    column1 = t2.column1
from   table1 t1 inner join table t2
on     t1.somecol = t2.somecol

KH
[spoiler]Time is always against us[/spoiler]

boyfriendalex
Starting Member

15 Posts

Posted - 2008-07-22 : 01:24:50

quote:
Originally posted by khtan

quote:
Originally posted by boyfriendalex

I have one more problem

UPDATE table1
SET column1 = (SELECT column1 FROM table2)

It works well in sql2005
how to do in sql2000?

Are you sure it works well in SQL 2005 ?

update t1
set column1 = t2.column1
from table1 t1 inner join table t2
on t1.somecol = t2.somecol

KH
[spoiler]Time is always against us[/spoiler]

yes, I'm sure, why? Do you have any problem to do like this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-22 : 01:29:58

"yes, I'm sure,"
You only have 1 record in table2 right ? If you have more than 1 record in table2 your query will break

KH
[spoiler]Time is always against us[/spoiler]

boyfriendalex
Starting Member

15 Posts

Posted - 2008-07-22 : 02:00:16

quote:
Originally posted by khtan

"yes, I'm sure,"
You only have 1 record in table2 right ? If you have more than 1 record in table2 your query will break

KH
[spoiler]Time is always against us[/spoiler]

i had done already in sql 2005. somehow what is your problem? can i have your problem?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-22 : 02:06:58

Run this and see.

DROP TABLE table1
DROP TABLE table2
CREATE TABLE table1
(
	ID	int identity(1,1),
	col1	int
)

CREATE TABLE table2
(
	ID	int identity(1,1),
	col1	int
)

INSERT INTO table1(col1)
SELECT	10	UNION ALL
SELECT	10	

INSERT INTO table2(col1)
SELECT	100	
UNION ALL SELECT 100	-- try mask/unmask this line and run

UPDATE	table1
SET	col1	= (SELECT col1 FROM table2)

SELECT	*
FROM	table1

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-22 : 02:10:24

quote:
Originally posted by boyfriendalex

i had done already in sql 2005. somehow what is your problem? can i have your problem?

Not my problem. It's your problem

KH
[spoiler]Time is always against us[/spoiler]

boyfriendalex
Starting Member

15 Posts

Posted - 2008-07-22 : 02:16:05

quote:
Originally posted by khtan

quote:
Originally posted by boyfriendalex

i had done already in sql 2005. somehow what is your problem? can i have your problem?

Not my problem. It's your problem

KH
[spoiler]Time is always against us[/spoiler]

ok can u solve my problem more? you read the topic "how to solve it in sql2000"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-22 : 03:38:16

Use TOP 1 in the subquery.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources