Hi,
I have a main table that references many tables (something like Orders
table, referencing Customers,Employees...).
As an example, there are many orders that have Null value for their
CustomerID therefore I need an Outer Join from Orders table to retrieve all
rows.
Now a View that joins Customers to Orders cannot be indexed because of the
Outer Join.
I was thinking of adding a row to Customers with value of (#) for
CompanyName column and assigning its ID to all Orphan rows in Orders.
I must:
- Write trigger on Orders table to insert ID of # record for new orphan
orders instead of Null.
- Filter # record when I need a real Inner Join.
- and some other considerations...
Instead an Inner Join between Customers and Orders can return all orders and
this query can be used in indexed view.
The performance of queries from main(Orders) table is the most critical
issue. Is it a good decision for changing such queries to Inner Join?
I appreciated any suggestion.
LeilaDo you really have to use an index view ? I just wanna keep track that even
it is an outer join the indexes will be used if possible and if existing. I
would´t got the way you are describing although this situation is uncommon
to me, to enter an order where no customer is assigned to it. (?) Ok, but i
would´t worsen up the situation by building a trigger and doing this kind of
workaround. I would build my best on the possibiliies i have, perhaps there
is a way to tune up our queries ?!
http://www.sql-server-performance.com/tuning_joins.asp
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Hi
You can change your outer joins to a union of a query that uses an inner
join plus a join where the where clause eliminates those returned by the
inner join.
If you want to use a default for your order id, make the column not nullable
with the default.
I would have to ask why you have orders without customers?
John
"Leila" <leilas@.hotpop.com> wrote in message
news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a main table that references many tables (something like Orders
> table, referencing Customers,Employees...).
> As an example, there are many orders that have Null value for their
> CustomerID therefore I need an Outer Join from Orders table to retrieve
> all
> rows.
> Now a View that joins Customers to Orders cannot be indexed because of the
> Outer Join.
> I was thinking of adding a row to Customers with value of (#) for
> CompanyName column and assigning its ID to all Orphan rows in Orders.
> I must:
> - Write trigger on Orders table to insert ID of # record for new orphan
> orders instead of Null.
> - Filter # record when I need a real Inner Join.
> - and some other considerations...
> Instead an Inner Join between Customers and Orders can return all orders
> and
> this query can be used in indexed view.
> The performance of queries from main(Orders) table is the most critical
> issue. Is it a good decision for changing such queries to Inner Join?
> I appreciated any suggestion.
> Leila
>|||Thanks Jens,
>I just wanna keep track that even
> it is an outer join the indexes will be used if possible and if existing
- I do agree, but indexes and tuning the queries can help to limited extent.
If they could be ultimate solution for all situations, Indexed Views
wouldn't be invented! What if your query needs a covering index with more
that 16 columns? What if the index length exceeds 900 bytes? You will not be
able to create useful indexes.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:#0Fi1lCSFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Do you really have to use an index view ? I just wanna keep track that
even
> it is an outer join the indexes will be used if possible and if existing.
I
> would´t got the way you are describing although this situation is uncommon
> to me, to enter an order where no customer is assigned to it. (?) Ok, but
i
> would´t worsen up the situation by building a trigger and doing this kind
of
> workaround. I would build my best on the possibiliies i have, perhaps
there
> is a way to tune up our queries ?!
> http://www.sql-server-performance.com/tuning_joins.asp
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Leila" <leilas@.hotpop.com> schrieb im Newsbeitrag
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I have a main table that references many tables (something like Orders
> > table, referencing Customers,Employees...).
> > As an example, there are many orders that have Null value for their
> > CustomerID therefore I need an Outer Join from Orders table to retrieve
> > all
> > rows.
> > Now a View that joins Customers to Orders cannot be indexed because of
the
> > Outer Join.
> > I was thinking of adding a row to Customers with value of (#) for
> > CompanyName column and assigning its ID to all Orphan rows in Orders.
> > I must:
> > - Write trigger on Orders table to insert ID of # record for new orphan
> > orders instead of Null.
> > - Filter # record when I need a real Inner Join.
> > - and some other considerations...
> >
> > Instead an Inner Join between Customers and Orders can return all orders
> > and
> > this query can be used in indexed view.
> > The performance of queries from main(Orders) table is the most critical
> > issue. Is it a good decision for changing such queries to Inner Join?
> > I appreciated any suggestion.
> > Leila
> >
> >
>|||Thanks John,
Using union is great idea but in my query, the main table(orders) is joined
with other parent tables(like customers, employees..).
The same situation can exist: orders with EmployeeID=Null as well.
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e3LJqvCSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi
> You can change your outer joins to a union of a query that uses an inner
> join plus a join where the where clause eliminates those returned by the
> inner join.
> If you want to use a default for your order id, make the column not
nullable
> with the default.
> I would have to ask why you have orders without customers?
> John
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23s11KGCSFHA.1348@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> > I have a main table that references many tables (something like Orders
> > table, referencing Customers,Employees...).
> > As an example, there are many orders that have Null value for their
> > CustomerID therefore I need an Outer Join from Orders table to retrieve
> > all
> > rows.
> > Now a View that joins Customers to Orders cannot be indexed because of
the
> > Outer Join.
> > I was thinking of adding a row to Customers with value of (#) for
> > CompanyName column and assigning its ID to all Orphan rows in Orders.
> > I must:
> > - Write trigger on Orders table to insert ID of # record for new orphan
> > orders instead of Null.
> > - Filter # record when I need a real Inner Join.
> > - and some other considerations...
> >
> > Instead an Inner Join between Customers and Orders can return all orders
> > and
> > this query can be used in indexed view.
> > The performance of queries from main(Orders) table is the most critical
> > issue. Is it a good decision for changing such queries to Inner Join?
> > I appreciated any suggestion.
> > Leila
> >
> >
>|||"Leila" <leilas@.hotpop.com> wrote in message
news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> - I do agree, but indexes and tuning the queries can help to limited
> extent.
> If they could be ultimate solution for all situations, Indexed Views
> wouldn't be invented! What if your query needs a covering index with more
> that 16 columns? What if the index length exceeds 900 bytes? You will not
> be
> able to create useful indexes.
Not familiar with your DDL, or exactly how you're relating these tables to
one another, but columns with a lot of NULL values don't tend to make very
good indexes either... Are you really relating these tables to one another
using a CustomerID field that can be NULL? How do you keep track of who to
ship the order to? Or, more importantly, where to send the bill? In a
situation like this, would it make sense to generate all your NULL
customerid orders in a separate 'exception'-type query instead of plopping
them down in the middle of orders that were actually placed by customers?
Just wondering...
Instead of going through all the trigger-writing, etc., why not just UPDATE
all current NULL customerid's with the default value you specified; then
define the column as NON-NULL with a Default?|||Thank Michael,
Please note that orders without customer is just an example, I skipped
describing the real situation (and why orphan rows exist) because tables in
northwind are famous and everybody is familiar with them.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:K%Fae.3416$RP1.1452@.fe10.lga...
> "Leila" <leilas@.hotpop.com> wrote in message
> news:edXR8yCSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> > - I do agree, but indexes and tuning the queries can help to limited
> > extent.
> > If they could be ultimate solution for all situations, Indexed Views
> > wouldn't be invented! What if your query needs a covering index with
more
> > that 16 columns? What if the index length exceeds 900 bytes? You will
not
> > be
> > able to create useful indexes.
> Not familiar with your DDL, or exactly how you're relating these tables to
> one another, but columns with a lot of NULL values don't tend to make very
> good indexes either... Are you really relating these tables to one
another
> using a CustomerID field that can be NULL? How do you keep track of who
to
> ship the order to? Or, more importantly, where to send the bill? In a
> situation like this, would it make sense to generate all your NULL
> customerid orders in a separate 'exception'-type query instead of plopping
> them down in the middle of orders that were actually placed by customers?
> Just wondering...
> Instead of going through all the trigger-writing, etc., why not just
UPDATE
> all current NULL customerid's with the default value you specified; then
> define the column as NON-NULL with a Default?
>
>
Saturday, February 25, 2012
changing ordinal position of a field
Does anyone have the idea of how to change the ordinal position of a
field using DDL statements in access 97? I know i'm posting it in
incorrect group, as this is the only active group i've visited till
now.
thanks in advance<rameshsaive@.gmail.com> wrote in message
news:1140699343.348758.133780@.e56g2000cwe.googlegroups.com...
> Does anyone have the idea of how to change the ordinal position of a
> field using DDL statements in access 97? I know i'm posting it in
> incorrect group, as this is the only active group i've visited till
> now.
> thanks in advance
>
I'm not sure what that matters to you. The database stores the data in
whatever order makes the best sense to it. That said, the easy way would
be to do something like:
SELECT col1, col2, col3 -- Use the order that you want the data
displayed in
INTO SomeTempTable
FROM OldTable
DROP OldTabe
SELECT *
INTO NewTable
FROM SomeTempTable
DROP SomeTempTable
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick for your info.
There is no problem with the order of the fields in the database. But
it causing me a hell of a problems in my 100+ reports developed in
crystal reports 8.5.
I've a table named "employee", has a field named "postdesc". Initially
the size of the field "postdesc" is limited to 100 but now it has
increased to 200. Unfortunately, i've dropped the column & recreated
it using DDL Statements.
But this has changed the ordinal position of the field (it has added
the field at the end of the table). Now the reports which depends on
this field is not working properly. i know this is a problem with
crystal reports, but it is very cumbursome to change 100+ reports.
Please help me on this.|||<rameshsaive@.gmail.com> wrote in message
news:1140701965.950863.124030@.u72g2000cwu.googlegroups.com...
> Thanks Rick for your info.
> There is no problem with the order of the fields in the database. But
> it causing me a hell of a problems in my 100+ reports developed in
> crystal reports 8.5.
> I've a table named "employee", has a field named "postdesc". Initially
> the size of the field "postdesc" is limited to 100 but now it has
> increased to 200. Unfortunately, i've dropped the column & recreated
> it using DDL Statements.
> But this has changed the ordinal position of the field (it has added
> the field at the end of the table). Now the reports which depends on
> this field is not working properly. i know this is a problem with
> crystal reports, but it is very cumbursome to change 100+ reports.
> Please help me on this.
>
I understand now..
You should be able to go into Access and open the Table Designer. From
there, you should be able to move the row to where you want it in the list.
If that doesn't work, there is always VBScripting. ;-)
Rick Sawtell|||This will not fix your current problem, but ...
When at all possible use views instead of tables in crystal reports. When
the underlying table changes crystal will be unaffected.
If you change structure of the view (add or remove a field) you will still
need to go into crystal and verify the database though.
Just some general advice to make working with crystal a little bit easier.
<rameshsaive@.gmail.com> wrote in message
news:1140701965.950863.124030@.u72g2000cwu.googlegroups.com...
> Thanks Rick for your info.
> There is no problem with the order of the fields in the database. But
> it causing me a hell of a problems in my 100+ reports developed in
> crystal reports 8.5.
> I've a table named "employee", has a field named "postdesc". Initially
> the size of the field "postdesc" is limited to 100 but now it has
> increased to 200. Unfortunately, i've dropped the column & recreated
> it using DDL Statements.
> But this has changed the ordinal position of the field (it has added
> the field at the end of the table). Now the reports which depends on
> this field is not working properly. i know this is a problem with
> crystal reports, but it is very cumbursome to change 100+ reports.
> Please help me on this.
>
field using DDL statements in access 97? I know i'm posting it in
incorrect group, as this is the only active group i've visited till
now.
thanks in advance<rameshsaive@.gmail.com> wrote in message
news:1140699343.348758.133780@.e56g2000cwe.googlegroups.com...
> Does anyone have the idea of how to change the ordinal position of a
> field using DDL statements in access 97? I know i'm posting it in
> incorrect group, as this is the only active group i've visited till
> now.
> thanks in advance
>
I'm not sure what that matters to you. The database stores the data in
whatever order makes the best sense to it. That said, the easy way would
be to do something like:
SELECT col1, col2, col3 -- Use the order that you want the data
displayed in
INTO SomeTempTable
FROM OldTable
DROP OldTabe
SELECT *
INTO NewTable
FROM SomeTempTable
DROP SomeTempTable
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick for your info.
There is no problem with the order of the fields in the database. But
it causing me a hell of a problems in my 100+ reports developed in
crystal reports 8.5.
I've a table named "employee", has a field named "postdesc". Initially
the size of the field "postdesc" is limited to 100 but now it has
increased to 200. Unfortunately, i've dropped the column & recreated
it using DDL Statements.
But this has changed the ordinal position of the field (it has added
the field at the end of the table). Now the reports which depends on
this field is not working properly. i know this is a problem with
crystal reports, but it is very cumbursome to change 100+ reports.
Please help me on this.|||<rameshsaive@.gmail.com> wrote in message
news:1140701965.950863.124030@.u72g2000cwu.googlegroups.com...
> Thanks Rick for your info.
> There is no problem with the order of the fields in the database. But
> it causing me a hell of a problems in my 100+ reports developed in
> crystal reports 8.5.
> I've a table named "employee", has a field named "postdesc". Initially
> the size of the field "postdesc" is limited to 100 but now it has
> increased to 200. Unfortunately, i've dropped the column & recreated
> it using DDL Statements.
> But this has changed the ordinal position of the field (it has added
> the field at the end of the table). Now the reports which depends on
> this field is not working properly. i know this is a problem with
> crystal reports, but it is very cumbursome to change 100+ reports.
> Please help me on this.
>
I understand now..
You should be able to go into Access and open the Table Designer. From
there, you should be able to move the row to where you want it in the list.
If that doesn't work, there is always VBScripting. ;-)
Rick Sawtell|||This will not fix your current problem, but ...
When at all possible use views instead of tables in crystal reports. When
the underlying table changes crystal will be unaffected.
If you change structure of the view (add or remove a field) you will still
need to go into crystal and verify the database though.
Just some general advice to make working with crystal a little bit easier.
<rameshsaive@.gmail.com> wrote in message
news:1140701965.950863.124030@.u72g2000cwu.googlegroups.com...
> Thanks Rick for your info.
> There is no problem with the order of the fields in the database. But
> it causing me a hell of a problems in my 100+ reports developed in
> crystal reports 8.5.
> I've a table named "employee", has a field named "postdesc". Initially
> the size of the field "postdesc" is limited to 100 but now it has
> increased to 200. Unfortunately, i've dropped the column & recreated
> it using DDL Statements.
> But this has changed the ordinal position of the field (it has added
> the field at the end of the table). Now the reports which depends on
> this field is not working properly. i know this is a problem with
> crystal reports, but it is very cumbursome to change 100+ reports.
> Please help me on this.
>
Changing Ordering of a Dimension Attribute
I have a dimension attribute called Month. It is sorting alphabetically instead of based on it's underlying key value. How do I change this?
Thanks,
ChrisChange the order by from "Name" to "Key". No idea what "Attribute Name" and "Attribute Key" do however.|||If you choose attribute name, you can define the order by another field.
I have in each dimension an order field and I have configured each dimension to sort by this field. By default I fill in this field with name. But if a user request another order I can change values in this field without changing dimension structure.
Changing Ordering
Not quite sure how to word this.
Is there any way to change the ordering of SQL Server
I need it to order
ABC2 then ABC12 then ABC110 (this is by entire string, rather than
individual characters)
currently it orders it ABC110, ABC12, ABC2
is there anyway we can change this?
On Thu, 2 Feb 2006 02:11:27 -0800, Sian wrote:
>Not quite sure how to word this.
>Is there any way to change the ordering of SQL Server
>I need it to order
>ABC2 then ABC12 then ABC110 (this is by entire string, rather than
>individual characters)
>currently it orders it ABC110, ABC12, ABC2
>is there anyway we can change this?
Hi Sian,
ORDER BY ColumnName DESC
?
Hugo Kornelis, SQL Server MVP
|||Sorry I don't think I explained my problem properly. I can't just use desc
as this isn't what I need.
For example, I have the following parts on the shelf, in this (logical) order:
Table 1
FDB2
FDB12
FDB125
FDB1000
FDB2100
However, if I run a report that includes these part numbers on T21, the
report sorts them as follows:
Table 2
FDB1000
FDB12
FDB125
FDB2
FDB2100
I can see the sorting logic here (ie part no. is not an integer and
therefore it is read and sorted as text - from left to right) but I’m afraid
this sort order (Table 2) is no good.
We need a system option to manage/redefine sort orders for alpha-numeric
part numbers on all reports that contain part number lists.
|||"Sian" <Sian@.discussions.microsoft.com> wrote in message
news:D7CC01E7-6CD8-4DEC-B621-4F2889B1BCFA@.microsoft.com...
> Not quite sure how to word this.
> Is there any way to change the ordering of SQL Server
> I need it to order
> ABC2 then ABC12 then ABC110 (this is by entire string, rather than
> individual characters)
> currently it orders it ABC110, ABC12, ABC2
> is there anyway we can change this?
If the structure is consistant: 3 caracters followed by n figures I'd sort
on the value of the string minus the first 3 positions. Something like:
Order on VAL(MID([Myfield];4))
Or use another trick to get rid of all but figures '0123456789'
Function NumOnly(strSource) As String
' Get rid of all but figures
' on the fly adapted from other routine and untested
Dim strOK As String
Dim strPart As String * 1
Dim intLus As Integer
Dim intPlaats As Integer
NumOnly = ""
strOK = "0123456789"
While Len(strSource) > 0
strPart = Left(strSource, 1)
strSource = Mid(strSource, 2)
intPlaats = InStr(1, strOK, strPart)
If intPlaats > 0 Then
NumOnly = NumOnly + strPart
End If
Wend
End Function
and go with Order on VAL(NumOnly([MyField])
|||On Mon, 6 Feb 2006 02:11:30 -0800, Sian wrote:
>Sorry I don't think I explained my problem properly. I can't just use desc
>as this isn't what I need.
>For example, I have the following parts on the shelf, in this (logical) order:
>Table 1
> FDB2
> FDB12
> FDB125
> FDB1000
> FDB2100
>However, if I run a report that includes these part numbers on T21, the
>report sorts them as follows:
>Table 2
> FDB1000
> FDB12
> FDB125
> FDB2
> FDB2100
>I can see the sorting logic here (ie part no. is not an integer and
>therefore it is read and sorted as text - from left to right) but Im afraid
>this sort order (Table 2) is no good.
>We need a system option to manage/redefine sort orders for alpha-numeric
>part numbers on all reports that contain part number lists.
>
Hi Sian,
I was afraid of this...
The problem is caused by a flawed design. Apparently, these part numbers
are not a single code, but a concatenation of two attributes. And at
least one of those attributes has more meaning than just identifying a
part.
Since "FDB" and "1000" are two distinct attributes, they should have
been stored in two distinct columns. You can always concatenate them in
the front-end, in a view, or -if you really must- in a computed column
in the base table.
Until you get your design fixed, you can try using this kludge as a
temporary workaround:
ORDER BY LEFT(BadColumn, 3),
CAST(SUBSTRING(BadColumn, 4, LEN(BadColumn) - 3) AS integer)
However, this *will* break if you have any data in the table where the
part number doesn't follow the same layout. And it will perform very
bad, because the optimizer can't leverage an index on the BadColumn to
facilitate the sorting operation.
Hugo Kornelis, SQL Server MVP
Is there any way to change the ordering of SQL Server
I need it to order
ABC2 then ABC12 then ABC110 (this is by entire string, rather than
individual characters)
currently it orders it ABC110, ABC12, ABC2
is there anyway we can change this?
On Thu, 2 Feb 2006 02:11:27 -0800, Sian wrote:
>Not quite sure how to word this.
>Is there any way to change the ordering of SQL Server
>I need it to order
>ABC2 then ABC12 then ABC110 (this is by entire string, rather than
>individual characters)
>currently it orders it ABC110, ABC12, ABC2
>is there anyway we can change this?
Hi Sian,
ORDER BY ColumnName DESC
?
Hugo Kornelis, SQL Server MVP
|||Sorry I don't think I explained my problem properly. I can't just use desc
as this isn't what I need.
For example, I have the following parts on the shelf, in this (logical) order:
Table 1
FDB2
FDB12
FDB125
FDB1000
FDB2100
However, if I run a report that includes these part numbers on T21, the
report sorts them as follows:
Table 2
FDB1000
FDB12
FDB125
FDB2
FDB2100
I can see the sorting logic here (ie part no. is not an integer and
therefore it is read and sorted as text - from left to right) but I’m afraid
this sort order (Table 2) is no good.
We need a system option to manage/redefine sort orders for alpha-numeric
part numbers on all reports that contain part number lists.
|||"Sian" <Sian@.discussions.microsoft.com> wrote in message
news:D7CC01E7-6CD8-4DEC-B621-4F2889B1BCFA@.microsoft.com...
> Not quite sure how to word this.
> Is there any way to change the ordering of SQL Server
> I need it to order
> ABC2 then ABC12 then ABC110 (this is by entire string, rather than
> individual characters)
> currently it orders it ABC110, ABC12, ABC2
> is there anyway we can change this?
If the structure is consistant: 3 caracters followed by n figures I'd sort
on the value of the string minus the first 3 positions. Something like:
Order on VAL(MID([Myfield];4))
Or use another trick to get rid of all but figures '0123456789'
Function NumOnly(strSource) As String
' Get rid of all but figures
' on the fly adapted from other routine and untested
Dim strOK As String
Dim strPart As String * 1
Dim intLus As Integer
Dim intPlaats As Integer
NumOnly = ""
strOK = "0123456789"
While Len(strSource) > 0
strPart = Left(strSource, 1)
strSource = Mid(strSource, 2)
intPlaats = InStr(1, strOK, strPart)
If intPlaats > 0 Then
NumOnly = NumOnly + strPart
End If
Wend
End Function
and go with Order on VAL(NumOnly([MyField])
|||On Mon, 6 Feb 2006 02:11:30 -0800, Sian wrote:
>Sorry I don't think I explained my problem properly. I can't just use desc
>as this isn't what I need.
>For example, I have the following parts on the shelf, in this (logical) order:
>Table 1
> FDB2
> FDB12
> FDB125
> FDB1000
> FDB2100
>However, if I run a report that includes these part numbers on T21, the
>report sorts them as follows:
>Table 2
> FDB1000
> FDB12
> FDB125
> FDB2
> FDB2100
>I can see the sorting logic here (ie part no. is not an integer and
>therefore it is read and sorted as text - from left to right) but Im afraid
>this sort order (Table 2) is no good.
>We need a system option to manage/redefine sort orders for alpha-numeric
>part numbers on all reports that contain part number lists.
>
Hi Sian,
I was afraid of this...
The problem is caused by a flawed design. Apparently, these part numbers
are not a single code, but a concatenation of two attributes. And at
least one of those attributes has more meaning than just identifying a
part.
Since "FDB" and "1000" are two distinct attributes, they should have
been stored in two distinct columns. You can always concatenate them in
the front-end, in a view, or -if you really must- in a computed column
in the base table.
Until you get your design fixed, you can try using this kludge as a
temporary workaround:
ORDER BY LEFT(BadColumn, 3),
CAST(SUBSTRING(BadColumn, 4, LEN(BadColumn) - 3) AS integer)
However, this *will* break if you have any data in the table where the
part number doesn't follow the same layout. And it will perform very
bad, because the optimizer can't leverage an index on the BadColumn to
facilitate the sorting operation.
Hugo Kornelis, SQL Server MVP
changing order of DTS package
Hi everyone,
I have a DTS package that exports table from SQL Server to an Oracle database. However, I want to change the order in which the tables get exported because of referential integrity. I can find no way in the DTS designer to do that! IS there a way to change this order other than specifying a querY for each and every table!
Thanks,
PankajIf you're using Data Driven Query task in your package, - no. You'll need to replace it with individual table transfer tasks per table, where you can specify the order in which each table is being transfered. It's more tedious to create, but gives you much more control.
I have a DTS package that exports table from SQL Server to an Oracle database. However, I want to change the order in which the tables get exported because of referential integrity. I can find no way in the DTS designer to do that! IS there a way to change this order other than specifying a querY for each and every table!
Thanks,
PankajIf you're using Data Driven Query task in your package, - no. You'll need to replace it with individual table transfer tasks per table, where you can specify the order in which each table is being transfered. It's more tedious to create, but gives you much more control.
Changing Order of columns in a table
Is there any way to change the order (position) of columns of a table ,
without the need of dropping and recreating the table itself !?
I found the 'colid' field of the 'syscolumn' system table: by changing the
colid of each column I got the desired result, unfortunately if I have to
create indexes ih the above columns later on I get the error
Location: record.cpp: 759
Expression: pbind-> fcheckfornull ()
spID: errore 56 o 61
process ID 2976 o 2452
ID code -2147467259
as a side effect.
Moreover , I saw that in the 'syscolumns' system table , the change in the
'colid' field was an insert rather than an update...
Thanks,
Massimo.If you monkey around with the data in the sys... tables you can screw up
your database permanently.
I think you should really drop the table and re-create it.
You could also create a view with the columns in the required order.
"news" <massimo.facchi@.getronics.com> wrote in message
news:eqacvBkjDHA.2656@.TK2MSFTNGP10.phx.gbl...
> Is there any way to change the order (position) of columns of a table ,
> without the need of dropping and recreating the table itself !?
> I found the 'colid' field of the 'syscolumn' system table: by changing the
> colid of each column I got the desired result, unfortunately if I have to
> create indexes ih the above columns later on I get the error
> Location: record.cpp: 759
> Expression: pbind-> fcheckfornull ()
> spID: errore 56 o 61
> process ID 2976 o 2452
> ID code -2147467259
> as a side effect.
>
> Moreover , I saw that in the 'syscolumns' system table , the change in the
> 'colid' field was an insert rather than an update...
> Thanks,
>
> Massimo.
>
>
>|||I used to worry about the order of columns when I first started using sql,
but I quickly found I was wasting my time... As the previous poster said,
messing with system tables is not a good way to go... The order of the
columns physically in the record is different than the order you put in the
create table...
Unless you have some huge, overriding reason don't worry about column
order... Otherwise, drop and re-create the table.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"news" <massimo.facchi@.getronics.com> wrote in message
news:eqacvBkjDHA.2656@.TK2MSFTNGP10.phx.gbl...
> Is there any way to change the order (position) of columns of a table ,
> without the need of dropping and recreating the table itself !?
> I found the 'colid' field of the 'syscolumn' system table: by changing the
> colid of each column I got the desired result, unfortunately if I have to
> create indexes ih the above columns later on I get the error
> Location: record.cpp: 759
> Expression: pbind-> fcheckfornull ()
> spID: errore 56 o 61
> process ID 2976 o 2452
> ID code -2147467259
> as a side effect.
>
> Moreover , I saw that in the 'syscolumns' system table , the change in the
> 'colid' field was an insert rather than an update...
> Thanks,
>
> Massimo.
>
>
>
without the need of dropping and recreating the table itself !?
I found the 'colid' field of the 'syscolumn' system table: by changing the
colid of each column I got the desired result, unfortunately if I have to
create indexes ih the above columns later on I get the error
Location: record.cpp: 759
Expression: pbind-> fcheckfornull ()
spID: errore 56 o 61
process ID 2976 o 2452
ID code -2147467259
as a side effect.
Moreover , I saw that in the 'syscolumns' system table , the change in the
'colid' field was an insert rather than an update...
Thanks,
Massimo.If you monkey around with the data in the sys... tables you can screw up
your database permanently.
I think you should really drop the table and re-create it.
You could also create a view with the columns in the required order.
"news" <massimo.facchi@.getronics.com> wrote in message
news:eqacvBkjDHA.2656@.TK2MSFTNGP10.phx.gbl...
> Is there any way to change the order (position) of columns of a table ,
> without the need of dropping and recreating the table itself !?
> I found the 'colid' field of the 'syscolumn' system table: by changing the
> colid of each column I got the desired result, unfortunately if I have to
> create indexes ih the above columns later on I get the error
> Location: record.cpp: 759
> Expression: pbind-> fcheckfornull ()
> spID: errore 56 o 61
> process ID 2976 o 2452
> ID code -2147467259
> as a side effect.
>
> Moreover , I saw that in the 'syscolumns' system table , the change in the
> 'colid' field was an insert rather than an update...
> Thanks,
>
> Massimo.
>
>
>|||I used to worry about the order of columns when I first started using sql,
but I quickly found I was wasting my time... As the previous poster said,
messing with system tables is not a good way to go... The order of the
columns physically in the record is different than the order you put in the
create table...
Unless you have some huge, overriding reason don't worry about column
order... Otherwise, drop and re-create the table.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"news" <massimo.facchi@.getronics.com> wrote in message
news:eqacvBkjDHA.2656@.TK2MSFTNGP10.phx.gbl...
> Is there any way to change the order (position) of columns of a table ,
> without the need of dropping and recreating the table itself !?
> I found the 'colid' field of the 'syscolumn' system table: by changing the
> colid of each column I got the desired result, unfortunately if I have to
> create indexes ih the above columns later on I get the error
> Location: record.cpp: 759
> Expression: pbind-> fcheckfornull ()
> spID: errore 56 o 61
> process ID 2976 o 2452
> ID code -2147467259
> as a side effect.
>
> Moreover , I saw that in the 'syscolumns' system table , the change in the
> 'colid' field was an insert rather than an update...
> Thanks,
>
> Massimo.
>
>
>
Changing order of columns at run time
Here's the scenario:
I want to be able to create a report (typically these reports are
"table like" reports ... in other words, several columns with a list
of data underneath) and I want to be able to re-order the columns (not
the rows) in these reports at runtime.
So, for instance, if the "report design" is as follows:
COLUMNA COLUMNB COLUMNC
... data goes here ...
... I'd like to be able to pass a parameter in to the report that may
be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
would re-order, like follows:
COLUMNC COLUMNB COLUMNA
... data goes here ...
... I don't really care what report designer object is used (Table,
Matrix, etc) ... but I don't yet see a good way to do this. It seems
like the Matrix object might be able to do this via the ColumnGroups
"Sorting" tab ... but I don't seem to be able to come up with an
expression that can make this occur.
Any help is greatly appreciated.
Thanks,
RichardOn Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
> Here's the scenario:
> I want to be able to create a report (typically these reports are
> "table like" reports ... in other words, several columns with a list
> of data underneath) and I want to be able to re-order the columns (not
> the rows) in these reports at runtime.
> So, for instance, if the "report design" is as follows:
> COLUMNA COLUMNB COLUMNC
> ... data goes here ...
> ... I'd like to be able to pass a parameter in to the report that may
> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
> would re-order, like follows:
> COLUMNC COLUMNB COLUMNA
> ... data goes here ...
> ... I don't really care what report designer object is used (Table,
> Matrix, etc) ... but I don't yet see a good way to do this. It seems
> like the Matrix object might be able to do this via the ColumnGroups
> "Sorting" tab ... but I don't seem to be able to come up with an
> expression that can make this occur.
> Any help is greatly appreciated.
> Thanks,
> Richard
If you are using a matrix report, you can control the column layout
(assuming you know the column names prior to runtime) by adding spaces
in front of the names of the columns (like values in the pivot column)
and ordering/sorting by ascending order. If you are using a table
control, you can allow the user to select a sort order (asc/desc) in
the report and then set the columns in the returned resultset
accordingly. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Just Like the report was built by Report Builder..
when click title of column ,the order will be change,right?
how to do it in vs2005?
"EMartinez" <emartinez.pr1@.gmail.com>
':1177733247.239100.15830@.n59g2000hsh.googlegroups.com...
> On Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
>> Here's the scenario:
>> I want to be able to create a report (typically these reports are
>> "table like" reports ... in other words, several columns with a list
>> of data underneath) and I want to be able to re-order the columns (not
>> the rows) in these reports at runtime.
>> So, for instance, if the "report design" is as follows:
>> COLUMNA COLUMNB COLUMNC
>> ... data goes here ...
>> ... I'd like to be able to pass a parameter in to the report that may
>> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
>> would re-order, like follows:
>> COLUMNC COLUMNB COLUMNA
>> ... data goes here ...
>> ... I don't really care what report designer object is used (Table,
>> Matrix, etc) ... but I don't yet see a good way to do this. It seems
>> like the Matrix object might be able to do this via the ColumnGroups
>> "Sorting" tab ... but I don't seem to be able to come up with an
>> expression that can make this occur.
>> Any help is greatly appreciated.
>> Thanks,
>> Richard
>
> If you are using a matrix report, you can control the column layout
> (assuming you know the column names prior to runtime) by adding spaces
> in front of the names of the columns (like values in the pivot column)
> and ordering/sorting by ascending order. If you are using a table
> control, you can allow the user to select a sort order (asc/desc) in
> the report and then set the columns in the returned resultset
> accordingly. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||That's called interactive sort. click on the textbox or table textbox
properties and select the interactive sort tab and set your values.
Amarnath
"renhanyue" wrote:
> Just Like the report was built by Report Builder..
> when click title of column ,the order will be change,right?
> how to do it in vs2005?
> "EMartinez" <emartinez.pr1@.gmail.com>
> ':1177733247.239100.15830@.n59g2000hsh.googlegroups.com...
> > On Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
> >> Here's the scenario:
> >>
> >> I want to be able to create a report (typically these reports are
> >> "table like" reports ... in other words, several columns with a list
> >> of data underneath) and I want to be able to re-order the columns (not
> >> the rows) in these reports at runtime.
> >>
> >> So, for instance, if the "report design" is as follows:
> >>
> >> COLUMNA COLUMNB COLUMNC
> >> ... data goes here ...
> >>
> >> ... I'd like to be able to pass a parameter in to the report that may
> >> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
> >> would re-order, like follows:
> >>
> >> COLUMNC COLUMNB COLUMNA
> >> ... data goes here ...
> >>
> >> ... I don't really care what report designer object is used (Table,
> >> Matrix, etc) ... but I don't yet see a good way to do this. It seems
> >> like the Matrix object might be able to do this via the ColumnGroups
> >> "Sorting" tab ... but I don't seem to be able to come up with an
> >> expression that can make this occur.
> >>
> >> Any help is greatly appreciated.
> >>
> >> Thanks,
> >> Richard
> >
> >
> > If you are using a matrix report, you can control the column layout
> > (assuming you know the column names prior to runtime) by adding spaces
> > in front of the names of the columns (like values in the pivot column)
> > and ordering/sorting by ascending order. If you are using a table
> > control, you can allow the user to select a sort order (asc/desc) in
> > the report and then set the columns in the returned resultset
> > accordingly. Hope this helps.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> >
>
>|||The issue is that I'm trying to change is the order of the columns
themselves (left to right) ... not the data "within" the column (which
would be the "top to bottom").
Normally, these reports would be simple "table" reports ... the
"extra" feature that I need to give to my users is the ability specify
the order of the columns (left to right)
To further clarify, if the report where a simple SQL statement, the
report designer might specify:
SELECT A, B, C FROM SOMETABLE
... but I want to give the report "user" the ability to say
SELECT C, B, A FROM SOMETABLE
I want to be able to create a report (typically these reports are
"table like" reports ... in other words, several columns with a list
of data underneath) and I want to be able to re-order the columns (not
the rows) in these reports at runtime.
So, for instance, if the "report design" is as follows:
COLUMNA COLUMNB COLUMNC
... data goes here ...
... I'd like to be able to pass a parameter in to the report that may
be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
would re-order, like follows:
COLUMNC COLUMNB COLUMNA
... data goes here ...
... I don't really care what report designer object is used (Table,
Matrix, etc) ... but I don't yet see a good way to do this. It seems
like the Matrix object might be able to do this via the ColumnGroups
"Sorting" tab ... but I don't seem to be able to come up with an
expression that can make this occur.
Any help is greatly appreciated.
Thanks,
RichardOn Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
> Here's the scenario:
> I want to be able to create a report (typically these reports are
> "table like" reports ... in other words, several columns with a list
> of data underneath) and I want to be able to re-order the columns (not
> the rows) in these reports at runtime.
> So, for instance, if the "report design" is as follows:
> COLUMNA COLUMNB COLUMNC
> ... data goes here ...
> ... I'd like to be able to pass a parameter in to the report that may
> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
> would re-order, like follows:
> COLUMNC COLUMNB COLUMNA
> ... data goes here ...
> ... I don't really care what report designer object is used (Table,
> Matrix, etc) ... but I don't yet see a good way to do this. It seems
> like the Matrix object might be able to do this via the ColumnGroups
> "Sorting" tab ... but I don't seem to be able to come up with an
> expression that can make this occur.
> Any help is greatly appreciated.
> Thanks,
> Richard
If you are using a matrix report, you can control the column layout
(assuming you know the column names prior to runtime) by adding spaces
in front of the names of the columns (like values in the pivot column)
and ordering/sorting by ascending order. If you are using a table
control, you can allow the user to select a sort order (asc/desc) in
the report and then set the columns in the returned resultset
accordingly. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Just Like the report was built by Report Builder..
when click title of column ,the order will be change,right?
how to do it in vs2005?
"EMartinez" <emartinez.pr1@.gmail.com>
':1177733247.239100.15830@.n59g2000hsh.googlegroups.com...
> On Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
>> Here's the scenario:
>> I want to be able to create a report (typically these reports are
>> "table like" reports ... in other words, several columns with a list
>> of data underneath) and I want to be able to re-order the columns (not
>> the rows) in these reports at runtime.
>> So, for instance, if the "report design" is as follows:
>> COLUMNA COLUMNB COLUMNC
>> ... data goes here ...
>> ... I'd like to be able to pass a parameter in to the report that may
>> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
>> would re-order, like follows:
>> COLUMNC COLUMNB COLUMNA
>> ... data goes here ...
>> ... I don't really care what report designer object is used (Table,
>> Matrix, etc) ... but I don't yet see a good way to do this. It seems
>> like the Matrix object might be able to do this via the ColumnGroups
>> "Sorting" tab ... but I don't seem to be able to come up with an
>> expression that can make this occur.
>> Any help is greatly appreciated.
>> Thanks,
>> Richard
>
> If you are using a matrix report, you can control the column layout
> (assuming you know the column names prior to runtime) by adding spaces
> in front of the names of the columns (like values in the pivot column)
> and ordering/sorting by ascending order. If you are using a table
> control, you can allow the user to select a sort order (asc/desc) in
> the report and then set the columns in the returned resultset
> accordingly. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||That's called interactive sort. click on the textbox or table textbox
properties and select the interactive sort tab and set your values.
Amarnath
"renhanyue" wrote:
> Just Like the report was built by Report Builder..
> when click title of column ,the order will be change,right?
> how to do it in vs2005?
> "EMartinez" <emartinez.pr1@.gmail.com>
> ':1177733247.239100.15830@.n59g2000hsh.googlegroups.com...
> > On Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
> >> Here's the scenario:
> >>
> >> I want to be able to create a report (typically these reports are
> >> "table like" reports ... in other words, several columns with a list
> >> of data underneath) and I want to be able to re-order the columns (not
> >> the rows) in these reports at runtime.
> >>
> >> So, for instance, if the "report design" is as follows:
> >>
> >> COLUMNA COLUMNB COLUMNC
> >> ... data goes here ...
> >>
> >> ... I'd like to be able to pass a parameter in to the report that may
> >> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
> >> would re-order, like follows:
> >>
> >> COLUMNC COLUMNB COLUMNA
> >> ... data goes here ...
> >>
> >> ... I don't really care what report designer object is used (Table,
> >> Matrix, etc) ... but I don't yet see a good way to do this. It seems
> >> like the Matrix object might be able to do this via the ColumnGroups
> >> "Sorting" tab ... but I don't seem to be able to come up with an
> >> expression that can make this occur.
> >>
> >> Any help is greatly appreciated.
> >>
> >> Thanks,
> >> Richard
> >
> >
> > If you are using a matrix report, you can control the column layout
> > (assuming you know the column names prior to runtime) by adding spaces
> > in front of the names of the columns (like values in the pivot column)
> > and ordering/sorting by ascending order. If you are using a table
> > control, you can allow the user to select a sort order (asc/desc) in
> > the report and then set the columns in the returned resultset
> > accordingly. Hope this helps.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> >
>
>|||The issue is that I'm trying to change is the order of the columns
themselves (left to right) ... not the data "within" the column (which
would be the "top to bottom").
Normally, these reports would be simple "table" reports ... the
"extra" feature that I need to give to my users is the ability specify
the order of the columns (left to right)
To further clarify, if the report where a simple SQL statement, the
report designer might specify:
SELECT A, B, C FROM SOMETABLE
... but I want to give the report "user" the ability to say
SELECT C, B, A FROM SOMETABLE
Changing ORDER BY
Hi All
win 2k pro sql 2k IIS 5 ASP-vbscript
I got a stored proc which basically returns a set of records depending
on a category ID (or all if passed ID =0) - see below
The FAQ Category below is retuned mostly order by Title but in some
other areas of my site I need to return it orded by CatID.
I was wondering whats the best way to do this as I cant seem to pass a
column name to the procedure and put "ORDER BY @.SortOrder"
These are the other ways I thought of.
1) create a new procedure the same with order by catID.
2) pass a number and use CASE - but I get error 1008
eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
I can see this becoming a common problem so wouldn't like to do #1
above as that would require alot of maintence to keep them the same if
I changed any of the table design.
Thanks for any help.
Al
CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
@.SelCatID TINYINT = 0
AS
SET NOCOUNT ON
-- Return all the FAQ Categories with Cat ID of the selected Category
SELECT CatID, ParentID, Title, FaqCount
FROM Help_FAQ_Categories
WHERE CatID= CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
ORDER BY Title
GOTry
ORDER BY CASE WHEN @.sort = 'col1' THEN col1 END ASC,
"Harag" <harag@.softhome.net> wrote in message
news:3v4mlvo18e9vbjisl08jqrs9m75pc240gd@.4ax.com...
> Hi All
> win 2k pro sql 2k IIS 5 ASP-vbscript
> I got a stored proc which basically returns a set of records depending
> on a category ID (or all if passed ID =0) - see below
> The FAQ Category below is retuned mostly order by Title but in some
> other areas of my site I need to return it orded by CatID.
> I was wondering whats the best way to do this as I cant seem to pass a
> column name to the procedure and put "ORDER BY @.SortOrder"
> These are the other ways I thought of.
> 1) create a new procedure the same with order by catID.
> 2) pass a number and use CASE - but I get error 1008
> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
>
> I can see this becoming a common problem so wouldn't like to do #1
> above as that would require alot of maintence to keep them the same if
> I changed any of the table design.
> Thanks for any help.
> Al
>
> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
> @.SelCatID TINYINT = 0
> AS
> SET NOCOUNT ON
> -- Return all the FAQ Categories with Cat ID of the selected Category
> SELECT CatID, ParentID, Title, FaqCount
> FROM Help_FAQ_Categories
> WHERE CatID=> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
> ORDER BY Title
> GO
>|||Harag,
it is almost correct. However, you cannot mix data types without
explicit conversions. The typical solution to this type of problem is to
use an ORDER BY column for each sort order:
ORDER BY CASE WHEN @.Order=0 THEN Title END,
CASE WHEN @.Order=1 THEN CatID END
Gert-Jan
Harag wrote:
> Hi All
> win 2k pro sql 2k IIS 5 ASP-vbscript
> I got a stored proc which basically returns a set of records depending
> on a category ID (or all if passed ID =0) - see below
> The FAQ Category below is retuned mostly order by Title but in some
> other areas of my site I need to return it orded by CatID.
> I was wondering whats the best way to do this as I cant seem to pass a
> column name to the procedure and put "ORDER BY @.SortOrder"
> These are the other ways I thought of.
> 1) create a new procedure the same with order by catID.
> 2) pass a number and use CASE - but I get error 1008
> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
> I can see this becoming a common problem so wouldn't like to do #1
> above as that would require alot of maintence to keep them the same if
> I changed any of the table design.
> Thanks for any help.
> Al
> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
> @.SelCatID TINYINT = 0
> AS
> SET NOCOUNT ON
> -- Return all the FAQ Categories with Cat ID of the selected Category
> SELECT CatID, ParentID, Title, FaqCount
> FROM Help_FAQ_Categories
> WHERE CatID=> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
> ORDER BY Title
> GO|||Cool thanks guys this is what I wanted and will save alot of
maintence. :)
Thanks again
Al
On Sun, 07 Sep 2003 14:01:35 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>Harag,
>it is almost correct. However, you cannot mix data types without
>explicit conversions. The typical solution to this type of problem is to
>use an ORDER BY column for each sort order:
>ORDER BY CASE WHEN @.Order=0 THEN Title END,
> CASE WHEN @.Order=1 THEN CatID END
>Gert-Jan
>
>Harag wrote:
>> Hi All
>> win 2k pro sql 2k IIS 5 ASP-vbscript
>> I got a stored proc which basically returns a set of records depending
>> on a category ID (or all if passed ID =0) - see below
>> The FAQ Category below is retuned mostly order by Title but in some
>> other areas of my site I need to return it orded by CatID.
>> I was wondering whats the best way to do this as I cant seem to pass a
>> column name to the procedure and put "ORDER BY @.SortOrder"
>> These are the other ways I thought of.
>> 1) create a new procedure the same with order by catID.
>> 2) pass a number and use CASE - but I get error 1008
>> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
>> I can see this becoming a common problem so wouldn't like to do #1
>> above as that would require alot of maintence to keep them the same if
>> I changed any of the table design.
>> Thanks for any help.
>> Al
>> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
>> @.SelCatID TINYINT = 0
>> AS
>> SET NOCOUNT ON
>> -- Return all the FAQ Categories with Cat ID of the selected Category
>> SELECT CatID, ParentID, Title, FaqCount
>> FROM Help_FAQ_Categories
>> WHERE CatID=>> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
>> ORDER BY Title
>> GO
win 2k pro sql 2k IIS 5 ASP-vbscript
I got a stored proc which basically returns a set of records depending
on a category ID (or all if passed ID =0) - see below
The FAQ Category below is retuned mostly order by Title but in some
other areas of my site I need to return it orded by CatID.
I was wondering whats the best way to do this as I cant seem to pass a
column name to the procedure and put "ORDER BY @.SortOrder"
These are the other ways I thought of.
1) create a new procedure the same with order by catID.
2) pass a number and use CASE - but I get error 1008
eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
I can see this becoming a common problem so wouldn't like to do #1
above as that would require alot of maintence to keep them the same if
I changed any of the table design.
Thanks for any help.
Al
CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
@.SelCatID TINYINT = 0
AS
SET NOCOUNT ON
-- Return all the FAQ Categories with Cat ID of the selected Category
SELECT CatID, ParentID, Title, FaqCount
FROM Help_FAQ_Categories
WHERE CatID= CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
ORDER BY Title
GOTry
ORDER BY CASE WHEN @.sort = 'col1' THEN col1 END ASC,
"Harag" <harag@.softhome.net> wrote in message
news:3v4mlvo18e9vbjisl08jqrs9m75pc240gd@.4ax.com...
> Hi All
> win 2k pro sql 2k IIS 5 ASP-vbscript
> I got a stored proc which basically returns a set of records depending
> on a category ID (or all if passed ID =0) - see below
> The FAQ Category below is retuned mostly order by Title but in some
> other areas of my site I need to return it orded by CatID.
> I was wondering whats the best way to do this as I cant seem to pass a
> column name to the procedure and put "ORDER BY @.SortOrder"
> These are the other ways I thought of.
> 1) create a new procedure the same with order by catID.
> 2) pass a number and use CASE - but I get error 1008
> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
>
> I can see this becoming a common problem so wouldn't like to do #1
> above as that would require alot of maintence to keep them the same if
> I changed any of the table design.
> Thanks for any help.
> Al
>
> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
> @.SelCatID TINYINT = 0
> AS
> SET NOCOUNT ON
> -- Return all the FAQ Categories with Cat ID of the selected Category
> SELECT CatID, ParentID, Title, FaqCount
> FROM Help_FAQ_Categories
> WHERE CatID=> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
> ORDER BY Title
> GO
>|||Harag,
it is almost correct. However, you cannot mix data types without
explicit conversions. The typical solution to this type of problem is to
use an ORDER BY column for each sort order:
ORDER BY CASE WHEN @.Order=0 THEN Title END,
CASE WHEN @.Order=1 THEN CatID END
Gert-Jan
Harag wrote:
> Hi All
> win 2k pro sql 2k IIS 5 ASP-vbscript
> I got a stored proc which basically returns a set of records depending
> on a category ID (or all if passed ID =0) - see below
> The FAQ Category below is retuned mostly order by Title but in some
> other areas of my site I need to return it orded by CatID.
> I was wondering whats the best way to do this as I cant seem to pass a
> column name to the procedure and put "ORDER BY @.SortOrder"
> These are the other ways I thought of.
> 1) create a new procedure the same with order by catID.
> 2) pass a number and use CASE - but I get error 1008
> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
> I can see this becoming a common problem so wouldn't like to do #1
> above as that would require alot of maintence to keep them the same if
> I changed any of the table design.
> Thanks for any help.
> Al
> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
> @.SelCatID TINYINT = 0
> AS
> SET NOCOUNT ON
> -- Return all the FAQ Categories with Cat ID of the selected Category
> SELECT CatID, ParentID, Title, FaqCount
> FROM Help_FAQ_Categories
> WHERE CatID=> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
> ORDER BY Title
> GO|||Cool thanks guys this is what I wanted and will save alot of
maintence. :)
Thanks again
Al
On Sun, 07 Sep 2003 14:01:35 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>Harag,
>it is almost correct. However, you cannot mix data types without
>explicit conversions. The typical solution to this type of problem is to
>use an ORDER BY column for each sort order:
>ORDER BY CASE WHEN @.Order=0 THEN Title END,
> CASE WHEN @.Order=1 THEN CatID END
>Gert-Jan
>
>Harag wrote:
>> Hi All
>> win 2k pro sql 2k IIS 5 ASP-vbscript
>> I got a stored proc which basically returns a set of records depending
>> on a category ID (or all if passed ID =0) - see below
>> The FAQ Category below is retuned mostly order by Title but in some
>> other areas of my site I need to return it orded by CatID.
>> I was wondering whats the best way to do this as I cant seem to pass a
>> column name to the procedure and put "ORDER BY @.SortOrder"
>> These are the other ways I thought of.
>> 1) create a new procedure the same with order by catID.
>> 2) pass a number and use CASE - but I get error 1008
>> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
>> I can see this becoming a common problem so wouldn't like to do #1
>> above as that would require alot of maintence to keep them the same if
>> I changed any of the table design.
>> Thanks for any help.
>> Al
>> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
>> @.SelCatID TINYINT = 0
>> AS
>> SET NOCOUNT ON
>> -- Return all the FAQ Categories with Cat ID of the selected Category
>> SELECT CatID, ParentID, Title, FaqCount
>> FROM Help_FAQ_Categories
>> WHERE CatID=>> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
>> ORDER BY Title
>> GO
changing one field updates 3 other fields, use a join?
i currently have a table like this..
user_id username app_id app2_id app3_id app4_id
1 john 3 4 5 6
2 mike 4 5 6 6
3 manager 4 5 6 6
4 vicepres 5 6 6 6
5 ceo 6 6 6 6
6 board 6 6 6 6
the basic pattern is... a user has approvers, and those approvers have approvers as well... i have 4 columns of approvers.. and if my first approver is the manager, then my second approver will be the managers approver and my third approver will be the managers approver's approver and so on..
on my actual page, i have select fields for the app, app2, app3, app4 and i need it so that when i change the very first app, it'll automatically update app2, app3, and app4
any ideas how i would do this? im pretty new to sql but im thinking i would use some type of join?
thanksLook into triggers. You'd make an UPDATE trigger that executes automatically when an update occurs on the table. You can then check to see if the update is to app_id, and make changes to the other fields accordingly.
There are two types of triggers, INSTEAD OF and AFTER. You need to determine if you want to replace the original UPDATE with some SQL you want the trigger to execute, or if you want the trigger to execute after the original UPDATE occurrs.|||Look into triggers. You'd make an UPDATE trigger that executes automatically when an update occurs on the table. You can then check to see if the update is to app_id, and make changes to the other fields accordingly.
There are two types of triggers, INSTEAD OF and AFTER. You need to determine if you want to replace the original UPDATE with some SQL you want the trigger to execute, or if you want the trigger to execute after the original UPDATE occurrs.
im pretty new to sql, im reading up on triggers and examples of them right now.. they show some code for the triggers.. would that whole code be the entire SQL query or would i code that in perl ( what im using that to access the SQL database)?
thanks|||You actually store triggers at the table level of the DB. In Enterprise Manager, drill down into your DB, and down to your table. Then right click on the table you wish to apply a trigger to and select "All Tasks" then "Manage Triggers".|||You actually store triggers at the table level of the DB. In Enterprise Manager, drill down into your DB, and down to your table. Then right click on the table you wish to apply a trigger to and select "All Tasks" then "Manage Triggers".
ahh ok, found it, thanks|||ok i got it working... so now, if i update an approver for a specific user, the trigger will run and go ahead and update the app2, app3, and app4 so it does what it's suppose to.
so i still have my original table as above, but now i added this trigger to the table:
CREATE TRIGGER approvers ON users FOR UPDATE AS
IF UPDATE(approver_id)
BEGIN
DECLARE @.thisUser VARCHAR(10)
DECLARE @.newApp VARCHAR(10)
DECLARE @.newApp2 VARCHAR(10)
DECLARE @.newApp3 VARCHAR(10)
DECLARE @.newApp4 VARCHAR(10)
SELECT @.thisUser = (SELECT user_id FROM Inserted )
SELECT @.newApp = (SELECT approver_id FROM Inserted)
SELECT @.newApp2 = (SELECT approver_id from users WHERE user_id = @.newApp)
SELECT @.newApp3 = (SELECT approver_id from users WHERE user_id = @.newApp2)
SELECT @.newApp4 = (SELECT approver_id from users WHERE user_id = @.newApp3)
UPDATE users SET app2_id = @.newApp2 WHERE user_id = @.thisUser
UPDATE users SET app3_id = @.newApp3 WHERE user_id = @.thisUser
UPDATE users SET app4_id = @.newApp4 WHERE user_id = @.thisUser
END
but i just thought of a problem, say i update the approver of an approver, i.e. i update app_id of manager and change it from vice pres to the ceo, so i change app_id for the manager from 4 to 5.. then all the rows that had the manager as an app, app_2, app_3, or app_4 would need to be updated to since the managers approve is no longer the same person, the other peoples app2, wouldnt be the same and therefore their app3, and app4 wont be the same either .. i hope someone understand what im saying... how should i go about fixing this? like if i change the approver of manager, would i have to return all the rows that had manager as an app, app_2, app_3, or app_4 and then update all of those rows somehow? like looping through each of the rows returned?
im really new to sql, i hope someone can guide me through this
thanks|||Look into "Cascading Triggers". That may help you out.|||got it working now, thanks :)
user_id username app_id app2_id app3_id app4_id
1 john 3 4 5 6
2 mike 4 5 6 6
3 manager 4 5 6 6
4 vicepres 5 6 6 6
5 ceo 6 6 6 6
6 board 6 6 6 6
the basic pattern is... a user has approvers, and those approvers have approvers as well... i have 4 columns of approvers.. and if my first approver is the manager, then my second approver will be the managers approver and my third approver will be the managers approver's approver and so on..
on my actual page, i have select fields for the app, app2, app3, app4 and i need it so that when i change the very first app, it'll automatically update app2, app3, and app4
any ideas how i would do this? im pretty new to sql but im thinking i would use some type of join?
thanksLook into triggers. You'd make an UPDATE trigger that executes automatically when an update occurs on the table. You can then check to see if the update is to app_id, and make changes to the other fields accordingly.
There are two types of triggers, INSTEAD OF and AFTER. You need to determine if you want to replace the original UPDATE with some SQL you want the trigger to execute, or if you want the trigger to execute after the original UPDATE occurrs.|||Look into triggers. You'd make an UPDATE trigger that executes automatically when an update occurs on the table. You can then check to see if the update is to app_id, and make changes to the other fields accordingly.
There are two types of triggers, INSTEAD OF and AFTER. You need to determine if you want to replace the original UPDATE with some SQL you want the trigger to execute, or if you want the trigger to execute after the original UPDATE occurrs.
im pretty new to sql, im reading up on triggers and examples of them right now.. they show some code for the triggers.. would that whole code be the entire SQL query or would i code that in perl ( what im using that to access the SQL database)?
thanks|||You actually store triggers at the table level of the DB. In Enterprise Manager, drill down into your DB, and down to your table. Then right click on the table you wish to apply a trigger to and select "All Tasks" then "Manage Triggers".|||You actually store triggers at the table level of the DB. In Enterprise Manager, drill down into your DB, and down to your table. Then right click on the table you wish to apply a trigger to and select "All Tasks" then "Manage Triggers".
ahh ok, found it, thanks|||ok i got it working... so now, if i update an approver for a specific user, the trigger will run and go ahead and update the app2, app3, and app4 so it does what it's suppose to.
so i still have my original table as above, but now i added this trigger to the table:
CREATE TRIGGER approvers ON users FOR UPDATE AS
IF UPDATE(approver_id)
BEGIN
DECLARE @.thisUser VARCHAR(10)
DECLARE @.newApp VARCHAR(10)
DECLARE @.newApp2 VARCHAR(10)
DECLARE @.newApp3 VARCHAR(10)
DECLARE @.newApp4 VARCHAR(10)
SELECT @.thisUser = (SELECT user_id FROM Inserted )
SELECT @.newApp = (SELECT approver_id FROM Inserted)
SELECT @.newApp2 = (SELECT approver_id from users WHERE user_id = @.newApp)
SELECT @.newApp3 = (SELECT approver_id from users WHERE user_id = @.newApp2)
SELECT @.newApp4 = (SELECT approver_id from users WHERE user_id = @.newApp3)
UPDATE users SET app2_id = @.newApp2 WHERE user_id = @.thisUser
UPDATE users SET app3_id = @.newApp3 WHERE user_id = @.thisUser
UPDATE users SET app4_id = @.newApp4 WHERE user_id = @.thisUser
END
but i just thought of a problem, say i update the approver of an approver, i.e. i update app_id of manager and change it from vice pres to the ceo, so i change app_id for the manager from 4 to 5.. then all the rows that had the manager as an app, app_2, app_3, or app_4 would need to be updated to since the managers approve is no longer the same person, the other peoples app2, wouldnt be the same and therefore their app3, and app4 wont be the same either .. i hope someone understand what im saying... how should i go about fixing this? like if i change the approver of manager, would i have to return all the rows that had manager as an app, app_2, app_3, or app_4 and then update all of those rows somehow? like looping through each of the rows returned?
im really new to sql, i hope someone can guide me through this
thanks|||Look into "Cascading Triggers". That may help you out.|||got it working now, thanks :)
changing one column in each row
ie if i have
x 1 2 3 4 5
----
1| a b c d e
2| f g h i j
3| k l m n o
4| p q r s t
5| u v w x y
and i want to change it to
x 1 2 3 4 5
----
1| a b c d e
2| a b c d e
3| a b c d e
4| a b c d e
5| a b c d e
any ideas on how to do that?Hi, Zellos!
ZM> any ideas on how to do that?
have one:
update t
set t.fld1=t2.fld1,
t.fld2=t2.fld2,
t.fld3=t2.fld3,
t.fld4=t2.fld4,
t.fld5=t2.fld5
from table1 t, (select top 1 * from table1) t2
-
xexe!|||"Zellos Mokozu" <me@.zellos.net> wrote in message
news:a20163e6.0405251825.33e6abe3@.posting.google.c om...
> ie if i have
> x 1 2 3 4 5
> ----
> 1| a b c d e
> 2| f g h i j
> 3| k l m n o
> 4| p q r s t
> 5| u v w x y
>
> and i want to change it to
I'm confused. Why not simply, Update foo set 1='a', 2='b', 3='c', 4='d',
5='e'
It looks like all your rows are the same.
> x 1 2 3 4 5
> ----
> 1| a b c d e
> 2| a b c d e
> 3| a b c d e
> 4| a b c d e
> 5| a b c d e
> any ideas on how to do that?
Changing of displat text of a textobject
Hi, I am viewing a report (*.rpt file) from the crviewer. However, I need to change the text of one of the textobject in the rpt during runtime. Can this be done and how is it done. Thanks for reading this.Using RDC and CR 8.5, this can be accomplished by:
'dsrReport is the name of the dsr file
Set Report = New dsrReport
'txtTextObjectName is the name of the TextObject
Report.txtTextObjectName.SetText ("Enter Text Here")
I know you're not using RDC, but maybe you can adapt it to your needs?
'dsrReport is the name of the dsr file
Set Report = New dsrReport
'txtTextObjectName is the name of the TextObject
Report.txtTextObjectName.SetText ("Enter Text Here")
I know you're not using RDC, but maybe you can adapt it to your needs?
Changing of @@IDENTITY item in a trigger
Hello!
I have a instead of insert trigger that after doing some validation, inserts a record into a specific table. Another table requires the key generated by this insert in order to add a record, which is done at the end of the trigger. When called from a client (via a recordset.update call) the newly created key from the first insert is overridden with the key from the second insert. Any ideas on how I can save the first key, then pass it back to the client? The client currently receives the second key created.
Thanks in advance,
EverettI have exactly the same problem and although I don't have a solution (yet), I have found out that this appears to be by design. ADO does a "Select @.@.IDENTITY" after an insert to retrieve the key of the record inserted (See Chapter 11 of "Programming ADO" by David Sceppa at http://www.microsoft.com/mspress/books/sampchap/3445a.asp). If there was some way to modify ADO's behaviour to use instead "Select IDENT_CURRENT('<tablename>')" then problem solved. The article describes a "Update Resync" dynamic record set property but it doesn't appear to be flexible enough to tell ADO to use IDENT_CURRENT.
The hunt continues...
I have a instead of insert trigger that after doing some validation, inserts a record into a specific table. Another table requires the key generated by this insert in order to add a record, which is done at the end of the trigger. When called from a client (via a recordset.update call) the newly created key from the first insert is overridden with the key from the second insert. Any ideas on how I can save the first key, then pass it back to the client? The client currently receives the second key created.
Thanks in advance,
EverettI have exactly the same problem and although I don't have a solution (yet), I have found out that this appears to be by design. ADO does a "Select @.@.IDENTITY" after an insert to retrieve the key of the record inserted (See Chapter 11 of "Programming ADO" by David Sceppa at http://www.microsoft.com/mspress/books/sampchap/3445a.asp). If there was some way to modify ADO's behaviour to use instead "Select IDENT_CURRENT('<tablename>')" then problem solved. The article describes a "Update Resync" dynamic record set property but it doesn't appear to be flexible enough to tell ADO to use IDENT_CURRENT.
The hunt continues...
Changing object ownership in SQL 6.5?
Help!
I've been using SQL 7.0 & 2K for the last four years but need to
change the ownership of some tables in a legacy database residing on
SQL 6.5 from (user) to dbo. I know you can use sp_ChangeObjectOwner
in 7/2K but what is it's equivalent in 6.5 please?
Regards
ALIThere is no in 6.5. You can of course try to look at the source code of
sp_changeobjectowner and try to do the same on a 6.5 install, but that is
not supported and is risky (there might be some architectural reason why
this ability wasn't introduced until 7.0).
I suggest you re-create the object.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ALI" <ali.shah@.itnetplc.com> wrote in message
news:8487385c.0311110159.56b09753@.posting.google.com...
> Help!
> I've been using SQL 7.0 & 2K for the last four years but need to
> change the ownership of some tables in a legacy database residing on
> SQL 6.5 from (user) to dbo. I know you can use sp_ChangeObjectOwner
> in 7/2K but what is it's equivalent in 6.5 please?
> Regards
> ALI
I've been using SQL 7.0 & 2K for the last four years but need to
change the ownership of some tables in a legacy database residing on
SQL 6.5 from (user) to dbo. I know you can use sp_ChangeObjectOwner
in 7/2K but what is it's equivalent in 6.5 please?
Regards
ALIThere is no in 6.5. You can of course try to look at the source code of
sp_changeobjectowner and try to do the same on a 6.5 install, but that is
not supported and is risky (there might be some architectural reason why
this ability wasn't introduced until 7.0).
I suggest you re-create the object.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ALI" <ali.shah@.itnetplc.com> wrote in message
news:8487385c.0311110159.56b09753@.posting.google.com...
> Help!
> I've been using SQL 7.0 & 2K for the last four years but need to
> change the ownership of some tables in a legacy database residing on
> SQL 6.5 from (user) to dbo. I know you can use sp_ChangeObjectOwner
> in 7/2K but what is it's equivalent in 6.5 please?
> Regards
> ALI
Changing object ownership back to dbo
Hi:
I have a database where objects are owned by different users. I would like
to revert ownership of all objects to dbo. What is the best way to do this?
Thanks,
Charliesp_changeobjectowner
David Portas
SQL Server MVP
--
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:Oe9b3gfzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi:
> I have a database where objects are owned by different users. I would
> like
> to revert ownership of all objects to dbo. What is the best way to do
> this?
> Thanks,
> Charlie
>|||Hi,
Use the below script. Execute the script by replacing oldobjectowner and new
user. This query will give you script for changing the object.
Just cut and paste the output to a new query analyzer window and execute to
change the object owner.
set quoted_identifier off
select 'sp_changeobjectowner '+name+"'"+'newusername' +"'"+char(10)+'go'
from sysobjects where user_name(uid)='oldobjectowner'
set quoted_identifier on
Thanks
Hari
SQL Server MVP
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:Oe9b3gfzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi:
> I have a database where objects are owned by different users. I would
> like
> to revert ownership of all objects to dbo. What is the best way to do
> this?
> Thanks,
> Charlie
>
I have a database where objects are owned by different users. I would like
to revert ownership of all objects to dbo. What is the best way to do this?
Thanks,
Charliesp_changeobjectowner
David Portas
SQL Server MVP
--
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:Oe9b3gfzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi:
> I have a database where objects are owned by different users. I would
> like
> to revert ownership of all objects to dbo. What is the best way to do
> this?
> Thanks,
> Charlie
>|||Hi,
Use the below script. Execute the script by replacing oldobjectowner and new
user. This query will give you script for changing the object.
Just cut and paste the output to a new query analyzer window and execute to
change the object owner.
set quoted_identifier off
select 'sp_changeobjectowner '+name+"'"+'newusername' +"'"+char(10)+'go'
from sysobjects where user_name(uid)='oldobjectowner'
set quoted_identifier on
Thanks
Hari
SQL Server MVP
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:Oe9b3gfzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi:
> I have a database where objects are owned by different users. I would
> like
> to revert ownership of all objects to dbo. What is the best way to do
> this?
> Thanks,
> Charlie
>
changing object owner syntax
Hi there,
We have a user name convention here that specifies users in the
following way:
jane.doe
john.smith
We have a user name convention here that specifies users in the
following way:
jane.doe
john.smith
In order to change a database object I must specify the owner name along
with the object name. Does anyone know the proper syntax for doing this
with a user name that contains a period? When I put the user name
followed by the object name in quotes such as 'jane.doe.tb_test_table',
SQL Server can not find the object.
Any help is appreciated.
Kelly"Kelly Prendergast" <kelly.prendergast@.noaa.gov> wrote in message
news:403B9200.6882586F@.noaa.gov...
> Hi there,
> We have a user name convention here that specifies users in the
> following way:
> jane.doe
> john.smith
> In order to change a database object I must specify the owner name along
> with the object name. Does anyone know the proper syntax for doing this
> with a user name that contains a period? When I put the user name
> followed by the object name in quotes such as 'jane.doe.tb_test_table',
> SQL Server can not find the object.
> Any help is appreciated.
> Kelly
You will need to quote the owner name:
exec sp_changeobjectowner '[jane.doe].tb_test_table', 'dbo'
You may want to consider changing your naming convention - since the .
character delimits object name parts in MSSQL, it would probably be best to
avoid confusiong by not allowing it in user names. Although of course I
appreciate this may be beyond your control.
Simon
Changing Object Explorer Details Window
is it possible to change the columns that display in the Object Explorer
Details Window? I'd like to be able see some Extended Properties in this
window.
TIA
Dean
Nope...sorry but that's not an option.
-Sue
On Fri, 10 Aug 2007 09:51:26 -0400, "Dean"
<deanl144@.hotmail.com.nospam> wrote:
>is it possible to change the columns that display in the Object Explorer
>Details Window? I'd like to be able see some Extended Properties in this
>window.
>TIA
>Dean
>
Details Window? I'd like to be able see some Extended Properties in this
window.
TIA
Dean
Nope...sorry but that's not an option.
-Sue
On Fri, 10 Aug 2007 09:51:26 -0400, "Dean"
<deanl144@.hotmail.com.nospam> wrote:
>is it possible to change the columns that display in the Object Explorer
>Details Window? I'd like to be able see some Extended Properties in this
>window.
>TIA
>Dean
>
Changing nVarChar lengths
I have a db with about 6.5 million records. A few
records have a set number of characters, however the
table design specifies more characters than is really
necessary. I'd like to reclaim as much space as
possible. Am I safe to change the field lengths for the
Char fields to the maximum number of characters I know
they will ever contain, without losing any data? Will I
have to change to nVarChar? If so, any data loss?If your sure the new size is larger than the largest piece of data for any
row you should not have a problem in changing it from a database standpoint.
But you may break existing code or apps if you do. That depends on how they
use it etc.
--
Andrew J. Kelly
SQL Server MVP
"Mike" <tatemike44@.hotmail.com> wrote in message
news:033201c36e6e$8574ad20$a401280a@.phx.gbl...
> I have a db with about 6.5 million records. A few
> records have a set number of characters, however the
> table design specifies more characters than is really
> necessary. I'd like to reclaim as much space as
> possible. Am I safe to change the field lengths for the
> Char fields to the maximum number of characters I know
> they will ever contain, without losing any data? Will I
> have to change to nVarChar? If so, any data loss?
records have a set number of characters, however the
table design specifies more characters than is really
necessary. I'd like to reclaim as much space as
possible. Am I safe to change the field lengths for the
Char fields to the maximum number of characters I know
they will ever contain, without losing any data? Will I
have to change to nVarChar? If so, any data loss?If your sure the new size is larger than the largest piece of data for any
row you should not have a problem in changing it from a database standpoint.
But you may break existing code or apps if you do. That depends on how they
use it etc.
--
Andrew J. Kelly
SQL Server MVP
"Mike" <tatemike44@.hotmail.com> wrote in message
news:033201c36e6e$8574ad20$a401280a@.phx.gbl...
> I have a db with about 6.5 million records. A few
> records have a set number of characters, however the
> table design specifies more characters than is really
> necessary. I'd like to reclaim as much space as
> possible. Am I safe to change the field lengths for the
> Char fields to the maximum number of characters I know
> they will ever contain, without losing any data? Will I
> have to change to nVarChar? If so, any data loss?
Changing Number to Text in Query
I have two fields that have either a 0 or 1 in them. When I do a SELECT to
bring the data back, I would like to change the 0 to an "N" and a 1 to a
"Y".
I read up on the CASE statement and had something like but kept getting
syntax errors.
Select Name, Address, City,
CASE field_in_question
WHEN 0 THEN 'N'
WHEN 1 THEN 'Y'
END,
State, Zip from Notes
Can someone help a feebled old brain figure this one out?
Thanks,
Gary>> I read up on the CASE statement and had something like but kept getting
Can you post the error messages here?
Anith|||The error message says "Syntax error (missing operator) in query expression.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OCdGspmAFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Can you post the error messages here?
> --
> Anith
>|||Since your original post has no attributions, let us start from simple
issues. Are you use SQL Server or Access? CASE is not supported in Access.
If you are using SQL Server, are you using Query Analyzer? Some other
interfaces have limitations on certain constructs they allow
If you are using Query Analyzer, can you post your CREATE TABLE statement
for the table Notes? For details refer to www.aspfaq.com/5006
Anith|||That's the problem. I am using Access with a Visual Basic front end.
Is there a way to do what I need to do in Access SQL?
Thanks,
Gary
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eS5ywFnAFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Since your original post has no attributions, let us start from simple
> issues. Are you use SQL Server or Access? CASE is not supported in Access.
> If you are using SQL Server, are you using Query Analyzer? Some other
> interfaces have limitations on certain constructs they allow
> If you are using Query Analyzer, can you post your CREATE TABLE statement
> for the table Notes? For details refer to www.aspfaq.com/5006
> --
> Anith
>|||Create a stored procedure or a view, and select from that within Access
instead of accessing the table directly.
http://www.aspfaq.com/
(Reverse address to reply.)
"Gary Paris" <garyparis@.yada.com> wrote in message
news:ePQKtTnAFHA.2712@.TK2MSFTNGP15.phx.gbl...
> That's the problem. I am using Access with a Visual Basic front end.
> Is there a way to do what I need to do in Access SQL?
> Thanks,
> Gary
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:eS5ywFnAFHA.3416@.TK2MSFTNGP09.phx.gbl...
Access.
statement
>|||Oh, and don't use the view designer, because it doesn't support case! Use
Query Analyzer...
http://www.aspfaq.com/
(Reverse address to reply.)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uNFbUYnAFHA.2196@.TK2MSFTNGP14.phx.gbl...
> Create a stored procedure or a view, and select from that within Access
> instead of accessing the table directly.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Gary Paris" <garyparis@.yada.com> wrote in message
> news:ePQKtTnAFHA.2712@.TK2MSFTNGP15.phx.gbl...
> Access.
> statement
>|||I am using Access as the back end and not using SQL server. I don't believe
Query Analyzer comes with Access (although I could be wrong). Would it be
better to post my question in the Access group or is there a way to do what
I want with Access SQL?
Thanks,
Gary
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e4cZzanAFHA.3264@.TK2MSFTNGP12.phx.gbl...
> Oh, and don't use the view designer, because it doesn't support case! Use
> Query Analyzer...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uNFbUYnAFHA.2196@.TK2MSFTNGP14.phx.gbl...
>|||Sorry, I thought you were using Access to connect to SQL Server. Yes, you
can do this in Access using the IIF function. See the Access documentation
or post to an Access newsgroup. This group is dedicated to programming
in/for SQL Server.
"Gary Paris" <garyparis@.yada.com> wrote in message
news:O3$5VinAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> I am using Access as the back end and not using SQL server. I don't
believe
> Query Analyzer comes with Access (although I could be wrong). Would it
be
> better to post my question in the Access group or is there a way to do
what
> I want with Access SQL?
> Thanks,
> Gary
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:e4cZzanAFHA.3264@.TK2MSFTNGP12.phx.gbl...
Use
other
>
bring the data back, I would like to change the 0 to an "N" and a 1 to a
"Y".
I read up on the CASE statement and had something like but kept getting
syntax errors.
Select Name, Address, City,
CASE field_in_question
WHEN 0 THEN 'N'
WHEN 1 THEN 'Y'
END,
State, Zip from Notes
Can someone help a feebled old brain figure this one out?
Thanks,
Gary>> I read up on the CASE statement and had something like but kept getting
Can you post the error messages here?
Anith|||The error message says "Syntax error (missing operator) in query expression.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OCdGspmAFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Can you post the error messages here?
> --
> Anith
>|||Since your original post has no attributions, let us start from simple
issues. Are you use SQL Server or Access? CASE is not supported in Access.
If you are using SQL Server, are you using Query Analyzer? Some other
interfaces have limitations on certain constructs they allow
If you are using Query Analyzer, can you post your CREATE TABLE statement
for the table Notes? For details refer to www.aspfaq.com/5006
Anith|||That's the problem. I am using Access with a Visual Basic front end.
Is there a way to do what I need to do in Access SQL?
Thanks,
Gary
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eS5ywFnAFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Since your original post has no attributions, let us start from simple
> issues. Are you use SQL Server or Access? CASE is not supported in Access.
> If you are using SQL Server, are you using Query Analyzer? Some other
> interfaces have limitations on certain constructs they allow
> If you are using Query Analyzer, can you post your CREATE TABLE statement
> for the table Notes? For details refer to www.aspfaq.com/5006
> --
> Anith
>|||Create a stored procedure or a view, and select from that within Access
instead of accessing the table directly.
http://www.aspfaq.com/
(Reverse address to reply.)
"Gary Paris" <garyparis@.yada.com> wrote in message
news:ePQKtTnAFHA.2712@.TK2MSFTNGP15.phx.gbl...
> That's the problem. I am using Access with a Visual Basic front end.
> Is there a way to do what I need to do in Access SQL?
> Thanks,
> Gary
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:eS5ywFnAFHA.3416@.TK2MSFTNGP09.phx.gbl...
Access.
statement
>|||Oh, and don't use the view designer, because it doesn't support case! Use
Query Analyzer...
http://www.aspfaq.com/
(Reverse address to reply.)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uNFbUYnAFHA.2196@.TK2MSFTNGP14.phx.gbl...
> Create a stored procedure or a view, and select from that within Access
> instead of accessing the table directly.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Gary Paris" <garyparis@.yada.com> wrote in message
> news:ePQKtTnAFHA.2712@.TK2MSFTNGP15.phx.gbl...
> Access.
> statement
>|||I am using Access as the back end and not using SQL server. I don't believe
Query Analyzer comes with Access (although I could be wrong). Would it be
better to post my question in the Access group or is there a way to do what
I want with Access SQL?
Thanks,
Gary
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e4cZzanAFHA.3264@.TK2MSFTNGP12.phx.gbl...
> Oh, and don't use the view designer, because it doesn't support case! Use
> Query Analyzer...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uNFbUYnAFHA.2196@.TK2MSFTNGP14.phx.gbl...
>|||Sorry, I thought you were using Access to connect to SQL Server. Yes, you
can do this in Access using the IIF function. See the Access documentation
or post to an Access newsgroup. This group is dedicated to programming
in/for SQL Server.
"Gary Paris" <garyparis@.yada.com> wrote in message
news:O3$5VinAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> I am using Access as the back end and not using SQL server. I don't
believe
> Query Analyzer comes with Access (although I could be wrong). Would it
be
> better to post my question in the Access group or is there a way to do
what
> I want with Access SQL?
> Thanks,
> Gary
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:e4cZzanAFHA.3264@.TK2MSFTNGP12.phx.gbl...
Use
other
>
Changing number to date
I have a table in SQL Server that has a date field whose properties are 'number'. I want to export this data to a table in Oracle. I can't touch the Server table to alter it to be a date. Is there some way I can do this in the Oracle table? Example- SQL Server= 20030324 (March 24,2003). In Oracle I want it to read '24/03/2003', or some other date format.declare @.d as int
set @.d = 20031105
select cast(substring(cast(@.d as varchar(8)),5,2)+ '/' +
substring(cast(@.d as varchar(8)),7,2)+ '/' +
left(cast(@.d as varchar(8)),4)as smalldatetime)|||I need to update a whole column in an Oracle table. Thanks.
set @.d = 20031105
select cast(substring(cast(@.d as varchar(8)),5,2)+ '/' +
substring(cast(@.d as varchar(8)),7,2)+ '/' +
left(cast(@.d as varchar(8)),4)as smalldatetime)|||I need to update a whole column in an Oracle table. Thanks.
Changing number of per processor licenses
I want to change the number of licenses from 2 to 1 so I can use one license
on another machine. To accomplish this, do I need to physically remove the
CPU or I can unselect 1 processor from the Processor Tab of properties sheet
.You would violate license agreement by only unselecting one processor from E
M's config dialog. The
license terms states you need one proc license per processor that the OS can
use. So either disable
one processor in BIOS (if possible) or remove it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SB138" <SB138@.discussions.microsoft.com> wrote in message
news:0768CDED-D7D6-4E3F-8450-25B56D297A64@.microsoft.com...[vbcol=seagreen]
>I want to change the number of licenses from 2 to 1 so I can use one licens
e
> on another machine. To accomplish this, do I need to physically remove the
> CPU or I can unselect 1 processor from the Processor Tab of properties sheet.[/vbc
ol]
on another machine. To accomplish this, do I need to physically remove the
CPU or I can unselect 1 processor from the Processor Tab of properties sheet
.You would violate license agreement by only unselecting one processor from E
M's config dialog. The
license terms states you need one proc license per processor that the OS can
use. So either disable
one processor in BIOS (if possible) or remove it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SB138" <SB138@.discussions.microsoft.com> wrote in message
news:0768CDED-D7D6-4E3F-8450-25B56D297A64@.microsoft.com...[vbcol=seagreen]
>I want to change the number of licenses from 2 to 1 so I can use one licens
e
> on another machine. To accomplish this, do I need to physically remove the
> CPU or I can unselect 1 processor from the Processor Tab of properties sheet.[/vbc
ol]
Changing number of per processor licenses
I want to change the number of licenses from 2 to 1 so I can use one license
on another machine. To accomplish this, do I need to physically remove the
CPU or I can unselect 1 processor from the Processor Tab of properties sheet.
You would violate license agreement by only unselecting one processor from EM's config dialog. The
license terms states you need one proc license per processor that the OS can use. So either disable
one processor in BIOS (if possible) or remove it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SB138" <SB138@.discussions.microsoft.com> wrote in message
news:0768CDED-D7D6-4E3F-8450-25B56D297A64@.microsoft.com...
>I want to change the number of licenses from 2 to 1 so I can use one license
> on another machine. To accomplish this, do I need to physically remove the
> CPU or I can unselect 1 processor from the Processor Tab of properties sheet.
on another machine. To accomplish this, do I need to physically remove the
CPU or I can unselect 1 processor from the Processor Tab of properties sheet.
You would violate license agreement by only unselecting one processor from EM's config dialog. The
license terms states you need one proc license per processor that the OS can use. So either disable
one processor in BIOS (if possible) or remove it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SB138" <SB138@.discussions.microsoft.com> wrote in message
news:0768CDED-D7D6-4E3F-8450-25B56D297A64@.microsoft.com...
>I want to change the number of licenses from 2 to 1 so I can use one license
> on another machine. To accomplish this, do I need to physically remove the
> CPU or I can unselect 1 processor from the Processor Tab of properties sheet.
Changing number of per processor licenses
I want to change the number of licenses from 2 to 1 so I can use one license
on another machine. To accomplish this, do I need to physically remove the
CPU or I can unselect 1 processor from the Processor Tab of properties sheet.You would violate license agreement by only unselecting one processor from EM's config dialog. The
license terms states you need one proc license per processor that the OS can use. So either disable
one processor in BIOS (if possible) or remove it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SB138" <SB138@.discussions.microsoft.com> wrote in message
news:0768CDED-D7D6-4E3F-8450-25B56D297A64@.microsoft.com...
>I want to change the number of licenses from 2 to 1 so I can use one license
> on another machine. To accomplish this, do I need to physically remove the
> CPU or I can unselect 1 processor from the Processor Tab of properties sheet.
on another machine. To accomplish this, do I need to physically remove the
CPU or I can unselect 1 processor from the Processor Tab of properties sheet.You would violate license agreement by only unselecting one processor from EM's config dialog. The
license terms states you need one proc license per processor that the OS can use. So either disable
one processor in BIOS (if possible) or remove it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SB138" <SB138@.discussions.microsoft.com> wrote in message
news:0768CDED-D7D6-4E3F-8450-25B56D297A64@.microsoft.com...
>I want to change the number of licenses from 2 to 1 so I can use one license
> on another machine. To accomplish this, do I need to physically remove the
> CPU or I can unselect 1 processor from the Processor Tab of properties sheet.
Changing number of data files
I have a 225Gb database on a test server. It is currently in one file and I
would like to split it into several files smaller files. What is the best
way of doing this?
thanks
GavGav
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
"Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
news:bu3072$ltb$1@.titan.btinternet.com...
> I have a 225Gb database on a test server. It is currently in one file and
I
> would like to split it into several files smaller files. What is the best
> way of doing this?
> thanks
> Gav
>|||Hello,
Uri's solution will work, however you will have to drop
and re-create tables to add data to the filegroups.
This is because when you create a table (or index) you
also state which filegroup you will be saving your data to.
J
>--Original Message--
>I have a 225Gb database on a test server. It is currently
in one file and I
>would like to split it into several files smaller files.
What is the best
>way of doing this?
>thanks
>Gav
>
>.
>|||If I create another data file is it possible to move data from one file to
the other? I currently have a 220Gb and a 50Gb file I would like to fill up
the 50Gb file so I can resize the 220Gb file. Unfortunately I don't have
much spare storage space so I'm not sure how else I would do it. Only other
thing I can think of doing is dropping the entire database and recreating it
with more files, then I could restore a backup version.
Gav
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...
> Hello,
> Uri's solution will work, however you will have to drop
> and re-create tables to add data to the filegroups.
> This is because when you create a table (or index) you
> also state which filegroup you will be saving your data to.
> J
> >--Original Message--
> >I have a 225Gb database on a test server. It is currently
> in one file and I
> >would like to split it into several files smaller files.
> What is the best
> >way of doing this?
> >
> >thanks
> >
> >Gav
> >
> >
> >.
> >|||Yes, but that sort of depends where your data file is. for
instance you can create a data file on a different drive /
server than the one your using now, but if you create it
on a different server you will need to watch your network
speed.
The problem is not going to be your data files as such,
but filling them.
When you create a table/index you explicitly say
in T-SQL which data file any data the table is going to
store saves to.
Creating extra data files does will not move the data.
I don't think (and please someone tell me if I'm wrong)
whether a backup will work as a backup has in it the
database stucture so if you restore it you also restore
the number of datafiles you had before i.e 1.
There is a way of saving some time, and that it to do with
indexes.
About 1 third of all storeage space is used by indexes
(both clustered and non clusted) so hat you could do is
BACKUP YOUR DATABASE
(Sorry bit paraniod about data)
1. Create a new datafile called indexes.
2. Under EM Database - all tasks - generate SQL
Create 2 scripts one to remove all indexes and primary keys
one to re create them.
3. Drop all you indexes
4. In the script that has the create for the indexes
perform a search and replace for all the old data file
name to the new one.
5. Execute the script.
This should move about 70+ gb to your new file.
J
>--Original Message--
>If I create another data file is it possible to move data
from one file to
>the other? I currently have a 220Gb and a 50Gb file I
would like to fill up
>the 50Gb file so I can resize the 220Gb file.
Unfortunately I don't have
>much spare storage space so I'm not sure how else I would
do it. Only other
>thing I can think of doing is dropping the entire
database and recreating it
>with more files, then I could restore a backup version.
>Gav
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...
>> Hello,
>> Uri's solution will work, however you will have to drop
>> and re-create tables to add data to the filegroups.
>> This is because when you create a table (or index) you
>> also state which filegroup you will be saving your data
to.
>> J
>> >--Original Message--
>> >I have a 225Gb database on a test server. It is
currently
>> in one file and I
>> >would like to split it into several files smaller
files.
>> What is the best
>> >way of doing this?
>> >
>> >thanks
>> >
>> >Gav
>> >
>> >
>> >.
>> >
>
>.
>|||Gav
As Julie says, if you change the database and then restore
an old backup you will end up with the structure of the
old database.
If you want to create a second data file (or more) and
populate it you have serveral options.
If you want them poulated evenly, rebuild all your
clustered indexes(or in the case where you don't have any
build a clustered index and the drop it). SQL Server will
spread your tables evenly for you and you should end up
with two near equal data files.
If you want to control size/placement create a second file
group. Make the second data file part of the second file
group. You can then move tables to the new file group from
the design table pane in Enterprise manager.
You could also create a new database and use dts to
transfer the data. You would need more space though to do
that.
Hope this helps
John
>--Original Message--
>Yes, but that sort of depends where your data file is.
for
>instance you can create a data file on a different
drive /
>server than the one your using now, but if you create it
>on a different server you will need to watch your network
>speed.
>The problem is not going to be your data files as such,
>but filling them.
>When you create a table/index you explicitly say
>in T-SQL which data file any data the table is going to
>store saves to.
>Creating extra data files does will not move the data.
>I don't think (and please someone tell me if I'm wrong)
>whether a backup will work as a backup has in it the
>database stucture so if you restore it you also restore
>the number of datafiles you had before i.e 1.
>There is a way of saving some time, and that it to do
with
>indexes.
>About 1 third of all storeage space is used by indexes
>(both clustered and non clusted) so hat you could do is
>BACKUP YOUR DATABASE
>(Sorry bit paraniod about data)
>1. Create a new datafile called indexes.
>2. Under EM Database - all tasks - generate SQL
>Create 2 scripts one to remove all indexes and primary
keys
>one to re create them.
>3. Drop all you indexes
>4. In the script that has the create for the indexes
>perform a search and replace for all the old data file
>name to the new one.
>5. Execute the script.
>This should move about 70+ gb to your new file.
>J
>
>
>>--Original Message--
>>If I create another data file is it possible to move
data
>from one file to
>>the other? I currently have a 220Gb and a 50Gb file I
>would like to fill up
>>the 50Gb file so I can resize the 220Gb file.
>Unfortunately I don't have
>>much spare storage space so I'm not sure how else I
would
>do it. Only other
>>thing I can think of doing is dropping the entire
>database and recreating it
>>with more files, then I could restore a backup version.
>>Gav
>>"Julie" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...
>> Hello,
>> Uri's solution will work, however you will have to drop
>> and re-create tables to add data to the filegroups.
>> This is because when you create a table (or index) you
>> also state which filegroup you will be saving your
data
>to.
>> J
>> >--Original Message--
>> >I have a 225Gb database on a test server. It is
>currently
>> in one file and I
>> >would like to split it into several files smaller
>files.
>> What is the best
>> >way of doing this?
>> >
>> >thanks
>> >
>> >Gav
>> >
>> >
>> >.
>> >
>>
>>.
>.
>
would like to split it into several files smaller files. What is the best
way of doing this?
thanks
GavGav
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
"Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
news:bu3072$ltb$1@.titan.btinternet.com...
> I have a 225Gb database on a test server. It is currently in one file and
I
> would like to split it into several files smaller files. What is the best
> way of doing this?
> thanks
> Gav
>|||Hello,
Uri's solution will work, however you will have to drop
and re-create tables to add data to the filegroups.
This is because when you create a table (or index) you
also state which filegroup you will be saving your data to.
J
>--Original Message--
>I have a 225Gb database on a test server. It is currently
in one file and I
>would like to split it into several files smaller files.
What is the best
>way of doing this?
>thanks
>Gav
>
>.
>|||If I create another data file is it possible to move data from one file to
the other? I currently have a 220Gb and a 50Gb file I would like to fill up
the 50Gb file so I can resize the 220Gb file. Unfortunately I don't have
much spare storage space so I'm not sure how else I would do it. Only other
thing I can think of doing is dropping the entire database and recreating it
with more files, then I could restore a backup version.
Gav
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...
> Hello,
> Uri's solution will work, however you will have to drop
> and re-create tables to add data to the filegroups.
> This is because when you create a table (or index) you
> also state which filegroup you will be saving your data to.
> J
> >--Original Message--
> >I have a 225Gb database on a test server. It is currently
> in one file and I
> >would like to split it into several files smaller files.
> What is the best
> >way of doing this?
> >
> >thanks
> >
> >Gav
> >
> >
> >.
> >|||Yes, but that sort of depends where your data file is. for
instance you can create a data file on a different drive /
server than the one your using now, but if you create it
on a different server you will need to watch your network
speed.
The problem is not going to be your data files as such,
but filling them.
When you create a table/index you explicitly say
in T-SQL which data file any data the table is going to
store saves to.
Creating extra data files does will not move the data.
I don't think (and please someone tell me if I'm wrong)
whether a backup will work as a backup has in it the
database stucture so if you restore it you also restore
the number of datafiles you had before i.e 1.
There is a way of saving some time, and that it to do with
indexes.
About 1 third of all storeage space is used by indexes
(both clustered and non clusted) so hat you could do is
BACKUP YOUR DATABASE
(Sorry bit paraniod about data)
1. Create a new datafile called indexes.
2. Under EM Database - all tasks - generate SQL
Create 2 scripts one to remove all indexes and primary keys
one to re create them.
3. Drop all you indexes
4. In the script that has the create for the indexes
perform a search and replace for all the old data file
name to the new one.
5. Execute the script.
This should move about 70+ gb to your new file.
J
>--Original Message--
>If I create another data file is it possible to move data
from one file to
>the other? I currently have a 220Gb and a 50Gb file I
would like to fill up
>the 50Gb file so I can resize the 220Gb file.
Unfortunately I don't have
>much spare storage space so I'm not sure how else I would
do it. Only other
>thing I can think of doing is dropping the entire
database and recreating it
>with more files, then I could restore a backup version.
>Gav
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...
>> Hello,
>> Uri's solution will work, however you will have to drop
>> and re-create tables to add data to the filegroups.
>> This is because when you create a table (or index) you
>> also state which filegroup you will be saving your data
to.
>> J
>> >--Original Message--
>> >I have a 225Gb database on a test server. It is
currently
>> in one file and I
>> >would like to split it into several files smaller
files.
>> What is the best
>> >way of doing this?
>> >
>> >thanks
>> >
>> >Gav
>> >
>> >
>> >.
>> >
>
>.
>|||Gav
As Julie says, if you change the database and then restore
an old backup you will end up with the structure of the
old database.
If you want to create a second data file (or more) and
populate it you have serveral options.
If you want them poulated evenly, rebuild all your
clustered indexes(or in the case where you don't have any
build a clustered index and the drop it). SQL Server will
spread your tables evenly for you and you should end up
with two near equal data files.
If you want to control size/placement create a second file
group. Make the second data file part of the second file
group. You can then move tables to the new file group from
the design table pane in Enterprise manager.
You could also create a new database and use dts to
transfer the data. You would need more space though to do
that.
Hope this helps
John
>--Original Message--
>Yes, but that sort of depends where your data file is.
for
>instance you can create a data file on a different
drive /
>server than the one your using now, but if you create it
>on a different server you will need to watch your network
>speed.
>The problem is not going to be your data files as such,
>but filling them.
>When you create a table/index you explicitly say
>in T-SQL which data file any data the table is going to
>store saves to.
>Creating extra data files does will not move the data.
>I don't think (and please someone tell me if I'm wrong)
>whether a backup will work as a backup has in it the
>database stucture so if you restore it you also restore
>the number of datafiles you had before i.e 1.
>There is a way of saving some time, and that it to do
with
>indexes.
>About 1 third of all storeage space is used by indexes
>(both clustered and non clusted) so hat you could do is
>BACKUP YOUR DATABASE
>(Sorry bit paraniod about data)
>1. Create a new datafile called indexes.
>2. Under EM Database - all tasks - generate SQL
>Create 2 scripts one to remove all indexes and primary
keys
>one to re create them.
>3. Drop all you indexes
>4. In the script that has the create for the indexes
>perform a search and replace for all the old data file
>name to the new one.
>5. Execute the script.
>This should move about 70+ gb to your new file.
>J
>
>
>>--Original Message--
>>If I create another data file is it possible to move
data
>from one file to
>>the other? I currently have a 220Gb and a 50Gb file I
>would like to fill up
>>the 50Gb file so I can resize the 220Gb file.
>Unfortunately I don't have
>>much spare storage space so I'm not sure how else I
would
>do it. Only other
>>thing I can think of doing is dropping the entire
>database and recreating it
>>with more files, then I could restore a backup version.
>>Gav
>>"Julie" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...
>> Hello,
>> Uri's solution will work, however you will have to drop
>> and re-create tables to add data to the filegroups.
>> This is because when you create a table (or index) you
>> also state which filegroup you will be saving your
data
>to.
>> J
>> >--Original Message--
>> >I have a 225Gb database on a test server. It is
>currently
>> in one file and I
>> >would like to split it into several files smaller
>files.
>> What is the best
>> >way of doing this?
>> >
>> >thanks
>> >
>> >Gav
>> >
>> >
>> >.
>> >
>>
>>.
>.
>
Changing number of data files
Hello,
Uri's solution will work, however you will have to drop
and re-create tables to add data to the filegroups.
This is because when you create a table (or index) you
also state which filegroup you will be saving your data to.
J
in one file and I
What is the best
the other? I currently have a 220Gb and a 50Gb file I would like to fill up
the 50Gb file so I can resize the 220Gb file. Unfortunately I don't have
much spare storage space so I'm not sure how else I would do it. Only other
thing I can think of doing is dropping the entire database and recreating it
with more files, then I could restore a backup version.
Gav
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...[QUOTE]
> Hello,
> Uri's solution will work, however you will have to drop
> and re-create tables to add data to the filegroups.
> This is because when you create a table (or index) you
> also state which filegroup you will be saving your data to.
> J
>
> in one file and I
> What is the best|||Yes, but that sort of depends where your data file is. for
instance you can create a data file on a different drive /
server than the one your using now, but if you create it
on a different server you will need to watch your network
speed.
The problem is not going to be your data files as such,
but filling them.
When you create a table/index you explicitly say
in T-SQL which data file any data the table is going to
store saves to.
Creating extra data files does will not move the data.
I don't think (and please someone tell me if I'm wrong)
whether a backup will work as a backup has in it the
database stucture so if you restore it you also restore
the number of datafiles you had before i.e 1.
There is a way of saving some time, and that it to do with
indexes.
About 1 third of all storeage space is used by indexes
(both clustered and non clusted) so hat you could do is
BACKUP YOUR DATABASE
(Sorry bit paraniod about data)
1. Create a new datafile called indexes.
2. Under EM Database - all tasks - generate SQL
Create 2 scripts one to remove all indexes and primary keys
one to re create them.
3. Drop all you indexes
4. In the script that has the create for the indexes
perform a search and replace for all the old data file
name to the new one.
5. Execute the script.
This should move about 70+ gb to your new file.
J
from one file to
would like to fill up
Unfortunately I don't have
do it. Only other
database and recreating it
message
As Julie says, if you change the database and then restore
an old backup you will end up with the structure of the
old database.
If you want to create a second data file (or more) and
populate it you have serveral options.
If you want them poulated evenly, rebuild all your
clustered indexes(or in the case where you don't have any
build a clustered index and the drop it). SQL Server will
spread your tables evenly for you and you should end up
with two near equal data files.
If you want to control size/placement create a second file
group. Make the second data file part of the second file
group. You can then move tables to the new file group from
the design table pane in Enterprise manager.
You could also create a new database and use dts to
transfer the data. You would need more space though to do
that.
Hope this helps
John
for
drive /
with
keys
Uri's solution will work, however you will have to drop
and re-create tables to add data to the filegroups.
This is because when you create a table (or index) you
also state which filegroup you will be saving your data to.
J
quote:
>--Original Message--
>I have a 225Gb database on a test server. It is currently
in one file and I
quote:
>would like to split it into several files smaller files.
What is the best
quote:If I create another data file is it possible to move data from one file to
>way of doing this?
>thanks
>Gav
>
>.
>
the other? I currently have a 220Gb and a 50Gb file I would like to fill up
the 50Gb file so I can resize the 220Gb file. Unfortunately I don't have
much spare storage space so I'm not sure how else I would do it. Only other
thing I can think of doing is dropping the entire database and recreating it
with more files, then I could restore a backup version.
Gav
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...[QUOTE]
> Hello,
> Uri's solution will work, however you will have to drop
> and re-create tables to add data to the filegroups.
> This is because when you create a table (or index) you
> also state which filegroup you will be saving your data to.
> J
>
> in one file and I
> What is the best|||Yes, but that sort of depends where your data file is. for
instance you can create a data file on a different drive /
server than the one your using now, but if you create it
on a different server you will need to watch your network
speed.
The problem is not going to be your data files as such,
but filling them.
When you create a table/index you explicitly say
in T-SQL which data file any data the table is going to
store saves to.
Creating extra data files does will not move the data.
I don't think (and please someone tell me if I'm wrong)
whether a backup will work as a backup has in it the
database stucture so if you restore it you also restore
the number of datafiles you had before i.e 1.
There is a way of saving some time, and that it to do with
indexes.
About 1 third of all storeage space is used by indexes
(both clustered and non clusted) so hat you could do is
BACKUP YOUR DATABASE
(Sorry bit paraniod about data)
1. Create a new datafile called indexes.
2. Under EM Database - all tasks - generate SQL
Create 2 scripts one to remove all indexes and primary keys
one to re create them.
3. Drop all you indexes
4. In the script that has the create for the indexes
perform a search and replace for all the old data file
name to the new one.
5. Execute the script.
This should move about 70+ gb to your new file.
J
quote:
>--Original Message--
>If I create another data file is it possible to move data
from one file to
quote:
>the other? I currently have a 220Gb and a 50Gb file I
would like to fill up
quote:
>the 50Gb file so I can resize the 220Gb file.
Unfortunately I don't have
quote:
>much spare storage space so I'm not sure how else I would
do it. Only other
quote:
>thing I can think of doing is dropping the entire
database and recreating it
quote:
>with more files, then I could restore a backup version.
>Gav
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||Gav
>news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...
to.[QUOTE]
currently[QUOTE]
files.[QUOTE]
>
>.
>
As Julie says, if you change the database and then restore
an old backup you will end up with the structure of the
old database.
If you want to create a second data file (or more) and
populate it you have serveral options.
If you want them poulated evenly, rebuild all your
clustered indexes(or in the case where you don't have any
build a clustered index and the drop it). SQL Server will
spread your tables evenly for you and you should end up
with two near equal data files.
If you want to control size/placement create a second file
group. Make the second data file part of the second file
group. You can then move tables to the new file group from
the design table pane in Enterprise manager.
You could also create a new database and use dts to
transfer the data. You would need more space though to do
that.
Hope this helps
John
quote:
>--Original Message--
>Yes, but that sort of depends where your data file is.
for
quote:
>instance you can create a data file on a different
drive /
quote:
>server than the one your using now, but if you create it
>on a different server you will need to watch your network
>speed.
>The problem is not going to be your data files as such,
>but filling them.
>When you create a table/index you explicitly say
>in T-SQL which data file any data the table is going to
>store saves to.
>Creating extra data files does will not move the data.
>I don't think (and please someone tell me if I'm wrong)
>whether a backup will work as a backup has in it the
>database stucture so if you restore it you also restore
>the number of datafiles you had before i.e 1.
>There is a way of saving some time, and that it to do
with
quote:
>indexes.
>About 1 third of all storeage space is used by indexes
>(both clustered and non clusted) so hat you could do is
>BACKUP YOUR DATABASE
>(Sorry bit paraniod about data)
>1. Create a new datafile called indexes.
>2. Under EM Database - all tasks - generate SQL
>Create 2 scripts one to remove all indexes and primary
keys
quote:
>one to re create them.
>3. Drop all you indexes
>4. In the script that has the create for the indexes
>perform a search and replace for all the old data file
>name to the new one.
>5. Execute the script.
>This should move about 70+ gb to your new file.
>J
>
>
data[QUOTE]
>from one file to
>would like to fill up
>Unfortunately I don't have
would[QUOTE]
>do it. Only other
>database and recreating it
>message
data[QUOTE]
>to.
>currently
>files.
>.
>
Changing number of data files
I have a 225Gb database on a test server. It is currently in one file and I
would like to split it into several files smaller files. What is the best
way of doing this?
thanks
GavGav
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
"Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
news:bu3072$ltb$1@.titan.btinternet.com...
I
would like to split it into several files smaller files. What is the best
way of doing this?
thanks
GavGav
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
"Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
news:bu3072$ltb$1@.titan.btinternet.com...
quote:
> I have a 225Gb database on a test server. It is currently in one file and
I
quote:
> would like to split it into several files smaller files. What is the best
> way of doing this?
> thanks
> Gav
>
changing nullability- any other options besides alter
I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegroups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegroups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>
changing nullability- any other options besides alter
I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegroups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegroups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>
changing nullability- any other options besides alter
I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.
TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark
|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegro ups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.
TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark
|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegro ups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>
Changing NULL, to NOT NULL
SQL Server 2005
When I run the command:
alter table FileRepository alter column parentID int not null
I get the message:
Msg 5074, Level 16, State 1, Line 1
The index 'IX_FileRepository' is dependent on column 'parentID'.
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
'parentID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
this column.
No problem on the index as that is easy to drop and recreate. But what is
this stuff about statistics and how do I deal with it?
FWIW, I know just enough about 2000 to do my job, but diddle about 2005.You drop statistics in the same way that you drop indexes. Typically, you
don't have both statistics and indexes on the same column - except, say, if
you have an index on a single column and statistics on a pair of columns,
and vice-versa. In your case, it looks like the Database Tuning Advisor
recommended the stats and the DBA running the DTA allowed them to be built.
You'll have to run:
DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
SQL Server 2005
When I run the command:
alter table FileRepository alter column parentID int not null
I get the message:
Msg 5074, Level 16, State 1, Line 1
The index 'IX_FileRepository' is dependent on column 'parentID'.
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
'parentID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
this column.
No problem on the index as that is easy to drop and recreate. But what is
this stuff about statistics and how do I deal with it?
FWIW, I know just enough about 2000 to do my job, but diddle about 2005.|||Thank you sir.
After having a further conversation, it seems they have tried the alter
already and it ran for several hours before timing out ( a very large table).
This implies to me that it is doing a table rebuild, even though all data in
that column is already NOT NULL and only the DDL needs to be changed.
Am I probably right here? Can we make this type of change without it taking
more than a few seconds to do the alter?
"Tom Moreau" wrote:
> You drop statistics in the same way that you drop indexes. Typically, you
> don't have both statistics and indexes on the same column - except, say, if
> you have an index on a single column and statistics on a pair of columns,
> and vice-versa. In your case, it looks like the Database Tuning Advisor
> recommended the stats and the DBA running the DTA allowed them to be built.
> You'll have to run:
> DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
> SQL Server 2005
> When I run the command:
> alter table FileRepository alter column parentID int not null
> I get the message:
> Msg 5074, Level 16, State 1, Line 1
> The index 'IX_FileRepository' is dependent on column 'parentID'.
> Msg 5074, Level 16, State 1, Line 1
> The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
> 'parentID'.
> Msg 4922, Level 16, State 9, Line 1
> ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
> this column.
>
> No problem on the index as that is easy to drop and recreate. But what is
> this stuff about statistics and how do I deal with it?
> FWIW, I know just enough about 2000 to do my job, but diddle about 2005.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0A6F_01C77871.851AEB80
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Well, that's the issue. If you have a lot of data, it will take a long
time. Your log may fill up during that time. Ultimately, you may need to
create a new table, with the intended DDL. Then, you move the data in
chunks to the other table. Meanwhile, you create a partitioned view (with
the same name as the old table) that includes both tables. However, you
will need to add INSTEAD OF triggers for any INSERT/UPDATE/DELETE activity
that will occur during that time. Once all data have been moved, you can
drop the original table and view, renaming the new table.
The attached code has an example.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:3E756320-3BA2-46CA-A2E6-9DBD404D83B4@.microsoft.com...
Thank you sir.
After having a further conversation, it seems they have tried the alter
already and it ran for several hours before timing out ( a very large
table).
This implies to me that it is doing a table rebuild, even though all data in
that column is already NOT NULL and only the DDL needs to be changed.
Am I probably right here? Can we make this type of change without it taking
more than a few seconds to do the alter?
"Tom Moreau" wrote:
> You drop statistics in the same way that you drop indexes. Typically, you
> don't have both statistics and indexes on the same column - except, say,
> if
> you have an index on a single column and statistics on a pair of columns,
> and vice-versa. In your case, it looks like the Database Tuning Advisor
> recommended the stats and the DBA running the DTA allowed them to be
> built.
> You'll have to run:
> DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
> SQL Server 2005
> When I run the command:
> alter table FileRepository alter column parentID int not null
> I get the message:
> Msg 5074, Level 16, State 1, Line 1
> The index 'IX_FileRepository' is dependent on column 'parentID'.
> Msg 5074, Level 16, State 1, Line 1
> The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
> 'parentID'.
> Msg 4922, Level 16, State 9, Line 1
> ALTER TABLE ALTER COLUMN parentID failed because one or more objects
> access
> this column.
>
> No problem on the index as that is easy to drop and recreate. But what is
> this stuff about statistics and how do I deal with it?
> FWIW, I know just enough about 2000 to do my job, but diddle about 2005.
>
--=_NextPart_000_0A6F_01C77871.851AEB80
Content-Type: text/plain;
name="Alter.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="Alter.sql"
-- Listing 1
create table dbo.t1
(
id int primary key nonclustered
, dt datetime not null
, junk varchar (20) not null
)
create clustered index C_t1 on dbo.t1 (dt)
create table dbo.t2
(
id int primary key nonclustered
, dt datetime not null
, junk varchar (20) not null
, other1 bit not null
default (0)
, other2 varchar (30) null
)
create clustered index C_t2 on dbo.t2 (dt)
create view dbo.t
as
select
id
, dt
, junk
from
dbo.t2
union all
select
id
, dt
, junk
from
dbo.t1
go
-- Listing 2
create table dbo.threshold
(
threshold datetime not null
)
insert dbo.threshold values ('1995-01-01')
go
-- Listing 3
create trigger tri_t on dbo.t instead of insert
as
insert dbo.t1 (id, dt, junk)
select id, dt, junk
from inserted
where dt >= (select threshold from dbo.threshold)
insert dbo.t2 (id, dt, junk)
select id, dt, junk
from inserted
where dt < (select threshold from dbo.threshold)
go
-- Listing 4
create trigger tru_t on dbo.t instead of update
as
if (update (dt))
begin
-- no crossing of threshold
update x
set
dt = i.dt
, junk = i.junk
from
inserted i
join deleted d on d.id = i.id
join dbo.t2 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt < z.threshold
update x
set
dt = i.dt
, junk = i.junk
from
inserted i
join deleted d on d.id = i.id
join dbo.t1 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt >= z.threshold
-- threshold crossed
delete x
from
inserted i
join deleted d on d.id = i.id
join dbo.t2 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt < z.threshold
delete x
from
inserted i
join deleted d on d.id = i.id
join dbo.t1 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt >= z.threshold
insert dbo.t1 (id, dt, junk)
select i.id, i.dt, i.junk
from
inserted i
join deleted d on d.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt < z.threshold
insert dbo.t2 (id, dt, junk)
select i.id, i.dt, i.junk
from
inserted i
join deleted d on d.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt >= z.threshold
end
else
begin
update x
set
junk = i.junk
from
inserted i
join dbo.t1 x on x.id = i.id
where
i.dt >= (select threshold from dbo.threshold)
update x
set
junk = i.junk
from
inserted i
join dbo.t2 x on x.id = i.id
where
i.dt < (select threshold from dbo.threshold)
end
go
-- Listing 5
create trigger tru_t on dbo.t instead of delete
as
if @.@.ROWCOUNT = 0
return
delete x
from
deleted d
join dbo.t1 x on x.id = d.id
delete x
from
deleted d
join dbo.t2 x on x.id = d.id
go
-- populate the table
declare
@.year smallint
, @.month tinyint
, @.day tinyint
, @.id int
, @.date datetime
, @.icount int
, @.max int
select
@.year = 1995
, @.id = 1
while @.year < 2004
begin
set @.month = 1
while @.month <= 12
begin
set @.day = 1
while @.day <= case when @.month in (1, 3, 5, 7, 8, 10, 12) then 31
when @.month in (4, 6, 9, 11) then 30
when @.year % 4 = 0 then 29
else 28 end
begin
set @.date = cast (@.year as char (4))
+ replace (str (@.month, 2), ' ', '0')
+ replace (str (@.day, 2), ' ', '0')
select
@.max = rand () * 1000
, @.icount = 1
while @.icount <= @.max
begin
insert t1 (id, dt, junk)
values (@.id, @.date, replicate ('X', 20))
select
@.icount = @.icount + 1
, @.id = @.id + 1
end
set @.day = @.day + 1
end
set @.month = @.month + 1
end
set @.year = @.year + 1
end
go
-- Listing 6
-- migrate the data
declare
@.date datetime
, @.str varchar (8000)
select
@.date = min (dt)
from
t1
while @.date is not null and not datepart (hh, getdate()) between 8 and 18
begin
begin tran
update threshold
set
threshold = dateadd (dd, 1, @.date)
insert t2 (id, dt, junk)
select id, dt, junk
from t1
where dt = @.date
delete t1
where dt = @.date
commit tran
select
@.date = min (dt)
from
t1
end
/*
drop view t
drop table t1
drop table t2
drop table threshold
*/
--=_NextPart_000_0A6F_01C77871.851AEB80--|||> After having a further conversation, it seems they have tried the alter
> already and it ran for several hours before timing out ( a very large
> table).
> This implies to me that it is doing a table rebuild, even though all data
> in
> that column is already NOT NULL and only the DDL needs to be changed.
> Am I probably right here?
That depends, did they open Enterprise Manager and click around in the GUI
to make the change? By default, in most cases, when you click OK Enterprise
Manager will do exactly that behind your back: create a new table, move the
data over, drop the old table, rename the new table to the old name, etc.
No exactly pretty.
You should probably wait to attempt this during a maintenance window (e.g.
on a Sunday at midnight), and even enforce it with setting the database to
SINGLE_USER, and then you won't have to worry about blocking users, or being
blocked by users, or being slowed down by other activity in the database.
If it still takes too long, you may have to use Tom's solution, which is a
bit more complex but is certainly better than what Enterprise Manager tries
to do.|||> You should probably wait to attempt this
And by "this" I mean an ALTER statement in Query Analyzer, not clicking OK
to the change in Enterprise Manager.|||Yes, they tried to do it through the GUI.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > After having a further conversation, it seems they have tried the alter
> > already and it ran for several hours before timing out ( a very large
> > table).
> > This implies to me that it is doing a table rebuild, even though all data
> > in
> > that column is already NOT NULL and only the DDL needs to be changed.
> >
> > Am I probably right here?
> That depends, did they open Enterprise Manager and click around in the GUI
> to make the change? By default, in most cases, when you click OK Enterprise
> Manager will do exactly that behind your back: create a new table, move the
> data over, drop the old table, rename the new table to the old name, etc.
> No exactly pretty.
> You should probably wait to attempt this during a maintenance window (e.g.
> on a Sunday at midnight), and even enforce it with setting the database to
> SINGLE_USER, and then you won't have to worry about blocking users, or being
> blocked by users, or being slowed down by other activity in the database.
> If it still takes too long, you may have to use Tom's solution, which is a
> bit more complex but is certainly better than what Enterprise Manager tries
> to do.
>
>|||At present, I'm on a dev server (our only 2005 box).
As it happens, the ALTER statement does see that it's a simple DDL change
and took a couple of seconds.
I'm releived that SQL Server is smart enough to do that.
Thank you very much.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > After having a further conversation, it seems they have tried the alter
> > already and it ran for several hours before timing out ( a very large
> > table).
> > This implies to me that it is doing a table rebuild, even though all data
> > in
> > that column is already NOT NULL and only the DDL needs to be changed.
> >
> > Am I probably right here?
> That depends, did they open Enterprise Manager and click around in the GUI
> to make the change? By default, in most cases, when you click OK Enterprise
> Manager will do exactly that behind your back: create a new table, move the
> data over, drop the old table, rename the new table to the old name, etc.
> No exactly pretty.
> You should probably wait to attempt this during a maintenance window (e.g.
> on a Sunday at midnight), and even enforce it with setting the database to
> SINGLE_USER, and then you won't have to worry about blocking users, or being
> blocked by users, or being slowed down by other activity in the database.
> If it still takes too long, you may have to use Tom's solution, which is a
> bit more complex but is certainly better than what Enterprise Manager tries
> to do.
>
>
When I run the command:
alter table FileRepository alter column parentID int not null
I get the message:
Msg 5074, Level 16, State 1, Line 1
The index 'IX_FileRepository' is dependent on column 'parentID'.
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
'parentID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
this column.
No problem on the index as that is easy to drop and recreate. But what is
this stuff about statistics and how do I deal with it?
FWIW, I know just enough about 2000 to do my job, but diddle about 2005.You drop statistics in the same way that you drop indexes. Typically, you
don't have both statistics and indexes on the same column - except, say, if
you have an index on a single column and statistics on a pair of columns,
and vice-versa. In your case, it looks like the Database Tuning Advisor
recommended the stats and the DBA running the DTA allowed them to be built.
You'll have to run:
DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
SQL Server 2005
When I run the command:
alter table FileRepository alter column parentID int not null
I get the message:
Msg 5074, Level 16, State 1, Line 1
The index 'IX_FileRepository' is dependent on column 'parentID'.
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
'parentID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
this column.
No problem on the index as that is easy to drop and recreate. But what is
this stuff about statistics and how do I deal with it?
FWIW, I know just enough about 2000 to do my job, but diddle about 2005.|||Thank you sir.
After having a further conversation, it seems they have tried the alter
already and it ran for several hours before timing out ( a very large table).
This implies to me that it is doing a table rebuild, even though all data in
that column is already NOT NULL and only the DDL needs to be changed.
Am I probably right here? Can we make this type of change without it taking
more than a few seconds to do the alter?
"Tom Moreau" wrote:
> You drop statistics in the same way that you drop indexes. Typically, you
> don't have both statistics and indexes on the same column - except, say, if
> you have an index on a single column and statistics on a pair of columns,
> and vice-versa. In your case, it looks like the Database Tuning Advisor
> recommended the stats and the DBA running the DTA allowed them to be built.
> You'll have to run:
> DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
> SQL Server 2005
> When I run the command:
> alter table FileRepository alter column parentID int not null
> I get the message:
> Msg 5074, Level 16, State 1, Line 1
> The index 'IX_FileRepository' is dependent on column 'parentID'.
> Msg 5074, Level 16, State 1, Line 1
> The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
> 'parentID'.
> Msg 4922, Level 16, State 9, Line 1
> ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
> this column.
>
> No problem on the index as that is easy to drop and recreate. But what is
> this stuff about statistics and how do I deal with it?
> FWIW, I know just enough about 2000 to do my job, but diddle about 2005.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0A6F_01C77871.851AEB80
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Well, that's the issue. If you have a lot of data, it will take a long
time. Your log may fill up during that time. Ultimately, you may need to
create a new table, with the intended DDL. Then, you move the data in
chunks to the other table. Meanwhile, you create a partitioned view (with
the same name as the old table) that includes both tables. However, you
will need to add INSTEAD OF triggers for any INSERT/UPDATE/DELETE activity
that will occur during that time. Once all data have been moved, you can
drop the original table and view, renaming the new table.
The attached code has an example.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:3E756320-3BA2-46CA-A2E6-9DBD404D83B4@.microsoft.com...
Thank you sir.
After having a further conversation, it seems they have tried the alter
already and it ran for several hours before timing out ( a very large
table).
This implies to me that it is doing a table rebuild, even though all data in
that column is already NOT NULL and only the DDL needs to be changed.
Am I probably right here? Can we make this type of change without it taking
more than a few seconds to do the alter?
"Tom Moreau" wrote:
> You drop statistics in the same way that you drop indexes. Typically, you
> don't have both statistics and indexes on the same column - except, say,
> if
> you have an index on a single column and statistics on a pair of columns,
> and vice-versa. In your case, it looks like the Database Tuning Advisor
> recommended the stats and the DBA running the DTA allowed them to be
> built.
> You'll have to run:
> DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
> SQL Server 2005
> When I run the command:
> alter table FileRepository alter column parentID int not null
> I get the message:
> Msg 5074, Level 16, State 1, Line 1
> The index 'IX_FileRepository' is dependent on column 'parentID'.
> Msg 5074, Level 16, State 1, Line 1
> The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
> 'parentID'.
> Msg 4922, Level 16, State 9, Line 1
> ALTER TABLE ALTER COLUMN parentID failed because one or more objects
> access
> this column.
>
> No problem on the index as that is easy to drop and recreate. But what is
> this stuff about statistics and how do I deal with it?
> FWIW, I know just enough about 2000 to do my job, but diddle about 2005.
>
--=_NextPart_000_0A6F_01C77871.851AEB80
Content-Type: text/plain;
name="Alter.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="Alter.sql"
-- Listing 1
create table dbo.t1
(
id int primary key nonclustered
, dt datetime not null
, junk varchar (20) not null
)
create clustered index C_t1 on dbo.t1 (dt)
create table dbo.t2
(
id int primary key nonclustered
, dt datetime not null
, junk varchar (20) not null
, other1 bit not null
default (0)
, other2 varchar (30) null
)
create clustered index C_t2 on dbo.t2 (dt)
create view dbo.t
as
select
id
, dt
, junk
from
dbo.t2
union all
select
id
, dt
, junk
from
dbo.t1
go
-- Listing 2
create table dbo.threshold
(
threshold datetime not null
)
insert dbo.threshold values ('1995-01-01')
go
-- Listing 3
create trigger tri_t on dbo.t instead of insert
as
insert dbo.t1 (id, dt, junk)
select id, dt, junk
from inserted
where dt >= (select threshold from dbo.threshold)
insert dbo.t2 (id, dt, junk)
select id, dt, junk
from inserted
where dt < (select threshold from dbo.threshold)
go
-- Listing 4
create trigger tru_t on dbo.t instead of update
as
if (update (dt))
begin
-- no crossing of threshold
update x
set
dt = i.dt
, junk = i.junk
from
inserted i
join deleted d on d.id = i.id
join dbo.t2 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt < z.threshold
update x
set
dt = i.dt
, junk = i.junk
from
inserted i
join deleted d on d.id = i.id
join dbo.t1 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt >= z.threshold
-- threshold crossed
delete x
from
inserted i
join deleted d on d.id = i.id
join dbo.t2 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt < z.threshold
delete x
from
inserted i
join deleted d on d.id = i.id
join dbo.t1 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt >= z.threshold
insert dbo.t1 (id, dt, junk)
select i.id, i.dt, i.junk
from
inserted i
join deleted d on d.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt < z.threshold
insert dbo.t2 (id, dt, junk)
select i.id, i.dt, i.junk
from
inserted i
join deleted d on d.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt >= z.threshold
end
else
begin
update x
set
junk = i.junk
from
inserted i
join dbo.t1 x on x.id = i.id
where
i.dt >= (select threshold from dbo.threshold)
update x
set
junk = i.junk
from
inserted i
join dbo.t2 x on x.id = i.id
where
i.dt < (select threshold from dbo.threshold)
end
go
-- Listing 5
create trigger tru_t on dbo.t instead of delete
as
if @.@.ROWCOUNT = 0
return
delete x
from
deleted d
join dbo.t1 x on x.id = d.id
delete x
from
deleted d
join dbo.t2 x on x.id = d.id
go
-- populate the table
declare
@.year smallint
, @.month tinyint
, @.day tinyint
, @.id int
, @.date datetime
, @.icount int
, @.max int
select
@.year = 1995
, @.id = 1
while @.year < 2004
begin
set @.month = 1
while @.month <= 12
begin
set @.day = 1
while @.day <= case when @.month in (1, 3, 5, 7, 8, 10, 12) then 31
when @.month in (4, 6, 9, 11) then 30
when @.year % 4 = 0 then 29
else 28 end
begin
set @.date = cast (@.year as char (4))
+ replace (str (@.month, 2), ' ', '0')
+ replace (str (@.day, 2), ' ', '0')
select
@.max = rand () * 1000
, @.icount = 1
while @.icount <= @.max
begin
insert t1 (id, dt, junk)
values (@.id, @.date, replicate ('X', 20))
select
@.icount = @.icount + 1
, @.id = @.id + 1
end
set @.day = @.day + 1
end
set @.month = @.month + 1
end
set @.year = @.year + 1
end
go
-- Listing 6
-- migrate the data
declare
@.date datetime
, @.str varchar (8000)
select
@.date = min (dt)
from
t1
while @.date is not null and not datepart (hh, getdate()) between 8 and 18
begin
begin tran
update threshold
set
threshold = dateadd (dd, 1, @.date)
insert t2 (id, dt, junk)
select id, dt, junk
from t1
where dt = @.date
delete t1
where dt = @.date
commit tran
select
@.date = min (dt)
from
t1
end
/*
drop view t
drop table t1
drop table t2
drop table threshold
*/
--=_NextPart_000_0A6F_01C77871.851AEB80--|||> After having a further conversation, it seems they have tried the alter
> already and it ran for several hours before timing out ( a very large
> table).
> This implies to me that it is doing a table rebuild, even though all data
> in
> that column is already NOT NULL and only the DDL needs to be changed.
> Am I probably right here?
That depends, did they open Enterprise Manager and click around in the GUI
to make the change? By default, in most cases, when you click OK Enterprise
Manager will do exactly that behind your back: create a new table, move the
data over, drop the old table, rename the new table to the old name, etc.
No exactly pretty.
You should probably wait to attempt this during a maintenance window (e.g.
on a Sunday at midnight), and even enforce it with setting the database to
SINGLE_USER, and then you won't have to worry about blocking users, or being
blocked by users, or being slowed down by other activity in the database.
If it still takes too long, you may have to use Tom's solution, which is a
bit more complex but is certainly better than what Enterprise Manager tries
to do.|||> You should probably wait to attempt this
And by "this" I mean an ALTER statement in Query Analyzer, not clicking OK
to the change in Enterprise Manager.|||Yes, they tried to do it through the GUI.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > After having a further conversation, it seems they have tried the alter
> > already and it ran for several hours before timing out ( a very large
> > table).
> > This implies to me that it is doing a table rebuild, even though all data
> > in
> > that column is already NOT NULL and only the DDL needs to be changed.
> >
> > Am I probably right here?
> That depends, did they open Enterprise Manager and click around in the GUI
> to make the change? By default, in most cases, when you click OK Enterprise
> Manager will do exactly that behind your back: create a new table, move the
> data over, drop the old table, rename the new table to the old name, etc.
> No exactly pretty.
> You should probably wait to attempt this during a maintenance window (e.g.
> on a Sunday at midnight), and even enforce it with setting the database to
> SINGLE_USER, and then you won't have to worry about blocking users, or being
> blocked by users, or being slowed down by other activity in the database.
> If it still takes too long, you may have to use Tom's solution, which is a
> bit more complex but is certainly better than what Enterprise Manager tries
> to do.
>
>|||At present, I'm on a dev server (our only 2005 box).
As it happens, the ALTER statement does see that it's a simple DDL change
and took a couple of seconds.
I'm releived that SQL Server is smart enough to do that.
Thank you very much.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > After having a further conversation, it seems they have tried the alter
> > already and it ran for several hours before timing out ( a very large
> > table).
> > This implies to me that it is doing a table rebuild, even though all data
> > in
> > that column is already NOT NULL and only the DDL needs to be changed.
> >
> > Am I probably right here?
> That depends, did they open Enterprise Manager and click around in the GUI
> to make the change? By default, in most cases, when you click OK Enterprise
> Manager will do exactly that behind your back: create a new table, move the
> data over, drop the old table, rename the new table to the old name, etc.
> No exactly pretty.
> You should probably wait to attempt this during a maintenance window (e.g.
> on a Sunday at midnight), and even enforce it with setting the database to
> SINGLE_USER, and then you won't have to worry about blocking users, or being
> blocked by users, or being slowed down by other activity in the database.
> If it still takes too long, you may have to use Tom's solution, which is a
> bit more complex but is certainly better than what Enterprise Manager tries
> to do.
>
>
Subscribe to:
Posts (Atom)