|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Internal SQL Server errorRunning the script below will result in this error in SQL2K SP4 environment:
create view dbo.vw_INTERNAL_ERROR as select top 100 percent * from (select top 100 percent [DateField]=getdate() order by 1) x1 go if exists (select 1 from dbo.vw_INTERNAL_ERROR) print 'Exists' else print 'Doesn''t Exist' go drop view dbo.vw_INTERNAL_ERROR go But in SQL2K5 no error is produced. Anybody seen it? Not sure what it was supposed to do...would use a different existance
test...but it worked on my system SQL 2000 SP4. It does fail if you add the IF after the DROP i.e., create view dbo.vw_INTERNAL_ERROR as select top 100 percent * from (select top 100 percent [DateField]=getdate() order by 1) x1 go if exists (select 1 from dbo.vw_INTERNAL_ERROR) print 'Exists' else print 'Doesn''t Exist' go drop view dbo.vw_INTERNAL_ERROR go if exists (select 1 from dbo.vw_INTERNAL_ERROR) print 'Exists' else print 'Doesn''t Exist' go HTH Jerry Show quote "rdjabarov" <rdjaba***@discussions.microsoft.com> wrote in message news:ADD661AB-36D6-43F0-9D09-B0B05EA3CBF9@microsoft.com... > Running the script below will result in this error in SQL2K SP4 > environment: > > create view dbo.vw_INTERNAL_ERROR as > select top 100 percent * from (select top 100 percent > [DateField]=getdate() order by 1) x1 > go > if exists (select 1 from dbo.vw_INTERNAL_ERROR) > print 'Exists' > else > print 'Doesn''t Exist' > go > drop view dbo.vw_INTERNAL_ERROR > go > > But in SQL2K5 no error is produced. > > Anybody seen it? Thanks for replying, Jerry.
It's just a test that I had to come up with after a real production stored procedure started failing. So this was just to identify the issue. And I also posted the work-around, rather than the originally structured script, which you'll find below: create view dbo.vw_INTERNAL_ERROR as select top 100 percent * from (select [DateField]=getdate()) x1 order by x1.[DateField] go if exists (select 1 from dbo.vw_INTERNAL_ERROR) print 'Exists' else print 'Doesn''t Exist' go drop view dbo.vw_INTERNAL_ERROR go If you attempt to reference the object after it's dropped then of course the script will fail, but with "Invalid object..." error. Thanks in advance. Show quote "Jerry Spivey" wrote: > Not sure what it was supposed to do...would use a different existance > test...but it worked on my system SQL 2000 SP4. > > It does fail if you add the IF after the DROP i.e., > > create view dbo.vw_INTERNAL_ERROR as > select top 100 percent * from (select top 100 percent > [DateField]=getdate() order by 1) x1 > go > if exists (select 1 from dbo.vw_INTERNAL_ERROR) > print 'Exists' > else > print 'Doesn''t Exist' > go > drop view dbo.vw_INTERNAL_ERROR > go > if exists (select 1 from dbo.vw_INTERNAL_ERROR) > print 'Exists' > else > print 'Doesn''t Exist' > go > > HTH > > Jerry > "rdjabarov" <rdjaba***@discussions.microsoft.com> wrote in message > news:ADD661AB-36D6-43F0-9D09-B0B05EA3CBF9@microsoft.com... > > Running the script below will result in this error in SQL2K SP4 > > environment: > > > > create view dbo.vw_INTERNAL_ERROR as > > select top 100 percent * from (select top 100 percent > > [DateField]=getdate() order by 1) x1 > > go > > if exists (select 1 from dbo.vw_INTERNAL_ERROR) > > print 'Exists' > > else > > print 'Doesn''t Exist' > > go > > drop view dbo.vw_INTERNAL_ERROR > > go > > > > But in SQL2K5 no error is produced. > > > > Anybody seen it? > > > Please read my second post.
Show quote "rdjabarov" <rdjaba***@discussions.microsoft.com> wrote in message news:354A0002-82EA-4EF3-8BF4-73A3737A81C6@microsoft.com... > Thanks for replying, Jerry. > > It's just a test that I had to come up with after a real production stored > procedure started failing. So this was just to identify the issue. And I > also posted the work-around, rather than the originally structured script, > which you'll find below: > > create view dbo.vw_INTERNAL_ERROR as > select top 100 percent * from (select [DateField]=getdate()) x1 order by > x1.[DateField] > go > if exists (select 1 from dbo.vw_INTERNAL_ERROR) > print 'Exists' > else > print 'Doesn''t Exist' > go > drop view dbo.vw_INTERNAL_ERROR > go > > > If you attempt to reference the object after it's dropped then of course > the > script will fail, but with "Invalid object..." error. > > Thanks in advance. > > "Jerry Spivey" wrote: > >> Not sure what it was supposed to do...would use a different existance >> test...but it worked on my system SQL 2000 SP4. >> >> It does fail if you add the IF after the DROP i.e., >> >> create view dbo.vw_INTERNAL_ERROR as >> select top 100 percent * from (select top 100 percent >> [DateField]=getdate() order by 1) x1 >> go >> if exists (select 1 from dbo.vw_INTERNAL_ERROR) >> print 'Exists' >> else >> print 'Doesn''t Exist' >> go >> drop view dbo.vw_INTERNAL_ERROR >> go >> if exists (select 1 from dbo.vw_INTERNAL_ERROR) >> print 'Exists' >> else >> print 'Doesn''t Exist' >> go >> >> HTH >> >> Jerry >> "rdjabarov" <rdjaba***@discussions.microsoft.com> wrote in message >> news:ADD661AB-36D6-43F0-9D09-B0B05EA3CBF9@microsoft.com... >> > Running the script below will result in this error in SQL2K SP4 >> > environment: >> > >> > create view dbo.vw_INTERNAL_ERROR as >> > select top 100 percent * from (select top 100 percent >> > [DateField]=getdate() order by 1) x1 >> > go >> > if exists (select 1 from dbo.vw_INTERNAL_ERROR) >> > print 'Exists' >> > else >> > print 'Doesn''t Exist' >> > go >> > drop view dbo.vw_INTERNAL_ERROR >> > go >> > >> > But in SQL2K5 no error is produced. >> > >> > Anybody seen it? >> >> >> I did, but it answers the question that I didn't ask.
Thanks. Show quote "Jerry Spivey" wrote: > Please read my second post. > > "rdjabarov" <rdjaba***@discussions.microsoft.com> wrote in message > news:354A0002-82EA-4EF3-8BF4-73A3737A81C6@microsoft.com... > > Thanks for replying, Jerry. > > > > It's just a test that I had to come up with after a real production stored > > procedure started failing. So this was just to identify the issue. And I > > also posted the work-around, rather than the originally structured script, > > which you'll find below: > > > > create view dbo.vw_INTERNAL_ERROR as > > select top 100 percent * from (select [DateField]=getdate()) x1 order by > > x1.[DateField] > > go > > if exists (select 1 from dbo.vw_INTERNAL_ERROR) > > print 'Exists' > > else > > print 'Doesn''t Exist' > > go > > drop view dbo.vw_INTERNAL_ERROR > > go > > > > > > If you attempt to reference the object after it's dropped then of course > > the > > script will fail, but with "Invalid object..." error. > > > > Thanks in advance. > > > > "Jerry Spivey" wrote: > > > >> Not sure what it was supposed to do...would use a different existance > >> test...but it worked on my system SQL 2000 SP4. > >> > >> It does fail if you add the IF after the DROP i.e., > >> > >> create view dbo.vw_INTERNAL_ERROR as > >> select top 100 percent * from (select top 100 percent > >> [DateField]=getdate() order by 1) x1 > >> go > >> if exists (select 1 from dbo.vw_INTERNAL_ERROR) > >> print 'Exists' > >> else > >> print 'Doesn''t Exist' > >> go > >> drop view dbo.vw_INTERNAL_ERROR > >> go > >> if exists (select 1 from dbo.vw_INTERNAL_ERROR) > >> print 'Exists' > >> else > >> print 'Doesn''t Exist' > >> go > >> > >> HTH > >> > >> Jerry > >> "rdjabarov" <rdjaba***@discussions.microsoft.com> wrote in message > >> news:ADD661AB-36D6-43F0-9D09-B0B05EA3CBF9@microsoft.com... > >> > Running the script below will result in this error in SQL2K SP4 > >> > environment: > >> > > >> > create view dbo.vw_INTERNAL_ERROR as > >> > select top 100 percent * from (select top 100 percent > >> > [DateField]=getdate() order by 1) x1 > >> > go > >> > if exists (select 1 from dbo.vw_INTERNAL_ERROR) > >> > print 'Exists' > >> > else > >> > print 'Doesn''t Exist' > >> > go > >> > drop view dbo.vw_INTERNAL_ERROR > >> > go > >> > > >> > But in SQL2K5 no error is produced. > >> > > >> > Anybody seen it? > >> > >> > >> > > > Try this instead:
if object_id('dbo.vw_INTERNAL_ERROR') IS NOT NULL print 'Exists' else print 'Doesn''t Exist' go HTH Jerry Show quote "rdjabarov" <rdjaba***@discussions.microsoft.com> wrote in message news:ADD661AB-36D6-43F0-9D09-B0B05EA3CBF9@microsoft.com... > Running the script below will result in this error in SQL2K SP4 > environment: > > create view dbo.vw_INTERNAL_ERROR as > select top 100 percent * from (select top 100 percent > [DateField]=getdate() order by 1) x1 > go > if exists (select 1 from dbo.vw_INTERNAL_ERROR) > print 'Exists' > else > print 'Doesn''t Exist' > go > drop view dbo.vw_INTERNAL_ERROR > go > > But in SQL2K5 no error is produced. > > Anybody seen it? The point of the exercise here is not to test for object existance. I need
to see if any data can be retrieved using the view construct. The script appears to always fail if ORDER BY clause is present for the outer query. If I completely remove the clause, or move it into sub-query section, - the error disappears. But doing so may affect the order of data returned for other parts of the app where the view is used. Having a separate view just to test for existence of data will require additional code maintenance if any modifications are introduced, which is not a desired outcome. Thanks. Show quote "Jerry Spivey" wrote: > Try this instead: > > if object_id('dbo.vw_INTERNAL_ERROR') IS NOT NULL > print 'Exists' > else > print 'Doesn''t Exist' > go > > HTH > > Jerry > "rdjabarov" <rdjaba***@discussions.microsoft.com> wrote in message > news:ADD661AB-36D6-43F0-9D09-B0B05EA3CBF9@microsoft.com... > > Running the script below will result in this error in SQL2K SP4 > > environment: > > > > create view dbo.vw_INTERNAL_ERROR as > > select top 100 percent * from (select top 100 percent > > [DateField]=getdate() order by 1) x1 > > go > > if exists (select 1 from dbo.vw_INTERNAL_ERROR) > > print 'Exists' > > else > > print 'Doesn''t Exist' > > go > > drop view dbo.vw_INTERNAL_ERROR > > go > > > > But in SQL2K5 no error is produced. > > > > Anybody seen it? > > > Ok...perhaps you should FULLY state your requirements in future posts so we
don't have to guess or try read your mind. I think that approach would help alleviate any misunderstanding and or confusion. Agreed? Jerry Show quote "rdjabarov" <rdjaba***@discussions.microsoft.com> wrote in message news:6A66933E-B866-45B6-AE42-3665B165350A@microsoft.com... > The point of the exercise here is not to test for object existance. I > need > to see if any data can be retrieved using the view construct. The script > appears to always fail if ORDER BY clause is present for the outer query. > If > I completely remove the clause, or move it into sub-query section, - the > error disappears. But doing so may affect the order of data returned for > other parts of the app where the view is used. Having a separate view > just > to test for existence of data will require additional code maintenance if > any > modifications are introduced, which is not a desired outcome. > > Thanks. > > "Jerry Spivey" wrote: > >> Try this instead: >> >> if object_id('dbo.vw_INTERNAL_ERROR') IS NOT NULL >> print 'Exists' >> else >> print 'Doesn''t Exist' >> go >> >> HTH >> >> Jerry >> "rdjabarov" <rdjaba***@discussions.microsoft.com> wrote in message >> news:ADD661AB-36D6-43F0-9D09-B0B05EA3CBF9@microsoft.com... >> > Running the script below will result in this error in SQL2K SP4 >> > environment: >> > >> > create view dbo.vw_INTERNAL_ERROR as >> > select top 100 percent * from (select top 100 percent >> > [DateField]=getdate() order by 1) x1 >> > go >> > if exists (select 1 from dbo.vw_INTERNAL_ERROR) >> > print 'Exists' >> > else >> > print 'Doesn''t Exist' >> > go >> > drop view dbo.vw_INTERNAL_ERROR >> > go >> > >> > But in SQL2K5 no error is produced. >> > >> > Anybody seen it? >> >> >> You got it!
So, the requirements are to test the existence of data in a table through a view that is also used in other parts of the app. Running a hypothetical simmulation of the original script below results in "Internal..." error: create view dbo.vw_INTERNAL_ERROR as select top 100 percent * from (select [DateField]=getdate()) x1 order by x1.[DateField] go if exists (select 1 from dbo.vw_INTERNAL_ERROR) print 'Exists' else print 'Doesn''t Exist' go drop view dbo.vw_INTERNAL_ERROR go The same script runs successfully in SQL2K5 (September CTP). I just want to confirm that this situation is reproduceable and requires a work-around that I already implemented. Thanks again. Show quote "Jerry Spivey" wrote: > Ok...perhaps you should FULLY state your requirements in future posts so we > don't have to guess or try read your mind. I think that approach would help > alleviate any misunderstanding and or confusion. Agreed? > > Jerry > "rdjabarov" <rdjaba***@discussions.microsoft.com> wrote in message > news:6A66933E-B866-45B6-AE42-3665B165350A@microsoft.com... > > The point of the exercise here is not to test for object existance. I > > need > > to see if any data can be retrieved using the view construct. The script > > appears to always fail if ORDER BY clause is present for the outer query. > > If > > I completely remove the clause, or move it into sub-query section, - the > > error disappears. But doing so may affect the order of data returned for > > other parts of the app where the view is used. Having a separate view > > just > > to test for existence of data will require additional code maintenance if > > any > > modifications are introduced, which is not a desired outcome. > > > > Thanks. > > > > "Jerry Spivey" wrote: > > > >> Try this instead: > >> > >> if object_id('dbo.vw_INTERNAL_ERROR') IS NOT NULL > >> print 'Exists' > >> else > >> print 'Doesn''t Exist' > >> go > >> > >> HTH > >> > >> Jerry > >> "rdjabarov" <rdjaba***@discussions.microsoft.com> wrote in message > >> news:ADD661AB-36D6-43F0-9D09-B0B05EA3CBF9@microsoft.com... > >> > Running the script below will result in this error in SQL2K SP4 > >> > environment: > >> > > >> > create view dbo.vw_INTERNAL_ERROR as > >> > select top 100 percent * from (select top 100 percent > >> > [DateField]=getdate() order by 1) x1 > >> > go > >> > if exists (select 1 from dbo.vw_INTERNAL_ERROR) > >> > print 'Exists' > >> > else > >> > print 'Doesn''t Exist' > >> > go > >> > drop view dbo.vw_INTERNAL_ERROR > >> > go > >> > > >> > But in SQL2K5 no error is produced. > >> > > >> > Anybody seen it? > >> > >> > >> > > > I posted my workarounds here:
http://www.dbforums.com/showthread.php?p=4480795#post4480795 Should it be considered a bug? Show quote "rdjabarov" wrote: > Running the script below will result in this error in SQL2K SP4 environment: > > create view dbo.vw_INTERNAL_ERROR as > select top 100 percent * from (select top 100 percent > [DateField]=getdate() order by 1) x1 > go > if exists (select 1 from dbo.vw_INTERNAL_ERROR) > print 'Exists' > else > print 'Doesn''t Exist' > go > drop view dbo.vw_INTERNAL_ERROR > go > > But in SQL2K5 no error is produced. > > Anybody seen it? rdjabarov (rdjaba***@discussions.microsoft.com) writes:
> I posted my workarounds here: "Internal error" = "bug" in my book.> http://www.dbforums.com/showthread.php?p=4480795#post4480795 > > Should it be considered a bug? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp Thanks for the answer.
Where should I post the bug? Show quote "Erland Sommarskog" wrote: > rdjabarov (rdjaba***@discussions.microsoft.com) writes: > > I posted my workarounds here: > > http://www.dbforums.com/showthread.php?p=4480795#post4480795 > > > > Should it be considered a bug? > > "Internal error" = "bug" in my book. > > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp > > =?Utf-8?B?cmRqYWJhcm92?= (rdjaba***@discussions.microsoft.com) writes:
> Thanks for the answer. I've reported the bug in our internal MVP forum, which is also monitored> > Where should I post the bug? by Microsoft people. However, I would suspect that since since the query works in SQL 2005, that it is not likely that the bug is fixed in SQL 2000. If it is important to you that the bug is fixed in SQL 2000, you should open a case with Microsoft, and try to convince them that you need a hotfix. When you open a case, you are usually charged a fee for that, but since this is a bug, you should be refunded. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp  
Other interesting topics
|
|||||||||||||||||||||||
