Problem regarding getting an XML script from a stored procedure that
returns XML string format of a select query on a temporary table
created by the stored procedure itself and values also inserted within
the stored procedure.See if this helps: http://www.sqlxml.org/faqs.aspx?faq=104
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"abc" <er.nehasinghal@.gmail.com> wrote in message
news:1121936278.733465.80930@.g47g2000cwa.googlegroups.com...
Problem regarding getting an XML script from a stored procedure that
returns XML string format of a select query on a temporary table
created by the stored procedure itself and values also inserted within
the stored procedure.sql
Showing posts with label script. Show all posts
Showing posts with label script. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
problem exectuing storded procedure
Hi NG,
I've got an ASP Script (useing Javascript) to which i send data to by POST.
This data stored in variables will be transferred to SQL stored Procedure.
As shown at bottom...
welches eine Variable übergeben
There is coming up an error...
I don't know how to solve it,
please help or give me a hint...
[asp code line 665 and + from Functions.asp]
var TopicName=String(Request.Form("TopicName"));
var TopicContent=String(Request.Form ("TopicContent"));
var strSQL="EXECUTE Topic @.TopicTitle=["+TopicName+"],
@.TopicContent=["+TopicContent+"]";
cmd.CommandText = strSQL;
cmd.Execute;
oDB_connect.close;
[/asp code]
[error page]
Fehlertyp:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (beginnend mit
'0123456789012345678901234567890123456789012345678901234567890123456789012345678
9012345678901234567890123456789012345678901234567') ist zu lang. Die
Maximallänge beträgt 128.
/Functions.asp, line 669
[/error page]
[snip from Stored Procedure]
CREATE PROCEDURE dbo.Topic @.TopicTitle VARCHAR(20), @.TopicContent VARCHAR(200)
AS INSERT INTO Table.......etc
[/snip from Stored Procedure][posted and mailed, please reply in news]
Jan Schmidt (histery@.gmx.net) writes:
> [asp code line 665 and + from Functions.asp]
> var TopicName=String(Request.Form("TopicName"));
> var TopicContent=String(Request.Form ("TopicContent"));
> var strSQL="EXECUTE Topic @.TopicTitle=["+TopicName+"],
> @.TopicContent=["+TopicContent+"]";
> cmd.CommandText = strSQL;
> cmd.Execute;
> oDB_connect.close;
> [/asp code]
> [error page]
> Fehlertyp:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (beginnend mit
>
'012345678901234567890123456789012345678901234567890123456789012345678901234
5678
> 9012345678901234567890123456789012345678901234567') ist zu lang. Die
> Maximallänge beträgt 128.
> /Functions.asp, line 669
> [/error page]
Before I address you actual problem, permit me to point out that you are
using the MSDASQL provider, OLE DB over ODBC. Use the SQLOLEDB provider
instead, since this provider is directly targeted for SQL Server and more
effecient. Add Provider=SQLOLEDB to your connection string and remove
Driver={SQL Server}.
For some reason you are putting brackets around the parameters to your
stored procedure. In Transact-SQL, brackets are used to delimit
identifiers; this is to permit you have table and column names with
special charcters such as space in them. Thus, in this case your parameters
are parsed as identifiers, and obviously at least one of them is longer
than 128 which is the maximum length for the optimizer.
Unless you are into something special, you should use ' instead to delimit
the parameters, but don't rush and change this, read on instead. You
cannot pass user input directly into an SQL string like this. Say that you
actually replaced the brackets with single quotes instead. Say then that
the user enters data with a single quote in it. The result: a syntax
error. Maybe. A malicious user can use this to enter a completely
different SQL command than you had intended. Thus, you have a big security
hole. And, no, don't laugh. SQL injection is a very common means of attack
on the web today.
A simple way out is to run the intput through a procedure that double
all single quotes in the input. That is, if the user enters "O'Brien",
you pass "O''Brien" to SQL Server. This is then parsed as O'Brien.
However, much better is to use the command type adStoredProcedure and
pass the parameter values through the .Parameters collection. Then you
don't have to bother about quoting or bracketing or anything. This is
also a more effecient way to call a stored procedure.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||"Erland Sommarskog" schrieb
> Before I address you actual problem, permit me to point out that you are
> using the MSDASQL provider, OLE DB over ODBC. Use the SQLOLEDB provider
> instead, since this provider is directly targeted for SQL Server and more
> effecient. Add Provider=SQLOLEDB to your connection string and remove
> Driver={SQL Server}.
You've got a special Webpage to read the differences between this two methods?
I tried to make a simple SQL request via Query Analyzer, like this:
[code]
Declare @.TopicTitle varchar(40),
@.TopicContent varchar(200)
set
@.TopicContent="12345678901234567890123456789012345678901234567890123456789012345
67890123456789012345678901234567890123456789012345678901234567890"
set @.TopicTitle="test3";
INSERT INTO Themen(Themen_Name, Themen_Content) VALUES(@.TopicTitle,
@.TopicContent)
[/code]
and also getting the same error:
[error]
Server: Nachr.-Nr. 103, Schweregrad 15 ...
Identifier (begins with '1234567...') is too long. The Maximum length is 128.
[/error]
[used table]
CREATE TABLE [dbo].[Themen] (
[Thema_Nr] [int] IDENTITY (1, 1) NOT NULL ,
[Themen_Name] [varchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,
[Themen_Content] [varchar] (200) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Themen] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[Thema_Nr]
) ON [PRIMARY]
GO
[/used table]|||You're using double quotes. You need to use single quotes. Double quotes are
used for identifiers like brackets are.
--
David Gugick
Imceda Software|||found my mistake ,-)
the " was wrong|||"Erland Sommarskog" schrieb
> However, much better is to use the command type adStoredProcedure and
> pass the parameter values through the .Parameters collection. Then you
> don't have to bother about quoting or bracketing or anything. This is
> also a more effecient way to call a stored procedure.
hmm, can you give me an example code please?
i searched around a bit for asStoredProcedure but didn't get a result for it.
perhaps it's the best and easiest way for me to handle my problem
regards
Jan|||Jan Schmidt (histery@.gmx.net) writes:
> "Erland Sommarskog" schrieb
>> However, much better is to use the command type adStoredProcedure and
>> pass the parameter values through the .Parameters collection. Then you
>> don't have to bother about quoting or bracketing or anything. This is
>> also a more effecient way to call a stored procedure.
> hmm, can you give me an example code please? i searched around a bit for
> asStoredProcedure but didn't get a result for it. perhaps it's the
> best and easiest way for me to handle my problem
Sorry, the name of the constant is adCmdStoredProc.
I can't give a example in ASP, since I don't know ASP. This snippet is
Visual Basic, and shows how to changes the password for the user abc:
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=MyServer" & _
"Initial Catalog='tempdn'; _
"Integrated Security='SSPI';"
cnn.ConnectionTimeout = 5
cnn.Open
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "master.dbo.sp_password"
cmd.Parameters.Append _
cmd.CreateParameter("@.old", adVarChar, adParamInput, 10, "nisse")
cmd.Parameters.Append _
cmd.CreateParameter("@.new", adVarChar, adParamInput, 10, "pelle")
cmd.Parameters.Append
cmd.CreateParameter("@.login", adVarChar, adParamInput, 10, "abc")
cmd.Execute
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
> "Erland Sommarskog" schrieb
>> Before I address you actual problem, permit me to point out that you are
>> using the MSDASQL provider, OLE DB over ODBC. Use the SQLOLEDB provider
>> instead, since this provider is directly targeted for SQL Server and more
>> effecient. Add Provider=SQLOLEDB to your connection string and remove
>> Driver={SQL Server}.
> You've got a special Webpage to read the differences between this two
> methods?
The section Deprecated Components in the MDAC Books Online (this material
is also in MSDN Library) says:
ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the
OLE DB NET Data Provider.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||"Erland Sommarskog" schrieb
> cmd.CommandType = adCmdStoredProc
Yes, i did what you said,
everything seams to be good, except, as ai said, i use asp with Javascript not
VB,
so it says there isn't any function called adCmdStoredProc
i also tried adCmdStoredProcedure and adStoredProc and adStoredProcedure and
StoredProc and StoredProcedure.
Why?
regards
Jan|||Jan Schmidt (histery@.gmx.net) writes:
> "Erland Sommarskog" schrieb
>> cmd.CommandType = adCmdStoredProc
> Yes, i did what you said, everything seams to be good, except, as ai
> said, i use asp with Javascript not VB, so it says there isn't any
> function called adCmdStoredProc i also tried adCmdStoredProcedure and
> adStoredProc and adStoredProcedure and StoredProc and StoredProcedure.
adCmdStoredProc is not a procedure but a constant.
Since I know Javascript just as equally well as I know ASP - that is, not
at all - I can't say how you get hold of these constants. But you can
always use the underlying values. You find them in the MDAC Books Online,
and all about MDAC is also in MSDN Library. If you don't have these
resources on disk, they are available on the web.
If you look around a little more, you might be able to find include files
for ADO constants to be used in Javascript.
A quick searh on Google, reveals that the value for adCmdStoredProc is 4,
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||I have Macromedia Dreamweaver and this is the code it produced to run an ADO
command for a stored procedure:
var Command1 = Server.CreateObject("ADODB.Command");
Command1.ActiveConnection = Yuor_Connection_String;
Command1.CommandText = "sp_yourStoredProc";
Command1.CommandType = 4;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Parameters.Append(Command1.CreateParameter("@.RETURN_VALUE", 3, 4));
Command1.Parameters.Append(Command1.CreateParameter("@.SubscriberID", 3,
1,4,local_var_SubscriberID));
Command1.Parameters.Append(Command1.CreateParameter("@.Email", 200,
1,50,local_var_Email));
Command1.Parameters.Append(Command1.CreateParameter("@.Name", 200,
1,50,local_var_Name));
var Recordset1 = Command1.Execute();
You'll notice of course the CommandType = 4
HPH
Andy
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.gmx.net) writes:
> > "Erland Sommarskog" schrieb
> >> cmd.CommandType = adCmdStoredProc
> >
> > Yes, i did what you said, everything seams to be good, except, as ai
> > said, i use asp with Javascript not VB, so it says there isn't any
> > function called adCmdStoredProc i also tried adCmdStoredProcedure and
> > adStoredProc and adStoredProcedure and StoredProc and StoredProcedure.
> adCmdStoredProc is not a procedure but a constant.
> Since I know Javascript just as equally well as I know ASP - that is, not
> at all - I can't say how you get hold of these constants. But you can
> always use the underlying values. You find them in the MDAC Books Online,
> and all about MDAC is also in MSDN Library. If you don't have these
> resources on disk, they are available on the web.
> If you look around a little more, you might be able to find include files
> for ADO constants to be used in Javascript.
> A quick searh on Google, reveals that the value for adCmdStoredProc is 4,
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.gmx.net) writes:
>> "Erland Sommarskog" schrieb
>> cmd.CommandType = adCmdStoredProc
>> Yes, i did what you said, everything seams to be good, except, as ai
>> said, i use asp with Javascript not VB, so it says there isn't any
>> function called adCmdStoredProc i also tried adCmdStoredProcedure and
>> adStoredProc and adStoredProcedure and StoredProc and StoredProcedure.
> adCmdStoredProc is not a procedure but a constant.
> Since I know Javascript just as equally well as I know ASP - that is, not
> at all - I can't say how you get hold of these constants. But you can
> always use the underlying values. You find them in the MDAC Books Online,
> and all about MDAC is also in MSDN Library. If you don't have these
> resources on disk, they are available on the web.
> If you look around a little more, you might be able to find include files
> for ADO constants to be used in Javascript.
Try the file adojavas.inc in C:\Program Files\Common Files\System\ado
The VBScript include is in the same dir (adovbs.inc)
:)
Dan
I've got an ASP Script (useing Javascript) to which i send data to by POST.
This data stored in variables will be transferred to SQL stored Procedure.
As shown at bottom...
welches eine Variable übergeben
There is coming up an error...
I don't know how to solve it,
please help or give me a hint...
[asp code line 665 and + from Functions.asp]
var TopicName=String(Request.Form("TopicName"));
var TopicContent=String(Request.Form ("TopicContent"));
var strSQL="EXECUTE Topic @.TopicTitle=["+TopicName+"],
@.TopicContent=["+TopicContent+"]";
cmd.CommandText = strSQL;
cmd.Execute;
oDB_connect.close;
[/asp code]
[error page]
Fehlertyp:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (beginnend mit
'0123456789012345678901234567890123456789012345678901234567890123456789012345678
9012345678901234567890123456789012345678901234567') ist zu lang. Die
Maximallänge beträgt 128.
/Functions.asp, line 669
[/error page]
[snip from Stored Procedure]
CREATE PROCEDURE dbo.Topic @.TopicTitle VARCHAR(20), @.TopicContent VARCHAR(200)
AS INSERT INTO Table.......etc
[/snip from Stored Procedure][posted and mailed, please reply in news]
Jan Schmidt (histery@.gmx.net) writes:
> [asp code line 665 and + from Functions.asp]
> var TopicName=String(Request.Form("TopicName"));
> var TopicContent=String(Request.Form ("TopicContent"));
> var strSQL="EXECUTE Topic @.TopicTitle=["+TopicName+"],
> @.TopicContent=["+TopicContent+"]";
> cmd.CommandText = strSQL;
> cmd.Execute;
> oDB_connect.close;
> [/asp code]
> [error page]
> Fehlertyp:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (beginnend mit
>
'012345678901234567890123456789012345678901234567890123456789012345678901234
5678
> 9012345678901234567890123456789012345678901234567') ist zu lang. Die
> Maximallänge beträgt 128.
> /Functions.asp, line 669
> [/error page]
Before I address you actual problem, permit me to point out that you are
using the MSDASQL provider, OLE DB over ODBC. Use the SQLOLEDB provider
instead, since this provider is directly targeted for SQL Server and more
effecient. Add Provider=SQLOLEDB to your connection string and remove
Driver={SQL Server}.
For some reason you are putting brackets around the parameters to your
stored procedure. In Transact-SQL, brackets are used to delimit
identifiers; this is to permit you have table and column names with
special charcters such as space in them. Thus, in this case your parameters
are parsed as identifiers, and obviously at least one of them is longer
than 128 which is the maximum length for the optimizer.
Unless you are into something special, you should use ' instead to delimit
the parameters, but don't rush and change this, read on instead. You
cannot pass user input directly into an SQL string like this. Say that you
actually replaced the brackets with single quotes instead. Say then that
the user enters data with a single quote in it. The result: a syntax
error. Maybe. A malicious user can use this to enter a completely
different SQL command than you had intended. Thus, you have a big security
hole. And, no, don't laugh. SQL injection is a very common means of attack
on the web today.
A simple way out is to run the intput through a procedure that double
all single quotes in the input. That is, if the user enters "O'Brien",
you pass "O''Brien" to SQL Server. This is then parsed as O'Brien.
However, much better is to use the command type adStoredProcedure and
pass the parameter values through the .Parameters collection. Then you
don't have to bother about quoting or bracketing or anything. This is
also a more effecient way to call a stored procedure.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||"Erland Sommarskog" schrieb
> Before I address you actual problem, permit me to point out that you are
> using the MSDASQL provider, OLE DB over ODBC. Use the SQLOLEDB provider
> instead, since this provider is directly targeted for SQL Server and more
> effecient. Add Provider=SQLOLEDB to your connection string and remove
> Driver={SQL Server}.
You've got a special Webpage to read the differences between this two methods?
I tried to make a simple SQL request via Query Analyzer, like this:
[code]
Declare @.TopicTitle varchar(40),
@.TopicContent varchar(200)
set
@.TopicContent="12345678901234567890123456789012345678901234567890123456789012345
67890123456789012345678901234567890123456789012345678901234567890"
set @.TopicTitle="test3";
INSERT INTO Themen(Themen_Name, Themen_Content) VALUES(@.TopicTitle,
@.TopicContent)
[/code]
and also getting the same error:
[error]
Server: Nachr.-Nr. 103, Schweregrad 15 ...
Identifier (begins with '1234567...') is too long. The Maximum length is 128.
[/error]
[used table]
CREATE TABLE [dbo].[Themen] (
[Thema_Nr] [int] IDENTITY (1, 1) NOT NULL ,
[Themen_Name] [varchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,
[Themen_Content] [varchar] (200) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Themen] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[Thema_Nr]
) ON [PRIMARY]
GO
[/used table]|||You're using double quotes. You need to use single quotes. Double quotes are
used for identifiers like brackets are.
--
David Gugick
Imceda Software|||found my mistake ,-)
the " was wrong|||"Erland Sommarskog" schrieb
> However, much better is to use the command type adStoredProcedure and
> pass the parameter values through the .Parameters collection. Then you
> don't have to bother about quoting or bracketing or anything. This is
> also a more effecient way to call a stored procedure.
hmm, can you give me an example code please?
i searched around a bit for asStoredProcedure but didn't get a result for it.
perhaps it's the best and easiest way for me to handle my problem
regards
Jan|||Jan Schmidt (histery@.gmx.net) writes:
> "Erland Sommarskog" schrieb
>> However, much better is to use the command type adStoredProcedure and
>> pass the parameter values through the .Parameters collection. Then you
>> don't have to bother about quoting or bracketing or anything. This is
>> also a more effecient way to call a stored procedure.
> hmm, can you give me an example code please? i searched around a bit for
> asStoredProcedure but didn't get a result for it. perhaps it's the
> best and easiest way for me to handle my problem
Sorry, the name of the constant is adCmdStoredProc.
I can't give a example in ASP, since I don't know ASP. This snippet is
Visual Basic, and shows how to changes the password for the user abc:
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=MyServer" & _
"Initial Catalog='tempdn'; _
"Integrated Security='SSPI';"
cnn.ConnectionTimeout = 5
cnn.Open
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "master.dbo.sp_password"
cmd.Parameters.Append _
cmd.CreateParameter("@.old", adVarChar, adParamInput, 10, "nisse")
cmd.Parameters.Append _
cmd.CreateParameter("@.new", adVarChar, adParamInput, 10, "pelle")
cmd.Parameters.Append
cmd.CreateParameter("@.login", adVarChar, adParamInput, 10, "abc")
cmd.Execute
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
> "Erland Sommarskog" schrieb
>> Before I address you actual problem, permit me to point out that you are
>> using the MSDASQL provider, OLE DB over ODBC. Use the SQLOLEDB provider
>> instead, since this provider is directly targeted for SQL Server and more
>> effecient. Add Provider=SQLOLEDB to your connection string and remove
>> Driver={SQL Server}.
> You've got a special Webpage to read the differences between this two
> methods?
The section Deprecated Components in the MDAC Books Online (this material
is also in MSDN Library) says:
ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the
OLE DB NET Data Provider.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||"Erland Sommarskog" schrieb
> cmd.CommandType = adCmdStoredProc
Yes, i did what you said,
everything seams to be good, except, as ai said, i use asp with Javascript not
VB,
so it says there isn't any function called adCmdStoredProc
i also tried adCmdStoredProcedure and adStoredProc and adStoredProcedure and
StoredProc and StoredProcedure.
Why?
regards
Jan|||Jan Schmidt (histery@.gmx.net) writes:
> "Erland Sommarskog" schrieb
>> cmd.CommandType = adCmdStoredProc
> Yes, i did what you said, everything seams to be good, except, as ai
> said, i use asp with Javascript not VB, so it says there isn't any
> function called adCmdStoredProc i also tried adCmdStoredProcedure and
> adStoredProc and adStoredProcedure and StoredProc and StoredProcedure.
adCmdStoredProc is not a procedure but a constant.
Since I know Javascript just as equally well as I know ASP - that is, not
at all - I can't say how you get hold of these constants. But you can
always use the underlying values. You find them in the MDAC Books Online,
and all about MDAC is also in MSDN Library. If you don't have these
resources on disk, they are available on the web.
If you look around a little more, you might be able to find include files
for ADO constants to be used in Javascript.
A quick searh on Google, reveals that the value for adCmdStoredProc is 4,
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||I have Macromedia Dreamweaver and this is the code it produced to run an ADO
command for a stored procedure:
var Command1 = Server.CreateObject("ADODB.Command");
Command1.ActiveConnection = Yuor_Connection_String;
Command1.CommandText = "sp_yourStoredProc";
Command1.CommandType = 4;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Parameters.Append(Command1.CreateParameter("@.RETURN_VALUE", 3, 4));
Command1.Parameters.Append(Command1.CreateParameter("@.SubscriberID", 3,
1,4,local_var_SubscriberID));
Command1.Parameters.Append(Command1.CreateParameter("@.Email", 200,
1,50,local_var_Email));
Command1.Parameters.Append(Command1.CreateParameter("@.Name", 200,
1,50,local_var_Name));
var Recordset1 = Command1.Execute();
You'll notice of course the CommandType = 4
HPH
Andy
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.gmx.net) writes:
> > "Erland Sommarskog" schrieb
> >> cmd.CommandType = adCmdStoredProc
> >
> > Yes, i did what you said, everything seams to be good, except, as ai
> > said, i use asp with Javascript not VB, so it says there isn't any
> > function called adCmdStoredProc i also tried adCmdStoredProcedure and
> > adStoredProc and adStoredProcedure and StoredProc and StoredProcedure.
> adCmdStoredProc is not a procedure but a constant.
> Since I know Javascript just as equally well as I know ASP - that is, not
> at all - I can't say how you get hold of these constants. But you can
> always use the underlying values. You find them in the MDAC Books Online,
> and all about MDAC is also in MSDN Library. If you don't have these
> resources on disk, they are available on the web.
> If you look around a little more, you might be able to find include files
> for ADO constants to be used in Javascript.
> A quick searh on Google, reveals that the value for adCmdStoredProc is 4,
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.gmx.net) writes:
>> "Erland Sommarskog" schrieb
>> cmd.CommandType = adCmdStoredProc
>> Yes, i did what you said, everything seams to be good, except, as ai
>> said, i use asp with Javascript not VB, so it says there isn't any
>> function called adCmdStoredProc i also tried adCmdStoredProcedure and
>> adStoredProc and adStoredProcedure and StoredProc and StoredProcedure.
> adCmdStoredProc is not a procedure but a constant.
> Since I know Javascript just as equally well as I know ASP - that is, not
> at all - I can't say how you get hold of these constants. But you can
> always use the underlying values. You find them in the MDAC Books Online,
> and all about MDAC is also in MSDN Library. If you don't have these
> resources on disk, they are available on the web.
> If you look around a little more, you might be able to find include files
> for ADO constants to be used in Javascript.
Try the file adojavas.inc in C:\Program Files\Common Files\System\ado
The VBScript include is in the same dir (adovbs.inc)
:)
Dan
problem exectuing storded procedure
Hi NG,
I've got an ASP Script (useing Javascript) to which i send data to by POST.
This data stored in variables will be transferred to SQL stored Procedure.
As shown at bottom...
welches eine Variable bergeben
There is coming up an error...
I don't know how to solve it,
please help or give me a hint...
[asp code line 665 and + from Functions.asp]
var TopicName=String(Request.Form("TopicName"));
var TopicContent=String(Request.Form ("TopicContent"));
var strSQL="EXECUTE Topic @.TopicTitle=["+TopicName+"],
@.TopicContent=["+TopicContent+"]";
cmd.CommandText = strSQL;
cmd.Execute;
oDB_connect.close;
[/asp code]
[error page]
Fehlertyp:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (begin
nend mit
'012345678901234567890123456789012345678
901234567890123456789012345678901234
5678
9012345678901234567890123456789012345678
901234567') ist zu lang. Die
Maximallnge betrgt 128.
/Functions.asp, line 669
[/error page]
[snip from Stored Procedure]
CREATE PROCEDURE dbo.Topic @.TopicTitle VARCHAR(20), @.TopicContent VARCHAR(20
0)
AS INSERT INTO Table.......etc
[/snip from Stored Procedure][posted and mailed, please reply in news]
Jan Schmidt (histery@.gmx.net) writes:
> [asp code line 665 and + from Functions.asp]
> var TopicName=String(Request.Form("TopicName"));
> var TopicContent=String(Request.Form ("TopicContent"));
> var strSQL="EXECUTE Topic @.TopicTitle=["+TopicName+"],
> @.TopicContent=["+TopicContent+"]";
> cmd.CommandText = strSQL;
> cmd.Execute;
> oDB_connect.close;
> [/asp code]
> [error page]
> Fehlertyp:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (beg
innend mit
>
'012345678901234567890123456789012345678
901234567890123456789012345678901234
5678
> 9012345678901234567890123456789012345678
901234567') ist zu lang. Die
> Maximallnge betrgt 128.
> /Functions.asp, line 669
> [/error page]
Before I address you actual problem, permit me to point out that you are
using the MSDASQL provider, OLE DB over ODBC. Use the SQLOLEDB provider
instead, since this provider is directly targeted for SQL Server and more
effecient. Add Provider=SQLOLEDB to your connection string and remove
Driver={SQL Server}.
For some reason you are putting brackets around the parameters to your
stored procedure. In Transact-SQL, brackets are used to delimit
identifiers; this is to permit you have table and column names with
special charcters such as space in them. Thus, in this case your parameters
are parsed as identifiers, and obviously at least one of them is longer
than 128 which is the maximum length for the optimizer.
Unless you are into something special, you should use ' instead to delimit
the parameters, but don't rush and change this, read on instead. You
cannot pass user input directly into an SQL string like this. Say that you
actually replaced the brackets with single quotes instead. Say then that
the user enters data with a single quote in it. The result: a syntax
error. Maybe. A malicious user can use this to enter a completely
different SQL command than you had intended. Thus, you have a big security
hole. And, no, don't laugh. SQL injection is a very common means of attack
on the web today.
A simple way out is to run the intput through a procedure that double
all single quotes in the input. That is, if the user enters "O'Brien",
you pass "O''Brien" to SQL Server. This is then parsed as O'Brien.
However, much better is to use the command type adStoredProcedure and
pass the parameter values through the .Parameters collection. Then you
don't have to bother about quoting or bracketing or anything. This is
also a more effecient way to call a stored procedure.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" schrieb
> However, much better is to use the command type adStoredProcedure and
> pass the parameter values through the .Parameters collection. Then you
> don't have to bother about quoting or bracketing or anything. This is
> also a more effecient way to call a stored procedure.
hmm, can you give me an example code please?
i searched around a bit for asStoredProcedure but didn't get a result for
it.
perhaps it's the best and easiest way for me to handle my problem
regards
Jan|||Jan Schmidt (histery@.gmx.net) writes:
> "Erland Sommarskog" schrieb
> hmm, can you give me an example code please? i searched around a bit for
> asStoredProcedure but didn't get a result for it. perhaps it's the
> best and easiest way for me to handle my problem
Sorry, the name of the constant is adCmdStoredProc.
I can't give a example in ASP, since I don't know ASP. This snippet is
Visual Basic, and shows how to changes the password for the user abc:
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=MyServer" & _
"Initial Catalog='tempdn'; _
"Integrated Security='SSPI';"
cnn.ConnectionTimeout = 5
cnn.Open
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "master.dbo.sp_password"
cmd.Parameters.Append _
cmd.CreateParameter("@.old", adVarChar, adParamInput, 10, "nisse")
cmd.Parameters.Append _
cmd.CreateParameter("@.new", adVarChar, adParamInput, 10, "pelle")
cmd.Parameters.Append
cmd.CreateParameter("@.login", adVarChar, adParamInput, 10, "abc")
cmd.Execute
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
> "Erland Sommarskog" schrieb
> You've got a special Webpage to read the differences between this two
> methods?
The section Deprecated Components in the MDAC Books Online (this material
is also in MSDN Library) says:
ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the
OLE DB NET Data Provider.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" schrieb
> cmd.CommandType = adCmdStoredProc
Yes, i did what you said,
everything seams to be good, except, as ai said, i use asp with Javascript n
ot
VB,
so it says there isn't any function called adCmdStoredProc
i also tried adCmdStoredProcedure and adStoredProc and adStoredProcedure and
StoredProc and StoredProcedure.
Why?
regards
Jan|||Jan Schmidt (histery@.gmx.net) writes:
> "Erland Sommarskog" schrieb
> Yes, i did what you said, everything seams to be good, except, as ai
> said, i use asp with Javascript not VB, so it says there isn't any
> function called adCmdStoredProc i also tried adCmdStoredProcedure and
> adStoredProc and adStoredProcedure and StoredProc and StoredProcedure.
adCmdStoredProc is not a procedure but a constant.
Since I know Javascript just as equally well as I know ASP - that is, not
at all - I can't say how you get hold of these constants. But you can
always use the underlying values. You find them in the MDAC Books Online,
and all about MDAC is also in MSDN Library. If you don't have these
resources on disk, they are available on the web.
If you look around a little more, you might be able to find include files
for ADO constants to be used in Javascript.
A quick searh on Google, reveals that the value for adCmdStoredProc is 4,
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have Macromedia Dreamweaver and this is the code it produced to run an ADO
command for a stored procedure:
var Command1 = Server.CreateObject("ADODB.Command");
Command1.ActiveConnection = Yuor_Connection_String;
Command1.CommandText = "sp_yourStoredProc";
Command1.CommandType = 4;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Parameters.Append(Command1.CreateParameter("@.RETURN_VALUE", 3, 4));
Command1.Parameters.Append(Command1.CreateParameter("@.SubscriberID", 3,
1,4,local_var_SubscriberID));
Command1.Parameters.Append(Command1.CreateParameter("@.Email", 200,
1,50,local_var_Email));
Command1.Parameters.Append(Command1.CreateParameter("@.Name", 200,
1,50,local_var_Name));
var Recordset1 = Command1.Execute();
You'll notice of course the CommandType = 4
HPH
Andy
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.gmx.net) writes:
> adCmdStoredProc is not a procedure but a constant.
> Since I know Javascript just as equally well as I know ASP - that is, not
> at all - I can't say how you get hold of these constants. But you can
> always use the underlying values. You find them in the MDAC Books Online,
> and all about MDAC is also in MSDN Library. If you don't have these
> resources on disk, they are available on the web.
> If you look around a little more, you might be able to find include files
> for ADO constants to be used in Javascript.
> A quick searh on Google, reveals that the value for adCmdStoredProc is 4,
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.gmx.net) writes:
> adCmdStoredProc is not a procedure but a constant.
> Since I know Javascript just as equally well as I know ASP - that is, not
> at all - I can't say how you get hold of these constants. But you can
> always use the underlying values. You find them in the MDAC Books Online,
> and all about MDAC is also in MSDN Library. If you don't have these
> resources on disk, they are available on the web.
> If you look around a little more, you might be able to find include files
> for ADO constants to be used in Javascript.
Try the file adojavas.inc in C:\Program Files\Common Files\System\ado
The VBScript include is in the same dir (adovbs.inc)
Dan
I've got an ASP Script (useing Javascript) to which i send data to by POST.
This data stored in variables will be transferred to SQL stored Procedure.
As shown at bottom...
welches eine Variable bergeben
There is coming up an error...
I don't know how to solve it,
please help or give me a hint...
[asp code line 665 and + from Functions.asp]
var TopicName=String(Request.Form("TopicName"));
var TopicContent=String(Request.Form ("TopicContent"));
var strSQL="EXECUTE Topic @.TopicTitle=["+TopicName+"],
@.TopicContent=["+TopicContent+"]";
cmd.CommandText = strSQL;
cmd.Execute;
oDB_connect.close;
[/asp code]
[error page]
Fehlertyp:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (begin
nend mit
'012345678901234567890123456789012345678
901234567890123456789012345678901234
5678
9012345678901234567890123456789012345678
901234567') ist zu lang. Die
Maximallnge betrgt 128.
/Functions.asp, line 669
[/error page]
[snip from Stored Procedure]
CREATE PROCEDURE dbo.Topic @.TopicTitle VARCHAR(20), @.TopicContent VARCHAR(20
0)
AS INSERT INTO Table.......etc
[/snip from Stored Procedure][posted and mailed, please reply in news]
Jan Schmidt (histery@.gmx.net) writes:
> [asp code line 665 and + from Functions.asp]
> var TopicName=String(Request.Form("TopicName"));
> var TopicContent=String(Request.Form ("TopicContent"));
> var strSQL="EXECUTE Topic @.TopicTitle=["+TopicName+"],
> @.TopicContent=["+TopicContent+"]";
> cmd.CommandText = strSQL;
> cmd.Execute;
> oDB_connect.close;
> [/asp code]
> [error page]
> Fehlertyp:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (beg
innend mit
>
'012345678901234567890123456789012345678
901234567890123456789012345678901234
5678
> 9012345678901234567890123456789012345678
901234567') ist zu lang. Die
> Maximallnge betrgt 128.
> /Functions.asp, line 669
> [/error page]
Before I address you actual problem, permit me to point out that you are
using the MSDASQL provider, OLE DB over ODBC. Use the SQLOLEDB provider
instead, since this provider is directly targeted for SQL Server and more
effecient. Add Provider=SQLOLEDB to your connection string and remove
Driver={SQL Server}.
For some reason you are putting brackets around the parameters to your
stored procedure. In Transact-SQL, brackets are used to delimit
identifiers; this is to permit you have table and column names with
special charcters such as space in them. Thus, in this case your parameters
are parsed as identifiers, and obviously at least one of them is longer
than 128 which is the maximum length for the optimizer.
Unless you are into something special, you should use ' instead to delimit
the parameters, but don't rush and change this, read on instead. You
cannot pass user input directly into an SQL string like this. Say that you
actually replaced the brackets with single quotes instead. Say then that
the user enters data with a single quote in it. The result: a syntax
error. Maybe. A malicious user can use this to enter a completely
different SQL command than you had intended. Thus, you have a big security
hole. And, no, don't laugh. SQL injection is a very common means of attack
on the web today.
A simple way out is to run the intput through a procedure that double
all single quotes in the input. That is, if the user enters "O'Brien",
you pass "O''Brien" to SQL Server. This is then parsed as O'Brien.
However, much better is to use the command type adStoredProcedure and
pass the parameter values through the .Parameters collection. Then you
don't have to bother about quoting or bracketing or anything. This is
also a more effecient way to call a stored procedure.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" schrieb
> However, much better is to use the command type adStoredProcedure and
> pass the parameter values through the .Parameters collection. Then you
> don't have to bother about quoting or bracketing or anything. This is
> also a more effecient way to call a stored procedure.
hmm, can you give me an example code please?
i searched around a bit for asStoredProcedure but didn't get a result for
it.
perhaps it's the best and easiest way for me to handle my problem
regards
Jan|||Jan Schmidt (histery@.gmx.net) writes:
> "Erland Sommarskog" schrieb
> hmm, can you give me an example code please? i searched around a bit for
> asStoredProcedure but didn't get a result for it. perhaps it's the
> best and easiest way for me to handle my problem
Sorry, the name of the constant is adCmdStoredProc.
I can't give a example in ASP, since I don't know ASP. This snippet is
Visual Basic, and shows how to changes the password for the user abc:
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=MyServer" & _
"Initial Catalog='tempdn'; _
"Integrated Security='SSPI';"
cnn.ConnectionTimeout = 5
cnn.Open
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "master.dbo.sp_password"
cmd.Parameters.Append _
cmd.CreateParameter("@.old", adVarChar, adParamInput, 10, "nisse")
cmd.Parameters.Append _
cmd.CreateParameter("@.new", adVarChar, adParamInput, 10, "pelle")
cmd.Parameters.Append
cmd.CreateParameter("@.login", adVarChar, adParamInput, 10, "abc")
cmd.Execute
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
> "Erland Sommarskog" schrieb
> You've got a special Webpage to read the differences between this two
> methods?
The section Deprecated Components in the MDAC Books Online (this material
is also in MSDN Library) says:
ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the
OLE DB NET Data Provider.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" schrieb
> cmd.CommandType = adCmdStoredProc
Yes, i did what you said,
everything seams to be good, except, as ai said, i use asp with Javascript n
ot
VB,
so it says there isn't any function called adCmdStoredProc
i also tried adCmdStoredProcedure and adStoredProc and adStoredProcedure and
StoredProc and StoredProcedure.
Why?
regards
Jan|||Jan Schmidt (histery@.gmx.net) writes:
> "Erland Sommarskog" schrieb
> Yes, i did what you said, everything seams to be good, except, as ai
> said, i use asp with Javascript not VB, so it says there isn't any
> function called adCmdStoredProc i also tried adCmdStoredProcedure and
> adStoredProc and adStoredProcedure and StoredProc and StoredProcedure.
adCmdStoredProc is not a procedure but a constant.
Since I know Javascript just as equally well as I know ASP - that is, not
at all - I can't say how you get hold of these constants. But you can
always use the underlying values. You find them in the MDAC Books Online,
and all about MDAC is also in MSDN Library. If you don't have these
resources on disk, they are available on the web.
If you look around a little more, you might be able to find include files
for ADO constants to be used in Javascript.
A quick searh on Google, reveals that the value for adCmdStoredProc is 4,
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have Macromedia Dreamweaver and this is the code it produced to run an ADO
command for a stored procedure:
var Command1 = Server.CreateObject("ADODB.Command");
Command1.ActiveConnection = Yuor_Connection_String;
Command1.CommandText = "sp_yourStoredProc";
Command1.CommandType = 4;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Parameters.Append(Command1.CreateParameter("@.RETURN_VALUE", 3, 4));
Command1.Parameters.Append(Command1.CreateParameter("@.SubscriberID", 3,
1,4,local_var_SubscriberID));
Command1.Parameters.Append(Command1.CreateParameter("@.Email", 200,
1,50,local_var_Email));
Command1.Parameters.Append(Command1.CreateParameter("@.Name", 200,
1,50,local_var_Name));
var Recordset1 = Command1.Execute();
You'll notice of course the CommandType = 4
HPH
Andy
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.gmx.net) writes:
> adCmdStoredProc is not a procedure but a constant.
> Since I know Javascript just as equally well as I know ASP - that is, not
> at all - I can't say how you get hold of these constants. But you can
> always use the underlying values. You find them in the MDAC Books Online,
> and all about MDAC is also in MSDN Library. If you don't have these
> resources on disk, they are available on the web.
> If you look around a little more, you might be able to find include files
> for ADO constants to be used in Javascript.
> A quick searh on Google, reveals that the value for adCmdStoredProc is 4,
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.gmx.net) writes:
> adCmdStoredProc is not a procedure but a constant.
> Since I know Javascript just as equally well as I know ASP - that is, not
> at all - I can't say how you get hold of these constants. But you can
> always use the underlying values. You find them in the MDAC Books Online,
> and all about MDAC is also in MSDN Library. If you don't have these
> resources on disk, they are available on the web.
> If you look around a little more, you might be able to find include files
> for ADO constants to be used in Javascript.
Try the file adojavas.inc in C:\Program Files\Common Files\System\ado
The VBScript include is in the same dir (adovbs.inc)
Dan
problem exectuing storded procedure
Hi NG,
I've got an ASP Script (useing Javascript) to which i send data to by POST.
This data stored in variables will be transferred to SQL stored Procedure.
As shown at bottom...
welches eine Variable bergeben
There is coming up an error...
I don't know how to solve it,
please help or give me a hint...
[asp code line 665 and + from Functions.asp]
var TopicName=String(Request.Form("TopicName"));
var TopicContent=String(Request.Form ("TopicContent"));
var strSQL="EXECUTE Topic @.TopicTitle=["+TopicName+"],
@.TopicContent=["+TopicContent+"]";
cmd.CommandText = strSQL;
cmd.Execute;
oDB_connect.close;
[/asp code]
[error page]
Fehlertyp:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (beginnend mit
'0123456789012345678901234567890123456789012345678 901234567890123456789012345678
9012345678901234567890123456789012345678901234567' ) ist zu lang. Die
Maximallnge betrgt 128.
/Functions.asp, line 669
[/error page]
[snip from Stored Procedure]
CREATE PROCEDURE dbo.Topic @.TopicTitle VARCHAR(20), @.TopicContent VARCHAR(200)
AS INSERT INTO Table.......etc
[/snip from Stored Procedure]
[posted and mailed, please reply in news]
Jan Schmidt (histery@.gmx.net) writes:
> [asp code line 665 and + from Functions.asp]
> var TopicName=String(Request.Form("TopicName"));
> var TopicContent=String(Request.Form ("TopicContent"));
> var strSQL="EXECUTE Topic @.TopicTitle=["+TopicName+"],
> @.TopicContent=["+TopicContent+"]";
> cmd.CommandText = strSQL;
> cmd.Execute;
> oDB_connect.close;
> [/asp code]
> [error page]
> Fehlertyp:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (beginnend mit
>
'0123456789012345678901234567890123456789012345678 90123456789012345678901234
5678
> 9012345678901234567890123456789012345678901234567' ) ist zu lang. Die
> Maximallnge betrgt 128.
> /Functions.asp, line 669
> [/error page]
Before I address you actual problem, permit me to point out that you are
using the MSDASQL provider, OLE DB over ODBC. Use the SQLOLEDB provider
instead, since this provider is directly targeted for SQL Server and more
effecient. Add Provider=SQLOLEDB to your connection string and remove
Driver={SQL Server}.
For some reason you are putting brackets around the parameters to your
stored procedure. In Transact-SQL, brackets are used to delimit
identifiers; this is to permit you have table and column names with
special charcters such as space in them. Thus, in this case your parameters
are parsed as identifiers, and obviously at least one of them is longer
than 128 which is the maximum length for the optimizer.
Unless you are into something special, you should use ' instead to delimit
the parameters, but don't rush and change this, read on instead. You
cannot pass user input directly into an SQL string like this. Say that you
actually replaced the brackets with single quotes instead. Say then that
the user enters data with a single quote in it. The result: a syntax
error. Maybe. A malicious user can use this to enter a completely
different SQL command than you had intended. Thus, you have a big security
hole. And, no, don't laugh. SQL injection is a very common means of attack
on the web today.
A simple way out is to run the intput through a procedure that double
all single quotes in the input. That is, if the user enters "O'Brien",
you pass "O''Brien" to SQL Server. This is then parsed as O'Brien.
However, much better is to use the command type adStoredProcedure and
pass the parameter values through the .Parameters collection. Then you
don't have to bother about quoting or bracketing or anything. This is
also a more effecient way to call a stored procedure.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||"Erland Sommarskog" schrieb
> However, much better is to use the command type adStoredProcedure and
> pass the parameter values through the .Parameters collection. Then you
> don't have to bother about quoting or bracketing or anything. This is
> also a more effecient way to call a stored procedure.
hmm, can you give me an example code please?
i searched around a bit for asStoredProcedure but didn't get a result for it.
perhaps it's the best and easiest way for me to handle my problem
regards
Jan
|||Jan Schmidt (histery@.gmx.net) writes:
> "Erland Sommarskog" schrieb
> hmm, can you give me an example code please? i searched around a bit for
> asStoredProcedure but didn't get a result for it. perhaps it's the
> best and easiest way for me to handle my problem
Sorry, the name of the constant is adCmdStoredProc.
I can't give a example in ASP, since I don't know ASP. This snippet is
Visual Basic, and shows how to changes the password for the user abc:
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=MyServer" & _
"Initial Catalog='tempdn'; _
"Integrated Security='SSPI';"
cnn.ConnectionTimeout = 5
cnn.Open
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "master.dbo.sp_password"
cmd.Parameters.Append _
cmd.CreateParameter("@.old", adVarChar, adParamInput, 10, "nisse")
cmd.Parameters.Append _
cmd.CreateParameter("@.new", adVarChar, adParamInput, 10, "pelle")
cmd.Parameters.Append
cmd.CreateParameter("@.login", adVarChar, adParamInput, 10, "abc")
cmd.Execute
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
> "Erland Sommarskog" schrieb
> You've got a special Webpage to read the differences between this two
> methods?
The section Deprecated Components in the MDAC Books Online (this material
is also in MSDN Library) says:
ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the
OLE DB NET Data Provider.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||"Erland Sommarskog" schrieb
> cmd.CommandType = adCmdStoredProc
Yes, i did what you said,
everything seams to be good, except, as ai said, i use asp with Javascript not
VB,
so it says there isn't any function called adCmdStoredProc
i also tried adCmdStoredProcedure and adStoredProc and adStoredProcedure and
StoredProc and StoredProcedure.
Why?
regards
Jan
|||Jan Schmidt (histery@.gmx.net) writes:
> "Erland Sommarskog" schrieb
> Yes, i did what you said, everything seams to be good, except, as ai
> said, i use asp with Javascript not VB, so it says there isn't any
> function called adCmdStoredProc i also tried adCmdStoredProcedure and
> adStoredProc and adStoredProcedure and StoredProc and StoredProcedure.
adCmdStoredProc is not a procedure but a constant.
Since I know Javascript just as equally well as I know ASP - that is, not
at all - I can't say how you get hold of these constants. But you can
always use the underlying values. You find them in the MDAC Books Online,
and all about MDAC is also in MSDN Library. If you don't have these
resources on disk, they are available on the web.
If you look around a little more, you might be able to find include files
for ADO constants to be used in Javascript.
A quick searh on Google, reveals that the value for adCmdStoredProc is 4,
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||I have Macromedia Dreamweaver and this is the code it produced to run an ADO
command for a stored procedure:
var Command1 = Server.CreateObject("ADODB.Command");
Command1.ActiveConnection = Yuor_Connection_String;
Command1.CommandText = "sp_yourStoredProc";
Command1.CommandType = 4;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Parameters.Append(Command1.CreateParamete r("@.RETURN_VALUE", 3, 4));
Command1.Parameters.Append(Command1.CreateParamete r("@.SubscriberID", 3,
1,4,local_var_SubscriberID));
Command1.Parameters.Append(Command1.CreateParamete r("@.Email", 200,
1,50,local_var_Email));
Command1.Parameters.Append(Command1.CreateParamete r("@.Name", 200,
1,50,local_var_Name));
var Recordset1 = Command1.Execute();
You'll notice of course the CommandType = 4
HPH
Andy
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.gmx.net) writes:
> adCmdStoredProc is not a procedure but a constant.
> Since I know Javascript just as equally well as I know ASP - that is, not
> at all - I can't say how you get hold of these constants. But you can
> always use the underlying values. You find them in the MDAC Books Online,
> and all about MDAC is also in MSDN Library. If you don't have these
> resources on disk, they are available on the web.
> If you look around a little more, you might be able to find include files
> for ADO constants to be used in Javascript.
> A quick searh on Google, reveals that the value for adCmdStoredProc is 4,
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.gmx.net) writes:
> adCmdStoredProc is not a procedure but a constant.
> Since I know Javascript just as equally well as I know ASP - that is, not
> at all - I can't say how you get hold of these constants. But you can
> always use the underlying values. You find them in the MDAC Books Online,
> and all about MDAC is also in MSDN Library. If you don't have these
> resources on disk, they are available on the web.
> If you look around a little more, you might be able to find include files
> for ADO constants to be used in Javascript.
Try the file adojavas.inc in C:\Program Files\Common Files\System\ado
The VBScript include is in the same dir (adovbs.inc)
Dan
I've got an ASP Script (useing Javascript) to which i send data to by POST.
This data stored in variables will be transferred to SQL stored Procedure.
As shown at bottom...
welches eine Variable bergeben
There is coming up an error...
I don't know how to solve it,
please help or give me a hint...
[asp code line 665 and + from Functions.asp]
var TopicName=String(Request.Form("TopicName"));
var TopicContent=String(Request.Form ("TopicContent"));
var strSQL="EXECUTE Topic @.TopicTitle=["+TopicName+"],
@.TopicContent=["+TopicContent+"]";
cmd.CommandText = strSQL;
cmd.Execute;
oDB_connect.close;
[/asp code]
[error page]
Fehlertyp:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (beginnend mit
'0123456789012345678901234567890123456789012345678 901234567890123456789012345678
9012345678901234567890123456789012345678901234567' ) ist zu lang. Die
Maximallnge betrgt 128.
/Functions.asp, line 669
[/error page]
[snip from Stored Procedure]
CREATE PROCEDURE dbo.Topic @.TopicTitle VARCHAR(20), @.TopicContent VARCHAR(200)
AS INSERT INTO Table.......etc
[/snip from Stored Procedure]
[posted and mailed, please reply in news]
Jan Schmidt (histery@.gmx.net) writes:
> [asp code line 665 and + from Functions.asp]
> var TopicName=String(Request.Form("TopicName"));
> var TopicContent=String(Request.Form ("TopicContent"));
> var strSQL="EXECUTE Topic @.TopicTitle=["+TopicName+"],
> @.TopicContent=["+TopicContent+"]";
> cmd.CommandText = strSQL;
> cmd.Execute;
> oDB_connect.close;
> [/asp code]
> [error page]
> Fehlertyp:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Bezeichner (beginnend mit
>
'0123456789012345678901234567890123456789012345678 90123456789012345678901234
5678
> 9012345678901234567890123456789012345678901234567' ) ist zu lang. Die
> Maximallnge betrgt 128.
> /Functions.asp, line 669
> [/error page]
Before I address you actual problem, permit me to point out that you are
using the MSDASQL provider, OLE DB over ODBC. Use the SQLOLEDB provider
instead, since this provider is directly targeted for SQL Server and more
effecient. Add Provider=SQLOLEDB to your connection string and remove
Driver={SQL Server}.
For some reason you are putting brackets around the parameters to your
stored procedure. In Transact-SQL, brackets are used to delimit
identifiers; this is to permit you have table and column names with
special charcters such as space in them. Thus, in this case your parameters
are parsed as identifiers, and obviously at least one of them is longer
than 128 which is the maximum length for the optimizer.
Unless you are into something special, you should use ' instead to delimit
the parameters, but don't rush and change this, read on instead. You
cannot pass user input directly into an SQL string like this. Say that you
actually replaced the brackets with single quotes instead. Say then that
the user enters data with a single quote in it. The result: a syntax
error. Maybe. A malicious user can use this to enter a completely
different SQL command than you had intended. Thus, you have a big security
hole. And, no, don't laugh. SQL injection is a very common means of attack
on the web today.
A simple way out is to run the intput through a procedure that double
all single quotes in the input. That is, if the user enters "O'Brien",
you pass "O''Brien" to SQL Server. This is then parsed as O'Brien.
However, much better is to use the command type adStoredProcedure and
pass the parameter values through the .Parameters collection. Then you
don't have to bother about quoting or bracketing or anything. This is
also a more effecient way to call a stored procedure.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||"Erland Sommarskog" schrieb
> However, much better is to use the command type adStoredProcedure and
> pass the parameter values through the .Parameters collection. Then you
> don't have to bother about quoting or bracketing or anything. This is
> also a more effecient way to call a stored procedure.
hmm, can you give me an example code please?
i searched around a bit for asStoredProcedure but didn't get a result for it.
perhaps it's the best and easiest way for me to handle my problem
regards
Jan
|||Jan Schmidt (histery@.gmx.net) writes:
> "Erland Sommarskog" schrieb
> hmm, can you give me an example code please? i searched around a bit for
> asStoredProcedure but didn't get a result for it. perhaps it's the
> best and easiest way for me to handle my problem
Sorry, the name of the constant is adCmdStoredProc.
I can't give a example in ASP, since I don't know ASP. This snippet is
Visual Basic, and shows how to changes the password for the user abc:
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=MyServer" & _
"Initial Catalog='tempdn'; _
"Integrated Security='SSPI';"
cnn.ConnectionTimeout = 5
cnn.Open
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "master.dbo.sp_password"
cmd.Parameters.Append _
cmd.CreateParameter("@.old", adVarChar, adParamInput, 10, "nisse")
cmd.Parameters.Append _
cmd.CreateParameter("@.new", adVarChar, adParamInput, 10, "pelle")
cmd.Parameters.Append
cmd.CreateParameter("@.login", adVarChar, adParamInput, 10, "abc")
cmd.Execute
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
> "Erland Sommarskog" schrieb
> You've got a special Webpage to read the differences between this two
> methods?
The section Deprecated Components in the MDAC Books Online (this material
is also in MSDN Library) says:
ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the
OLE DB NET Data Provider.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||"Erland Sommarskog" schrieb
> cmd.CommandType = adCmdStoredProc
Yes, i did what you said,
everything seams to be good, except, as ai said, i use asp with Javascript not
VB,
so it says there isn't any function called adCmdStoredProc
i also tried adCmdStoredProcedure and adStoredProc and adStoredProcedure and
StoredProc and StoredProcedure.
Why?
regards
Jan
|||Jan Schmidt (histery@.gmx.net) writes:
> "Erland Sommarskog" schrieb
> Yes, i did what you said, everything seams to be good, except, as ai
> said, i use asp with Javascript not VB, so it says there isn't any
> function called adCmdStoredProc i also tried adCmdStoredProcedure and
> adStoredProc and adStoredProcedure and StoredProc and StoredProcedure.
adCmdStoredProc is not a procedure but a constant.
Since I know Javascript just as equally well as I know ASP - that is, not
at all - I can't say how you get hold of these constants. But you can
always use the underlying values. You find them in the MDAC Books Online,
and all about MDAC is also in MSDN Library. If you don't have these
resources on disk, they are available on the web.
If you look around a little more, you might be able to find include files
for ADO constants to be used in Javascript.
A quick searh on Google, reveals that the value for adCmdStoredProc is 4,
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||I have Macromedia Dreamweaver and this is the code it produced to run an ADO
command for a stored procedure:
var Command1 = Server.CreateObject("ADODB.Command");
Command1.ActiveConnection = Yuor_Connection_String;
Command1.CommandText = "sp_yourStoredProc";
Command1.CommandType = 4;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Parameters.Append(Command1.CreateParamete r("@.RETURN_VALUE", 3, 4));
Command1.Parameters.Append(Command1.CreateParamete r("@.SubscriberID", 3,
1,4,local_var_SubscriberID));
Command1.Parameters.Append(Command1.CreateParamete r("@.Email", 200,
1,50,local_var_Email));
Command1.Parameters.Append(Command1.CreateParamete r("@.Name", 200,
1,50,local_var_Name));
var Recordset1 = Command1.Execute();
You'll notice of course the CommandType = 4
HPH
Andy
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.gmx.net) writes:
> adCmdStoredProc is not a procedure but a constant.
> Since I know Javascript just as equally well as I know ASP - that is, not
> at all - I can't say how you get hold of these constants. But you can
> always use the underlying values. You find them in the MDAC Books Online,
> and all about MDAC is also in MSDN Library. If you don't have these
> resources on disk, they are available on the web.
> If you look around a little more, you might be able to find include files
> for ADO constants to be used in Javascript.
> A quick searh on Google, reveals that the value for adCmdStoredProc is 4,
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns959A7C3202F0FYazorman@.127.0.0.1...
> Jan Schmidt (histery@.gmx.net) writes:
> adCmdStoredProc is not a procedure but a constant.
> Since I know Javascript just as equally well as I know ASP - that is, not
> at all - I can't say how you get hold of these constants. But you can
> always use the underlying values. You find them in the MDAC Books Online,
> and all about MDAC is also in MSDN Library. If you don't have these
> resources on disk, they are available on the web.
> If you look around a little more, you might be able to find include files
> for ADO constants to be used in Javascript.
Try the file adojavas.inc in C:\Program Files\Common Files\System\ado
The VBScript include is in the same dir (adovbs.inc)
Dan
Monday, March 12, 2012
problem dropping columns
One of our developers accidentally added a 'rowguid' column to all of our
tables (mssql 2000). I'm trying to write a script that will drop this
column from all the tables; however, I've run into a problem where I can't
drop them because there are dependant contraints/indexes. The following
code is what I have so far. Is there's a way to identify and drop all
dependancies on this column first?
DECLARE @.TableName sysname
DECLARE @.ColumnName sysname
DECLARE RowGuidColumnList CURSOR
FOR select t.name, c.name
FROM sysobjects t
JOIN syscolumns c
ON (c.id = t.id and t.type = 'U')
WHERE c.name = 'rowguid'
order by t.name, c.name
OPEN RowGuidColumnList
FETCH NEXT FROM RowGuidColumnList
INTO @.TableName, @.ColumnName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Removing rowguid column from ' + @.TableName
execute('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
FETCH NEXT FROM RowGuidColumnList
INTO @.TableName, @.ColumnName
END
CLOSE RowGuidColumnList
DEALLOCATE RowGuidColumnList
Thanks, DougYou can find information about dependencies of some
particular column from system tables:
sysconstraints,
syscolumns,
sysobjects
Take a look about this tables in BOL.
Regards
----
All information provided above AS IS
>--Original Message--
>One of our developers accidentally added a 'rowguid'
column to all of our
>tables (mssql 2000). I'm trying to write a script that
will drop this
>column from all the tables; however, I've run into a
problem where I can't
>drop them because there are dependant
contraints/indexes. The following
>code is what I have so far. Is there's a way to identify
and drop all
>dependancies on this column first?
>DECLARE @.TableName sysname
>DECLARE @.ColumnName sysname
>DECLARE RowGuidColumnList CURSOR
>FOR select t.name, c.name
>FROM sysobjects t
> JOIN syscolumns c
> ON (c.id = t.id and t.type = 'U')
>WHERE c.name = 'rowguid'
>order by t.name, c.name
>OPEN RowGuidColumnList
>FETCH NEXT FROM RowGuidColumnList
>INTO @.TableName, @.ColumnName
>WHILE @.@.FETCH_STATUS = 0
>BEGIN
> PRINT 'Removing rowguid column from ' + @.TableName
> execute('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' +
@.ColumnName)
> FETCH NEXT FROM RowGuidColumnList
> INTO @.TableName, @.ColumnName
>END
>CLOSE RowGuidColumnList
>DEALLOCATE RowGuidColumnList
>
>Thanks, Doug
>
>.
>
tables (mssql 2000). I'm trying to write a script that will drop this
column from all the tables; however, I've run into a problem where I can't
drop them because there are dependant contraints/indexes. The following
code is what I have so far. Is there's a way to identify and drop all
dependancies on this column first?
DECLARE @.TableName sysname
DECLARE @.ColumnName sysname
DECLARE RowGuidColumnList CURSOR
FOR select t.name, c.name
FROM sysobjects t
JOIN syscolumns c
ON (c.id = t.id and t.type = 'U')
WHERE c.name = 'rowguid'
order by t.name, c.name
OPEN RowGuidColumnList
FETCH NEXT FROM RowGuidColumnList
INTO @.TableName, @.ColumnName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Removing rowguid column from ' + @.TableName
execute('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
FETCH NEXT FROM RowGuidColumnList
INTO @.TableName, @.ColumnName
END
CLOSE RowGuidColumnList
DEALLOCATE RowGuidColumnList
Thanks, DougYou can find information about dependencies of some
particular column from system tables:
sysconstraints,
syscolumns,
sysobjects
Take a look about this tables in BOL.
Regards
----
All information provided above AS IS
>--Original Message--
>One of our developers accidentally added a 'rowguid'
column to all of our
>tables (mssql 2000). I'm trying to write a script that
will drop this
>column from all the tables; however, I've run into a
problem where I can't
>drop them because there are dependant
contraints/indexes. The following
>code is what I have so far. Is there's a way to identify
and drop all
>dependancies on this column first?
>DECLARE @.TableName sysname
>DECLARE @.ColumnName sysname
>DECLARE RowGuidColumnList CURSOR
>FOR select t.name, c.name
>FROM sysobjects t
> JOIN syscolumns c
> ON (c.id = t.id and t.type = 'U')
>WHERE c.name = 'rowguid'
>order by t.name, c.name
>OPEN RowGuidColumnList
>FETCH NEXT FROM RowGuidColumnList
>INTO @.TableName, @.ColumnName
>WHILE @.@.FETCH_STATUS = 0
>BEGIN
> PRINT 'Removing rowguid column from ' + @.TableName
> execute('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' +
@.ColumnName)
> FETCH NEXT FROM RowGuidColumnList
> INTO @.TableName, @.ColumnName
>END
>CLOSE RowGuidColumnList
>DEALLOCATE RowGuidColumnList
>
>Thanks, Doug
>
>.
>
Friday, March 9, 2012
Problem Deleting Duplicate Data
I have a table that contains more than 10,000 rows of
duplicate data. The script below copies the data to a temp table then
deletes from the original table. My problem is that after it runs, I now
have 122 rows of triplicate data (but dups are gone). If I rerun the script, it doesn't see the
triplicate data and returns 0 rows. I've use three different versions of
delete dup row scripts with the same result. There are no triggers or
constraints on the table, not even a primary key. What am I missing?
-/**********************************************
Delete Duplicate Data
**********************************************/
--Create temp table to hold duplicate data
CREATE TABLE #tempduplicatedata
(
[student_test_uniq] [bigint] NULL,
[test_uniq] [int] NULL,
[concept_id] [smallint] NULL,
[test_id] [varchar](12) NULL,
[questions_correct] [smallint] NULL,
[questions_count] [smallint] NULL,
[percentage_correct] [decimal](6, 3) NULL,
[concept_response_count] [smallint] NULL
)
--Identify and save dup data into temp table
INSERT INTO #tempduplicatedata
SELECT * FROM crt_concept_score
GROUP BY student_test_uniq,
test_uniq,
concept_id,
test_id,
questions_correct,
questions_count,
percentage_correct,
concept_response_count
HAVING COUNT(*) > 1
--Confirm number of dup rows
SELECT @.@.ROWCOUNT AS 'Number of Duplicate Rows'
--Delete dup from original table
DELETE FROM crt_concept_score
FROM crt_concept_score
INNER JOIN #tempduplicatedata
ON crt_concept_score.student_test_uniq = #tempduplicatedata.student_test_uniq
AND crt_concept_score.test_uniq = #tempduplicatedata.test_uniq
AND crt_concept_score.concept_id = #tempduplicatedata.concept_id
AND crt_concept_score.test_id = #tempduplicatedata.test_id
AND crt_concept_score.questions_correct = #tempduplicatedata.questions_correct
AND crt_concept_score.questions_count = #tempduplicatedata.questions_count
AND crt_concept_score.percentage_correct = #tempduplicatedata.percentage_correct
AND crt_concept_score.concept_response_count = #tempduplicatedata.concept_response_count
--Insert the delete data back
INSERT INTO crt_concept_score
SELECT * FROM #tempduplicatedata
--Check for dup data.
SELECT * FROM crt_concept_score
GROUP BY student_test_uniq,
test_uniq,
concept_id,
test_id,
questions_correct,
questions_count,
percentage_correct,
concept_response_count
HAVING COUNT(*) > 1
--Check table
-- SELECT * FROM crt_concept_score
--Drop temp table
DROP TABLE #tempduplicatedata
GO
i've seen something like this in one of Kat's post
here's the link
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=746636&SiteID=1
problem creation, script with create view
I created a script like this :
use tk_main
GO
if exists (select table_name from information_schema.views where table_name
= 'V_08701')
drop view V_08701
GO
CREATE VIEW V_08701 (D_ATE,NO_ENVOI,ARRIVEE,TOT_COLIS) AS SELECT
D_ATE,NO_ENVOI,ARRIVEE,SUM(NB_COLIS) FROM POSBAR_L GROUP BY
D_ATE,NO_ENVOI,ARRIVEE
GO
grant all on V_08701 to public
GO
if exists (select table_name from information_schema.views where table_name
= 'V95001') drop view V_95001
GO
CREATE VIEW V_95001 (NO_FAC,NO_ENVOI,ARRIVEE,NOMBRE) AS SELECT
NO_FAC,NO_ENVOI_TK,ARRIVEE,COUNT(NO_ENVO
I_TK) FROM FACTURE_IMP_D GROUP BY
NO_FAC,NO_ENVOI_TK,ARRIVEE
GO
if exists (select table_name from information_schema.views where table_name
= 'V_08601') drop view V_08601
GO
CREATE VIEW V_08601
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO
IDS,TYP_SCANNAGE) AS SELECT ARRIVEE.DTE_ARR_DEP, ARRIVEE.TRANSPORTEUR,
ARRIVEE.ARRIVEE,POSBAR_E.NO_ENVOI,
ARRIVEE.OPERATION_C_D,ARRIVEE.ETAT_ARRIVEE, POSBAR_E.ETAT,
POSBAR_E.POIDS,POSBAR_E.TYP_SCANNAGE FROM ARRIVEE, POSBAR_E WHERE
ARRIVEE.ARRIVEE = POSBAR_E.ARRIVEE
GO
but the analyser doesn't like this script.
Can someboady help me out.
Thanks in advance
RalfWhat error messages do you get?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ralf Meuser" <rmeuser@.free.fr> wrote in message
news:40618606$0$7376$626a14ce@.news.free.fr...
> I created a script like this :
> use tk_main
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V_08701')
> drop view V_08701
> GO
> CREATE VIEW V_08701 (D_ATE,NO_ENVOI,ARRIVEE,TOT_COLIS) AS SELECT
> D_ATE,NO_ENVOI,ARRIVEE,SUM(NB_COLIS) FROM POSBAR_L GROUP BY
> D_ATE,NO_ENVOI,ARRIVEE
> GO
> grant all on V_08701 to public
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V95001') drop view V_95001
> GO
> CREATE VIEW V_95001 (NO_FAC,NO_ENVOI,ARRIVEE,NOMBRE) AS SELECT
> NO_FAC,NO_ENVOI_TK,ARRIVEE,COUNT(NO_ENVO
I_TK) FROM FACTURE_IMP_D GROUP BY
> NO_FAC,NO_ENVOI_TK,ARRIVEE
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V_08601') drop view V_08601
> GO
> CREATE VIEW V_08601
>
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO[col
or=darkred]
> IDS,TYP_SCANNAGE) AS SELECT ARRIVEE.DTE_ARR_DEP, ARRIVEE.TRANSPORTEUR,
> ARRIVEE.ARRIVEE,POSBAR_E.NO_ENVOI,
> ARRIVEE.OPERATION_C_D,ARRIVEE.ETAT_ARRIVEE, POSBAR_E.ETAT,
> POSBAR_E.POIDS,POSBAR_E.TYP_SCANNAGE FROM ARRIVEE, POSBAR_E WHERE
> ARRIVEE.ARRIVEE = POSBAR_E.ARRIVEE
> GO
> --
> but the analyser doesn't like this script.
> Can someboady help me out.
> Thanks in advance
> Ralf
>
>[/color]|||Sorry I forgot to sedn the error :
Serveur : Msg 170, Niveau 15, tat 1, Procdure V_08701, Ligne 2
Ligne 2 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 1
Ligne 1 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 2
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 3
Ligne 3 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 5
Ligne 5 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 6
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 7
Ligne 7 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 9
Ligne 9 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 10
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 11
Ligne 11 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 13
Ligne 13 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 14
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Ralf
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
dans le message de news:em9NhSaEEHA.3980@.TK2MSFTNGP09.phx.gbl...
> What error messages do you get?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Ralf Meuser" <rmeuser@.free.fr> wrote in message
> news:40618606$0$7376$626a14ce@.news.free.fr...
> table_name
> table_name
BY
> table_name
>
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO[col
or=darkred]
>|||Since this is an English speaking newsgroup, it would be helpful if you woul
d translate the French messages
instead of letting us do that.
I don't see a problem with this, unless you actually have a line-break in th
e middle of a column name in your
code as well (I assume it is inserted by your newsreader).
Perhaps someone has changed the batch separator (from GO to something else)
in Query Analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ralf Meuser" <rmeuser@.free.fr> wrote in message news:406197b3$0$309$626a14ce@.news.free.fr.
.
> Sorry I forgot to sedn the error :
> Serveur : Msg 170, Niveau 15, tat 1, Procdure V_08701, Ligne 2
> Ligne 2 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 1
> Ligne 1 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 2
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 3
> Ligne 3 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 5
> Ligne 5 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 6
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 7
> Ligne 7 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 9
> Ligne 9 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 10
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 11
> Ligne 11 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 13
> Ligne 13 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 14
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
>
> Ralf
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
> dans le message de news:em9NhSaEEHA.3980@.TK2MSFTNGP09.phx.gbl...
> BY
> (DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,
PO
>
use tk_main
GO
if exists (select table_name from information_schema.views where table_name
= 'V_08701')
drop view V_08701
GO
CREATE VIEW V_08701 (D_ATE,NO_ENVOI,ARRIVEE,TOT_COLIS) AS SELECT
D_ATE,NO_ENVOI,ARRIVEE,SUM(NB_COLIS) FROM POSBAR_L GROUP BY
D_ATE,NO_ENVOI,ARRIVEE
GO
grant all on V_08701 to public
GO
if exists (select table_name from information_schema.views where table_name
= 'V95001') drop view V_95001
GO
CREATE VIEW V_95001 (NO_FAC,NO_ENVOI,ARRIVEE,NOMBRE) AS SELECT
NO_FAC,NO_ENVOI_TK,ARRIVEE,COUNT(NO_ENVO
I_TK) FROM FACTURE_IMP_D GROUP BY
NO_FAC,NO_ENVOI_TK,ARRIVEE
GO
if exists (select table_name from information_schema.views where table_name
= 'V_08601') drop view V_08601
GO
CREATE VIEW V_08601
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO
IDS,TYP_SCANNAGE) AS SELECT ARRIVEE.DTE_ARR_DEP, ARRIVEE.TRANSPORTEUR,
ARRIVEE.ARRIVEE,POSBAR_E.NO_ENVOI,
ARRIVEE.OPERATION_C_D,ARRIVEE.ETAT_ARRIVEE, POSBAR_E.ETAT,
POSBAR_E.POIDS,POSBAR_E.TYP_SCANNAGE FROM ARRIVEE, POSBAR_E WHERE
ARRIVEE.ARRIVEE = POSBAR_E.ARRIVEE
GO
but the analyser doesn't like this script.
Can someboady help me out.
Thanks in advance
RalfWhat error messages do you get?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ralf Meuser" <rmeuser@.free.fr> wrote in message
news:40618606$0$7376$626a14ce@.news.free.fr...
> I created a script like this :
> use tk_main
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V_08701')
> drop view V_08701
> GO
> CREATE VIEW V_08701 (D_ATE,NO_ENVOI,ARRIVEE,TOT_COLIS) AS SELECT
> D_ATE,NO_ENVOI,ARRIVEE,SUM(NB_COLIS) FROM POSBAR_L GROUP BY
> D_ATE,NO_ENVOI,ARRIVEE
> GO
> grant all on V_08701 to public
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V95001') drop view V_95001
> GO
> CREATE VIEW V_95001 (NO_FAC,NO_ENVOI,ARRIVEE,NOMBRE) AS SELECT
> NO_FAC,NO_ENVOI_TK,ARRIVEE,COUNT(NO_ENVO
I_TK) FROM FACTURE_IMP_D GROUP BY
> NO_FAC,NO_ENVOI_TK,ARRIVEE
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V_08601') drop view V_08601
> GO
> CREATE VIEW V_08601
>
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO[col
or=darkred]
> IDS,TYP_SCANNAGE) AS SELECT ARRIVEE.DTE_ARR_DEP, ARRIVEE.TRANSPORTEUR,
> ARRIVEE.ARRIVEE,POSBAR_E.NO_ENVOI,
> ARRIVEE.OPERATION_C_D,ARRIVEE.ETAT_ARRIVEE, POSBAR_E.ETAT,
> POSBAR_E.POIDS,POSBAR_E.TYP_SCANNAGE FROM ARRIVEE, POSBAR_E WHERE
> ARRIVEE.ARRIVEE = POSBAR_E.ARRIVEE
> GO
> --
> but the analyser doesn't like this script.
> Can someboady help me out.
> Thanks in advance
> Ralf
>
>[/color]|||Sorry I forgot to sedn the error :
Serveur : Msg 170, Niveau 15, tat 1, Procdure V_08701, Ligne 2
Ligne 2 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 1
Ligne 1 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 2
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 3
Ligne 3 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 5
Ligne 5 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 6
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 7
Ligne 7 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 9
Ligne 9 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 10
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 11
Ligne 11 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 13
Ligne 13 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 14
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Ralf
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
dans le message de news:em9NhSaEEHA.3980@.TK2MSFTNGP09.phx.gbl...
> What error messages do you get?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Ralf Meuser" <rmeuser@.free.fr> wrote in message
> news:40618606$0$7376$626a14ce@.news.free.fr...
> table_name
> table_name
BY
> table_name
>
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO[col
or=darkred]
>|||Since this is an English speaking newsgroup, it would be helpful if you woul
d translate the French messages
instead of letting us do that.
I don't see a problem with this, unless you actually have a line-break in th
e middle of a column name in your
code as well (I assume it is inserted by your newsreader).
Perhaps someone has changed the batch separator (from GO to something else)
in Query Analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ralf Meuser" <rmeuser@.free.fr> wrote in message news:406197b3$0$309$626a14ce@.news.free.fr.
.
> Sorry I forgot to sedn the error :
> Serveur : Msg 170, Niveau 15, tat 1, Procdure V_08701, Ligne 2
> Ligne 2 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 1
> Ligne 1 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 2
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 3
> Ligne 3 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 5
> Ligne 5 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 6
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 7
> Ligne 7 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 9
> Ligne 9 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 10
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 11
> Ligne 11 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 13
> Ligne 13 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 14
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
>
> Ralf
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
> dans le message de news:em9NhSaEEHA.3980@.TK2MSFTNGP09.phx.gbl...
> BY
> (DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,
PO
>
Wednesday, March 7, 2012
Problem creating script for automating ALTER INDEX maintenance
I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
Self Study guide, working on managing index fragmentation.
What I am trying to do is collect data from sys.dt_db_index_physical_stats
DMF. I'm creating a temporary table that houses the table name in the format
"schema.table". Then I am attempting to put some of that information into a
cursor, and run an ALTER INDEX on all tables that match the criteria I need.
Following is the script I've come up with:
set ansi_nulls on
set quoted_identifier on
if exists (select * from sys.objects where name='tmp_TableIndex')
begin
drop table tmp_TableIndex
end
create table tmp_TableIndex
(
TableNamevarchar(50),
AvgFragInPercentint,
AvgPageSpaceUsedInPercentint
)
insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
'TableName',
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats
(
db_id(db_name()), null, null, null, 'detailed'
)
dt
join sys.objects sc
on sc.object_id=dt.object_id
join sys.indexes si
on si.object_id=dt.object_id
and si.index_id=dt.index_id
where dt.index_ID<>0
and dt.avg_fragmentation_in_percent between 10 and 15
or dt.avg_page_space_used_in_percent between 60 and 75
declare c_Indexreorg cursor
for
select distinct TableName from tmp_TableIndex
open c_Indexreorg
fetch next from c_Indexreorg
while (@.@.FETCH_STATUS=0)
begin
ALTER index ALL on [TableName]
REORGANIZE
fetch next from c_Indexreorg
end
close c_Indexreorg
deallocate c_Indexreorg
The table is created, but I get the following error:
Msg 1088, Level 16, State 9, Line 46
Cannot find the object "TableName" because it does not exist or you do not
have permissions.
I made sure the tablename in the temp table was in schema.tablename format,
but that still didn't help.
What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
Richard Tocci
College Station, TX
Richard
Do you run this script under what account?
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some
> time.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the
> format
> "schema.table". Then I am attempting to put some of that information into
> a
> cursor, and run an ALTER INDEX on all tables that match the criteria I
> need.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename
> format,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> College Station, TX
|||First, I want to recommend an already prepared script for you. See this topic in Books Online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
If you scroll down towards the end, you will find a script that does exactly what you want to do. In
addition, the script will only reoorganize the index if it is fragmented in the first place.
To answer why your script doesn't work:
> fetch next from c_Indexreorg
You don't fetch the column from the cursor into any variables. You should have something like:
> fetch next from c_Indexreorg INTO @.v1, @.v1, ...
> ALTER index ALL on [TableName]
Here you have hardcoded the table name to "TableName", and you probably don't have a table with that
name in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the format
> "schema.table". Then I am attempting to put some of that information into a
> cursor, and run an ALTER INDEX on all tables that match the criteria I need.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename format,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> College Station, TX
|||Forgive my curiosity, but below seems surprising:
> and dt.avg_fragmentation_in_percent between 10 and 15
Why wouldn't you want to reorg if the fragmentation level is high?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...[vbcol=seagreen]
> After reading the Books Online help file, I modified the script and now it
> works. Here it is:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> SchemaName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) as 'SchemaName',
> object_name(dt.object_id) as 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare @.table_name varchar(50),
> @.schema_name varchar(50),
> @.command varchar(100)
> declare c_Indexreorg cursor
> for
> select distinct SchemaName, TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg into @.table_name, @.schema_name
> while (@.@.FETCH_STATUS=0)
> begin
> set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
> REORGANIZE'
> exec (@.command)
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
>
> Once I created a couple more variables (I had the @.table_name variable on a
> previous iteration of the script but took it out, thinking it was making it
> to complex), and not slamming the schema name and table name together in my
> temporary table, it seemed to work better.
> THanks for all that responded.
> --
> Richard Tocci
> College Station, TX
>
> "Tibor Karaszi" wrote:
|||Actually, that should be an OR, not an AND. I was going by the 70-431 self
training book, page 453, at the bottom.
Richard Tocci
College Station, TX
"Tibor Karaszi" wrote:
> Forgive my curiosity, but below seems surprising:
>
> Why wouldn't you want to reorg if the fragmentation level is high?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...
>
|||Guys, what book are you referring to? I am studying for the exam and am
looking for all the help I can get.
Thanks!
-Richard K
"Richard Tocci" wrote:
[vbcol=seagreen]
> Actually, that should be an OR, not an AND. I was going by the 70-431 self
> training book, page 453, at the bottom.
> --
> Richard Tocci
> College Station, TX
>
> "Tibor Karaszi" wrote:
In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
Self Study guide, working on managing index fragmentation.
What I am trying to do is collect data from sys.dt_db_index_physical_stats
DMF. I'm creating a temporary table that houses the table name in the format
"schema.table". Then I am attempting to put some of that information into a
cursor, and run an ALTER INDEX on all tables that match the criteria I need.
Following is the script I've come up with:
set ansi_nulls on
set quoted_identifier on
if exists (select * from sys.objects where name='tmp_TableIndex')
begin
drop table tmp_TableIndex
end
create table tmp_TableIndex
(
TableNamevarchar(50),
AvgFragInPercentint,
AvgPageSpaceUsedInPercentint
)
insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
'TableName',
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats
(
db_id(db_name()), null, null, null, 'detailed'
)
dt
join sys.objects sc
on sc.object_id=dt.object_id
join sys.indexes si
on si.object_id=dt.object_id
and si.index_id=dt.index_id
where dt.index_ID<>0
and dt.avg_fragmentation_in_percent between 10 and 15
or dt.avg_page_space_used_in_percent between 60 and 75
declare c_Indexreorg cursor
for
select distinct TableName from tmp_TableIndex
open c_Indexreorg
fetch next from c_Indexreorg
while (@.@.FETCH_STATUS=0)
begin
ALTER index ALL on [TableName]
REORGANIZE
fetch next from c_Indexreorg
end
close c_Indexreorg
deallocate c_Indexreorg
The table is created, but I get the following error:
Msg 1088, Level 16, State 9, Line 46
Cannot find the object "TableName" because it does not exist or you do not
have permissions.
I made sure the tablename in the temp table was in schema.tablename format,
but that still didn't help.
What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
Richard Tocci
College Station, TX
Richard
Do you run this script under what account?
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some
> time.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the
> format
> "schema.table". Then I am attempting to put some of that information into
> a
> cursor, and run an ALTER INDEX on all tables that match the criteria I
> need.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename
> format,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> College Station, TX
|||First, I want to recommend an already prepared script for you. See this topic in Books Online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
If you scroll down towards the end, you will find a script that does exactly what you want to do. In
addition, the script will only reoorganize the index if it is fragmented in the first place.
To answer why your script doesn't work:
> fetch next from c_Indexreorg
You don't fetch the column from the cursor into any variables. You should have something like:
> fetch next from c_Indexreorg INTO @.v1, @.v1, ...
> ALTER index ALL on [TableName]
Here you have hardcoded the table name to "TableName", and you probably don't have a table with that
name in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the format
> "schema.table". Then I am attempting to put some of that information into a
> cursor, and run an ALTER INDEX on all tables that match the criteria I need.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename format,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> College Station, TX
|||Forgive my curiosity, but below seems surprising:
> and dt.avg_fragmentation_in_percent between 10 and 15
Why wouldn't you want to reorg if the fragmentation level is high?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...[vbcol=seagreen]
> After reading the Books Online help file, I modified the script and now it
> works. Here it is:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> SchemaName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) as 'SchemaName',
> object_name(dt.object_id) as 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare @.table_name varchar(50),
> @.schema_name varchar(50),
> @.command varchar(100)
> declare c_Indexreorg cursor
> for
> select distinct SchemaName, TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg into @.table_name, @.schema_name
> while (@.@.FETCH_STATUS=0)
> begin
> set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
> REORGANIZE'
> exec (@.command)
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
>
> Once I created a couple more variables (I had the @.table_name variable on a
> previous iteration of the script but took it out, thinking it was making it
> to complex), and not slamming the schema name and table name together in my
> temporary table, it seemed to work better.
> THanks for all that responded.
> --
> Richard Tocci
> College Station, TX
>
> "Tibor Karaszi" wrote:
|||Actually, that should be an OR, not an AND. I was going by the 70-431 self
training book, page 453, at the bottom.
Richard Tocci
College Station, TX
"Tibor Karaszi" wrote:
> Forgive my curiosity, but below seems surprising:
>
> Why wouldn't you want to reorg if the fragmentation level is high?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...
>
|||Guys, what book are you referring to? I am studying for the exam and am
looking for all the help I can get.
Thanks!
-Richard K
"Richard Tocci" wrote:
[vbcol=seagreen]
> Actually, that should be an OR, not an AND. I was going by the 70-431 self
> training book, page 453, at the bottom.
> --
> Richard Tocci
> College Station, TX
>
> "Tibor Karaszi" wrote:
Problem creating script for automating ALTER INDEX maintenance
I'm new to SQL 2005, but have been working with SQL 2000 for quite some time
.
In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
Self Study guide, working on managing index fragmentation.
What I am trying to do is collect data from sys.dt_db_index_physical_stats
DMF. I'm creating a temporary table that houses the table name in the forma
t
"schema.table". Then I am attempting to put some of that information into a
cursor, and run an ALTER INDEX on all tables that match the criteria I need.
Following is the script I've come up with:
set ansi_nulls on
set quoted_identifier on
if exists (select * from sys.objects where name='tmp_TableIndex')
begin
drop table tmp_TableIndex
end
create table tmp_TableIndex
(
TableName varchar(50),
AvgFragInPercent int,
AvgPageSpaceUsedInPercent int
)
insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent
)
select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
'TableName',
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats
(
db_id(db_name()), null, null, null, 'detailed'
)
dt
join sys.objects sc
on sc.object_id=dt.object_id
join sys.indexes si
on si.object_id=dt.object_id
and si.index_id=dt.index_id
where dt.index_ID<>0
and dt.avg_fragmentation_in_percent between 10 and 15
or dt.avg_page_space_used_in_percent between 60 and 75
declare c_Indexreorg cursor
for
select distinct TableName from tmp_TableIndex
open c_Indexreorg
fetch next from c_Indexreorg
while (@.@.FETCH_STATUS=0)
begin
ALTER index ALL on [TableName]
REORGANIZE
fetch next from c_Indexreorg
end
close c_Indexreorg
deallocate c_Indexreorg
The table is created, but I get the following error:
Msg 1088, Level 16, State 9, Line 46
Cannot find the object "TableName" because it does not exist or you do not
have permissions.
I made sure the tablename in the temp table was in schema.tablename format,
but that still didn't help.
What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
Richard Tocci
College Station, TXRichard
Do you run this script under what account?
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some
> time.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the
> format
> "schema.table". Then I am attempting to put some of that information into
> a
> cursor, and run an ALTER INDEX on all tables that match the criteria I
> need.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename
> format,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> college Station, TX|||First, I want to recommend an already prepared script for you. See this topi
c in Books Online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-
e57702230613.htm
If you scroll down towards the end, you will find a script that does exactly
what you want to do. In
addition, the script will only reoorganize the index if it is fragmented in
the first place.
To answer why your script doesn't work:
> fetch next from c_Indexreorg
You don't fetch the column from the cursor into any variables. You should ha
ve something like:
> fetch next from c_Indexreorg INTO @.v1, @.v1, ...
> ALTER index ALL on [TableName]
Here you have hardcoded the table name to "TableName", and you probably don'
t have a table with that
name in the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some ti
me.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the for
mat
> "schema.table". Then I am attempting to put some of that information into
a
> cursor, and run an ALTER INDEX on all tables that match the criteria I nee
d.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPerce
nt)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename format
,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> college Station, TX|||After reading the Books Online help file, I modified the script and now it
works. Here it is:
set ansi_nulls on
set quoted_identifier on
if exists (select * from sys.objects where name='tmp_TableIndex')
begin
drop table tmp_TableIndex
end
create table tmp_TableIndex
(
TableName varchar(50),
SchemaName varchar(50),
AvgFragInPercent int,
AvgPageSpaceUsedInPercent int
)
insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
AvgPageSpaceUsedInPercent)
select schema_name(sc.schema_id) as 'SchemaName',
object_name(dt.object_id) as 'TableName',
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats
(
db_id(db_name()), null, null, null, 'detailed'
)
dt
join sys.objects sc
on sc.object_id=dt.object_id
join sys.indexes si
on si.object_id=dt.object_id
and si.index_id=dt.index_id
where dt.index_ID<>0
and dt.avg_fragmentation_in_percent between 10 and 15
or dt.avg_page_space_used_in_percent between 60 and 75
declare @.table_name varchar(50),
@.schema_name varchar(50),
@.command varchar(100)
declare c_Indexreorg cursor
for
select distinct SchemaName, TableName from tmp_TableIndex
open c_Indexreorg
fetch next from c_Indexreorg into @.table_name, @.schema_name
while (@.@.FETCH_STATUS=0)
begin
set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
REORGANIZE'
exec (@.command)
fetch next from c_Indexreorg
end
close c_Indexreorg
deallocate c_Indexreorg
Once I created a couple more variables (I had the @.table_name variable on a
previous iteration of the script but took it out, thinking it was making it
to complex), and not slamming the schema name and table name together in my
temporary table, it seemed to work better.
THanks for all that responded.
--
Richard Tocci
College Station, TX
"Tibor Karaszi" wrote:
> First, I want to recommend an already prepared script for you. See this to
pic in Books Online:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2
d-e57702230613.htm
> If you scroll down towards the end, you will find a script that does exact
ly what you want to do. In
> addition, the script will only reoorganize the index if it is fragmented i
n the first place.
>
> To answer why your script doesn't work:
>
> You don't fetch the column from the cursor into any variables. You should
have something like:
>
>
> Here you have hardcoded the table name to "TableName", and you probably do
n't have a table with that
> name in the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
>
>|||Forgive my curiosity, but below seems surprising:
> and dt.avg_fragmentation_in_percent between 10 and 15
Why wouldn't you want to reorg if the fragmentation level is high?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...[vbcol=seagreen]
> After reading the Books Online help file, I modified the script and now it
> works. Here it is:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> SchemaName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) as 'SchemaName',
> object_name(dt.object_id) as 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare @.table_name varchar(50),
> @.schema_name varchar(50),
> @.command varchar(100)
> declare c_Indexreorg cursor
> for
> select distinct SchemaName, TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg into @.table_name, @.schema_name
> while (@.@.FETCH_STATUS=0)
> begin
> set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
> REORGANIZE'
> exec (@.command)
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
>
> Once I created a couple more variables (I had the @.table_name variable on
a
> previous iteration of the script but took it out, thinking it was making i
t
> to complex), and not slamming the schema name and table name together in m
y
> temporary table, it seemed to work better.
> THanks for all that responded.
> --
> Richard Tocci
> college Station, TX
>
> "Tibor Karaszi" wrote:
>|||Actually, that should be an OR, not an AND. I was going by the 70-431 self
training book, page 453, at the bottom.
--
Richard Tocci
College Station, TX
"Tibor Karaszi" wrote:
> Forgive my curiosity, but below seems surprising:
>
> Why wouldn't you want to reorg if the fragmentation level is high?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...
>|||Guys, what book are you referring to? I am studying for the exam and am
looking for all the help I can get.
Thanks!
-Richard K
"Richard Tocci" wrote:
[vbcol=seagreen]
> Actually, that should be an OR, not an AND. I was going by the 70-431 sel
f
> training book, page 453, at the bottom.
> --
> Richard Tocci
> college Station, TX
>
> "Tibor Karaszi" wrote:
>
.
In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
Self Study guide, working on managing index fragmentation.
What I am trying to do is collect data from sys.dt_db_index_physical_stats
DMF. I'm creating a temporary table that houses the table name in the forma
t
"schema.table". Then I am attempting to put some of that information into a
cursor, and run an ALTER INDEX on all tables that match the criteria I need.
Following is the script I've come up with:
set ansi_nulls on
set quoted_identifier on
if exists (select * from sys.objects where name='tmp_TableIndex')
begin
drop table tmp_TableIndex
end
create table tmp_TableIndex
(
TableName varchar(50),
AvgFragInPercent int,
AvgPageSpaceUsedInPercent int
)
insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent
)
select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
'TableName',
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats
(
db_id(db_name()), null, null, null, 'detailed'
)
dt
join sys.objects sc
on sc.object_id=dt.object_id
join sys.indexes si
on si.object_id=dt.object_id
and si.index_id=dt.index_id
where dt.index_ID<>0
and dt.avg_fragmentation_in_percent between 10 and 15
or dt.avg_page_space_used_in_percent between 60 and 75
declare c_Indexreorg cursor
for
select distinct TableName from tmp_TableIndex
open c_Indexreorg
fetch next from c_Indexreorg
while (@.@.FETCH_STATUS=0)
begin
ALTER index ALL on [TableName]
REORGANIZE
fetch next from c_Indexreorg
end
close c_Indexreorg
deallocate c_Indexreorg
The table is created, but I get the following error:
Msg 1088, Level 16, State 9, Line 46
Cannot find the object "TableName" because it does not exist or you do not
have permissions.
I made sure the tablename in the temp table was in schema.tablename format,
but that still didn't help.
What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
Richard Tocci
College Station, TXRichard
Do you run this script under what account?
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some
> time.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the
> format
> "schema.table". Then I am attempting to put some of that information into
> a
> cursor, and run an ALTER INDEX on all tables that match the criteria I
> need.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename
> format,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> college Station, TX|||First, I want to recommend an already prepared script for you. See this topi
c in Books Online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-
e57702230613.htm
If you scroll down towards the end, you will find a script that does exactly
what you want to do. In
addition, the script will only reoorganize the index if it is fragmented in
the first place.
To answer why your script doesn't work:
> fetch next from c_Indexreorg
You don't fetch the column from the cursor into any variables. You should ha
ve something like:
> fetch next from c_Indexreorg INTO @.v1, @.v1, ...
> ALTER index ALL on [TableName]
Here you have hardcoded the table name to "TableName", and you probably don'
t have a table with that
name in the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some ti
me.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the for
mat
> "schema.table". Then I am attempting to put some of that information into
a
> cursor, and run an ALTER INDEX on all tables that match the criteria I nee
d.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPerce
nt)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename format
,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> college Station, TX|||After reading the Books Online help file, I modified the script and now it
works. Here it is:
set ansi_nulls on
set quoted_identifier on
if exists (select * from sys.objects where name='tmp_TableIndex')
begin
drop table tmp_TableIndex
end
create table tmp_TableIndex
(
TableName varchar(50),
SchemaName varchar(50),
AvgFragInPercent int,
AvgPageSpaceUsedInPercent int
)
insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
AvgPageSpaceUsedInPercent)
select schema_name(sc.schema_id) as 'SchemaName',
object_name(dt.object_id) as 'TableName',
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats
(
db_id(db_name()), null, null, null, 'detailed'
)
dt
join sys.objects sc
on sc.object_id=dt.object_id
join sys.indexes si
on si.object_id=dt.object_id
and si.index_id=dt.index_id
where dt.index_ID<>0
and dt.avg_fragmentation_in_percent between 10 and 15
or dt.avg_page_space_used_in_percent between 60 and 75
declare @.table_name varchar(50),
@.schema_name varchar(50),
@.command varchar(100)
declare c_Indexreorg cursor
for
select distinct SchemaName, TableName from tmp_TableIndex
open c_Indexreorg
fetch next from c_Indexreorg into @.table_name, @.schema_name
while (@.@.FETCH_STATUS=0)
begin
set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
REORGANIZE'
exec (@.command)
fetch next from c_Indexreorg
end
close c_Indexreorg
deallocate c_Indexreorg
Once I created a couple more variables (I had the @.table_name variable on a
previous iteration of the script but took it out, thinking it was making it
to complex), and not slamming the schema name and table name together in my
temporary table, it seemed to work better.
THanks for all that responded.
--
Richard Tocci
College Station, TX
"Tibor Karaszi" wrote:
> First, I want to recommend an already prepared script for you. See this to
pic in Books Online:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2
d-e57702230613.htm
> If you scroll down towards the end, you will find a script that does exact
ly what you want to do. In
> addition, the script will only reoorganize the index if it is fragmented i
n the first place.
>
> To answer why your script doesn't work:
>
> You don't fetch the column from the cursor into any variables. You should
have something like:
>
>
> Here you have hardcoded the table name to "TableName", and you probably do
n't have a table with that
> name in the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
>
>|||Forgive my curiosity, but below seems surprising:
> and dt.avg_fragmentation_in_percent between 10 and 15
Why wouldn't you want to reorg if the fragmentation level is high?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...[vbcol=seagreen]
> After reading the Books Online help file, I modified the script and now it
> works. Here it is:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> SchemaName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) as 'SchemaName',
> object_name(dt.object_id) as 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare @.table_name varchar(50),
> @.schema_name varchar(50),
> @.command varchar(100)
> declare c_Indexreorg cursor
> for
> select distinct SchemaName, TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg into @.table_name, @.schema_name
> while (@.@.FETCH_STATUS=0)
> begin
> set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
> REORGANIZE'
> exec (@.command)
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
>
> Once I created a couple more variables (I had the @.table_name variable on
a
> previous iteration of the script but took it out, thinking it was making i
t
> to complex), and not slamming the schema name and table name together in m
y
> temporary table, it seemed to work better.
> THanks for all that responded.
> --
> Richard Tocci
> college Station, TX
>
> "Tibor Karaszi" wrote:
>|||Actually, that should be an OR, not an AND. I was going by the 70-431 self
training book, page 453, at the bottom.
--
Richard Tocci
College Station, TX
"Tibor Karaszi" wrote:
> Forgive my curiosity, but below seems surprising:
>
> Why wouldn't you want to reorg if the fragmentation level is high?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...
>|||Guys, what book are you referring to? I am studying for the exam and am
looking for all the help I can get.
Thanks!
-Richard K
"Richard Tocci" wrote:
[vbcol=seagreen]
> Actually, that should be an OR, not an AND. I was going by the 70-431 sel
f
> training book, page 453, at the bottom.
> --
> Richard Tocci
> college Station, TX
>
> "Tibor Karaszi" wrote:
>
Problem creating script for automating ALTER INDEX maintenance
I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
Self Study guide, working on managing index fragmentation.
What I am trying to do is collect data from sys.dt_db_index_physical_stats
DMF. I'm creating a temporary table that houses the table name in the format
"schema.table". Then I am attempting to put some of that information into a
cursor, and run an ALTER INDEX on all tables that match the criteria I need.
Following is the script I've come up with:
set ansi_nulls on
set quoted_identifier on
if exists (select * from sys.objects where name='tmp_TableIndex')
begin
drop table tmp_TableIndex
end
create table tmp_TableIndex
(
TableName varchar(50),
AvgFragInPercent int,
AvgPageSpaceUsedInPercent int
)
insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
'TableName',
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats
(
db_id(db_name()), null, null, null, 'detailed'
)
dt
join sys.objects sc
on sc.object_id=dt.object_id
join sys.indexes si
on si.object_id=dt.object_id
and si.index_id=dt.index_id
where dt.index_ID<>0
and dt.avg_fragmentation_in_percent between 10 and 15
or dt.avg_page_space_used_in_percent between 60 and 75
declare c_Indexreorg cursor
for
select distinct TableName from tmp_TableIndex
open c_Indexreorg
fetch next from c_Indexreorg
while (@.@.FETCH_STATUS=0)
begin
ALTER index ALL on [TableName]
REORGANIZE
fetch next from c_Indexreorg
end
close c_Indexreorg
deallocate c_Indexreorg
The table is created, but I get the following error:
Msg 1088, Level 16, State 9, Line 46
Cannot find the object "TableName" because it does not exist or you do not
have permissions.
I made sure the tablename in the temp table was in schema.tablename format,
but that still didn't help.
What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
--
Richard Tocci
College Station, TXRichard
Do you run this script under what account?
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some
> time.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the
> format
> "schema.table". Then I am attempting to put some of that information into
> a
> cursor, and run an ALTER INDEX on all tables that match the criteria I
> need.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename
> format,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> College Station, TX|||First, I want to recommend an already prepared script for you. See this topic in Books Online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
If you scroll down towards the end, you will find a script that does exactly what you want to do. In
addition, the script will only reoorganize the index if it is fragmented in the first place.
To answer why your script doesn't work:
> fetch next from c_Indexreorg
You don't fetch the column from the cursor into any variables. You should have something like:
> fetch next from c_Indexreorg INTO @.v1, @.v1, ...
> ALTER index ALL on [TableName]
Here you have hardcoded the table name to "TableName", and you probably don't have a table with that
name in the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the format
> "schema.table". Then I am attempting to put some of that information into a
> cursor, and run an ALTER INDEX on all tables that match the criteria I need.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename format,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> College Station, TX|||After reading the Books Online help file, I modified the script and now it
works. Here it is:
set ansi_nulls on
set quoted_identifier on
if exists (select * from sys.objects where name='tmp_TableIndex')
begin
drop table tmp_TableIndex
end
create table tmp_TableIndex
(
TableName varchar(50),
SchemaName varchar(50),
AvgFragInPercent int,
AvgPageSpaceUsedInPercent int
)
insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
AvgPageSpaceUsedInPercent)
select schema_name(sc.schema_id) as 'SchemaName',
object_name(dt.object_id) as 'TableName',
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats
(
db_id(db_name()), null, null, null, 'detailed'
)
dt
join sys.objects sc
on sc.object_id=dt.object_id
join sys.indexes si
on si.object_id=dt.object_id
and si.index_id=dt.index_id
where dt.index_ID<>0
and dt.avg_fragmentation_in_percent between 10 and 15
or dt.avg_page_space_used_in_percent between 60 and 75
declare @.table_name varchar(50),
@.schema_name varchar(50),
@.command varchar(100)
declare c_Indexreorg cursor
for
select distinct SchemaName, TableName from tmp_TableIndex
open c_Indexreorg
fetch next from c_Indexreorg into @.table_name, @.schema_name
while (@.@.FETCH_STATUS=0)
begin
set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
REORGANIZE'
exec (@.command)
fetch next from c_Indexreorg
end
close c_Indexreorg
deallocate c_Indexreorg
Once I created a couple more variables (I had the @.table_name variable on a
previous iteration of the script but took it out, thinking it was making it
to complex), and not slamming the schema name and table name together in my
temporary table, it seemed to work better.
THanks for all that responded.
--
Richard Tocci
College Station, TX
"Tibor Karaszi" wrote:
> First, I want to recommend an already prepared script for you. See this topic in Books Online:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
> If you scroll down towards the end, you will find a script that does exactly what you want to do. In
> addition, the script will only reoorganize the index if it is fragmented in the first place.
>
> To answer why your script doesn't work:
> > fetch next from c_Indexreorg
> You don't fetch the column from the cursor into any variables. You should have something like:
> > fetch next from c_Indexreorg INTO @.v1, @.v1, ...
> > ALTER index ALL on [TableName]
> Here you have hardcoded the table name to "TableName", and you probably don't have a table with that
> name in the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> > I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
> > In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> > Self Study guide, working on managing index fragmentation.
> >
> > What I am trying to do is collect data from sys.dt_db_index_physical_stats
> > DMF. I'm creating a temporary table that houses the table name in the format
> > "schema.table". Then I am attempting to put some of that information into a
> > cursor, and run an ALTER INDEX on all tables that match the criteria I need.
> >
> > Following is the script I've come up with:
> >
> > set ansi_nulls on
> > set quoted_identifier on
> >
> > if exists (select * from sys.objects where name='tmp_TableIndex')
> > begin
> > drop table tmp_TableIndex
> > end
> >
> > create table tmp_TableIndex
> > (
> > TableName varchar(50),
> > AvgFragInPercent int,
> > AvgPageSpaceUsedInPercent int
> > )
> >
> > insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
> > select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> > 'TableName',
> > dt.avg_fragmentation_in_percent,
> > dt.avg_page_space_used_in_percent
> > from sys.dm_db_index_physical_stats
> > (
> > db_id(db_name()), null, null, null, 'detailed'
> > )
> > dt
> > join sys.objects sc
> > on sc.object_id=dt.object_id
> > join sys.indexes si
> > on si.object_id=dt.object_id
> > and si.index_id=dt.index_id
> > where dt.index_ID<>0
> > and dt.avg_fragmentation_in_percent between 10 and 15
> > or dt.avg_page_space_used_in_percent between 60 and 75
> >
> > declare c_Indexreorg cursor
> > for
> > select distinct TableName from tmp_TableIndex
> >
> > open c_Indexreorg
> >
> > fetch next from c_Indexreorg
> >
> > while (@.@.FETCH_STATUS=0)
> > begin
> > ALTER index ALL on [TableName]
> > REORGANIZE
> > fetch next from c_Indexreorg
> > end
> >
> > close c_Indexreorg
> >
> > deallocate c_Indexreorg
> >
> >
> > The table is created, but I get the following error:
> > Msg 1088, Level 16, State 9, Line 46
> > Cannot find the object "TableName" because it does not exist or you do not
> > have permissions.
> >
> > I made sure the tablename in the temp table was in schema.tablename format,
> > but that still didn't help.
> >
> > What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
> >
> >
> >
> > --
> > Richard Tocci
> > College Station, TX
>
>|||Forgive my curiosity, but below seems surprising:
> and dt.avg_fragmentation_in_percent between 10 and 15
Why wouldn't you want to reorg if the fragmentation level is high?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...
> After reading the Books Online help file, I modified the script and now it
> works. Here it is:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> SchemaName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) as 'SchemaName',
> object_name(dt.object_id) as 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare @.table_name varchar(50),
> @.schema_name varchar(50),
> @.command varchar(100)
> declare c_Indexreorg cursor
> for
> select distinct SchemaName, TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg into @.table_name, @.schema_name
> while (@.@.FETCH_STATUS=0)
> begin
> set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
> REORGANIZE'
> exec (@.command)
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
>
> Once I created a couple more variables (I had the @.table_name variable on a
> previous iteration of the script but took it out, thinking it was making it
> to complex), and not slamming the schema name and table name together in my
> temporary table, it seemed to work better.
> THanks for all that responded.
> --
> Richard Tocci
> College Station, TX
>
> "Tibor Karaszi" wrote:
>> First, I want to recommend an already prepared script for you. See this topic in Books Online:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
>> If you scroll down towards the end, you will find a script that does exactly what you want to do.
>> In
>> addition, the script will only reoorganize the index if it is fragmented in the first place.
>>
>> To answer why your script doesn't work:
>> > fetch next from c_Indexreorg
>> You don't fetch the column from the cursor into any variables. You should have something like:
>> > fetch next from c_Indexreorg INTO @.v1, @.v1, ...
>> > ALTER index ALL on [TableName]
>> Here you have hardcoded the table name to "TableName", and you probably don't have a table with
>> that
>> name in the database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
>> news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
>> > I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
>> > In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
>> > Self Study guide, working on managing index fragmentation.
>> >
>> > What I am trying to do is collect data from sys.dt_db_index_physical_stats
>> > DMF. I'm creating a temporary table that houses the table name in the format
>> > "schema.table". Then I am attempting to put some of that information into a
>> > cursor, and run an ALTER INDEX on all tables that match the criteria I need.
>> >
>> > Following is the script I've come up with:
>> >
>> > set ansi_nulls on
>> > set quoted_identifier on
>> >
>> > if exists (select * from sys.objects where name='tmp_TableIndex')
>> > begin
>> > drop table tmp_TableIndex
>> > end
>> >
>> > create table tmp_TableIndex
>> > (
>> > TableName varchar(50),
>> > AvgFragInPercent int,
>> > AvgPageSpaceUsedInPercent int
>> > )
>> >
>> > insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
>> > select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
>> > 'TableName',
>> > dt.avg_fragmentation_in_percent,
>> > dt.avg_page_space_used_in_percent
>> > from sys.dm_db_index_physical_stats
>> > (
>> > db_id(db_name()), null, null, null, 'detailed'
>> > )
>> > dt
>> > join sys.objects sc
>> > on sc.object_id=dt.object_id
>> > join sys.indexes si
>> > on si.object_id=dt.object_id
>> > and si.index_id=dt.index_id
>> > where dt.index_ID<>0
>> > and dt.avg_fragmentation_in_percent between 10 and 15
>> > or dt.avg_page_space_used_in_percent between 60 and 75
>> >
>> > declare c_Indexreorg cursor
>> > for
>> > select distinct TableName from tmp_TableIndex
>> >
>> > open c_Indexreorg
>> >
>> > fetch next from c_Indexreorg
>> >
>> > while (@.@.FETCH_STATUS=0)
>> > begin
>> > ALTER index ALL on [TableName]
>> > REORGANIZE
>> > fetch next from c_Indexreorg
>> > end
>> >
>> > close c_Indexreorg
>> >
>> > deallocate c_Indexreorg
>> >
>> >
>> > The table is created, but I get the following error:
>> > Msg 1088, Level 16, State 9, Line 46
>> > Cannot find the object "TableName" because it does not exist or you do not
>> > have permissions.
>> >
>> > I made sure the tablename in the temp table was in schema.tablename format,
>> > but that still didn't help.
>> >
>> > What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>> >
>> >
>> >
>> > --
>> > Richard Tocci
>> > College Station, TX
>>|||Actually, that should be an OR, not an AND. I was going by the 70-431 self
training book, page 453, at the bottom.
--
Richard Tocci
College Station, TX
"Tibor Karaszi" wrote:
> Forgive my curiosity, but below seems surprising:
> > and dt.avg_fragmentation_in_percent between 10 and 15
> Why wouldn't you want to reorg if the fragmentation level is high?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...
> > After reading the Books Online help file, I modified the script and now it
> > works. Here it is:
> >
> > set ansi_nulls on
> > set quoted_identifier on
> >
> > if exists (select * from sys.objects where name='tmp_TableIndex')
> > begin
> > drop table tmp_TableIndex
> > end
> >
> > create table tmp_TableIndex
> > (
> > TableName varchar(50),
> > SchemaName varchar(50),
> > AvgFragInPercent int,
> > AvgPageSpaceUsedInPercent int
> > )
> >
> > insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
> > AvgPageSpaceUsedInPercent)
> > select schema_name(sc.schema_id) as 'SchemaName',
> > object_name(dt.object_id) as 'TableName',
> > dt.avg_fragmentation_in_percent,
> > dt.avg_page_space_used_in_percent
> > from sys.dm_db_index_physical_stats
> > (
> > db_id(db_name()), null, null, null, 'detailed'
> > )
> > dt
> > join sys.objects sc
> > on sc.object_id=dt.object_id
> > join sys.indexes si
> > on si.object_id=dt.object_id
> > and si.index_id=dt.index_id
> > where dt.index_ID<>0
> > and dt.avg_fragmentation_in_percent between 10 and 15
> > or dt.avg_page_space_used_in_percent between 60 and 75
> >
> > declare @.table_name varchar(50),
> > @.schema_name varchar(50),
> > @.command varchar(100)
> >
> > declare c_Indexreorg cursor
> > for
> > select distinct SchemaName, TableName from tmp_TableIndex
> >
> > open c_Indexreorg
> >
> > fetch next from c_Indexreorg into @.table_name, @.schema_name
> >
> > while (@.@.FETCH_STATUS=0)
> > begin
> > set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
> > REORGANIZE'
> > exec (@.command)
> > fetch next from c_Indexreorg
> > end
> >
> > close c_Indexreorg
> >
> > deallocate c_Indexreorg
> >
> >
> >
> >
> > Once I created a couple more variables (I had the @.table_name variable on a
> > previous iteration of the script but took it out, thinking it was making it
> > to complex), and not slamming the schema name and table name together in my
> > temporary table, it seemed to work better.
> >
> > THanks for all that responded.
> > --
> > Richard Tocci
> > College Station, TX
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> First, I want to recommend an already prepared script for you. See this topic in Books Online:
> >> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
> >>
> >> If you scroll down towards the end, you will find a script that does exactly what you want to do.
> >> In
> >> addition, the script will only reoorganize the index if it is fragmented in the first place.
> >>
> >>
> >> To answer why your script doesn't work:
> >>
> >> > fetch next from c_Indexreorg
> >>
> >> You don't fetch the column from the cursor into any variables. You should have something like:
> >>
> >> > fetch next from c_Indexreorg INTO @.v1, @.v1, ...
> >>
> >> > ALTER index ALL on [TableName]
> >>
> >> Here you have hardcoded the table name to "TableName", and you probably don't have a table with
> >> that
> >> name in the database.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> >> news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> >> > I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
> >> > In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> >> > Self Study guide, working on managing index fragmentation.
> >> >
> >> > What I am trying to do is collect data from sys.dt_db_index_physical_stats
> >> > DMF. I'm creating a temporary table that houses the table name in the format
> >> > "schema.table". Then I am attempting to put some of that information into a
> >> > cursor, and run an ALTER INDEX on all tables that match the criteria I need.
> >> >
> >> > Following is the script I've come up with:
> >> >
> >> > set ansi_nulls on
> >> > set quoted_identifier on
> >> >
> >> > if exists (select * from sys.objects where name='tmp_TableIndex')
> >> > begin
> >> > drop table tmp_TableIndex
> >> > end
> >> >
> >> > create table tmp_TableIndex
> >> > (
> >> > TableName varchar(50),
> >> > AvgFragInPercent int,
> >> > AvgPageSpaceUsedInPercent int
> >> > )
> >> >
> >> > insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
> >> > select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> >> > 'TableName',
> >> > dt.avg_fragmentation_in_percent,
> >> > dt.avg_page_space_used_in_percent
> >> > from sys.dm_db_index_physical_stats
> >> > (
> >> > db_id(db_name()), null, null, null, 'detailed'
> >> > )
> >> > dt
> >> > join sys.objects sc
> >> > on sc.object_id=dt.object_id
> >> > join sys.indexes si
> >> > on si.object_id=dt.object_id
> >> > and si.index_id=dt.index_id
> >> > where dt.index_ID<>0
> >> > and dt.avg_fragmentation_in_percent between 10 and 15
> >> > or dt.avg_page_space_used_in_percent between 60 and 75
> >> >
> >> > declare c_Indexreorg cursor
> >> > for
> >> > select distinct TableName from tmp_TableIndex
> >> >
> >> > open c_Indexreorg
> >> >
> >> > fetch next from c_Indexreorg
> >> >
> >> > while (@.@.FETCH_STATUS=0)
> >> > begin
> >> > ALTER index ALL on [TableName]
> >> > REORGANIZE
> >> > fetch next from c_Indexreorg
> >> > end
> >> >
> >> > close c_Indexreorg
> >> >
> >> > deallocate c_Indexreorg
> >> >
> >> >
> >> > The table is created, but I get the following error:
> >> > Msg 1088, Level 16, State 9, Line 46
> >> > Cannot find the object "TableName" because it does not exist or you do not
> >> > have permissions.
> >> >
> >> > I made sure the tablename in the temp table was in schema.tablename format,
> >> > but that still didn't help.
> >> >
> >> > What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
> >> >
> >> >
> >> >
> >> > --
> >> > Richard Tocci
> >> > College Station, TX
> >>
> >>
> >>
>|||Guys, what book are you referring to? I am studying for the exam and am
looking for all the help I can get.
Thanks!
-Richard K
"Richard Tocci" wrote:
> Actually, that should be an OR, not an AND. I was going by the 70-431 self
> training book, page 453, at the bottom.
> --
> Richard Tocci
> College Station, TX
>
> "Tibor Karaszi" wrote:
> > Forgive my curiosity, but below seems surprising:
> >
> > > and dt.avg_fragmentation_in_percent between 10 and 15
> >
> > Why wouldn't you want to reorg if the fragmentation level is high?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> > news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...
> > > After reading the Books Online help file, I modified the script and now it
> > > works. Here it is:
> > >
> > > set ansi_nulls on
> > > set quoted_identifier on
> > >
> > > if exists (select * from sys.objects where name='tmp_TableIndex')
> > > begin
> > > drop table tmp_TableIndex
> > > end
> > >
> > > create table tmp_TableIndex
> > > (
> > > TableName varchar(50),
> > > SchemaName varchar(50),
> > > AvgFragInPercent int,
> > > AvgPageSpaceUsedInPercent int
> > > )
> > >
> > > insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
> > > AvgPageSpaceUsedInPercent)
> > > select schema_name(sc.schema_id) as 'SchemaName',
> > > object_name(dt.object_id) as 'TableName',
> > > dt.avg_fragmentation_in_percent,
> > > dt.avg_page_space_used_in_percent
> > > from sys.dm_db_index_physical_stats
> > > (
> > > db_id(db_name()), null, null, null, 'detailed'
> > > )
> > > dt
> > > join sys.objects sc
> > > on sc.object_id=dt.object_id
> > > join sys.indexes si
> > > on si.object_id=dt.object_id
> > > and si.index_id=dt.index_id
> > > where dt.index_ID<>0
> > > and dt.avg_fragmentation_in_percent between 10 and 15
> > > or dt.avg_page_space_used_in_percent between 60 and 75
> > >
> > > declare @.table_name varchar(50),
> > > @.schema_name varchar(50),
> > > @.command varchar(100)
> > >
> > > declare c_Indexreorg cursor
> > > for
> > > select distinct SchemaName, TableName from tmp_TableIndex
> > >
> > > open c_Indexreorg
> > >
> > > fetch next from c_Indexreorg into @.table_name, @.schema_name
> > >
> > > while (@.@.FETCH_STATUS=0)
> > > begin
> > > set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
> > > REORGANIZE'
> > > exec (@.command)
> > > fetch next from c_Indexreorg
> > > end
> > >
> > > close c_Indexreorg
> > >
> > > deallocate c_Indexreorg
> > >
> > >
> > >
> > >
> > > Once I created a couple more variables (I had the @.table_name variable on a
> > > previous iteration of the script but took it out, thinking it was making it
> > > to complex), and not slamming the schema name and table name together in my
> > > temporary table, it seemed to work better.
> > >
> > > THanks for all that responded.
> > > --
> > > Richard Tocci
> > > College Station, TX
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> First, I want to recommend an already prepared script for you. See this topic in Books Online:
> > >> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
> > >>
> > >> If you scroll down towards the end, you will find a script that does exactly what you want to do.
> > >> In
> > >> addition, the script will only reoorganize the index if it is fragmented in the first place.
> > >>
> > >>
> > >> To answer why your script doesn't work:
> > >>
> > >> > fetch next from c_Indexreorg
> > >>
> > >> You don't fetch the column from the cursor into any variables. You should have something like:
> > >>
> > >> > fetch next from c_Indexreorg INTO @.v1, @.v1, ...
> > >>
> > >> > ALTER index ALL on [TableName]
> > >>
> > >> Here you have hardcoded the table name to "TableName", and you probably don't have a table with
> > >> that
> > >> name in the database.
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >>
> > >>
> > >> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> > >> news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> > >> > I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
> > >> > In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> > >> > Self Study guide, working on managing index fragmentation.
> > >> >
> > >> > What I am trying to do is collect data from sys.dt_db_index_physical_stats
> > >> > DMF. I'm creating a temporary table that houses the table name in the format
> > >> > "schema.table". Then I am attempting to put some of that information into a
> > >> > cursor, and run an ALTER INDEX on all tables that match the criteria I need.
> > >> >
> > >> > Following is the script I've come up with:
> > >> >
> > >> > set ansi_nulls on
> > >> > set quoted_identifier on
> > >> >
> > >> > if exists (select * from sys.objects where name='tmp_TableIndex')
> > >> > begin
> > >> > drop table tmp_TableIndex
> > >> > end
> > >> >
> > >> > create table tmp_TableIndex
> > >> > (
> > >> > TableName varchar(50),
> > >> > AvgFragInPercent int,
> > >> > AvgPageSpaceUsedInPercent int
> > >> > )
> > >> >
> > >> > insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
> > >> > select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> > >> > 'TableName',
> > >> > dt.avg_fragmentation_in_percent,
> > >> > dt.avg_page_space_used_in_percent
> > >> > from sys.dm_db_index_physical_stats
> > >> > (
> > >> > db_id(db_name()), null, null, null, 'detailed'
> > >> > )
> > >> > dt
> > >> > join sys.objects sc
> > >> > on sc.object_id=dt.object_id
> > >> > join sys.indexes si
> > >> > on si.object_id=dt.object_id
> > >> > and si.index_id=dt.index_id
> > >> > where dt.index_ID<>0
> > >> > and dt.avg_fragmentation_in_percent between 10 and 15
> > >> > or dt.avg_page_space_used_in_percent between 60 and 75
> > >> >
> > >> > declare c_Indexreorg cursor
> > >> > for
> > >> > select distinct TableName from tmp_TableIndex
> > >> >
> > >> > open c_Indexreorg
> > >> >
> > >> > fetch next from c_Indexreorg
> > >> >
> > >> > while (@.@.FETCH_STATUS=0)
> > >> > begin
> > >> > ALTER index ALL on [TableName]
> > >> > REORGANIZE
> > >> > fetch next from c_Indexreorg
> > >> > end
> > >> >
> > >> > close c_Indexreorg
> > >> >
> > >> > deallocate c_Indexreorg
> > >> >
> > >> >
> > >> > The table is created, but I get the following error:
> > >> > Msg 1088, Level 16, State 9, Line 46
> > >> > Cannot find the object "TableName" because it does not exist or you do not
> > >> > have permissions.
> > >> >
> > >> > I made sure the tablename in the temp table was in schema.tablename format,
> > >> > but that still didn't help.
> > >> >
> > >> > What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
> > >> >
> > >> >
> > >> >
> > >> > --
> > >> > Richard Tocci
> > >> > College Station, TX
> > >>
> > >>
> > >>
> >
> >
In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
Self Study guide, working on managing index fragmentation.
What I am trying to do is collect data from sys.dt_db_index_physical_stats
DMF. I'm creating a temporary table that houses the table name in the format
"schema.table". Then I am attempting to put some of that information into a
cursor, and run an ALTER INDEX on all tables that match the criteria I need.
Following is the script I've come up with:
set ansi_nulls on
set quoted_identifier on
if exists (select * from sys.objects where name='tmp_TableIndex')
begin
drop table tmp_TableIndex
end
create table tmp_TableIndex
(
TableName varchar(50),
AvgFragInPercent int,
AvgPageSpaceUsedInPercent int
)
insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
'TableName',
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats
(
db_id(db_name()), null, null, null, 'detailed'
)
dt
join sys.objects sc
on sc.object_id=dt.object_id
join sys.indexes si
on si.object_id=dt.object_id
and si.index_id=dt.index_id
where dt.index_ID<>0
and dt.avg_fragmentation_in_percent between 10 and 15
or dt.avg_page_space_used_in_percent between 60 and 75
declare c_Indexreorg cursor
for
select distinct TableName from tmp_TableIndex
open c_Indexreorg
fetch next from c_Indexreorg
while (@.@.FETCH_STATUS=0)
begin
ALTER index ALL on [TableName]
REORGANIZE
fetch next from c_Indexreorg
end
close c_Indexreorg
deallocate c_Indexreorg
The table is created, but I get the following error:
Msg 1088, Level 16, State 9, Line 46
Cannot find the object "TableName" because it does not exist or you do not
have permissions.
I made sure the tablename in the temp table was in schema.tablename format,
but that still didn't help.
What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
--
Richard Tocci
College Station, TXRichard
Do you run this script under what account?
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some
> time.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the
> format
> "schema.table". Then I am attempting to put some of that information into
> a
> cursor, and run an ALTER INDEX on all tables that match the criteria I
> need.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename
> format,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> College Station, TX|||First, I want to recommend an already prepared script for you. See this topic in Books Online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
If you scroll down towards the end, you will find a script that does exactly what you want to do. In
addition, the script will only reoorganize the index if it is fragmented in the first place.
To answer why your script doesn't work:
> fetch next from c_Indexreorg
You don't fetch the column from the cursor into any variables. You should have something like:
> fetch next from c_Indexreorg INTO @.v1, @.v1, ...
> ALTER index ALL on [TableName]
Here you have hardcoded the table name to "TableName", and you probably don't have a table with that
name in the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the format
> "schema.table". Then I am attempting to put some of that information into a
> cursor, and run an ALTER INDEX on all tables that match the criteria I need.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename format,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> College Station, TX|||After reading the Books Online help file, I modified the script and now it
works. Here it is:
set ansi_nulls on
set quoted_identifier on
if exists (select * from sys.objects where name='tmp_TableIndex')
begin
drop table tmp_TableIndex
end
create table tmp_TableIndex
(
TableName varchar(50),
SchemaName varchar(50),
AvgFragInPercent int,
AvgPageSpaceUsedInPercent int
)
insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
AvgPageSpaceUsedInPercent)
select schema_name(sc.schema_id) as 'SchemaName',
object_name(dt.object_id) as 'TableName',
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats
(
db_id(db_name()), null, null, null, 'detailed'
)
dt
join sys.objects sc
on sc.object_id=dt.object_id
join sys.indexes si
on si.object_id=dt.object_id
and si.index_id=dt.index_id
where dt.index_ID<>0
and dt.avg_fragmentation_in_percent between 10 and 15
or dt.avg_page_space_used_in_percent between 60 and 75
declare @.table_name varchar(50),
@.schema_name varchar(50),
@.command varchar(100)
declare c_Indexreorg cursor
for
select distinct SchemaName, TableName from tmp_TableIndex
open c_Indexreorg
fetch next from c_Indexreorg into @.table_name, @.schema_name
while (@.@.FETCH_STATUS=0)
begin
set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
REORGANIZE'
exec (@.command)
fetch next from c_Indexreorg
end
close c_Indexreorg
deallocate c_Indexreorg
Once I created a couple more variables (I had the @.table_name variable on a
previous iteration of the script but took it out, thinking it was making it
to complex), and not slamming the schema name and table name together in my
temporary table, it seemed to work better.
THanks for all that responded.
--
Richard Tocci
College Station, TX
"Tibor Karaszi" wrote:
> First, I want to recommend an already prepared script for you. See this topic in Books Online:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
> If you scroll down towards the end, you will find a script that does exactly what you want to do. In
> addition, the script will only reoorganize the index if it is fragmented in the first place.
>
> To answer why your script doesn't work:
> > fetch next from c_Indexreorg
> You don't fetch the column from the cursor into any variables. You should have something like:
> > fetch next from c_Indexreorg INTO @.v1, @.v1, ...
> > ALTER index ALL on [TableName]
> Here you have hardcoded the table name to "TableName", and you probably don't have a table with that
> name in the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> > I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
> > In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> > Self Study guide, working on managing index fragmentation.
> >
> > What I am trying to do is collect data from sys.dt_db_index_physical_stats
> > DMF. I'm creating a temporary table that houses the table name in the format
> > "schema.table". Then I am attempting to put some of that information into a
> > cursor, and run an ALTER INDEX on all tables that match the criteria I need.
> >
> > Following is the script I've come up with:
> >
> > set ansi_nulls on
> > set quoted_identifier on
> >
> > if exists (select * from sys.objects where name='tmp_TableIndex')
> > begin
> > drop table tmp_TableIndex
> > end
> >
> > create table tmp_TableIndex
> > (
> > TableName varchar(50),
> > AvgFragInPercent int,
> > AvgPageSpaceUsedInPercent int
> > )
> >
> > insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
> > select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> > 'TableName',
> > dt.avg_fragmentation_in_percent,
> > dt.avg_page_space_used_in_percent
> > from sys.dm_db_index_physical_stats
> > (
> > db_id(db_name()), null, null, null, 'detailed'
> > )
> > dt
> > join sys.objects sc
> > on sc.object_id=dt.object_id
> > join sys.indexes si
> > on si.object_id=dt.object_id
> > and si.index_id=dt.index_id
> > where dt.index_ID<>0
> > and dt.avg_fragmentation_in_percent between 10 and 15
> > or dt.avg_page_space_used_in_percent between 60 and 75
> >
> > declare c_Indexreorg cursor
> > for
> > select distinct TableName from tmp_TableIndex
> >
> > open c_Indexreorg
> >
> > fetch next from c_Indexreorg
> >
> > while (@.@.FETCH_STATUS=0)
> > begin
> > ALTER index ALL on [TableName]
> > REORGANIZE
> > fetch next from c_Indexreorg
> > end
> >
> > close c_Indexreorg
> >
> > deallocate c_Indexreorg
> >
> >
> > The table is created, but I get the following error:
> > Msg 1088, Level 16, State 9, Line 46
> > Cannot find the object "TableName" because it does not exist or you do not
> > have permissions.
> >
> > I made sure the tablename in the temp table was in schema.tablename format,
> > but that still didn't help.
> >
> > What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
> >
> >
> >
> > --
> > Richard Tocci
> > College Station, TX
>
>|||Forgive my curiosity, but below seems surprising:
> and dt.avg_fragmentation_in_percent between 10 and 15
Why wouldn't you want to reorg if the fragmentation level is high?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...
> After reading the Books Online help file, I modified the script and now it
> works. Here it is:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> SchemaName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) as 'SchemaName',
> object_name(dt.object_id) as 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare @.table_name varchar(50),
> @.schema_name varchar(50),
> @.command varchar(100)
> declare c_Indexreorg cursor
> for
> select distinct SchemaName, TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg into @.table_name, @.schema_name
> while (@.@.FETCH_STATUS=0)
> begin
> set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
> REORGANIZE'
> exec (@.command)
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
>
> Once I created a couple more variables (I had the @.table_name variable on a
> previous iteration of the script but took it out, thinking it was making it
> to complex), and not slamming the schema name and table name together in my
> temporary table, it seemed to work better.
> THanks for all that responded.
> --
> Richard Tocci
> College Station, TX
>
> "Tibor Karaszi" wrote:
>> First, I want to recommend an already prepared script for you. See this topic in Books Online:
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
>> If you scroll down towards the end, you will find a script that does exactly what you want to do.
>> In
>> addition, the script will only reoorganize the index if it is fragmented in the first place.
>>
>> To answer why your script doesn't work:
>> > fetch next from c_Indexreorg
>> You don't fetch the column from the cursor into any variables. You should have something like:
>> > fetch next from c_Indexreorg INTO @.v1, @.v1, ...
>> > ALTER index ALL on [TableName]
>> Here you have hardcoded the table name to "TableName", and you probably don't have a table with
>> that
>> name in the database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
>> news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
>> > I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
>> > In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
>> > Self Study guide, working on managing index fragmentation.
>> >
>> > What I am trying to do is collect data from sys.dt_db_index_physical_stats
>> > DMF. I'm creating a temporary table that houses the table name in the format
>> > "schema.table". Then I am attempting to put some of that information into a
>> > cursor, and run an ALTER INDEX on all tables that match the criteria I need.
>> >
>> > Following is the script I've come up with:
>> >
>> > set ansi_nulls on
>> > set quoted_identifier on
>> >
>> > if exists (select * from sys.objects where name='tmp_TableIndex')
>> > begin
>> > drop table tmp_TableIndex
>> > end
>> >
>> > create table tmp_TableIndex
>> > (
>> > TableName varchar(50),
>> > AvgFragInPercent int,
>> > AvgPageSpaceUsedInPercent int
>> > )
>> >
>> > insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
>> > select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
>> > 'TableName',
>> > dt.avg_fragmentation_in_percent,
>> > dt.avg_page_space_used_in_percent
>> > from sys.dm_db_index_physical_stats
>> > (
>> > db_id(db_name()), null, null, null, 'detailed'
>> > )
>> > dt
>> > join sys.objects sc
>> > on sc.object_id=dt.object_id
>> > join sys.indexes si
>> > on si.object_id=dt.object_id
>> > and si.index_id=dt.index_id
>> > where dt.index_ID<>0
>> > and dt.avg_fragmentation_in_percent between 10 and 15
>> > or dt.avg_page_space_used_in_percent between 60 and 75
>> >
>> > declare c_Indexreorg cursor
>> > for
>> > select distinct TableName from tmp_TableIndex
>> >
>> > open c_Indexreorg
>> >
>> > fetch next from c_Indexreorg
>> >
>> > while (@.@.FETCH_STATUS=0)
>> > begin
>> > ALTER index ALL on [TableName]
>> > REORGANIZE
>> > fetch next from c_Indexreorg
>> > end
>> >
>> > close c_Indexreorg
>> >
>> > deallocate c_Indexreorg
>> >
>> >
>> > The table is created, but I get the following error:
>> > Msg 1088, Level 16, State 9, Line 46
>> > Cannot find the object "TableName" because it does not exist or you do not
>> > have permissions.
>> >
>> > I made sure the tablename in the temp table was in schema.tablename format,
>> > but that still didn't help.
>> >
>> > What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>> >
>> >
>> >
>> > --
>> > Richard Tocci
>> > College Station, TX
>>|||Actually, that should be an OR, not an AND. I was going by the 70-431 self
training book, page 453, at the bottom.
--
Richard Tocci
College Station, TX
"Tibor Karaszi" wrote:
> Forgive my curiosity, but below seems surprising:
> > and dt.avg_fragmentation_in_percent between 10 and 15
> Why wouldn't you want to reorg if the fragmentation level is high?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...
> > After reading the Books Online help file, I modified the script and now it
> > works. Here it is:
> >
> > set ansi_nulls on
> > set quoted_identifier on
> >
> > if exists (select * from sys.objects where name='tmp_TableIndex')
> > begin
> > drop table tmp_TableIndex
> > end
> >
> > create table tmp_TableIndex
> > (
> > TableName varchar(50),
> > SchemaName varchar(50),
> > AvgFragInPercent int,
> > AvgPageSpaceUsedInPercent int
> > )
> >
> > insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
> > AvgPageSpaceUsedInPercent)
> > select schema_name(sc.schema_id) as 'SchemaName',
> > object_name(dt.object_id) as 'TableName',
> > dt.avg_fragmentation_in_percent,
> > dt.avg_page_space_used_in_percent
> > from sys.dm_db_index_physical_stats
> > (
> > db_id(db_name()), null, null, null, 'detailed'
> > )
> > dt
> > join sys.objects sc
> > on sc.object_id=dt.object_id
> > join sys.indexes si
> > on si.object_id=dt.object_id
> > and si.index_id=dt.index_id
> > where dt.index_ID<>0
> > and dt.avg_fragmentation_in_percent between 10 and 15
> > or dt.avg_page_space_used_in_percent between 60 and 75
> >
> > declare @.table_name varchar(50),
> > @.schema_name varchar(50),
> > @.command varchar(100)
> >
> > declare c_Indexreorg cursor
> > for
> > select distinct SchemaName, TableName from tmp_TableIndex
> >
> > open c_Indexreorg
> >
> > fetch next from c_Indexreorg into @.table_name, @.schema_name
> >
> > while (@.@.FETCH_STATUS=0)
> > begin
> > set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
> > REORGANIZE'
> > exec (@.command)
> > fetch next from c_Indexreorg
> > end
> >
> > close c_Indexreorg
> >
> > deallocate c_Indexreorg
> >
> >
> >
> >
> > Once I created a couple more variables (I had the @.table_name variable on a
> > previous iteration of the script but took it out, thinking it was making it
> > to complex), and not slamming the schema name and table name together in my
> > temporary table, it seemed to work better.
> >
> > THanks for all that responded.
> > --
> > Richard Tocci
> > College Station, TX
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> First, I want to recommend an already prepared script for you. See this topic in Books Online:
> >> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
> >>
> >> If you scroll down towards the end, you will find a script that does exactly what you want to do.
> >> In
> >> addition, the script will only reoorganize the index if it is fragmented in the first place.
> >>
> >>
> >> To answer why your script doesn't work:
> >>
> >> > fetch next from c_Indexreorg
> >>
> >> You don't fetch the column from the cursor into any variables. You should have something like:
> >>
> >> > fetch next from c_Indexreorg INTO @.v1, @.v1, ...
> >>
> >> > ALTER index ALL on [TableName]
> >>
> >> Here you have hardcoded the table name to "TableName", and you probably don't have a table with
> >> that
> >> name in the database.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> >> news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> >> > I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
> >> > In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> >> > Self Study guide, working on managing index fragmentation.
> >> >
> >> > What I am trying to do is collect data from sys.dt_db_index_physical_stats
> >> > DMF. I'm creating a temporary table that houses the table name in the format
> >> > "schema.table". Then I am attempting to put some of that information into a
> >> > cursor, and run an ALTER INDEX on all tables that match the criteria I need.
> >> >
> >> > Following is the script I've come up with:
> >> >
> >> > set ansi_nulls on
> >> > set quoted_identifier on
> >> >
> >> > if exists (select * from sys.objects where name='tmp_TableIndex')
> >> > begin
> >> > drop table tmp_TableIndex
> >> > end
> >> >
> >> > create table tmp_TableIndex
> >> > (
> >> > TableName varchar(50),
> >> > AvgFragInPercent int,
> >> > AvgPageSpaceUsedInPercent int
> >> > )
> >> >
> >> > insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
> >> > select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> >> > 'TableName',
> >> > dt.avg_fragmentation_in_percent,
> >> > dt.avg_page_space_used_in_percent
> >> > from sys.dm_db_index_physical_stats
> >> > (
> >> > db_id(db_name()), null, null, null, 'detailed'
> >> > )
> >> > dt
> >> > join sys.objects sc
> >> > on sc.object_id=dt.object_id
> >> > join sys.indexes si
> >> > on si.object_id=dt.object_id
> >> > and si.index_id=dt.index_id
> >> > where dt.index_ID<>0
> >> > and dt.avg_fragmentation_in_percent between 10 and 15
> >> > or dt.avg_page_space_used_in_percent between 60 and 75
> >> >
> >> > declare c_Indexreorg cursor
> >> > for
> >> > select distinct TableName from tmp_TableIndex
> >> >
> >> > open c_Indexreorg
> >> >
> >> > fetch next from c_Indexreorg
> >> >
> >> > while (@.@.FETCH_STATUS=0)
> >> > begin
> >> > ALTER index ALL on [TableName]
> >> > REORGANIZE
> >> > fetch next from c_Indexreorg
> >> > end
> >> >
> >> > close c_Indexreorg
> >> >
> >> > deallocate c_Indexreorg
> >> >
> >> >
> >> > The table is created, but I get the following error:
> >> > Msg 1088, Level 16, State 9, Line 46
> >> > Cannot find the object "TableName" because it does not exist or you do not
> >> > have permissions.
> >> >
> >> > I made sure the tablename in the temp table was in schema.tablename format,
> >> > but that still didn't help.
> >> >
> >> > What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
> >> >
> >> >
> >> >
> >> > --
> >> > Richard Tocci
> >> > College Station, TX
> >>
> >>
> >>
>|||Guys, what book are you referring to? I am studying for the exam and am
looking for all the help I can get.
Thanks!
-Richard K
"Richard Tocci" wrote:
> Actually, that should be an OR, not an AND. I was going by the 70-431 self
> training book, page 453, at the bottom.
> --
> Richard Tocci
> College Station, TX
>
> "Tibor Karaszi" wrote:
> > Forgive my curiosity, but below seems surprising:
> >
> > > and dt.avg_fragmentation_in_percent between 10 and 15
> >
> > Why wouldn't you want to reorg if the fragmentation level is high?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> > news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...
> > > After reading the Books Online help file, I modified the script and now it
> > > works. Here it is:
> > >
> > > set ansi_nulls on
> > > set quoted_identifier on
> > >
> > > if exists (select * from sys.objects where name='tmp_TableIndex')
> > > begin
> > > drop table tmp_TableIndex
> > > end
> > >
> > > create table tmp_TableIndex
> > > (
> > > TableName varchar(50),
> > > SchemaName varchar(50),
> > > AvgFragInPercent int,
> > > AvgPageSpaceUsedInPercent int
> > > )
> > >
> > > insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
> > > AvgPageSpaceUsedInPercent)
> > > select schema_name(sc.schema_id) as 'SchemaName',
> > > object_name(dt.object_id) as 'TableName',
> > > dt.avg_fragmentation_in_percent,
> > > dt.avg_page_space_used_in_percent
> > > from sys.dm_db_index_physical_stats
> > > (
> > > db_id(db_name()), null, null, null, 'detailed'
> > > )
> > > dt
> > > join sys.objects sc
> > > on sc.object_id=dt.object_id
> > > join sys.indexes si
> > > on si.object_id=dt.object_id
> > > and si.index_id=dt.index_id
> > > where dt.index_ID<>0
> > > and dt.avg_fragmentation_in_percent between 10 and 15
> > > or dt.avg_page_space_used_in_percent between 60 and 75
> > >
> > > declare @.table_name varchar(50),
> > > @.schema_name varchar(50),
> > > @.command varchar(100)
> > >
> > > declare c_Indexreorg cursor
> > > for
> > > select distinct SchemaName, TableName from tmp_TableIndex
> > >
> > > open c_Indexreorg
> > >
> > > fetch next from c_Indexreorg into @.table_name, @.schema_name
> > >
> > > while (@.@.FETCH_STATUS=0)
> > > begin
> > > set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
> > > REORGANIZE'
> > > exec (@.command)
> > > fetch next from c_Indexreorg
> > > end
> > >
> > > close c_Indexreorg
> > >
> > > deallocate c_Indexreorg
> > >
> > >
> > >
> > >
> > > Once I created a couple more variables (I had the @.table_name variable on a
> > > previous iteration of the script but took it out, thinking it was making it
> > > to complex), and not slamming the schema name and table name together in my
> > > temporary table, it seemed to work better.
> > >
> > > THanks for all that responded.
> > > --
> > > Richard Tocci
> > > College Station, TX
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> First, I want to recommend an already prepared script for you. See this topic in Books Online:
> > >> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
> > >>
> > >> If you scroll down towards the end, you will find a script that does exactly what you want to do.
> > >> In
> > >> addition, the script will only reoorganize the index if it is fragmented in the first place.
> > >>
> > >>
> > >> To answer why your script doesn't work:
> > >>
> > >> > fetch next from c_Indexreorg
> > >>
> > >> You don't fetch the column from the cursor into any variables. You should have something like:
> > >>
> > >> > fetch next from c_Indexreorg INTO @.v1, @.v1, ...
> > >>
> > >> > ALTER index ALL on [TableName]
> > >>
> > >> Here you have hardcoded the table name to "TableName", and you probably don't have a table with
> > >> that
> > >> name in the database.
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >>
> > >>
> > >> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> > >> news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> > >> > I'm new to SQL 2005, but have been working with SQL 2000 for quite some time.
> > >> > In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> > >> > Self Study guide, working on managing index fragmentation.
> > >> >
> > >> > What I am trying to do is collect data from sys.dt_db_index_physical_stats
> > >> > DMF. I'm creating a temporary table that houses the table name in the format
> > >> > "schema.table". Then I am attempting to put some of that information into a
> > >> > cursor, and run an ALTER INDEX on all tables that match the criteria I need.
> > >> >
> > >> > Following is the script I've come up with:
> > >> >
> > >> > set ansi_nulls on
> > >> > set quoted_identifier on
> > >> >
> > >> > if exists (select * from sys.objects where name='tmp_TableIndex')
> > >> > begin
> > >> > drop table tmp_TableIndex
> > >> > end
> > >> >
> > >> > create table tmp_TableIndex
> > >> > (
> > >> > TableName varchar(50),
> > >> > AvgFragInPercent int,
> > >> > AvgPageSpaceUsedInPercent int
> > >> > )
> > >> >
> > >> > insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent)
> > >> > select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> > >> > 'TableName',
> > >> > dt.avg_fragmentation_in_percent,
> > >> > dt.avg_page_space_used_in_percent
> > >> > from sys.dm_db_index_physical_stats
> > >> > (
> > >> > db_id(db_name()), null, null, null, 'detailed'
> > >> > )
> > >> > dt
> > >> > join sys.objects sc
> > >> > on sc.object_id=dt.object_id
> > >> > join sys.indexes si
> > >> > on si.object_id=dt.object_id
> > >> > and si.index_id=dt.index_id
> > >> > where dt.index_ID<>0
> > >> > and dt.avg_fragmentation_in_percent between 10 and 15
> > >> > or dt.avg_page_space_used_in_percent between 60 and 75
> > >> >
> > >> > declare c_Indexreorg cursor
> > >> > for
> > >> > select distinct TableName from tmp_TableIndex
> > >> >
> > >> > open c_Indexreorg
> > >> >
> > >> > fetch next from c_Indexreorg
> > >> >
> > >> > while (@.@.FETCH_STATUS=0)
> > >> > begin
> > >> > ALTER index ALL on [TableName]
> > >> > REORGANIZE
> > >> > fetch next from c_Indexreorg
> > >> > end
> > >> >
> > >> > close c_Indexreorg
> > >> >
> > >> > deallocate c_Indexreorg
> > >> >
> > >> >
> > >> > The table is created, but I get the following error:
> > >> > Msg 1088, Level 16, State 9, Line 46
> > >> > Cannot find the object "TableName" because it does not exist or you do not
> > >> > have permissions.
> > >> >
> > >> > I made sure the tablename in the temp table was in schema.tablename format,
> > >> > but that still didn't help.
> > >> >
> > >> > What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
> > >> >
> > >> >
> > >> >
> > >> > --
> > >> > Richard Tocci
> > >> > College Station, TX
> > >>
> > >>
> > >>
> >
> >
Subscribe to:
Posts (Atom)