May 13, 2015 at 6:03 am
Hi All,
I have a UDF with a select * that works fine in one region (DEV) but not another (QC). It's not returning the last 2 columns from the table in QC.
I looked at the UDF and it does a fairly simple select:
select a.*
from myTable A
The table is the same in both regions and I did a sp_help on the table to ensure these 2 columns are listed. They are. Also, executing a select * in a query windows does return the final 2 columns from the table in QC. The issue resides in the QC version of the UDF only.
The UDF has already been updated to retrieve all columns by name but I'm curious why this would happen. For some reason I'd just like to know and in case it happens again.
TIA,
Mark
May 13, 2015 at 2:41 pm
Drop and recreate the UDF?
Worked out an example that I believe mimics your scenario...would appear that the columns that satisfy * get "saved" to sys.columns when UDF created.
USE tempdb;
CREATE TABLE dbo.TempTbl (
RowId INT IDENTITY(1,1)
,SomeValue VARCHAR(10)
,SomeValue2 VARCHAR(10)
,SomeValue3 VARCHAR(10)
);
GO
INSERT INTO dbo.TempTbl (SomeValue,SomeValue2,SomeValue3)
VALUES ('a','a','a'),('b','b','b'),('c','c','c');
GO
CREATE FUNCTION dbo.FN_TempTbl ()
RETURNS TABLE
AS
RETURN(
SELECT*
FROMdbo.TempTbl
);
GO
SELECT*
FROMdbo.FN_TempTbl();
GO
ALTER TABLE dbo.TempTbl ADD
SomeValue4 VARCHAR(4);
GO
SELECT*
FROMdbo.FN_TempTbl();
GO
SELECT*
FROMdbo.TempTbl;
GO
SELECT*
FROMsys.objects o
INNER JOIN sys.columns c ON c.object_id = o.object_id
WHEREtype_desc = 'SQL_INLINE_TABLE_VALUED_FUNCTION';
GO
DROP FUNCTION dbo.FN_TempTbl;
DROP TABLE dbo.TempTbl;
GO
_____________________________________________________________________
- Nate
May 13, 2015 at 2:45 pm
Before that you may want to try:
EXEC sp_refreshview
on that view.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 14, 2015 at 5:44 am
I don't have direct access to this server but I'll pass this on to the DBA and ask him to look in to it.
Thanks for the suggestions!
Mark
June 18, 2015 at 9:57 am
Echoing what Nate said above, this also happens if you create a VIEW as SELECT * FROM MyTable and then later add columns to MyTable: the VIEW will not return those additional columns. You have to drop and re-create the view. Another good reason not to use SELECT *.
When I first encountered this years ago, interestingly the best explanation I found was on a Sybase web page, now 404. But here's the meat of it:
"However, if you alter the structure of a view's underlying table by adding columns, the new columns will not appear in a view that is defined with a select * clause unless the view is dropped and redefined. This is because the asterisk in the original view definition considers only the original columns. "
Rich
June 18, 2015 at 12:14 pm
ScottPletcher (5/13/2015)
Before that you may want to try:EXEC sp_refreshview
on that view.
You mean sp_refreshsqlmodule because it's a UDF. 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply