rebuilding full text indexes

Aight, so I added a full text catalog and a full text index for one specific column and table in my database.

Now the issue is, whenever I rebuild it, it locks the full text index forever, making it unsuable. Now, there are only 30,000 records i need to search, so it isn't like there is this massive amount of data. What am I doing wrong to where it is locking the index and disallowing me to use the stored procedure that does the searching?

The indexes have changed in SQL Server 2005 but it places locks on the table or column during rebuilds that is the reason to schedule the now discontinued index related DBCC statements to run at night or when ever is a slow time for your users. There are some access restrictions during rebuild so, you may want to plan and use the new tuning advisor. In previous versions I know if needed you can count the IAM (index allocation mapping) pages and Extents so you can prevent related performance issues. Try the link below for the new index rebuild information. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms188388.aspx

|||

today we got a messagge like full text index is corrupt.Do u know why does this happen?

PS: we did send TRUNCATE TABLE COMMAND to the table that had the index...is it a problem?

sql

Rebuilding Full Text Catalog

Hi,
I have created a db install app that will install all datatables,
build full-text catalogs and add job scheduler to populate the
catalogs several times a day.
However, I would also like my catalogs to be rebuilt every midnight
and repopulated again. I know of Rebuild method and Start method but
how do I schedule it so it is rebuilt every midnight?
Thank you,
_dino_
Why do you want to do a rebuild? A full population should suffice.
To schedule a rebuild you would schedule sp_fulltext_catalog
'catalogname','rebuild'
Have this job run in the database you are full-text indexing.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:929nf11uhqac6ajetilqje30crccvjqast@.4ax.com...
> Hi,
> I have created a db install app that will install all datatables,
> build full-text catalogs and add job scheduler to populate the
> catalogs several times a day.
> However, I would also like my catalogs to be rebuilt every midnight
> and repopulated again. I know of Rebuild method and Start method but
> how do I schedule it so it is rebuilt every midnight?
> Thank you,
> _dino_

Rebuild Index Failing

The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date3/15/2007 8:00:00 PM
LogJob History (Weekly All DB Maintenance Plan)
Step ID0
ServerSQLSERVER
Job NameWeekly All DB Maintenance Plan
Step Name(Job outcome)
Duration04:25:27
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
John
John,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/03/08/be-aware-sp2-maint-plans-and-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John
|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John
|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:

> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
>
>

Rebuild Index Failing

The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date 3/15/2007 8:00:00 PM
Log Job History (Weekly All DB Maintenance Plan)
Step ID 0
Server SQLSERVER
Job Name Weekly All DB Maintenance Plan
Step Name (Job outcome)
Duration 04:25:27
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
JohnJohn,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_kara...nd-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:

> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
>
>

Rebuild Index Failing

The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
failing.
The text file report is of very little help...
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
Report was generated on "SQLSERVER".
Maintenance Plan: Weekly All DB Maintenance Plan
Duration: 00:00:00
Status: Succeeded.
Details:
The job history doesn't tell much either...
Date 3/15/2007 8:00:00 PM
Log Job History (Weekly All DB Maintenance Plan)
Step ID 0
Server SQLSERVER
Job Name Weekly All DB Maintenance Plan
Step Name (Job outcome)
Duration 04:25:27
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by Schedule 99 (Weekly All DB
Maintenance Plan). The last step to run was step 1 (Subplan).
These symptoms are worse on (but not limited to) databases migrated from SQL
2000.
I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
Index to run. But eventually Rebuild Index will fail requiring UPDATEUSAGE
be run again.
Is there any way to get a more verbose (meaningful) task report?
What is causing this?
Is there any harm in running UPDATEUSAGE on a regular basis?
Thanks in advance,
JohnJohn,
SP2 will give you a lot more output but there are known problems with maint
plans and checkdb. See:-
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/03/08/be-aware-sp2-maint-plans-and-checkdb.aspx
Chris
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Its well known that index usage counts could get out of whack on SQL 2000
but the algorithm was changed for 2005 suh that they never should. I'd
really like to know more about this - can you send me email with the error
messages you're getting from the rebuilds? (Go to the blog site below and
send email through there).
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> failing.
> The text file report is of very little help...
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> Report was generated on "SQLSERVER".
> Maintenance Plan: Weekly All DB Maintenance Plan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> The job history doesn't tell much either...
> Date 3/15/2007 8:00:00 PM
> Log Job History (Weekly All DB Maintenance Plan)
> Step ID 0
> Server SQLSERVER
> Job Name Weekly All DB Maintenance Plan
> Step Name (Job outcome)
> Duration 04:25:27
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> Maintenance Plan). The last step to run was step 1 (Subplan).
>
> These symptoms are worse on (but not limited to) databases migrated from
> SQL
> 2000.
> I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> Index to run. But eventually Rebuild Index will fail requiring
> UPDATEUSAGE
> be run again.
> Is there any way to get a more verbose (meaningful) task report?
> What is causing this?
> Is there any harm in running UPDATEUSAGE on a regular basis?
> Thanks in advance,
> John|||Hi Paul,
Thank you for taking an interest in my little dilemma.
Forgive me but I don't see how to send an email from your blog. Can you
please expand on how to do it?
Thanks,
John
"Paul S. Randal [MS]" wrote:
> Its well known that index usage counts could get out of whack on SQL 2000
> but the algorithm was changed for 2005 suh that they never should. I'd
> really like to know more about this - can you send me email with the error
> messages you're getting from the rebuilds? (Go to the blog site below and
> send email through there).
> Thanks
> --
> Paul Randal
> Principal Lead Program Manager
> Microsoft SQL Server Core Storage Engine,
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
>
> "John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
> news:5F4C1EBF-EE19-486C-A09C-D6B1F3614108@.microsoft.com...
> > The SQL Server 2005 (9.0.2047) 'Rebuild Index' maintenance plan task is
> > failing.
> >
> > The text file report is of very little help...
> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.2047
> > Report was generated on "SQLSERVER".
> > Maintenance Plan: Weekly All DB Maintenance Plan
> > Duration: 00:00:00
> > Status: Succeeded.
> > Details:
> >
> > The job history doesn't tell much either...
> >
> > Date 3/15/2007 8:00:00 PM
> > Log Job History (Weekly All DB Maintenance Plan)
> > Step ID 0
> > Server SQLSERVER
> > Job Name Weekly All DB Maintenance Plan
> > Step Name (Job outcome)
> > Duration 04:25:27
> > Sql Severity 0
> > Sql Message ID 0
> > Operator Emailed
> > Operator Net sent
> > Operator Paged
> > Retries Attempted 0
> >
> > Message
> > The job failed. The Job was invoked by Schedule 99 (Weekly All DB
> > Maintenance Plan). The last step to run was step 1 (Subplan).
> >
> >
> >
> > These symptoms are worse on (but not limited to) databases migrated from
> > SQL
> > 2000.
> >
> > I have discovered running 'DBCC UPDATEUSAGE (DatabaseName)' allows Rebuild
> > Index to run. But eventually Rebuild Index will fail requiring
> > UPDATEUSAGE
> > be run again.
> >
> > Is there any way to get a more verbose (meaningful) task report?
> >
> > What is causing this?
> >
> > Is there any harm in running UPDATEUSAGE on a regular basis?
> >
> > Thanks in advance,
> > John
>
>

Rearrange column

Hi!
I want to rearrange a column in a table within a stored procedure.
I got this table with this sample data:
ID Text Number
1 Testing 1
2 Testing2 2
3 Testing3 3
4 Testing4 4
5 Testing5 5
I want this:
ID Text Number
1 Testing 5
2 Testing2 4
3 Testing3 3
4 Testing4 2
5 Testing5 1
The numbers of rows varies so it must be dynamical and i want 2 inparameters
(startID and endID).
I hope someone can help me.
//MagnusNumber = endID - Number +1?
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Mange" <Mange@.discussions.microsoft.com> wrote in message
news:777484C3-3018-48ED-81D8-5E9BF071DD1C@.microsoft.com...
> Hi!
> I want to rearrange a column in a table within a stored procedure.
> I got this table with this sample data:
> ID Text Number
> 1 Testing 1
> 2 Testing2 2
> 3 Testing3 3
> 4 Testing4 4
> 5 Testing5 5
> I want this:
> ID Text Number
> 1 Testing 5
> 2 Testing2 4
> 3 Testing3 3
> 4 Testing4 2
> 5 Testing5 1
> The numbers of rows varies so it must be dynamical and i want 2
> inparameters
> (startID and endID).
> I hope someone can help me.
> //Magnus
>|||When you say "rearrange a column", exactly what transformation are you
looking for? If you only specify start and end rows then what
determines the order of the other values you don't specify?
If this column is always to be dynamic then it doesn't really make
sense to have it in the table at all. Derive it in a query like this:
SELECT A.au_id, A.au_lname,
(SELECT COUNT(*)
FROM pubs.dbo.authors
WHERE au_id<=A.au_id) AS number
FROM pubs.dbo.authors AS A
ORDER BY A.au_id ;
David Portas
SQL Server MVP
--|||The meaning with the column "Number" is to display data sorted in webform.
But sometime this displayorder must be changed.
The rest of the columns must stay the same.
//Magnus
"David Portas" wrote:

> When you say "rearrange a column", exactly what transformation are you
> looking for? If you only specify start and end rows then what
> determines the order of the other values you don't specify?
> If this column is always to be dynamic then it doesn't really make
> sense to have it in the table at all. Derive it in a query like this:
> SELECT A.au_id, A.au_lname,
> (SELECT COUNT(*)
> FROM pubs.dbo.authors
> WHERE au_id<=A.au_id) AS number
> FROM pubs.dbo.authors AS A
> ORDER BY A.au_id ;
> --
> David Portas
> SQL Server MVP
> --
>|||Mange
create table #test
(
col int not null primary key,
col1 char(1)
)
insert into #test values (1,'a')
insert into #test values (2,'b')
insert into #test values (3,'c')
insert into #test values (4,'d')
select col,col1,
(select count(*) from #test t where t.col>=#test.col) from #test
"Mange" <Mange@.discussions.microsoft.com> wrote in message
news:5ABD8DA6-F591-4EFD-8C19-716042AB7010@.microsoft.com...
> The meaning with the column "Number" is to display data sorted in webform.
> But sometime this displayorder must be changed.
> The rest of the columns must stay the same.
> //Magnus
>
>
> "David Portas" wrote:
>|||>> The meaning with the column "Number" is to display data sorted in webform
. But sometime this displayorder must be changed. The rest of the columns mu
st stay the same. <<
Instead of the vague name "number", you should have used sometrhng like
"physical_display_position" so people maintaining the code would know
it is not a logical data element.
The basic principle of a tiered architecture is that display is done in
the front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.|||It doesnt matter what the intention with the column is.
Do you mean that all sorting is done in the webform ?
"--CELKO--" wrote:

> Instead of the vague name "number", you should have used sometrhng like
> "physical_display_position" so people maintaining the code would know
> it is not a logical data element.
> The basic principle of a tiered architecture is that display is done in
> the front end and never in the back end. This a more basic programming
> principle than just SQL and RDBMS.
>

Rearrange column

Hi!
I'll try again
I want to rearrange a column in a table within a stored procedure.
I got this table with this sample data:
ID Text Displayorder
1 Testing 100
2 Testing2 200
3 Testing3 300
4 Testing4 400
5 Testing5 500
The numbers of rows varies so i want 2 inparameters
(start_ID and End_ID).
Example:
start_ID, End_ID (2,4)
Will return.
ID Text Number
1 Testing 100
2 Testing2 400
3 Testing3 300
4 Testing4 200
5 Testing5 500
Can this be done?
I hope someone can help me.
//MagnusHi Magnus
Assuming that your Id columns are not contiguous then you will need to rank
them and do something like:
DECLARE @.lower int, @.upper int
SELECT @.lower = 2, @.upper = 4
SELECT [ASC].ID, [ASC].[Text],
CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
[DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
[Order],
D.id, D.[Text], D.DisplayOrder
FROM MyDisplays D ) [ASC]
JOIN
( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
D.id, D.[Text], D.DisplayOrder
FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
You can still use [id] in the case statement if necessary.
John
"Mange" wrote:

> Hi!
> I'll try again
> I want to rearrange a column in a table within a stored procedure.
> I got this table with this sample data:
> ID Text Displayorder
> 1 Testing 100
> 2 Testing2 200
> 3 Testing3 300
> 4 Testing4 400
> 5 Testing5 500
> The numbers of rows varies so i want 2 inparameters
> (start_ID and End_ID).
> Example:
> start_ID, End_ID (2,4)
> Will return.
> ID Text Number
> 1 Testing 100
> 2 Testing2 400
> 3 Testing3 300
> 4 Testing4 200
> 5 Testing5 500
> Can this be done?
> I hope someone can help me.
> //Magnus
>|||Thanks but..
That doesnt save the result into that table.
"John Bell" wrote:
> Hi Magnus
> Assuming that your Id columns are not contiguous then you will need to ran
k
> them and do something like:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> SELECT [ASC].ID, [ASC].[Text],
> CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
> [DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
> FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
> [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [ASC]
> JOIN
> ( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
> You can still use [id] in the case statement if necessary.
> John
>
> "Mange" wrote:
>|||> That doesnt save the result into that table.
No, it does not, but you can *use* it in your procedure to make it do whan
you need. :)
ML|||It doesnt work.
The result is the whole table.
"John Bell" wrote:
> Hi Magnus
> Assuming that your Id columns are not contiguous then you will need to ran
k
> them and do something like:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> SELECT [ASC].ID, [ASC].[Text],
> CASE WHEN [ASC].[ORDER] >= @.lower AND [ASC].[ORDER] <= @.upper THEN
> [DESC].DisplayOrder ELSE [ASC].DisplayOrder END AS Number
> FROM ( SELECT (Select count(*) FROM MyDisplays M where m.id <= D.id) AS
> [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [ASC]
> JOIN
> ( SELECT (Select count(*) FROM MyDisplays M where m.id >= D.id) AS [Order],
> D.id, D.[Text], D.DisplayOrder
> FROM MyDisplays D ) [DESC] ON [ASC].[Order] = [DESC].[ORDER]
> You can still use [id] in the case statement if necessary.
> John
>
> "Mange" wrote:
>|||Hi
It does what you specified with the data that you gave. You have not
specified what your restriction should be but you should be able to use a
WHERE clause in each of the derived tables to do what you require.
John
"Mange" wrote:
> Thanks but..
> That doesnt save the result into that table.
>
> "John Bell" wrote:
>|||If you actually want to change the data try:
DECLARE @.lower int, @.upper int
SELECT @.lower = 2, @.upper = 4
UPDATE O
SET DisplayOrder = N.DisplayOrder
FROM MyDisplays O
JOIN MyDisplays N ON ( O.id = @.lower AND N.id = @.upper ) OR ( O.id = @.upper
AND N.id = @.lower )
John
"Mange" wrote:
> Thanks but..
> That doesnt save the result into that table.
>
> "John Bell" wrote:
>|||Hi
To be more precise.
The result is exactly like if i would have used Select * from MyDisplays
"John Bell" wrote:
> Hi
> It does what you specified with the data that you gave. You have not
> specified what your restriction should be but you should be able to use a
> WHERE clause in each of the derived tables to do what you require.
> John
> "Mange" wrote:
>|||Many Thanks John you are a star.
I'm sorry about my bad english and my poor knowledge about SQL.
Thanks again it works know.
//Magnus
"John Bell" wrote:
> If you actually want to change the data try:
> DECLARE @.lower int, @.upper int
> SELECT @.lower = 2, @.upper = 4
> UPDATE O
> SET DisplayOrder = N.DisplayOrder
> FROM MyDisplays O
> JOIN MyDisplays N ON ( O.id = @.lower AND N.id = @.upper ) OR ( O.id = @.uppe
r
> AND N.id = @.lower )
> John
> "Mange" wrote:
>

Really important, i need your help!

Hi, i need to know if in RS2000 i can:
1 - Change the text of the exportar link to an image or another text.
2 - Chage de arrangement of the parameters to vertical (one below another)
3- Can i add tooltips for the report parameters?
This is really important for me.
Thanks a lot.What's an "exportar" link?
Mike G.
"Carlos López." <CarlosLpez@.discussions.microsoft.com> wrote in message
news:8FB4B886-0871-4BFA-BDA8-0CAB9D67D82D@.microsoft.com...
> Hi, i need to know if in RS2000 i can:
> 1 - Change the text of the exportar link to an image or another text.
> 2 - Chage de arrangement of the parameters to vertical (one below another)
> 3- Can i add tooltips for the report parameters?
> This is really important for me.
> Thanks a lot.
>|||In the toolbar there's a link of name "Export" sorry :-s
"Mike G." wrote:
> What's an "exportar" link?
> Mike G.
>
> "Carlos López." <CarlosLpez@.discussions.microsoft.com> wrote in message
> news:8FB4B886-0871-4BFA-BDA8-0CAB9D67D82D@.microsoft.com...
> > Hi, i need to know if in RS2000 i can:
> > 1 - Change the text of the exportar link to an image or another text.
> > 2 - Chage de arrangement of the parameters to vertical (one below another)
> > 3- Can i add tooltips for the report parameters?
> >
> > This is really important for me.
> > Thanks a lot.
> >
> >
>
>|||If you're talking about modifying the toolbar, it might not be as easy. But
you can create your own page submit it to reporting server using URL method.
go ahead and google URL Access SQL Reporting Services.
"Carlos López." wrote:
> Hi, i need to know if in RS2000 i can:
> 1 - Change the text of the exportar link to an image or another text.
> 2 - Chage de arrangement of the parameters to vertical (one below another)
> 3- Can i add tooltips for the report parameters?
> This is really important for me.
> Thanks a lot.
>

Really basic question about inserting data into a SQL database

Hi everyone,

I'm new to programming, and trying to learn and can't seem to find a clear-cut answer on how to insert text from a textbox into a database as a new record. I have a textbox and a button that when the button_click even is fired, that it will insert the data from the textbox as a new record. Any help will be greatly appreciated!!

If it's SQL Server you can do something like this (in C#)

SqlConnection conn = new SqlConnection("Server=<servername>;Database=<dbName>;Integrated Security=true");

conn.Open();

string sql = "Insert into.... the rest of your insert statement";

SqlCommand cmd = new SqlCommand(sql, conn);

cmd.ExecuteNonQuery();

conn.Close();

Or you can modify that a bit and use a stored procedure if that suits your needs better. If you need to connect with a username/password just remove the integrated security part of the connection string and add the username/password.

Hope that helps.

|||

i'm using Visual Basic

to perform an insert

when a button add is pressed

its surpose to insert the data from a textbox to the database

the data type of the tables column is char(4)

its similar to the top code but u have to add some other stuff do u know what they are

Really basic question about inserting data into a SQL database

Hi everyone,

I'm new to programming, and trying to learn and can't seem to find a clear-cut answer on how to insert text from a textbox into a database as a new record. I have a textbox and a button that when the button_click even is fired, that it will insert the data from the textbox as a new record. Any help will be greatly appreciated!!

If it's SQL Server you can do something like this (in C#)

SqlConnection conn = new SqlConnection("Server=<servername>;Database=<dbName>;Integrated Security=true");

conn.Open();

string sql = "Insert into.... the rest of your insert statement";

SqlCommand cmd = new SqlCommand(sql, conn);

cmd.ExecuteNonQuery();

conn.Close();

Or you can modify that a bit and use a stored procedure if that suits your needs better. If you need to connect with a username/password just remove the integrated security part of the connection string and add the username/password.

Hope that helps.

|||

i'm using Visual Basic

to perform an insert

when a button add is pressed

its surpose to insert the data from a textbox to the database

the data type of the tables column is char(4)

its similar to the top code but u have to add some other stuff do u know what they are