alter text column

Hi

I had a text type not null column which i wanted to change to a null column.Writing a simple alter statement gave me an eror cannot change text type column so i tried to rename the original column create a new column with the same name and allowing nulls on it and then copying the contents of the renamed column to the new column and finally deleting the renamed column.

EXEC sp_rename 'TableName.ColumnName', 'ColumnName_old', 'COLUMN'

ALTER TABLE TableName ADD ColumnName text NULL
UPDATE TableName SET ColumnName = ColumnName_old
ALTER TABLE TableName DROP COLUMN ColumnName_old

However when i tried to execute these statements in query analyser on the Update statement it gave me the error that ColumnName_old does not exist.

However then I tried to execute these queries one by one I was able to do that.

Can anybody tell me whats causing the queries to not be executed all at once without giving the ColumnName_old does not exist error cause I wanted to run them on live dbs.

any help would be appreciated.

Himani

Your logic should work with a little change. Add a GO between each batch.

Like:

EXEC sp_rename 'TableName.ColumnName', 'ColumnName_old', 'COLUMN'

GO

ALTER TABLE TableName ADD ColumnName text NULL

GO
UPDATE TableName SET ColumnName = ColumnName_old

GO
ALTER TABLE TableName DROP COLUMN ColumnName_old

GO

After each DML statement executed, you should get what you want.

By the way, it seems you can change the colum with text datatype from not null to allow null directly from the table definition in SQL 2005 Management Studio.

Also, you can directly do the update like this:

UPDATE yourTable

Set yourNewcolumTextAllowNull=youroldcolumnnotAllowNull

HTH

|||

Thanks a lot limno,it worked !!!

:-)

sql

Alter table

I have a table which has a field with datatype text , now i want to change this field to varchar.

How i can do that.

Thanks in advance...alter table mytable alter column mycol varchar(50)

Alter of text type field

Can I change the datatype for a particular field which was previously set to text?If possible then how?Secondly can I change the datatype of a filed of int type to identity after inserting data?Can I remove identity property from a field?USE Northwind
GO

CREATE TABLE myTable98 (Col1 int, Col2 text)
GO

INSERT INTO myTable98 (Col1, Col2)
SELECT 1, REPLICATE('X',8001) UNION ALL
SELECT 2, 'Hi! How the hell are you' UNION ALL
SELECT 3, 'X'
GO

ALTER TABLE myTable98 ALTER Column Col2 varchar(8000)
GO
-- No Good
ALTER TABLE myTable98 ADD Col3 varchar(8000)
GO

UPDATE myTable98 SET Col3 = Col2

SELECT Col1, LEN(Col3), Col3 FROM myTable98

ALTER TABLE myTable98 DROP Column Col2
GO

SELECT * FROM myTable98
GO

DROP TABLE myTable98
GO

Look up ALTER in Books Online for more....

ALTER data type

I have created a table from a text file delimited by comma's. The date within the text file is YYYYMMDD and needs to be stored within a smalldatetime column. Unfortunatley whenever I import the data it errors out. If I import the data into a nvarchar column, it imports correctly and then allows me to change the datatype to smalldatetime(thus giving me the correct format). Is there a way I could run a command pre and post to the import?

ALTER TABLE PO MODIFY x_column nvarchar

IMPORT DATA

ALTER TABLE PO MODIFY x_column smalldatetime

hope this is clear enough, thanks for the helpalter table tblname alter column colname smalldatetime

You would be safer importing to a staging table then inserting from there.|||thanks for the quick response, worked like a charm

Alter Columns?

Hello,

I am trying to edit several tables that were imported in tab-delimited format from text files. I am trying to generate a script that will alter the data type for several different columns.

I have succesfully edited a single column with the following code:
USE THCIC
ALTER TABLE PudfTest
ALTER COLUMN
DISCHARGE VARCHAR(6) NULL

However, I have need to create a script that will change the data type for over 100 columns. So far, everything I've read tells me that multiple 'alter column' statements cannot be run in a single query. I'm hoping someone can shed some light on this, or at least point me in another direction so that I won't have to manually change the data type for every column in each of the tables.

Any help would be greatly appreciated.
Thanks!Look into this one and elaborate as needed:

select 'alter table ' + table_name + ' alter column ' + column_name + ' ' +
case data_type
when 'int' then 'varchar(25)'
when 'datetime' then 'char(10)'
else data_type
end + ' null'
from information_schema.columns

ALTER COLUMN on a text or ntext field

Hi,
I'd like to run the following command:
ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
but it falls over because the current [purpose] column is 'text'
I can change it through design view in Enterprise manager, after clicking ok
on the warning message, but I need to find a way to override this error in
Script.
Is there a way I can overide the fact that it is a text column and change it
to varchar?
Thanks in advance!
PaulEXEC sp_rename 'cal_respurpose.purpose', 'purpose_old', 'COLUMN'
ALTER TABLE cal_respurpose ADD purpose VARCHAR(255)
UPDATE cal_respurpose SET purpose = SUBSTRING(purpose_old, 1, 255)
ALTER TABLE cal_respurpose DROP COLUMN purpose_old
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message
news:e0ESFPsvFHA.708@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'd like to run the following command:
> ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
> but it falls over because the current [purpose] column is 'text'
> I can change it through design view in Enterprise manager, after clicking
> ok on the warning message, but I need to find a way to override this error
> in Script.
> Is there a way I can overide the fact that it is a text column and change
> it to varchar?
> Thanks in advance!
> Paul
>|||When faced with situations like this, it might be helpful for you to
know that you can save the change script (third icon on standard
toolbar) from the Enterprise Manager which will show you how the change
is implemented by the EM. Granted, the method that is implemented is
usually not how I would do it, but it's helpful in a pinch.
In this case, I created and saved a table with a single text column,
and then changed it to a varchar column; this is the script EM used to
implement the change:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_test1
(
test varchar(50) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.test1)
EXEC('INSERT INTO dbo.Tmp_test1 (test)
SELECT CONVERT(varchar(50), test) FROM dbo.test1 TABLOCKX')
GO
DROP TABLE dbo.test1
GO
EXECUTE sp_rename N'dbo.Tmp_test1', N'test1', 'OBJECT'
GO
COMMIT
HTH,
Stu|||Thanks Guys!
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message
news:e0ESFPsvFHA.708@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'd like to run the following command:
> ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
> but it falls over because the current [purpose] column is 'text'
> I can change it through design view in Enterprise manager, after clicking
> ok on the warning message, but I need to find a way to override this error
> in Script.
> Is there a way I can overide the fact that it is a text column and change
> it to varchar?
> Thanks in advance!
> Paul
>

ALTER COLUMN on a text or ntext field

Hi,
I'd like to run the following command:
ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
but it falls over because the current [purpose] column is 'text': -
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'purpose' because it is 'text'.
I can change it through design view in Enterprise manager, after clicking ok on the warning message, but I need to find a way to override this error in Script.
Is there a way I can overide the fact that it is a text column and change it to varchar?
Thanks in advance!
Paul
Got answer from Aaron Bertrand [SQL Server MVP] on another newsgroup.
EXEC sp_rename 'cal_respurpose.purpose', 'purpose_old', 'COLUMN'
ALTER TABLE cal_respurpose ADD purpose VARCHAR(255)
UPDATE cal_respurpose SET purpose = SUBSTRING(purpose_old, 1, 255)
ALTER TABLE cal_respurpose DROP COLUMN purpose_old
Cheers!
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message news:%23J%23SJCsvFHA.1996@.TK2MSFTNGP10.phx.gbl...
Hi,
I'd like to run the following command:
ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
but it falls over because the current [purpose] column is 'text': -
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'purpose' because it is 'text'.
I can change it through design view in Enterprise manager, after clicking ok on the warning message, but I need to find a way to override this error in Script.
Is there a way I can overide the fact that it is a text column and change it to varchar?
Thanks in advance!
Paul
|||Cool, but this code will add the column to the end of the table, the column "purpose" will be the last one in "select * from cal_respurpose". this could be risky if the software or the store procedures performs an insert based on the columns indices.
so what i would suggest is to copy the table to a new table ( with new stucture ) after backing up the table and renaming the new one to the original table name.
I don't know if there is a way to preserve the columns order.
Faris

Quote:

Originally Posted by Paul B

Got answer from Aaron Bertrand [SQL Server MVP] on another newsgroup.
EXEC sp_rename 'cal_respurpose.purpose', 'purpose_old', 'COLUMN'
ALTER TABLE cal_respurpose ADD purpose VARCHAR(255)
UPDATE cal_respurpose SET purpose = SUBSTRING(purpose_old, 1, 255)
ALTER TABLE cal_respurpose DROP COLUMN purpose_old
Cheers!
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message news:%23J%23SJCsvFHA.1996@.TK2MSFTNGP10.phx.gbl...
Hi,
I'd like to run the following command:
ALTER TABLE cal_respurpose ALTER COLUMN [purpose] varchar(255)
but it falls over because the current [purpose] column is 'text': -
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'purpose' because it is 'text'.
I can change it through design view in Enterprise manager, after clicking ok on the warning message, but I need to find a way to override this error in Script.
Is there a way I can overide the fact that it is a text column and change it to varchar?
Thanks in advance!
Paul

Alter Column datatype in SMO

I m trying to alter some columns' datatype in the existing database.

it seems in SMO, some datatype like Text, throw exceptions "Cos it's Text".

so how do I code it in SMO for this problem?

I read there are some TSQL solutions to create a temp table for this.

but can SMO has a way to impletment it?

best regards

Hi,

from which type to which type to you want to switch ? The common approach would be:

Server s = newServer(".");

Table t = s.Databases["Northwind"].Tables["SomeTable"];

t.Columns["ColA"].DataType = DataType.Int;

t.Alter();

HTH, Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

|||

the problem I have is to alter Text Field to NText.

the exception is alter column x failed cos it's Text field.

all other datatypes seem straightforward.

if you have SMO solutions for this issue, I will be really appreciated.

thanks for the reply

|||

Did you ever find a solution for this? I ran into the same problem yesterday. I can convert nearly all datatypes, except text <> ntext.

I could understand it, if I was trying to convert from ntext to text, but thats not the scenario - it is text to ntext.

Alter Column datatype in SMO

I m trying to alter some columns' datatype in the existing database.

it seems in SMO, some datatype like Text, throw exceptions "Cos it's Text".

so how do I code it in SMO for this problem?

I read there are some TSQL solutions to create a temp table for this.

but can SMO has a way to impletment it?

best regards

Hi,

from which type to which type to you want to switch ? The common approach would be:

Server s = new Server(".");

Table t = s.Databases["Northwind"].Tables["SomeTable"];

t.Columns["ColA"].DataType = DataType.Int;

t.Alter();

HTH, Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

|||

the problem I have is to alter Text Field to NText.

the exception is alter column x failed cos it's Text field.

all other datatypes seem straightforward.

if you have SMO solutions for this issue, I will be really appreciated.

thanks for the reply

|||

Did you ever find a solution for this? I ran into the same problem yesterday. I can convert nearly all datatypes, except text <> ntext.

I could understand it, if I was trying to convert from ntext to text, but thats not the scenario - it is text to ntext.

Alter Add - Before Text Datatype

I am constantly updating tables in my database with new fields, a lot of tables have a field with text datatype as the last field in the table.

It's very time consuming to run a script that renames the table, creates a new table with the new field before the text field, and insert into new table using select from renamed table. (SQL BELOW)

execute sp_rename CUSTDEF, CUSTDEF_1030A
GO

CREATE TABLE [dbo].[CUSTDEF] (
[CustDef1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDef5] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustDefNEW] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTES] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE INDEX [CUSTDEF_ONE] ON [dbo].[CUSTDEF]([CUSTDEF1], [CUSTDEF2]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

INSERT INTO CUSTDEF (CustDef1, CustDef2, CustDef3, CustDef4, CustDef5)
SELECT CustDef1, CustDef2, CustDef3, CustDef4, CustDef5
FROM CUSTDEF_1030A
GO

What I would like to do is to be able to have sql where I can use an ALTER ADD to add in CustDefNEW before the text field. Is there any way that I can do this, and save time more time than doing an insert/select against 50,000 records.

Thanks alot!The order of columns in a database has no bearing on the perforance. Is your background DB2? It used to be that way for varchars..

And why do you have text columns? How big is the data?

Bigger than 8000 bytes?

And no, ALter Add does manage the order of the columns (at least as far as I understand).

You can do it in EM...I think it'll do all that work for you behind the scenes...

I'm just not too keen about doing work there...see some weird things...

Good Luck

Another idea might be to use a view which looks like what you want...|||Brett,

I think the order can make a difference if you have, say, a long varchar field before the values on which you are searching. The server would have to determine the length of the data for the varchar in each row in order to calculate the offset of any data after it. If you know something that contradicts this, let me know.

In any case, MHawkins19, the TEXT datatype is not even stored in your rowset. All that is stored is a fixed length pointer to the location where the TEXT data is stored. Therefore, it make little or no difference what order your columns are in.

blindman

almost done but stuck

Ok I have created a 2005 sql advanced database with text indexing. I have create the database like so

created a new database with text indexing enabled and the following table

createtable support
(problemIdVARCHAR(50)NOTNULLPRIMARYKEY,
problemTitlevarchar(50)NOTNULL,
problemBodytextNOTNULL,
linkOnevarchar(50),
linkTwovarchar(50),
linkThreevarchar(50),
linkFourvarchar(50),ftid int NOT NULL)

next

createfulltextcatalog remoteSupportCatalog

createuniqueindex ui_remotesupportON support(ftid)

then

createfulltextindexon support(problemBody)keyindex PK__support__7C8480AEon remoteSupportCatalog

---

I then populated some rows and issues a quesry

Select * from support where freetext(problemBody, 'test database')

it works pulls back all the data I expected it to pull back

In my asp page I created a database connection with the folling select command

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE FREETEXT(problemBody, @.srchBox)">

created the search parameter

<SelectParameters>

<asp:ControlParameterControlID="srchBox"PropertyName="Text"Type="String"Name="srchBox"/> //this is a text box that is searchable with a button

</SelectParameters>

and it doesnt give me back an error or data it does nothing. What am I missing???

here is the whole asp page

<%@.PageLanguage="C#" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

</script>

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<bodybgcolor="#e4e4e4">

<formid="form1"runat="server">

<div>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE freetext(problemBody, @.srchBox ) ">

<SelectParameters>

<asp:ParameterName="srchBox"/>

</SelectParameters>

</asp:SqlDataSource>

<tablestyle="z-index: 100; left: 107px; position: absolute; top: 144px; width: 695px; height: 443px;"bgcolor="#000000">

<tr>

<tdstyle="width: 475px; height: 125px">

<asp:DetailsViewID="DetailsView1"runat="server"AllowPaging="True"AutoGenerateRows="False"

CellPadding="4"DataKeyNames="problemId"DataSourceID="SqlDataSource1"ForeColor="#333333"

GridLines="None"Height="52px"Width="679px">

<FooterStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<CommandRowStyleBackColor="#C5BBAF"Font-Bold="True"/>

<EditRowStyleBackColor="#7C6F57"/>

<RowStyleBackColor="#E3EAEB"/>

<PagerStyleBackColor="#666666"ForeColor="White"HorizontalAlign="Center"/>

<Fields>

<asp:BoundFieldDataField="problemId"HeaderText="Id:"ReadOnly="True"SortExpression="problemId">

<ItemStyleWidth="600px"BorderColor="White"BorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemTitle"HeaderText="Description:"SortExpression="problemTitle">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemBody"HeaderText="Resolution:"SortExpression="problemBody">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkOne"HeaderText="Links:"SortExpression="linkOne">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkTwo"HeaderText="linkTwo"SortExpression="linkTwo"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkThree"HeaderText="linkThree"SortExpression="linkThree"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkFour"HeaderText="linkFour"SortExpression="linkFour"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

</Fields>

<FieldHeaderStyleBackColor="#D0D0D0"Font-Bold="True"/>

<HeaderStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<AlternatingRowStyleBackColor="White"/>

</asp:DetailsView>

<asp:LabelID="Label1"runat="server"Font-Bold="True"ForeColor="White"Style="z-index: 103;

left: 13px; position: absolute; top: 59px"Width="521px"></asp:Label>

<asp:TextBoxID="srchBox"runat="server"Style="z-index: 101; left: 11px; position: absolute;

top: 31px"Width="520px"></asp:TextBox>

<asp:ButtonID="Button1"runat="server"Style="z-index: 102; left: 555px; position: absolute;

top: 31px"Text="Search It"/>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"></asp:SqlDataSource>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

|||

here is the whole asp page

<%@.PageLanguage="C#" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<scriptrunat="server">

</script> <htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title> </head>

<bodybgcolor="#e4e4e4">

<formid="form1"runat="server">

<div>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE freetext(problemBody, @.srchBox ) ">

<SelectParameters>

<asp:ParameterName="srchBox"/>

</SelectParameters>

</asp:SqlDataSource>

<tablestyle="z-index: 100; left: 107px; position: absolute; top: 144px; width: 695px; height: 443px;"bgcolor="#000000">

<tr>

<tdstyle="width: 475px; height: 125px">

<asp:DetailsViewID="DetailsView1"runat="server"AllowPaging="True"AutoGenerateRows="False"

CellPadding="4"DataKeyNames="problemId"DataSourceID="SqlDataSource1"ForeColor="#333333"

GridLines="None"Height="52px"Width="679px">

<FooterStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<CommandRowStyleBackColor="#C5BBAF"Font-Bold="True"/>

<EditRowStyleBackColor="#7C6F57"/>

<RowStyleBackColor="#E3EAEB"/>

<PagerStyleBackColor="#666666"ForeColor="White"HorizontalAlign="Center"/>

<Fields>

<asp:BoundFieldDataField="problemId"HeaderText="Id:"ReadOnly="True"SortExpression="problemId">

<ItemStyleWidth="600px"BorderColor="White"BorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemTitle"HeaderText="Description:"SortExpression="problemTitle">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemBody"HeaderText="Resolution:"SortExpression="problemBody">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkOne"HeaderText="Links:"SortExpression="linkOne">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkTwo"HeaderText="linkTwo"SortExpression="linkTwo"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkThree"HeaderText="linkThree"SortExpression="linkThree"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkFour"HeaderText="linkFour"SortExpression="linkFour"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

</Fields>

<FieldHeaderStyleBackColor="#D0D0D0"Font-Bold="True"/>

<HeaderStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<AlternatingRowStyleBackColor="White"/>

</asp:DetailsView>

<asp:LabelID="Label1"runat="server"Font-Bold="True"ForeColor="White"Style="z-index: 103;

left: 13px; position: absolute; top: 59px"Width="521px"></asp:Label>

<asp:TextBoxID="srchBox"runat="server"Style="z-index: 101; left: 11px; position: absolute;

top: 31px"Width="520px"></asp:TextBox>

<asp:ButtonID="Button1"runat="server"Style="z-index: 102; left: 555px; position: absolute;

top: 31px"Text="Search It"/>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"></asp:SqlDataSource>

</td></tr>

</table>

</div>

</form> </body>

</html>

|||

quarinteen:

<SelectParameters>

<asp:ParameterName="srchBox"/>

</SelectParameters>

Have you tried to use Control parameter ? One more thing, what is the code you have written to execute the select method ? Can you post the code behind code also ?

|||

There is not any c# or vb to control the select statement. Just the asp.

|||

What I think is that you must be executing the datasource.select() method on the button click event, otherwise when would you bind the data to your grid after the user writes something in the textbox ? You've created everything and shown everything to the user.

Now, when user writes something in the textbox and presses the "Search It" button, you must execute the datasource.select() method. This is my assumption after reading your code. Post some more details as to what you are doing now and what is happing ( does any error occur ? ) so that I can help you better.

|||

no there is no c# or vb on this page that I have writter. Also I know varyations of this works I I put in the select statement "Select * from support where ((problemBody '%' + @.srchBox + '%') OR @.srchBox IS NULL)" it brings back the data

|||

Take the page you posted in the 2nd message. Change the parameter to a control parameter like you posted in the first message. Then change the sqldatasource, so that the "CancelSelectOnNullParameter" property is set to false. Change the search textbox, so it's autopostback property is set to true.

You may also need to change the parameter's properties to keep it from changing empty string to null, depending on how the freetext function interprets null parameters. For that matter, it might not like the empty string either.

Allowing for blank fields in date text boxes

Several textboxes are being on a web form for date entry. and we want to allow the user to leave date fields blank (if particular date is unknown). However, when the field is left blank, the SQL Server 2000 database defaults to 1/1/1900 (datatype = datetime).
My question is as follows: How is it possible to leave a date textbox field blank, and either send 00\00\0000 or spaces to the SQL Server 2000 database datetime field?
Any advice/insight is appreciated! RobEDIT
Both Datetime and SmallDatetime allow NULLs Datetime is 8bytes while Smalldatetime is 4bytes but its resolution is limited. If you want Time Span you have to use DateTime.
when you are using NULL with Datetime you must use COUNT (*) in all you calculations because all SQL Server aggregate functions ignore NULLs except COUNT (*).
Hope this helps.|||Check outthis article

Allow zero length strings

In Microsoft Access there is the option to set a field to accept zero length
strings.
This of course means that an empty text box could be entered into the
database without an error occuring.
Is there any way I can do this in SQL Server.
I know that when developing a front end I could write some code to check
each text box and when a textbox is empty, push a " " into it before
insertion but I'm sick of this.
Is there any other way ?We use a Default in SQL Server 2000 like the following:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[EmptyString]') and OBJECTPROPERTY(id,
N'IsDefault') = 1)
drop default [dbo].[EmptyString]
GO
create default [Space] as ''
GO
>--Original Message--
>In Microsoft Access there is the option to set a field to
accept zero length
>strings.
>This of course means that an empty text box could be
entered into the
>database without an error occuring.
>Is there any way I can do this in SQL Server.
>I know that when developing a front end I could write
some code to check
>each text box and when a textbox is empty, push a " "
into it before
>insertion but I'm sick of this.
>Is there any other way ?
>
>.
>|||You can insert an empty (zero-length) string into a varchar (or nvarchar)
column:
CREATE TABLE MyTable
(
MyColumn varchar(10) NOT NULL
)
INSERT INTO MyTable VALUES('')
SELECT
MyColumn,
DATALENGTH(MyColumn)
FROM MyTable
GO
I don't know much about Access programming so there may be programming
considerations. Does an empty text box return in a zero-length string value
or does this return a NULL value?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Poppy" <paul.diamond@.NOSPAMthemedialounge.com> wrote in message
news:ePYQ%23tctDHA.980@.TK2MSFTNGP10.phx.gbl...
> In Microsoft Access there is the option to set a field to accept zero
length
> strings.
> This of course means that an empty text box could be entered into the
> database without an error occuring.
> Is there any way I can do this in SQL Server.
> I know that when developing a front end I could write some code to check
> each text box and when a textbox is empty, push a " " into it before
> insertion but I'm sick of this.
> Is there any other way ?
>

Allow numbers in full text search

Hi, I need to search a product catalog for an online store, users must be
able to search for kitchens with "4" burners, but I get this error : "A
clause of the query contained only ignored words", below is the code for
stored procedure I'm using. Before I send the "@.SearchTerms" parameter, on
the client side (asp.net/vb.net) I parse the users input and concatenate
each word with an "AND", so if the user searches for "4 burner GE" y convert
this to "4 AND burner AND GE", I know the digit "4" is a noise word, and
I've seen post where people have just edit the noise word files, but I'm on
a shared hosting plan so I don't have access to them. Any ideas?
Regards,
Pablo Tola
pablo at imaget dot com
CREATE PROCEDURE SearchProducts (
@.SearchTerms varchar(500)
)
AS
SELECT
[p].[ProductId],
[p].[CategoryId],
[p].[BrandId],
[p].[Code],
[p].[ManufacturerCode],
[p].[Name],
[p].[Description],
[p].[Characteristics],
[p].[Keywords],
[p].[Image],
[p].[Price1],
[p].[Price2],
[p].[Price3],
[p].[IsDisplayedInCatalog],
[p].[IsInOutlet],
[p].[IsCalculatedForGift],
[p].[GiftRangeId],
[p].[StatusId],
KEY_TBL.RANK
FROM
CONTAINSTABLE ([dbo].[Products],*,@.SearchTerms) KEY_TBL
JOIN [dbo].[Products] p ON [KEY_TBL].[KEY] = [p].[ProductId]
Order By
KEY_TBL.RANK DESC
GO
have a look at this link for more info.
http://www.indexserverfaq.com/noise.htm
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
"Pablo Tola" <pablo@.imaget.com> wrote in message
news:edn1kEBTFHA.3696@.TK2MSFTNGP15.phx.gbl...
> Hi, I need to search a product catalog for an online store, users must be
> able to search for kitchens with "4" burners, but I get this error : "A
> clause of the query contained only ignored words", below is the code for
> stored procedure I'm using. Before I send the "@.SearchTerms" parameter, on
> the client side (asp.net/vb.net) I parse the users input and concatenate
> each word with an "AND", so if the user searches for "4 burner GE" y
convert
> this to "4 AND burner AND GE", I know the digit "4" is a noise word, and
> I've seen post where people have just edit the noise word files, but I'm
on
> a shared hosting plan so I don't have access to them. Any ideas?
> Regards,
> Pablo Tola
> pablo at imaget dot com
> CREATE PROCEDURE SearchProducts (
> @.SearchTerms varchar(500)
> )
> AS
> SELECT
> [p].[ProductId],
> [p].[CategoryId],
> [p].[BrandId],
> [p].[Code],
> [p].[ManufacturerCode],
> [p].[Name],
> [p].[Description],
> [p].[Characteristics],
> [p].[Keywords],
> [p].[Image],
> [p].[Price1],
> [p].[Price2],
> [p].[Price3],
> [p].[IsDisplayedInCatalog],
> [p].[IsInOutlet],
> [p].[IsCalculatedForGift],
> [p].[GiftRangeId],
> [p].[StatusId],
> KEY_TBL.RANK
> FROM
> CONTAINSTABLE ([dbo].[Products],*,@.SearchTerms) KEY_TBL
> JOIN [dbo].[Products] p ON [KEY_TBL].[KEY] = [p].[ProductId]
> Order By
> KEY_TBL.RANK DESC
> GO
>

Allocations of LOB datatypes

All
We have a very large table (1M+ rows) in which we are
storing large xmls (datalength(column) of 10K) in a text
datatype field.
We are using up space at a much greater rate than
originally planned - and will stop saving the the xml
for certain events. There are about 25 events during the
lifetime of an order.
I am trying to understand how we can get back the space
allocated to this object if we decided to set the column = null. It does not seem to change the information returned
by sp_spaceused even afer using the updateusage flag.
Must I truncate and reload this table to reclaim the space.
(Doing a select into of the table to a new table does seem
to reclaim the space)
Thanks
LBHi Len
Take a look at DBCC CLEANTABLE and see if that helps.
It's documented in Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Len Bearse" <anonymous@.discussions.microsoft.com> wrote in message
news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
> All
> We have a very large table (1M+ rows) in which we are
> storing large xmls (datalength(column) of 10K) in a text
> datatype field.
> We are using up space at a much greater rate than
> originally planned - and will stop saving the the xml
> for certain events. There are about 25 events during the
> lifetime of an order.
> I am trying to understand how we can get back the space
> allocated to this object if we decided to set the column => null. It does not seem to change the information returned
> by sp_spaceused even afer using the updateusage flag.
> Must I truncate and reload this table to reclaim the space.
> (Doing a select into of the table to a new table does seem
> to reclaim the space)
> Thanks
> LB
>|||It did not seem to help - please note in my tests
I am just setting the column = null.
>--Original Message--
>Hi Len
>Take a look at DBCC CLEANTABLE and see if that helps.
>It's documented in Books Online.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Len Bearse" <anonymous@.discussions.microsoft.com> wrote
in message
>news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
>> All
>> We have a very large table (1M+ rows) in which we are
>> storing large xmls (datalength(column) of 10K) in a text
>> datatype field.
>> We are using up space at a much greater rate than
>> originally planned - and will stop saving the the xml
>> for certain events. There are about 25 events during the
>> lifetime of an order.
>> I am trying to understand how we can get back the space
>> allocated to this object if we decided to set the
column =>> null. It does not seem to change the information
returned
>> by sp_spaceused even afer using the updateusage flag.
>> Must I truncate and reload this table to reclaim the
space.
>> (Doing a select into of the table to a new table does
seem
>> to reclaim the space)
>> Thanks
>> LB
>
>.
>|||Len
Ok, I see. You're not dropping the column, just setting it to null. But,
hey, that's a thought that might be a bit more efficient that a complete
recreate. Drop the column, run dbcc cleantable and then readd the column.
But of course, YMMV and you should test it well first.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:036801c3a577$66680450$a401280a@.phx.gbl...
> It did not seem to help - please note in my tests
> I am just setting the column = null.
>
> >--Original Message--
> >Hi Len
> >
> >Take a look at DBCC CLEANTABLE and see if that helps.
> >It's documented in Books Online.
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"Len Bearse" <anonymous@.discussions.microsoft.com> wrote
> in message
> >news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
> >> All
> >> We have a very large table (1M+ rows) in which we are
> >> storing large xmls (datalength(column) of 10K) in a text
> >> datatype field.
> >>
> >> We are using up space at a much greater rate than
> >> originally planned - and will stop saving the the xml
> >> for certain events. There are about 25 events during the
> >> lifetime of an order.
> >>
> >> I am trying to understand how we can get back the space
> >> allocated to this object if we decided to set the
> column => >> null. It does not seem to change the information
> returned
> >> by sp_spaceused even afer using the updateusage flag.
> >>
> >> Must I truncate and reload this table to reclaim the
> space.
> >> (Doing a select into of the table to a new table does
> seem
> >> to reclaim the space)
> >>
> >> Thanks
> >>
> >> LB
> >>
> >
> >
> >.
> >|||I guess the real underlying question is how is space
allocated to the structures the contain the lob data.
I had thought that the b-tree structure would allocate
data in a manner similar to other SQL server objects.
That as pages and extents were deallocated would be freed
and listed as available. It doesn't seem to be working
that way. In testing we don't seem to be freeing the data
to the same degree we are using it up.
I think what I am going to do one of the following
1) No check constraints - bcp table out - truncate table -
bcp table in - re-enable constraints
2) no check constraints - rename table to old_tbl - create
new_tab - insert into new empty table - re-enable
constraints
Len
>--Original Message--
>Len
>Ok, I see. You're not dropping the column, just setting
it to null. But,
>hey, that's a thought that might be a bit more efficient
that a complete
>recreate. Drop the column, run dbcc cleantable and then
readd the column.
>But of course, YMMV and you should test it well first.
>
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:036801c3a577$66680450$a401280a@.phx.gbl...
>> It did not seem to help - please note in my tests
>> I am just setting the column = null.
>>
>> >--Original Message--
>> >Hi Len
>> >
>> >Take a look at DBCC CLEANTABLE and see if that helps.
>> >It's documented in Books Online.
>> >
>> >--
>> >HTH
>> >--
>> >Kalen Delaney
>> >SQL Server MVP
>> >www.SolidQualityLearning.com
>> >
>> >
>> >"Len Bearse" <anonymous@.discussions.microsoft.com>
wrote
>> in message
>> >news:02d401c3a566$43ab68a0$a301280a@.phx.gbl...
>> >> All
>> >> We have a very large table (1M+ rows) in which we
are
>> >> storing large xmls (datalength(column) of 10K) in a
text
>> >> datatype field.
>> >>
>> >> We are using up space at a much greater rate than
>> >> originally planned - and will stop saving the the xml
>> >> for certain events. There are about 25 events during
the
>> >> lifetime of an order.
>> >>
>> >> I am trying to understand how we can get back the
space
>> >> allocated to this object if we decided to set the
>> column =>> >> null. It does not seem to change the information
>> returned
>> >> by sp_spaceused even afer using the updateusage flag.
>> >>
>> >> Must I truncate and reload this table to reclaim the
>> space.
>> >> (Doing a select into of the table to a new table does
>> seem
>> >> to reclaim the space)
>> >>
>> >> Thanks
>> >>
>> >> LB
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

Alignment etc...

This is a multi-part message in MIME format.
--=_NextPart_000_0028_01C6A504.9BB3C560
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
How can I align a field with the right margin of the report? With the = Matrix, the report width varies depending on the amount of data. I can = seem to find a way to specify that a control is anchored to the right = margin. Or maybe even centered in the page..
Help?
Jerry
--=_NextPart_000_0028_01C6A504.9BB3C560
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
How can I align a field with the right = margin of the report? With the Matrix, the report width varies depending on = the amount of data. I can seem to find a way to specify that a control = is anchored to the right margin. Or maybe even centered in the page..

Help?

Jerry
--=_NextPart_000_0028_01C6A504.9BB3C560--Hi Jerry,
Thank you for your posting!
Based on my test, you could just put the textbox next to the right border
of your report. The textbox will align with the right border of the report.
Please try and let me know the result.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks, Wei... But that doesn't work. Since the report is wider when it's
run than it is in the designer. And there's no way to determine, at design
time, how wide the report will, as the matrix expands dynamically based on
the criteria entered by the user at run time.
So... Is there any way to dynamically position any of the controls at run
time?
Thanks.
Jerry
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:TWFXnMYpGHA.4612@.TK2MSFTNGXA01.phx.gbl...
> Hi Jerry,
> Thank you for your posting!
> Based on my test, you could just put the textbox next to the right border
> of your report. The textbox will align with the right border of the
> report.
> Please try and let me know the result.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Jerry,
Thank you for the update.
You could not control the position of the control dynamically in the
report.
If the text box left border is next to the right border of the matrix, the
textbox will be position on the right of the matrix.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Jerry,
How are you doing on this issue? Does Wei's suggestion in the last reply
helps you some? If you still have any problems or need any further
assistance, please feel free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||I'm not sure I understand his suggestion at all... I have a textbox just
below and aligned with the right border of the matrix...
At run time, the matrix expands to accomodate maybe 3 to 7 columns,
depending on data. But the textbox stays at the position it was at in the
designer.
I want the text box's right edge position to stay aligned with the right
edge of the matrix as it expands.
Thanks.
Jerry
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:$TtwwejqGHA.2024@.TK2MSFTNGXA01.phx.gbl...
> Hello Jerry,
> How are you doing on this issue? Does Wei's suggestion in the last reply
> helps you some? If you still have any problems or need any further
> assistance, please feel free to post here.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> =================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>

Aligning text that has been placed inside text boxes

I am new to reporting services. I am having trouble aligning my text. I put
some things in text boxes and even though I have the boxes as close together
as possible, they appear far apart. I tried overlapping the boxes but then
when they deploy it pushes the data in the second box down to the next line.
Anybody know how I can fix this? Problem is part of what I am putting in the
report needs to be bolded and part doesnt, so I cant keep my data together. I
would appreciate any help that I could get.I have fixed this problem before by putting both test boxes in side of a
rectangle. Just make sure the text boxes are not over lapping . Also make the
rectangle the over all size of both text boxes.
"KimB" wrote:
> I am new to reporting services. I am having trouble aligning my text. I put
> some things in text boxes and even though I have the boxes as close together
> as possible, they appear far apart. I tried overlapping the boxes but then
> when they deploy it pushes the data in the second box down to the next line.
> Anybody know how I can fix this? Problem is part of what I am putting in the
> report needs to be bolded and part doesnt, so I cant keep my data together. I
> would appreciate any help that I could get.|||Thank you for your help. I've tried this too, and not overlapping the text
boxes solved the problem of not pushing the data to the next line. But I
still end up with my text far apart. Instead of having something like this:
First Name: Johnny (with Firstname bolded and Johnny not) I end up with
First Name: Johnny Even with the boxes as close together as I can
get them without overlapping them, the data comes back spaced far apart.
"C.M" wrote:
> I have fixed this problem before by putting both test boxes in side of a
> rectangle. Just make sure the text boxes are not over lapping . Also make the
> rectangle the over all size of both text boxes.
>
>
>
> "KimB" wrote:
> > I am new to reporting services. I am having trouble aligning my text. I put
> > some things in text boxes and even though I have the boxes as close together
> > as possible, they appear far apart. I tried overlapping the boxes but then
> > when they deploy it pushes the data in the second box down to the next line.
> > Anybody know how I can fix this? Problem is part of what I am putting in the
> > report needs to be bolded and part doesnt, so I cant keep my data together. I
> > would appreciate any help that I could get.