funcion that works in sql 2000 do not work more under sql 2014

Hi all
a funtion do not work any more

USE [1010]
GO
/****** Object:  UserDefinedFunction [dbo].[GetSpecs]    Script Date: 28/10/2015 04:24:19 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[GetSpecs] ( @CODE varchar(12), @Descripcion varchar(3000) )
returns varchar(3000)
as
begin
      declare @specs varchar(100)
       select @specs = ''
      Select @specs = @specs + SPEC + ','
      From valvulas_paso
      where CODE = @CODE
      and DESCRIPCION = @descripcion
     
      if @specs <> ''
       Select @specs = left(@specs, len(@specs)-1)
      return @specs
end

any advice?
ErnestoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Post in this question the variable values used to execute this question, the return value, and any error message(s) returned.

'It doesn't work anymore' is not the most actionable question.
ErnestoAuthor Commented:
ok
the query that use that function is

insert into valvulas_paso_tmp select CODE,DESCRIPCION,dbo.GetSpecs(CODE,Descripcion),REV='0',IDENT='0'
         from valvulas_paso
        group by CODE, DESCRIPCION ORDER BY CODE

and have an error that is
Column name or number of supplied values does not match table definition.

in sql 2000 works perfectly
regards
Scott PletcherSenior DBACommented:
The error is because of the structure of table "valvulas_paso_tmp", not because of the function.

You're inserting 5 columns into that table, but the error is saying that SQL expects a different number of columns / column values.

Something in the table definition or structure must have changed.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ErnestoAuthor Commented:
the structure is the same
im going to check again
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
quick guess...

insert into valvulas_paso_tmp
select distinct CODE, dbo.GetSpecs(CODE,Descripcion),REV='0',IDENT='0'
         from valvulas_paso 
        ORDER BY CODE

Open in new window


Assuming the target table: valvulas_paso_tmp has 4 columns, which represent fields of: CODE, Specs, Rev and Ident.

or:
insert into valvulas_paso_tmp
select distinct CODE, DESCRIPCION,REV='0',IDENT='0'
         from valvulas_paso 
        ORDER BY CODE

Open in new window


Assuming the target table: valvulas_paso_tmp has 4 columns, which represent fields of: CODE, Descripcion, Rev and Ident.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
How about enumerating the column names if the target table?
insert into valvulas_paso_tmp (code,  descripcion, spec, rev, ident) 
select... 

Open in new window

ErnestoAuthor Commented:
The tables are the same the query works in sql 2000
mmm
any advice
ErnestoAuthor Commented:
i need to use the function
that is the one that gave me the required functionality for the query
regards
ErnestoAuthor Commented:
the upper/lower afects in the sintax now in sql?
or what happend you experts, help!!
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You didn't follow our advice, or didn't tell us you did. In particular http:#a41141224 should help, if we are correct about the field sequence / field names.
ErnestoAuthor Commented:
put all the query, i dont know what you mean
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
insert into valvulas_paso_tmp
      (code, descripcion, spec                          , rev    , ident) 
select CODE, DESCRIPCION, dbo.GetSpecs(CODE,Descripcion), REV='0', IDENT='0'
         from valvulas_paso 
        group by CODE, DESCRIPCION ORDER BY CODE

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ErnestoAuthor Commented:
oh,man!
you r the man!!!!
tsm
regards!!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.