hot backup: alter

Showing posts with label alter. Show all posts

Wednesday, March 21, 2012

How can I alter the parameters pane design?

Hi all,
I was wondering if it's possible to somehow change the parameters pane
design. My problem is that some of the reports we write are in right-
to-left languages (hebrew, arabic). We can change the whole report so
it's right-to-left, but the parameters stick to the left.
Is there any way I can change this?
Thanks in advance,
R. GreenI don't believe there is a good way to do it.
you can write your own parameter controls and just interface RS with
SOAP calls.
people who need to use asp 1.1(no report viewer control) have had to
do this for instance.|||Hi Tom,
Thanks for your prompt answer.
I didn't think there's a way to do it too, until I saw Reporting
Services 2000 and the parameters *were* right-to-left when viewing a
hebrew report.
Perhaps I can somehow use CSS to change the parameters pane design?
On Jul 3, 10:16 pm, tomk...@.gmail.com wrote:
> I don't believe there is a good way to do it.
> you can write your own parameter controls and just interface RS with
> SOAP calls.
> people who need to use asp 1.1(no report viewer control) have had to
> do this for instance.sql

How can I alter an int column to IDENTITY

Hi!
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre AmentNo, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament

How can I alter an int column to IDENTITY

Hi!
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre Ament
No, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament

How can I alter an int column to IDENTITY

Hi!
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre AmentNo, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
--
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament

How can I alter a table turning ON or OFF an IDENTITY field ?

How can I alter a table turning ON or OFF anIDENTITY field ?

for example:
if I had my DB with Client_ID as an I IDENTITY field and for some reason it has
changed to just INT (with no IDENTITY) - how can I tell it to be IDENTITY field again ?

Does anyone knows an article on database planning ?
(I wanna know when should I use the IDENTITY field)In Enterprise Manager go to that table right click Design, highlight that column below there should be setting for Identity set to yes.

or in Query analizer I usually do this for tables that do have DO have identity insert on and I need to force a particular id

SET IDENTITY_INSERT [dbo].[TestTable] ON -- turns auto increment off

INSERT INTO TestTable (TestTabelID, Title)
VALUES (34, 'Test')

SET IDENTITY_INSERT [dbo].[TestTable] OFF -- turns auto increment back on

if it is not set to auto increment you may have to run an alter statement

-- untested code
ALTER TABLE [dbo].[TestTable]
ALTER COLUMN [TestTableID] [int] IDENTITY (1, 1) NOT NULL

seems odd that you say it has changed?|||You cannot use Alter table/column to add/remove identity property of a column. EM actually bulk copy data out to a temp table, delete the old one and then rename it any time you tinker with identity property.

--
-oj
http://rac4sql.net|||You could use:

SET IDENTITY_INSERT MyTable ON

--Do your stuff

SET IDENTITY_INSERT MyTable OFF|||Thank U all,
but what will happen to the UNIQUE numbers after I set IDENTITY ON back again ?

for example - if I had:

IDENTITY (1,1) ON
Client_ID 1 linked to Order_ID 1 linked to more...
Client_ID 2 linked to Order_ID 2 linked to more...
Client_ID 3 linked to Order_ID 3 linked to more...
...

and then (when transfering to other DB or for other reasons)
IDENTITY is set to OFF and Client_ID 2 is deleted:

Client_ID 1 linked to Order_ID 1 linked to more...
Client_ID 3 linked to Order_ID 2 linked to more...
...

so when I set up IDENTITY (1,1) turned ON again for the new data what will happen ?
will SQL Server will use the numbers on the table or just give the whole column
NEW numbers from 1.. to the last record...
(when will SQL Server remember all used unique numbers and when it will not)

Any articles on this issue ?
(use SQL server auto numbers or create my ownflexible unique numbers system with "locking" records)|||but what will happen to the UNIQUE numbers after I set IDENTITY ON back again ?

I believe nothing.
if you have three records with the values 5, 6, and 7 for the identity (CLIENT_ID) they will remain as is and any new records inserted to that table will start at 8.|||Thank U all.

Friday, March 9, 2012

How Alter text column length?

How can I change the length of a Data Type text column? All I've found in BOL
is that you can't do it!
Thanks -Can you be more specific? Text can hold up to 2GB data, and you do not specify that length, as you
do with varchar.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
news:691FEB50-310C-4DB5-91B2-D28822BA252B@.microsoft.com...
> How can I change the length of a Data Type text column? All I've found in BOL
> is that you can't do it!
> Thanks -|||Tibor, Looks like Bil needs to see the entire contents of text column while
querying (SELECT).
Bil,
See the command SET TEXTSIZE in books online.
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oq2JDiSmFHA.2860@.TK2MSFTNGP15.phx.gbl...
> Can you be more specific? Text can hold up to 2GB data, and you do not
> specify that length, as you do with varchar.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
> news:691FEB50-310C-4DB5-91B2-D28822BA252B@.microsoft.com...
>> How can I change the length of a Data Type text column? All I've found in
>> BOL
>> is that you can't do it!
>> Thanks -
>|||The column was created with Length = 16. I am trying to store a text stream
which is being truncated at aprox 88 characters. Sample:
 *** TOP OF REPORT *** NORTH CAROLINA DIVISION OF MOTOR VEHICLES 
I don't get the correlation between 16 and 88, if I am counting those chars
correctly.
OBJECTPROPERTY TableTextInRowLimit = 0 indicates text in row option is not
set.
- Bil Click
"Tibor Karaszi" wrote:
> Can you be more specific? Text can hold up to 2GB data, and you do not specify that length, as you
> do with varchar.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
> news:691FEB50-310C-4DB5-91B2-D28822BA252B@.microsoft.com...
> > How can I change the length of a Data Type text column? All I've found in BOL
> > is that you can't do it!
> >
> > Thanks -
>|||The length16 is just Enterprise Manager's rather stupid way to tell you that the pointer used in the
internal handling of the text datatype is 16 bytes. It has nothing to do with what you can actually
store (which is 2GB, nothing that you configure in any way). The truncation either happens at input
or output of the data. Try checking the length using the DATALENGTH function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
news:57F1B4D3-9B8B-43F4-BDB9-F5D2057AEFD2@.microsoft.com...
> The column was created with Length = 16. I am trying to store a text stream
> which is being truncated at aprox 88 characters. Sample:
> *** TOP OF REPORT *** NORTH CAROLINA DIVISION OF MOTOR VEHICLES 
>
> I don't get the correlation between 16 and 88, if I am counting those chars
> correctly.
> OBJECTPROPERTY TableTextInRowLimit = 0 indicates text in row option is not
> set.
> - Bil Click
>
> "Tibor Karaszi" wrote:
>> Can you be more specific? Text can hold up to 2GB data, and you do not specify that length, as
>> you
>> do with varchar.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
>> news:691FEB50-310C-4DB5-91B2-D28822BA252B@.microsoft.com...
>> > How can I change the length of a Data Type text column? All I've found in BOL
>> > is that you can't do it!
>> >
>> > Thanks -
>>|||Turns out to be a Query Analyzer "gotcha", truncated the displayed field.
When I used the Datalength function I could see there really was more data
than being displayed in QA. EM only says <Long Text>. I finally opeded up
osql and see that the full text is being stored.
Thanks for the tips! Cheers!
- Bil
"Tibor Karaszi" wrote:
> The length16 is just Enterprise Manager's rather stupid way to tell you that the pointer used in the
> internal handling of the text datatype is 16 bytes. It has nothing to do with what you can actually
> store (which is 2GB, nothing that you configure in any way). The truncation either happens at input
> or output of the data. Try checking the length using the DATALENGTH function.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
> news:57F1B4D3-9B8B-43F4-BDB9-F5D2057AEFD2@.microsoft.com...
> > The column was created with Length = 16. I am trying to store a text stream
> > which is being truncated at aprox 88 characters. Sample:
> >
> > *** TOP OF REPORT *** NORTH CAROLINA DIVISION OF MOTOR VEHICLES 
> >
> >
> > I don't get the correlation between 16 and 88, if I am counting those chars
> > correctly.
> >
> > OBJECTPROPERTY TableTextInRowLimit = 0 indicates text in row option is not
> > set.
> >
> > - Bil Click
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Can you be more specific? Text can hold up to 2GB data, and you do not specify that length, as
> >> you
> >> do with varchar.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Bil Click" <BilClick@.discussions.microsoft.com> wrote in message
> >> news:691FEB50-310C-4DB5-91B2-D28822BA252B@.microsoft.com...
> >> > How can I change the length of a Data Type text column? All I've found in BOL
> >> > is that you can't do it!
> >> >
> >> > Thanks -
> >>
> >>
>

How Alter column name of table?

Hi all,
I need modify one column name of my table.
How I can do this?
ThanksHi ReTF
Please always specify what version you are using.
In SQL Server 2005, the following example renames the TerritoryID column in
the SalesTerritory table to TerrID.
USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO
The same syntax works in SQL Server 7 and 2000, but there is no
AdventureWorks database to try it on.
The key point when using the sp_rename procedure to rename a column is that
the first parameter includes both the table name and column name and can
also include the schema or owner name, and the second parameter is only the
new column name.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:OgTprj8DGHA.336@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I need modify one column name of my table.
> How I can do this?
> Thanks
>|||You can use something like this:
EXEC sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'
However, you should keep in mind that:
"Renaming an object such as a table or column will not automatically
rename references to that object. You must modify any objects that
reference the renamed object manually. For example, if you rename a
table column and that column is referenced in a trigger, you must
modify the trigger to reflect the new column name."
(quote from Books Online, topic "sp_rename")
Razvan

Wednesday, March 21, 2012

How can I alter the parameters pane design?

How can I alter an int column to IDENTITY

How can I alter an int column to IDENTITY

How can I alter an int column to IDENTITY

How can I alter a table turning ON or OFF an IDENTITY field ?

Friday, March 9, 2012

How Alter text column length?

How Alter text column length?

How Alter text column length?

How Alter column name of table?

hot backup

Blog Archive

About Me