Can someone help with the following
I would like to delete all the data from a column so I can then update it
with new data.
Thankshow do you mean delete from a column? set it to null? In which case you
want UPDATE tblA SET colA = null.
You can only really delete a row, not a column, that's just updating a
value.|||looks painfully simple.. but still... :)
update table1 set column1 = null
--
"Chris" wrote:
> Can someone help with the following
> I would like to delete all the data from a column so I can then update it
> with new data.
> Thanks|||Thanks Guys
I am really new to SQL (Tax Accountant Not A Programer)
"Omnibuzz" wrote:
> looks painfully simple.. but still... :)
> update table1 set column1 = null
> --
>
>
> "Chris" wrote:
>|||no problem.. have a great day.
--
"Chris" wrote:
> Thanks Guys
> I am really new to SQL (Tax Accountant Not A Programer)
> "Omnibuzz" wrote:
>
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Tuesday, March 27, 2012
Delete Column Problems
I created a report with say 5 columns, then decide I only want 4 of them.
My approach was:
1) go to data area and remove from query
2) go to Layout Tab and remove from display
But when I preview it - I get error - it still seems to always be looking
for the column I deleted - what can I do?make sure there are no referneces to that column or field thru any filters
or the where statement in the data area.
"Joe" <hortoristic@.gmail dot com> wrote in message
news:eAINil6yGHA.4648@.TK2MSFTNGP04.phx.gbl...
>I created a report with say 5 columns, then decide I only want 4 of them.
> My approach was:
> 1) go to data area and remove from query
> 2) go to Layout Tab and remove from display
> But when I preview it - I get error - it still seems to always be looking
> for the column I deleted - what can I do?
>|||Go to DataSet,Go to Fields,remove the unwanted column.it should work
fine now
Regards
Raj Deep.A
Ben Watts wrote:
> make sure there are no referneces to that column or field thru any filters
> or the where statement in the data area.
> "Joe" <hortoristic@.gmail dot com> wrote in message
> news:eAINil6yGHA.4648@.TK2MSFTNGP04.phx.gbl...
> >I created a report with say 5 columns, then decide I only want 4 of them.
> >
> > My approach was:
> > 1) go to data area and remove from query
> > 2) go to Layout Tab and remove from display
> >
> > But when I preview it - I get error - it still seems to always be looking
> > for the column I deleted - what can I do?
> >sql
My approach was:
1) go to data area and remove from query
2) go to Layout Tab and remove from display
But when I preview it - I get error - it still seems to always be looking
for the column I deleted - what can I do?make sure there are no referneces to that column or field thru any filters
or the where statement in the data area.
"Joe" <hortoristic@.gmail dot com> wrote in message
news:eAINil6yGHA.4648@.TK2MSFTNGP04.phx.gbl...
>I created a report with say 5 columns, then decide I only want 4 of them.
> My approach was:
> 1) go to data area and remove from query
> 2) go to Layout Tab and remove from display
> But when I preview it - I get error - it still seems to always be looking
> for the column I deleted - what can I do?
>|||Go to DataSet,Go to Fields,remove the unwanted column.it should work
fine now
Regards
Raj Deep.A
Ben Watts wrote:
> make sure there are no referneces to that column or field thru any filters
> or the where statement in the data area.
> "Joe" <hortoristic@.gmail dot com> wrote in message
> news:eAINil6yGHA.4648@.TK2MSFTNGP04.phx.gbl...
> >I created a report with say 5 columns, then decide I only want 4 of them.
> >
> > My approach was:
> > 1) go to data area and remove from query
> > 2) go to Layout Tab and remove from display
> >
> > But when I preview it - I get error - it still seems to always be looking
> > for the column I deleted - what can I do?
> >sql
Delete Column
I want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is store in the table below. If the UdateMonth is not null then I want to delete.
hi,can u please tell me, do u want to delete a column or a row? if u really want to delete a row whose FinancilaPeriod Month value is ThisYearMonthEnd10. u can use the following statement
delete from tblMarketing where FinancilaPeriodMonth = 'ThisYearMonthEnd10'
I really want to delete a column based on the following example:
Table 1
FinancilaPeriodMonthUdateMonth
ThisYearMonthEnd01Jan
ThisYearMonthEnd02Feb
ThisYearMonthEnd03Mar
ThisYearMonthEnd04Apr
ThisYearMonthEnd05May
ThisYearMonthEnd06Jun
ThisYearMonthEnd07Jul
ThisYearMonthEnd08Aug
ThisYearMonthEnd09Sep
ThisYearMonthEnd10OctOct
ThisYearMonthEnd11Nov
ThisYearMonthEnd12Dec
Actually two of threads were futher explanation of the problem.
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is store in the table below. If the UdateMonth is not null then I want to delete.
FinancilaPeriodMonthUdateMonth
ThisYearMonthEnd01Jan
ThisYearMonthEnd02Feb
ThisYearMonthEnd03Mar
ThisYearMonthEnd04Apr
ThisYearMonthEnd05May
ThisYearMonthEnd06Jun
ThisYearMonthEnd07Jul
ThisYearMonthEnd08Aug
ThisYearMonthEnd09Sep
ThisYearMonthEnd10OctOct
ThisYearMonthEnd11Nov
ThisYearMonthEnd12Dec
Can anyone help.
ThanksI want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.
FinancilaPeriod Month UdateMonth
ThisYearMonthEnd01 Jan
ThisYearMonthEnd02 Feb
ThisYearMonthEnd03 Mar
ThisYearMonthEnd04 Apr
ThisYearMonthEnd05 May
ThisYearMonthEnd06 Jun
ThisYearMonthEnd07 Jul
ThisYearMonthEnd08 Aug
ThisYearMonthEnd09 Sep
ThisYearMonthEnd10 Oct Oct
ThisYearMonthEnd11 Nov
ThisYearMonthEnd12 Dec
Can anyone help.
Thanks|||------------------------
I want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.
FinancilaPeriod Month UdateMonth
ThisYearMonthEnd01 Jan
ThisYearMonthEnd02 Feb
ThisYearMonthEnd03 Mar
ThisYearMonthEnd04 Apr
ThisYearMonthEnd05 May
ThisYearMonthEnd06 Jun
ThisYearMonthEnd07 Jul
ThisYearMonthEnd08 Aug
ThisYearMonthEnd09 Sep
ThisYearMonthEnd10 Oct Oct
ThisYearMonthEnd11 Nov
ThisYearMonthEnd12 Dec
Can anyone help.
Thanks|||------------------------
I want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.
FinancilaPeriod Month UdateMonth
ThisYearMonthEnd01 Jan
ThisYearMonthEnd02 Feb
ThisYearMonthEnd03 Mar
ThisYearMonthEnd04 Apr
ThisYearMonthEnd05 May
ThisYearMonthEnd06 Jun
ThisYearMonthEnd07 Jul
ThisYearMonthEnd08 Aug
ThisYearMonthEnd09 Sep
ThisYearMonthEnd10 Oct Oct
ThisYearMonthEnd11 Nov
ThisYearMonthEnd12 Dec
Can anyone help.
Thanks|||A table is nothing more then a collection of columns. If you want to remove a column you do it for the entire table. You cannot just drop a column for one specific row when it contains some data. When you drop a column, it removes that entire column from the table, data or not.
Ronald :cool:.|||
Quote:
Originally Posted by shieldsco
------------------------
I want to delete a colum in a table using the following sql statement:
ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEnd10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.
FinancilaPeriod Month UdateMonth
ThisYearMonthEnd01 Jan
ThisYearMonthEnd02 Feb
ThisYearMonthEnd03 Mar
ThisYearMonthEnd04 Apr
ThisYearMonthEnd05 May
ThisYearMonthEnd06 Jun
ThisYearMonthEnd07 Jul
ThisYearMonthEnd08 Aug
ThisYearMonthEnd09 Sep
ThisYearMonthEnd10 Oct Oct
ThisYearMonthEnd11 Nov
ThisYearMonthEnd12 Dec
Can anyone help.
Thanks
hi,can u please tell me, do u want to delete a column or a row? if u really want to delete a row whose FinancilaPeriod Month value is ThisYearMonthEnd10. u can use the following statement
delete from tblMarketing where FinancilaPeriodMonth = 'ThisYearMonthEnd10'
I hope i understood ur problem correctly.|||
Quote:
Originally Posted by Anu139
hi,can u please tell me, do u want to delete a column or a row? if u really want to delete a row whose FinancilaPeriod Month value is ThisYearMonthEnd10. u can use the following statement
delete from tblMarketing where FinancilaPeriodMonth = 'ThisYearMonthEnd10'
I hope i understood ur problem correctly.
I really want to delete a column based on the following example:
Table 1
FinancilaPeriodMonthUdateMonth
ThisYearMonthEnd01Jan
ThisYearMonthEnd02Feb
ThisYearMonthEnd03Mar
ThisYearMonthEnd04Apr
ThisYearMonthEnd05May
ThisYearMonthEnd06Jun
ThisYearMonthEnd07Jul
ThisYearMonthEnd08Aug
ThisYearMonthEnd09Sep
ThisYearMonthEnd10OctOct
ThisYearMonthEnd11Nov
ThisYearMonthEnd12Dec
Table 2
ThisYearMonthEnd01ThisYearMonthEnd02ThisYearMonthEnd10
I want to delete the cloumn ThisYearMonthEnd10 in Table 2 based on the the UdateMonth in table 1.|||Hi shieldsco,
I hope you are getting the help you want. However I would like to request that in future you don't double (or in this case quadruple) post the same question to the same forum.
If you think you question/problem has been overlooked then please post a reply to it yourself so that it gets bumped to the top of the Forum list.
I have now merged your 4 threads on this subject into this single thread.
Regards
Banfa|||
Quote:
Originally Posted by Banfa
Hi shieldsco,
I hope you are getting the help you want. However I would like to request that in future you don't double (or in this case quadruple) post the same question to the same forum.
If you think you question/problem has been overlooked then please post a reply to it yourself so that it gets bumped to the top of the Forum list.
I have now merged your 4 threads on this subject into this single thread.
Regards
Banfa
Actually two of threads were futher explanation of the problem.
delete BLOB objects
When I delete rows from a table that contains an NTEXT column, I get a delet
e
capacity of 100 rows per second. I think this is slow. How can I make it
delete faster?
The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246 byte
s
When I populate the same table with BULK INSERT I get performance of
2000-3000 rows per second. Shouldn’t deletion of rows reach about the same
performance as population of the same data?
I have a primary key, id, which I use to select my rows for deletion like
this:
DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
optimal with a single “clustered index delete”.
I have removed all constraints and all indexes on the table to isolate the
problem as much as possible. I'm running in simple recovery mode.
The funny thing is that if I do the following exercise the delete
performance is about 1000-2000 rows per seconds:
Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
id > 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
.. The update, however, does about 50 rows per second.
If I do this:
Step 1: UPDATE mytable SET ntextcolumn = N’-1’ WHERE id < 10000 and id
> 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
…then delete capacity is ca 15.000 rows per second. The update, however,
does about 33 rows per second.
Is the above behavior normal? Does it really take that much work for SQL
Server to remove the blob-object?When you do the bulk Insert you are most likely getting a minimally logged
load which does not log the actual data in the transaction log. It only
marks which extents have been altered in the bulk load. But when you delete
or Update the row it has to log the text data in the transaction log. That
is a lot of data to log all at once. The delete after the update is faster
for two reasons. One the data is already all in cache and you have no text
data to log. Where is your log file located? If it is not on a RAID 1 or
Raid 10 by itself you should think about moving it.
Andrew J. Kelly SQL MVP
"HenrikF" <HenrikF@.discussions.microsoft.com> wrote in message
news:B2A7FD8D-B356-4EBA-B201-B761C0673EF2@.microsoft.com...
> When I delete rows from a table that contains an NTEXT column, I get a
> delete
> capacity of 100 rows per second. I think this is slow. How can I make it
> delete faster?
> The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246
> bytes
> When I populate the same table with BULK INSERT I get performance of
> 2000-3000 rows per second. Shouldn't deletion of rows reach about the same
> performance as population of the same data?
> I have a primary key, id, which I use to select my rows for deletion like
> this:
> DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
> optimal with a single "clustered index delete".
>
> I have removed all constraints and all indexes on the table to isolate the
> problem as much as possible. I'm running in simple recovery mode.
> The funny thing is that if I do the following exercise the delete
> performance is about 1000-2000 rows per seconds:
>
> Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
> id > 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .. The update, however, does about 50 rows per second.
>
> If I do this:
> Step 1: UPDATE mytable SET ntextcolumn = N'-1' WHERE id < 10000 and id >
> 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .then delete capacity is ca 15.000 rows per second. The update, however,
> does about 33 rows per second.
>
> Is the above behavior normal? Does it really take that much work for SQL
> Server to remove the blob-object?
>sql
e
capacity of 100 rows per second. I think this is slow. How can I make it
delete faster?
The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246 byte
s
When I populate the same table with BULK INSERT I get performance of
2000-3000 rows per second. Shouldn’t deletion of rows reach about the same
performance as population of the same data?
I have a primary key, id, which I use to select my rows for deletion like
this:
DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
optimal with a single “clustered index delete”.
I have removed all constraints and all indexes on the table to isolate the
problem as much as possible. I'm running in simple recovery mode.
The funny thing is that if I do the following exercise the delete
performance is about 1000-2000 rows per seconds:
Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
id > 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
.. The update, however, does about 50 rows per second.
If I do this:
Step 1: UPDATE mytable SET ntextcolumn = N’-1’ WHERE id < 10000 and id
> 0
Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
…then delete capacity is ca 15.000 rows per second. The update, however,
does about 33 rows per second.
Is the above behavior normal? Does it really take that much work for SQL
Server to remove the blob-object?When you do the bulk Insert you are most likely getting a minimally logged
load which does not log the actual data in the transaction log. It only
marks which extents have been altered in the bulk load. But when you delete
or Update the row it has to log the text data in the transaction log. That
is a lot of data to log all at once. The delete after the update is faster
for two reasons. One the data is already all in cache and you have no text
data to log. Where is your log file located? If it is not on a RAID 1 or
Raid 10 by itself you should think about moving it.
Andrew J. Kelly SQL MVP
"HenrikF" <HenrikF@.discussions.microsoft.com> wrote in message
news:B2A7FD8D-B356-4EBA-B201-B761C0673EF2@.microsoft.com...
> When I delete rows from a table that contains an NTEXT column, I get a
> delete
> capacity of 100 rows per second. I think this is slow. How can I make it
> delete faster?
> The average size of my ntextcolumn is 22.000 bytes. Max size is 132.246
> bytes
> When I populate the same table with BULK INSERT I get performance of
> 2000-3000 rows per second. Shouldn't deletion of rows reach about the same
> performance as population of the same data?
> I have a primary key, id, which I use to select my rows for deletion like
> this:
> DELETE FROM mytable WHERE id < 10000 and id > 0. The execution plan is
> optimal with a single "clustered index delete".
>
> I have removed all constraints and all indexes on the table to isolate the
> problem as much as possible. I'm running in simple recovery mode.
> The funny thing is that if I do the following exercise the delete
> performance is about 1000-2000 rows per seconds:
>
> Step 1: UPDATE mytable SET ntextcolumn = ntextcolumn WHERE id < 10000 and
> id > 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .. The update, however, does about 50 rows per second.
>
> If I do this:
> Step 1: UPDATE mytable SET ntextcolumn = N'-1' WHERE id < 10000 and id >
> 0
> Step 2: DELETE FROM mytable WHERE id < 10000 and id > 0
> .then delete capacity is ca 15.000 rows per second. The update, however,
> does about 33 rows per second.
>
> Is the above behavior normal? Does it really take that much work for SQL
> Server to remove the blob-object?
>sql
Delete blank column
hi! can anybody please help me..i have a table employee and it has 4 column namely id,lastname,firstname,salary and i want to delete all rows where firstname is equal to blank or ' ' NULL? thanks a bunch!DELETE FROM employee
WHERE firstname IS NULL
OR firstname = ' ';|||thanks so much!!|||i call homework
WHERE firstname IS NULL
OR firstname = ' ';|||thanks so much!!|||i call homework
delete all statistics in db
Hi,
anybody have a script or an idea on how to delete all stats in a db?
while on the topic why do some stats (column I think) prevent column from
being upgraded via alter table statement? I would have expected stats to be
dropped as soon as column modified.
Thanks.
Try the following post from Dan Guzman:
http://groups.google.com/group/micro...5ec3c61?hl=en&
The requirement is listed in books online under the alter
table topic in the section alter column - the limitation is
from statistics generated by an explicit create statistics
statement. I think you also can hit issues if you have
hypothetical index left behind from the Index tuning wizard.
From books online:
The altered column cannot be:
Used in statistics generated by the CREATE STATISTICS
statement. First remove the statistics using the DROP
STATISTICS statement. Statistics automatically generated by
the query optimizer are automatically dropped by ALTER
COLUMN.
-Sue
On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
<sysbox27@.discussions.microsoft.com> wrote:
>Hi,
>anybody have a script or an idea on how to delete all stats in a db?
>while on the topic why do some stats (column I think) prevent column from
>being upgraded via alter table statement? I would have expected stats to be
>dropped as soon as column modified.
>Thanks.
|||Thanks very much Sue.
"Sue Hoegemeier" wrote:
> Try the following post from Dan Guzman:
> http://groups.google.com/group/micro...5ec3c61?hl=en&
> The requirement is listed in books online under the alter
> table topic in the section alter column - the limitation is
> from statistics generated by an explicit create statistics
> statement. I think you also can hit issues if you have
> hypothetical index left behind from the Index tuning wizard.
> From books online:
> The altered column cannot be:
> Used in statistics generated by the CREATE STATISTICS
> statement. First remove the statistics using the DROP
> STATISTICS statement. Statistics automatically generated by
> the query optimizer are automatically dropped by ALTER
> COLUMN.
> -Sue
> On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
> <sysbox27@.discussions.microsoft.com> wrote:
>
>
anybody have a script or an idea on how to delete all stats in a db?
while on the topic why do some stats (column I think) prevent column from
being upgraded via alter table statement? I would have expected stats to be
dropped as soon as column modified.
Thanks.
Try the following post from Dan Guzman:
http://groups.google.com/group/micro...5ec3c61?hl=en&
The requirement is listed in books online under the alter
table topic in the section alter column - the limitation is
from statistics generated by an explicit create statistics
statement. I think you also can hit issues if you have
hypothetical index left behind from the Index tuning wizard.
From books online:
The altered column cannot be:
Used in statistics generated by the CREATE STATISTICS
statement. First remove the statistics using the DROP
STATISTICS statement. Statistics automatically generated by
the query optimizer are automatically dropped by ALTER
COLUMN.
-Sue
On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
<sysbox27@.discussions.microsoft.com> wrote:
>Hi,
>anybody have a script or an idea on how to delete all stats in a db?
>while on the topic why do some stats (column I think) prevent column from
>being upgraded via alter table statement? I would have expected stats to be
>dropped as soon as column modified.
>Thanks.
|||Thanks very much Sue.
"Sue Hoegemeier" wrote:
> Try the following post from Dan Guzman:
> http://groups.google.com/group/micro...5ec3c61?hl=en&
> The requirement is listed in books online under the alter
> table topic in the section alter column - the limitation is
> from statistics generated by an explicit create statistics
> statement. I think you also can hit issues if you have
> hypothetical index left behind from the Index tuning wizard.
> From books online:
> The altered column cannot be:
> Used in statistics generated by the CREATE STATISTICS
> statement. First remove the statistics using the DROP
> STATISTICS statement. Statistics automatically generated by
> the query optimizer are automatically dropped by ALTER
> COLUMN.
> -Sue
> On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
> <sysbox27@.discussions.microsoft.com> wrote:
>
>
delete all statistics in db
Hi,
anybody have a script or an idea on how to delete all stats in a db?
while on the topic why do some stats (column I think) prevent column from
being upgraded via alter table statement? I would have expected stats to be
dropped as soon as column modified.
Thanks.Try the following post from Dan Guzman:
http://groups.google.com/group/microsoft.public.sqlserver.server/msg/8dd9fd47a5ec3c61?hl=en&
The requirement is listed in books online under the alter
table topic in the section alter column - the limitation is
from statistics generated by an explicit create statistics
statement. I think you also can hit issues if you have
hypothetical index left behind from the Index tuning wizard.
From books online:
The altered column cannot be:
Used in statistics generated by the CREATE STATISTICS
statement. First remove the statistics using the DROP
STATISTICS statement. Statistics automatically generated by
the query optimizer are automatically dropped by ALTER
COLUMN.
-Sue
On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
<sysbox27@.discussions.microsoft.com> wrote:
>Hi,
>anybody have a script or an idea on how to delete all stats in a db?
>while on the topic why do some stats (column I think) prevent column from
>being upgraded via alter table statement? I would have expected stats to be
>dropped as soon as column modified.
>Thanks.|||Thanks very much Sue.
"Sue Hoegemeier" wrote:
> Try the following post from Dan Guzman:
> http://groups.google.com/group/microsoft.public.sqlserver.server/msg/8dd9fd47a5ec3c61?hl=en&
> The requirement is listed in books online under the alter
> table topic in the section alter column - the limitation is
> from statistics generated by an explicit create statistics
> statement. I think you also can hit issues if you have
> hypothetical index left behind from the Index tuning wizard.
> From books online:
> The altered column cannot be:
> Used in statistics generated by the CREATE STATISTICS
> statement. First remove the statistics using the DROP
> STATISTICS statement. Statistics automatically generated by
> the query optimizer are automatically dropped by ALTER
> COLUMN.
> -Sue
> On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
> <sysbox27@.discussions.microsoft.com> wrote:
> >Hi,
> >anybody have a script or an idea on how to delete all stats in a db?
> >while on the topic why do some stats (column I think) prevent column from
> >being upgraded via alter table statement? I would have expected stats to be
> >dropped as soon as column modified.
> >Thanks.
>
anybody have a script or an idea on how to delete all stats in a db?
while on the topic why do some stats (column I think) prevent column from
being upgraded via alter table statement? I would have expected stats to be
dropped as soon as column modified.
Thanks.Try the following post from Dan Guzman:
http://groups.google.com/group/microsoft.public.sqlserver.server/msg/8dd9fd47a5ec3c61?hl=en&
The requirement is listed in books online under the alter
table topic in the section alter column - the limitation is
from statistics generated by an explicit create statistics
statement. I think you also can hit issues if you have
hypothetical index left behind from the Index tuning wizard.
From books online:
The altered column cannot be:
Used in statistics generated by the CREATE STATISTICS
statement. First remove the statistics using the DROP
STATISTICS statement. Statistics automatically generated by
the query optimizer are automatically dropped by ALTER
COLUMN.
-Sue
On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
<sysbox27@.discussions.microsoft.com> wrote:
>Hi,
>anybody have a script or an idea on how to delete all stats in a db?
>while on the topic why do some stats (column I think) prevent column from
>being upgraded via alter table statement? I would have expected stats to be
>dropped as soon as column modified.
>Thanks.|||Thanks very much Sue.
"Sue Hoegemeier" wrote:
> Try the following post from Dan Guzman:
> http://groups.google.com/group/microsoft.public.sqlserver.server/msg/8dd9fd47a5ec3c61?hl=en&
> The requirement is listed in books online under the alter
> table topic in the section alter column - the limitation is
> from statistics generated by an explicit create statistics
> statement. I think you also can hit issues if you have
> hypothetical index left behind from the Index tuning wizard.
> From books online:
> The altered column cannot be:
> Used in statistics generated by the CREATE STATISTICS
> statement. First remove the statistics using the DROP
> STATISTICS statement. Statistics automatically generated by
> the query optimizer are automatically dropped by ALTER
> COLUMN.
> -Sue
> On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
> <sysbox27@.discussions.microsoft.com> wrote:
> >Hi,
> >anybody have a script or an idea on how to delete all stats in a db?
> >while on the topic why do some stats (column I think) prevent column from
> >being upgraded via alter table statement? I would have expected stats to be
> >dropped as soon as column modified.
> >Thanks.
>
delete all statistics in db
Hi,
anybody have a script or an idea on how to delete all stats in a db?
while on the topic why do some stats (column I think) prevent column from
being upgraded via alter table statement? I would have expected stats to be
dropped as soon as column modified.
Thanks.Try the following post from Dan Guzman:
http://groups.google.com/group/micr... />
c61?hl=en&
The requirement is listed in books online under the alter
table topic in the section alter column - the limitation is
from statistics generated by an explicit create statistics
statement. I think you also can hit issues if you have
hypothetical index left behind from the Index tuning wizard.
From books online:
The altered column cannot be:
Used in statistics generated by the CREATE STATISTICS
statement. First remove the statistics using the DROP
STATISTICS statement. Statistics automatically generated by
the query optimizer are automatically dropped by ALTER
COLUMN.
-Sue
On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
<sysbox27@.discussions.microsoft.com> wrote:
>Hi,
>anybody have a script or an idea on how to delete all stats in a db?
>while on the topic why do some stats (column I think) prevent column from
>being upgraded via alter table statement? I would have expected stats to be
>dropped as soon as column modified.
>Thanks.|||Thanks very much Sue.
"Sue Hoegemeier" wrote:
> Try the following post from Dan Guzman:
> http://groups.google.com/group/micr...>
c3c61?hl=en&
> The requirement is listed in books online under the alter
> table topic in the section alter column - the limitation is
> from statistics generated by an explicit create statistics
> statement. I think you also can hit issues if you have
> hypothetical index left behind from the Index tuning wizard.
> From books online:
> The altered column cannot be:
> Used in statistics generated by the CREATE STATISTICS
> statement. First remove the statistics using the DROP
> STATISTICS statement. Statistics automatically generated by
> the query optimizer are automatically dropped by ALTER
> COLUMN.
> -Sue
> On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
> <sysbox27@.discussions.microsoft.com> wrote:
>
>
anybody have a script or an idea on how to delete all stats in a db?
while on the topic why do some stats (column I think) prevent column from
being upgraded via alter table statement? I would have expected stats to be
dropped as soon as column modified.
Thanks.Try the following post from Dan Guzman:
http://groups.google.com/group/micr... />
c61?hl=en&
The requirement is listed in books online under the alter
table topic in the section alter column - the limitation is
from statistics generated by an explicit create statistics
statement. I think you also can hit issues if you have
hypothetical index left behind from the Index tuning wizard.
From books online:
The altered column cannot be:
Used in statistics generated by the CREATE STATISTICS
statement. First remove the statistics using the DROP
STATISTICS statement. Statistics automatically generated by
the query optimizer are automatically dropped by ALTER
COLUMN.
-Sue
On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
<sysbox27@.discussions.microsoft.com> wrote:
>Hi,
>anybody have a script or an idea on how to delete all stats in a db?
>while on the topic why do some stats (column I think) prevent column from
>being upgraded via alter table statement? I would have expected stats to be
>dropped as soon as column modified.
>Thanks.|||Thanks very much Sue.
"Sue Hoegemeier" wrote:
> Try the following post from Dan Guzman:
> http://groups.google.com/group/micr...>
c3c61?hl=en&
> The requirement is listed in books online under the alter
> table topic in the section alter column - the limitation is
> from statistics generated by an explicit create statistics
> statement. I think you also can hit issues if you have
> hypothetical index left behind from the Index tuning wizard.
> From books online:
> The altered column cannot be:
> Used in statistics generated by the CREATE STATISTICS
> statement. First remove the statistics using the DROP
> STATISTICS statement. Statistics automatically generated by
> the query optimizer are automatically dropped by ALTER
> COLUMN.
> -Sue
> On Tue, 13 Sep 2005 15:03:01 -0700, "sysbox27"
> <sysbox27@.discussions.microsoft.com> wrote:
>
>
Sunday, March 25, 2012
Delete all data from single column question
Does anyone know the correct syntax for using the DELETE command to completly
delete/remove all data from a single column within a given table only? I do
not want to save the data from column A. I would like to have the column
left in place with no data. Thanks for any info.
"brettr78" <brettr78@.discussions.microsoft.com> wrote in message
news:DDF0D6CB-CABF-49E8-A48A-DF6D0C4C62FE@.microsoft.com...
> Does anyone know the correct syntax for using the DELETE command to
completly
> delete/remove all data from a single column within a given table only? I
do
> not want to save the data from column A. I would like to have the column
> left in place with no data. Thanks for any info.
Depending on the datatype, something like:
UPDATE Foo
SET column_x = NULL
Rick
delete/remove all data from a single column within a given table only? I do
not want to save the data from column A. I would like to have the column
left in place with no data. Thanks for any info.
"brettr78" <brettr78@.discussions.microsoft.com> wrote in message
news:DDF0D6CB-CABF-49E8-A48A-DF6D0C4C62FE@.microsoft.com...
> Does anyone know the correct syntax for using the DELETE command to
completly
> delete/remove all data from a single column within a given table only? I
do
> not want to save the data from column A. I would like to have the column
> left in place with no data. Thanks for any info.
Depending on the datatype, something like:
UPDATE Foo
SET column_x = NULL
Rick
Delete all data from single column question
Does anyone know the correct syntax for using the DELETE command to completly
delete/remove all data from a single column within a given table only? I do
not want to save the data from column A. I would like to have the column
left in place with no data. Thanks for any info."brettr78" <brettr78@.discussions.microsoft.com> wrote in message
news:DDF0D6CB-CABF-49E8-A48A-DF6D0C4C62FE@.microsoft.com...
> Does anyone know the correct syntax for using the DELETE command to
completly
> delete/remove all data from a single column within a given table only? I
do
> not want to save the data from column A. I would like to have the column
> left in place with no data. Thanks for any info.
Depending on the datatype, something like:
UPDATE Foo
SET column_x = NULL
Rick
delete/remove all data from a single column within a given table only? I do
not want to save the data from column A. I would like to have the column
left in place with no data. Thanks for any info."brettr78" <brettr78@.discussions.microsoft.com> wrote in message
news:DDF0D6CB-CABF-49E8-A48A-DF6D0C4C62FE@.microsoft.com...
> Does anyone know the correct syntax for using the DELETE command to
completly
> delete/remove all data from a single column within a given table only? I
do
> not want to save the data from column A. I would like to have the column
> left in place with no data. Thanks for any info.
Depending on the datatype, something like:
UPDATE Foo
SET column_x = NULL
Rick
Delete all data from single column question
Does anyone know the correct syntax for using the DELETE command to completl
y
delete/remove all data from a single column within a given table only? I do
not want to save the data from column A. I would like to have the column
left in place with no data. Thanks for any info."brettr78" <brettr78@.discussions.microsoft.com> wrote in message
news:DDF0D6CB-CABF-49E8-A48A-DF6D0C4C62FE@.microsoft.com...
> Does anyone know the correct syntax for using the DELETE command to
completly
> delete/remove all data from a single column within a given table only? I
do
> not want to save the data from column A. I would like to have the column
> left in place with no data. Thanks for any info.
Depending on the datatype, something like:
UPDATE Foo
SET column_x = NULL
Rick
y
delete/remove all data from a single column within a given table only? I do
not want to save the data from column A. I would like to have the column
left in place with no data. Thanks for any info."brettr78" <brettr78@.discussions.microsoft.com> wrote in message
news:DDF0D6CB-CABF-49E8-A48A-DF6D0C4C62FE@.microsoft.com...
> Does anyone know the correct syntax for using the DELETE command to
completly
> delete/remove all data from a single column within a given table only? I
do
> not want to save the data from column A. I would like to have the column
> left in place with no data. Thanks for any info.
Depending on the datatype, something like:
UPDATE Foo
SET column_x = NULL
Rick
Thursday, March 22, 2012
delete 30 days from todays date?
I have a column that is to be updated to todays date - 30 days if it is older than that.
I tried this code but I get error for the conversion to datetime because it is out of range. Anyone have another solution?
update DATAFILE
set [Effective Date] = [Effective Date] - CAST('yyyy-mm-30' as datetime)
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30update DATAFILE
set [Effective Date] = [Effective Date] - 30
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30|||Ok, it was so simple?
I found this code worked also.
update DATAFILE
set [Effective Date] = DATEADD(dd, -30, GetDate())
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30
The date is supposed to be updated to todays date minus 30 days.
Its ok now. Thanks...|||Do not use addition or subtraction operations on dates. SQL Server dates or not so simple as dates in other Microsoft products where you can just add or subtract whole numbers. I have gotten spurious results this way in the past.
Use the DATEADD and DATEDIFF functions instead.
blindman
I tried this code but I get error for the conversion to datetime because it is out of range. Anyone have another solution?
update DATAFILE
set [Effective Date] = [Effective Date] - CAST('yyyy-mm-30' as datetime)
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30update DATAFILE
set [Effective Date] = [Effective Date] - 30
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30|||Ok, it was so simple?
I found this code worked also.
update DATAFILE
set [Effective Date] = DATEADD(dd, -30, GetDate())
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30
The date is supposed to be updated to todays date minus 30 days.
Its ok now. Thanks...|||Do not use addition or subtraction operations on dates. SQL Server dates or not so simple as dates in other Microsoft products where you can just add or subtract whole numbers. I have gotten spurious results this way in the past.
Use the DATEADD and DATEDIFF functions instead.
blindman
Wednesday, March 21, 2012
Delay for Conflict Resolution in SS2K5
I have a couple of tables set up with column level tracking and using the "Subscriber Always Wins" conflict resolver.
I am still trying to work out why there is a conflict as the only updates to these rows happen at the subscriber.
What I am finding is that a conflict occurs and there appears to be a delay between the conflict being recognised and then resolved.
The reason I know there is a delay is because this is playing havoc with a data extract that relies on the updates from the subscriber.
For instance, I have a job on which the status is changed. That status change triggers an extract via BizTalk to another system. BizTalk, via an SQL adapter extracts the data from the server and I find that some of it is missing in the extract.
I go and look into the database at the raw data and find that the data is in fact there.
I have certain triggers running which set up export flags in the database to say this data should be exported. This works perfectly when there are no conflicts.
In each case where data has not been extracted, a conflict occurs.
Is there any way to guarantee that the conflict will be resolved at the same time as the synchronisation is complete - i.e. within the same transaction?
I thought replication was supposed to be a single transaction - you get it all or you get nothing.
I'm interested to hear from anyone who has experienced this behaviour.
Thanks
Are you saying that when you run merge agent, there happens a conflict and that conflict is not resolved in the same merge session?
Please explain your scenario more clearly so that we can troubleshoot it.
Also if you could simplify and narrow down the failure, it will be helpful.
|||There is another post on here that details the conflicts we are getting. It drills into some of the detailshttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=540159&SiteID=1
Just to clarify, the subscribers we have are all from SQL Mobile.
When the status data gets synchronised, triggers operate on the update/insert to create a record to indicate that the data should be exported from the system. BizTalk is responsible for extracting this data and looks into SQL every ten seconds for data to extract.
I am getting, on occasion, data that is incomplete. Whenever this occurs, the incomplete data is always from one of the two tables I have that are getting conflicts (see other post for this particular problem). When I go back and look at the data in the database, I see what I would have expected the extract process to get.
This to me indicates that there is a delay in the conflict resolution. (Custom - subscriber always wins).
Let me know what you need if this is not enough.
Monday, March 19, 2012
Degenerate dimension vs synthetic lookup
I'm trying to get a handle on a modeling question of sorts. Basically I have a fact table with ~ 10 million rows. This table has a column called lot_id for each of several components (lets say componentn_lot_id to be generic). My componentn_lot_id has ~ 1.5 million distinct values. Theoretically speaking is it better to treat this attribute as a degenerate dimension of the fact table or would it be better to synthesize a lookup by creating a named query like:
select component1_lot_id from fact_table group by 1
and using this as a lookup table for a regular dimension? I have about 12 dimensions like this, so the simplicity of the degenerate dimension method is nice, but seems to have a pretty high price to pay when processing the cube (though the lookup method may also be costly). Are there other issues to keep in mind here?
I'm seeking advice as to rules of thumb (or techniques) that should be used when faced with this decision. I am currently using AS2005.
Thanks,
Keehan
Are you using ROLAP or MOLAP?
For MOLAP storage, very probably there's no difference regarding processing time.
You might want to take some measurements anyway, if only to make sure scalability and sizing requirements are met...
Hope this helps
Defragmentation of table
We are using database having a current size of over 300GB
to store attachment files in an image column of a table.
is the approach of exporting and importing data records
from the table a good way to defragment the table? if so,
is it possible to export the data records as a flat file
to a tape drive.
thanks.
There are commands in SQL Server to defrag and reorg. I strongly suggest you start by reading below
excellent whitepaper:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <anonymous@.discussions.microsoft.com> wrote in message
news:1dd4a01c454d4$11ae92f0$a301280a@.phx.gbl...
> We are using database having a current size of over 300GB
> to store attachment files in an image column of a table.
> is the approach of exporting and importing data records
> from the table a good way to defragment the table? if so,
> is it possible to export the data records as a flat file
> to a tape drive.
> thanks.
>
to store attachment files in an image column of a table.
is the approach of exporting and importing data records
from the table a good way to defragment the table? if so,
is it possible to export the data records as a flat file
to a tape drive.
thanks.
There are commands in SQL Server to defrag and reorg. I strongly suggest you start by reading below
excellent whitepaper:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <anonymous@.discussions.microsoft.com> wrote in message
news:1dd4a01c454d4$11ae92f0$a301280a@.phx.gbl...
> We are using database having a current size of over 300GB
> to store attachment files in an image column of a table.
> is the approach of exporting and importing data records
> from the table a good way to defragment the table? if so,
> is it possible to export the data records as a flat file
> to a tape drive.
> thanks.
>
Defragmentation of table
We are using database having a current size of over 300GB
to store attachment files in an image column of a table.
is the approach of exporting and importing data records
from the table a good way to defragment the table? if so,
is it possible to export the data records as a flat file
to a tape drive.
thanks.There are commands in SQL Server to defrag and reorg. I strongly suggest you
start by reading below
excellent whitepaper:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <anonymous@.discussions.microsoft.com> wrote in message
news:1dd4a01c454d4$11ae92f0$a301280a@.phx
.gbl...
> We are using database having a current size of over 300GB
> to store attachment files in an image column of a table.
> is the approach of exporting and importing data records
> from the table a good way to defragment the table? if so,
> is it possible to export the data records as a flat file
> to a tape drive.
> thanks.
>
to store attachment files in an image column of a table.
is the approach of exporting and importing data records
from the table a good way to defragment the table? if so,
is it possible to export the data records as a flat file
to a tape drive.
thanks.There are commands in SQL Server to defrag and reorg. I strongly suggest you
start by reading below
excellent whitepaper:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <anonymous@.discussions.microsoft.com> wrote in message
news:1dd4a01c454d4$11ae92f0$a301280a@.phx
.gbl...
> We are using database having a current size of over 300GB
> to store attachment files in an image column of a table.
> is the approach of exporting and importing data records
> from the table a good way to defragment the table? if so,
> is it possible to export the data records as a flat file
> to a tape drive.
> thanks.
>
Defragmentation of table
We are using database having a current size of over 300GB
to store attachment files in an image column of a table.
is the approach of exporting and importing data records
from the table a good way to defragment the table? if so,
is it possible to export the data records as a flat file
to a tape drive.
thanks.There are commands in SQL Server to defrag and reorg. I strongly suggest you start by reading below
excellent whitepaper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <anonymous@.discussions.microsoft.com> wrote in message
news:1dd4a01c454d4$11ae92f0$a301280a@.phx.gbl...
> We are using database having a current size of over 300GB
> to store attachment files in an image column of a table.
> is the approach of exporting and importing data records
> from the table a good way to defragment the table? if so,
> is it possible to export the data records as a flat file
> to a tape drive.
> thanks.
>
to store attachment files in an image column of a table.
is the approach of exporting and importing data records
from the table a good way to defragment the table? if so,
is it possible to export the data records as a flat file
to a tape drive.
thanks.There are commands in SQL Server to defrag and reorg. I strongly suggest you start by reading below
excellent whitepaper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <anonymous@.discussions.microsoft.com> wrote in message
news:1dd4a01c454d4$11ae92f0$a301280a@.phx.gbl...
> We are using database having a current size of over 300GB
> to store attachment files in an image column of a table.
> is the approach of exporting and importing data records
> from the table a good way to defragment the table? if so,
> is it possible to export the data records as a flat file
> to a tape drive.
> thanks.
>
Friday, March 9, 2012
Defining custom column groups on a matrix report in report builder
I have table called Buildings which has market and sq. ft information.
I want to create a matrix report that has the market as the row group and
sq. ft ranges as the column group and shows the # of buildings in each
market that fall in the sq. ft ranges.
Sq. ft ranges are 0-5000, 5001-10000 etc...
My questions is a) is this something that can be done using report builder
b) how do i define the sq. ft ranges
c) how do I find the count of buildings whose sq. footage falls within the
range.
ThanksOn Nov 14, 1:18 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> I have table called Buildings which has market and sq. ft information.
> I want to create a matrix report that has the market as the row group and
> sq. ft ranges as the column group and shows the # of buildings in each
> market that fall in the sq. ft ranges.
> Sq. ft ranges are 0-5000, 5001-10000 etc...
> My questions is a) is this something that can be done using report builder
> b) how do i define the sq. ft ranges
> c) how do I find the count of buildings whose sq. footage falls within the
> range.
> Thanks
A) Yes
B) In your Datasets window, right-click on the name of the dataset and
Add a new field. Call it "Sq Foot Range" and make it a calculated
field. Use an expression like
= Fields!SqFoot.Value - Fields!SqFoot.Value Mod 5000
Since you are shifting your range to include the evenly divided number
in the lower group, you really want
= CStr( ( (X-1) - (X-1) Mod 5000 ) + 1 ) & " to " & CStr( ( (X-1) -
(X-1) Mod 5000 ) + 5000 )
C) Create a Matrix with a Market in the Row Group, Sq Foot Range in
the Column Group, and Count( Fields!SqFoot.Value ) in the Details.
Hope that helps.
-- Scott
I want to create a matrix report that has the market as the row group and
sq. ft ranges as the column group and shows the # of buildings in each
market that fall in the sq. ft ranges.
Sq. ft ranges are 0-5000, 5001-10000 etc...
My questions is a) is this something that can be done using report builder
b) how do i define the sq. ft ranges
c) how do I find the count of buildings whose sq. footage falls within the
range.
ThanksOn Nov 14, 1:18 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> I have table called Buildings which has market and sq. ft information.
> I want to create a matrix report that has the market as the row group and
> sq. ft ranges as the column group and shows the # of buildings in each
> market that fall in the sq. ft ranges.
> Sq. ft ranges are 0-5000, 5001-10000 etc...
> My questions is a) is this something that can be done using report builder
> b) how do i define the sq. ft ranges
> c) how do I find the count of buildings whose sq. footage falls within the
> range.
> Thanks
A) Yes
B) In your Datasets window, right-click on the name of the dataset and
Add a new field. Call it "Sq Foot Range" and make it a calculated
field. Use an expression like
= Fields!SqFoot.Value - Fields!SqFoot.Value Mod 5000
Since you are shifting your range to include the evenly divided number
in the lower group, you really want
= CStr( ( (X-1) - (X-1) Mod 5000 ) + 1 ) & " to " & CStr( ( (X-1) -
(X-1) Mod 5000 ) + 5000 )
C) Create a Matrix with a Market in the Row Group, Sq Foot Range in
the Column Group, and Count( Fields!SqFoot.Value ) in the Details.
Hope that helps.
-- Scott
Wednesday, March 7, 2012
Defining a Query in a Table Adapter
Now, I don't know if what I want to do is possible, but here goes. In the table I want to query, there is an "approval status" column, of type Int32. There are four approval levels, 1, 2, 3 and 4. What I want to set up is a query in the table adapter that can return all entries of one or more approval levels. In "raw" sql, I would do something like:
SELECT * FROM facility_table WHERE (approved IN (1,2,3));
What I want to do though, is to have the list of approval codes to be a parameter that I can pass to the table adapter query, so the where clause becomes "WHERE (approved IN (@.approval))", and I pass a string with the list of approval codes. But the query designer doesn't want to cooperate with me, as it insists that "@.approval" should be an int32.
Any suggestions?
If you have the flexibility to do so, I would recommend changing the datatype to string. Remember you'll need to delimit the individual values when you pass them in - '1','2','3'.
Good luck.
Defining a concatenated column size
I have a column within a view that is a concatenation of several different columns of another table. It goes something like: tblA.columnA + '-' tblA.columnB + '-' tblA.columnC + '-' tblA.columnD As 'NewColumn'
Is it possible to define the size of the 'NewColumn' within the view? I have a stored procedure that is going to create a temp table based on this view, but when I execute the stored procedure I get the following error:
Line 1: Incorrect syntax near '*'.
In looking at the stored procedure code, the error is a result of when the create table occurs, it defines the NewColumn as nvarchar(*), which is causing the error.
Any help would be appreciated.
Thanks.
On Fri, 18 Jun 2004 07:49:01 -0700, Kirk wrote:
>I have a column within a view that is a concatenation of several different columns of another table. It goes something like: tblA.columnA + '-' tblA.columnB + '-' tblA.columnC + '-' tblA.columnD As 'NewColumn'
>Is it possible to define the size of the 'NewColumn' within the view? I have a stored procedure that is going to create a temp table based on this view, but when I execute the stored procedure I get the following error:
>Line 1: Incorrect syntax near '*'.
>In looking at the stored procedure code, the error is a result of when the create table occurs, it defines the NewColumn as nvarchar(*), which is causing the error.
>Any help would be appreciated.
>Thanks.
Hi Kirk,
Define the column as nvarchar(4000) (the maximum length). Or, if you want
the max. length to be shorter, use a lower number. The result of the
concatenation will be trimmed to the max. length the column holds (and you
may also choose to trim explicitly, using LEFT or CAST function).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Is it possible to define the size of the 'NewColumn' within the view? I have a stored procedure that is going to create a temp table based on this view, but when I execute the stored procedure I get the following error:
Line 1: Incorrect syntax near '*'.
In looking at the stored procedure code, the error is a result of when the create table occurs, it defines the NewColumn as nvarchar(*), which is causing the error.
Any help would be appreciated.
Thanks.
On Fri, 18 Jun 2004 07:49:01 -0700, Kirk wrote:
>I have a column within a view that is a concatenation of several different columns of another table. It goes something like: tblA.columnA + '-' tblA.columnB + '-' tblA.columnC + '-' tblA.columnD As 'NewColumn'
>Is it possible to define the size of the 'NewColumn' within the view? I have a stored procedure that is going to create a temp table based on this view, but when I execute the stored procedure I get the following error:
>Line 1: Incorrect syntax near '*'.
>In looking at the stored procedure code, the error is a result of when the create table occurs, it defines the NewColumn as nvarchar(*), which is causing the error.
>Any help would be appreciated.
>Thanks.
Hi Kirk,
Define the column as nvarchar(4000) (the maximum length). Or, if you want
the max. length to be shorter, use a lower number. The result of the
concatenation will be trimmed to the max. length the column holds (and you
may also choose to trim explicitly, using LEFT or CAST function).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)