Error in Updating records using Openquery

Hi,

I am trying to update a DB2 table from Sql Server using Openquery but it gives me an

error, I tried selecting rows from the DB2 table using Openquery and it works fine.

Following is the update query:

UPDATE OPENQUERY(DB2_DB2T, 'SELECT STATUS FROM $ZUDBA01.TPT20_VOLS

WHERE AUD_NBR=10000004 AND POLL_NUM_ACTUAL=''999456789'' AND

REC_TYP=''SYM''') SET STATUS='RETURNED'

This query when executed gives following error:

Server: Msg 7399, Level 16, State 1, Line 35
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Insufficient base table information for updating or

refreshing.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange:: SetData returned

0x80004005: ].

Kindly help me out.

Thanks and Regards,

Pranjal

I am not sure how much this helps...but check it

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=113747&SiteID=1

sql

Error in the Stored Procedure

I am trying to swap two rows in a table .. I am stuck with this error since a long time.. can anyone guess where the problem is ? create procedure was working fine in query analyzer but when used it in the stored procedure. I am getting these .. can anyone help me out please ... Your help will be greatly appreciated.. UpdateRowsReorderUp is my storedprocedure ... and i am using MS Sql 2000 .. am I doing something really wrong which i'm not supposed to ???

Thanks friends..

Procedure 'UpdateRowsReorderUp' expects parameter '@.nextlowestsortID', which was not supplied.

CREATE PROCEDURE [dbo].[UpdateRowsReorderUp]

(

@.intsortID int,

@.nextlowestsortID int,

@.MemberID int

)

AS

Select @.nextlowestsortID=(Select Top 1 SortID from SelectedCredits where SortID<@.intsortID order by SortID DESC)

UPDATE SelectedCredits SET SortID= CASE

WHEN SortID = @.nextlowestsortID then @.intsortID

WHEN SortID = @.intsortID then @.nextlowestsortID ELSE SortID End

WHERE MemberID = @.MemberID

SELECT * FROM SelectedCredits WHERE MemberID= @.MemberID ORDER BY SortID

GO

**************

// this is my script on the page

void moveup(Object s, DataListCommandEventArgs e) {

objcmd= new SqlCommand("UpdateRowsReorderUp",objConn);

objcmd.CommandType = CommandType.StoredProcedure;

objcmd.Parameters.Add("@.intsortID",intsortID);

objcmd.Parameters.Add("@.MemberID",Session["MemberID"]);

objRdr= objcmd.ExecuteReader();

dlSelCredits.DataSource = objRdr;

dlSelCredits.DataBind();

objRdr.Close();

objConn.Close();

BindData();

}

You are missing the @.nextlowestsortid parameter in your code. Add it between the other two.
|||

Thanks for your reply . I'm storing a value in the @.nextlowestsortid using a SELECT statement.I am not assigning any value outside so that i can pass it into the parameter. for example @.intsortid i'm assiging intsortid through the code but what can i assign to this? Sorry to ask like this i am a newbie in this field..

Can i write the SELECT statement in different way so that there is no need add any parameter through the code ??

|||If the @.nextlowestsortid parameter is an internal parameter, remove itfrom the parameter list and declare it after the AS, like this:
DECLARE @.nextlowestsortid int
Then you stored procedure will only have two parameters that you supply, and the error will go away.
Sam
|||

Thank you very much Sam .. I am really grateful to you ..

You solved my problem .. that was the perfect solution ..

cheers mate

error in Enterprise Manager while returning all or few rows.

Hi, Everyone
I use SQL Server Enterprise Manager to access DB server.
I can connect and see the tables fine. But when i tried
to open table returen all rows or open design table, I
got an error saying:
An unexpected error happend during this operation.
[MS Desgin Tools] -- Not enough storage is available to
complete this operation.
Any idea how to fix this? This is SQL2K SP4 on WIN2K SP4.
Thanks
Adi ..adi wrote:
> Hi, Everyone
> I use SQL Server Enterprise Manager to access DB server.
> I can connect and see the tables fine. But when i tried
> to open table returen all rows or open design table, I
> got an error saying:
> An unexpected error happend during this operation.
> [MS Desgin Tools] -- Not enough storage is available to
> complete this operation.
> Any idea how to fix this? This is SQL2K SP4 on WIN2K SP4.
> Thanks
> Adi ..
>
Don't use Enterprise Manager to view or modify data - use Query Analyzer.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I appreciate your advice. But what I am looking for is a solution for the
problem I mentioned.
"Tracy McKibben" wrote:
> adi wrote:
> > Hi, Everyone
> >
> > I use SQL Server Enterprise Manager to access DB server.
> > I can connect and see the tables fine. But when i tried
> > to open table returen all rows or open design table, I
> > got an error saying:
> >
> > An unexpected error happend during this operation.
> > [MS Desgin Tools] -- Not enough storage is available to
> > complete this operation.
> >
> > Any idea how to fix this? This is SQL2K SP4 on WIN2K SP4.
> >
> > Thanks
> > Adi ..
> >
> Don't use Enterprise Manager to view or modify data - use Query Analyzer.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||adi wrote:
> I appreciate your advice. But what I am looking for is a solution for the
> problem I mentioned.
>
Start reading:
http://www.google.com/search?q=%22enterprise+manager%22+%22Not+enough+storage+is+available+to%22
You could avoid the whole mess by using the proper tools, i.e. using
Query Analyzer instead of Enterprise Manager.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Adi,
The nature of the problem mentioned is using the wrong tool for the job.
Enterprise Manager is NOT the 'right' tool to use for table alteration, data
manipulation, or, as you noticed, even viewing large amounts of data.
Tracy's advise is good advise. Continuing to use EM sets you up for
numerous, as of yet, inexperienced, problems -of which this is just the tip
of the iceberg.
Your question is like asking how to hammer a nail with a screwdriver. And
when someone mentions that a hammer would be better, your response is like
saying "I don't care, I am going to hammer the nail with the screwdriver."
You won't find many skilled SQL Server folks actively encouraging or
abetting the use of Enterprise Mangler.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"adi" <adi@.discussions.microsoft.com> wrote in message
news:4BFDAB54-4ED5-48C7-A19A-2CEAFBA31E02@.microsoft.com...
>I appreciate your advice. But what I am looking for is a solution for the
> problem I mentioned.
> "Tracy McKibben" wrote:
>> adi wrote:
>> > Hi, Everyone
>> >
>> > I use SQL Server Enterprise Manager to access DB server.
>> > I can connect and see the tables fine. But when i tried
>> > to open table returen all rows or open design table, I
>> > got an error saying:
>> >
>> > An unexpected error happend during this operation.
>> > [MS Desgin Tools] -- Not enough storage is available to
>> > complete this operation.
>> >
>> > Any idea how to fix this? This is SQL2K SP4 on WIN2K SP4.
>> >
>> > Thanks
>> > Adi ..
>> >
>> Don't use Enterprise Manager to view or modify data - use Query Analyzer.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||Arnie,
Now, I'm not against your advice or anyones for that matter. In fact that's
what we are looking for through these forums. I agree with what you said as
I've been managing SQL Servers since v6.5 days. But if I come accross this
problem and google or MS KB not being able to shortlist the problem
resolution then my search is always for the possible answer to the question.
Thanks again for your wise words. have a good day.
Adi ..
"Arnie Rowland" wrote:
> Adi,
> The nature of the problem mentioned is using the wrong tool for the job.
> Enterprise Manager is NOT the 'right' tool to use for table alteration, data
> manipulation, or, as you noticed, even viewing large amounts of data.
> Tracy's advise is good advise. Continuing to use EM sets you up for
> numerous, as of yet, inexperienced, problems -of which this is just the tip
> of the iceberg.
> Your question is like asking how to hammer a nail with a screwdriver. And
> when someone mentions that a hammer would be better, your response is like
> saying "I don't care, I am going to hammer the nail with the screwdriver."
> You won't find many skilled SQL Server folks actively encouraging or
> abetting the use of Enterprise Mangler.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "adi" <adi@.discussions.microsoft.com> wrote in message
> news:4BFDAB54-4ED5-48C7-A19A-2CEAFBA31E02@.microsoft.com...
> >I appreciate your advice. But what I am looking for is a solution for the
> > problem I mentioned.
> >
> > "Tracy McKibben" wrote:
> >
> >> adi wrote:
> >> > Hi, Everyone
> >> >
> >> > I use SQL Server Enterprise Manager to access DB server.
> >> > I can connect and see the tables fine. But when i tried
> >> > to open table returen all rows or open design table, I
> >> > got an error saying:
> >> >
> >> > An unexpected error happend during this operation.
> >> > [MS Desgin Tools] -- Not enough storage is available to
> >> > complete this operation.
> >> >
> >> > Any idea how to fix this? This is SQL2K SP4 on WIN2K SP4.
> >> >
> >> > Thanks
> >> > Adi ..
> >> >
> >>
> >> Don't use Enterprise Manager to view or modify data - use Query Analyzer.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com
> >>
>
>

error in Enterprise Manager while returning all or few rows.

Hi, Everyone
I use SQL Server Enterprise Manager to access DB server.
I can connect and see the tables fine. But when i tried
to open table returen all rows or open design table, I
got an error saying:
An unexpected error happend during this operation.
[MS Desgin Tools] -- Not enough storage is available to
complete this operation.
Any idea how to fix this? This is SQL2K SP4 on WIN2K SP4.
Thanks
Adi ..adi wrote:
> Hi, Everyone
> I use SQL Server Enterprise Manager to access DB server.
> I can connect and see the tables fine. But when i tried
> to open table returen all rows or open design table, I
> got an error saying:
> An unexpected error happend during this operation.
> [MS Desgin Tools] -- Not enough storage is available to
> complete this operation.
> Any idea how to fix this? This is SQL2K SP4 on WIN2K SP4.
> Thanks
> Adi ..
>
Don't use Enterprise Manager to view or modify data - use Query Analyzer.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I appreciate your advice. But what I am looking for is a solution for the
problem I mentioned.
"Tracy McKibben" wrote:

> adi wrote:
> Don't use Enterprise Manager to view or modify data - use Query Analyzer.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||adi wrote:
> I appreciate your advice. But what I am looking for is a solution for the
> problem I mentioned.
>
Start reading:
http://www.google.com/search? q=%22...
vailable+to%22
You could avoid the whole mess by using the proper tools, i.e. using
Query Analyzer instead of Enterprise Manager.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Adi,
The nature of the problem mentioned is using the wrong tool for the job.
Enterprise Manager is NOT the 'right' tool to use for table alteration, data
manipulation, or, as you noticed, even viewing large amounts of data.
Tracy's advise is good advise. Continuing to use EM sets you up for
numerous, as of yet, inexperienced, problems -of which this is just the tip
of the iceberg.
Your question is like asking how to hammer a nail with a screwdriver. And
when someone mentions that a hammer would be better, your response is like
saying "I don't care, I am going to hammer the nail with the screwdriver."
You won't find many skilled SQL Server folks actively encouraging or
abetting the use of Enterprise Mangler.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"adi" <adi@.discussions.microsoft.com> wrote in message
news:4BFDAB54-4ED5-48C7-A19A-2CEAFBA31E02@.microsoft.com...[vbcol=seagreen]
>I appreciate your advice. But what I am looking for is a solution for the
> problem I mentioned.
> "Tracy McKibben" wrote:
>|||Arnie,
Now, I'm not against your advice or anyones for that matter. In fact that's
what we are looking for through these forums. I agree with what you said as
I've been managing SQL Servers since v6.5 days. But if I come accross this
problem and google or MS KB not being able to shortlist the problem
resolution then my search is always for the possible answer to the question.
Thanks again for your wise words. have a good day.
Adi ..
"Arnie Rowland" wrote:

> Adi,
> The nature of the problem mentioned is using the wrong tool for the job.
> Enterprise Manager is NOT the 'right' tool to use for table alteration, da
ta
> manipulation, or, as you noticed, even viewing large amounts of data.
> Tracy's advise is good advise. Continuing to use EM sets you up for
> numerous, as of yet, inexperienced, problems -of which this is just the ti
p
> of the iceberg.
> Your question is like asking how to hammer a nail with a screwdriver. And
> when someone mentions that a hammer would be better, your response is like
> saying "I don't care, I am going to hammer the nail with the screwdriver."
> You won't find many skilled SQL Server folks actively encouraging or
> abetting the use of Enterprise Mangler.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "adi" <adi@.discussions.microsoft.com> wrote in message
> news:4BFDAB54-4ED5-48C7-A19A-2CEAFBA31E02@.microsoft.com...
>
>

Error in DELETE trigger

Hi,
I get an error when I try to delete multiple rows from a table.
Server: Msg 512, Level 16, State 1, Procedure tr_Documents_Delete, Line 16
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
I guess it is because of my trigger. The trigger looks like this:
ALTER TRIGGER tr_Documents_Delete
ON DocumentTrans
AFTER DELETE
AS
if @.@.rowcount = 0
return
DECLARE @.count INT
SET @.count = (SELECT COUNT(*) FROM DocumentTrans INNER JOIN Deleted
ON DocumentTrans.TableReference = Deleted.TableReference
AND DocumentTrans.RowNumberReference = Deleted.RowNumberReference)
IF @.count > 0
return
DECLARE @.TableName VARCHAR(100)
SET @.TableName = (SELECT TableReference FROM Deleted)
DECLARE @.SQLString VARCHAR(1000)
SET @.SQLString = 'UPDATE ' + @.TableName +
' SET DocumentsExists = NULL WHERE ' +
@.TableName + '.RowNumber = ' + CAST((SELECT RowNumberReference FROM
Deleted) AS VARCHAR(100))
EXEC(@.SQLString)
I have tried with a loop. But it also gives me an error
WHILE (SELECT TableReference FROM Deleted) IS NOT NULL
BEGIN
DECLARE @.TableName VARCHAR(100)
SET @.TableName = (Select TableReference FROM Deleted)
DECLARE @.SQLString VARCHAR(1000)
SET @.SQLString = 'UPDATE ' + @.TableName +
' SET DocumentsExists = NULL WHERE ' +
@.TableName + '.RowNumber = ' + CAST((SELECT RowNumberReference FROM
Deleted) AS VARCHAR(100))
EXEC(@.SQLString)
END
Any ideas?
Best regards
HenrikHi
The offending line could be:
SET @.TableName = (SELECT TableReference FROM Deleted)
A trigger fires once per batch, and not once per row deleted. The assumption
that only 1 row is being deleted fails on that line. The select is returning
more than one row, but the variable can not hold more than one row's value.
Either look at the way you process the row, or disallow more than one row to
be deleted at the same time with another check earlier on the in trigger.
Regards
Mike
"Henrik Skak Pedersen" wrote:

> Hi,
> I get an error when I try to delete multiple rows from a table.
> Server: Msg 512, Level 16, State 1, Procedure tr_Documents_Delete, Line 16
> Subquery returned more than 1 value. This is not permitted when the subque
ry
> follows =, !=, <, <= , >, >= or when the subquery is used as an expression
.
> The statement has been terminated.
> I guess it is because of my trigger. The trigger looks like this:
> ALTER TRIGGER tr_Documents_Delete
> ON DocumentTrans
> AFTER DELETE
> AS
> if @.@.rowcount = 0
> return
> DECLARE @.count INT
> SET @.count = (SELECT COUNT(*) FROM DocumentTrans INNER JOIN Deleted
> ON DocumentTrans.TableReference = Deleted.TableReference
> AND DocumentTrans.RowNumberReference = Deleted.RowNumberReference)
> IF @.count > 0
> return
> DECLARE @.TableName VARCHAR(100)
> SET @.TableName = (SELECT TableReference FROM Deleted)
> DECLARE @.SQLString VARCHAR(1000)
> SET @.SQLString = 'UPDATE ' + @.TableName +
> ' SET DocumentsExists = NULL WHERE ' +
> @.TableName + '.RowNumber = ' + CAST((SELECT RowNumberReference FROM
> Deleted) AS VARCHAR(100))
> EXEC(@.SQLString)
> I have tried with a loop. But it also gives me an error
> WHILE (SELECT TableReference FROM Deleted) IS NOT NULL
> BEGIN
> DECLARE @.TableName VARCHAR(100)
> SET @.TableName = (Select TableReference FROM Deleted)
> DECLARE @.SQLString VARCHAR(1000)
> SET @.SQLString = 'UPDATE ' + @.TableName +
> ' SET DocumentsExists = NULL WHERE ' +
> @.TableName + '.RowNumber = ' + CAST((SELECT RowNumberReference FROM
> Deleted) AS VARCHAR(100))
> EXEC(@.SQLString)
> END
> Any ideas?
> Best regards
> Henrik
>
>

Error Handling in a stored procedure

I have an application where I am bcping a file into a holder table and then looping through each of the rows in that table to add it into the main table for the app. If the data is improperly formatted (ie someone accidently enters 39.Y6 instead of 39.66), we still want to keep it in the system, so we can update the bad fields manually and then import it into the system and keep going through the loop so that if there are 50 rows to import and only one of them is bad, the other 49 will still get imported fine.

I am putting each field from the holder table into a variable and passing them into an existing stored procedure that updates the main table. I had originally checked each one of those variables that had to be numeric or a date to make sure that it was correct before passing them into the procedure but there are about 30 fields so this made the application run unacceptably slow. I then just checked the @.@.Error value after passing them into the stored procedure (tried after the sp and after the INSERT statement inside the sp) to get that this row didn't insert correctly and move onto the next one. The problem is that the "Error converting data type varchar to numeric" doesn't seem to be handled by the error handling and just bombs the whole thing, so none of the subsequent rows or processing is done.

Is there any way to handle this error and continue the processing without the whole stored procedure crashing? The data entry is being outsourced to India (grrr...), so I don't have any control over checking the data when they enter it and have to do it from within my application on the database side.What you can probably do is in your insert stored procedure, set up an output parameter to send any error code back to the calling stored procedure. The error in your insert probably won't bubble up so do something like this

Create Procedure InsertIt
(
@.fields ...,
@.errorcode int = null output
) as
insert into...
set @.errorcode = @.@.error
return

the error will be returned back as an output parameter rather than you trying to use @.@.error.

I've done similar things in the past and what I did was run a query that searched for the possible error conditions and flagged those records as needing attention. Then just do an insert ... select query, selecting only unflagged records, into your main table. As long as you make sure data types are good to go, you shouldn't have a failure. Probably faster than using a cursor and checking each field with in each iteration. Of course, if you the insert is more complex than just a plain import, the cursor may be necessary. Just a thought.