Solved

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

Posted on 2014-02-05
24
2,978 Views
Last Modified: 2014-02-08
Good Day,

I have a SQL database on a SQL 2008 server that I want to transfer to a SQL 2005 server.

I generated two scripts to create the tables with data and the stored procedures separately.

I ran the two scripts on the 2005 SQL server and the tables procedure ran successfully creating all the tables, but the stored procedure query ran with errors and example of one is as follows

Msg 207, Level 16, State 1, Procedure IPClstClient, Line 111
Invalid column name 'intauto'.

but intAuto does exist in the physical table when i look at it.

I ran the two procedures on another 2008 SQL server and they ran successfully so i know the procedures work.

Any ideas why this may be happening?
0
Comment
Question by:RDLFC
  • 11
  • 6
  • 5
  • +1
24 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39835719
>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.
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39835729
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39835746
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
0
 

Author Comment

by:RDLFC
ID: 39835810
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
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39835827
>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.
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39835837
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?
0
 

Author Comment

by:RDLFC
ID: 39835843
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?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39835865
did you check the case sensitivity suggestion?
0
 

Author Comment

by:RDLFC
ID: 39835871
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

Open in new window

0
 

Author Comment

by:RDLFC
ID: 39835885
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?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39835916
>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
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 250 total points
ID: 39835918
I think this can be a collation problem between your old and new databases...
Can you check the colaltion in your 2005 one vs 2008

you can do that by the below sql statement

SELECT name, collation_name
FROM sys.databases
WHERE name = '<your database name>';

Open in new window


and change the collation of what 2008 has in 2005 you can do that as below

ALTER DATABASE <your Database Name>
COLLATE <2008 collation>;

Open in new window

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39835925
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39835945
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.
0
 

Author Comment

by:RDLFC
ID: 39835965
are you saying that I have to change the case sensitivity on the master in order for the scripts to work?
0
 

Author Comment

by:RDLFC
ID: 39836518
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?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39836586
it would be without the quotes:
ALTER DATABASE [testdb] COLLATE SQL_Latin1_General_CP1_CS_AS

Open 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
0
 

Author Comment

by:RDLFC
ID: 39839119
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'.
0
 

Author Comment

by:RDLFC
ID: 39839121
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

Open in new window

0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39839566
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.
0
 

Author Comment

by:RDLFC
ID: 39840002
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'.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39840172
remove WITH from WITH (NOLOCK)

and the other error is about a syntax not supported in the SQL 2005, but which works in SQL 2008. in short:
FROM table a JOIN table b JOIN table c on c.id = b.id ON b.id = a.Id seems to work in sql 2008, but I would change it like this so it works on both versions
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 (nolock)
   inner join TESTDB.[dbo].[tblIPC] a  (nolock)
  on b.txtTransref = a.txtTransref
  
   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 (nolock)
    inner join TESTDB.[dbo].[tblIPC] b (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
  
   where b.txtTransRef=@txttransref
  

  
END
GO
                                            

Open in new window

0
 

Author Comment

by:RDLFC
ID: 39840184
ok will try it tonight and see how it goes.
0
 

Author Closing Comment

by:RDLFC
ID: 39844029
thanks guys got it too work.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now