Prev: Trigger Syntax
Next: ddl not working in scrip
From: Sceren on 18 Jun 2010 17:12
Hi,

I have some script where i create a temp table in sql 2k5 such as

create table #tmp
(
usrlogin varchar(10)
)

I then insert data into this temp table and run an alternate query that
deletes some of the rows...

Next i add an identity column so i can use it for a while loop as opposed to
a cursor.

alter table #tmp add row_id int identity(1,1)

The problem is it doesnt execute the ddl statement above in my script. I
know that sql server determines a query execution plan and might run some
things out of order. I tried separating with the go statement and even
created as a stored procedure and created the statement as dynamic sql and
executed it via exec sp_executesql but it still wont execute with any of
those methods.

So when it gets to the point where i use the column row_id the script bombs
out with an invalid column error.


what am i doing wrong?

Thanks,
--
-D Sceren
From: Erland Sommarskog on 18 Jun 2010 17:33
Sceren (Sceren(a)discussions.microsoft.com) writes:
> I have some script where i create a temp table in sql 2k5 such as
>
> create table #tmp
> (
> usrlogin varchar(10)
> )
>
> I then insert data into this temp table and run an alternate query that
> deletes some of the rows...
>
> Next i add an identity column so i can use it for a while loop as
> opposed to a cursor.
>
> alter table #tmp add row_id int identity(1,1)
>
> The problem is it doesnt execute the ddl statement above in my script. I
> know that sql server determines a query execution plan and might run some
> things out of order. I tried separating with the go statement and even
> created as a stored procedure and created the statement as dynamic sql and
> executed it via exec sp_executesql but it still wont execute with any of
> those methods.
>
> So when it gets to the point where i use the column row_id the script
> bombs out with an invalid column error.

Since I don't see the script, I can't say what is wrong. But I see
little reason not to use a cursor, just be sure to make it static:

DECLARE cur STATIC LOCAL FOR
SELECT usrlogin FROM #tmp

If you don't add an index on the identity column, your while loop
will be efficient at all.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Sceren on 18 Jun 2010 19:32
essentially here is the script... It creates the temp table and fills it and
deletes several users then fails with error messages on all lines that
contain row_id. When running from a query window i can then select from the
temp table and see that it did not add the column.

create table #tmpusers
(
logon_id varchar(10)
)
insert into #tmpusers
select logon_id from sales_users where isUserActive = 1

--
--delete query that removes several users baesd on certain criteria
--

alter table #tmpusers add row_id int identity(1,1)

declare @Counter int, @NumUnits int, @NUmUsers int, @logon_id varchar(10)

if (select max(row_id) from #tmpusers) > 0
Begin
set @numusers = (select max(row_id) from #tmpusers
set @Counter = 1
while @Counter < = @Numusers
Begin
set @logon_id = (select logon_id from #tmpusers where
row_id = @Counter)
set @NumUnits =
(
select 1200 - count(*) from dbo.user_sales_units
where logon_id = @logon_id
)
if @NumUnits > 0
begin
set @sqlStr =
'update user_sales_units set logon_id = ''' +
@logon_id + ''' where sales_unit_id in
(
select top ' + convert(varchar(3),
@NumUnits) + ' sales_unit_id from user_sales_units
where logon_id is null
)'
exec sp_executesql @SqlStr
End
set @Counter = @Counter + 1
End
End

--
-D Sceren


"Erland Sommarskog" wrote:

> Sceren (Sceren(a)discussions.microsoft.com) writes:
> > I have some script where i create a temp table in sql 2k5 such as
> >
> > create table #tmp
> > (
> > usrlogin varchar(10)
> > )
> >
> > I then insert data into this temp table and run an alternate query that
> > deletes some of the rows...
> >
> > Next i add an identity column so i can use it for a while loop as
> > opposed to a cursor.
> >
> > alter table #tmp add row_id int identity(1,1)
> >
> > The problem is it doesnt execute the ddl statement above in my script. I
> > know that sql server determines a query execution plan and might run some
> > things out of order. I tried separating with the go statement and even
> > created as a stored procedure and created the statement as dynamic sql and
> > executed it via exec sp_executesql but it still wont execute with any of
> > those methods.
> >
> > So when it gets to the point where i use the column row_id the script
> > bombs out with an invalid column error.
>
> Since I don't see the script, I can't say what is wrong. But I see
> little reason not to use a cursor, just be sure to make it static:
>
> DECLARE cur STATIC LOCAL FOR
> SELECT usrlogin FROM #tmp
>
> If you don't add an index on the identity column, your while loop
> will be efficient at all.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
From: Erland Sommarskog on 19 Jun 2010 06:33
Sceren (Sceren(a)discussions.microsoft.com) writes:
> essentially here is the script... It creates the temp table and fills it
> and deletes several users then fails with error messages on all lines
> that contain row_id. When running from a query window i can then select
> from the temp table and see that it did not add the column.

Since you did not post the entire script, I don't know if there is a
"go" separator somewhere, but if there is one in the parts you left
out, the batch with the ALTER statement will be compiled with the
table definition with out the column. ("go" is not a statement, it is
a separator that some client tools react on to divide the query text
in batches, and each batch is sent separately to SQL Server.)

But as I said in my previous post, there is little reason to do this
anyway. You could include the IDENTITY column in the CREATE TABLE
statement, and declare it as a primary key. Or you could run a static
cursor over the table. The current solution you have is inferior from
all points of view - the fact that it doesn't work is just one of the
problems.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Trigger Syntax
Next: ddl not working in scrip