Monday, March 19, 2012

getting results of sp_pkeys in a recordset

Please tell me how to achieve this.

rsPrimaryKey.Open "exec sp_pkeys 'dbo.tmpQuestionnaireDumpReport'"

doesn't work

rsPrimaryKey.Open "sp_pkeys 'dbo.tmpQuestionnaireDumpReport'"

doesn't work either.

I know in Query Analyzer, sp_pkeys 'dbo.tmpQuestionnaireDumpReport'
works.

Please help.Check this one:

<% LANGUAGE="VBScript" %>
<% Response.Expires =0 %>
<HTML>
<HEAD>
<TITLE>
</TITLE>
</HEAD>
<BODY>
<%

Session("db")="DSN=homebudget;UID=sa;PWD=;"

Set Session("dbcon") = Server.CreateObject("ADODB.Connection")

Session("dbcon").Open Session("db")

set execommand = Server.CreateObject("ADODB.Command")

execommand.CommandText = "sp_pkeys"
execommand.ActiveConnection = Session("dbcon")
execommand.CommandType = &H0004

execommand.Parameters.Append execommand.CreateParameter("table",200,&H0001,8000)

execommand("table")="test"

set rs=execommand.execute

response.write rs(0)+"<br>"
response.write rs(1)

rs.close
set rs=nothing


%>

</BODY>
</HTML>|||Thank you Snail, but I tried that already. The command object doesn't work with sp_pkeys in VB. It would work with any other stored procedure that you define yourself.
This is what worked for me:

select Column_Name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
ON t.Constraint_Name = c.Constraint_Name
where t.table_name = 'lkuDataAudit'
AND t.Constraint_Type = 'PRIMARY KEY'|||Originally posted by archnam
Thank you Snail, but I tried that already. The command object doesn't work with sp_pkeys in VB. It would work with any other stored procedure that you define yourself.
This is what worked for me:

select Column_Name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS t
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
ON t.Constraint_Name = c.Constraint_Name
where t.table_name = 'lkuDataAudit'
AND t.Constraint_Type = 'PRIMARY KEY'

It works in ASP it has to work in VB (I did not test it in VB).
You have to run command as stored procedure for sp_pkeys.

No comments:

Post a Comment