Help with sp code - cursor

Friends,
When I step through the following code in QA it works fine - when I alter
the proc and run it, I get an error (you should be able to copy the code as
it is into your own QA for testing) - Anybody know where I am making the
mistke? Thanks in advance for your help ... Bill Morgan
create proc Tester
as
/* this is test code that creates a table and then alters that table
to add columns that are the required USA states - it then populates
the date column and updates
one of the state columns*/
set nocount on
DECLARE @.sql nvarchar(4000),
@.state varchar(10),
@.dater smalldatetime
set @.sql = 'alter table #main '
If object_id('tempdb..#states') is not null
begin
drop table #states
end
If object_id('tempdb..#main') is not null
begin
drop table #main
end
create table #states
(state varchar(5) null)
Create Table #main
(Dates smalldatetime null)
insert into #states values ('CA')
insert into #states values ('MN')
insert into #states values ('ND')
insert into #states values ('NJ')
insert into #states values ('NY')
insert into #states values ('TX')
insert into #states values ('IL')
insert into #states values ('IA')
insert into #states values ('WY')
insert into #states values ('FL')
DECLARE mycursor CURSOR
FOR
SELECT state
FROM #states
begin tran
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.state
WHILE @.@.fetch_status = 0
BEGIN
set @.sql = 'alter table #main '
set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
exec sp_executesql @.sql
FETCH NEXT
FROM mycursor
INTO @.state
END
CLOSE mycursor
DEALLOCATE mycursor
set @.dater = getdate()
while @.dater < getdate() + 365
begin
insert into #main (Dates)
values
(@.dater)
set @.dater = @.dater + 1
end
update #main
set ca = 'a'
select * from #main
set nocount off
returnYour table #main doesn't have a column "ca" in it, just a column "Dates":

> update #main
> set ca = 'a'
"bill_morgan" <bill_morgan@.discussions.microsoft.com> wrote in message
news:7F945501-89B0-4B6C-81BC-4767C92B0464@.microsoft.com...
> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code
> as
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Look at where you create the #Main table, there is no "ca" column defined in
it, only a "dates" column... Therefore, later on where you try to update the
'ca' column, it fails... Can;t begin to suggest a fix until I know what
Stored Proc is SUpposed t odo...
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Sorry, Now I see what you're doing...
Wat's wrong is that You have an Open uncommitted transaction
just delete the Begin Tran line and try it again... If you need the tran,
then you have to put in a corresponding Commit tran...
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Here is the explanation of what is happening.
http://groups.google.ca/groups?selm...FTNGP11.phx.gbl
Also, you have a begin transaction without a matching commit / rollback.
Example:
-- I commented the begin transaction
use northwind
go
create proc Tester
as
/* this is test code that creates a table and then alters that table
to add columns that are the required USA states - it then populates
the date column and updates
one of the state columns*/
set nocount on
DECLARE @.sql nvarchar(4000),
@.state varchar(10),
@.dater smalldatetime
set @.sql = 'alter table #main '
If object_id('tempdb..#states') is not null
begin
drop table #states
end
If object_id('tempdb..#main') is not null
begin
drop table #main
end
create table #states
(state varchar(5) null)
Create Table #main
(Dates smalldatetime null)
insert into #states values ('CA')
insert into #states values ('MN')
insert into #states values ('ND')
insert into #states values ('NJ')
insert into #states values ('NY')
insert into #states values ('TX')
insert into #states values ('IL')
insert into #states values ('IA')
insert into #states values ('WY')
insert into #states values ('FL')
DECLARE mycursor CURSOR
FOR
SELECT state
FROM #states
--begin tran
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.state
WHILE @.@.fetch_status = 0
BEGIN
set @.sql = 'alter table #main '
set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
exec sp_executesql @.sql
FETCH NEXT
FROM mycursor
INTO @.state
END
CLOSE mycursor
DEALLOCATE mycursor
set @.dater = getdate()
while @.dater < getdate() + 365
begin
insert into #main (Dates)
values (@.dater)
set @.dater = @.dater + 1
end
exec ('update #main set ca = ''a''')
select * from #main
set nocount off
return
go
exec tester
go
drop procedure tester
go
AMB
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||Bill, What is going on is that SQL7/2000 has what is called delayed
verification o(or something like that) which basically does NOT check the
column names of tables whoch ddo not currently exist when you create the
Stored Proc. It waits until run time... Then it checks again, BEFORE The
stored Proc runs, to make sure that every column and table exists...
So what's going on here is that the compiler sees that you're going to
create the #Main table, and that it will have a column named 'dates', but it
doesn't (no way it can) see that you're going to alter the table and add all
those state name columns, so the Update #Main Set CA = 'a' line fails the
compiler test...
If you comment that line out, (and fx the Open Transaction issue), the code
will work.
"bill_morgan" wrote:

> Friends,
> When I step through the following code in QA it works fine - when I alter
> the proc and run it, I get an error (you should be able to copy the code a
s
> it is into your own QA for testing) - Anybody know where I am making the
> mistke? Thanks in advance for your help ... Bill Morgan
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values
> (@.dater)
> set @.dater = @.dater + 1
> end
> update #main
> set ca = 'a'
> select * from #main
> set nocount off
> return
>
>|||my apologies for the begin tran statement - i was monkeying with the
procedure and forgot to take that out - once it's removed you can step
through the procedure, but trying to run it all at once fails ...
"CBretana" wrote:
> Sorry, Now I see what you're doing...
> Wat's wrong is that You have an Open uncommitted transaction
> just delete the Begin Tran line and try it again... If you need the tran,
> then you have to put in a corresponding Commit tran...
> "bill_morgan" wrote:
>|||Thanks for the guidance ... my apologies for the begin tran - i forgot to
take that out before I posted this question (I thought the begin tran /
commit tran) might fix things ... I am visiting the sight you suggest ...
thanks ...
"Alejandro Mesa" wrote:
> Here is the explanation of what is happening.
> [url]http://groups.google.ca/groups?selm=uxV68C33DHA.3468%40TK2MSFTNGP11.phx.gbl[/url
]
> Also, you have a begin transaction without a matching commit / rollback.
> Example:
> -- I commented the begin transaction
> use northwind
> go
> create proc Tester
> as
> /* this is test code that creates a table and then alters that table
> to add columns that are the required USA states - it then populates
> the date column and updates
> one of the state columns*/
> set nocount on
> DECLARE @.sql nvarchar(4000),
> @.state varchar(10),
> @.dater smalldatetime
> set @.sql = 'alter table #main '
> If object_id('tempdb..#states') is not null
> begin
> drop table #states
> end
> If object_id('tempdb..#main') is not null
> begin
> drop table #main
> end
> create table #states
> (state varchar(5) null)
> Create Table #main
> (Dates smalldatetime null)
> insert into #states values ('CA')
> insert into #states values ('MN')
> insert into #states values ('ND')
> insert into #states values ('NJ')
> insert into #states values ('NY')
> insert into #states values ('TX')
> insert into #states values ('IL')
> insert into #states values ('IA')
> insert into #states values ('WY')
> insert into #states values ('FL')
> DECLARE mycursor CURSOR
> FOR
> SELECT state
> FROM #states
> --begin tran
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.state
> WHILE @.@.fetch_status = 0
> BEGIN
> set @.sql = 'alter table #main '
> set @.sql = @.sql + 'add ['+ @.state +'] varchar(10) null'
> exec sp_executesql @.sql
> FETCH NEXT
> FROM mycursor
> INTO @.state
> END
> CLOSE mycursor
> DEALLOCATE mycursor
> set @.dater = getdate()
> while @.dater < getdate() + 365
> begin
> insert into #main (Dates)
> values (@.dater)
> set @.dater = @.dater + 1
> end
> exec ('update #main set ca = ''a''')
> select * from #main
> set nocount off
> return
> go
> exec tester
> go
> drop procedure tester
> go
>
> AMB
>
> "bill_morgan" wrote:
>|||Yes ..!! I created a new proc to handle that final update ... Proc 1 calls
Proc 2 and it works great ... thank you for the new knowledge ...
"CBretana" wrote:
> Bill, What is going on is that SQL7/2000 has what is called delayed
> verification o(or something like that) which basically does NOT check the
> column names of tables whoch ddo not currently exist when you create the
> Stored Proc. It waits until run time... Then it checks again, BEFORE The
> stored Proc runs, to make sure that every column and table exists...
> So what's going on here is that the compiler sees that you're going to
> create the #Main table, and that it will have a column named 'dates', but
it
> doesn't (no way it can) see that you're going to alter the table and add a
ll
> those state name columns, so the Update #Main Set CA = 'a' line fails the
> compiler test...
> If you comment that line out, (and fx the Open Transaction issue), the cod
e
> will work.
> "bill_morgan" wrote:
>sql

Help with rewriting code without cursor

Hello,
Just wondering if anyone can tell me the best way to rewrite the below code
without a cursor.
It's just passing each Id to a stored procedure.
Let me know if you need any more info.
Thanks & go easy on me, I know cursors tend to rile everyone up.
Declare cur_DeleteStuff Cursor Scroll For
Select distinct TableID
from tbl_DTM
Where APID IN
(Select TableID from tbl_DTM where
supplierID = @.v_FromSupplierID)
Open cur_DeleteStuff
Fetch First FROM cur_DeleteStiff into @.ChildTableID
While (@.@.Fetch_Status <> -1)
Begin
exec sp_SMART_ANADeleteFrom @.ChildTableID, 1, 0
If @.@.Error <> 0
BEGIN
ROLLBACK Transaction CDTTransfer
RAISERROR('Something Bad Happened, Updates ROLLED BACK!',1,1)
RETURN
END
Fetch Next FROM cur_Deletestuff into @.ChildTableID
END
Close cur_DeleteStuff
Deallocate cur_DeleteStuff"Lesley" <Lesley@.discussions.microsoft.com> wrote in message
news:F2C468A7-7573-4FE4-8FDC-D8D75D2AE374@.microsoft.com...
> Hello,
> Just wondering if anyone can tell me the best way to rewrite the below
> code
> without a cursor.
> It's just passing each Id to a stored procedure.
> Let me know if you need any more info.
> Thanks & go easy on me, I know cursors tend to rile everyone up.
>
What is the code for the stored procedure: sp_SMART_ANADeleteFrom
If the sp_SMART_ANADeleteFrom procedure is performing some type of delete
based on the ChildTableID
then you should be able to modify the delete to do something like the
following:
DELETE TableName
WHERE ChildTableID IN
(Select distinct TableID
from tbl_DTM
Where APID IN
(Select TableID from tbl_DTM where
supplierID = @.v_FromSupplierID))
One a side note: You should probably not be naming your user defined stored
procedures with an sp_ prefix. The sp_ prefix while not disallowed, is
generally use for SQL Server system stored procedure which are found in the
master database and are available globally throughout the system.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for your help Rick,
Though the naming convention implies it's only deleting a child - it's
actually doing something completely different.
I still need to call the stored procedure for each table ID found.
Thanks for the sp_ info.
Lesley
"Rick Sawtell" wrote:

> "Lesley" <Lesley@.discussions.microsoft.com> wrote in message
> news:F2C468A7-7573-4FE4-8FDC-D8D75D2AE374@.microsoft.com...
> What is the code for the stored procedure: sp_SMART_ANADeleteFrom
> If the sp_SMART_ANADeleteFrom procedure is performing some type of delete
> based on the ChildTableID
> then you should be able to modify the delete to do something like the
> following:
> DELETE TableName
> WHERE ChildTableID IN
> (Select distinct TableID
> from tbl_DTM
> Where APID IN
> (Select TableID from tbl_DTM where
> supplierID = @.v_FromSupplierID))
>
> One a side note: You should probably not be naming your user defined stor
ed
> procedures with an sp_ prefix. The sp_ prefix while not disallowed, is
> generally use for SQL Server system stored procedure which are found in th
e
> master database and are available globally throughout the system.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Whatever it does, we can't help you find a set-baset solution without you
posting the procedure.
ML|||Sorry, I was thinking I could just use the sp as is. I pasted it below. It
was written a while ago by someone else & is in production now.
Basically it's deleting rows from a table, then deleting the defining row
from another table based on the tableID
I'd welcome any input on how to change this to set based.
That may also address rollback issues I predict I will have.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE sp_SMART_ANADeleteFrom
@.FromTableID INT = 0,
@.OKtoDeleteORIG BIT = 0,
@.DebugMode INT = 0
AS
DECLARE @.TableType CHAR(3)
DECLARE @.AnalyticParentID INT
DECLARE @.FromPhysicalTableName varchar(255)
DECLARE @.strSQL nvarchar(2000)
if @.FromTableID is null
begin
raiserror ('Invalid From Table ID.',1,1)
return
end
--
SELECT @.TableType = MyType,
@.AnalyticParentID = AnalyticParentID,
@.FromPhysicalTableName = PhysicalDataTableName
from
tbl_DataTableMaster where Tableid = @.FromTableID
BEGIN TRANSACTION DELETEfromAnalytics
-- Delete the rows from the Quarterly ANA table
SET @.StrSQL = N'DELETE FROM My_Users.' + @.FromPhysicalTableName +
N' WHERE TableID = ' + rtrim(convert(char(10),@.FromTableID))
if @.DebugMode <> 0
begin
print '-- DELETE Statement --'
print @.strsql
end
EXEC (@.StrSQL)
if @.@.Error <> 0
begin
ROLLBACK Transaction
Raiserror('Error deleting rows. Table Deletion did NOT occur!!',1,1)
RETURN
end
--delete row from tbl_DataTableMaster
SET @.strSQL = N'DELETE FROM tbl_DataTableMaster ' +
N' WHERE TableID = ' + rtrim(convert(char(10),@.FromTableID))
if @.DebugMode <> 0
begin
print '-- DELETE data table master Statement --'
print @.strsql
end
EXEC (@.StrSQL)
if @.@.Error <> 0
begin
ROLLBACK Transaction
Raiserror('Error Deleting in Data Table Master. Table Deletion did NOT
occur!!',1,1)
RETURN
end
--
COMMIT TRANSACTION DELETEfromAnalytics
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql

Help with Reindexing all tables in a Database?

Hello,
I was provided this script:
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
However, I am not sure of what the variables:
table_name
information_schema.tables
base table
are. And if I do not have to give them values - how does the script
know what they are?
Am I required to fill them in? And if so with what data.
I do have access to the DB and can see all the table names and have
logged in as the database owner.
I tried running it as it and got this:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 2501, Level 16, State 1, Line 12
Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
Any suggestions for the forced into place back up dba?
Thanks,
TmuldMaybe you're simply running this script in the wrong database?
Make sure you've selected the correct database in the database selection
list in the menu bar before running the script.
It might even help if you placed a USE command at the top of the script so
that it ensures the correct DB is being used when the script is run, eg:
USE [yourdbname]
DECALRE @.TableName...
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1176418044.844493.175160@.y80g2000hsf.googlegroups.com...
> Hello,
> I was provided this script:
> DECLARE @.TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DBCC DBREINDEX(@.TableName,' ',90)
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> However, I am not sure of what the variables:
> table_name
> information_schema.tables
> base table
> are. And if I do not have to give them values - how does the script
> know what they are?
> Am I required to fill them in? And if so with what data.
> I do have access to the DB and can see all the table names and have
> logged in as the database owner.
> I tried running it as it and got this:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Server: Msg 2501, Level 16, State 1, Line 12
> Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
> Any suggestions for the forced into place back up dba?
> Thanks,
> Tmuld
>sql

Help with Reindexing all tables in a Database?

Hello,
I was provided this script:
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
However, I am not sure of what the variables:
table_name
information_schema.tables
base table
are. And if I do not have to give them values - how does the script
know what they are?
Am I required to fill them in? And if so with what data.
I do have access to the DB and can see all the table names and have
logged in as the database owner.
I tried running it as it and got this:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 2501, Level 16, State 1, Line 12
Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
Any suggestions for the forced into place back up dba?
Thanks,
TmuldMaybe you're simply running this script in the wrong database?
Make sure you've selected the correct database in the database selection
list in the menu bar before running the script.
It might even help if you placed a USE command at the top of the script so
that it ensures the correct DB is being used when the script is run, eg:
USE [yourdbname]
DECALRE @.TableName...
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1176418044.844493.175160@.y80g2000hsf.googlegroups.com...
> Hello,
> I was provided this script:
> DECLARE @.TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DBCC DBREINDEX(@.TableName,' ',90)
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> However, I am not sure of what the variables:
> table_name
> information_schema.tables
> base table
> are. And if I do not have to give them values - how does the script
> know what they are?
> Am I required to fill them in? And if so with what data.
> I do have access to the DB and can see all the table names and have
> logged in as the database owner.
> I tried running it as it and got this:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Server: Msg 2501, Level 16, State 1, Line 12
> Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
> Any suggestions for the forced into place back up dba?
> Thanks,
> Tmuld
>