SQL procedure Error - Msg 207, Level 16, State 1

>Invalid column name 'intauto'.
>Any ideas why this may be happening?

Please post the T-SQL that generated this error, with emphasis on which line is line 111.

intauto may be a user defined datatype in your SQL Server 2008 version, which is missing a create script .....
and that is the reason why you are getting that error.
http://technet.microsoft.com/en-us/library/ms175007.aspx

hmmm, my first "guess" would be that the db is case sensitive, and hence "intauto" <> "intAuto"
second "guess" would be some difference with the way sql sees columns from subqueries, but for that, I agree with Jim Horn, that we need to see the actual code

here is an example

error

Msg 207, Level 16, State 1, Procedure RateAvg, Line 21
Invalid column name 'newavgcostrate'.
Msg 207, Level 16, State 1, Procedure RateAvg, Line 21
Invalid column name 'intauto'.
Msg 207, Level 16, State 1, Procedure RateAvg, Line 26
Invalid column name 'intauto'.
Msg 207, Level 16, State 1, Procedure RateAvg, Line 32
Invalid column name 'txtcurrency'.

sql

/****** Object:  StoredProcedure [dbo].[RateAvg]    Script Date: 02/05/2014 07:57:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Rene D Lashley>

-- =============================================
CREATE procedure[dbo].[RateAvg]
@currency nvarchar(10)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;


declare @startingrate float
declare @newavgcostrate as float
set @startingrate =(select round(isnull([startingavgRate],0),6) FROM [TestDB].[dbo].[tblAvgStartingRate] with(nolock))

set @newavgcostrate=isnull((
SELECT round(newavgcostrate,6) as newavgcostrate
FROM (
select txtCurrency , datTransaction,newavgcostrate, intauto
FROM [TestDB].[dbo].tblTradingPosition b with(nolock)
) b
inner join (
select MAX(intauto) as intauto from (
select MAX(datTransaction) as dat , intauto
FROM [TestDB].[dbo].tblTradingPosition with(nolock)
where curTradingPosition is not null
group by intAuto )aa
) a
on a.intauto=b.intauto
where b.txtcurrency = @currency),@startingrate)

select @newavgcostrate as newavgcostrate


                        
END
GO

>set @newavgcostrate=isnull((

For starters, that's a wompload of T-SQL that is setting a scalar variable, so if your T-SQL returns more than one row this will throw an error.

can you also post the create table script for the table tblTradingPosition

can you ensure that the column intauto is created in 2005 version or not?

I should never return more than one record because the intauto in a system generated autonumber field and I am always pulling the largest number. with the max function.

this script works in the 2008 sql database is there some issue with running it in 2005?

did you check the case sensitivity suggestion?

sql to create the table

/****** Object:  Table [dbo].[tblTradingPosition]    Script Date: 02/05/2014 07:57:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTradingPosition](
	[intAuto] [int] IDENTITY(1,1) NOT NULL,
	[txtTransRef] [nvarchar](50) NULL,
	[datTransaction] [datetime] NULL,
	[txtAction] [nvarchar](50) NULL,
	[txtCurrency] [nvarchar](10) NULL,
	[curAmtBuy] [money] NULL,
	[curAmtSell] [money] NULL,
	[intRate] [float] NULL,
	[oldavgcostRate] [float] NULL,
	[newavgcostRate] [float] NULL,
	[txtConvertto] [nvarchar](10) NULL,
	[curConversion] [money] NULL,
	[curConversionatavgrate] [money] NULL,
	[midrate] [float] NULL,
	[curProfitBuy] [money] NULL,
	[curProfitSell] [money] NULL,
	[curTradingPosition] [money] NULL,
	[curTradingPositionConverted] [money] NULL,
	[curTradingPositionConvertedatavg] [money] NULL,
	[curProfitSellatAvg] [money] NULL,
	[txtverifiedby] [nvarchar](100) NULL,
	[intverifiedby] [int] NULL,
	[txtapprovedby] [nvarchar](100) NULL,
	[intapprovedby] [int] NULL,
	[curFee] [money] NULL,
 CONSTRAINT [PK_tblTradingPosition] PRIMARY KEY CLUSTERED 
(
	[intAuto] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblTradingPosition] ON [dbo].[tblTradingPosition] 
(
	[datTransaction] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Select allOpen in new window

I will not be able to check until this evening but if it is a sensitivity issue i have a ton of procedures to update.  

is it that SQL 2008 ignores the sensitivity?

>is it that SQL 2008 ignores the sensitivity?
no, this is not a version thing, but a configuration of the instance ... in regards to object names
changing that one will not be simple:
http://msdn.microsoft.com/en-us/library/ms179254.aspx

Yes, SQL Server ignores sensitivity if the collation is SQL_Latin1_General_CP1_CI_AS, but it will not ignore the case sensitivity if the collation is SQL_Latin1_General_CP1_CS_AS

note on the above: changing the collation on user database will only have an effect on the tables data, not on the catalog. only the master db case sensitivity will handle that one.

are you saying that I have to change the case sensitivity on the master in order for the scripts to work?

when i try to code above to change the collation I get the following error

ALTER DATABASE 'testdb'
COLLATE 'SQL_Latin1_General_CP1_CS_AS'

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'testdb'

am i scripting it incorrect.

also will changing the collation at the database level solve my problem or is it as Guy was saying at the master db level?

it would be without the quotes:

ALTER DATABASE [testdb] COLLATE SQL_Latin1_General_CP1_CS_AS

Select allOpen in new window

but, before going that way, can you first test/confirm that this IS actually the issue, by changing the script:
select MAX(intauto) as intauto from (
to
select MAX(intAuto) as intAuto from (

and see if the procedure compiles with at least 1 error less?
if that is indeed the case, you will have to change the collation

I was able to attempt the suggestions and it is a collation error.  I changed the collation on my 2008 sql server to match to 2005 server and I received the same error I initially got when it was loaded to the 2005 SQL server.

I then attempted to created a new database on the 2005 sql server and specified the case insensitive collation -  SQL_Latin1_General_CP1_CI_AS now I am getting a different error

see error and code below

Msg 137, Level 15, State 2, Procedure AccountLedgerOutgoingBalancingEntry, Line 30
Must declare the scalar variable "@datdate".
Msg 319, Level 15, State 1, Procedure AccountLedgerOutgoingBalancingEntry, Line 32
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Procedure AccountLedgerOutgoingBalancingEntry, Line 33
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Procedure AccountLedgerOutgoingBalancingEntry, Line 42
Incorrect syntax near 'c'.

CREATE procedure[dbo].[AccountLedgerOutgoingBalancingEntry] 
@txttransref nvarchar(20),
@datDate datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;





declare @baseaccountcurrency nvarchar(50)
declare @feesaccountnumber nvarchar(50)
declare @feesaccountname nvarchar(50)

set @feesaccountnumber = (select txtFeeAccount FROM TESTDB.[dbo].[tblDefault] with(nolock))
set @baseaccountcurrency = (select [txtBaseCurrency] FROM TESTDB.[dbo].[tblDefault] with(nolock))
set @feesaccountname= (selECT [txtChartName] FROM TESTDB.[dbo].[tblChartofAccounts]with(nolock) where intChartNum=@feesaccountnumber )


insert into TESTDB.[dbo].[tblLedger](transMethod,intRefAuto,txtTransactionCurrency,txtAccountCurrency,txtTypeofForm,txtcustnum,ord,[datDate],[AccountNumber],[AccountName],debittrans, [Debit] , credittrans,credit ,[Memo],txtTransref,currate,txtArrangedby,txtVerifiedby,txtApprovedby)


select '', 0, '', @baseaccountcurrency , 'Confirmation', a.txtcustnum, 5,@datdate, @feesaccountnumber, @feesaccountname, null,null, null,  c.BalanceEntry ,  'Balancing Entry ',@txttransref,a.curRate
,a.txtArrangedby,a.txtVerifiedby,a.txtApprovedby
 from TESTDB.[dbo].[tblTradingPosition] b with(nolock)
   inner join TESTDB.[dbo].[tblIPC] a with(nolock)
   
   inner join --balancing entry
   (    select a.txttransref,round(sum(credit),2) as tc, b.curtotal, (b.curtotal-  (round(sum(credit),2))) as BalanceEntry
    FROM TESTDB.[dbo].[tblLedger] a with(nolock)
    inner join TESTDB.[dbo].[tblIPC] b with(nolock)
    on a.txttransref= b.txttransref
  where  a.txttransref=@txttransref
  group by  b.curtotal ,  a.txttransref 
  having (b.curtotal-  (round(sum(credit),2))) > 0) c
  
  on c.txttransref = a.txttransref
  
  
 on b.txtTransref = a.txtTransref
  where b.txtTransRef=@txttransref
  

  
END
GO

Select allOpen in new window

Part of the issue is still due to the collation issue...

@datDate vs @datdate --- SQL is treating both of them differently can you check your server collation and change it as well.

I agree the server setting will be easy to set but the problem is that the server currently host a number of other databases and the Admin is not too keen on making server wide changes to accomodate my DB.

I can easily do a find and replace to make all of the variables the same case but and that will take care of the Msg 137, Level 15, State 2

but how do i handle the Msg 319, Level 15, State 1, which is being thrown because i have
inner join TESTDB.[dbo].[tblIPC] a with(nolock)

at the end of my table reference

and i am not sure what is causing
Msg 102, Level 15, State 1, Procedure AccountLedgerOutgoingBalancingEntry, Line 42
Incorrect syntax near 'c'.

ok will try it tonight and see how it goes.

thanks guys got it too work.