How To Auto Increment A Primary ID Field?

I was just wondering on a very simple database table with lets say a primary key set to columb ID and another columb lets say products, can you make the primary key automaticly increment its self whenever a new entry has been put in?

For instance say I have this table set up with ID Being the primary KEY, Columb 1 = ID( INT ), Columb 2 = Products ( VarChar(50) ), and have the fields ID = 1, and products = my product....and if a user inserts a new record say from a gridview or some sort of data entry the second ID Feild will automaticly be 2 and the products gets updated per user input......I'm very sorry but I'm having a hard time putting this into words for some reason..

umm basicly user adds something into the products feild and the ID field automaticly increments one number higher from the last one?

Thanks

Adam.

On the ID column set identity=Yes, and the value will be assigned automatically!

HTH

|||OH MAN your joking right? LOL I must have overlooked something.....because at first I thought that was how you do it but I was not sure and I think I tried setting it to yes in the past but I must have overlooked the values that changed or something....maybe I was having an off day.....but thats perfect thank you so much...|||Actually sorry to bother you again by any change would you know if you can set a primary key and a foreign key inside the same table?|||

This is why I needed my foregn key and primary key things to work...well at least my search's that I do point me in that direction.

I have a couple of listboxs where a user clicks on an option in parent listbox and the results come out on the child list box

Pritty much my problem is that when I use one table and several columbs the first ListBox (ListBox1) which only contained 6 Items had about 30 more Blank (Or Database NULL) fields after the 6 Selections....This was due because some fields were longer then others......The only way I've got around this is to create a different sql datasource for all selections which gives me 7 total sqldatasource on one particular page.....not a very smart coding practise lol...

How to assign random number to one column for 500 records?

I try to set up a testing sample table which contain one integer project_ID field for table Sample around 500 records, and want the project_ID to be random number within 1 to 99, how to implement script todo it?

thanks!

Here you go !
using System
namespace x {
classe y {
public y () {}
public int getRandomID () {
Random r = new Random()
return r.Next(1,99); // this indicates the start and end limit of the random function
}
}
}

then use the returned value into your SQL query to generate the records... make sure you dont have the project_ID as primary key as this above code does not check for uniqueness of the generated number....
HTH

how to arrange data horizontally in SQL Server 2005 report designer

I am wondering whether anyone knows, in SQL Server 2005 reporting service, how to arrange data (retrieved from a table field) horizontally (from left to right) instead of vertically (from top to bottom).

It is natural to arrange data vertically. However, it is not easy to arrange data horizontally. I tried using the multi-column feature in the reporting service to achieve the effect. It does not work. I also considered writing SQL code to transpose data (rows to columns, columns to rows). It does not help either.

Does anyone know how to achieve the effect? Is there a work-around?

Any idea, tip, or suggestion is greatly appreciated.

Thanks.

Use a matrix report on top of the data.|||

Besides using a matrix, you could also look into the "horizontal table" approach describes on Chris Hays' blog: http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

-- Robert

How to append data to ntext field?

Hi,
Can anybody tell me how to append data to ntext field?
JoydeepOn the client side if that is one of your choices!

If you absolutely MUST do it within SQL Server, the first step is to re-evalute the requirement ;) If you are convinced that the pain is worth it, then you need to pursue UPDATETEXT (http://msdn2.microsoft.com/en-us/library/ms189466.aspx).

-PatP|||On the client side if that is one of your choices!

If you absolutely MUST do it within SQL Server, the first step is to re-evalute the requirement ;) If you are convinced that the pain is worth it, then you need to pursue UPDATETEXT (http://msdn2.microsoft.com/en-us/library/ms189466.aspx).

-PatP
Hi PatP,
I already tried the above one,but can't make out thats why I put it in the forum.I am trying it in Sql Server.
The Updatetext syntax required the insert_off parameter.How can I find out the length of a ntext field?
Be sure that I want to append text so I need the total no of characters after which this piece of text will be inserted.
Plz comment.
Joydeep|||Check out the datalength() function in BOL. I forget if you have to divide by 2 for ntext, and am too lazy to look it up, now.|||Check out the datalength() function in BOL. I forget if you have to divide by 2 for ntext, and am too lazy to look it up, now.
Sorry,Nothing found like dividing like /2,just
http://msdn2.microsoft.com/en-us/library(d=robot)/ms176068.aspx

I was testing this one..

use test
declare
@.pp binary(16),
@.txt numeric

set @.txt=(select datalength(info) from table1)

set @.pp=(select textptr(info) from table1)

updatetext table1.info @.pp @.txt 0 'asljdklsadjKLKLSDJLJAdlkdjlKAJKLSJDKLAS
asjDHjkhkjlJHSDJKASHDJKASHDJKhkjasdhKJHsfajdfkljas dkljflksdjflkj
asdfjkdlaskjfklajsdfkljsdafkljsldkfjsdkljfklj
sdfjal;dasjkfkljalsdkfjldksjfklsdjflkjlkdsjfkjfkls djfklsdfjlskfjskladf JOYDEEP'

getting an error as ...

Server: Msg 7116, Level 16, State 4, Line 10
Offset 1091077014 is not in the range of available text, ntext, or image data.
The statement has been terminated.

??:(
Thanks anyway..
Joydeep|||Sorry,Nothing found like dividing like /2,just
http://msdn2.microsoft.com/en-us/library(d=robot)/ms176068.aspx

I was testing this one..

getting an error as ...

??:(
Thanks anyway..
Joydeep

Again if I change the datatype to Text this is working fine

use test
create table tom
(info text)
insert into tom values('Starting Text')

use test
declare
@.pp binary(16),
@.txt int
select @.pp=textptr(info) from tom
select @.txt=datalength(info) from tom
updatetext tom.info @.pp @.txt 0 'joydeep'
select * from tom

Result..

info Starting Textjoydeep

(1 row(s) affected)

Plz comment...
Joydeep|||Consider this code:

use pubs
go
create table test1
(col1 text, col2 ntext)

insert into test1
values ('hello', 'hello')

select datalength(col1), datalength(col2)
from test1

drop table test1

The answer should jump at you...|||Consider this code:

use pubs
go
create table test1
(col1 text, col2 ntext)

insert into test1
values ('hello', 'hello')

select datalength(col1), datalength(col2)
from test1

drop table test1

The answer should jump at you...

Well,You are absolutely right,but same problem with the statement.I am getting the same error..

use test

create table table1(info ntext)
insert into table1 values('First string')
declare
@.pp binary(16),
@.txt numeric

set @.txt=(select datalength(info) from table1)
set @.txt=@.txt/2
print @.txt
set @.pp=(select textptr(info) from table1)

updatetext table1.info @.pp @.txt 0 'JOYDEEP'
go
select * from table1
drop table table1


error ...

(1 row(s) affected)

12
Server: Msg 7116, Level 16, State 4, Line 14
Offset 1087931286 is not in the range of available text, ntext, or image data.
The statement has been terminated.
info
----------------------------------------------------------------------------
First string

(1 row(s) affected)

Thanks
Joydeep|||Well,well,well at last I got it.....:D :D

use test
create table gogo(col1 ntext)
insert into gogo values('first string')

Declare @.pointer binary(16),@.txtlen int
select @.pointer=textptr(col1) from gogo
select @.txtlen=datalength(col1) from gogo
set @.txtlen=@.txtlen/2
updatetext gogo.col1 @.pointer @.txtlen 0 'JoydeepDattagfhgfhfghgfdhgdfhdfhfggfhgfhgfhgfhcxn bnvn
fghgfhdfdfh892739281837`2jihqwjkhdkhjh$&^%&^%*%^*&%^*&%^&^%jghjkhakdhaskdhsk'

go
select * from gogo
go
drop table gogo

;) testing is the best solution
Thanks!!
Joydeep|||Well,well,well at last I got it.....:D :D

;) testing is the best solution
Thanks!!
Joydeep

I just changed the datatype of the variable from numeric to int.That solved the problem.I mean the variable that was keeping the length.:D :p

How to alter(add) a Table with a default value and by allowing Nulls?

Hi

I am using this query to alter a table

ALTER TABLE myTable ADD age int NULL DEFAULT(0)

But above query is adding age field by storing Nulls but not with default values

So I need to add age field to the table by storing default value as 0 and by allowing Nulls

Please advice

Thanks

Use the below query,

Code Snippet

Create table #Mytable

(

Id int,

Name varchar(100)

)

Insert Into #Mytable values(1,'One');

Insert Into #Mytable values(2,'Two');

Insert Into #Mytable values(3,'Three');

Alter table #MyTable Add Age int NOT NULL Default(0)

Alter table #MyTable Alter Column Age int NULL;

select * from #MyTable

Insert Into #Mytable(Id,Name) values(4,'Four');

select * from #MyTable

|||

Thanks Mani

I got my answer

and

Can't we do it in a single step in Sql Server?

|||

Yes, we have it,

Code Snippet

Alter table #MyTable Add Age int NULL Default(0) WITH VALUES

|||

Thanks a lot Mani, That's what I'm talking about.

With Regards

Vijay

How to alter the default of a field by SQL

How to alter the default of a field by SQL?
ad
DROP constraint and re-create it again
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:eA3Tqk5AHHA.1012@.TK2MSFTNGP04.phx.gbl...
> How to alter the default of a field by SQL?
>

How to alter the default of a field by SQL

How to alter the default of a field by SQL?ad
DROP constraint and re-create it again
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:eA3Tqk5AHHA.1012@.TK2MSFTNGP04.phx.gbl...
> How to alter the default of a field by SQL?
>sql

How to alter the default of a field by SQL

How to alter the default of a field by SQL?ad
DROP constraint and re-create it again
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:eA3Tqk5AHHA.1012@.TK2MSFTNGP04.phx.gbl...
> How to alter the default of a field by SQL?
>

How to alter the data type of a field

I want to change the filed type form nvarchar(5) to varchar(10)
There is no need to reserve the data in the filed.
How can I do with SQL command?
Use the ALTER TABLE command:
ALTER TABLE my_table
ALTER COLUMN my_column varchar(10)
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>I want to change the filed type form nvarchar(5) to varchar(10)
> There is no need to reserve the data in the filed.
> How can I do with SQL command?
>
|||Thank,
Can I determinate if the data type varchar before ater it.
Like
if type(my_table.my_column) <>varchar(10)
ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.g bl...
> Use the ALTER TABLE command:
> ALTER TABLE my_table
> ALTER COLUMN my_column varchar(10)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>
|||What version?
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Thank,
> Can I determinate if the data type varchar before ater it.
> Like
> if type(my_table.my_column) <>varchar(10)
> ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com>
> glsD:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.g bl...
>
|||my version is SQLServer 2005 Express
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:ORvLXDCGHHA.4804@.TK2MSFTNGP03.phx.g bl...
> What version?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
>
|||This query returns all the datatypes and lengths for columns in a given
table. You should be able to figure out how to write your IF statement from
this information:
SELECT object_name(object_id) as [object_name], c.name as column_name,
t.name as type_name, c.max_length
FROM sys.types t join sys.columns c
ON t.user_type_id = c.system_type_id
WHERE object_name(object_id) = 'my_table'
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:eoktgyCGHHA.2456@.TK2MSFTNGP06.phx.gbl...
> my version is SQLServer 2005 Express
> "Kalen Delaney" <replies@.public_newsgroups.com>
> glsD:ORvLXDCGHHA.4804@.TK2MSFTNGP03.phx.g bl...
>

How to alter the data type of a field

I want to change the filed type form nvarchar(5) to varchar(10)
There is no need to reserve the data in the filed.
How can I do with SQL command?Use the ALTER TABLE command:
ALTER TABLE my_table
ALTER COLUMN my_column varchar(10)
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>I want to change the filed type form nvarchar(5) to varchar(10)
> There is no need to reserve the data in the filed.
> How can I do with SQL command?
>|||Thank,
Can I determinate if the data type varchar before ater it.
Like
if type(my_table.my_column) <>varchar(10)
ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:O30DznAGHHA.2128@.TK2MSFTNGP03
.phx.gbl...
> Use the ALTER TABLE command:
> ALTER TABLE my_table
> ALTER COLUMN my_column varchar(10)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>|||What version?
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Thank,
> Can I determinate if the data type varchar before ater it.
> Like
> if type(my_table.my_column) <>varchar(10)
> ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com>
> glsD:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
>|||my version is SQLServer 2005 Express
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:ORvLXDCGHHA.4804@.TK2MSFTNGP03
.phx.gbl...
> What version?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
>|||This query returns all the datatypes and lengths for columns in a given
table. You should be able to figure out how to write your IF statement from
this information:
SELECT object_name(object_id) as [object_name], c.name as column_name,
t.name as type_name, c.max_length
FROM sys.types t join sys.columns c
ON t.user_type_id = c.system_type_id
WHERE object_name(object_id) = 'my_table'
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:eoktgyCGHHA.2456@.TK2MSFTNGP06.phx.gbl...
> my version is SQLServer 2005 Express
> "Kalen Delaney" <replies@.public_newsgroups.com>
> glsD:ORvLXDCGHHA.4804@.TK2MSFTNGP03.phx.gbl...
>

How to alter the data type of a field

I want to change the filed type form nvarchar(5) to varchar(10)
There is no need to reserve the data in the filed.
How can I do with SQL command?Use the ALTER TABLE command:
ALTER TABLE my_table
ALTER COLUMN my_column varchar(10)
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>I want to change the filed type form nvarchar(5) to varchar(10)
> There is no need to reserve the data in the filed.
> How can I do with SQL command?
>|||Thank,
Can I determinate if the data type varchar before ater it.
Like
if type(my_table.my_column) <>varchar(10)
ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
"Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
> Use the ALTER TABLE command:
> ALTER TABLE my_table
> ALTER COLUMN my_column varchar(10)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>>I want to change the filed type form nvarchar(5) to varchar(10)
>> There is no need to reserve the data in the filed.
>> How can I do with SQL command?
>|||What version?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Thank,
> Can I determinate if the data type varchar before ater it.
> Like
> if type(my_table.my_column) <>varchar(10)
> ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com>
> ¼¶¼g©ó¶l¥ó·s»D:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
>> Use the ALTER TABLE command:
>> ALTER TABLE my_table
>> ALTER COLUMN my_column varchar(10)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>>I want to change the filed type form nvarchar(5) to varchar(10)
>> There is no need to reserve the data in the filed.
>> How can I do with SQL command?
>>
>|||my version is SQLServer 2005 Express
"Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:ORvLXDCGHHA.4804@.TK2MSFTNGP03.phx.gbl...
> What version?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
>> Thank,
>> Can I determinate if the data type varchar before ater it.
>> Like
>> if type(my_table.my_column) <>varchar(10)
>> ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
>>
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
>> Use the ALTER TABLE command:
>> ALTER TABLE my_table
>> ALTER COLUMN my_column varchar(10)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>>I want to change the filed type form nvarchar(5) to varchar(10)
>> There is no need to reserve the data in the filed.
>> How can I do with SQL command?
>>
>>
>|||This query returns all the datatypes and lengths for columns in a given
table. You should be able to figure out how to write your IF statement from
this information:
SELECT object_name(object_id) as [object_name], c.name as column_name,
t.name as type_name, c.max_length
FROM sys.types t join sys.columns c
ON t.user_type_id = c.system_type_id
WHERE object_name(object_id) = 'my_table'
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:eoktgyCGHHA.2456@.TK2MSFTNGP06.phx.gbl...
> my version is SQLServer 2005 Express
> "Kalen Delaney" <replies@.public_newsgroups.com>
> ¼¶¼g©ó¶l¥ó·s»D:ORvLXDCGHHA.4804@.TK2MSFTNGP03.phx.gbl...
>> What version?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:OD2cMPBGHHA.5104@.TK2MSFTNGP03.phx.gbl...
>> Thank,
>> Can I determinate if the data type varchar before ater it.
>> Like
>> if type(my_table.my_column) <>varchar(10)
>> ALTER TABLE my_table ALTER COLUMN my_column varchar(10)
>>
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com>
>> ¼¶¼g©ó¶l¥ó·s»D:O30DznAGHHA.2128@.TK2MSFTNGP03.phx.gbl...
>> Use the ALTER TABLE command:
>> ALTER TABLE my_table
>> ALTER COLUMN my_column varchar(10)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
>> news:umBJWlAGHHA.4652@.TK2MSFTNGP04.phx.gbl...
>>I want to change the filed type form nvarchar(5) to varchar(10)
>> There is no need to reserve the data in the filed.
>> How can I do with SQL command?
>>
>>
>>
>

How to allow use to exec xp_sendmail

I have a stored proc that assigns a value to a field based on user input from an Access front end.

The last part of the stored proc sends an email if certain conditions are met.

It appears that users do not have permission to execute xp_sendmail. I guess this is because it is executed on the master database. Is there a way I can give them permission to this stored proc?

The users are getting this message:

EXECUTE permission denied on bject 'xp_sendmail', 'database master', owner 'dbo'.(#229)You can grant a user to execute xp_sendmail by going to properties of xp_sendmail in master database and grant the user permission to execute.

How to allow null by SQL

I have a field in a table, which is not allow null.
How can I set it to allow by SQL?
You can use the ALTER TABLE command:
ALTER TABLE my_table ALTER COLUMN my_column column_datatype NULL
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uBqoduPGHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I have a field in a table, which is not allow null.
> How can I set it to allow by SQL?
>
|||Hello,
Add on to Kalen; You can not chnage the column to Nullable if the column is
associated with a Primary key. Where as for a Unique column if you set a
NULL set even then
only one null will be allowed.
Thanks
Hari
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ezwlW%23PGHHA.3464@.TK2MSFTNGP05.phx.gbl...
> You can use the ALTER TABLE command:
> ALTER TABLE my_table ALTER COLUMN my_column column_datatype NULL
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:uBqoduPGHHA.1252@.TK2MSFTNGP02.phx.gbl...
>

How to allow null by SQL

I have a field in a table, which is not allow null.
How can I set it to allow by SQL?You can use the ALTER TABLE command:
ALTER TABLE my_table ALTER COLUMN my_column column_datatype NULL
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uBqoduPGHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I have a field in a table, which is not allow null.
> How can I set it to allow by SQL?
>|||Hello,
Add on to Kalen; You can not chnage the column to Nullable if the column is
associated with a Primary key. Where as for a Unique column if you set a
NULL set even then
only one null will be allowed.
Thanks
Hari
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ezwlW%23PGHHA.3464@.TK2MSFTNGP05.phx.gbl...
> You can use the ALTER TABLE command:
> ALTER TABLE my_table ALTER COLUMN my_column column_datatype NULL
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:uBqoduPGHHA.1252@.TK2MSFTNGP02.phx.gbl...
>

How to allow null by SQL

I have a field in a table, which is not allow null.
How can I set it to allow by SQL?You can use the ALTER TABLE command:
ALTER TABLE my_table ALTER COLUMN my_column column_datatype NULL
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uBqoduPGHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I have a field in a table, which is not allow null.
> How can I set it to allow by SQL?
>|||Hello,
Add on to Kalen; You can not chnage the column to Nullable if the column is
associated with a Primary key. Where as for a Unique column if you set a
NULL set even then
only one null will be allowed.
Thanks
Hari
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ezwlW%23PGHHA.3464@.TK2MSFTNGP05.phx.gbl...
> You can use the ALTER TABLE command:
> ALTER TABLE my_table ALTER COLUMN my_column column_datatype NULL
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:uBqoduPGHHA.1252@.TK2MSFTNGP02.phx.gbl...
>>I have a field in a table, which is not allow null.
>> How can I set it to allow by SQL?
>sql

How To Add Time Field In Crystal Reports 4.6

I want to add Date and Time details in my Report. (Crystal Reports 4.6) Date is ok, But I can't add time field. Please help me to do that.
Regards,
Supunu got two special fields print date and print time use them

How to add summary functions in group footers for report with no database field

Hi,

I use a crystal report that does not have any database fields, only formula fields. I already know how to add a group with the addgroup method, but need to know how are summary fields added?

I need to add a sub total for a money field in each group that has to show at bottom of each group but am unsure as to how this is done. Any help would be very much appreciated.

Again, I don't use any database field and so CR does not allow me to physically add a group section and paste any formulas. It seems this needs to be done by code.

I'm desperate!

Regards,

MikeHi,
U need database fiels in crystal report, it is very important, without having any data what r u gonna show in your report.

Field on which u want to summarize,

In your report detail section, right click the desired field and "Insert" > "Summary". Select "count" and place it in group footer

U will have various options as maximum , minimum, count , Mode etc, select according to your report requirements.

How to add new fields in crystal report 11?

Hi, how do I add a new field in the crystal report? Im using crystal report 11. Please advice... :)You're 'aving a laugh, right?|||What do you mean? How can I add new fields to my report from my database? :)|||The same way you added existing ones, drag 'em there...
Or maybe there's some subtly in your question that I just don't understand?|||Do you want add new fields that are created after the report was designed? Open the report and do verify database|||Do you want add new fields that are created after the report was designed? Open the report and do verify database

Ya, the fields are new and are not in the report. How do I add the new field to the report? How do I verify database? Im very new to crystal rpt. Please guide me along...:)|||Open the report. In the menu Database->Verify Database. If if says database is uptodate, then you can add the new columns to that report

how to add image file to a table?

If I have a table with an image column, how can I add an image file to the
image field? Using ADO.Net I can use the command object to add text data
like this:
cmd.CommandText = "Insert Into tlb1(fld1) Values('test1')"
cmd.ExecuteNonQuery()
But what if I want to add the file at C:\dir1\pic1.jpg?
cmd.CommandText = ?
Do I need to use the ADODB Stream Object for this? While I am at it, is
there a difference between storing an image file in an image column or a
binary column?
Thanks,
RichJust have a look here, there is a code snipplet presented within the
article:
http://www.codeproject.com/aspnet/PicManager.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Rich" <Rich@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B848F047-4D0A-4EAE-B609-3DC58482FFA0@.microsoft.com...
> If I have a table with an image column, how can I add an image file to the
> image field? Using ADO.Net I can use the command object to add text data
> like this:
> cmd.CommandText = "Insert Into tlb1(fld1) Values('test1')"
> cmd.ExecuteNonQuery()
> But what if I want to add the file at C:\dir1\pic1.jpg?
> cmd.CommandText = ?
> Do I need to use the ADODB Stream Object for this? While I am at it, is
> there a difference between storing an image file in an image column or a
> binary column?
> Thanks,
> Rich
>|||Thanks very much for your reply. I retrieved the code you pointed me to.
But one problem I seem to always have is that when I try to run an aspx app
that I copied from somewhere else I keep getting the following error message
:
"Error while trying to run project: Unable to start debugging on the web
server. The project is not configured to be debugged."
My workaround has been to create a whole new project and copy the parts from
the original project. I have explored the Assembly file, the WebConfig file
to see if there is anything to configure there. But I have not had any luck
.
Do you know how I could configure this application so that I don't have to
create a whole new one?
Thanks again,
Rich
"Jens Sü?meyer" wrote:

> Just have a look here, there is a code snipplet presented within the
> article:
> http://www.codeproject.com/aspnet/PicManager.asp
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Rich" <Rich@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:B848F047-4D0A-4EAE-B609-3DC58482FFA0@.microsoft.com...
>
>|||Well, I went ahead and created a new project. When I ran it - it worked
perfectly! Thanks very much for your help.
Rich
"Rich" wrote:
> Thanks very much for your reply. I retrieved the code you pointed me to.
> But one problem I seem to always have is that when I try to run an aspx ap
p
> that I copied from somewhere else I keep getting the following error messa
ge:
> "Error while trying to run project: Unable to start debugging on the web
> server. The project is not configured to be debugged."
> My workaround has been to create a whole new project and copy the parts fr
om
> the original project. I have explored the Assembly file, the WebConfig fi
le
> to see if there is anything to configure there. But I have not had any lu
ck.
> Do you know how I could configure this application so that I don't have t
o
> create a whole new one?
> Thanks again,
> Rich
> "Jens Sü?meyer" wrote:
>|||One more question if I may. This routine is using a Web button which
contains the PostedFile method.
Upload.PostedFile.InputStream.Read (pic, 0, len);
So, even though this works, I can only do it from an aspx page. Can
something like this be done using ADO/ADO.Net?
Anyway, thanks again for your help on this.
Rich
"Rich" wrote:
> Well, I went ahead and created a new project. When I ran it - it worked
> perfectly! Thanks very much for your help.
> Rich
> "Rich" wrote:
>|||You have to go in the properties of the virtual Directory and create an
Applicationame for the Virtual Directory, then it is ackknowledge from IIS
as an asp.NET project.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Rich" <Rich@.discussions.microsoft.com> schrieb im Newsbeitrag
news:7CD9B4A2-0EB2-49F2-8157-F6C22C81E247@.microsoft.com...
> Thanks very much for your reply. I retrieved the code you pointed me to.
> But one problem I seem to always have is that when I try to run an aspx
> app
> that I copied from somewhere else I keep getting the following error
> message:
> "Error while trying to run project: Unable to start debugging on the web
> server. The project is not configured to be debugged."
> My workaround has been to create a whole new project and copy the parts
> from
> the original project. I have explored the Assembly file, the WebConfig
> file
> to see if there is anything to configure there. But I have not had any
> luck.
> Do you know how I could configure this application so that I don't have to
> create a whole new one?
> Thanks again,
> Rich
> "Jens Smeyer" wrote:
>|||http://support.microsoft.com/kb/309158/en-us
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Rich" <Rich@.discussions.microsoft.com> schrieb im Newsbeitrag
news:87DAE68E-8E09-4FF9-A7A2-F317A7B2618E@.microsoft.com...
> One more question if I may. This routine is using a Web button which
> contains the PostedFile method.
> Upload.PostedFile.InputStream.Read (pic, 0, len);
> So, even though this works, I can only do it from an aspx page. Can
> something like this be done using ADO/ADO.Net?
> Anyway, thanks again for your help on this.
> Rich
> "Rich" wrote:
>|||Thank you again for your help. And I appologize for my ignorance. Anyway,
I
went to the properties of my virtual directory. There is already an
application name there - the name of the virtual directory. I am not clear
on what name I should put in there. Is it the name of my project? So
everytime I copy a project from somehwere I put the name of that project in
the Application name box in the properties of the Virtual directory?
Thanks also for the article on using ADO.Net for writing blobs to Sql
Server.
Rich
"Jens Sü?meyer" wrote:

> You have to go in the properties of the virtual Directory and create an
> Applicationame for the Virtual Directory, then it is ackknowledge from IIS
> as an asp.NET project.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Rich" <Rich@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:7CD9B4A2-0EB2-49F2-8157-F6C22C81E247@.microsoft.com...
>
>

How to add field to table after replication has started?

I already have Merge replication started between 2
machines and now I want to add a field to a table that is
being used by replication. How can I go about doing this?
Is there a stored procedure to do this?
Thanks in advance,
Jim
Hi,
If you right-click on the publication you can access to all the articles to
alter them, if you go to the filer columns tab you will find the columns
list, you can alter there.
cheers
Salva
"Jim" wrote:

> I already have Merge replication started between 2
> machines and now I want to add a field to a table that is
> being used by replication. How can I go about doing this?
> Is there a stored procedure to do this?
> Thanks in advance,
> Jim
>
|||you can also use sp_repladdcolumn
SQL Server 2005 will allow DDL statements to be replicated.
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
"Jim" <jrathmann@.vtti.vt.edu> wrote in message
news:043101c539df$f3e70120$a601280a@.phx.gbl...
> I already have Merge replication started between 2
> machines and now I want to add a field to a table that is
> being used by replication. How can I go about doing this?
> Is there a stored procedure to do this?
> Thanks in advance,
> Jim