Showing posts with label error. Show all posts
Showing posts with label error. Show all posts

Friday, March 30, 2012

How can I resolve this database error, in VS 2005?

Every time I try to add a new row to my table, i get this error which i don't now what it means and how i can correct it, could you please advice. i am using VS 2005 and VB Language

**************The error message

"No row was updated.

The data in row 2 was not committed.

Error source:mscorlib.

Error Message: Index was out of range. Must be non-negative and less than the size of the collection.

Parameter name: index"

What code are you using to add the row?

This is the basic VB syntax

Dim myNewRow as DataRow

myNewRow=MyDataTable.NewRow()

|||

Yep. What kind of table? A table in a sql server database, or a datatable, which is a .net class?

|||

i am using sql server database

Wednesday, March 28, 2012

How can I read dump

My SQL Server 2000 genereated dump file with a error in spid 4. How can I
read the dump file to understand what is hapening ?
Thanks,
Marcos VinXcius Feitosa
Suport Analyst - Hepta Tecnologia
Hello Marcos
Typically memory dump files are analyzed by Microsoft Product Support
engineers. Please work with PSS engineer to work on determining the root
cause.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Monday, March 26, 2012

How can I Query Analyze DB Greater than 128MG

I have a 300MG DB and Query Analyzer gives me the "... DB larger than configured..." error when I try to connect to it...

What is the work around?

Thanks in advance

JEK

If you have sql server 2005 you can use the sql server management studio to connect to your sql ce (.sdf) database.
|||In SQL Server Management Studio, in the Connect To Server dialog, click "Options >>", and you will be able to increase the max db size from the default of 128 MB.|||I need to use Query Analyzer 3.0 on the Handheld|||There is no workaround this "bug". Await the next version or move the SDF file to your desktop and use one of the tools mentioned above.

How can I Query Analyze DB Greater than 128MG

I have a 300MG DB and Query Analyzer gives me the "... DB larger than configured..." error when I try to connect to it...

What is the work around?

Thanks in advance

JEK

If you have sql server 2005 you can use the sql server management studio to connect to your sql ce (.sdf) database.
|||In SQL Server Management Studio, in the Connect To Server dialog, click "Options >>", and you will be able to increase the max db size from the default of 128 MB.|||I need to use Query Analyzer 3.0 on the Handheld|||There is no workaround this "bug". Await the next version or move the SDF file to your desktop and use one of the tools mentioned above.

Friday, March 23, 2012

How can i pass GUID as sqlreport input paramater?

Hi,
How can I pass GUID as sqlreport Input parameter? I get the following error -

"Failed to convert parameter value from a String to a Guid. (System.Data)"

I also tried passing guid within the curly braces but of now use. Please help!
Ashu

Try creating a ReportParameter object and then storing the GUID, as a string, inside this parameter.

Example:

Microsoft.Reporting.Winforms.ReportParameter tempParameter = new Microsoft.Reporting.Winforms.ReportParameter();

tempParameter = new Microsoft.Reporting.Winforms.ReportParameter("ID", GUID.ToString());

Finally, pass this ReportParameter object to the report.

Example:

reportViewer.LocalReport.SetParameters(tempParameter);

Wednesday, March 7, 2012

How can I implement error handling within a SQL script that uses b

I am trying to incorporate error handling into a SQL script that creates
several database objects.
Those objects are referenced later in the script by other blocks of code.
It is my understanding that I need to use a new batch to reference an object
that was just created. I can do this by inserting a GO statement after the
object creation statement.
But how do I implement error handling within a SQL script that uses batches?
IOW I don't want a subsequent batch to execute if a previous batch failed.
If I didn't have separate batches in my script I could use a RETURN or GOTO
statement to handle errors. But with batches I have the following problem:
--use RETURN
PRINT '1'
GO
PRINT '2'
RETURN
GO
PRINT '3'
--OUTPUT
1
2
3
--use GOTO
PRINT '1'
GO
PRINT '2'
GOTO error
GO
PRINT '3'
ERROR:
PRINT 'ERROR'
--OUTPUT
1
Server: Msg 133, Level 15, State 1, Line 2
A GOTO statement references the label 'error' but the label has not been
declared.
3
ERROR
Is the only solution to break up the script to run as indivifual batchs and
then evaluate each one after execution?Use the
IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE
id=object_id(@.v_objectname))
BEGIN
-- start processing
END
ELSE
BEGIN
PRINT @.v_objectname + ' was not created'
END|||Do you need a new batch to reference an object created in the current bacth?
try this
create table t1 (A int)
insert into t1 values(1)
select * from t1
drop table t1
Or did I misunderstand u?|||On Wed, 31 May 2006 10:36:02 -0700, Dave wrote:
(snip)
>But how do I implement error handling within a SQL script that uses batches
?
> IOW I don't want a subsequent batch to execute if a previous batch failed.[/color
]
Hi Dave,
There's no good answer for this. Since the cient sends just one batch to
the server, waits for it to execute, then sends the other one, there is
no simple way to have an error in batch #1 abort batch #2.
There are kludges, though. The easiest (but nastiest) is to use a
RAISERROR with a severity of at least 20 - those are considered fatal,
and prompt SQL Server to terminate the connection after sending the
error message.
Somewhat more work (but still the version I'd prefer) is to use a table
to carry over status information. This can be a temp table. For
instance:
-- Preparational steps
CREATE TABLE #Status (Status varchar(30) NOT NULL)
INSERT INTO #Status (Status) VALUES ('Okay')
go
-- Start of batch #1
(Do something)
(Do some more)
IF (something went wrong)
BEGIN
UPDATE #Status
SET Status = 'Error in batch #1'
END
go
-- Start of batch #2
IF (SELECT Status FROM #Status) = 'Okay'
BEGIN
(Do something)
(Do some more)
IF (something went wrong)
BEGIN
UPDATE #Status
SET Status = 'Error in batch #2'
END
END
go
-- Repeat for batches #3, #4, ...
-- After last batch:
IF (SELECT Status FROM #Status) <> 'Okay'
BEGIN
PRINT 'Error'
SELECT Status FROM #Status
END
DROP TABLE #Status
Hugo Kornelis, SQL Server MVP

How can I implement error handling within a SQL script that us

Hi Hugo,
I was thinking in similar lines as the solution you gave (not the one
with severity 20.. I would have never come up with such a solution :)
But I think there is a problem in the other one. We are talking about
different batches. and these batches will be creating database objects.
Can you give an example on how will you implement the error handling with
temp tables for this scenario?
1. Create a view on some existing table first (select *)
2. If the view creation is successful,then create another view on this view.On Thu, 1 Jun 2006 22:35:01 -0700, Omnibuzz wrote:
(snip)
>Can you give an example on how will you implement the error handling with
>temp tables for this scenario?
>1. Create a view on some existing table first (select *)
>2. If the view creation is successful,then create another view on this view.[/color
]
Hi Omnibuzz,
There's one thing I hadn't thought about - CREATE VIEW must be the only
in a batch, so you'll have to use dynamic SQL to make it conditional.
-- Preparational steps
CREATE TABLE #Status (Status varchar(30) NOT NULL)
INSERT INTO #Status (Status) VALUES ('Okay')
go
-- Start of batch #1
EXEC ('CREATE VIEW v1
AS
SELECT 1 AS a')
IF @.@.error > 0
BEGIN
UPDATE #Status
SET Status = 'Error in batch #1'
END
go
-- Start of batch #2
IF (SELECT Status FROM #Status) = 'Okay'
BEGIN
EXEC ('CREATE VIEW v2
AS
SELECT a
FROM v1')
IF @.@.error > 0
BEGIN
UPDATE #Status
SET Status = 'Error in batch #2'
END
END
go
-- Repeat for batches #3, #4, ...
-- After last batch:
IF (SELECT Status FROM #Status) <> 'Okay'
BEGIN
PRINT 'Error'
SELECT Status FROM #Status
END
DROP TABLE #Status
go
Hugo Kornelis, SQL Server MVP

How can I handle an error

Is it possible to catch and error and then keep the process going in a stored procedure?

So if an update encounters a primary key violation on a row, is it possible to skip that row and keep the process going?

You can use a try block in a stored procedure

http://msdn2.microsoft.com/en-us/library/ms175976.aspx

|||

Ken,

I have looked at the try block, but I'm not sure how to set it up. Here is what I'm doing, can you giving me any help on adding the try block so it will keep processing after it catches a primary key violation on a row?

 @.KeepClientIDint, @.RemoveClientIDintASBEGIN UPDATE tbOrgCodesSET tblOrgCodes.Client_ID = @.KeepClientIDWHERE tblOrgCodes.Client_ID = @.RemoveClientIDEND
|||

I added the try/catch and it stopped the error, but it did not continue the process. What do I need to do to get it to continue?

@.KeepClientIDint, @.RemoveClientIDintASBEGINBEGIN TRYUPDATE tbOrgCodesSET tblOrgCodes.Client_ID = @.KeepClientIDWHERE tblOrgCodes.Client_ID = @.RemoveClientIDEND TRYBEGIN CATCH--?END CATCHEND
|||

Hi Jack,

We can catch the exception that is thrown within this single update. If the update affects serveral rows, we cannot only ignore one row and proceed with other rows.

However, if you're calling the DataAdapter.Update method to update rows. When one row throws exception, and you need to go on with other rows, you can set DataAdapter.ContinueUpdateOnError to true, so that it will continue with the next row when error generates.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Monday, February 27, 2012

how can I get the row number(error row) while constraint failure at the OLE DB destination?

my project is insert data to OLE DB destination from csv file.

my question is

how can I get the row number(error row) while constraint failure at the OLE DB destination?

thanks.

You can't. There is no concept of row numbers in the SSIS pipeline (for good reasons).

What you CAN do is divert the erroring rows elsewhere for examination later.

-Jamie

|||

thank you.

it saves me a lot of time.

|||What good reasons ?

how can I get the row number(error row) while constraint failure at the OLE DB destination?

my project is insert data to OLE DB destination from csv file.

my question is

how can I get the row number(error row) while constraint failure at the OLE DB destination?

thanks.

You can't. There is no concept of row numbers in the SSIS pipeline (for good reasons).

What you CAN do is divert the erroring rows elsewhere for examination later.

-Jamie

|||

thank you.

it saves me a lot of time.

|||What good reasons ?

How can I get the message return?

For some reasons I want to get the massage of return when I carry out a query.
I only know use @.@.error:
select * from teste; --teste does't exist
select description from master.dbo.sysmessages where error = @.@.error
Invalid object name '%.*ls'.
But I hope to get the massage:
Invalid object name 'teste'.
thanks!Hi
What is you SQL Server's version?
if you are using SQL Server 2005 try
BEGIN TRY
INSERT INTO Employees(empid, empname, mgrid)
VALUES(1, 'Emp1', NULL)
PRINT 'After INSERT.'
END TRY
BEGIN CATCH
PRINT 'INSERT failed.'
/* perform corrective activity */
END CATCH
In SQL Server 2000
IF OBJECT_ID('teste ') IS NULL
PRINT 'Invalid object name teste'
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:6CAC52A9-FC79-4C22-9053-CC7F3424E991@.microsoft.com...
> For some reasons I want to get the massage of return when I carry out a
> query.
> I only know use @.@.error:
> select * from teste; --teste does't exist
> select description from master.dbo.sysmessages where error = @.@.error
> Invalid object name '%.*ls'.
> But I hope to get the massage:
> Invalid object name 'teste'.
> thanks!|||Therefore you have to use a substitute parameter within the message.
Best thing would be NOT to use SELECT thing, better use the RAISERROR
statement:
USE MASTER
EXEC sp_addmessage
@.msgnum = 60003,
@.severity = 16,
@.msgtext = N'Something happened on the way to %s'
RAISERROR(60001,1,1,'heaven')
GO
HTH, Jens Suessmeyer.|||Sorry wrong RAISERROR according to the exmaples above:
RAISERROR(60003,1,1,'heaven')|||In 2000, you can't. (Well you can use DBCC OUPUTBUFFER, which is a mess, suggested here a coupe of
days ago. Check the archives.)
In 2005, you can use the new ERROR_MESSAGE() function
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:6CAC52A9-FC79-4C22-9053-CC7F3424E991@.microsoft.com...
> For some reasons I want to get the massage of return when I carry out a query.
> I only know use @.@.error:
> select * from teste; --teste does't exist
> select description from master.dbo.sysmessages where error = @.@.error
> Invalid object name '%.*ls'.
> But I hope to get the massage:
> Invalid object name 'teste'.
> thanks!|||Thanks a lot!!
"Tibor Karaszi" wrote:
> In 2000, you can't. (Well you can use DBCC OUPUTBUFFER, which is a mess, suggested here a coupe of
> days ago. Check the archives.)
> In 2005, you can use the new ERROR_MESSAGE() function
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "luyan" <luyan@.discussions.microsoft.com> wrote in message
> news:6CAC52A9-FC79-4C22-9053-CC7F3424E991@.microsoft.com...
> > For some reasons I want to get the massage of return when I carry out a query.
> > I only know use @.@.error:
> > select * from teste; --teste does't exist
> > select description from master.dbo.sysmessages where error = @.@.error
> > Invalid object name '%.*ls'.
> > But I hope to get the massage:
> > Invalid object name 'teste'.
> >
> > thanks!
>

Friday, February 24, 2012

how can I get the error message?

I want to insert csv file into table.

In Control Flow Tab I insert a Data Flow Task and a Script Task

The Script Task is under the Data Flow Task as the failure handle to generate Log.

but I don't konw how to get the Data Flow Task's error message in Script Task?

thank you for you answer!!

Try moving the script task into an OnError eventhandler. The error message is available in there.

Let me know if this creates a problem.

-Jamie

|||

thank you for the answer.

I moved the script task to event handler tag. but it didn't run though my deta flow task generated a primary key error.

also I would like to know wether it is the err.description from that property I can konw the error message?

How can i get the details of the error

How can i get the details of the error in sql server 2000 such where it
occurs in which line. I need something like i get in sql query analyzer.

--
Message posted via http://www.sqlmonster.comE B via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> How can i get the details of the error in sql server 2000 such where it
> occurs in which line. I need something like i get in sql query analyzer.

In SQL 2000, the only item you can find out about an error is the
number of the error.

In SQL 2005, currently in beta, there is a vastly improved support in
this area.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, however what can you advice me??

--
Message posted via http://www.sqlmonster.com|||E B via SQLMonster.com (forum@.SQLMonster.com) writes:
> Thanks, however what can you advice me??

Since I don't know what you are after, it's quite difficult to give any
advice.

However, I have two articles on my web site on error handling in SQL Server.
They may give you some guidande.
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||ok thanks

--
Message posted via http://www.sqlmonster.com

Sunday, February 19, 2012

How can i get a return code of 1 for an osql command which has a lower severity..

I am running the following OSQL command and capturing the return code
for the error .Whenver i have an error like server not exists or uable
to login I get a return code of 1 for the %ERRORLEVEL%.However
whenever I have an errorof a wrong dbcompatibility error the retun
code id 1 even though sql returns an iformation message from OSQL that
the right copatibilty levels are 60,70 and 80.How can i get OSQL to
return the right return code whenver a error of this type occurrs from
batch mode sql.The OSQL i am running from the batch is

osql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%
-Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txt
ECHO %errorlevel% >> %logbatch%
IF %ERRORLEVEL% NEQ 0 Goto SQLError

sqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80

Thanks in anticipation.

Ajay[posted and mailed, please reply in news]

Ajay Garg (ajayz90@.hotmail.com) writes:
> I am running the following OSQL command and capturing the return code
> for the error .Whenver i have an error like server not exists or uable
> to login I get a return code of 1 for the %ERRORLEVEL%.However
> whenever I have an errorof a wrong dbcompatibility error the retun
> code id 1 even though sql returns an iformation message from OSQL that
> the right copatibilty levels are 60,70 and 80.How can i get OSQL to
> return the right return code whenver a error of this type occurrs from
> batch mode sql.The OSQL i am running from the batch is
> osql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%
> -Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txt
> ECHO %errorlevel% >> %logbatch%
> IF %ERRORLEVEL% NEQ 0 Goto SQLError
> sqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80

Here is a simple example that illustrates:

E:\temp>osql -E -n -Q "EXIT (SELECT 47)"

----
47

(1 row affected)

E:\temp>echo %ERRORLEVEL%
47

E:\temp
By putting the entire SQL batch within EXIT(), OSQL will return the
value of the last result set to the command-line environment.

For details, see the topic on OSQL in Books Online.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9402EF532A497Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Ajay Garg (ajayz90@.hotmail.com) writes:
> > I am running the following OSQL command and capturing the return code
> > for the error .Whenver i have an error like server not exists or uable
> > to login I get a return code of 1 for the %ERRORLEVEL%.However
> > whenever I have an errorof a wrong dbcompatibility error the retun
> > code id 1 even though sql returns an iformation message from OSQL that
> > the right copatibilty levels are 60,70 and 80.How can i get OSQL to
> > return the right return code whenver a error of this type occurrs from
> > batch mode sql.The OSQL i am running from the batch is
> > osql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%
> > -Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txt
> > ECHO %errorlevel% >> %logbatch%
> > IF %ERRORLEVEL% NEQ 0 Goto SQLError
> > sqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80
> Here is a simple example that illustrates:
> E:\temp>osql -E -n -Q "EXIT (SELECT 47)"
> ----
> 47
> (1 row affected)
> E:\temp>echo %ERRORLEVEL%
> 47
> E:\temp>
> By putting the entire SQL batch within EXIT(), OSQL will return the
> value of the last result set to the command-line environment.
> For details, see the topic on OSQL in Books Online.

I noticed someting even more interesting.Even though the return code
was 0
when there was an error the job on sql server which actually failed
showed that it ran sucessfully.Even though when i run the job as an
Xp_cmdshell command on the sql server it shows that it failed what
could be the reason that it behaves that way?

Thanks in anticipation.

Ajay|||Ajay Garg (ajayz90@.hotmail.com) writes:
> I noticed someting even more interesting.Even though the return code
> was 0
> when there was an error the job on sql server which actually failed
> showed that it ran sucessfully.Even though when i run the job as an
> Xp_cmdshell command on the sql server it shows that it failed what
> could be the reason that it behaves that way?

I'm sorry, but I don't follow. Could you clarify with an example?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How can i find the exact error line ?

Hi all,

i have an insert function using stored procedure with parameters. At some point while assigning parameters i make a casting problem but all i get is:

String was not recognized as a valid Boolean.

how can i find the exact line causing the error?

thank you

-shane

can you post the code? Perhaps you are passing some incompatible value for a boolean type parameter?|||It's listed in the stack trace that follows that message you pasted here. If you stopped the program in VS 2005, tell it to continue executing, and you'll see it on the HTML page.|||I m using vs 2003 and in the stack tree there is no explanation about it. i found the problem , i was trying to compare a posted radiobutton list item value (which is 0 or 1) with 0 and 1 wihtout knowing radiobutton values were sent as string. i got teh error message because i was going like :
if request("rblcontrol') then
do this...
end if

The problem is; i dont get the line that was causing the problem. And this is what i was looking for. You find the prblem this way or that way, maybe after several hours, but is there a way to get the exact line number that is causing hte problem. Stack tree doesnt give me this, if it would i woulnt ask this question. I tried debugging but i m not getting the error while debugging either. its just happening at the end without giving me any clue of whats causing the problem.

Any ideas/
thank you
-shane|||Usually it will tell you in vs or in a webpage the error with the exact page as long as the page was run on the local computer. Did you end up casting the value to boolean?|||

Once again, my problem isnt with the page. Of course i m getting the error in the page it occurs. The problem is the line that causes the problem.

I am gettng ar error in abc.aspx but the only thing i get is generic error message. For some cases i get the line number, usually for the casting problems i never get the line number. Its taking for ever to find which line causes the problem because sometimes everthing looks so right.

-shane

|||You're not being clear. Which line number? In your .aspxpage, in your code-behind page, or in your stored procedure?

Could you possibly show the part of the code casuing trouble and the exact copy-and-paste of the error screen?