sql 2000 error handling
in [Programming SQL]
|
From: Mecn on 9 Jun 2010 18:10 hi, I have a sql SP that process text file into a sql table. I need to have error hjandling for the SP. .net interface can get the error message. Raiserror() ---how do i get error message? SQL2000 sp4a thanks,
From: Erland Sommarskog on 10 Jun 2010 03:14 Mecn (mecn(a)yahoo.com) writes: > I have a sql SP that process text file into a sql table. > > I need to have error hjandling for the SP. .net interface can get the > error message. > Raiserror() ---how do i get error message? Where do you want to retrieve the error message? Your question is somewhat terse. Maybe you could elaborate a bit? -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Mecn on 10 Jun 2010 09:46 I am exec a SP with a file location parameter. If I entered a not exist file location, raiserror() should give me "path/file do not exist" something like that. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D935E16399D5Yazorman(a)127.0.0.1... > Mecn (mecn(a)yahoo.com) writes: >> I have a sql SP that process text file into a sql table. >> >> I need to have error hjandling for the SP. .net interface can get the >> error message. >> Raiserror() ---how do i get error message? > > Where do you want to retrieve the error message? Your question is somewhat > terse. Maybe you could elaborate a bit? > > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Erland Sommarskog on 10 Jun 2010 10:05 Mecn (mecn(a)yahoo.com) writes: > I am exec a SP with a file location parameter. > If I entered a not exist file location, raiserror() should give me > "path/file do not exist" something like that. And what does the procedure do? BULK INSERT? I think you should let it suffice with the error message from BULK INSERT. One problem is that there is no good way to check from T-SQL whether a file exists or not. Another problem is that you cannot catch the error message from BULK INSERT in SQL 2000 - just as you cannot catch any error message from SQL 2000. The message from BULK INSERT is particularly difficult, because it is considered a compilation error. This means that the procedure is aborted in this case, so even in SQL 2005 this is tricky. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Mecn on 10 Jun 2010 10:16
Yes Bulk insert "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D93A3C18B6D7Yazorman(a)127.0.0.1... > Mecn (mecn(a)yahoo.com) writes: >> I am exec a SP with a file location parameter. >> If I entered a not exist file location, raiserror() should give me >> "path/file do not exist" something like that. > > And what does the procedure do? BULK INSERT? > > I think you should let it suffice with the error message from BULK > INSERT. > > One problem is that there is no good way to check from T-SQL whether a > file exists or not. > > Another problem is that you cannot catch the error message from BULK > INSERT in SQL 2000 - just as you cannot catch any error message from > SQL 2000. The message from BULK INSERT is particularly difficult, because > it is considered a compilation error. This means that the procedure is > aborted in this case, so even in SQL 2005 this is tricky. > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |