Some automated tools use sp_columns to find out the columns for a table, view or UDF that returns table.
In SQL Server 2000 it gives columns back but in SQL Server 2005 it does not, compat level set at 80 and 90.
Does anyone have an idea what id going on here
Repro script below. I expect the script to return information about the two columns in the table defined in fnTestColumnsFromFunctions().
if
exists (select 1from sysobjects
where id = object_id('dbo.fnTestColumnsFromFunctions')
and type in ('IF', 'FN', 'TF'))
drop function dbo.fnTestColumnsFromFunctions;
go
create function dbo.fnTestColumnsFromFunctions()
returns @TestTable table
(
ttID int,
ttName varchar(50)
)
as
begin
return;
end
go
declare
@dbname sysname;declare @n int;
set @dbname = db_name();
exec @n = dbo.sp_columns
@table_name = 'fnTestColumnsFromFunctions',
@table_owner = 'dbo',
@table_qualifier = @dbname,
@column_name = null,
@ODBCVer = 2;
if
exists (select 1from sysobjects
where id = object_id('dbo.fnTestColumnsFromFunctions')
and type in ('IF', 'FN', 'TF'))
drop function dbo.fnTestColumnsFromFunctions;
go
sp_columns in SQL2K5 gives no rows for function that return table
Arijan Luiken
Hello,
I have found a solution to this, for the moment : I create the past SQL2K procedure ...
First, i export the table "master.dbo.spt_datatype_info" from SQL2K into SQL2K5.
I use DTS to do that ...
I rename it "spt_datatype_info_2K", since a table "spt_datatype_info" still exists
(but is not visible).
Second, i take the script of the procedure "dbo.sp_columns" from SQL2K.
You have to make minor changes in this script :
* replace "master.dbo.spt_datatype_info" by "master.dbo.spt_datatype_info_2K" (evident ... ;-) )
* delete the line IS_NULLABLE in the "selects" (it makes an error on the substring,
no matter what it is!)
* and change its name !!
Then, you just have to run this script into master, and grant this procedure the execute rights (to public)....
THAT'S ALL
Dam's.
PS :
Xcuse for my poor english.
Crooks_K
MCJJ
You can also take a look at the INFORMATION_SCHEMA.ROUTINE_COLUMNS View (I am not sure that you can modify your code)
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,ORDINAL_POSITION
FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS
WHERE TABLE_NAME ='fnTestColumnsFromFunctions'
ORDER BY ORDINAL_POSITION
Denis the SQL Menace
http://sqlservercode.blogspot.com/Ray Hayes
Rennie
As you noticed, it is not permissions. I was also running as sa.
I have submitted it to product feedback center.
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx feedbackid=bf2623a6-90f3-47c4-a0b6-90187258ade6
W_F
What context are you executing the script in If it is a low privileged user then it might be because in sql server 2005 metadata visibility is restricted only to users with some permission on the object.
Take a look at
http://msdn2.microsoft.com/en-us/library/ms187113.aspx
for more information.