I have a table with 9 columns in it - ID, ColA, ColB, etc. I'm trying to use
a view to display the ID column,and only one other, which will vary depending
on my requirements. I have another table that contains only one row (and is
updated regularly) with the column name (ColID) I'm trying to retieve.
Can anyone tell me how I can pass the value of ColID into the column name in
my view?
I hope this is clear. Thanks in anticipation...
Take a look at this example:
USE Northwind
CREATE TABLE QueryColumn
(qc sysname)
GO
INSERT INTO QueryColumn VALUES(N'CompanyName')
DECLARE @.q varchar(8000)
SET @.q='SELECT ShipperId, '+(SELECT qc FROM QueryColumn)+' FROM Shippers'
EXEC (@.q)
UPDATE QueryColumn SET qc=N'Phone'
SET @.q='SELECT ShipperId, '+(SELECT qc FROM QueryColumn)+' FROM Shippers'
EXEC (@.q)
GO
DROP TABLE QueryColumn
GO
Do please also read the article at
http://www.sommarskog.se/dynamic_sql.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"MillionChads" <MillionChads@.discussions.microsoft.com> wrote in message
news:ABCE5205-361F-4BB8-8DAC-DCBEF6DACB4C@.microsoft.com...
> I have a table with 9 columns in it - ID, ColA, ColB, etc. I'm trying to
use
> a view to display the ID column,and only one other, which will vary
depending
> on my requirements. I have another table that contains only one row (and
is
> updated regularly) with the column name (ColID) I'm trying to retieve.
> Can anyone tell me how I can pass the value of ColID into the column name
in
> my view?
> I hope this is clear. Thanks in anticipation...
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Wednesday, March 21, 2012
rename columns in a temp table
hello
I have a stored procedure that calculates the values of column data based
on an input variable. I would like to rename the columns to the input
variables so that i can directly graph the results of the table. Is this
possible?
for example, a column in the table #summary is called year1 but i need it to
be renamed to '@.inputYear - 5'. So if @.inputYear = 2000, i need the column
to be renamed to 1995.
thanks for any and all help!
benBen wrote:
> hello
> I have a stored procedure that calculates the values of column data
> based on an input variable. I would like to rename the columns to
> the input variables so that i can directly graph the results of the
> table. Is this possible?
> for example, a column in the table #summary is called year1 but i
> need it to be renamed to '@.inputYear - 5'. So if @.inputYear = 2000,
> i need the column to be renamed to 1995.
> thanks for any and all help!
> ben
You can use an alias for the column, but will have to use dynamic sql.
Or you can return two result sets. The first is the name of the columns,
the second is the data to graph.
EXEC ('Select year1 as "' + CAST(@.inputYear - 5 as VARCHAR(4)) + '"
From...')
David Gugick
Quest Software
www.imceda.com
www.quest.com
I have a stored procedure that calculates the values of column data based
on an input variable. I would like to rename the columns to the input
variables so that i can directly graph the results of the table. Is this
possible?
for example, a column in the table #summary is called year1 but i need it to
be renamed to '@.inputYear - 5'. So if @.inputYear = 2000, i need the column
to be renamed to 1995.
thanks for any and all help!
benBen wrote:
> hello
> I have a stored procedure that calculates the values of column data
> based on an input variable. I would like to rename the columns to
> the input variables so that i can directly graph the results of the
> table. Is this possible?
> for example, a column in the table #summary is called year1 but i
> need it to be renamed to '@.inputYear - 5'. So if @.inputYear = 2000,
> i need the column to be renamed to 1995.
> thanks for any and all help!
> ben
You can use an alias for the column, but will have to use dynamic sql.
Or you can return two result sets. The first is the name of the columns,
the second is the data to graph.
EXEC ('Select year1 as "' + CAST(@.inputYear - 5 as VARCHAR(4)) + '"
From...')
David Gugick
Quest Software
www.imceda.com
www.quest.com
Rename Columns
I need some help with renaming about 115 columns
Here is what I have thus far
EXEC sp_rename
'Copy_AFS.Col015','INTEREST-PAYMENT-CODE',
'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY',
'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY',
'Copy_AFS.Col018','NEXT-INTEREST-PAYMENT-DATE',
'Copy_AFS.Col019','ORIGINAL-TRANS-PURCH-DATE',
'Copy_AFS.Col020','ORIGINAL-BOOK-VALUE',
'Copy_AFS.Col021','PREM-DISC-AT-TRANS-PURCH',
'Copy_AFS.Col022','PREPAY-PERCENT-PER-MONTH',
'Copy_AFS.Col023','LOWEST-LIFETIME-RATE',
'Copy_AFS.Col024','HIGHEST-LIFETIME-RATE',
'Copy_AFS.Col025','MAXIMUM-RATE-CHANGE',
'Copy_AFS.Col026','REPRICING-SPREAD-VALUE',
'Copy_AFS.Col027','LAST-REPRICING-DATE',
'Copy_AFS.Col028','AMORTIZATION-DATE',
'Copy_AFS.Col029','COTERM-RATE',
'Copy_AFS.Col030','COTERM-INDEX',
'Copy_AFS.Col031','AVERAGE-BALANCE',
'Copy_AFS.Col032','TRANSACTION-STATUS-CODE',
'Copy_AFS.Col033','REPRICING-DRIVE-RATE',
'Copy_AFS.Col034','INTEREST-INCOME-MTD',
'Copy_AFS.Col035','INTEREST-INCOME-YTD',
'Copy_AFS.Col036','PROCESSING-DATE',
'Copy_AFS.Col037','ACCT-NUM',
'Copy_AFS.Col038','BANK-NUM',
'Copy_AFS.Col039','CORP-NUM',
'Copy_AFS.Col040','BRANCH-NUM',
'Copy_AFS.Col041','CREDIT-RISK-RATING-CODE',
'Copy_AFS.Col042','COMMERCIAL-RETAIL-FLAG',
'Copy_AFS.Col043','CUSTOMER-NUM',
'Copy_AFS.Col044','COUNTERPARTY-INFO',
'Copy_AFS.Col045','GL-ACCT-NUM-II-PRIN',
'Copy_AFS.Col046','GL-ACCT-NUM-II-IE',
'Copy_AFS.Col047','GL-ACCT-NUM-ACCRUED-INT',
'Copy_AFS.Col048','PRODUCT-CODE',
'Copy_AFS.Col049','SERVICED-LOAN-INDICATOR',
'Copy_AFS.Col050','Special Subsystem FTP Code',
'Copy_AFS.Col051','RESP-CNTR-NUM-II-PRIN',
'Copy_AFS.Col052','RESP-CNTR-NUM-II-IE',
'Copy_AFS.Col053','RESP-CNTR-NUM-ACCRUED-INT',
'Copy_AFS.Col054','BRANCH-RC',
'Copy_AFS.Col055','SIC-CODE',
'Copy_AFS.Col056','SUBSYSID',
'Copy_AFS.Col057','ACCRUED-INT-BAL',
'Copy_AFS.Col058','AVERAGE-COTERM-RATE',
'Copy_AFS.Col059','FTP-CHARGE-CREDIT',
'Copy_AFS.Col060','FTP-LIQUIDITY-CHARGE',
'Copy_AFS.Col061','VARIABLE-COST',
'Copy_AFS.Col062','FIXED-COST',
'Copy_AFS.Col063','OVERHEAD-COST',
'Copy_AFS.Col064','ORIG-TERM',
'Copy_AFS.Col065','REMAINING-TERM',
'Copy_AFS.Col066','AMT-OVERDUE-30',
'Copy_AFS.Col067','AMT-OVERDUE-60',
'Copy_AFS.Col068','AMT-OVERDUE-90',
'Copy_AFS.Col069','AMT-OVERDUE-GT-90',
'Copy_AFS.Col070','PAST-DUE-DAYS',
'Copy_AFS.Col071','FUNDING-SOURCE',
'Copy_AFS.Col072','Fill-1',
'Copy_AFS.Col073','GL-FUNDING-ROLLUP',
'Copy_AFS.Col074','GL-FUNDING-ROLLUP-EXCEPTION',
'Copy_AFS.Col075','FTP-GL-ACCOUNT',
'Copy_AFS.Col076','FTP-LIQUIDITY-ACCOUNT',
'Copy_AFS.Col077','FUNDING-LOOKBACK',
'Copy_AFS.Col078','Filler',
'Copy_AFS.Col079','CALCULATED-COTERM-RATE',
'Copy_AFS.Col080','LIQUIDITY-PREMIUM',
'Copy_AFS.Col081','PREMIUM-TWO',
'Copy_AFS.Col082','PREMIUM-THREE',
'Copy_AFS.Col083','PREMIUM-FOUR',
'Copy_AFS.Col084','FTP-LASTREPRICINGDATE',
'Copy_AFS.Col085','FTP-ORIGINAL-BALANCE',
'Copy_AFS.Col086','FTP-CURRENT-YLD',
'Copy_AFS.Col087','PRODUCT',
'Copy_AFS.Col088','COUNTRY-OF-DOMICILE',
'Copy_AFS.Col089','ACCOUNT-STATUS',
'Copy_AFS.Col090','CURRENT-PAYOFF-AMOUNT',
'Copy_AFS.Col091','OBLIGATION TYPE',
'Copy_AFS.Col092','PROCESSING-TYPE (MC038)',
'Copy_AFS.Col093','INT-HDR-EFFECTIVE-FROM-DATE',
'Copy_AFS.Col094','INT-HDR-EFFECTIVE-TO-DATE',
'Copy_AFS.Col095','INTEREST-PRIME-NUMBER',
'Copy_AFS.Col096','PRIME-EFF-FROM-DATE',
'Copy_AFS.Col097','EFF-THROUGH-DATE (RJ330)',
'Copy_AFS.Col098','TICKLER-TYPE',
'Copy_AFS.Col099','TICKLER-START-DATE',
'Copy_AFS.Col100','TICKLER-EXPIRY-DATE',
'Copy_AFS.Col101','TICKLER-DESCRIPTION',
'Copy_AFS.Col102','PEHERNTY (PE908)',
'Copy_AFS.Col103','TAKEDOWN-OBLIGATION',
'Copy_AFS.Col104','TAKEDOWN-OBLIGOR',
'Copy_AFS.Col105','CHARGE-CD',
'Copy_AFS.Col106','AGGREGATE-BAL-PRINCIPAL',
'Copy_AFS.Col107','LAST-RENEWAL-DATE',
'Copy_AFS.Col108','ACCRUAL-METHOD (PE906)',
'Copy_AFS.Col109','AMORT-CODE (RJ440)',
'Copy_AFS.Col110','INT-HDR-EARNINGS-TYPE',
'Copy_AFS.Col111','AMORT-DATE-CALC',
'Copy_AFS.Col112','Filler2',
'Copy_AFS.Col113','BRANCH-ID',
'Copy_AFS.Col114','OFFICER-CODE',
'Copy_AFS.Col115','FILLER2';
But I am getting the following error
"Procedure or function sp_rename has too many arguments specified."
Please HelpHi, Chris
The error message is correct: there are way too many arguments... Also,
it's not a good idea to use hypens in column names; you should replace
them with underlines. You should use something like this:
EXEC sp_rename 'Copy_AFS.Col015','INTEREST_PAYMENT_CODE'
EXEC sp_rename 'Copy_AFS.Col016','INTEREST_COMPOUND_FREQUENCY'
[...]
Razvan|||sp_rename renames one column at a time and you should specify that you are
renaming a column. So you need
EXEC sp_rename
'Copy_AFS.Col015','INTEREST-PAYMENT-CODE','COLUMN'
EXEC sp_rename
'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY','COLUMN'
EXEC sp_rename
'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY','COLUMN'
etc
Tom
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:55EBC93B-282D-4361-8DDB-DA4C02862F5E@.microsoft.com...
>I need some help with renaming about 115 columns
> Here is what I have thus far
> EXEC sp_rename
> 'Copy_AFS.Col015','INTEREST-PAYMENT-CODE',
> 'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY',
> 'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY',
> 'Copy_AFS.Col018','NEXT-INTEREST-PAYMENT-DATE',
> 'Copy_AFS.Col019','ORIGINAL-TRANS-PURCH-DATE',
> 'Copy_AFS.Col020','ORIGINAL-BOOK-VALUE',
> 'Copy_AFS.Col021','PREM-DISC-AT-TRANS-PURCH',
> 'Copy_AFS.Col022','PREPAY-PERCENT-PER-MONTH',
> 'Copy_AFS.Col023','LOWEST-LIFETIME-RATE',
> 'Copy_AFS.Col024','HIGHEST-LIFETIME-RATE',
> 'Copy_AFS.Col025','MAXIMUM-RATE-CHANGE',
> 'Copy_AFS.Col026','REPRICING-SPREAD-VALUE',
> 'Copy_AFS.Col027','LAST-REPRICING-DATE',
> 'Copy_AFS.Col028','AMORTIZATION-DATE',
> 'Copy_AFS.Col029','COTERM-RATE',
> 'Copy_AFS.Col030','COTERM-INDEX',
> 'Copy_AFS.Col031','AVERAGE-BALANCE',
> 'Copy_AFS.Col032','TRANSACTION-STATUS-CODE',
> 'Copy_AFS.Col033','REPRICING-DRIVE-RATE',
> 'Copy_AFS.Col034','INTEREST-INCOME-MTD',
> 'Copy_AFS.Col035','INTEREST-INCOME-YTD',
> 'Copy_AFS.Col036','PROCESSING-DATE',
> 'Copy_AFS.Col037','ACCT-NUM',
> 'Copy_AFS.Col038','BANK-NUM',
> 'Copy_AFS.Col039','CORP-NUM',
> 'Copy_AFS.Col040','BRANCH-NUM',
> 'Copy_AFS.Col041','CREDIT-RISK-RATING-CODE',
> 'Copy_AFS.Col042','COMMERCIAL-RETAIL-FLAG',
> 'Copy_AFS.Col043','CUSTOMER-NUM',
> 'Copy_AFS.Col044','COUNTERPARTY-INFO',
> 'Copy_AFS.Col045','GL-ACCT-NUM-II-PRIN',
> 'Copy_AFS.Col046','GL-ACCT-NUM-II-IE',
> 'Copy_AFS.Col047','GL-ACCT-NUM-ACCRUED-INT',
> 'Copy_AFS.Col048','PRODUCT-CODE',
> 'Copy_AFS.Col049','SERVICED-LOAN-INDICATOR',
> 'Copy_AFS.Col050','Special Subsystem FTP Code',
> 'Copy_AFS.Col051','RESP-CNTR-NUM-II-PRIN',
> 'Copy_AFS.Col052','RESP-CNTR-NUM-II-IE',
> 'Copy_AFS.Col053','RESP-CNTR-NUM-ACCRUED-INT',
> 'Copy_AFS.Col054','BRANCH-RC',
> 'Copy_AFS.Col055','SIC-CODE',
> 'Copy_AFS.Col056','SUBSYSID',
> 'Copy_AFS.Col057','ACCRUED-INT-BAL',
> 'Copy_AFS.Col058','AVERAGE-COTERM-RATE',
> 'Copy_AFS.Col059','FTP-CHARGE-CREDIT',
> 'Copy_AFS.Col060','FTP-LIQUIDITY-CHARGE',
> 'Copy_AFS.Col061','VARIABLE-COST',
> 'Copy_AFS.Col062','FIXED-COST',
> 'Copy_AFS.Col063','OVERHEAD-COST',
> 'Copy_AFS.Col064','ORIG-TERM',
> 'Copy_AFS.Col065','REMAINING-TERM',
> 'Copy_AFS.Col066','AMT-OVERDUE-30',
> 'Copy_AFS.Col067','AMT-OVERDUE-60',
> 'Copy_AFS.Col068','AMT-OVERDUE-90',
> 'Copy_AFS.Col069','AMT-OVERDUE-GT-90',
> 'Copy_AFS.Col070','PAST-DUE-DAYS',
> 'Copy_AFS.Col071','FUNDING-SOURCE',
> 'Copy_AFS.Col072','Fill-1',
> 'Copy_AFS.Col073','GL-FUNDING-ROLLUP',
> 'Copy_AFS.Col074','GL-FUNDING-ROLLUP-EXCEPTION',
> 'Copy_AFS.Col075','FTP-GL-ACCOUNT',
> 'Copy_AFS.Col076','FTP-LIQUIDITY-ACCOUNT',
> 'Copy_AFS.Col077','FUNDING-LOOKBACK',
> 'Copy_AFS.Col078','Filler',
> 'Copy_AFS.Col079','CALCULATED-COTERM-RATE',
> 'Copy_AFS.Col080','LIQUIDITY-PREMIUM',
> 'Copy_AFS.Col081','PREMIUM-TWO',
> 'Copy_AFS.Col082','PREMIUM-THREE',
> 'Copy_AFS.Col083','PREMIUM-FOUR',
> 'Copy_AFS.Col084','FTP-LASTREPRICINGDATE',
> 'Copy_AFS.Col085','FTP-ORIGINAL-BALANCE',
> 'Copy_AFS.Col086','FTP-CURRENT-YLD',
> 'Copy_AFS.Col087','PRODUCT',
> 'Copy_AFS.Col088','COUNTRY-OF-DOMICILE',
> 'Copy_AFS.Col089','ACCOUNT-STATUS',
> 'Copy_AFS.Col090','CURRENT-PAYOFF-AMOUNT',
> 'Copy_AFS.Col091','OBLIGATION TYPE',
> 'Copy_AFS.Col092','PROCESSING-TYPE (MC038)',
> 'Copy_AFS.Col093','INT-HDR-EFFECTIVE-FROM-DATE',
> 'Copy_AFS.Col094','INT-HDR-EFFECTIVE-TO-DATE',
> 'Copy_AFS.Col095','INTEREST-PRIME-NUMBER',
> 'Copy_AFS.Col096','PRIME-EFF-FROM-DATE',
> 'Copy_AFS.Col097','EFF-THROUGH-DATE (RJ330)',
> 'Copy_AFS.Col098','TICKLER-TYPE',
> 'Copy_AFS.Col099','TICKLER-START-DATE',
> 'Copy_AFS.Col100','TICKLER-EXPIRY-DATE',
> 'Copy_AFS.Col101','TICKLER-DESCRIPTION',
> 'Copy_AFS.Col102','PEHERNTY (PE908)',
> 'Copy_AFS.Col103','TAKEDOWN-OBLIGATION',
> 'Copy_AFS.Col104','TAKEDOWN-OBLIGOR',
> 'Copy_AFS.Col105','CHARGE-CD',
> 'Copy_AFS.Col106','AGGREGATE-BAL-PRINCIPAL',
> 'Copy_AFS.Col107','LAST-RENEWAL-DATE',
> 'Copy_AFS.Col108','ACCRUAL-METHOD (PE906)',
> 'Copy_AFS.Col109','AMORT-CODE (RJ440)',
> 'Copy_AFS.Col110','INT-HDR-EARNINGS-TYPE',
> 'Copy_AFS.Col111','AMORT-DATE-CALC',
> 'Copy_AFS.Col112','Filler2',
> 'Copy_AFS.Col113','BRANCH-ID',
> 'Copy_AFS.Col114','OFFICER-CODE',
> 'Copy_AFS.Col115','FILLER2';
> But I am getting the following error
> "Procedure or function sp_rename has too many arguments specified."
> Please Help
>|||Beware of renaming in database production environment.
sp_rename make a complex SQL Script passing through a temp table.
This can make an extremly high volume of data in log file wich can go
down your server.
Do it in a single temp table if all the columns are in the same table !
A +
Chris a écrit :
> I need some help with renaming about 115 columns
> Here is what I have thus far
> EXEC sp_rename
> 'Copy_AFS.Col015','INTEREST-PAYMENT-CODE',
> 'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY',
> 'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY',
> 'Copy_AFS.Col018','NEXT-INTEREST-PAYMENT-DATE',
> 'Copy_AFS.Col019','ORIGINAL-TRANS-PURCH-DATE',
> 'Copy_AFS.Col020','ORIGINAL-BOOK-VALUE',
> 'Copy_AFS.Col021','PREM-DISC-AT-TRANS-PURCH',
> 'Copy_AFS.Col022','PREPAY-PERCENT-PER-MONTH',
> 'Copy_AFS.Col023','LOWEST-LIFETIME-RATE',
> 'Copy_AFS.Col024','HIGHEST-LIFETIME-RATE',
> 'Copy_AFS.Col025','MAXIMUM-RATE-CHANGE',
> 'Copy_AFS.Col026','REPRICING-SPREAD-VALUE',
> 'Copy_AFS.Col027','LAST-REPRICING-DATE',
> 'Copy_AFS.Col028','AMORTIZATION-DATE',
> 'Copy_AFS.Col029','COTERM-RATE',
> 'Copy_AFS.Col030','COTERM-INDEX',
> 'Copy_AFS.Col031','AVERAGE-BALANCE',
> 'Copy_AFS.Col032','TRANSACTION-STATUS-CODE',
> 'Copy_AFS.Col033','REPRICING-DRIVE-RATE',
> 'Copy_AFS.Col034','INTEREST-INCOME-MTD',
> 'Copy_AFS.Col035','INTEREST-INCOME-YTD',
> 'Copy_AFS.Col036','PROCESSING-DATE',
> 'Copy_AFS.Col037','ACCT-NUM',
> 'Copy_AFS.Col038','BANK-NUM',
> 'Copy_AFS.Col039','CORP-NUM',
> 'Copy_AFS.Col040','BRANCH-NUM',
> 'Copy_AFS.Col041','CREDIT-RISK-RATING-CODE',
> 'Copy_AFS.Col042','COMMERCIAL-RETAIL-FLAG',
> 'Copy_AFS.Col043','CUSTOMER-NUM',
> 'Copy_AFS.Col044','COUNTERPARTY-INFO',
> 'Copy_AFS.Col045','GL-ACCT-NUM-II-PRIN',
> 'Copy_AFS.Col046','GL-ACCT-NUM-II-IE',
> 'Copy_AFS.Col047','GL-ACCT-NUM-ACCRUED-INT',
> 'Copy_AFS.Col048','PRODUCT-CODE',
> 'Copy_AFS.Col049','SERVICED-LOAN-INDICATOR',
> 'Copy_AFS.Col050','Special Subsystem FTP Code',
> 'Copy_AFS.Col051','RESP-CNTR-NUM-II-PRIN',
> 'Copy_AFS.Col052','RESP-CNTR-NUM-II-IE',
> 'Copy_AFS.Col053','RESP-CNTR-NUM-ACCRUED-INT',
> 'Copy_AFS.Col054','BRANCH-RC',
> 'Copy_AFS.Col055','SIC-CODE',
> 'Copy_AFS.Col056','SUBSYSID',
> 'Copy_AFS.Col057','ACCRUED-INT-BAL',
> 'Copy_AFS.Col058','AVERAGE-COTERM-RATE',
> 'Copy_AFS.Col059','FTP-CHARGE-CREDIT',
> 'Copy_AFS.Col060','FTP-LIQUIDITY-CHARGE',
> 'Copy_AFS.Col061','VARIABLE-COST',
> 'Copy_AFS.Col062','FIXED-COST',
> 'Copy_AFS.Col063','OVERHEAD-COST',
> 'Copy_AFS.Col064','ORIG-TERM',
> 'Copy_AFS.Col065','REMAINING-TERM',
> 'Copy_AFS.Col066','AMT-OVERDUE-30',
> 'Copy_AFS.Col067','AMT-OVERDUE-60',
> 'Copy_AFS.Col068','AMT-OVERDUE-90',
> 'Copy_AFS.Col069','AMT-OVERDUE-GT-90',
> 'Copy_AFS.Col070','PAST-DUE-DAYS',
> 'Copy_AFS.Col071','FUNDING-SOURCE',
> 'Copy_AFS.Col072','Fill-1',
> 'Copy_AFS.Col073','GL-FUNDING-ROLLUP',
> 'Copy_AFS.Col074','GL-FUNDING-ROLLUP-EXCEPTION',
> 'Copy_AFS.Col075','FTP-GL-ACCOUNT',
> 'Copy_AFS.Col076','FTP-LIQUIDITY-ACCOUNT',
> 'Copy_AFS.Col077','FUNDING-LOOKBACK',
> 'Copy_AFS.Col078','Filler',
> 'Copy_AFS.Col079','CALCULATED-COTERM-RATE',
> 'Copy_AFS.Col080','LIQUIDITY-PREMIUM',
> 'Copy_AFS.Col081','PREMIUM-TWO',
> 'Copy_AFS.Col082','PREMIUM-THREE',
> 'Copy_AFS.Col083','PREMIUM-FOUR',
> 'Copy_AFS.Col084','FTP-LASTREPRICINGDATE',
> 'Copy_AFS.Col085','FTP-ORIGINAL-BALANCE',
> 'Copy_AFS.Col086','FTP-CURRENT-YLD',
> 'Copy_AFS.Col087','PRODUCT',
> 'Copy_AFS.Col088','COUNTRY-OF-DOMICILE',
> 'Copy_AFS.Col089','ACCOUNT-STATUS',
> 'Copy_AFS.Col090','CURRENT-PAYOFF-AMOUNT',
> 'Copy_AFS.Col091','OBLIGATION TYPE',
> 'Copy_AFS.Col092','PROCESSING-TYPE (MC038)',
> 'Copy_AFS.Col093','INT-HDR-EFFECTIVE-FROM-DATE',
> 'Copy_AFS.Col094','INT-HDR-EFFECTIVE-TO-DATE',
> 'Copy_AFS.Col095','INTEREST-PRIME-NUMBER',
> 'Copy_AFS.Col096','PRIME-EFF-FROM-DATE',
> 'Copy_AFS.Col097','EFF-THROUGH-DATE (RJ330)',
> 'Copy_AFS.Col098','TICKLER-TYPE',
> 'Copy_AFS.Col099','TICKLER-START-DATE',
> 'Copy_AFS.Col100','TICKLER-EXPIRY-DATE',
> 'Copy_AFS.Col101','TICKLER-DESCRIPTION',
> 'Copy_AFS.Col102','PEHERNTY (PE908)',
> 'Copy_AFS.Col103','TAKEDOWN-OBLIGATION',
> 'Copy_AFS.Col104','TAKEDOWN-OBLIGOR',
> 'Copy_AFS.Col105','CHARGE-CD',
> 'Copy_AFS.Col106','AGGREGATE-BAL-PRINCIPAL',
> 'Copy_AFS.Col107','LAST-RENEWAL-DATE',
> 'Copy_AFS.Col108','ACCRUAL-METHOD (PE906)',
> 'Copy_AFS.Col109','AMORT-CODE (RJ440)',
> 'Copy_AFS.Col110','INT-HDR-EARNINGS-TYPE',
> 'Copy_AFS.Col111','AMORT-DATE-CALC',
> 'Copy_AFS.Col112','Filler2',
> 'Copy_AFS.Col113','BRANCH-ID',
> 'Copy_AFS.Col114','OFFICER-CODE',
> 'Copy_AFS.Col115','FILLER2';
> But I am getting the following error
> "Procedure or function sp_rename has too many arguments specified."
> Please Help
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||> sp_rename make a complex SQL Script passing through a temp table.
> This can make an extremly high volume of data in log file wich can go down your se
rver.
I don't understand what you are saying here. Sp_rename does *not* produce a
large number of log
records. In fact, a test show that the procedure produces less than 10 log r
ecords, regardless of
the number of rows in the table. the procedure only changes a value in one o
f the system tables, so
no need for producing lots of log records.
Run below if you want to verify:
USE master
GO
DROP DATABASE x
GO
CREATE DATABASE x
GO
USE x
GO
ALTER DATABASE x SET RECOVERY FULL
BACKUP DATABASE x TO DISK = 'nul'
CREATE TABLE t(c1 int)
GO
BACKUP LOG x TO DISK = 'nul'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
EXEC sp_rename 't.c1', 'cX', 'column'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
GO
--Insert 1000 rows and prove that no more logging:
INSERT INTO t(cX)
SELECT TOP 10000 1 FROM master..sysobjects, master..syscolumns
BACKUP LOG x TO DISK = 'nul'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
EXEC sp_rename 't.cX', 'cY', 'column'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:%233smgTNTGHA.4952@.TK2MSFTNGP09.phx.gbl...
> Beware of renaming in database production environment.
> sp_rename make a complex SQL Script passing through a temp table.
> This can make an extremly high volume of data in log file wich can go down
your server.
> Do it in a single temp table if all the columns are in the same table !
> A +
> Chris a écrit :
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQ
L
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************sql
Here is what I have thus far
EXEC sp_rename
'Copy_AFS.Col015','INTEREST-PAYMENT-CODE',
'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY',
'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY',
'Copy_AFS.Col018','NEXT-INTEREST-PAYMENT-DATE',
'Copy_AFS.Col019','ORIGINAL-TRANS-PURCH-DATE',
'Copy_AFS.Col020','ORIGINAL-BOOK-VALUE',
'Copy_AFS.Col021','PREM-DISC-AT-TRANS-PURCH',
'Copy_AFS.Col022','PREPAY-PERCENT-PER-MONTH',
'Copy_AFS.Col023','LOWEST-LIFETIME-RATE',
'Copy_AFS.Col024','HIGHEST-LIFETIME-RATE',
'Copy_AFS.Col025','MAXIMUM-RATE-CHANGE',
'Copy_AFS.Col026','REPRICING-SPREAD-VALUE',
'Copy_AFS.Col027','LAST-REPRICING-DATE',
'Copy_AFS.Col028','AMORTIZATION-DATE',
'Copy_AFS.Col029','COTERM-RATE',
'Copy_AFS.Col030','COTERM-INDEX',
'Copy_AFS.Col031','AVERAGE-BALANCE',
'Copy_AFS.Col032','TRANSACTION-STATUS-CODE',
'Copy_AFS.Col033','REPRICING-DRIVE-RATE',
'Copy_AFS.Col034','INTEREST-INCOME-MTD',
'Copy_AFS.Col035','INTEREST-INCOME-YTD',
'Copy_AFS.Col036','PROCESSING-DATE',
'Copy_AFS.Col037','ACCT-NUM',
'Copy_AFS.Col038','BANK-NUM',
'Copy_AFS.Col039','CORP-NUM',
'Copy_AFS.Col040','BRANCH-NUM',
'Copy_AFS.Col041','CREDIT-RISK-RATING-CODE',
'Copy_AFS.Col042','COMMERCIAL-RETAIL-FLAG',
'Copy_AFS.Col043','CUSTOMER-NUM',
'Copy_AFS.Col044','COUNTERPARTY-INFO',
'Copy_AFS.Col045','GL-ACCT-NUM-II-PRIN',
'Copy_AFS.Col046','GL-ACCT-NUM-II-IE',
'Copy_AFS.Col047','GL-ACCT-NUM-ACCRUED-INT',
'Copy_AFS.Col048','PRODUCT-CODE',
'Copy_AFS.Col049','SERVICED-LOAN-INDICATOR',
'Copy_AFS.Col050','Special Subsystem FTP Code',
'Copy_AFS.Col051','RESP-CNTR-NUM-II-PRIN',
'Copy_AFS.Col052','RESP-CNTR-NUM-II-IE',
'Copy_AFS.Col053','RESP-CNTR-NUM-ACCRUED-INT',
'Copy_AFS.Col054','BRANCH-RC',
'Copy_AFS.Col055','SIC-CODE',
'Copy_AFS.Col056','SUBSYSID',
'Copy_AFS.Col057','ACCRUED-INT-BAL',
'Copy_AFS.Col058','AVERAGE-COTERM-RATE',
'Copy_AFS.Col059','FTP-CHARGE-CREDIT',
'Copy_AFS.Col060','FTP-LIQUIDITY-CHARGE',
'Copy_AFS.Col061','VARIABLE-COST',
'Copy_AFS.Col062','FIXED-COST',
'Copy_AFS.Col063','OVERHEAD-COST',
'Copy_AFS.Col064','ORIG-TERM',
'Copy_AFS.Col065','REMAINING-TERM',
'Copy_AFS.Col066','AMT-OVERDUE-30',
'Copy_AFS.Col067','AMT-OVERDUE-60',
'Copy_AFS.Col068','AMT-OVERDUE-90',
'Copy_AFS.Col069','AMT-OVERDUE-GT-90',
'Copy_AFS.Col070','PAST-DUE-DAYS',
'Copy_AFS.Col071','FUNDING-SOURCE',
'Copy_AFS.Col072','Fill-1',
'Copy_AFS.Col073','GL-FUNDING-ROLLUP',
'Copy_AFS.Col074','GL-FUNDING-ROLLUP-EXCEPTION',
'Copy_AFS.Col075','FTP-GL-ACCOUNT',
'Copy_AFS.Col076','FTP-LIQUIDITY-ACCOUNT',
'Copy_AFS.Col077','FUNDING-LOOKBACK',
'Copy_AFS.Col078','Filler',
'Copy_AFS.Col079','CALCULATED-COTERM-RATE',
'Copy_AFS.Col080','LIQUIDITY-PREMIUM',
'Copy_AFS.Col081','PREMIUM-TWO',
'Copy_AFS.Col082','PREMIUM-THREE',
'Copy_AFS.Col083','PREMIUM-FOUR',
'Copy_AFS.Col084','FTP-LASTREPRICINGDATE',
'Copy_AFS.Col085','FTP-ORIGINAL-BALANCE',
'Copy_AFS.Col086','FTP-CURRENT-YLD',
'Copy_AFS.Col087','PRODUCT',
'Copy_AFS.Col088','COUNTRY-OF-DOMICILE',
'Copy_AFS.Col089','ACCOUNT-STATUS',
'Copy_AFS.Col090','CURRENT-PAYOFF-AMOUNT',
'Copy_AFS.Col091','OBLIGATION TYPE',
'Copy_AFS.Col092','PROCESSING-TYPE (MC038)',
'Copy_AFS.Col093','INT-HDR-EFFECTIVE-FROM-DATE',
'Copy_AFS.Col094','INT-HDR-EFFECTIVE-TO-DATE',
'Copy_AFS.Col095','INTEREST-PRIME-NUMBER',
'Copy_AFS.Col096','PRIME-EFF-FROM-DATE',
'Copy_AFS.Col097','EFF-THROUGH-DATE (RJ330)',
'Copy_AFS.Col098','TICKLER-TYPE',
'Copy_AFS.Col099','TICKLER-START-DATE',
'Copy_AFS.Col100','TICKLER-EXPIRY-DATE',
'Copy_AFS.Col101','TICKLER-DESCRIPTION',
'Copy_AFS.Col102','PEHERNTY (PE908)',
'Copy_AFS.Col103','TAKEDOWN-OBLIGATION',
'Copy_AFS.Col104','TAKEDOWN-OBLIGOR',
'Copy_AFS.Col105','CHARGE-CD',
'Copy_AFS.Col106','AGGREGATE-BAL-PRINCIPAL',
'Copy_AFS.Col107','LAST-RENEWAL-DATE',
'Copy_AFS.Col108','ACCRUAL-METHOD (PE906)',
'Copy_AFS.Col109','AMORT-CODE (RJ440)',
'Copy_AFS.Col110','INT-HDR-EARNINGS-TYPE',
'Copy_AFS.Col111','AMORT-DATE-CALC',
'Copy_AFS.Col112','Filler2',
'Copy_AFS.Col113','BRANCH-ID',
'Copy_AFS.Col114','OFFICER-CODE',
'Copy_AFS.Col115','FILLER2';
But I am getting the following error
"Procedure or function sp_rename has too many arguments specified."
Please HelpHi, Chris
The error message is correct: there are way too many arguments... Also,
it's not a good idea to use hypens in column names; you should replace
them with underlines. You should use something like this:
EXEC sp_rename 'Copy_AFS.Col015','INTEREST_PAYMENT_CODE'
EXEC sp_rename 'Copy_AFS.Col016','INTEREST_COMPOUND_FREQUENCY'
[...]
Razvan|||sp_rename renames one column at a time and you should specify that you are
renaming a column. So you need
EXEC sp_rename
'Copy_AFS.Col015','INTEREST-PAYMENT-CODE','COLUMN'
EXEC sp_rename
'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY','COLUMN'
EXEC sp_rename
'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY','COLUMN'
etc
Tom
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:55EBC93B-282D-4361-8DDB-DA4C02862F5E@.microsoft.com...
>I need some help with renaming about 115 columns
> Here is what I have thus far
> EXEC sp_rename
> 'Copy_AFS.Col015','INTEREST-PAYMENT-CODE',
> 'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY',
> 'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY',
> 'Copy_AFS.Col018','NEXT-INTEREST-PAYMENT-DATE',
> 'Copy_AFS.Col019','ORIGINAL-TRANS-PURCH-DATE',
> 'Copy_AFS.Col020','ORIGINAL-BOOK-VALUE',
> 'Copy_AFS.Col021','PREM-DISC-AT-TRANS-PURCH',
> 'Copy_AFS.Col022','PREPAY-PERCENT-PER-MONTH',
> 'Copy_AFS.Col023','LOWEST-LIFETIME-RATE',
> 'Copy_AFS.Col024','HIGHEST-LIFETIME-RATE',
> 'Copy_AFS.Col025','MAXIMUM-RATE-CHANGE',
> 'Copy_AFS.Col026','REPRICING-SPREAD-VALUE',
> 'Copy_AFS.Col027','LAST-REPRICING-DATE',
> 'Copy_AFS.Col028','AMORTIZATION-DATE',
> 'Copy_AFS.Col029','COTERM-RATE',
> 'Copy_AFS.Col030','COTERM-INDEX',
> 'Copy_AFS.Col031','AVERAGE-BALANCE',
> 'Copy_AFS.Col032','TRANSACTION-STATUS-CODE',
> 'Copy_AFS.Col033','REPRICING-DRIVE-RATE',
> 'Copy_AFS.Col034','INTEREST-INCOME-MTD',
> 'Copy_AFS.Col035','INTEREST-INCOME-YTD',
> 'Copy_AFS.Col036','PROCESSING-DATE',
> 'Copy_AFS.Col037','ACCT-NUM',
> 'Copy_AFS.Col038','BANK-NUM',
> 'Copy_AFS.Col039','CORP-NUM',
> 'Copy_AFS.Col040','BRANCH-NUM',
> 'Copy_AFS.Col041','CREDIT-RISK-RATING-CODE',
> 'Copy_AFS.Col042','COMMERCIAL-RETAIL-FLAG',
> 'Copy_AFS.Col043','CUSTOMER-NUM',
> 'Copy_AFS.Col044','COUNTERPARTY-INFO',
> 'Copy_AFS.Col045','GL-ACCT-NUM-II-PRIN',
> 'Copy_AFS.Col046','GL-ACCT-NUM-II-IE',
> 'Copy_AFS.Col047','GL-ACCT-NUM-ACCRUED-INT',
> 'Copy_AFS.Col048','PRODUCT-CODE',
> 'Copy_AFS.Col049','SERVICED-LOAN-INDICATOR',
> 'Copy_AFS.Col050','Special Subsystem FTP Code',
> 'Copy_AFS.Col051','RESP-CNTR-NUM-II-PRIN',
> 'Copy_AFS.Col052','RESP-CNTR-NUM-II-IE',
> 'Copy_AFS.Col053','RESP-CNTR-NUM-ACCRUED-INT',
> 'Copy_AFS.Col054','BRANCH-RC',
> 'Copy_AFS.Col055','SIC-CODE',
> 'Copy_AFS.Col056','SUBSYSID',
> 'Copy_AFS.Col057','ACCRUED-INT-BAL',
> 'Copy_AFS.Col058','AVERAGE-COTERM-RATE',
> 'Copy_AFS.Col059','FTP-CHARGE-CREDIT',
> 'Copy_AFS.Col060','FTP-LIQUIDITY-CHARGE',
> 'Copy_AFS.Col061','VARIABLE-COST',
> 'Copy_AFS.Col062','FIXED-COST',
> 'Copy_AFS.Col063','OVERHEAD-COST',
> 'Copy_AFS.Col064','ORIG-TERM',
> 'Copy_AFS.Col065','REMAINING-TERM',
> 'Copy_AFS.Col066','AMT-OVERDUE-30',
> 'Copy_AFS.Col067','AMT-OVERDUE-60',
> 'Copy_AFS.Col068','AMT-OVERDUE-90',
> 'Copy_AFS.Col069','AMT-OVERDUE-GT-90',
> 'Copy_AFS.Col070','PAST-DUE-DAYS',
> 'Copy_AFS.Col071','FUNDING-SOURCE',
> 'Copy_AFS.Col072','Fill-1',
> 'Copy_AFS.Col073','GL-FUNDING-ROLLUP',
> 'Copy_AFS.Col074','GL-FUNDING-ROLLUP-EXCEPTION',
> 'Copy_AFS.Col075','FTP-GL-ACCOUNT',
> 'Copy_AFS.Col076','FTP-LIQUIDITY-ACCOUNT',
> 'Copy_AFS.Col077','FUNDING-LOOKBACK',
> 'Copy_AFS.Col078','Filler',
> 'Copy_AFS.Col079','CALCULATED-COTERM-RATE',
> 'Copy_AFS.Col080','LIQUIDITY-PREMIUM',
> 'Copy_AFS.Col081','PREMIUM-TWO',
> 'Copy_AFS.Col082','PREMIUM-THREE',
> 'Copy_AFS.Col083','PREMIUM-FOUR',
> 'Copy_AFS.Col084','FTP-LASTREPRICINGDATE',
> 'Copy_AFS.Col085','FTP-ORIGINAL-BALANCE',
> 'Copy_AFS.Col086','FTP-CURRENT-YLD',
> 'Copy_AFS.Col087','PRODUCT',
> 'Copy_AFS.Col088','COUNTRY-OF-DOMICILE',
> 'Copy_AFS.Col089','ACCOUNT-STATUS',
> 'Copy_AFS.Col090','CURRENT-PAYOFF-AMOUNT',
> 'Copy_AFS.Col091','OBLIGATION TYPE',
> 'Copy_AFS.Col092','PROCESSING-TYPE (MC038)',
> 'Copy_AFS.Col093','INT-HDR-EFFECTIVE-FROM-DATE',
> 'Copy_AFS.Col094','INT-HDR-EFFECTIVE-TO-DATE',
> 'Copy_AFS.Col095','INTEREST-PRIME-NUMBER',
> 'Copy_AFS.Col096','PRIME-EFF-FROM-DATE',
> 'Copy_AFS.Col097','EFF-THROUGH-DATE (RJ330)',
> 'Copy_AFS.Col098','TICKLER-TYPE',
> 'Copy_AFS.Col099','TICKLER-START-DATE',
> 'Copy_AFS.Col100','TICKLER-EXPIRY-DATE',
> 'Copy_AFS.Col101','TICKLER-DESCRIPTION',
> 'Copy_AFS.Col102','PEHERNTY (PE908)',
> 'Copy_AFS.Col103','TAKEDOWN-OBLIGATION',
> 'Copy_AFS.Col104','TAKEDOWN-OBLIGOR',
> 'Copy_AFS.Col105','CHARGE-CD',
> 'Copy_AFS.Col106','AGGREGATE-BAL-PRINCIPAL',
> 'Copy_AFS.Col107','LAST-RENEWAL-DATE',
> 'Copy_AFS.Col108','ACCRUAL-METHOD (PE906)',
> 'Copy_AFS.Col109','AMORT-CODE (RJ440)',
> 'Copy_AFS.Col110','INT-HDR-EARNINGS-TYPE',
> 'Copy_AFS.Col111','AMORT-DATE-CALC',
> 'Copy_AFS.Col112','Filler2',
> 'Copy_AFS.Col113','BRANCH-ID',
> 'Copy_AFS.Col114','OFFICER-CODE',
> 'Copy_AFS.Col115','FILLER2';
> But I am getting the following error
> "Procedure or function sp_rename has too many arguments specified."
> Please Help
>|||Beware of renaming in database production environment.
sp_rename make a complex SQL Script passing through a temp table.
This can make an extremly high volume of data in log file wich can go
down your server.
Do it in a single temp table if all the columns are in the same table !
A +
Chris a écrit :
> I need some help with renaming about 115 columns
> Here is what I have thus far
> EXEC sp_rename
> 'Copy_AFS.Col015','INTEREST-PAYMENT-CODE',
> 'Copy_AFS.Col016','INTEREST-COMPOUND-FREQUENCY',
> 'Copy_AFS.Col017','INTEREST-PAYMENT-FREQUENCY',
> 'Copy_AFS.Col018','NEXT-INTEREST-PAYMENT-DATE',
> 'Copy_AFS.Col019','ORIGINAL-TRANS-PURCH-DATE',
> 'Copy_AFS.Col020','ORIGINAL-BOOK-VALUE',
> 'Copy_AFS.Col021','PREM-DISC-AT-TRANS-PURCH',
> 'Copy_AFS.Col022','PREPAY-PERCENT-PER-MONTH',
> 'Copy_AFS.Col023','LOWEST-LIFETIME-RATE',
> 'Copy_AFS.Col024','HIGHEST-LIFETIME-RATE',
> 'Copy_AFS.Col025','MAXIMUM-RATE-CHANGE',
> 'Copy_AFS.Col026','REPRICING-SPREAD-VALUE',
> 'Copy_AFS.Col027','LAST-REPRICING-DATE',
> 'Copy_AFS.Col028','AMORTIZATION-DATE',
> 'Copy_AFS.Col029','COTERM-RATE',
> 'Copy_AFS.Col030','COTERM-INDEX',
> 'Copy_AFS.Col031','AVERAGE-BALANCE',
> 'Copy_AFS.Col032','TRANSACTION-STATUS-CODE',
> 'Copy_AFS.Col033','REPRICING-DRIVE-RATE',
> 'Copy_AFS.Col034','INTEREST-INCOME-MTD',
> 'Copy_AFS.Col035','INTEREST-INCOME-YTD',
> 'Copy_AFS.Col036','PROCESSING-DATE',
> 'Copy_AFS.Col037','ACCT-NUM',
> 'Copy_AFS.Col038','BANK-NUM',
> 'Copy_AFS.Col039','CORP-NUM',
> 'Copy_AFS.Col040','BRANCH-NUM',
> 'Copy_AFS.Col041','CREDIT-RISK-RATING-CODE',
> 'Copy_AFS.Col042','COMMERCIAL-RETAIL-FLAG',
> 'Copy_AFS.Col043','CUSTOMER-NUM',
> 'Copy_AFS.Col044','COUNTERPARTY-INFO',
> 'Copy_AFS.Col045','GL-ACCT-NUM-II-PRIN',
> 'Copy_AFS.Col046','GL-ACCT-NUM-II-IE',
> 'Copy_AFS.Col047','GL-ACCT-NUM-ACCRUED-INT',
> 'Copy_AFS.Col048','PRODUCT-CODE',
> 'Copy_AFS.Col049','SERVICED-LOAN-INDICATOR',
> 'Copy_AFS.Col050','Special Subsystem FTP Code',
> 'Copy_AFS.Col051','RESP-CNTR-NUM-II-PRIN',
> 'Copy_AFS.Col052','RESP-CNTR-NUM-II-IE',
> 'Copy_AFS.Col053','RESP-CNTR-NUM-ACCRUED-INT',
> 'Copy_AFS.Col054','BRANCH-RC',
> 'Copy_AFS.Col055','SIC-CODE',
> 'Copy_AFS.Col056','SUBSYSID',
> 'Copy_AFS.Col057','ACCRUED-INT-BAL',
> 'Copy_AFS.Col058','AVERAGE-COTERM-RATE',
> 'Copy_AFS.Col059','FTP-CHARGE-CREDIT',
> 'Copy_AFS.Col060','FTP-LIQUIDITY-CHARGE',
> 'Copy_AFS.Col061','VARIABLE-COST',
> 'Copy_AFS.Col062','FIXED-COST',
> 'Copy_AFS.Col063','OVERHEAD-COST',
> 'Copy_AFS.Col064','ORIG-TERM',
> 'Copy_AFS.Col065','REMAINING-TERM',
> 'Copy_AFS.Col066','AMT-OVERDUE-30',
> 'Copy_AFS.Col067','AMT-OVERDUE-60',
> 'Copy_AFS.Col068','AMT-OVERDUE-90',
> 'Copy_AFS.Col069','AMT-OVERDUE-GT-90',
> 'Copy_AFS.Col070','PAST-DUE-DAYS',
> 'Copy_AFS.Col071','FUNDING-SOURCE',
> 'Copy_AFS.Col072','Fill-1',
> 'Copy_AFS.Col073','GL-FUNDING-ROLLUP',
> 'Copy_AFS.Col074','GL-FUNDING-ROLLUP-EXCEPTION',
> 'Copy_AFS.Col075','FTP-GL-ACCOUNT',
> 'Copy_AFS.Col076','FTP-LIQUIDITY-ACCOUNT',
> 'Copy_AFS.Col077','FUNDING-LOOKBACK',
> 'Copy_AFS.Col078','Filler',
> 'Copy_AFS.Col079','CALCULATED-COTERM-RATE',
> 'Copy_AFS.Col080','LIQUIDITY-PREMIUM',
> 'Copy_AFS.Col081','PREMIUM-TWO',
> 'Copy_AFS.Col082','PREMIUM-THREE',
> 'Copy_AFS.Col083','PREMIUM-FOUR',
> 'Copy_AFS.Col084','FTP-LASTREPRICINGDATE',
> 'Copy_AFS.Col085','FTP-ORIGINAL-BALANCE',
> 'Copy_AFS.Col086','FTP-CURRENT-YLD',
> 'Copy_AFS.Col087','PRODUCT',
> 'Copy_AFS.Col088','COUNTRY-OF-DOMICILE',
> 'Copy_AFS.Col089','ACCOUNT-STATUS',
> 'Copy_AFS.Col090','CURRENT-PAYOFF-AMOUNT',
> 'Copy_AFS.Col091','OBLIGATION TYPE',
> 'Copy_AFS.Col092','PROCESSING-TYPE (MC038)',
> 'Copy_AFS.Col093','INT-HDR-EFFECTIVE-FROM-DATE',
> 'Copy_AFS.Col094','INT-HDR-EFFECTIVE-TO-DATE',
> 'Copy_AFS.Col095','INTEREST-PRIME-NUMBER',
> 'Copy_AFS.Col096','PRIME-EFF-FROM-DATE',
> 'Copy_AFS.Col097','EFF-THROUGH-DATE (RJ330)',
> 'Copy_AFS.Col098','TICKLER-TYPE',
> 'Copy_AFS.Col099','TICKLER-START-DATE',
> 'Copy_AFS.Col100','TICKLER-EXPIRY-DATE',
> 'Copy_AFS.Col101','TICKLER-DESCRIPTION',
> 'Copy_AFS.Col102','PEHERNTY (PE908)',
> 'Copy_AFS.Col103','TAKEDOWN-OBLIGATION',
> 'Copy_AFS.Col104','TAKEDOWN-OBLIGOR',
> 'Copy_AFS.Col105','CHARGE-CD',
> 'Copy_AFS.Col106','AGGREGATE-BAL-PRINCIPAL',
> 'Copy_AFS.Col107','LAST-RENEWAL-DATE',
> 'Copy_AFS.Col108','ACCRUAL-METHOD (PE906)',
> 'Copy_AFS.Col109','AMORT-CODE (RJ440)',
> 'Copy_AFS.Col110','INT-HDR-EARNINGS-TYPE',
> 'Copy_AFS.Col111','AMORT-DATE-CALC',
> 'Copy_AFS.Col112','Filler2',
> 'Copy_AFS.Col113','BRANCH-ID',
> 'Copy_AFS.Col114','OFFICER-CODE',
> 'Copy_AFS.Col115','FILLER2';
> But I am getting the following error
> "Procedure or function sp_rename has too many arguments specified."
> Please Help
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||> sp_rename make a complex SQL Script passing through a temp table.
> This can make an extremly high volume of data in log file wich can go down your se
rver.
I don't understand what you are saying here. Sp_rename does *not* produce a
large number of log
records. In fact, a test show that the procedure produces less than 10 log r
ecords, regardless of
the number of rows in the table. the procedure only changes a value in one o
f the system tables, so
no need for producing lots of log records.
Run below if you want to verify:
USE master
GO
DROP DATABASE x
GO
CREATE DATABASE x
GO
USE x
GO
ALTER DATABASE x SET RECOVERY FULL
BACKUP DATABASE x TO DISK = 'nul'
CREATE TABLE t(c1 int)
GO
BACKUP LOG x TO DISK = 'nul'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
EXEC sp_rename 't.c1', 'cX', 'column'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
GO
--Insert 1000 rows and prove that no more logging:
INSERT INTO t(cX)
SELECT TOP 10000 1 FROM master..sysobjects, master..syscolumns
BACKUP LOG x TO DISK = 'nul'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
EXEC sp_rename 't.cX', 'cY', 'column'
SELECT COUNT(*) FROM ::fn_dblog(NULL, NULL)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:%233smgTNTGHA.4952@.TK2MSFTNGP09.phx.gbl...
> Beware of renaming in database production environment.
> sp_rename make a complex SQL Script passing through a temp table.
> This can make an extremly high volume of data in log file wich can go down
your server.
> Do it in a single temp table if all the columns are in the same table !
> A +
> Chris a écrit :
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQ
L
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************sql
Tuesday, March 20, 2012
Re-name a Column in an Anonymous Merge Publication
Upsized an Access .MDB and Published successfully, EXCEPT forgot to make
visible the sysobjects, so two Tables each have four Columns named: Expr1,
Expr2, Expr3, Expr4. When I try to rename them with SSEM in the SQL Database,
I get errors like this:
'tblNoteAdmit' table
- Unable to rename column from 'Expr1' to 'Pump'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the
table because it is published for replication.
It looks like time to Create a Script, that the Snapshot Agent can
understand ...
Sometime back, I blundered through that process to Add a Table
(Successfully) but am having trouble finding the SP_ to trick Column Names.
Aubrey Kelley
How do I Rename a Column in an Active Replicated Database?
24 hr update: Got this far: (SP is sp_rename, but still blocked by
Replication.)
USE CareData
GO
EXEC sp_rename 'tblNoteAdmit.Expr1', 'Pump', 'COLUMN'
Server: Msg 15051, Level 11, State 1, Procedure sp_rename, Line 172
Cannot rename the table because it is published for replication.
Aubrey Kelley
"Aubrey" wrote:
> Upsized an Access .MDB and Published successfully, EXCEPT forgot to make
> visible the sysobjects, so two Tables each have four Columns named: Expr1,
> Expr2, Expr3, Expr4. When I try to rename them with SSEM in the SQL Database,
> I get errors like this:
> 'tblNoteAdmit' table
> - Unable to rename column from 'Expr1' to 'Pump'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the
> table because it is published for replication.
> It looks like time to Create a Script, that the Snapshot Agent can
> understand ...
> Sometime back, I blundered through that process to Add a Table
> (Successfully) but am having trouble finding the SP_ to trick Column Names.
> --
> Aubrey Kelley
|||You will have to do sp_repladdcolumn to add a dummy column with the same
data type as the column you wish to rename. Then update this temp column
with the values in the column you are wishing to rename. Then drop the
column you wish to rename using sp_repldropcolumn. Then readd the column
with the new name using sp_repladdcolumn. Then update this column with the
values in the temp column. Then drop the temp column using
sp_repldropcolumn.
Alternatively you may wish to create a temp table with the values in the
column you wish to rename along with the PK. Then drop the column you wish
to rename using sp_repldropcolumn, and then add it back with the new name
using sp_repladdcolumn. Update this new column with the values in the temp
table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Aubrey" <miscuates@.online.nospam> wrote in message
news:51D04366-E78F-463C-A7C0-17E2CE461867@.microsoft.com...[vbcol=seagreen]
> How do I Rename a Column in an Active Replicated Database?
> 24 hr update: Got this far: (SP is sp_rename, but still blocked by
> Replication.)
> USE CareData
> GO
> EXEC sp_rename 'tblNoteAdmit.Expr1', 'Pump', 'COLUMN'
> Server: Msg 15051, Level 11, State 1, Procedure sp_rename, Line 172
> Cannot rename the table because it is published for replication.
> --
> Aubrey Kelley
>
> "Aubrey" wrote:
Expr1,[vbcol=seagreen]
Database,[vbcol=seagreen]
the[vbcol=seagreen]
Names.[vbcol=seagreen]
|||Thanks, Hilary! and only 43 minutes after my updated post, Sunday early AM?
That was what it looked like in your SS2K T&SR Book. Was not sure it applied
to Anonymous Merge. Will try it Real-Soon-Now ...
Aubrey Kelley
"Hilary Cotter" wrote:
> You will have to do sp_repladdcolumn to add a dummy column with the same
> data type as the column you wish to rename. Then update this temp column
> with the values in the column you are wishing to rename. Then drop the
> column you wish to rename using sp_repldropcolumn. Then readd the column
> with the new name using sp_repladdcolumn. Then update this column with the
> values in the temp column. Then drop the temp column using
> sp_repldropcolumn.
> Alternatively you may wish to create a temp table with the values in the
> column you wish to rename along with the PK. Then drop the column you wish
> to rename using sp_repldropcolumn, and then add it back with the new name
> using sp_repladdcolumn. Update this new column with the values in the temp
> table.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Aubrey" <miscuates@.online.nospam> wrote in message
> news:51D04366-E78F-463C-A7C0-17E2CE461867@.microsoft.com...
> Expr1,
> Database,
> the
> Names.
>
>
|||What I did: Opened Query Analyzer
USE CareData
GO
EXEC sp_repladdcolumn N'Orders', N'IsSaved', 'bit', N'CareData'
[RUN]
Results:
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'CareData' because schema replication is performed.
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
The stored procedure will still be created.
Updated Values and Tested from Remote Client. Voila! Worked like a charm.
Why did you suggest a Temp column? Seemed superfluous to Copy from OldColumn
to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn
works great.
Aubrey Kelley
"Aubrey" wrote:
[vbcol=seagreen]
> Thanks, Hilary! and only 43 minutes after my updated post, Sunday early AM?
> That was what it looked like in your SS2K T&SR Book. Was not sure it applied
> to Anonymous Merge. Will try it Real-Soon-Now ...
> --
> Aubrey Kelley
>
> "Hilary Cotter" wrote:
|||I'm glad it worked.
The temp table solution works great if you are able to kick your users off
while you do it. If not and the table was huge, the temp table could take
come time to populate and will get progressively out of sync if people are
banging away at it. So you would loose consistency as you do it.
However, my approach will "break" the table as you are making the changes so
the app which is using this column will fail as you do your renaming.
So neither solutions are perfect - mine takes longer but might guarantee
better consistency than yours. Your method is shorter, but your method is
preferred if you can kick all of your users off your system.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Aubrey" <miscuates@.online.nospam> wrote in message
news:A9C71B7B-F1AF-4DAD-A202-6CF3DD2703FB@.microsoft.com...
> What I did: Opened Query Analyzer
> USE CareData
> GO
> EXEC sp_repladdcolumn N'Orders', N'IsSaved', 'bit', N'CareData'
> [RUN]
> Results:
> Warning: only Subscribers running SQL Server 2000 can synchronize with
> publication 'CareData' because schema replication is performed.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object
'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
> The stored procedure will still be created.
> Updated Values and Tested from Remote Client. Voila! Worked like a charm.
> Why did you suggest a Temp column? Seemed superfluous to Copy from
OldColumn[vbcol=seagreen]
> to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn
> works great.
> --
> Aubrey Kelley
>
> "Aubrey" wrote:
AM?[vbcol=seagreen]
applied[vbcol=seagreen]
same[vbcol=seagreen]
column[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
wish[vbcol=seagreen]
name[vbcol=seagreen]
temp[vbcol=seagreen]
to make[vbcol=seagreen]
named:[vbcol=seagreen]
SQL[vbcol=seagreen]
rename[vbcol=seagreen]
Column[vbcol=seagreen]
|||Correct; I was fortunate that ALL Users are Merge Clients, No ONE is directly
updating the Publisher Database.
Just to Test, I added a couple of Rows while all this was running, and they
were missing the Values in the New Columns. Fortunately I had WHERE Clauses
in the Data Copy Form, Event Procedures just in case the Project Locked Up or
Aborted.
Now, onward and upward ... Have a Great Week!
Aubrey Kelley
"Hilary Cotter" wrote:
> I'm glad it worked.
> The temp table solution works great if you are able to kick your users off
> while you do it. If not and the table was huge, the temp table could take
> come time to populate and will get progressively out of sync if people are
> banging away at it. So you would loose consistency as you do it.
> However, my approach will "break" the table as you are making the changes so
> the app which is using this column will fail as you do your renaming.
> So neither solutions are perfect - mine takes longer but might guarantee
> better consistency than yours. Your method is shorter, but your method is
> preferred if you can kick all of your users off your system.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Aubrey" <miscuates@.online.nospam> wrote in message
> news:A9C71B7B-F1AF-4DAD-A202-6CF3DD2703FB@.microsoft.com...
> 'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
> OldColumn
> AM?
> applied
> same
> column
> column
> the
> the
> wish
> name
> temp
> to make
> named:
> SQL
> rename
> Column
>
>
visible the sysobjects, so two Tables each have four Columns named: Expr1,
Expr2, Expr3, Expr4. When I try to rename them with SSEM in the SQL Database,
I get errors like this:
'tblNoteAdmit' table
- Unable to rename column from 'Expr1' to 'Pump'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the
table because it is published for replication.
It looks like time to Create a Script, that the Snapshot Agent can
understand ...
Sometime back, I blundered through that process to Add a Table
(Successfully) but am having trouble finding the SP_ to trick Column Names.
Aubrey Kelley
How do I Rename a Column in an Active Replicated Database?
24 hr update: Got this far: (SP is sp_rename, but still blocked by
Replication.)
USE CareData
GO
EXEC sp_rename 'tblNoteAdmit.Expr1', 'Pump', 'COLUMN'
Server: Msg 15051, Level 11, State 1, Procedure sp_rename, Line 172
Cannot rename the table because it is published for replication.
Aubrey Kelley
"Aubrey" wrote:
> Upsized an Access .MDB and Published successfully, EXCEPT forgot to make
> visible the sysobjects, so two Tables each have four Columns named: Expr1,
> Expr2, Expr3, Expr4. When I try to rename them with SSEM in the SQL Database,
> I get errors like this:
> 'tblNoteAdmit' table
> - Unable to rename column from 'Expr1' to 'Pump'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the
> table because it is published for replication.
> It looks like time to Create a Script, that the Snapshot Agent can
> understand ...
> Sometime back, I blundered through that process to Add a Table
> (Successfully) but am having trouble finding the SP_ to trick Column Names.
> --
> Aubrey Kelley
|||You will have to do sp_repladdcolumn to add a dummy column with the same
data type as the column you wish to rename. Then update this temp column
with the values in the column you are wishing to rename. Then drop the
column you wish to rename using sp_repldropcolumn. Then readd the column
with the new name using sp_repladdcolumn. Then update this column with the
values in the temp column. Then drop the temp column using
sp_repldropcolumn.
Alternatively you may wish to create a temp table with the values in the
column you wish to rename along with the PK. Then drop the column you wish
to rename using sp_repldropcolumn, and then add it back with the new name
using sp_repladdcolumn. Update this new column with the values in the temp
table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Aubrey" <miscuates@.online.nospam> wrote in message
news:51D04366-E78F-463C-A7C0-17E2CE461867@.microsoft.com...[vbcol=seagreen]
> How do I Rename a Column in an Active Replicated Database?
> 24 hr update: Got this far: (SP is sp_rename, but still blocked by
> Replication.)
> USE CareData
> GO
> EXEC sp_rename 'tblNoteAdmit.Expr1', 'Pump', 'COLUMN'
> Server: Msg 15051, Level 11, State 1, Procedure sp_rename, Line 172
> Cannot rename the table because it is published for replication.
> --
> Aubrey Kelley
>
> "Aubrey" wrote:
Expr1,[vbcol=seagreen]
Database,[vbcol=seagreen]
the[vbcol=seagreen]
Names.[vbcol=seagreen]
|||Thanks, Hilary! and only 43 minutes after my updated post, Sunday early AM?
That was what it looked like in your SS2K T&SR Book. Was not sure it applied
to Anonymous Merge. Will try it Real-Soon-Now ...
Aubrey Kelley
"Hilary Cotter" wrote:
> You will have to do sp_repladdcolumn to add a dummy column with the same
> data type as the column you wish to rename. Then update this temp column
> with the values in the column you are wishing to rename. Then drop the
> column you wish to rename using sp_repldropcolumn. Then readd the column
> with the new name using sp_repladdcolumn. Then update this column with the
> values in the temp column. Then drop the temp column using
> sp_repldropcolumn.
> Alternatively you may wish to create a temp table with the values in the
> column you wish to rename along with the PK. Then drop the column you wish
> to rename using sp_repldropcolumn, and then add it back with the new name
> using sp_repladdcolumn. Update this new column with the values in the temp
> table.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Aubrey" <miscuates@.online.nospam> wrote in message
> news:51D04366-E78F-463C-A7C0-17E2CE461867@.microsoft.com...
> Expr1,
> Database,
> the
> Names.
>
>
|||What I did: Opened Query Analyzer
USE CareData
GO
EXEC sp_repladdcolumn N'Orders', N'IsSaved', 'bit', N'CareData'
[RUN]
Results:
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'CareData' because schema replication is performed.
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
The stored procedure will still be created.
Updated Values and Tested from Remote Client. Voila! Worked like a charm.
Why did you suggest a Temp column? Seemed superfluous to Copy from OldColumn
to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn
works great.
Aubrey Kelley
"Aubrey" wrote:
[vbcol=seagreen]
> Thanks, Hilary! and only 43 minutes after my updated post, Sunday early AM?
> That was what it looked like in your SS2K T&SR Book. Was not sure it applied
> to Anonymous Merge. Will try it Real-Soon-Now ...
> --
> Aubrey Kelley
>
> "Hilary Cotter" wrote:
|||I'm glad it worked.
The temp table solution works great if you are able to kick your users off
while you do it. If not and the table was huge, the temp table could take
come time to populate and will get progressively out of sync if people are
banging away at it. So you would loose consistency as you do it.
However, my approach will "break" the table as you are making the changes so
the app which is using this column will fail as you do your renaming.
So neither solutions are perfect - mine takes longer but might guarantee
better consistency than yours. Your method is shorter, but your method is
preferred if you can kick all of your users off your system.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Aubrey" <miscuates@.online.nospam> wrote in message
news:A9C71B7B-F1AF-4DAD-A202-6CF3DD2703FB@.microsoft.com...
> What I did: Opened Query Analyzer
> USE CareData
> GO
> EXEC sp_repladdcolumn N'Orders', N'IsSaved', 'bit', N'CareData'
> [RUN]
> Results:
> Warning: only Subscribers running SQL Server 2000 can synchronize with
> publication 'CareData' because schema replication is performed.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object
'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
> The stored procedure will still be created.
> Updated Values and Tested from Remote Client. Voila! Worked like a charm.
> Why did you suggest a Temp column? Seemed superfluous to Copy from
OldColumn[vbcol=seagreen]
> to Temp, then copy Temp to NewColumn, when OldColumn directly to NewColumn
> works great.
> --
> Aubrey Kelley
>
> "Aubrey" wrote:
AM?[vbcol=seagreen]
applied[vbcol=seagreen]
same[vbcol=seagreen]
column[vbcol=seagreen]
column[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
wish[vbcol=seagreen]
name[vbcol=seagreen]
temp[vbcol=seagreen]
to make[vbcol=seagreen]
named:[vbcol=seagreen]
SQL[vbcol=seagreen]
rename[vbcol=seagreen]
Column[vbcol=seagreen]
|||Correct; I was fortunate that ALL Users are Merge Clients, No ONE is directly
updating the Publisher Database.
Just to Test, I added a couple of Rows while all this was running, and they
were missing the Values in the New Columns. Fortunately I had WHERE Clauses
in the Data Copy Form, Event Procedures just in case the Project Locked Up or
Aborted.
Now, onward and upward ... Have a Great Week!
Aubrey Kelley
"Hilary Cotter" wrote:
> I'm glad it worked.
> The temp table solution works great if you are able to kick your users off
> while you do it. If not and the table was huge, the temp table could take
> come time to populate and will get progressively out of sync if people are
> banging away at it. So you would loose consistency as you do it.
> However, my approach will "break" the table as you are making the changes so
> the app which is using this column will fail as you do your renaming.
> So neither solutions are perfect - mine takes longer but might guarantee
> better consistency than yours. Your method is shorter, but your method is
> preferred if you can kick all of your users off your system.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Aubrey" <miscuates@.online.nospam> wrote in message
> news:A9C71B7B-F1AF-4DAD-A202-6CF3DD2703FB@.microsoft.com...
> 'sp_sel_B469A5B2466148AC3170B37FE7C24426_pal'.
> OldColumn
> AM?
> applied
> same
> column
> column
> the
> the
> wish
> name
> temp
> to make
> named:
> SQL
> rename
> Column
>
>
Removing the spaces left by hidden columns or rows
I have written a comprehensive report that is used as a subreport for many
others. The comprehensive report supports dynamic grouping and the dynamic
hiding of columns or rows.
My problem is that if a column is hidden, a huge gap is seen in the report
where the column is hidden.
A C D
+=====+ (hidden column) +=====+==========+
|xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
...
Is there any way to make column A move to live where the hidden column "B"
would have been had it not been hidden?
Thanks.
--
Jim Campbell
FujiFilm eSystemsYou have to put the colum width to 0 (in the properties window)
"Jim Campbell" wrote:
> I have written a comprehensive report that is used as a subreport for many
> others. The comprehensive report supports dynamic grouping and the dynamic
> hiding of columns or rows.
> My problem is that if a column is hidden, a huge gap is seen in the report
> where the column is hidden.
> A C D
> +=====+ (hidden column) +=====+==========+
> |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> ...
> Is there any way to make column A move to live where the hidden column "B"
> would have been had it not been hidden?
> Thanks.
> --
> Jim Campbell
> FujiFilm eSystems|||If only it were that simple... I need to set the width based on whether or
not the column is hidden. Yet, I read the post below:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
that seems to imply there is no way to do this under the current release.
Has anyone found a work around?
- Jim
--
Jim Campbell
FujiFilm eSystems
"Antoon" wrote:
> You have to put the colum width to 0 (in the properties window)
> "Jim Campbell" wrote:
> > I have written a comprehensive report that is used as a subreport for many
> > others. The comprehensive report supports dynamic grouping and the dynamic
> > hiding of columns or rows.
> >
> > My problem is that if a column is hidden, a huge gap is seen in the report
> > where the column is hidden.
> > A C D
> > +=====+ (hidden column) +=====+==========+
> > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> >
> > ...
> >
> > Is there any way to make column A move to live where the hidden column "B"
> > would have been had it not been hidden?
> >
> > Thanks.
> > --
> > Jim Campbell
> > FujiFilm eSystems|||You probably have a condition that you use to hide the column; try setting
the colum width to =iif(condition;0;20)
"Jim Campbell" wrote:
> If only it were that simple... I need to set the width based on whether or
> not the column is hidden. Yet, I read the post below:
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> that seems to imply there is no way to do this under the current release.
> Has anyone found a work around?
> - Jim
> --
> Jim Campbell
> FujiFilm eSystems
>
> "Antoon" wrote:
> > You have to put the colum width to 0 (in the properties window)
> >
> > "Jim Campbell" wrote:
> >
> > > I have written a comprehensive report that is used as a subreport for many
> > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > hiding of columns or rows.
> > >
> > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > where the column is hidden.
> > > A C D
> > > +=====+ (hidden column) +=====+==========+
> > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > >
> > > ...
> > >
> > > Is there any way to make column A move to live where the hidden column "B"
> > > would have been had it not been hidden?
> > >
> > > Thanks.
> > > --
> > > Jim Campbell
> > > FujiFilm eSystems|||But I am pretty sure that u can't write an expression in the width property.
U can just enter numeric values..
am I wrong ?
"Antoon" wrote:
> You probably have a condition that you use to hide the column; try setting
> the colum width to =iif(condition;0;20)
> "Jim Campbell" wrote:
> > If only it were that simple... I need to set the width based on whether or
> > not the column is hidden. Yet, I read the post below:
> >
> > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> >
> > that seems to imply there is no way to do this under the current release.
> > Has anyone found a work around?
> >
> > - Jim
> > --
> > Jim Campbell
> > FujiFilm eSystems
> >
> >
> > "Antoon" wrote:
> >
> > > You have to put the colum width to 0 (in the properties window)
> > >
> > > "Jim Campbell" wrote:
> > >
> > > > I have written a comprehensive report that is used as a subreport for many
> > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > hiding of columns or rows.
> > > >
> > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > where the column is hidden.
> > > > A C D
> > > > +=====+ (hidden column) +=====+==========+
> > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > >
> > > > ...
> > > >
> > > > Is there any way to make column A move to live where the hidden column "B"
> > > > would have been had it not been hidden?
> > > >
> > > > Thanks.
> > > > --
> > > > Jim Campbell
> > > > FujiFilm eSystems|||Yeah you're rigth, sorry about that.
Now I rember how to do it. Put the width value to zero and flag the
properties/"can increase to accomadate length" .
When visible the colomn width will be fine, when invisible it will
be...truly invisible
"Jerome" wrote:
> But I am pretty sure that u can't write an expression in the width property.
> U can just enter numeric values..
> am I wrong ?
> "Antoon" wrote:
> > You probably have a condition that you use to hide the column; try setting
> > the colum width to =iif(condition;0;20)
> >
> > "Jim Campbell" wrote:
> >
> > > If only it were that simple... I need to set the width based on whether or
> > > not the column is hidden. Yet, I read the post below:
> > >
> > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > >
> > > that seems to imply there is no way to do this under the current release.
> > > Has anyone found a work around?
> > >
> > > - Jim
> > > --
> > > Jim Campbell
> > > FujiFilm eSystems
> > >
> > >
> > > "Antoon" wrote:
> > >
> > > > You have to put the colum width to 0 (in the properties window)
> > > >
> > > > "Jim Campbell" wrote:
> > > >
> > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > hiding of columns or rows.
> > > > >
> > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > where the column is hidden.
> > > > > A C D
> > > > > +=====+ (hidden column) +=====+==========+
> > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > >
> > > > > ...
> > > > >
> > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > would have been had it not been hidden?
> > > > >
> > > > > Thanks.
> > > > > --
> > > > > Jim Campbell
> > > > > FujiFilm eSystems|||Hi,
Access to the Reporting Service site at Microsoft appears to be
hanging, so I'll reply using Google Groups...
Can you tell me where to find this 'can increase to accommodate length'
option? The only thing I see that is like this is "can increase to
accommodate height."
Can this width option be used if there is a textbox in the column? Can
a textbox widen to accommodate contents, too?
Thanks,
Jim Campbell
Antoon wrote:
> Yeah you're rigth, sorry about that.
> Now I rember how to do it. Put the width value to zero and flag the
> properties/"can increase to accomadate length" .
> When visible the colomn width will be fine, when invisible it will
> be...truly invisible
> "Jerome" wrote:
> > But I am pretty sure that u can't write an expression in the width property.
> > U can just enter numeric values..
> > am I wrong ?
> >
> > "Antoon" wrote:
> >
> > > You probably have a condition that you use to hide the column; try setting
> > > the colum width to =iif(condition;0;20)
> > >
> > > "Jim Campbell" wrote:
> > >
> > > > If only it were that simple... I need to set the width based on whether or
> > > > not the column is hidden. Yet, I read the post below:
> > > >
> > > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > > >
> > > > that seems to imply there is no way to do this under the current release.
> > > > Has anyone found a work around?
> > > >
> > > > - Jim
> > > > --
> > > > Jim Campbell
> > > > FujiFilm eSystems
> > > >
> > > >
> > > > "Antoon" wrote:
> > > >
> > > > > You have to put the colum width to 0 (in the properties window)
> > > > >
> > > > > "Jim Campbell" wrote:
> > > > >
> > > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > > hiding of columns or rows.
> > > > > >
> > > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > > where the column is hidden.
> > > > > > A C D
> > > > > > +=====+ (hidden column) +=====+==========+
> > > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > > >
> > > > > > ...
> > > > > >
> > > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > > would have been had it not been hidden?
> > > > > >
> > > > > > Thanks.
> > > > > > --
> > > > > > Jim Campbell
> > > > > > FujiFilm eSystems|||I did my own research here, and I see that the "CanGrow" feature of
textboxes applies only to height and not width in this particular
version of MS Reporting Services. I do not see any way to do what I am
asking.
To illustrate what I have today:
col1--col2--col3--data1--data2
X Y Z
where col1 may or may not show field X if the user groups by field X,
col2 may or may not show field Y if the user groups by field Y, etc...
This means that if, for example, the user chooses not to group by field
Y, the report will appear like this
col1 col3--data1--data2
X Z
I suppose that if I found a way to have true dynmaic grouping, e.g., by
having col3 group by X, Y, or Z, rather than have each column represent
one field toggled on or off with grouping, I could it make it all
better, but I would still have the problem of spaces to the left of the
report, representing all the non-grouped columns.
I guess we'll have to put this problem down to the limitations inherent
in this "1.0-version" piece of software.
- Jim
jtk6204@.gmail.com wrote:
> Hi,
> Access to the Reporting Service site at Microsoft appears to be
> hanging, so I'll reply using Google Groups...
> Can you tell me where to find this 'can increase to accommodate length'
> option? The only thing I see that is like this is "can increase to
> accommodate height."
> Can this width option be used if there is a textbox in the column? Can
> a textbox widen to accommodate contents, too?
> Thanks,
> Jim Campbell
> Antoon wrote:
> > Yeah you're rigth, sorry about that.
> > Now I rember how to do it. Put the width value to zero and flag the
> > properties/"can increase to accomadate length" .
> > When visible the colomn width will be fine, when invisible it will
> > be...truly invisible
> >
> > "Jerome" wrote:
> >
> > > But I am pretty sure that u can't write an expression in the width property.
> > > U can just enter numeric values..
> > > am I wrong ?
> > >
> > > "Antoon" wrote:
> > >
> > > > You probably have a condition that you use to hide the column; try setting
> > > > the colum width to =iif(condition;0;20)
> > > >
> > > > "Jim Campbell" wrote:
> > > >
> > > > > If only it were that simple... I need to set the width based on whether or
> > > > > not the column is hidden. Yet, I read the post below:
> > > > >
> > > > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > > > >
> > > > > that seems to imply there is no way to do this under the current release.
> > > > > Has anyone found a work around?
> > > > >
> > > > > - Jim
> > > > > --
> > > > > Jim Campbell
> > > > > FujiFilm eSystems
> > > > >
> > > > >
> > > > > "Antoon" wrote:
> > > > >
> > > > > > You have to put the colum width to 0 (in the properties window)
> > > > > >
> > > > > > "Jim Campbell" wrote:
> > > > > >
> > > > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > > > hiding of columns or rows.
> > > > > > >
> > > > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > > > where the column is hidden.
> > > > > > > A C D
> > > > > > > +=====+ (hidden column) +=====+==========+
> > > > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > > > >
> > > > > > > ...
> > > > > > >
> > > > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > > > would have been had it not been hidden?
> > > > > > >
> > > > > > > Thanks.
> > > > > > > --
> > > > > > > Jim Campbell
> > > > > > > FujiFilm eSystems
others. The comprehensive report supports dynamic grouping and the dynamic
hiding of columns or rows.
My problem is that if a column is hidden, a huge gap is seen in the report
where the column is hidden.
A C D
+=====+ (hidden column) +=====+==========+
|xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
...
Is there any way to make column A move to live where the hidden column "B"
would have been had it not been hidden?
Thanks.
--
Jim Campbell
FujiFilm eSystemsYou have to put the colum width to 0 (in the properties window)
"Jim Campbell" wrote:
> I have written a comprehensive report that is used as a subreport for many
> others. The comprehensive report supports dynamic grouping and the dynamic
> hiding of columns or rows.
> My problem is that if a column is hidden, a huge gap is seen in the report
> where the column is hidden.
> A C D
> +=====+ (hidden column) +=====+==========+
> |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> ...
> Is there any way to make column A move to live where the hidden column "B"
> would have been had it not been hidden?
> Thanks.
> --
> Jim Campbell
> FujiFilm eSystems|||If only it were that simple... I need to set the width based on whether or
not the column is hidden. Yet, I read the post below:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
that seems to imply there is no way to do this under the current release.
Has anyone found a work around?
- Jim
--
Jim Campbell
FujiFilm eSystems
"Antoon" wrote:
> You have to put the colum width to 0 (in the properties window)
> "Jim Campbell" wrote:
> > I have written a comprehensive report that is used as a subreport for many
> > others. The comprehensive report supports dynamic grouping and the dynamic
> > hiding of columns or rows.
> >
> > My problem is that if a column is hidden, a huge gap is seen in the report
> > where the column is hidden.
> > A C D
> > +=====+ (hidden column) +=====+==========+
> > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> >
> > ...
> >
> > Is there any way to make column A move to live where the hidden column "B"
> > would have been had it not been hidden?
> >
> > Thanks.
> > --
> > Jim Campbell
> > FujiFilm eSystems|||You probably have a condition that you use to hide the column; try setting
the colum width to =iif(condition;0;20)
"Jim Campbell" wrote:
> If only it were that simple... I need to set the width based on whether or
> not the column is hidden. Yet, I read the post below:
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> that seems to imply there is no way to do this under the current release.
> Has anyone found a work around?
> - Jim
> --
> Jim Campbell
> FujiFilm eSystems
>
> "Antoon" wrote:
> > You have to put the colum width to 0 (in the properties window)
> >
> > "Jim Campbell" wrote:
> >
> > > I have written a comprehensive report that is used as a subreport for many
> > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > hiding of columns or rows.
> > >
> > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > where the column is hidden.
> > > A C D
> > > +=====+ (hidden column) +=====+==========+
> > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > >
> > > ...
> > >
> > > Is there any way to make column A move to live where the hidden column "B"
> > > would have been had it not been hidden?
> > >
> > > Thanks.
> > > --
> > > Jim Campbell
> > > FujiFilm eSystems|||But I am pretty sure that u can't write an expression in the width property.
U can just enter numeric values..
am I wrong ?
"Antoon" wrote:
> You probably have a condition that you use to hide the column; try setting
> the colum width to =iif(condition;0;20)
> "Jim Campbell" wrote:
> > If only it were that simple... I need to set the width based on whether or
> > not the column is hidden. Yet, I read the post below:
> >
> > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> >
> > that seems to imply there is no way to do this under the current release.
> > Has anyone found a work around?
> >
> > - Jim
> > --
> > Jim Campbell
> > FujiFilm eSystems
> >
> >
> > "Antoon" wrote:
> >
> > > You have to put the colum width to 0 (in the properties window)
> > >
> > > "Jim Campbell" wrote:
> > >
> > > > I have written a comprehensive report that is used as a subreport for many
> > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > hiding of columns or rows.
> > > >
> > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > where the column is hidden.
> > > > A C D
> > > > +=====+ (hidden column) +=====+==========+
> > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > >
> > > > ...
> > > >
> > > > Is there any way to make column A move to live where the hidden column "B"
> > > > would have been had it not been hidden?
> > > >
> > > > Thanks.
> > > > --
> > > > Jim Campbell
> > > > FujiFilm eSystems|||Yeah you're rigth, sorry about that.
Now I rember how to do it. Put the width value to zero and flag the
properties/"can increase to accomadate length" .
When visible the colomn width will be fine, when invisible it will
be...truly invisible
"Jerome" wrote:
> But I am pretty sure that u can't write an expression in the width property.
> U can just enter numeric values..
> am I wrong ?
> "Antoon" wrote:
> > You probably have a condition that you use to hide the column; try setting
> > the colum width to =iif(condition;0;20)
> >
> > "Jim Campbell" wrote:
> >
> > > If only it were that simple... I need to set the width based on whether or
> > > not the column is hidden. Yet, I read the post below:
> > >
> > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > >
> > > that seems to imply there is no way to do this under the current release.
> > > Has anyone found a work around?
> > >
> > > - Jim
> > > --
> > > Jim Campbell
> > > FujiFilm eSystems
> > >
> > >
> > > "Antoon" wrote:
> > >
> > > > You have to put the colum width to 0 (in the properties window)
> > > >
> > > > "Jim Campbell" wrote:
> > > >
> > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > hiding of columns or rows.
> > > > >
> > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > where the column is hidden.
> > > > > A C D
> > > > > +=====+ (hidden column) +=====+==========+
> > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > >
> > > > > ...
> > > > >
> > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > would have been had it not been hidden?
> > > > >
> > > > > Thanks.
> > > > > --
> > > > > Jim Campbell
> > > > > FujiFilm eSystems|||Hi,
Access to the Reporting Service site at Microsoft appears to be
hanging, so I'll reply using Google Groups...
Can you tell me where to find this 'can increase to accommodate length'
option? The only thing I see that is like this is "can increase to
accommodate height."
Can this width option be used if there is a textbox in the column? Can
a textbox widen to accommodate contents, too?
Thanks,
Jim Campbell
Antoon wrote:
> Yeah you're rigth, sorry about that.
> Now I rember how to do it. Put the width value to zero and flag the
> properties/"can increase to accomadate length" .
> When visible the colomn width will be fine, when invisible it will
> be...truly invisible
> "Jerome" wrote:
> > But I am pretty sure that u can't write an expression in the width property.
> > U can just enter numeric values..
> > am I wrong ?
> >
> > "Antoon" wrote:
> >
> > > You probably have a condition that you use to hide the column; try setting
> > > the colum width to =iif(condition;0;20)
> > >
> > > "Jim Campbell" wrote:
> > >
> > > > If only it were that simple... I need to set the width based on whether or
> > > > not the column is hidden. Yet, I read the post below:
> > > >
> > > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > > >
> > > > that seems to imply there is no way to do this under the current release.
> > > > Has anyone found a work around?
> > > >
> > > > - Jim
> > > > --
> > > > Jim Campbell
> > > > FujiFilm eSystems
> > > >
> > > >
> > > > "Antoon" wrote:
> > > >
> > > > > You have to put the colum width to 0 (in the properties window)
> > > > >
> > > > > "Jim Campbell" wrote:
> > > > >
> > > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > > hiding of columns or rows.
> > > > > >
> > > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > > where the column is hidden.
> > > > > > A C D
> > > > > > +=====+ (hidden column) +=====+==========+
> > > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > > >
> > > > > > ...
> > > > > >
> > > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > > would have been had it not been hidden?
> > > > > >
> > > > > > Thanks.
> > > > > > --
> > > > > > Jim Campbell
> > > > > > FujiFilm eSystems|||I did my own research here, and I see that the "CanGrow" feature of
textboxes applies only to height and not width in this particular
version of MS Reporting Services. I do not see any way to do what I am
asking.
To illustrate what I have today:
col1--col2--col3--data1--data2
X Y Z
where col1 may or may not show field X if the user groups by field X,
col2 may or may not show field Y if the user groups by field Y, etc...
This means that if, for example, the user chooses not to group by field
Y, the report will appear like this
col1 col3--data1--data2
X Z
I suppose that if I found a way to have true dynmaic grouping, e.g., by
having col3 group by X, Y, or Z, rather than have each column represent
one field toggled on or off with grouping, I could it make it all
better, but I would still have the problem of spaces to the left of the
report, representing all the non-grouped columns.
I guess we'll have to put this problem down to the limitations inherent
in this "1.0-version" piece of software.
- Jim
jtk6204@.gmail.com wrote:
> Hi,
> Access to the Reporting Service site at Microsoft appears to be
> hanging, so I'll reply using Google Groups...
> Can you tell me where to find this 'can increase to accommodate length'
> option? The only thing I see that is like this is "can increase to
> accommodate height."
> Can this width option be used if there is a textbox in the column? Can
> a textbox widen to accommodate contents, too?
> Thanks,
> Jim Campbell
> Antoon wrote:
> > Yeah you're rigth, sorry about that.
> > Now I rember how to do it. Put the width value to zero and flag the
> > properties/"can increase to accomadate length" .
> > When visible the colomn width will be fine, when invisible it will
> > be...truly invisible
> >
> > "Jerome" wrote:
> >
> > > But I am pretty sure that u can't write an expression in the width property.
> > > U can just enter numeric values..
> > > am I wrong ?
> > >
> > > "Antoon" wrote:
> > >
> > > > You probably have a condition that you use to hide the column; try setting
> > > > the colum width to =iif(condition;0;20)
> > > >
> > > > "Jim Campbell" wrote:
> > > >
> > > > > If only it were that simple... I need to set the width based on whether or
> > > > > not the column is hidden. Yet, I read the post below:
> > > > >
> > > > > http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/24eee624eae87b62/4e72f1c1abed9fcf?q=column+width&rnum=2#4e72f1c1abed9fcf
> > > > >
> > > > > that seems to imply there is no way to do this under the current release.
> > > > > Has anyone found a work around?
> > > > >
> > > > > - Jim
> > > > > --
> > > > > Jim Campbell
> > > > > FujiFilm eSystems
> > > > >
> > > > >
> > > > > "Antoon" wrote:
> > > > >
> > > > > > You have to put the colum width to 0 (in the properties window)
> > > > > >
> > > > > > "Jim Campbell" wrote:
> > > > > >
> > > > > > > I have written a comprehensive report that is used as a subreport for many
> > > > > > > others. The comprehensive report supports dynamic grouping and the dynamic
> > > > > > > hiding of columns or rows.
> > > > > > >
> > > > > > > My problem is that if a column is hidden, a huge gap is seen in the report
> > > > > > > where the column is hidden.
> > > > > > > A C D
> > > > > > > +=====+ (hidden column) +=====+==========+
> > > > > > > |xxxxxxxx| |xxxxxxxx|xxxxxxxxxxxxxxx|
> > > > > > >
> > > > > > > ...
> > > > > > >
> > > > > > > Is there any way to make column A move to live where the hidden column "B"
> > > > > > > would have been had it not been hidden?
> > > > > > >
> > > > > > > Thanks.
> > > > > > > --
> > > > > > > Jim Campbell
> > > > > > > FujiFilm eSystems
Friday, March 9, 2012
Removing msrepl_trans columns from db
Hello,
Is there a way to remove the msrepl_tran columns from
tables that have been marked for replication?
I've a backup taken from a replicated system that I want
to remove all the replicated tables and columns from. I
can go through and delete the msrepl_tran columns and
constraint from the tables manually but it is very time
consuming to do 100 tables.
Can any one help?
Thanks in advance.
Enterprise Manager is your friend. Right click on your tables and remove
away.
You might also try to run a script like this.
select 'alter table '+ object_name(parent_obj) +' drop constraint '+name+
char(13)+' go'
From sysobjects where name like '%msrepl%' and type ='D' and
object_name(parent_obj)
<>'MSreplication_subscriptions'
GO
select 'alter table '+object_name(ID) +' drop column msrepl_tran_version '+
char(13) +'GO' from syscolumns where name like 'msrepl_tran_version%' and
object_name(ID) not like 'conflict%'
GO
Copy what appears in the results pane and then paste it in the execution
window and run it.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Harvey" <anonymous@.discussions.microsoft.com> wrote in message
news:26d901c4dde2$9c58f700$a301280a@.phx.gbl...
> Hello,
> Is there a way to remove the msrepl_tran columns from
> tables that have been marked for replication?
> I've a backup taken from a replicated system that I want
> to remove all the replicated tables and columns from. I
> can go through and delete the msrepl_tran columns and
> constraint from the tables manually but it is very time
> consuming to do 100 tables.
> Can any one help?
> Thanks in advance.
|||Thanks for the replys. I have over 100 tables and
dropping one at a time would take way to long.
I'll use the cursor method and see how I get on. Thanks
again
>--Original Message--
>Enterprise Manager is your friend. Right click on your
tables and remove
>away.
>You might also try to run a script like this.
>select 'alter table '+ object_name(parent_obj) +' drop
constraint '+name+
>char(13)+' go'
>From sysobjects where name like '%msrepl%' and type ='D'
and
>object_name(parent_obj)
><>'MSreplication_subscriptions'
>GO
>select 'alter table '+object_name(ID) +' drop column
msrepl_tran_version '+
>char(13) +'GO' from syscolumns where name
like 'msrepl_tran_version%' and
>object_name(ID) not like 'conflict%'
>GO
>Copy what appears in the results pane and then paste it
in the execution
>window and run it.
>
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>Now available for purchase at:
>http://www.nwsu.com/0974973602.html
>"Harvey" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:26d901c4dde2$9c58f700$a301280a@.phx.gbl...
want[vbcol=seagreen]
I
>
>.
>
Is there a way to remove the msrepl_tran columns from
tables that have been marked for replication?
I've a backup taken from a replicated system that I want
to remove all the replicated tables and columns from. I
can go through and delete the msrepl_tran columns and
constraint from the tables manually but it is very time
consuming to do 100 tables.
Can any one help?
Thanks in advance.
Enterprise Manager is your friend. Right click on your tables and remove
away.
You might also try to run a script like this.
select 'alter table '+ object_name(parent_obj) +' drop constraint '+name+
char(13)+' go'
From sysobjects where name like '%msrepl%' and type ='D' and
object_name(parent_obj)
<>'MSreplication_subscriptions'
GO
select 'alter table '+object_name(ID) +' drop column msrepl_tran_version '+
char(13) +'GO' from syscolumns where name like 'msrepl_tran_version%' and
object_name(ID) not like 'conflict%'
GO
Copy what appears in the results pane and then paste it in the execution
window and run it.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Harvey" <anonymous@.discussions.microsoft.com> wrote in message
news:26d901c4dde2$9c58f700$a301280a@.phx.gbl...
> Hello,
> Is there a way to remove the msrepl_tran columns from
> tables that have been marked for replication?
> I've a backup taken from a replicated system that I want
> to remove all the replicated tables and columns from. I
> can go through and delete the msrepl_tran columns and
> constraint from the tables manually but it is very time
> consuming to do 100 tables.
> Can any one help?
> Thanks in advance.
|||Thanks for the replys. I have over 100 tables and
dropping one at a time would take way to long.
I'll use the cursor method and see how I get on. Thanks
again
>--Original Message--
>Enterprise Manager is your friend. Right click on your
tables and remove
>away.
>You might also try to run a script like this.
>select 'alter table '+ object_name(parent_obj) +' drop
constraint '+name+
>char(13)+' go'
>From sysobjects where name like '%msrepl%' and type ='D'
and
>object_name(parent_obj)
><>'MSreplication_subscriptions'
>GO
>select 'alter table '+object_name(ID) +' drop column
msrepl_tran_version '+
>char(13) +'GO' from syscolumns where name
like 'msrepl_tran_version%' and
>object_name(ID) not like 'conflict%'
>GO
>Copy what appears in the results pane and then paste it
in the execution
>window and run it.
>
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>Now available for purchase at:
>http://www.nwsu.com/0974973602.html
>"Harvey" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:26d901c4dde2$9c58f700$a301280a@.phx.gbl...
want[vbcol=seagreen]
I
>
>.
>
Wednesday, March 7, 2012
Removing Identity Property
Folks,
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra SharmaYou cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
>> Folks,
>> I am trying to alter my database thru SQL Scripts. I have a few tables
> where
>> I have columns having Identity Property. I now want to modify these
> columns
>> having no Identity Property. But I can't find SQL Statement that can do
>> this.
>> Can some one help?
>> Regards
>> Shailaindra Sharma
>>
>|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Why can't we do things like that which we can easily do in access. How hard
> can it be for ms to add these things which will make life so easy.
> Regards
>|||You mean no one has found it to be a problem as yet? :)
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
>> Why can't we do things like that which we can easily do in access. How
>> hard can it be for ms to add these things which will make life so easy.
>> Regards
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra SharmaYou cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
>> Folks,
>> I am trying to alter my database thru SQL Scripts. I have a few tables
> where
>> I have columns having Identity Property. I now want to modify these
> columns
>> having no Identity Property. But I can't find SQL Statement that can do
>> this.
>> Can some one help?
>> Regards
>> Shailaindra Sharma
>>
>|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Why can't we do things like that which we can easily do in access. How hard
> can it be for ms to add these things which will make life so easy.
> Regards
>|||You mean no one has found it to be a problem as yet? :)
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
>> Why can't we do things like that which we can easily do in access. How
>> hard can it be for ms to add these things which will make life so easy.
>> Regards
Removing Identity Property
Folks,
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra Sharma
You cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>
|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> where
> columns
>
|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Why can't we do things like that which we can easily do in access. How hard
> can it be for ms to add these things which will make life so easy.
> Regards
>
|||You mean no one has found it to be a problem as yet?
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra Sharma
You cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>
|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> where
> columns
>
|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Why can't we do things like that which we can easily do in access. How hard
> can it be for ms to add these things which will make life so easy.
> Regards
>
|||You mean no one has found it to be a problem as yet?
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
Removing Identity Property
Folks,
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra SharmaYou cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> where
> columns
>|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx
.gbl...
> Why can't we do things like that which we can easily do in access. How har
d
> can it be for ms to add these things which will make life so easy.
> Regards
>|||You mean no one has found it to be a problem as yet?
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra SharmaYou cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> where
> columns
>|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx
.gbl...
> Why can't we do things like that which we can easily do in access. How har
d
> can it be for ms to add these things which will make life so easy.
> Regards
>|||You mean no one has found it to be a problem as yet?
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
removing duplicates
I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie YaegerAh - you need a "tiebreaker". Basically another column that you can
include in your selectivity criteria to make the rows unique. For
example, if it's feasible to assume that the same acctnum/invnum is
associated with only a single address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you
one of the dups. MIN() will give you a different dup. TOP 1 would
arbitrarily give you any 1 of the dups. It may be necessary to include
all the columns in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you
want more info. Itzik Ben-Gan has recently written about them in SQLMag
(http://www.windowsitpro.com/Article...5235/45235.html) if
you're a SQL Mag subscriber. But I'm sure a google search and/or a
google groups search would return many hits regarding tiebreakers in SQL
queries.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Bernie Yaeger wrote:
>I have a table with 15 columns. Among these are 2 text columns - acctnum
>and invnum. If any rows are the same, I want them removed. Thus,
>acctnum invnum cprice amtpaid address
>1234 33688 5.99 12.97 22 Hope Street
>9876 33688 4.22 1.97 27 Hope Street
>1234 33688 14.69 4.36 26 Hope Street
>5407 33688 3.47 8.89 25 Hope Street
>1234 00921 8.11 1107 24 Hope Street
>2970 33688 10.05 125.97 23 Hope Street
>Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
>3 should remain, but not both (if doesn't matter which is removed). If I
>select distinct and use the 'select...into' clause, I don't get all the
>other columns of information.
>Can someone help me with this?
>Thanks for any help.
>Bernie Yaeger
>
>|||Hi,
You can do this by setting rowcount. Try the following code:
SET ROWCOUNT 1
SELECT a.* FROM [tablename] a, [tablename] b
WHERE a.acctnum = b.acctnum
AND a.invnum = b.invnum
Now, although there are two different records (row 1 and row 3), the query
will return only one row.
Finally do this,
SET ROWCOUNT 0
"Mike Hodgson" wrote:
> Ah - you need a "tiebreaker". Basically another column that you can
> include in your selectivity criteria to make the rows unique. For
> example, if it's feasible to assume that the same acctnum/invnum is
> associated with only a single address then you could say
> select acctnum, invnum, cprice, amtpaid, address
> from mytable as a
> where address =
> (select max(b.address) from mytable as b
> where a.acctnum = b.acctnum
> and a.invnum = b.invnum)
> Any subquery that returns one of the dups would do. MAX() will give you
> one of the dups. MIN() will give you a different dup. TOP 1 would
> arbitrarily give you any 1 of the dups. It may be necessary to include
> all the columns in the select list to get a single acctnum/invnum result.
> There has been quite a bit written on tie-breakers on the web if you
> want more info. Itzik Ben-Gan has recently written about them in SQLMag
> (http://www.windowsitpro.com/Article...5235/45235.html) if
> you're a SQL Mag subscriber. But I'm sure a google search and/or a
> google groups search would return many hits regarding tiebreakers in SQL
> queries.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> Bernie Yaeger wrote:
>
>|||Hi Bernie Yaeger,
I am not suggesting that following method is correct, but it surely
works.
Step 1 :- Create a table with select statement having identity column
select identity (1,1) myid,* into myTable from BaseTable
Step 2 :- Delete whatever rows you wish
delete from MyTable where myid in ( whatever)
Step 3 :- Remove the Identity column
alter table taa drop column myid
Step 4 :- Remove all records from Base Table
truncate BaseTable
Step 5:- Insert all records from MYTable
insert into BaseTable select * from MyTable
With regards
Jatinder|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column (ide
ntity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u33
7rUlaFHA.1472@.TK2MSFTNGP10.phx.gbl...
Ah - you need a "tiebreaker". Basically another column that you can include
in your selectivity criteria to make the rows unique. For example, if it's
feasible to assume that the same acctnum/invnum is associated with only a s
ingle address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you one
of the dups. MIN() will give you a different dup. TOP 1 would arbitrarily
give you any 1 of the dups. It may be necessary to include all the columns
in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you want more info. I
tzik Ben-Gan has recently written about them in SQLMag (http://www.windowsitpro.co
m...5235/45235.html) if you're a SQL Mag subscriber. But I
'm sure a google search and/or a google groups search would return many hits
regarding tiebreakers in SQL queries.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Bernie Yaeger wrote:
I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie Yaeger|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column
(identity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Deeraj" <Deeraj@.discussions.microsoft.com> wrote in message
news:BD5EE2B3-E11D-4713-AA5F-F2D38433FF61@.microsoft.com...
> Hi,
> You can do this by setting rowcount. Try the following code:
> SET ROWCOUNT 1
> SELECT a.* FROM [tablename] a, [tablename] b
> WHERE a.acctnum = b.acctnum
> AND a.invnum = b.invnum
> Now, although there are two different records (row 1 and row 3), the query
> will return only one row.
> Finally do this,
> SET ROWCOUNT 0
> "Mike Hodgson" wrote:
>|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column (ide
ntity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u33
7rUlaFHA.1472@.TK2MSFTNGP10.phx.gbl...
Ah - you need a "tiebreaker". Basically another column that you can include
in your selectivity criteria to make the rows unique. For example, if it's
feasible to assume that the same acctnum/invnum is associated with only a s
ingle address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you one
of the dups. MIN() will give you a different dup. TOP 1 would arbitrarily
give you any 1 of the dups. It may be necessary to include all the columns
in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you want more info. I
tzik Ben-Gan has recently written about them in SQLMag (http://www.windowsitpro.co
m...5235/45235.html) if you're a SQL Mag subscriber. But I
'm sure a google search and/or a google groups search would return many hits
regarding tiebreakers in SQL queries.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Bernie Yaeger wrote:
I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie Yaeger
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie YaegerAh - you need a "tiebreaker". Basically another column that you can
include in your selectivity criteria to make the rows unique. For
example, if it's feasible to assume that the same acctnum/invnum is
associated with only a single address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you
one of the dups. MIN() will give you a different dup. TOP 1 would
arbitrarily give you any 1 of the dups. It may be necessary to include
all the columns in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you
want more info. Itzik Ben-Gan has recently written about them in SQLMag
(http://www.windowsitpro.com/Article...5235/45235.html) if
you're a SQL Mag subscriber. But I'm sure a google search and/or a
google groups search would return many hits regarding tiebreakers in SQL
queries.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Bernie Yaeger wrote:
>I have a table with 15 columns. Among these are 2 text columns - acctnum
>and invnum. If any rows are the same, I want them removed. Thus,
>acctnum invnum cprice amtpaid address
>1234 33688 5.99 12.97 22 Hope Street
>9876 33688 4.22 1.97 27 Hope Street
>1234 33688 14.69 4.36 26 Hope Street
>5407 33688 3.47 8.89 25 Hope Street
>1234 00921 8.11 1107 24 Hope Street
>2970 33688 10.05 125.97 23 Hope Street
>Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
>3 should remain, but not both (if doesn't matter which is removed). If I
>select distinct and use the 'select...into' clause, I don't get all the
>other columns of information.
>Can someone help me with this?
>Thanks for any help.
>Bernie Yaeger
>
>|||Hi,
You can do this by setting rowcount. Try the following code:
SET ROWCOUNT 1
SELECT a.* FROM [tablename] a, [tablename] b
WHERE a.acctnum = b.acctnum
AND a.invnum = b.invnum
Now, although there are two different records (row 1 and row 3), the query
will return only one row.
Finally do this,
SET ROWCOUNT 0
"Mike Hodgson" wrote:
> Ah - you need a "tiebreaker". Basically another column that you can
> include in your selectivity criteria to make the rows unique. For
> example, if it's feasible to assume that the same acctnum/invnum is
> associated with only a single address then you could say
> select acctnum, invnum, cprice, amtpaid, address
> from mytable as a
> where address =
> (select max(b.address) from mytable as b
> where a.acctnum = b.acctnum
> and a.invnum = b.invnum)
> Any subquery that returns one of the dups would do. MAX() will give you
> one of the dups. MIN() will give you a different dup. TOP 1 would
> arbitrarily give you any 1 of the dups. It may be necessary to include
> all the columns in the select list to get a single acctnum/invnum result.
> There has been quite a bit written on tie-breakers on the web if you
> want more info. Itzik Ben-Gan has recently written about them in SQLMag
> (http://www.windowsitpro.com/Article...5235/45235.html) if
> you're a SQL Mag subscriber. But I'm sure a google search and/or a
> google groups search would return many hits regarding tiebreakers in SQL
> queries.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> Bernie Yaeger wrote:
>
>|||Hi Bernie Yaeger,
I am not suggesting that following method is correct, but it surely
works.
Step 1 :- Create a table with select statement having identity column
select identity (1,1) myid,* into myTable from BaseTable
Step 2 :- Delete whatever rows you wish
delete from MyTable where myid in ( whatever)
Step 3 :- Remove the Identity column
alter table taa drop column myid
Step 4 :- Remove all records from Base Table
truncate BaseTable
Step 5:- Insert all records from MYTable
insert into BaseTable select * from MyTable
With regards
Jatinder|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column (ide
ntity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u33
7rUlaFHA.1472@.TK2MSFTNGP10.phx.gbl...
Ah - you need a "tiebreaker". Basically another column that you can include
in your selectivity criteria to make the rows unique. For example, if it's
feasible to assume that the same acctnum/invnum is associated with only a s
ingle address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you one
of the dups. MIN() will give you a different dup. TOP 1 would arbitrarily
give you any 1 of the dups. It may be necessary to include all the columns
in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you want more info. I
tzik Ben-Gan has recently written about them in SQLMag (http://www.windowsitpro.co
m...5235/45235.html) if you're a SQL Mag subscriber. But I
'm sure a google search and/or a google groups search would return many hits
regarding tiebreakers in SQL queries.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Bernie Yaeger wrote:
I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie Yaeger|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column
(identity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Deeraj" <Deeraj@.discussions.microsoft.com> wrote in message
news:BD5EE2B3-E11D-4713-AA5F-F2D38433FF61@.microsoft.com...
> Hi,
> You can do this by setting rowcount. Try the following code:
> SET ROWCOUNT 1
> SELECT a.* FROM [tablename] a, [tablename] b
> WHERE a.acctnum = b.acctnum
> AND a.invnum = b.invnum
> Now, although there are two different records (row 1 and row 3), the query
> will return only one row.
> Finally do this,
> SET ROWCOUNT 0
> "Mike Hodgson" wrote:
>|||Hi MIke, Deeraj, Jatinder,
Tx for all of your help. I was able to get it done with a rowid column (ide
ntity), as follows:
delete d1 from invdetbal d1 join invdetbal d2 on
d2.imcacct = d1.imcacct and d2.invnum = d1.invnum
and d1.rowid > d2.rowid
Thanks again for everyone's help.
Bernie
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u33
7rUlaFHA.1472@.TK2MSFTNGP10.phx.gbl...
Ah - you need a "tiebreaker". Basically another column that you can include
in your selectivity criteria to make the rows unique. For example, if it's
feasible to assume that the same acctnum/invnum is associated with only a s
ingle address then you could say
select acctnum, invnum, cprice, amtpaid, address
from mytable as a
where address =
(select max(b.address) from mytable as b
where a.acctnum = b.acctnum
and a.invnum = b.invnum)
Any subquery that returns one of the dups would do. MAX() will give you one
of the dups. MIN() will give you a different dup. TOP 1 would arbitrarily
give you any 1 of the dups. It may be necessary to include all the columns
in the select list to get a single acctnum/invnum result.
There has been quite a bit written on tie-breakers on the web if you want more info. I
tzik Ben-Gan has recently written about them in SQLMag (http://www.windowsitpro.co
m...5235/45235.html) if you're a SQL Mag subscriber. But I
'm sure a google search and/or a google groups search would return many hits
regarding tiebreakers in SQL queries.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Bernie Yaeger wrote:
I have a table with 15 columns. Among these are 2 text columns - acctnum
and invnum. If any rows are the same, I want them removed. Thus,
acctnum invnum cprice amtpaid address
1234 33688 5.99 12.97 22 Hope Street
9876 33688 4.22 1.97 27 Hope Street
1234 33688 14.69 4.36 26 Hope Street
5407 33688 3.47 8.89 25 Hope Street
1234 00921 8.11 1107 24 Hope Street
2970 33688 10.05 125.97 23 Hope Street
Of the above 6 rows, all should remain except rows 1 and 3, and either 1 or
3 should remain, but not both (if doesn't matter which is removed). If I
select distinct and use the 'select...into' clause, I don't get all the
other columns of information.
Can someone help me with this?
Thanks for any help.
Bernie Yaeger
removing duplicated rows
Hi,
Need to remove the duplicated rows from a table which has text/ntext/image type columns. The table does not have any PK/Unique column. (I accept its a bad data model). But currently changing the data model is not possible. Hence doing changes in application.
I couldn't do 'SELECT DISTINCT * from table', since the table has text columns. Though there is no PK constraint, If I know that col1 and col2 are join PKs in the table, Is that possible to select the distinct rows from such a table.
Please advise,
Thanks,
Smithahttp://www.aspfaq.com/2431
Need to remove the duplicated rows from a table which has text/ntext/image type columns. The table does not have any PK/Unique column. (I accept its a bad data model). But currently changing the data model is not possible. Hence doing changes in application.
I couldn't do 'SELECT DISTINCT * from table', since the table has text columns. Though there is no PK constraint, If I know that col1 and col2 are join PKs in the table, Is that possible to select the distinct rows from such a table.
Please advise,
Thanks,
Smithahttp://www.aspfaq.com/2431
news:e3d0a7da-ac87-46f8-a9c5-008fbcb4cd8d@.discussions.microsoft.com... > Hi, > > Need to remove the duplicated rows from a table which has > text/ntext/image type columns. The table does not have any PK/Unique > column. (I accept its a bad data model). But currently changing the data > model is not possible. Hence doing changes in application. > > I couldn't do 'SELECT DISTINCT * from table', since the table has text > columns. Though there is no PK constraint, If I know that col1 and col2 > are join PKs in the table, Is that possible to select the distinct rows > from such a table. > > Please advise, > > Thanks, > Smitha > >
removing duplicated records
Hi,
Need to remove the duplicated rows from a table which has text/ntext/image type columns. The table does not have any PK/Unique column. (I accept its a bad data model). But currently changing the data model is not possible. Hence doing changes in application.
I couldn't do 'SELECT DISTINCT * from table', since the table has text columns. Though there is no PK constraint, If I know that col1 and col2 are join PKs in the table, Is that possible to select the distinct rows from such a table.
Please advise,
Thanks,
MiraJwhy dont u post the DDL of table,some describtion abt table|||maybe you could use group by and use max() function to exclude duplicates
select col1, col2, max(text_col3), max(text_col4)...
from t
group by col1, col2...|||why dont u post the DDL of table,some describtion abt table
Table :
create table test(col1 int, col2 int, col3 text, col4 image)|||it is quiet difficult to do, there are some options,
insert all records into temp table ,add unique column before transfering data to temp table
1.If data values are less
than 8000 characters, you can cast the values to VARCHAR(8000) and use DISTINCT or
GROUP by to get the unique row.
If data values are greater than
8000
characters you may have to use a cursor & loop through
the rows & delete the
rows. In some cases you may have to use
an identity column or a number table
( with a cross join
operation ) to easily distingush one row from another.
----------
This link may help u http://www.windowsitpro.com/SQLServer/Article/ArticleID/23234/23234.html
Need to remove the duplicated rows from a table which has text/ntext/image type columns. The table does not have any PK/Unique column. (I accept its a bad data model). But currently changing the data model is not possible. Hence doing changes in application.
I couldn't do 'SELECT DISTINCT * from table', since the table has text columns. Though there is no PK constraint, If I know that col1 and col2 are join PKs in the table, Is that possible to select the distinct rows from such a table.
Please advise,
Thanks,
MiraJwhy dont u post the DDL of table,some describtion abt table|||maybe you could use group by and use max() function to exclude duplicates
select col1, col2, max(text_col3), max(text_col4)...
from t
group by col1, col2...|||why dont u post the DDL of table,some describtion abt table
Table :
create table test(col1 int, col2 int, col3 text, col4 image)|||it is quiet difficult to do, there are some options,
insert all records into temp table ,add unique column before transfering data to temp table
1.If data values are less
than 8000 characters, you can cast the values to VARCHAR(8000) and use DISTINCT or
GROUP by to get the unique row.
If data values are greater than
8000
characters you may have to use a cursor & loop through
the rows & delete the
rows. In some cases you may have to use
an identity column or a number table
( with a cross join
operation ) to easily distingush one row from another.
----------
This link may help u http://www.windowsitpro.com/SQLServer/Article/ArticleID/23234/23234.html
Removing duplicate rows
I need to remove duplicate rows that are not exactly
identical in all columns. Only if a row has three columns
that are identical to another, I want to remove it. In my
example I'm looking for Account_Number, Check_Number,
Check_Amount. Is there a Query that can do this?
LeeWithout seeing the DDL it's impossible to give a precise answer. If you have
a single-column primary key (Keycol in this example) and don't mind which of
the duplicate rows get removed:
DELETE FROM Sometable
WHERE keycol NOT IN
(SELECT MIN(keycol)
FROM Sometable
GROUP BY account_number, check_number, check_amount)
--
David Portas
--
Please reply only to the newsgroup
--
"Lee Surma" <lee@.honeycomb.net> wrote in message
news:018101c36b3c$f66dd9f0$a601280a@.phx.gbl...
> I need to remove duplicate rows that are not exactly
> identical in all columns. Only if a row has three columns
> that are identical to another, I want to remove it. In my
> example I'm looking for Account_Number, Check_Number,
> Check_Amount. Is there a Query that can do this?
> Lee
>
>|||This might help you:
http://www.sql-server-
performance.com/rd_delete_duplicates.asp
How big is the table?
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I need to remove duplicate rows that are not exactly
>identical in all columns. Only if a row has three columns
>that are identical to another, I want to remove it. In my
>example I'm looking for Account_Number, Check_Number,
>Check_Amount. Is there a Query that can do this?
>Lee
>
>
>.
>|||More, this one from Microsoft:
http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/q1
39/4/44.asp&NoWebContent=1
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I need to remove duplicate rows that are not exactly
>identical in all columns. Only if a row has three columns
>that are identical to another, I want to remove it. In my
>example I'm looking for Account_Number, Check_Number,
>Check_Amount. Is there a Query that can do this?
>Lee
>
>
>.
>
identical in all columns. Only if a row has three columns
that are identical to another, I want to remove it. In my
example I'm looking for Account_Number, Check_Number,
Check_Amount. Is there a Query that can do this?
LeeWithout seeing the DDL it's impossible to give a precise answer. If you have
a single-column primary key (Keycol in this example) and don't mind which of
the duplicate rows get removed:
DELETE FROM Sometable
WHERE keycol NOT IN
(SELECT MIN(keycol)
FROM Sometable
GROUP BY account_number, check_number, check_amount)
--
David Portas
--
Please reply only to the newsgroup
--
"Lee Surma" <lee@.honeycomb.net> wrote in message
news:018101c36b3c$f66dd9f0$a601280a@.phx.gbl...
> I need to remove duplicate rows that are not exactly
> identical in all columns. Only if a row has three columns
> that are identical to another, I want to remove it. In my
> example I'm looking for Account_Number, Check_Number,
> Check_Amount. Is there a Query that can do this?
> Lee
>
>|||This might help you:
http://www.sql-server-
performance.com/rd_delete_duplicates.asp
How big is the table?
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I need to remove duplicate rows that are not exactly
>identical in all columns. Only if a row has three columns
>that are identical to another, I want to remove it. In my
>example I'm looking for Account_Number, Check_Number,
>Check_Amount. Is there a Query that can do this?
>Lee
>
>
>.
>|||More, this one from Microsoft:
http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/q1
39/4/44.asp&NoWebContent=1
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I need to remove duplicate rows that are not exactly
>identical in all columns. Only if a row has three columns
>that are identical to another, I want to remove it. In my
>example I'm looking for Account_Number, Check_Number,
>Check_Amount. Is there a Query that can do this?
>Lee
>
>
>.
>
Saturday, February 25, 2012
removing constraints without knowing the constraint name
I have the need to remove a constraint on a table since I'm trying to alter
the datatype of one of the columns. I know I can drop the constraint given
the name, but since the name is auto generated (something like
DF__WHRPT_ITV__Expor__45F365D3)
I need a way to find this constraint name so that I can programmatically
remove it.I can get the name using sp_helpconstraint on the table, but can't
seem to locate where the actual constraint_name is stored.
the datatype of one of the columns. I know I can drop the constraint given
the name, but since the name is auto generated (something like
DF__WHRPT_ITV__Expor__45F365D3)
I need a way to find this constraint name so that I can programmatically
remove it.I can get the name using sp_helpconstraint on the table, but can't
seem to locate where the actual constraint_name is stored.
This is on SQL 2000.
Any help is appreciated.
Thanks,
-GaryYou can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
--
Jacco Schalkwijk
SQL Server MVP
"Gary" <gld@.hotmail.com> wrote in message
news:EIJmd.349072$wV.15308@.attbi_s54...
>I have the need to remove a constraint on a table since I'm trying to alter
> the datatype of one of the columns. I know I can drop the constraint given
> the name, but since the name is auto generated (something like
> DF__WHRPT_ITV__Expor__45F365D3)
> I need a way to find this constraint name so that I can programmatically
> remove it.I can get the name using sp_helpconstraint on the table, but
> can't
> seem to locate where the actual constraint_name is stored.
> This is on SQL 2000.
> Any help is appreciated.
> Thanks,
> -Gary|||Thanks. That works perfectly.
-Gary
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uXQ32kLzEHA.1392@.TK2MSFTNGP14.phx.gbl...
> You can get rid of Defaults with automatically named constraints in a
script
> with the following bit of code. Just replace the <table name> and <column
> names> with your table and column(s):
> DECLARE @.constraint_name SYSNAME
> -- remove all the defaults
> WHILE 1=1
> BEGIN
> SET @.constraint_name = (SELECT TOP 1 c_obj.name
> FROM sysobjects t_obj
> INNER JOIN sysobjects c_obj
> ON t_obj.id = c_obj.parent_obj
> INNER JOIN syscolumns cols
> ON cols.colid = c_obj.info
> AND cols.id = c_obj.parent_obj
> WHERE t_obj.id = OBJECT_ID('<table name>')
> AND c_obj.xtype = 'D'
> AND cols.[name]IN ('<column names>'))
> IF @.constraint_name IS NULL BREAK
> EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
> END
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Gary" <gld@.hotmail.com> wrote in message
> news:EIJmd.349072$wV.15308@.attbi_s54...
> >I have the need to remove a constraint on a table since I'm trying to
alter
> > the datatype of one of the columns. I know I can drop the constraint
given
> > the name, but since the name is auto generated (something like
> > DF__WHRPT_ITV__Expor__45F365D3)
> > I need a way to find this constraint name so that I can programmatically
> > remove it.I can get the name using sp_helpconstraint on the table, but
> > can't
> > seem to locate where the actual constraint_name is stored.
> > This is on SQL 2000.
> > Any help is appreciated.
> > Thanks,
> > -Gary
Removing Columns from a data flow
Seems obvious but I can't see how. How would I remove columns from a data flow so that columns which have been used earlier but are not needed for insert/update are taken out of the flow.
I'm asking because the data ends up in a update statement and the flow has got so big it is unreadable.
Cheers, Al
You cannot remove a column from the buffer, the best method is to not create it to start with. Obviously starting from your source, only bring through the columns you need. Any asynchronous components, e.g. Aggregates, Sorts, Joins, can also be used to carefully select only the required columns. A new buffer is allocated for the output in the case of an asynchronous component, so the column selection can be done there. When the component is synchronous, changing the buffer structure would be a problem, as this would mean messing the what is designed to be a fast and efficient data transfer method, and messing with it during it's lifetime would presumably cost more than just carrying the data along. It would however be nice to hide columns at a meta-data level. Perhaps add it as a request in MSDN Feedback.
|||Is there a way to remove columns in the UNION ALL component? My understanding is that it is an asynchronous component but I have not found a way to remove unneeded columns.
thanks
Peter
|||Once you have added the component, and added the inputs, you can open the UI for the Uion All. Now click on a row (for the column) to select it, then right-click and Delete. The column is now gone.Removing Columns from a data flow
Seems obvious but I can't see how. How would I remove columns from a data flow so that columns which have been used earlier but are not needed for insert/update are taken out of the flow.
I'm asking because the data ends up in a update statement and the flow has got so big it is unreadable.
Cheers, Al
You cannot remove a column from the buffer, the best method is to not create it to start with. Obviously starting from your source, only bring through the columns you need. Any asynchronous components, e.g. Aggregates, Sorts, Joins, can also be used to carefully select only the required columns. A new buffer is allocated for the output in the case of an asynchronous component, so the column selection can be done there. When the component is synchronous, changing the buffer structure would be a problem, as this would mean messing the what is designed to be a fast and efficient data transfer method, and messing with it during it's lifetime would presumably cost more than just carrying the data along. It would however be nice to hide columns at a meta-data level. Perhaps add it as a request in MSDN Feedback.
|||Is there a way to remove columns in the UNION ALL component? My understanding is that it is an asynchronous component but I have not found a way to remove unneeded columns.
thanks
Peter
|||Once you have added the component, and added the inputs, you can open the UI for the Uion All. Now click on a row (for the column) to select it, then right-click and Delete. The column is now gone.Removing Columns and/or Rows on Export
I have a Report I need to hide subtotals and total and include detail only when I am exporting to excel. So, is there a way to capture that it is exporting to Excel? Possibly a way to capture the rs:Format=Excel?
Hello,
I don't think you'll be able to do this, but you could always create a report parameter that would hold the RenderMethod. Then, you can change the formatting of your report based on this value to display how you want.
Jarret
|||True, but I was hoping I might be able to accomplish it without having to have a user go though the step of chosing, since they are already choosing to export to excel.|||YOu can′t change the output based on a different rendering format, as already mentioned you would have to make a parametrized report, discarding some fields in the case of a specific parameters OR design another report which has the wanted output format.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
That was what I was affraid of, just wanted more experienced minds to confirm it for me.
Thanks for the help.
Subscribe to:
Posts (Atom)