468,537 Members | 1,826 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,537 developers. It's quick & easy.

Do you understand this Error msg (ERROR Msg 512, Level 16, State 1, Line 33)

I have created a procedure which start by fecthing data from DB-X and put in into the temporary memory. what im trying to do now is to take data from temporary memory insert/update DB-Y.

But now I get this ERROR Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


HERE IS MY PROCEDURE


DECLARE @MineID int,
@MineName varchar(80),
@MineDescription [varchar](80) ,
@MineLocation varchar(80),
@Country varchar(80),
@Northing float,
@Easting float,
@Elevation float,
@Latitude float ,
@Longitude float,
@MineLogo varbinary(max)

DECLARE MYCURSOR CURSOR
FOR SELECT * FROM [TLC].[DBO].[MINE]
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO
@MineID,
@MineName ,
@MineDescription ,
@MineLocation ,
@Country ,
@Northing ,
@Easting ,
@Elevation ,
@Latitude,
@Longitude,
@MineLogo
WHILE @@FETCH_STATUS = 0

BEGIN

IF @MineID =(select MineID from [TEST].[dbo].[MINE1])
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] ON
BEGIN UPDATE [TEST].[dbo].[MINE1]
SET MineName = @MineName
,MineDescription = @MineDescription
,MineLocation = @MineLocation
,Country =@Country
,Northing = @Northing
,Easting = @Easting
,Elevation = @Elevation
,Latitude = @Latitude
,Longitude = @Longitude
,MineLogo =@MineLogo
where MineId = @MineID


END
IF @MineID <> (select MineID from [TEST].[dbo].[MINE1])
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] ON
INSERT INTO [TEST].[dbo].[MINE1]
(MineID
,MineName
,MineDescription
,MineLocation
,Country
,Northing
,Easting
,Elevation
,Latitude
,Longitude
,MineLogo )
VALUES (
@MineID
,@MineName
,@MineDescription
,@MineLocation
,@Country
,@Northing
,@Easting
,@Elevation
,@Latitude
,@Longitude
,@MineLogo)

FETCH NEXT FROM MYCURSOR INTO

@MineID,
@MineName ,
@MineDescription ,
@MineLocation ,
@Country ,
@Northing ,
@Easting ,
@Elevation ,
@Latitude,
@Longitude,
@MineLogo

END
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] OFF
SET IDENTITY_INSERT [TEST].[dbo].[MINE1] OFF
CLOSE MYCURSOR
DEALLOCATE MYCURSOR
Sep 13 '07 #1
3 5795
almaz
168 Expert 100+
You don't need cursors for this purpose. Please look at following sample that demonstrates how you should synchronize your data:

create database temp
create database temp1
go
create table temp.dbo.mine(id int identity, name nvarchar(max))
create table temp1.dbo.mine(id int identity, name nvarchar(max))

insert temp.dbo.mine(name) values('UpdatedValue1')
insert temp.dbo.mine(name) values('UpdatedValue2')
insert temp.dbo.mine(name) values('UpdatedValue3')
insert temp.dbo.mine(name) values('UpdatedValue4')
insert temp.dbo.mine(name) values('UpdatedValue5')
insert temp.dbo.mine(name) values('NewValue6')
insert temp1.dbo.mine(name) values('OldValue1')
insert temp1.dbo.mine(name) values('OldValue2')
insert temp1.dbo.mine(name) values('OldValue3')
insert temp1.dbo.mine(name) values('OldValue4')
insert temp1.dbo.mine(name) values('OldValue5')
select * from temp.dbo.mine
select * from temp1.dbo.mine

update temp1.dbo.mine
set
name = Source.name
from temp.dbo.mine Source
where mine.id = Source.id

set identity_insert temp1.dbo.mine on
insert temp1.dbo.mine(id, name)
select id, name
from temp.dbo.mine
where id not in (select id from temp1.dbo.mine)
set identity_insert temp1.dbo.mine off

select * from temp.dbo.mine
select * from temp1.dbo.mine

drop database temp
drop database temp1
Sep 13 '07 #2
Thank you for your response, But my problem is not resolved yet, What im actually trying to do is to take data from Another DB-TLC to DB-Test. And these two database they are not integrated and we can't intergrate them.So what will happen is: if the data exist it can update else insert a new record. I'm not creating database from scratch.

this error is trigged by these statemement

IF @BlasterID <> (select BlasterID from Blasters )
INSERT INTO [TEST].[dbo].[Blasters]

BELOW IS THE ERROR
{Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.}

It seems like SQL doesn't want that , I have also tried the IF NOT EXIST clause but still it doesn't help. Please if you have any ideas agains on how to handle this with SQL please help
Sep 13 '07 #3
almaz
168 Expert 100+
Thank you for your response, But my problem is not resolved yet, What im actually trying to do is to take data from Another DB-TLC to DB-Test
Have you looked at sample I provided? It demonstrates how to synchronize data from two different databases in more efficient way than you do.
----
Concerning your particular problem with
IF @BlasterID <> (select BlasterID from Blasters ):
You can compare a variable (@BlasterID) with resultset (select BlasterID from Blasters) only when resultset contains a single row with single column. In your case resultset contains all rows from Blasters table, not one. Correct solution is:
IF NOT EXISTS (select * from Blasters WHERE BlasterID = @BlasterID)
Sep 14 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Mahesh Hardikar | last post: by
3 posts views Thread by LineVoltageHalogen | last post: by
4 posts views Thread by David Lozzi | last post: by
2 posts views Thread by Josue.Barrios | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.