How to change the error log location?

How can you change the location of the Server's error log? I need to get it
off of the c: drive!
Thanks!
It is in the registry. To change from EM: Right-click the server, properties, startup parameters.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:475F8B64-872F-4285-AE4C-14D6255B0379@.microsoft.com...
> How can you change the location of the Server's error log? I need to get it
> off of the c: drive!
> Thanks!
|||Hi ,
I found the following article that talks about the same. Read the summary
below for breif info .
SUMMARY:
==============
This article describes how to change the location of the data and log files
for any SQL Server 7.0 or SQL Server 2000 database.
Moving SQL Server databases to a new location with Detach/Attach :
================================================== ===============
Microsoft Knowledge Base Article - 224071 :
http://support.microsoft.com/default...b;EN-US;224071
Moving the master database:
===========================
Change the path for the master data and log files in SQL Server Enterprise
Manager.
Note You may optionally change the location of the error log here as well.
Right-click the SQL Server in Enterprise Manager and click Properties.
Click the Startup Parameters button and you will see the following entries:
-dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf
-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log file.
Change these values as follows:
Remove the current entries for the Master.mdf and Mastlog.ldf files.
Add new entries specifying the new location: -dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf
Stop SQL Server.
Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
Restart SQL Server.
Regards,
Venkat.

how to change the crystal report database location at runtime in vb.net

Hello,

I am using the following codings to change the databse location at runtime. But i found an error 'invalid field name in formula' in some of the reports. The error occurs when we use the databse fields in 'formula field editor' of crystal reports. let me know the reason why its happen? find and send a solution to solve this problem.

Dim logOnInfo As New TableLogOnInfo()
Dim crtableLogoninfo As New TableLogOnInfo()
Dim crConnectionInfo As New ConnectionInfo()
Dim TableLocation As String

Dim vTable As Table
crConnectionInfo.ServerName = gConnServerName
crConnectionInfo.DatabaseName = gConnDatabase
crConnectionInfo.UserID = gConnUserName
crConnectionInfo.Password = gConnPassword

RptDoc.SetDatabaseLogon(gConnUserName, gConnPassword, gConnServerName, gConnDatabase)

For Each vTable In RptDoc.Database.Tables
crtableLogoninfo = vTable.LogOnInfo
crtableLogoninfo.ConnectionInfo = crConnectionInfo
vTable.ApplyLogOnInfo(crtableLogoninfo)
'' vTable.TestConnectivity()
'' MsgBox(vTable.TestConnectivity)
'' MsgBox(vTable.Fields(0).Name())

vTable.Location = Trim(crConnectionInfo.DatabaseName) & ".dbo." & Trim(vTable.Location.Substring(vTable.Location.LastIndexOf(".") + 1))

Next

thank you .
shanthipl reply

How to change the collation of all field in all table of a databas

when I join two table, I got the error "Cannot resolve collation conflict for
equal to operation.", I know this is due to the collation is different for
two table, so I want to write a script to change all field in all table of a
database to use the same collation, how to write that script?
Thx!!
kei
ALTER TABLE....ALTER COLUMN......
For more details please refer to the BOL.
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!
|||Meanwhile, you can specify collation in the join statement. Check for
COLLATE.
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!

How to change the collation of all field in all table of a databas

when I join two table, I got the error "Cannot resolve collation conflict for
equal to operation.", I know this is due to the collation is different for
two table, so I want to write a script to change all field in all table of a
database to use the same collation, how to write that script?
Thx!!kei
ALTER TABLE....ALTER COLUMN......
For more details please refer to the BOL.
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!|||Meanwhile, you can specify collation in the join statement. Check for
COLLATE.
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!|||But no method to change the collation of all field in all table of a
database? if sepcify collation in the join statement, then all sql statement
need to use such method, quite trouble...
I know there is alter column..., but I also know this method can't work on
primary, foreign key, index field..., right?
"MC" wrote:
> Meanwhile, you can specify collation in the join statement. Check for
> COLLATE.
> MC
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> > when I join two table, I got the error "Cannot resolve collation conflict
> > for
> > equal to operation.", I know this is due to the collation is different for
> > two table, so I want to write a script to change all field in all table of
> > a
> > database to use the same collation, how to write that script?
> > Thx!!
>
>|||kei
Have you checked it?
"kei" <kei@.discussions.microsoft.com> wrote in message
news:76EABC8C-E8BA-4161-A8FA-24E98AD79A47@.microsoft.com...
> But no method to change the collation of all field in all table of a
> database? if sepcify collation in the join statement, then all sql
> statement
> need to use such method, quite trouble...
> I know there is alter column..., but I also know this method can't work on
> primary, foreign key, index field..., right?
> "MC" wrote:
>> Meanwhile, you can specify collation in the join statement. Check for
>> COLLATE.
>> MC
>>
>> "kei" <kei@.discussions.microsoft.com> wrote in message
>> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
>> > when I join two table, I got the error "Cannot resolve collation
>> > conflict
>> > for
>> > equal to operation.", I know this is due to the collation is different
>> > for
>> > two table, so I want to write a script to change all field in all table
>> > of
>> > a
>> > database to use the same collation, how to write that script?
>> > Thx!!
>>|||I still don't know the answer, could you write more explicit? I check
"Changing Collations" in BOL, and it only teach how to change collation for
one column only, and with limitation that if the column is an index..., then
the method is not work, so could you write the solution more clearly.
Thx!!
"Uri Dimant" wrote:
> kei
> Have you checked it?
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:76EABC8C-E8BA-4161-A8FA-24E98AD79A47@.microsoft.com...
> > But no method to change the collation of all field in all table of a
> > database? if sepcify collation in the join statement, then all sql
> > statement
> > need to use such method, quite trouble...
> > I know there is alter column..., but I also know this method can't work on
> > primary, foreign key, index field..., right?
> >
> > "MC" wrote:
> >
> >> Meanwhile, you can specify collation in the join statement. Check for
> >> COLLATE.
> >>
> >> MC
> >>
> >>
> >> "kei" <kei@.discussions.microsoft.com> wrote in message
> >> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> >> > when I join two table, I got the error "Cannot resolve collation
> >> > conflict
> >> > for
> >> > equal to operation.", I know this is due to the collation is different
> >> > for
> >> > two table, so I want to write a script to change all field in all table
> >> > of
> >> > a
> >> > database to use the same collation, how to write that script?
> >> > Thx!!
> >>
> >>
> >>
>
>|||You need to alter each column that has text datatype (varchar, char....).
Alternatively, you can script your database in enterprise manager and
specify 7.0 compatibility. That should give you script without collations.
Then you could execute that script and you get empty database. All thats
left for you is transfer data....
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:26C46269-DC86-4102-89A5-C9195EB47A57@.microsoft.com...
>I still don't know the answer, could you write more explicit? I check
> "Changing Collations" in BOL, and it only teach how to change collation
> for
> one column only, and with limitation that if the column is an index...,
> then
> the method is not work, so could you write the solution more clearly.
> Thx!!
> "Uri Dimant" wrote:
>> kei
>> Have you checked it?
>>
>> "kei" <kei@.discussions.microsoft.com> wrote in message
>> news:76EABC8C-E8BA-4161-A8FA-24E98AD79A47@.microsoft.com...
>> > But no method to change the collation of all field in all table of a
>> > database? if sepcify collation in the join statement, then all sql
>> > statement
>> > need to use such method, quite trouble...
>> > I know there is alter column..., but I also know this method can't work
>> > on
>> > primary, foreign key, index field..., right?
>> >
>> > "MC" wrote:
>> >
>> >> Meanwhile, you can specify collation in the join statement. Check for
>> >> COLLATE.
>> >>
>> >> MC
>> >>
>> >>
>> >> "kei" <kei@.discussions.microsoft.com> wrote in message
>> >> news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
>> >> > when I join two table, I got the error "Cannot resolve collation
>> >> > conflict
>> >> > for
>> >> > equal to operation.", I know this is due to the collation is
>> >> > different
>> >> > for
>> >> > two table, so I want to write a script to change all field in all
>> >> > table
>> >> > of
>> >> > a
>> >> > database to use the same collation, how to write that script?
>> >> > Thx!!
>> >>
>> >>
>> >>
>>

How to change the collation of all field in all table of a databas

when I join two table, I got the error "Cannot resolve collation conflict fo
r
equal to operation.", I know this is due to the collation is different for
two table, so I want to write a script to change all field in all table of a
database to use the same collation, how to write that script?
Thx!!kei
ALTER TABLE....ALTER COLUMN......
For more details please refer to the BOL.
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!|||Meanwhile, you can specify collation in the join statement. Check for
COLLATE.
MC
"kei" <kei@.discussions.microsoft.com> wrote in message
news:1678D16D-092D-4AB6-AEAD-260F710E3AEC@.microsoft.com...
> when I join two table, I got the error "Cannot resolve collation conflict
> for
> equal to operation.", I know this is due to the collation is different for
> two table, so I want to write a script to change all field in all table of
> a
> database to use the same collation, how to write that script?
> Thx!!

How to change SMTP address

Hello,
For some reason, over the last few weeks, my reports are not being
emailed out. Against the reports, it reports an SMTP email error.
Can somebody point me to the correct location in SQL Reporter 2003 to
change the SMTP address please.
Many thanksIt is in the RSReportServer.config file.
Andy Potter

How to change error log location

I have been trying to change the location of the SQL 2005 error log. It's logging to the default location at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

I want to move the log location to the E drive because it can get quite large.

I already changed the setting in the SQL Server Error and Usage config tool, but this did not seem to make a difference.

Can someone tell me please how to do this?

Thank You,

Ed

I don't believe there is a suported way of doing this.

The SQL Server Error and Usage not for the standard SQL Log, but for the log relating to fatal errors and usage information see "Error and Usage Report Settings" in BOl for more information.

|||YOu can schedule a job to recycle the error log and step2 to move the archive files to another drive where you have more storage.|||

The errorlog location is controlled by the -e startup parameter, which is read from the registry at startup.

You can change the default location from the SQL Server Configuration Manager. Open the properties for the SQL Server Service and find the Startup Parameters entry on the Advanced page.

By default the entry looks something like this:

-dD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

Change the path after -e to be anything you'd like.

How to change encoding for XML Column

I am updating an xml column in SQLServer 2005, but it always gives me
an error
"XML parsing: line 1, character 38, unable to switch the encoding"
my csharp is like the following
cmd.Parameters.Add("@.xml", SqlDbType.Xml);
cmd.Parameters["@.xml"].Value =xmldata;
My xml have the following line
"<?xml version="1.0" encoding="UTF-8"?>..."
If I remove this line, everything is OK, looks like the SQLParameter is
set to UTF-16 and my xml data is set to UTF-8, how do I make
SQLParameter take UTF-8? I did not see any properties under
SQLParameter to change the encode.
Please advice.
Thanks in advance.
JohnWell I do search a bit and perhaps you want to remove that line before you
insert it in to the column.
chanmm
"John" <johnxhc@.yahoo.com> wrote in message
news:1149546544.618008.58060@.i39g2000cwa.googlegroups.com...
>I am updating an xml column in SQLServer 2005, but it always gives me
> an error
> "XML parsing: line 1, character 38, unable to switch the encoding"
> my csharp is like the following
> cmd.Parameters.Add("@.xml", SqlDbType.Xml);
> cmd.Parameters["@.xml"].Value =xmldata;
> My xml have the following line
> "<?xml version="1.0" encoding="UTF-8"?>..."
> If I remove this line, everything is OK, looks like the SQLParameter is
> set to UTF-16 and my xml data is set to UTF-8, how do I make
> SQLParameter take UTF-8? I did not see any properties under
> SQLParameter to change the encode.
> Please advice.
> Thanks in advance.
> John
>|||Try using SqlDbType.VarChar (even if your parameter in the proc is really
XML). This worked for me. Reason is likely that you're using a System.String
(Unicode) and the XML data type is internally UTF-16, so telling it UTF-8 is
switching encodings, after its "decided" you wanted UTF-16. There is
automatic string conversion if the encoding matches the SQL varchar data
type.
Bear in mind, you'll always get the data out as UTF-16.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"John" <johnxhc@.yahoo.com> wrote in message
news:1149546544.618008.58060@.i39g2000cwa.googlegroups.com...
>I am updating an xml column in SQLServer 2005, but it always gives me
> an error
> "XML parsing: line 1, character 38, unable to switch the encoding"
> my csharp is like the following
> cmd.Parameters.Add("@.xml", SqlDbType.Xml);
> cmd.Parameters["@.xml"].Value =xmldata;
> My xml have the following line
> "<?xml version="1.0" encoding="UTF-8"?>..."
> If I remove this line, everything is OK, looks like the SQLParameter is
> set to UTF-16 and my xml data is set to UTF-8, how do I make
> SQLParameter take UTF-8? I did not see any properties under
> SQLParameter to change the encode.
> Please advice.
> Thanks in advance.
> John
>

How to catch messages on Event Handlers

hello everyone

I'd like to know if there is a way to catch the error messages when a tasks fails, that's because i's like to store every message on a user variable, so i could log all of them later, I was thinking that it may be possible with the event handlers, could it be?

regards

Yes. When an executable errors the error message is in the @.[System::ErrorDescription] variable scoped to the OnError eventhandler.

-Jamie

|||Thanks Jammie, i'll try to do so.

best regards

How to catch error and retry AFTER dts script step has ran

hello,

i am trying to figure out how to check for failure or success AFTER the script task has ran.

its a piece of cake to write script logic that runs before the task but how do i check things and decide to retry AFTER a script task has ran?

i want to check for an error after a large table replication and if it detects that there was an error i want to RETRY.

dts does not seem to have this one specific piece of functionality. am i overlooking something?alright... i am closer to figuring this out although the solution seems a bit complex.

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=6196&pg=2

the WROX book that i bought on DTS "DOES NOT EVEN COVER THIS TOPIC" . it covers reactive error handling but doesn't say a word about proactive error handling.

i have a java programming background. can anyone see this from my point of view and give me a hint?

this reminds me of using the "onStart" and "onLoad" methods of ASP programming but it doesn't seem to be quite as simple to use...

How to catch an exception?

Is there something like exception handling in T-SQL?
For example, how to catch an error of convertion at this
sample:

CREATE PROCEDURE SP
@.param VARCHAR(50)
AS BEGIN
DELCARE @.var INT
-- try {
SET @.var = CONVERT( int, @.param)
-- } catch (error#245) {
-- handle an error right here
-- }
END

It must be invisible for a caller of SP if something wrong inside SP.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!>
> CREATE PROCEDURE SP
> @.param VARCHAR(50)
> AS BEGIN
> DELCARE @.var INT
> -- try {
> SET @.var = CONVERT( int, @.param)
> -- } catch (error#245) {
> -- handle an error right here
> -- }
> END
In this situation you can use ISNUMERIC function.
In T-SQL there are not try..catch constructions and all errors you will get
on client :(.

ALTER PROCEDURE SP
@.param VARCHAR(50)
AS BEGIN
DECLARE @.var INT
-- try {
if ISNUMERIC(@.param) = 0
begin
RAISERROR('Error converting @.param -> @.var',16,10)
RETURN -1
end
SET @.var = CONVERT( int, @.param)
END
go
exec SP
@.param = '1a'
go|||Hi, Garry!

Thank you for your answer but my question was not about how to suppress
exactly convertion error. I'm looking for something like try-catch. Is
it truth that no way to handle an exception inside the server execution?
It is sad...

Ok, my problem is that: some of my procedures are able to generate both
correct rowset and some error messages at the same time. But when I try
to open the query with EXEC thru OLE DB I receive an error, not rowset
:( The best issue for me: if I would be able to handle all the errors
inside the stored procedure body...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Evgeny Gopengauz (evgop@.ucs.ru) writes:
> Is there something like exception handling in T-SQL?
> For example, how to catch an error of convertion at this
> sample:
> CREATE PROCEDURE SP
> @.param VARCHAR(50)
> AS BEGIN
> DELCARE @.var INT
> -- try {
> SET @.var = CONVERT( int, @.param)
> -- } catch (error#245) {
> -- handle an error right here
> -- }
> END
> It must be invisible for a caller of SP if something wrong inside SP.

For SQL2000 the answer is very distinctively: NO. Error handling in
SQL Server 2000 is a mess. There are two articles on my web site about
the topic http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html.

The good news is that in the next version of SQL Server, SQL 2005 which
now is in beta, there are great improvements in this area, and there
is indeed a TRY-CATCH construct.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Garry (vadim@.viii.ntu-kpi.kiev.ua) writes:
> In this situation you can use ISNUMERIC function.

No, you can never use the isnumeric() function, because it is
virtually useless. isnumeric() tells you that a string can be
converted to some numeric data type, but you can find out which. A
string that can be converted to money may not convert to float or
vice versa.

For test of a positive integer number, this is the way to do:

@.x NOT LIKE '%[^0-9]%'

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

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

How to capture the error message?

Is possible to capture the message of error generated in the execution
of a command SQL?
Thanks.DECLARE@.Error

..sql statement

SELECT @.Error = @.@.ERROR

SELECT *
FROM master..sysmessages
WHERE error = @.Error|||I thank its reply, but I want to capture the specific message of the error and not it generic message. For example:

"Cannot insert the value NULL into column 'field1', table 'DB.dbo.Tab'; column does not allow nulls. INSERT fails."

instead of :

"Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails."

DECLARE@.Error

..sql statement

SELECT @.Error = @.@.ERROR

SELECT *
FROM master..sysmessages
WHERE error = @.Error

How to capture Package Validation Error?

Hi,

I have a package which has 5 connection managers. One of the Connection Manager has incorrect server name, which results in Package Validation error. Which event handler should be used to run on such errors for OnError Event handler doesnt work @. all.

Thanks

Prasad

I may be wrong; but I think if the validation step fails; the package logging nor event handler would work as they need a valid package. What you can do is to capture the output of the package execution command line. Eg if you are using sql server agent; and run the package as CmdExec step; you will see the validation error in the job log.|||

i dont see any errors in the job log which point out to invalid packages.

Is there any metadata table like sysdtspackages90 which can be queried to find out if the generated packages are valid or not?

|||You need to run the package through a CmdExec step, not from a SSIS step, as Rafael mentioned. This is the same as running it from the command line.|||using dtexec /validate we can validate the packages|||

2lazydba wrote:

using dtexec /validate we can validate the packages

Well now THERE is something I never knew. Thanks 2lazydba.

-Jamie

How to capture error upon connect error?

I would like to continue processing when a connect error occurs.

Set oServer = CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect oName

Would like to continue if the connect fails rather than have the script fail. How do I do that?

Thanks!

Add a Try-Catch construct to your code:

Try
oServer = CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect(oName)
Catch ex As Exception
MsgBox(ex.Message)
End Try

How to capture error upon connect error?

I would like to continue processing when a connect error occurs.

Set oServer = CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect oName

Would like to continue if the connect fails rather than have the script fail. How do I do that?

Thanks!

Add a Try-Catch construct to your code:

Try
oServer = CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True
oServer.Connect(oName)
Catch ex As Exception
MsgBox(ex.Message)
End Try

How to capture Error Messages in script

I know how to capture the error number using @.@.ERROR, but I don't know how
to capture the actual text. It is easy to do so via an application that uses
a database driver (BDE, ADO .NET etc...), but I just want to capture the
error text in SQL script and log it to a table.
Please Note:
'select description from master.dbo.sysmessages' alone is not sufficientThe current version of SQL Server does not provide a method to get error
message text in Transact-SQL. This functionality is planned for SQL 2005,
though.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <nospam@.nospam.com> wrote in message
news:eRXqGcYjFHA.3448@.TK2MSFTNGP10.phx.gbl...
>I know how to capture the error number using @.@.ERROR, but I don't know how
> to capture the actual text. It is easy to do so via an application that
> uses
> a database driver (BDE, ADO .NET etc...), but I just want to capture the
> error text in SQL script and log it to a table.
> Please Note:
> 'select description from master.dbo.sysmessages' alone is not sufficient
>

how to capture custom made error message into table

Dear all,
I want to know how to custom made error message into table, as I illustrated
T-SQL below :
if objectproperty(object_id('DateTable'),'I
sUserTable')=1
drop table DateTable
if objectproperty(object_id('InputFromFlatF
ile1'),'IsUserTable')=1
drop table InputFromFlatFile1
if objectproperty(object_id('errorLog'),'Is
UserTable')=1
drop table errorLog
if objectproperty(object_id('usp_testData')
,'IsProcedure')=1
drop proc usp_testData
create table DateTable
(datetimestamp datetime)
go
create table errorLog
(
data varchar(200),
errmsg varchar(2000)
)
go
create proc usp_testData
(@.dateinfo datetime)
as
begin
if convert(varchar(8),@.dateinfo,112)<'20060328'
begin
raiserror('Date input invalid because it''s entered before 28 Mar
2006',16,1)
return
end
else
insert into DateTable (datetimestamp) values (@.dateinfo)
end
go
create table InputFromFlatFile1
(
id int identity (1,1) not null,
linestring varchar(100)
)
go
insert into InputFromFlatFile1 (linestring) values ('20060328');
insert into InputFromFlatFile1 (linestring) values ('20060212');
insert into InputFromFlatFile1 (linestring) values ('20060115');
declare @.linestring varchar(200), @.errmsg varchar(2000)
declare @.cnt int, @.recnum int, @.error int
set @.cnt = 1
select @.recnum = count(*) from InputFromFlatFile1
while @.cnt <= @.recnum
begin
select @.linestring = linestring from InputFromFlatFile1 where id = @.cnt;
print @.linestring
exec @.error = usp_testData @.linestring;
if @.error<>0 or @.@.error <> 0
begin
if @.@.error <> 0
begin
set @.error = @.@.error
select @.errmsg = description from master.dbo.sysmessages where error =
@.error
end
/*
How can I capture user-made error in stored procedure usp_testData to put
into ErrorLog table
'
*/
insert into errorLog values (@.linestring, @.errmsg)
end
set @.cnt = @.cnt + 1;
end
select * from DateTable
select * from errorLog
from query analyzer I get error message below :
Date input invalid because it is entered before 28 Mar 2006
but I cannot get the error message as above in errorLog table
How can I capture error message like in query analyzer in errorLog table?
Regards,
Koronx
SQL HobbistHi
Check out http://www.sommarskog.se/error-handling-II.html and
http://www.sommarskog.se/error-handling-I.html You will need to add the
insert statement into your code or possibly do it through the client
application by trapping the error message returned.
John
"Kornx Koronx" wrote:

> Dear all,
> I want to know how to custom made error message into table, as I illustrat
ed
> T-SQL below :
> if objectproperty(object_id('DateTable'),'I
sUserTable')=1
> drop table DateTable
> if objectproperty(object_id('InputFromFlatF
ile1'),'IsUserTable')=1
> drop table InputFromFlatFile1
> if objectproperty(object_id('errorLog'),'Is
UserTable')=1
> drop table errorLog
> if objectproperty(object_id('usp_testData')
,'IsProcedure')=1
> drop proc usp_testData
> create table DateTable
> (datetimestamp datetime)
> go
> create table errorLog
> (
> data varchar(200),
> errmsg varchar(2000)
> )
> go
> create proc usp_testData
> (@.dateinfo datetime)
> as
> begin
> if convert(varchar(8),@.dateinfo,112)<'20060328'
> begin
> raiserror('Date input invalid because it''s entered before 28 Mar
> 2006',16,1)
> return
> end
> else
> insert into DateTable (datetimestamp) values (@.dateinfo)
> end
> go
> create table InputFromFlatFile1
> (
> id int identity (1,1) not null,
> linestring varchar(100)
> )
> go
> insert into InputFromFlatFile1 (linestring) values ('20060328');
> insert into InputFromFlatFile1 (linestring) values ('20060212');
> insert into InputFromFlatFile1 (linestring) values ('20060115');
> declare @.linestring varchar(200), @.errmsg varchar(2000)
> declare @.cnt int, @.recnum int, @.error int
> set @.cnt = 1
> select @.recnum = count(*) from InputFromFlatFile1
> while @.cnt <= @.recnum
> begin
> select @.linestring = linestring from InputFromFlatFile1 where id = @.cnt;
> print @.linestring
> exec @.error = usp_testData @.linestring;
> if @.error<>0 or @.@.error <> 0
> begin
> if @.@.error <> 0
> begin
> set @.error = @.@.error
> select @.errmsg = description from master.dbo.sysmessages where error =
> @.error
> end
> /*
> How can I capture user-made error in stored procedure usp_testData to put
> into ErrorLog table
> '
> */
> insert into errorLog values (@.linestring, @.errmsg)
> end
> set @.cnt = @.cnt + 1;
> end
> select * from DateTable
> select * from errorLog
> from query analyzer I get error message below :
> Date input invalid because it is entered before 28 Mar 2006
>
> but I cannot get the error message as above in errorLog table
> How can I capture error message like in query analyzer in errorLog table?
> Regards,
> Koronx
> SQL Hobbist

How to capture an error message in T-SQL?

I need to capture an actual error message and place this record into a
log table.
I know that I can get error # from ERROR variable.
Where can I get the text, that consist information aboout objects that
caused problem?
Example
create procedure MyProc as
DECLARE Err int;
DECLARE ErrMsg varchar(256)
DELETE FROM ParentTable where ID=@.P_ID;
set @.Err=ERROR;
if @.Err!=0
begin
set @.ErrMsg=''?
insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
end;
go;
I expect to see in MyLog something like `Foreign key violation, child
record found, constraint "FK_ChildTable"`,
or "No permission to delete for table "ParentTable"`
or whatever I would see if I execute this TSQL in SQL Analyzer manually.
BTW, are all of such errors being written into some standard MSSQL log?
Is it SysLog or where?
Thanks,
MarkMark
Unfortunatly you cannot capture such kind of errors in current version.I
know with Yukon will introduce error handle like try--catch but cannot give
more details at this time.
On other hand look at my example helps you to solve the problem (Modify it
for your needs)
create table parent
(
col int not null primary key
)
go
insert into parent values (1)
go
create table child
(
col int not null primary key,
col1 int not null --references parent (col)
)
go
insert into child(col,col1) values (1,1)
insert into child(col,col1) values (2,1)
go
create trigger my_tr on parent for delete
as
if exists ( select * from child c join deleted d on c.col1=d.col)
raiserror ('Cannot delete',16,1)
go
delete parent where col=1
go
drop table child
drop table parent
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>|||> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot
give
> more details at this time.
See http://msdn.microsoft.com/msdnmag/i...on/default.aspx
for new Yukon enhancements in error handling with the TRY/CATCH construct
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
************************************
SQL Source Control 2003 - for
SQL Server Source Safe integration
and custom databases documentation|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot gi
ve
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot gi
ve
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot gi
ve
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>
>
>|||Sebastian, thanks,
It is good that Yukon can handle exceptions by TRY/CATCH. However I have
not found any Error Message Text capturing in examples. The error
messages are coded into the procedure, and are not actual messages from
Server. That unfortunate because in many cases the sole error code is
not enough, the object names associated with exception would be very
helpfull for further troubleshooting.
Mark
Sebastian K. Zaklada wrote:
> give
>
>
> See http://msdn.microsoft.com/msdnmag/i...ledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ************************************
> SQL Source Control 2003 - for
> SQL Server Source Safe integration
> and custom databases documentation
>|||Mark,

> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information about objects that
> caused problem?
You can't at the TSQL level.

> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
It depends whether the error is configured to. Check out the manage errors
dialog in EM and you'll see that checkbox. "Soft" errors are not written to
logs by default.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>

How to capture an error message in T-SQL?

I need to capture an actual error message and place this record into a
log table.
I know that I can get error # from ERROR variable.
Where can I get the text, that consist information aboout objects that
caused problem?
Example
create procedure MyProc as
DECLARE Err int;
DECLARE ErrMsg varchar(256)
DELETE FROM ParentTable where ID=@.P_ID;
set @.Err=ERROR;
if @.Err!=0
begin
set @.ErrMsg=''?
insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
end;
go;
I expect to see in MyLog something like `Foreign key violation, child
record found, constraint "FK_ChildTable"`,
or "No permission to delete for table "ParentTable"`
or whatever I would see if I execute this TSQL in SQL Analyzer manually.
BTW, are all of such errors being written into some standard MSSQL log?
Is it SysLog or where?
Thanks,
MarkMark
Unfortunatly you cannot capture such kind of errors in current version.I
know with Yukon will introduce error handle like try--catch but cannot give
more details at this time.
On other hand look at my example helps you to solve the problem (Modify it
for your needs)
create table parent
(
col int not null primary key
)
go
insert into parent values (1)
go
create table child
(
col int not null primary key,
col1 int not null --references parent (col)
)
go
insert into child(col,col1) values (1,1)
insert into child(col,col1) values (2,1)
go
create trigger my_tr on parent for delete
as
if exists ( select * from child c join deleted d on c.col1=d.col)
raiserror ('Cannot delete',16,1)
go
delete parent where col=1
go
drop table child
drop table parent
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>|||> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot
give
> more details at this time.
See http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/default.aspx
for new Yukon enhancements in error handling with the TRY/CATCH construct
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
************************************
SQL Source Control 2003 - for
SQL Server Source Safe integration
and custom databases documentation|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot give
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>>I need to capture an actual error message and place this record into a
>>log table.
>>I know that I can get error # from ERROR variable.
>>Where can I get the text, that consist information aboout objects that
>>caused problem?
>>Example
>>create procedure MyProc as
>>DECLARE Err int;
>>DECLARE ErrMsg varchar(256)
>>DELETE FROM ParentTable where ID=@.P_ID;
>>set @.Err=ERROR;
>>if @.Err!=0
>> begin
>> set @.ErrMsg=''?
>> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
>> end;
>>go;
>>I expect to see in MyLog something like `Foreign key violation, child
>>record found, constraint "FK_ChildTable"`,
>>or "No permission to delete for table "ParentTable"`
>>or whatever I would see if I execute this TSQL in SQL Analyzer manually.
>>BTW, are all of such errors being written into some standard MSSQL log?
>>Is it SysLog or where?
>>Thanks,
>>Mark
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot give
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>>I need to capture an actual error message and place this record into a
>>log table.
>>I know that I can get error # from ERROR variable.
>>Where can I get the text, that consist information aboout objects that
>>caused problem?
>>Example
>>create procedure MyProc as
>>DECLARE Err int;
>>DECLARE ErrMsg varchar(256)
>>DELETE FROM ParentTable where ID=@.P_ID;
>>set @.Err=ERROR;
>>if @.Err!=0
>> begin
>> set @.ErrMsg=''?
>> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
>> end;
>>go;
>>I expect to see in MyLog something like `Foreign key violation, child
>>record found, constraint "FK_ChildTable"`,
>>or "No permission to delete for table "ParentTable"`
>>or whatever I would see if I execute this TSQL in SQL Analyzer manually.
>>BTW, are all of such errors being written into some standard MSSQL log?
>>Is it SysLog or where?
>>Thanks,
>>Mark
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot give
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>>I need to capture an actual error message and place this record into a
>>log table.
>>I know that I can get error # from ERROR variable.
>>Where can I get the text, that consist information aboout objects that
>>caused problem?
>>Example
>>create procedure MyProc as
>>DECLARE Err int;
>>DECLARE ErrMsg varchar(256)
>>DELETE FROM ParentTable where ID=@.P_ID;
>>set @.Err=ERROR;
>>if @.Err!=0
>> begin
>> set @.ErrMsg=''?
>> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
>> end;
>>go;
>>I expect to see in MyLog something like `Foreign key violation, child
>>record found, constraint "FK_ChildTable"`,
>>or "No permission to delete for table "ParentTable"`
>>or whatever I would see if I execute this TSQL in SQL Analyzer manually.
>>BTW, are all of such errors being written into some standard MSSQL log?
>>Is it SysLog or where?
>>Thanks,
>>Mark
>
>|||Sebastian, thanks,
It is good that Yukon can handle exceptions by TRY/CATCH. However I have
not found any Error Message Text capturing in examples. The error
messages are coded into the procedure, and are not actual messages from
Server. That unfortunate because in many cases the sole error code is
not enough, the object names associated with exception would be very
helpfull for further troubleshooting.
Mark
Sebastian K. Zaklada wrote:
>>Unfortunatly you cannot capture such kind of errors in current version.I
>>know with Yukon will introduce error handle like try--catch but cannot
> give
>>more details at this time.
>
> See http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/default.aspx
> for new Yukon enhancements in error handling with the TRY/CATCH construct
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ************************************
> SQL Source Control 2003 - for
> SQL Server Source Safe integration
> and custom databases documentation
>|||Mark,
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information about objects that
> caused problem?
You can't at the TSQL level.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
It depends whether the error is configured to. Check out the manage errors
dialog in EM and you'll see that checkbox. "Soft" errors are not written to
logs by default.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>

How to by pass this error message

Hi,

I am trying to import transfer data from one database(sqlserver) to another database(sqlserver)...

But when i run the stored procedure... it gives me the following error

Msg 2627, Level 14, State 1, Procedure usp_ImportFunds_Growthof10K, Line 36

Violation of PRIMARY KEY constraint 'PK_Growthof10K'. Cannot insert duplicate key in object 'Growthof10K'.

and this is my sproc

Code Snippet

USE [StageFiserv_Dev]

GO

/****** Object: StoredProcedure [dbo].[usp_ImportFunds_Growthof10K] Script Date: 08/10/2007 12:53:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[usp_ImportFunds_Growthof10K]

AS

BEGIN

DECLARE @.Count int

SET NOCOUNT ON;

UPDATE Fiserv_Dev..Growthof10K

SET

ChartHeader = g.ChartHeader,

Dates = Substring(g.Dates,1,9),

NAV = g.NAV,

LastChangeDate = GetDate()

FROM

Fiserv_Dev..Growthof10K gk

Join [Growth] g ON gk.Cusip = g.Cusip

Where

gk.ChartHeader <> g.ChartHeader

OR

gk.Dates <> Substring(g.Dates,1,9)

OR

gk.NAV <> g.NAV

SET @.Count = @.@.ROWCOUNT

IF @.Count > 0

RAISERROR('Updated %d records(s) in Growthof10K.', 0, 1, @.Count) WITH NOWAIT

ELSE

RAISERROR('No changes made Growthof10K.', 0, 1) WITH NOWAIT

RAISERROR('Adding records to Growthof10K.',0,1) With NOWAIT

INSERT INTO Fiserv_Dev..Growthof10K

(

Cusip,

ChartHeader,

Dates,

NAV

)

SELECT

g.Cusip,

g.ChartHeader,

Substring(g.Dates,1,9),

g.NAV

FROM

Growth g

Where

NOT Exists (

Select *

FROM

Fiserv_Dev..Growthof10K gk

Where

gk.Cusip = g.Cusip

)

SET @.Count = @.@.ROWCOUNT

IF @.Count > 0

RAISERROR('Added %d records(s) to Growthof10K table.', 0, 1, @.Count) WITH NOWAIT

ELSE

RAISERROR('No records added to Growthof10K table.', 0, 1) WITH NOWAIT

SET NOCOUNT OFF

END

There around 763 records in the growth table

Can some one please help me.

Regards,

Karen

Karen

You are inserting a duplicate value in a primary key. You can't do that. Delete the duplicate values for the primary key field or remove the primary key constraint.

|||

thanks...|||

Did this solve your problem? If so then please mark it as answer.