SQL concurrency problem .. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL concurrency problem ..

Author

Topic

asood
Starting Member

4 Posts

Posted - 2004-09-23 : 10:56:22

I have a stored procedure in which i am putting some data in temporary table. Then adding a identity column to that table so that I get a unique column for each row. And then I am bring some data from the table using that Identity column and based on some search criteria. All this is done by creating a Sql string and then executing it in the end.

The only problem is, it gives error when executed and the error is Invalid ID column (ID is the identity column I am adding using Alter Table Command). I think what's happening is before the table is completely altered, its trying to execute the final query which uses that column and hence it generates the invalid column error. Is this what it is or something else ? If it is, then how can i stop sql to run individual statements separately. I think sql is trying to run them concurrently.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-23 : 11:02:12

you are adding the ID column to the temporary table, after it is created? Why not just create the column along with the table in the first place?

if you are using SELECT INTO you can use the IDENTITY() function to create this column for you.

SELECT A.*, IDENTITY(1,1) as ID
INTO #Temp
FROM YourTable A

- Jeff

asood
Starting Member

4 Posts

Posted - 2004-09-23 : 11:31:37

I know that and that is what I was doing initially. But I had to change that for a reason. That reason was, when I was building the temporary table, it was building it dynamically based on some fields, whereClause and order By clause. Now the whereClause has some Distinct keyword in it and if I take identity column at the time of building the table it will not bring correct data because identity column is always distinct. So I had to add it afterwards using Alter Table command. That way I get correct data as well as Identity column.

asood
Starting Member

4 Posts

Posted - 2004-09-25 : 10:13:09

please anybody ?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-25 : 10:58:52

you gotta give us more information and show us exactly what you are doing.

- Jeff

asood
Starting Member

4 Posts

Posted - 2004-09-26 : 11:54:52

quote:
Originally posted by jsmith8858

you gotta give us more information and show us exactly what you are doing.

- Jeff

Here's the script which should give you the idea of what I said in my first post.

CREATE PROCEDURE dbo.CRM_SP_Data_Paging
(
@Page int,
@PageSize int,
@Fields varchar(3000),
@Table varchar(1000),
@Where varchar(8000)='',
@OrderBy varchar(100)=''
)
AS
Set NoCount On

Declare @ValidTable int
Declare @CharPosition int
Declare @PreviousRows int
Declare @RowsReviewed int

Declare @strSQL varchar(8000)
Declare @Identity_Column varchar(100)

Set @RowsReviewed = @PageSize * @page
Set @PreviousRows = @RowsReviewed - @PageSize

Set RowCount @RowsReviewed
Set @strSQL = ''

If(@Where<>'')
Begin
Set @strSQL=@strSQL+Char(13)+ ' Select ' + @Fields -- + ',(Select count(*) From ' + @Table + ' WHERE ' + @Where + ') as Paging_Rec_Cnt '
-- Set @strSQL=@strSQL+Char(13)+ ' IDENTITY(int, 1,1) AS Paging_ID_Col '
Set @strSQL=@strSQL+Char(13)+ ' INTO #tempPaging From ' + @table
Set @strSQL=@strSQL+Char(13)+ ' WHERE ' + @Where
End
Else
Begin
Set @strSQL=@strSQL+Char(13)+ ' Select ' + @Fields -- + ',(Select count(*) From ' + @Table + ') as Paging_Rec_Cnt '
-- Set @strSQL=@strSQL+Char(13)+ ' IDENTITY(int, 1,1) AS Paging_ID_Col '
Set @strSQL=@strSQL+Char(13)+ ' INTO #tempPaging From ' + @table
End

If(@OrderBy<>'')
Begin
Set @strSQL=@strSQL + Char(13) + 'Order By ' + @OrderBy
End

Set @strSQL=@strSQL + Char(13) + ' Select @@ROWCOUNT As Paging_Rec_Cnt , Identity(int,1,1) Paging_ID_Col, * Into #tempPaging1 From #tempPaging '

Set RowCount 0

If (@RowsReviewed = @PageSize)
Begin
Set @strSQL=@strSQL + Char(13) + ' Select Top ' + CONVERT(Varchar, @RowsReviewed) + ' * '
Set @strSQL = @strSQL + 'From #tempPaging1 '
End
Else

Begin
Set @strSQL=@strSQL + Char(13) + ' Select Top ' + CONVERT(Varchar, @PageSize) + ' * '
Set @strSQL = @strSQL + 'From #tempPaging1 '
Set @strSQL = @strSQL + 'Where Paging_ID_Col In '
Set @strSQL = @strSQL + '(Select Top ' + CONVERT(Varchar, @RowsReviewed) + ' Paging_ID_Col '
Set @strSQL = @strSQL + 'From #tempPaging1 ) '
Set @strSQL = @strSQL + 'And Paging_ID_Col Not In '
Set @strSQL = @strSQL + '(Select Top ' + CONVERT(Varchar, @PreviousRows) + ' Paging_ID_Col '
Set @strSQL = @strSQL + 'From #tempPaging1 ) '

End

Set @strSQL = @strSQL + Char(13) + ' Drop Table #tempPaging1 '

Exec (@strSQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-26 : 13:46:30

ok, so you are saying you are passing 'distinct f1,f2' as a parameter, and the distinct is causing problems?

try building your dynamic sql statement in this format instead to avoid that issue:

select a.*, identity(1,1) as ID from (select <fields> from <table> where <where>) a order by <order clause>

your DISTINCT will now work fine. by the way -- you are better off accepting a SQL statement as a parameter instead of tablename and fieldnames, it will be much more flexible. then you can build your SQL like this:

select a.*, identity(1,1) as ID from (<sql statement>) a order by <order clause>

Does this help?

- Jeff

Filia
Starting Member

3 Posts

Posted - 2004-10-20 : 03:57:13

Hi,

I am having EXACTLY the same problem, only my sp is not as complex. I am attaching a condensed version of it for anyone who wants to try it. When I try and execute, the error of invalid column name appears...WHY?? And can I overcome it somehow?

create table #Val
(intColumn int,
strColumn varchar(100))

insert into #Val
values(1,'Col1')

declare @strSQL varchar(4000), @strColumn varchar(100)
set @strColumn = 'Col2'

set @strSQL = 'alter table #Val add [' + @strColumn + '] int
update #Val set [' + @strColumn + '] = 10'

exec (@strSQL)
select * from #Val

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-20 : 08:59:28

do not dynamically create columns in your database. if you need to store additional data for a particular table, it should be structured so that you add ROWS and never columns.

- Jeff

Filia
Starting Member

3 Posts

Posted - 2004-10-20 : 09:07:20

What is the reason that columns shouldn't be added dynamically?

I have nevertheless solved the problem and got the above to work by replacing the last select statement with:

Set @strSQL = 'select * from #Val'
exec (@strSQL)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-20 : 09:10:56

read up on database design and normalization. do some google searches.

changing the underlying table structure in your application is not something that should be done dynamically or lightly. Your tables should be structured so that if you need to store more data, you add rows, never columns.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources