Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3392
  • Last Modified:

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

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
RDLFC
Asked:
RDLFC
  • 11
  • 6
  • 5
  • +1
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Surendra NathCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
RDLFCAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Surendra NathCommented:
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
 
RDLFCAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you check the case sensitivity suggestion?
0
 
RDLFCAuthor Commented:
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
 
RDLFCAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Surendra NathCommented:
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
 
Surendra NathCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
RDLFCAuthor Commented:
are you saying that I have to change the case sensitivity on the master in order for the scripts to work?
0
 
RDLFCAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
RDLFCAuthor Commented:
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
 
RDLFCAuthor Commented:
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
 
Surendra NathCommented:
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
 
RDLFCAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
RDLFCAuthor Commented:
ok will try it tonight and see how it goes.
0
 
RDLFCAuthor Commented:
thanks guys got it too work.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now