Changing connection transactions to database transactions

Hi there,

I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just likemyTransaction.Rollback() in asp.net.
But what about exceptions? In asp.net, I am used to doing the following:

<code>
Try
'execute commands
myTransaction.Commit()
Catch ex As Exception
Response.Write(ex.Message)
myTransaction.Rollback()
End Try
</code>
Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other thanrollback transaction?

Any help is greatly appreciated

Try these links for most of your needs, the most important is Transaction Save Point, it keeps your transactions from rolling back to the beginning if there is a roll back. If you need to use triggers look into Instead Of trigger it allows you to put your transactions within the trigger. Run a search for Transaction Save Points and Instead Of triggers in the BOL(books online) Hope this helps.

http://www.codeproject.com/database/sqlservertransactions.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_1ub2.asp

|||

Thanks Caddre,

I checked those links and finally figured it out. I have taken the approach to code my own error messages and put them in an output parameter.

One more thing, is there a way to simulate errors during a transaction? At the client side, I could explicitly throw an exception that would be caught. I don't know if this is possible in stored procedures... just want to test the error handling in my stored procedure. Anyone an idea?

|||Transaction stored procs are in a separate class, you either commit or roll back, that is the reason Savepoint is very important, if there is no Savepoint you can roll back to number one from number nineteen in a twenty transaction stored proc. You could test your Savepoints. Run a search on transaction error handling in the BOL(books online). Hope this helps.

Changing Column Names

Hey folks,
I have alot of column names in a database that I'd like to change however
there are alot of Stored Procedures that reference the existing names. Is
there a way to make SQL Server update my Stored Procedures whenever I change
a table's column name?
Thanks!No, SQL Server will not rewrite your code for you ... why do you think you
can't suppress the warning from sp_rename: "Caution: Changing any part of an
object name could break scripts and stored procedures. The COLUMN was
renamed to 'brap'."?
You should be able to find most stored procedures that rely on the table by
using sp_depends, but because of deferred name resolution and depending on
the order of your script creation, you might not catch all of them.
Assuming it's not a common word like TO or DATE, you can get the list by
scanning ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, but this isn't
flawless either.
http://www.aspfaq.com/
(Reverse address to reply.)
"John Smith" <js@.no.com> wrote in message
news:e1epiOkeEHA.2532@.TK2MSFTNGP09.phx.gbl...
> Hey folks,
> I have alot of column names in a database that I'd like to change however
> there are alot of Stored Procedures that reference the existing names. Is
> there a way to make SQL Server update my Stored Procedures whenever I
change
> a table's column name?
> Thanks!
>

Changing Column Names

Hey folks,
I have alot of column names in a database that I'd like to change however
there are alot of Stored Procedures that reference the existing names. Is
there a way to make SQL Server update my Stored Procedures whenever I change
a table's column name?
Thanks!No, SQL Server will not rewrite your code for you ... why do you think you
can't suppress the warning from sp_rename: "Caution: Changing any part of an
object name could break scripts and stored procedures. The COLUMN was
renamed to 'brap'."?
You should be able to find most stored procedures that rely on the table by
using sp_depends, but because of deferred name resolution and depending on
the order of your script creation, you might not catch all of them.
Assuming it's not a common word like TO or DATE, you can get the list by
scanning ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, but this isn't
flawless either.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"John Smith" <js@.no.com> wrote in message
news:e1epiOkeEHA.2532@.TK2MSFTNGP09.phx.gbl...
> Hey folks,
> I have alot of column names in a database that I'd like to change however
> there are alot of Stored Procedures that reference the existing names. Is
> there a way to make SQL Server update my Stored Procedures whenever I
change
> a table's column name?
> Thanks!
>|||for a 100% guarentee to pick up any defects due to the
result of an object rename, build a database from the
source code of the database. Due to problems with
sp_depends and the flaws with the information_schema
views, these are no guarentee that you have highlighted
all your potential problems. If you do a build using your
source code, then all errors will show. Of course
building has it's problems as your build order my be an
issue. These are some of the problems that our software
addresses DB Ghost. It can build a database from source
code and it takes care of all dependancies - flagging up
errors within your source code. This is change management
software and answers the question, What happens if I make
a change?
If your interested why not read our white paper on the
subject
http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Ap
proach_To_Do_Change_Mgt.pdf
DB Ghost - the only database change management software
for SQL Server on the market today.
>--Original Message--
>Hey folks,
>I have alot of column names in a database that I'd like
to change however
>there are alot of Stored Procedures that reference the
existing names. Is
>there a way to make SQL Server update my Stored
Procedures whenever I change
>a table's column name?
>Thanks!
>
>.
>|||Will it go so far as telling you that data will fail to be inserted into a
table because the foreign key dependency data is not in the foreign key
table?
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...
> for a 100% guarentee to pick up any defects due to the
> result of an object rename, build a database from the
> source code of the database. Due to problems with
> sp_depends and the flaws with the information_schema
> views, these are no guarentee that you have highlighted
> all your potential problems. If you do a build using your
> source code, then all errors will show. Of course
> building has it's problems as your build order my be an
> issue. These are some of the problems that our software
> addresses DB Ghost. It can build a database from source
> code and it takes care of all dependancies - flagging up
> errors within your source code. This is change management
> software and answers the question, What happens if I make
> a change?
> If your interested why not read our white paper on the
> subject
> http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Ap
> proach_To_Do_Change_Mgt.pdf
> DB Ghost - the only database change management software
> for SQL Server on the market today.
> >--Original Message--
> >Hey folks,
> >
> >I have alot of column names in a database that I'd like
> to change however
> >there are alot of Stored Procedures that reference the
> existing names. Is
> >there a way to make SQL Server update my Stored
> Procedures whenever I change
> >a table's column name?
> >
> >Thanks!
> >
> >
> >.
> >|||yes
>--Original Message--
>Will it go so far as telling you that data will fail to
be inserted into a
>table because the foreign key dependency data is not in
the foreign key
>table?
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...
>> for a 100% guarentee to pick up any defects due to the
>> result of an object rename, build a database from the
>> source code of the database. Due to problems with
>> sp_depends and the flaws with the information_schema
>> views, these are no guarentee that you have highlighted
>> all your potential problems. If you do a build using
your
>> source code, then all errors will show. Of course
>> building has it's problems as your build order my be an
>> issue. These are some of the problems that our software
>> addresses DB Ghost. It can build a database from source
>> code and it takes care of all dependancies - flagging
up
>> errors within your source code. This is change
management
>> software and answers the question, What happens if I
make
>> a change?
>> If your interested why not read our white paper on the
>> subject
http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Ap
>> proach_To_Do_Change_Mgt.pdf
>> DB Ghost - the only database change management software
>> for SQL Server on the market today.
>> >--Original Message--
>> >Hey folks,
>> >
>> >I have alot of column names in a database that I'd
like
>> to change however
>> >there are alot of Stored Procedures that reference the
>> existing names. Is
>> >there a way to make SQL Server update my Stored
>> Procedures whenever I change
>> >a table's column name?
>> >
>> >Thanks!
>> >
>> >
>> >.
>> >
>
>.
>sql

Changing Column Names

Hey folks,
I have alot of column names in a database that I'd like to change however
there are alot of Stored Procedures that reference the existing names. Is
there a way to make SQL Server update my Stored Procedures whenever I change
a table's column name?
Thanks!
No, SQL Server will not rewrite your code for you ... why do you think you
can't suppress the warning from sp_rename: "Caution: Changing any part of an
object name could break scripts and stored procedures. The COLUMN was
renamed to 'brap'."?
You should be able to find most stored procedures that rely on the table by
using sp_depends, but because of deferred name resolution and depending on
the order of your script creation, you might not catch all of them.
Assuming it's not a common word like TO or DATE, you can get the list by
scanning ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, but this isn't
flawless either.
http://www.aspfaq.com/
(Reverse address to reply.)
"John Smith" <js@.no.com> wrote in message
news:e1epiOkeEHA.2532@.TK2MSFTNGP09.phx.gbl...
> Hey folks,
> I have alot of column names in a database that I'd like to change however
> there are alot of Stored Procedures that reference the existing names. Is
> there a way to make SQL Server update my Stored Procedures whenever I
change
> a table's column name?
> Thanks!
>

Changing char length in stored procedures

Hello all, I'm using SQL Server 2000 and have about 250 stored procedures that use an EMPLID parameter or variable of type varchar with a length of 4. I need to change the length to 10 instead and would like to do so without having to open every sp for editing. Is there a way to do this through SQL Server 2000? Does anyone have a script to do this? Any help would be appreciated.I'd use SQL Enterprise Mangler to script all of the stored procedures, then a text editor to make the changes. It should be a simple replace operation, possibly using a regexp if you aren't careful about how you format your declarations.

-PatP

Changing an existing Crystal 8.0 report to use stored proc

Hello,
I have approximately 34 Crystal 8 reports that currently run off embedded sql statements. I need to change these to run off stored procedures, and am not sure how to convert them.

The returned columns are the same, although the queries have changed slightly.

If I attempt to directly change the SQL through "Show SQL Query" it doesn't take. I also tried "Set Location" which only replaces the table name with the sp name, and also fails.

If I build a new report from scratch, it works, but this is very time consuming and error prone.

Can someone tell me how to change the existing reports to use the sp's?

Thanks,
JamesShow SQL Query wont' allow u to change existing sql.

Report object has SQLQueryString String type. Gets or sets SQL query string. Read/Write Can be written only when formatting idle which according to crystal allows you to change sql at runtime. Maybe this would help. Change this to your procedure name systematically.

Thanks

Changing all VARCHAR to NVARCHAR database-wide?

Hi,
I have an ASP.NET application that uses VARCHAR extensively in the tables and, more importantly, stored procedures (a couple hundred of them).

This app needs to start accepting foreign language in some areas, so I was wondering if there was some way to go through the tables and, more importantly, the stored procedures and change all "VARCHAR" references to "NVARCHAR" ?

Are the stored procedures stored as a text file somewhere on the server? If so I could use some sort of "replace" software utility to go through and change all VARCHAR to NVARCHAR

thanks!

-BretYou should be able to write a script to change your tables - it will take you a while since you probably will need to:

For each table with Varchar columns
- Create a temp table
- Copy all the data to the temp table
- Remove all FK constraints to the table
- Drop the table
- Create the table with NVarchar columns
- Insert the data from the temp table
- Create the IDENTITY column if applicable
- Recreate all the FK constraints

If you have fewer than 100 tables or so it might be quicker to use EM to do this.

For the stored procs: The quickest way to do the change is to allow updates on system tables. You can change this in EM. Then you can find all the stored procedures in sysobjects, with the actual TSQL in syscomments (in the 'text' column) Of course it's not normally a good idea to update system tables directly, and I'm not 100% sure this will work, but it's worth a shot:

update C
 set C.Text = replace(C.[Text], 'varchar', 'nvarchar')
 from syscomments C
 join sysobjects O
 on C.[id] = O.[id]
 and O.Type = 'P'

Now obviously there's a lot of things that can go wrong here. You string 'varchar' may exist as a part of a variable name, or column name or some other place where it's not supposed to be replaced by 'nvarchar'. It's possible that a stored procedure is long enough to use more than one row in syscomments, and this will be a problem since 'nvarchar' is one character more than 'varchar' so the update will fail with 'string or binary data would be truncated'.

So be careful and be sure to test every SP. Again you can write a script to execute every stored proc in your DB and check for errors.

Have fun!|||You have to change to Unicode Collation, nothing serious in SQL Server 2000 you can change it down to column level but Unicode in SQL Server is Multi byte unlike .NET where you can choose UTF 8 which is equivalent to ASCII. Yes Stored Procs are stored in .SQL text files that you can open with notepad and can be seen under stored procs in the Enterprise Manager in your Database. Run a search in the BOL(books online) for Mixed collation and ALTER PROCEDURE. If you are now using UTF 8 in .NET you may not run into storage problems except disk space which will double. Stored procs in SQL Server 2000 can be up to 128 megs in size. Hope this helps.

Kind regards,
Gift Peddie

changing AD domains with SS2005

We have the need to move a sql server 2005 server from domain “a” to domain “b”
Is there anything such as certain store procedures to run or gotchas to be
looking out for to do this move between domains?
Thanks
-Jason
Hello Jason,
Based on my scope, SIDhistory does not work for this situation. After you
migrate the server to the new doamin, you have to migrate users of the old
domain to new domain so that the new domain users could have the same
permssions.
First, you need to transfer logins of old domain to new domain.
How to transfer the logins and the passwords between instances of SQL
Server 2005
http://support.microsoft.com/kb/918992/
Then you need to maintain the mapping between the logins in the master
database and the users in the user database by using the SIDs. This mapping
is necessary to maintain correct permissions for the logins in the user
databases. In SQL 2000, you could use the sp_sidmap stored procedure
mentioned in the following article to map SIDs for logins between original
and new server:
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
However, in SQL 2005, sp_SidMap doesn't work because it needs to write to
system table directly. You may need to develop a script to do the job
manually.
Please refer to the following article for more related information:
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
If you have any further concerns, please feel free to let's know. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||what about the local groups that were created on the server at install?
e.g. SQLServer2005MSSQLServerADHelperUser$
and SQLServer2005MSSQLUser$
etc.
""Peter Yang[MSFT]"" wrote:

> Hello Jason,
> Based on my scope, SIDhistory does not work for this situation. After you
> migrate the server to the new doamin, you have to migrate users of the old
> domain to new domain so that the new domain users could have the same
> permssions.
> First, you need to transfer logins of old domain to new domain.
> How to transfer the logins and the passwords between instances of SQL
> Server 2005
> http://support.microsoft.com/kb/918992/
> Then you need to maintain the mapping between the logins in the master
> database and the users in the user database by using the SIDs. This mapping
> is necessary to maintain correct permissions for the logins in the user
> databases. In SQL 2000, you could use the sp_sidmap stored procedure
> mentioned in the following article to map SIDs for logins between original
> and new server:
> 240872 HOW TO: Resolve Permission Issues When You Move a Database Between
> http://support.microsoft.com/?id=240872
> However, in SQL 2005, sp_SidMap doesn't work because it needs to write to
> system table directly. You may need to develop a script to do the job
> manually.
> Please refer to the following article for more related information:
> 314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/?id=314546
> If you have any further concerns, please feel free to let's know. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hello Jason,
These groups are for internal purpose which gets created during the time of
installation. You don't need to worry about the groups when migrating since
they are related to the local server other than the domains.
If you have any further quesitons or concerns, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Jason,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.

changing AD domains with SS2005

We have the need to move a sql server 2005 server from domain “a” to dom
ain “b”
Is there anything such as certain store procedures to run or gotchas to be
looking out for to do this move between domains?
Thanks
-JasonHello Jason,
Based on my scope, SIDhistory does not work for this situation. After you
migrate the server to the new doamin, you have to migrate users of the old
domain to new domain so that the new domain users could have the same
permssions.
First, you need to transfer logins of old domain to new domain.
How to transfer the logins and the passwords between instances of SQL
Server 2005
http://support.microsoft.com/kb/918992/
Then you need to maintain the mapping between the logins in the master
database and the users in the user database by using the SIDs. This mapping
is necessary to maintain correct permissions for the logins in the user
databases. In SQL 2000, you could use the sp_sidmap stored procedure
mentioned in the following article to map SIDs for logins between original
and new server:
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
However, in SQL 2005, sp_SidMap doesn't work because it needs to write to
system table directly. You may need to develop a script to do the job
manually.
Please refer to the following article for more related information:
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
If you have any further concerns, please feel free to let's know. Thank
you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||what about the local groups that were created on the server at install?
e.g. SQLServer2005MSSQLServerADHelperUser$
and SQLServer2005MSSQLUser$
etc.
""Peter Yang[MSFT]"" wrote:

> Hello Jason,
> Based on my scope, SIDhistory does not work for this situation. After you
> migrate the server to the new doamin, you have to migrate users of the old
> domain to new domain so that the new domain users could have the same
> permssions.
> First, you need to transfer logins of old domain to new domain.
> How to transfer the logins and the passwords between instances of SQL
> Server 2005
> http://support.microsoft.com/kb/918992/
> Then you need to maintain the mapping between the logins in the master
> database and the users in the user database by using the SIDs. This mappin
g
> is necessary to maintain correct permissions for the logins in the user
> databases. In SQL 2000, you could use the sp_sidmap stored procedure
> mentioned in the following article to map SIDs for logins between original
> and new server:
> 240872 HOW TO: Resolve Permission Issues When You Move a Database Between
> http://support.microsoft.com/?id=240872
> However, in SQL 2005, sp_SidMap doesn't work because it needs to write to
> system table directly. You may need to develop a script to do the job
> manually.
> Please refer to the following article for more related information:
> 314546 HOW TO: Move Databases Between Computers That Are Running SQL Serve
r
> http://support.microsoft.com/?id=314546
> If you have any further concerns, please feel free to let's know. Thank
> you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hello Jason,
These groups are for internal purpose which gets created during the time of
installation. You don't need to worry about the groups when migrating since
they are related to the local server other than the domains.
If you have any further quesitons or concerns, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Changes in Table

How we can stop alter statement of Table when the Table is referred by Stored Procedures and other Objects?

Quote:

Originally Posted by sajithamol

How we can stop alter statement of Table when the Table is referred by Stored Procedures and other Objects?

can you explain in detail

Change structure in Db

Hi.
How I can change a structure(add/drop columns or add st.
procedures) in databases which are involved in Merge
Replication process? I'm using SQL Server 2000.
Thank you.
use sp_repladdcolumn and sp_repldropcolumn

Change Sort_Order up/down SP problem

Hi,

I'm having a problem with some stored procedures which is supposed to change the position of an item in a list to move it up or down. I've written two stored procedures - one for up or down - but they work intermittenly or not at all and I'm not sure why. Could someone help me out? I'm sure my logic is messed up somewhere and probably more complicated than it needs to be.

Thanks!

My table structure:

SpecialNeedID (PK, int)
SpecialNeed (varchar)
Sort_Order (int)

My stored procedures:

CREATE PROCEDURE [dbo].[up_SortSpecialNeedsUp]
(
@.SpecialNeedID int
)
AS

BEGIN
UPDATE lkpSpecialNeeds SET Sort_Order=(
SELECT TOP 1 Sort_Order-10 FROM lkpSpecialNeeds WHERE Sort_Order >
(SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID))
WHERE SpecialNeedID=
(SELECT TOP 1 SpecialNeedID FROM lkpSpecialNeeds WHERE Sort_Order >
(SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID) ORDER BY Sort_Order)
END

BEGIN
UPDATE lkpSpecialNeeds SET Sort_Order=(
SELECT Sort_Order+10 FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID)
WHERE SpecialNeedID=@.SpecialNeedID
END

==================================================

CREATE PROCEDURE [dbo].[up_SortSpecialNeedsDown]
(
@.SpecialNeedID int
)
AS

BEGIN
UPDATE lkpSpecialNeeds SET Sort_Order=(
SELECT TOP 1 Sort_Order+10 FROM lkpSpecialNeeds WHERE Sort_Order <
(SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID))
WHERE SpecialNeedID=
(SELECT TOP 1 SpecialNeedID FROM lkpSpecialNeeds WHERE Sort_Order <
(SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID) ORDER BY Sort_Order)
END

BEGIN
UPDATE lkpSpecialNeeds SET Sort_Order=(
SELECT Sort_Order-10 FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID)
WHERE SpecialNeedID=@.SpecialNeedID
ENDWell, that is messy. For one thing, you've got some TOP statements in there without ORDER BYs, making them meaningless or even misleading.

Explain what you are trying to do. It looks like you are trying to swap the places of two entries 10 records apart, which doesn't make sense to me. I'd think you would want to be swapping the places of neighboring records.

Maybe some sample data would help clarify. Regardless, I think the final solution which be much simpler than what you have come up with so far.|||Thanks for the reply. Here's some sample data:

SpecialNeedID SpecialNeed Sort_Order
---------------
1 Blind 10
2 Deaf 20
3 Autistic 30

These items are arranged in a list - the last column on the list are two buttons: up and down. Using these, the admins should be able to move items up and down to dictate the order they appear in drop-down lists.|||OK, why are you incrementing the sort order by 10? That certainly leaves open a lot of opportunities for bad data leading to unpredictable results from your resorting procedure. Why not just increment by 1 with integers?|||try this... assuming Sort_Order is numeric... need a check for the top one

CREATE PROCEDURE [dbo].[up_SortSpecialNeedsUp]
( @.CurID int )
AS

declare @.PrevId int

set @.PrevId = (select top 1 SpecialNeedID from lkpSpecialNeeds where Sort_Order < (select Sort_Order from lkpSpecialNeeds where SpecialNeedID=@.CurId) order by Sort_Order desc)
update lkpSpecialNeeds set Sort_Order=Sort_Order-10 where SpecialNeedID = @.CurId
update lkpSpecialNeeds set Sort_Order=Sort_Order+10 where SpecialNeedID = @.PrevId|||try this... assuming Sort_Order is numeric... need a check for the top one

This worked perfectly, thanks so much!

Change schema name on Tables and Stored procedures

Hi,

Is there a way I can change schema name on tables and stored procedures? How do I do this?

I´m very news to SQL and .net

Thanks

You can use ALTER SCHEMA command, the following command will transfer mytable under myschema to dbo schema:

ALTER SCHEMA dbo TRANSFERmyschema.mytable

For more information, you can refer to:

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

change nc-index to c-index?

Found out a while back that my facts-tabel has an non-clustered index on its facts_id. In a bunch of procedures an update is executed against a facts_id unfortunately on it's facts-table. I was wondering if changing it into a clustered index is worth the effort / would make sense considering a +110 million facts and re-indexing the other indexes as well? Facts are loaded sequentially, so I would suspect them facts are in the ordered already?

thanx,Is update executed against facts_id itself or against the table "where facts_id=<whatever>"? If it's the first, then you have a lot of page splits in your index pages, but rebuilding non-clustered index with so many rows is much less destructive than if you had a clustered index there.|||the updates are like:

update facts
set name = value
where facts_id = @.facts_id

so I can expect only few page splits (is there a way to count 'em?).
There's no update on the facts_id itself and it's unlikely it'll happen in the future which, I guess, makes the facts_id even more attractive for a clustered index.
I'll have to rebuild the other indexes as well though but I guess that's okay.

I saw the facts has a bunch of statistics, my dev does not have this nor do the other warehouses that are derived from prod. Could this be a problem? If I'd throw 'em out (delete 'em), would sql rebuild them by itself?|||It will if Auto Create Statistics is on. It's a database setting.