HELP PLZ : go through the sinario and plz help
Prince P Y
Procedure is
create proc sp_GetMasterConstraints(@tabnm nvarchar(128),@flag nvarchar(128)='')
as
declare @reftabid int
declare @constid int
declare @refcolid int
declare @colid int
declare @colnm nvarchar(128)
declare @reftabnm nvarchar(128)
declare @refcolnm nvarchar(128)
declare @constnm nvarchar(128)
DECLARE @TempTable TABLE([Table Name] nvarchar(50),[Constraint Column Name] nvarchar(50),[Constraint Type] nvarchar(50),[Reference Table] nvarchar(50),[Reference Column Name] nvarchar(50),[Constraint Name] nvarchar(100))
select @flag=UPPER(@flag)
if @tabnm=''
begin
DECLARE cur_tabnm CURSOR FOR
select name from sysobjects where xtype='u'
OPEN cur_tabnm
FETCH NEXT FROM cur_tabnm into @tabnm
WHILE @@FETCH_STATUS = 0
BEGIN
begin
if @flag='PK'
begin
/*syscolumns.typestat=1 -> Not Null*/
/*syscolumns.xoffset=4 -> Primary Key*/
/*sysobjects.xtype='PK' -> Primary Key*/
if not exists(select SysObj1.name as [Table Name], SysCol.name as [Constraint Column Name], SysObj2.xtype as [Constraint Type] from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1)
begin
INSERT INTO @TempTable VALUES(@tabnm,'-','-NA-','','','')
end
else
begin
select @colnm=SysCol.name from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'PK','','','')
end end
else if @flag='FK'
begin
/*Foreign Key*/
select @constid=constid,@reftabid=rkeyid,@colid=fkey,@refcolid=rkey from sysforeignkeys where fkeyid=(select id from sysobjects where name=@tabnm)
select @reftabnm=name from sysobjects where id=@reftabid
select @constnm=name from sysobjects where id=@constid
select @colnm=name from syscolumns where id=(select id from sysobjects where name=@tabnm) and colid=@colid
select @refcolnm=name from syscolumns where id=@reftabid and colid=@refcolid
if not exists(select name from sysobjects where id=@reftabid)
begin
Select 'No Foreign Key Exist' as [Comments]
end
else
begin
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'FK', @reftabnm, @refcolnm,@constnm)
end
end
else if @flag='ALL'
begin
/*syscolumns.typestat=1 -> Not Null*/
/*syscolumns.xoffset=4 -> Primary Key*/
/*sysobjects.xtype='PK' -> Primary Key*/
if not exists(select SysObj1.name as [Table Name], SysCol.name as [Constraint Column Name], SysObj2.xtype as [Constraint Type] from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1)
begin
INSERT INTO @TempTable VALUES(@tabnm,'-','-NA-','','','')
end
else
begin
select @colnm=SysCol.name from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'PK','','','')
end
/*Foreign Key*/
select @constid=constid,@reftabid=rkeyid,@colid=fkey,@refcolid=rkey from sysforeignkeys where fkeyid=(select id from sysobjects where name=@tabnm)
select @reftabnm=name from sysobjects where id=@reftabid
select @constnm=name from sysobjects where id=@constid
select @colnm=name from syscolumns where id=(select id from sysobjects where name=@tabnm) and colid=@colid
select @refcolnm=name from syscolumns where id=@reftabid and colid=@refcolid
if not exists(select name from sysobjects where id=@reftabid)
begin
Select 'No Foreign Key Exist' as [Comments]
end
else
begin
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'FK', @reftabnm, @refcolnm,@constnm)
end
end
end
FETCH NEXT FROM cur_tabnm into @tabnm
END
CLOSE cur_tabnm
DEALLOCATE cur_tabnm
end
else if not exists(select * from sysobjects where name=@tabnm)
begin
Select 'Table Does Not Exist' as [Comments]
end
else
begin
if @flag='PK'
begin
/*syscolumns.typestat=1 -> Not Null*/
/*syscolumns.xoffset=4 -> Primary Key*/
/*sysobjects.xtype='PK' -> Primary Key*/
if not exists(select SysObj1.name as [Table Name], SysCol.name as [Constraint Column Name], SysObj2.xtype as [Constraint Type] from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1)
begin
INSERT INTO @TempTable VALUES(@tabnm,'-','-NA-','','','')
end
else
begin
select @colnm=SysCol.name from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'PK','','','')
end
end
else if @flag='FK'
begin
/*Foreign Key*/
select @constid=constid,@reftabid=rkeyid,@colid=fkey,@refcolid=rkey from sysforeignkeys where fkeyid=(select id from sysobjects where name=@tabnm)
select @reftabnm=name from sysobjects where id=@reftabid
select @constnm=name from sysobjects where id=@constid
select @colnm=name from syscolumns where id=(select id from sysobjects where name=@tabnm) and colid=@colid
select @refcolnm=name from syscolumns where id=@reftabid and colid=@refcolid
if not exists(select name from sysobjects where id=@reftabid)
begin
Select 'No Foreign Key Exist' as [Comments]
end
else
begin
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'FK', @reftabnm, @refcolnm,@constnm)
end
end
else if @flag='ALL'
begin
/*syscolumns.typestat=1 -> Not Null*/
/*syscolumns.xoffset=4 -> Primary Key*/
/*sysobjects.xtype='PK' -> Primary Key*/
if not exists(select SysObj1.name as [Table Name], SysCol.name as [Constraint Column Name], SysObj2.xtype as [Constraint Type] from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1)
begin
INSERT INTO @TempTable VALUES(@tabnm,'-','-NA-','','','')
end
else
begin
select @colnm=SysCol.name from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'PK','','','')
end
/*Foreign Key*/
select @constid=constid,@reftabid=rkeyid,@colid=fkey,@refcolid=rkey from sysforeignkeys where fkeyid=(select id from sysobjects where name=@tabnm)
select @reftabnm=name from sysobjects where id=@reftabid
select @constnm=name from sysobjects where id=@constid
select @colnm=name from syscolumns where id=(select id from sysobjects where name=@tabnm) and colid=@colid
select @refcolnm=name from syscolumns where id=@reftabid and colid=@refcolid
if not exists(select name from sysobjects where id=@reftabid)
begin
Select 'No Foreign Key Exist' as [Comments]
end
else
begin
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'FK', @reftabnm, @refcolnm,@constnm)
end
end
end
select * from @TempTable
GO
exec sp_GetMasterConstraints '','pk'
page code….
<%@ Language=VBScript %>
<%
set conn = CreateObject("ADODB.Connection")
conn.open "PROVIDER=SQLOLEDB; DATA SOURCE = .;UID=sa;PWD=sa;DATABASE=pubs"
set rs = conn.execute("exec sp_GetMasterConstraints '','pk'")
rs.
if not rs.eof then
do while not rs.eof
response.write rs(0) & ", " & rs(1) & "<br>"
rs.movenext
loop
end if
rs.close: set rs = nothing
conn.close: set conn = nothing
%>
When I run I am getting this error plz help
The page cannot be displayed |
|
|
There is a problem with the page you are trying to reach and it cannot be displayed. |
|
|
Please try the following:
HTTP 500.100 -
Internal Server Error - ASP error
|
|
Regards,
Prince P Y [B.E I.T],
S/W Engineer,
REACH Technologies,
#621, 5th Main,
OMBR Layout,
Bangalore -43
Mob: 91-944862035
Res: 080-55369104
Off: 080-56996111/2/3 - Ext: 227
Current Res Address:
24/6, 3rd Cross, Sri Lakshmi Residency,
St. Johns Orthodox Church Road,
A Narayanapura, Nagappareddy Layout,
Near K.R.Puram Railway station,
Bangalore – 16, India
murugesh
Can you try executing the stored proc in the sql queryanalyser and
check for any messages or print statements or warnings on the message
tab of the result grid. If you find any warning messages, set ansi
warnings off and give set nocount on and off. Try this and comment
print statements if any...
Regards,
T.murugesh Babu
> page code..
>
>
>
>
>
> <%@ Language=VBScript %>
>
> <%
>
> set conn = CreateObject("ADODB.Connection")
>
> conn.open "PROVIDER=SQLOLEDB; DATA SOURCE =
> .;UID=sa;PWD=sa;DATABASE=pubs"
>
> set rs = conn.execute("exec sp_GetMasterConstraints '','pk'")
>
> rs.
>
> if not rs.eof then
>
> do while not rs.eof
>
> response.write rs(0) & ", " & rs(1) & "<br>"
>
> rs.movenext
>
> loop
>
> end if
>
> rs.close: set rs = nothing
>
> conn.close: set conn = nothing
>
> %>
>
>
>
>
>
> When I run I am getting this error plz help
>
>
>
> The page cannot be displayed
>
>
>
> There is a problem with the page you are trying to reach and it cannot be
> displayed.
>
>
> _____
>
> Please try the following:
>
> * Click the <javascript:location.reload()> Refresh button, or try
> again later.
> * Open the <http://localhost> localhost home page, and then look for
> links to the information you want.
>
>
> HTTP 500.100 - Internal Server Error - ASP error
> Internet Information Services
>
> _____
>
>
> Technical Information (for support personnel)
>
> * Error Type:
> ADODB.Recordset (0x800A0E78)
> Operation is not allowed when the object is closed.
> /dvr/ASPPage1.asp, line 11
> * Browser Type:
> Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)
> * Page:
> GET /dvr/ASPPage1.asp
> * Time:
> Wednesday, November 02, 2005, 2:11:45 PM
> * More information:
>
> <http://www.microsoft.com/ContentRedirect.asp?prd=iis&sbp=&pver=5.0&ID=500;1
> 00&cat=ADODB%2ERecordset&os=&over=&hrd=&Opt1=&Opt2=%2D2146824584&Opt3=Operat
> ion+is+not+allowed+when+the+object+is+closed%2E> Microsoft Support
>
>
>
>
>
>
>
> Regards,
>
> Prince P Y [B.E I.T],
>
> S/W Engineer,
>
> REACH Technologies,
>
> #621, 5th Main,
>
> OMBR Layout,
>
> Bangalore -43
>
> www.reach-tech.com <http://www.reach-tech.com/>
>
> Mob: 91-944862035
>
> Res: 080-55369104
>
> Off: 080-56996111/2/3 - Ext: 227
>
>
>
> Current Res Address:
>
> 24/6, 3rd Cross, Sri Lakshmi Residency,
>
> St. Johns Orthodox Church Road,
>
> A Narayanapura, Nagappareddy Layout,
>
> Near K.R.Puram Railway station,
>
> Bangalore - 16, India