Experience Of Changing Sortorder

Oct 20, 1998

Hi!

I have 1 sql server 6.5 sp3 with wrong sortorder, is there anyone who can give some feedback of this job.

- How to?
- What issues should i have in mind?

Thanks in advance

Fredrik

View 2 Replies


ADVERTISEMENT

Transact SQL :: NVARCHAR SortOrder Breaks For More Than 9 Items?

Jul 22, 2015

I have below sample data table,

DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),

[Code] ....

Here I want,

1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)

2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)

3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)

Below Query works perfect, when count of child or grand child count less than 10, as SortOrder column here is

NVARCHAR,
;With cte As
(Select t.ItemId, t.ItemName, t.ItemDate, t.ParentItemName, t.ItemOrder, t.ReportId,
Cast(t.ItemOrder As nvarchar(max)) As SortOrder
From @TBL t
Where t.ParentItemName Is Null

[Code] ....

The output rows with ItemOrder's 10, 11, 12 (test grand child 10, test grand child 11, test grand child 12) should display after ItemOrder = 9 (test grand child 9). I know this is happening due to varchar sort order?

View 4 Replies View Related

Page 2 - Trying To Reorder A SortOrder Column With Delete Triggers

Dec 16, 2005

Quote: Originally Posted by mrtwice99 Yes, but how would you figure out which row was "before" it?
Code:

SELECT id, sortOrder, name FROM daTable
WHERE id = 937 OR sortorder =
( select max(sortorder) from daTable
where sortorder < ( select sortorder from daTable where id = 937) )

View 5 Replies View Related

Pls Share Your Experience With SAN&#39;s

Jan 26, 2001

Is anyone using sql server 70 or 2000 and a EMC or LSI Metastore SAN? If so, I would like to know how things are going for you. We are currently evaluating both of these products and will be making a decision as to which we will purchase in February. Please advise if you have anything positive or negative to offer.
Thanks.
Gail Wade
gwade@it.rjf.com

View 1 Replies View Related

T-SQL XML Experience??? (repost)

Nov 14, 2007



Hi all,
I have posted a thread on the XML forum but its not getting much traction there so I'm posting a link to it from here hoping that more people will pick it up. Hope that's OK.

I have a problem with a T-SQL query involving XML that is taking FAR too long to run.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2404607&SiteID=1&mode=1

-Jamie

View 1 Replies View Related

Database Experience With Sharepoint?

Apr 14, 2008

It looks like I'll be assigned with development and database management utilizing Microsoft Office Sharepoint Services and saw that there are some issues as it can become quite unwieldy.

I was wondering what difficulties to expect from MOSS and/or Sharepoint in terms of managing and backing up. Any tips or suggestions?

View 2 Replies View Related

Experience With Torn Pages?

May 15, 2002

Does anyone have any experience detecting and repairing torn pages? Does Optimization repair Torn Pages ? Any help or resources would be much appriciated.

Late
Matt

View 1 Replies View Related

SQL 2K5 Replication With DB Mirroring - Any Experience?

Feb 14, 2008

Looking for anyone that has had any experience with using SQL Merge replication while mirroring the publisher database. Thinking about doing this as a recovery plan in the event of a publisher failure.

Any advice would be appreciated.

Thank you!

View 3 Replies View Related

Please Share Your Valuable Experience

Nov 21, 2007

I have SQL Server 2000 database involving voluminous data spread across different locations in a state. The database involves lot of transactions. The database involves tables storing photo images and thumb impressions along with the textual data and other needed information in a single table. I have a application accessing and updating about 5 tables and reading most of the master tables. I need a strategy to design the database such that performance improves. Once the photo and thumb are inserted for a particular party it is not required for retrieval immediately at any circumstance. In this scenario how should I design the database and keep data volume in check. Can I archive photo and thumb impressions in CDs. Will it be reliable and feasible. Please clarify on this issue. Please share your valuable experience. Only insertions involved in the partyinfo table having Photo and Thumb impressions. Later the table is only used to fetch the data of party other than Photo and thumb.

View 3 Replies View Related

Any Experience With 2005 Sp2 Hotfixes

Jan 15, 2008

I am currently at build 9.00.3054.00 and I'm having some strange
performance problems.. inserts hanging etc.....

Has anyone applied build 3175 or 3215 with good results....

any input is appreciated!

View 1 Replies View Related

&#34;Production Support Experience&#34; In SQL Server

Jun 29, 2000

IN ONE OF THE ADVERTISEMENT I SAW, IT SAYS "WE NEED PRODUCTION SUPPORT EXPERIENCE". AND MINIMUM CRITERIA IS, HE/SHE SHOULD BE A SQL SERVER DBA.

My Question is, what do we mean by "PRODUCTION SUPPORT IN SQL SERVER".

Can anyone give a good explantion on that, please?. Thanks in advance.

Srinivasan.

View 1 Replies View Related

Horrible Experience With Xp_cmdshell - Your Comments Please

May 21, 2008

I had a nerve-wracking experience with xp_cmdshell today.

In SSMS, I ran a command to "cd" to a directory, then "rename" some files in that directory. I made the ignorant mistake of running the commands separately - NEVER NEVER DO THIS.

What happened was, by the time I ran the "rename" command it defaulted to the "system32" directory and started renaming files there instead... talk about !!$#*@(!-up!

Has any ever done this, or heard about this horrible experience before? I am trying to document cases of this, so that I don't look like the complete idiot that I am.

View 2 Replies View Related

Backup Plan Reccomendations For User With ZERO Experience.

Aug 15, 2007

Hello all, I was just awarded the job of maintaing the database serverfor our company. I have basically ZERO experience using MS SQL Server2000. Can anyone point me in the direction of a good resource forcreating backups of our database? I would love something that comeswith a gui that really simplifies the process; seeing as how i havenever even opened the MS SQL program.Our database is fairly small we have 7 users with access to thedatabase. That is it.any advice or good resources would be greatly appreciated.

View 2 Replies View Related

My First Experience Distributing The New-built Sql Db Onto Client Machine?

Dec 14, 2005

hi i am new to mssql db stuff. I just developed my first VB.NET application and it uses a mssql database which is hosted locally on my development machine. I built the deployment msi file for my application which can be installed easily on client machine, but have no idea how to move the mssql db with it.

View 1 Replies View Related

Has Anyone Had Any Experience With This Feature? Microsoft Provide A Sample C++ Prog

Apr 29, 2004

I am using SQL 2000 running a couple of large (up to 70GB) databases. I am currently backing up to disk, which is working fine.
I now want to backup the databases using the Snapshot feature of my storage array. To do this however, I first need to put my databases (3 of them) into 'backup mode', so as to stop transactions being written to the database, and provide a consistent database to backup.
Has anyone had any experience with this feature? Microsoft provide a sample C++ program called snaphot.cpp that puts a single database into 'backup mode'. I am not particularly clued up on C++. Can multiple databases be put into this mode simultaneously? Is this particularly difficult to do?
Any help would be greatly appreciated

View 1 Replies View Related

Experience A Hard Time To Connect To The Database My Dbase Is Sql Express Ed 2005

Aug 8, 2006

hi to all , well i succesfully installed SQL server 2005 express edition in windows 2000 however when i am trying to connect my program thru ODBC im having a difficulty to connect. but based on my observation if i am not connected to the internet i cannot connect to my database but if i am connected to the internet i could access the SQL server 2005 express edition.i have read the hardware requirements for SQL server 2005 express edition i have upgraded the IE5 into IE6 SP1,windows 2000 SP4, even my memory to 1GB.but still i have the problem.

my question is, do i have to maintain my connection to the internet for me to have a smooth SQL connection?

BTW, i badly need the answer.Bigthanks!



-gae-

View 1 Replies View Related

Changing The Sqldataroot

Dec 14, 2000

A college forgot to change the data root when installing a developer server. My question is can this be changed whitout reinstalling the server and restoring the developer databases.
I found sqldataroot under [HKEY_LOCAL_MACHINESoftwareMicrosoftMSSQLServerSetu pSQLDataRoot]
If I stop the sql server and change this value and move the *.mdb and *.ldf files. Would I risk currupting the databases and do I need to change something else? For example would changeing this value change the default path for backupdevices?

View 2 Replies View Related

Changing Passwords

Dec 5, 2000

I have a problem where our developers would like to change the passwords of the SQL Logins without knowing the users old password.

The only way they can use the below syntax is that they must be a member of the SYSAdmin Server Role.

sp_password NULL, 'newpassword',Max

I need a way to allow them to change the passwords without making a user a member of the SYSAdmin Server Role.

Any thoughts would be greatly apprecitated. Thanks in advance.

Max

View 1 Replies View Related

Changing Security

Dec 20, 2000

I need to change my security settings from "Windows NT Only" to "SQL Server and Windows NT". Does anyone know of any issues with doing this? Does this subtract anything or does it only add access??

View 1 Replies View Related

Changing License Key

Jun 5, 2002

Is there a way to change the license key after installation of SQL7.0?

View 1 Replies View Related

Changing Database Name

Nov 19, 2002

hi, I have created a database in sql server 2000. I need to duplicate the database under different name, how can I do that?
Also, can I rename a database name?
thanks
Ali

View 3 Replies View Related

Changing Server Name

Dec 7, 1999

Hi all,

I need to change the NT Server computer name (and the SQL Server name). I know how to do it in NT, but how would I go about changing the SQL Server name. Can anyone help me with this? Is this an easy process?

Thanks,
LC

View 3 Replies View Related

Someone Changing Permissions..

Mar 1, 2005

Someone is changing permissions on our production sql databases frequently which is causing big problems..any idea how to know who is changing and when? Any code that can be written to capture such activity?

thanks,
dbfan.

View 6 Replies View Related

Changing The Sa Password

Aug 6, 1998

I`m running SQL 6.5 with standard security. We`re running NT 4.0. I am a member of the NT Admin group - which, of course, has sa privileges in SQL Server. I changed the sa password (via Enterpise Manager on my client machine); and it worked. I tested the password change by connecting to ISQL/w - both on my client machine and at the server machine.

However, when I attempted to connect to the server via Enterprise Manager (SEM) - on my client machine - my login attempt failed. But at the server machine, I connected just fine.

Somehow, even though we`re using standard security, I`m getting locked out of SEM because of the new password. Changing the sa password back to what it had been resolved the problem; but the old password was only meant to be temporary.

Any suggestions or explanations?

View 3 Replies View Related

Changing SA Passwword

Dec 15, 1998

Hi,

During SQL installation I left the SA password blank. Now when I try to change it, the event log fills up with the following errors:

Mesg 18456 : Login failed- User: sa.
I am using SQL database for SMS and I have a SMS service account.
The error type in event viewer is "Failure Audit".

I am stopping and starting SQL once changes are made. I am also registering server with "Use Trusted Connection". I have also tried with "Use Standard Security" and the SA account.

Can anyone tell me how to change my SA password without affecting my SMS service account?

Thanks,

Terry

View 1 Replies View Related

Changing Column Name

Nov 9, 2004

Hi all,

I have a table that has integrity constraints in place and it is populated with the data. I need to rename one of the columns in the table. I am hesitant to use sp_rename procedure because when I used it before I get a warning message that says "changing any part of an object name could break scriptd and stored procedure." how big of a problem is it? Is there any other way to do it without hearting anything? Thanks.

View 1 Replies View Related

Changing A Column Name

Nov 30, 2005

How can you change a Column name for MS SQL 2000 ?

something like :

ALTER TABLE [dbo].[MyTable]
OldName RENAME NewName
GO

thank you

View 6 Replies View Related

Changing Collations...

Mar 2, 2006

I have looked thru the forum but have a couple of questions:
1) data base was created with case insensitive collation
2) all the tables were then create (72 tables) and by default got the CI collation on all columns
3) lots of data was added 2GB
4) discovered mistake and altered the database to have case sensitive collation
5)..... how to change all the collations for all the columns without doing them all manually
can i backup the database and change some settings and restore it?
export all the data, drop and recreate tables and import data?
????

View 7 Replies View Related

Tracking Who Is Changing What?

Aug 9, 2006

We are building an inventory management system complete with BOM. It is important to track what employee is updating what tables. Currently all such tables have a Date field that is updated when a change is made, and an EmployeeID field which records the employee making the change. I am wondering if someone knows of a better way to track this information. Any suggestions?

View 4 Replies View Related

Changing Formats

Apr 25, 2007

Hey guys,
I have a file that has date formatted like so: 2006-11-16 20:12:00
I would like the dateformat to be like mm/dd/yyy hh:mm

The file is being pulled into a varchar field..... as 2006-11-16 20:12:00
when I do a conversion i can only get it to mon 11,2006


any suggestions

View 1 Replies View Related

Changing Compatability From 65 To 80

Apr 21, 2008

Finally, I may have a good enough excuse to justify changing the compatabilty level of one of the biggies I look after!

Before I go ahead with the change I just wanted to run a couple of questions passed the community.
I know this is very vague, but what sort of problems should I expect? I know there are a few syntax changes, but I don't imagine I will have a problem going up in compatability level!
What is the best method to perform this?
A very simple

EXEC sp_dbcmptlevel 'playdb', 80

Or

ALTER DATABASE SET SINGLE_USER
EXEC sp_dbcmptlevel 'playdb', 80
ALTER DATABASE SET MULTI_USER

How long does this process take; are we talking seconds, minutes, hours? I imagine it's seconds, but I pose this question because a colleague reckons it will take longer because of the database size. I argue that because it's a db configuration setting, it will make no difference...

Thanks a bunch,
George

View 14 Replies View Related

Changing A Date

Jan 25, 2012

SO, If I had a problem with server computer date & stored false dates for some operations, How can I alter those dates to correct dates.

View 1 Replies View Related

Changing Server Name

Jan 29, 2004

Hi. Sorry to be asking this. I know there is a KB article with the steps for changing a SQL2K server name, but I can't find it for the life of me.

Anyone have it tagged?

TIA

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved