Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts

Wednesday, March 28, 2012

how to assign null in derived column transformation editor

Dear friends, can any one tell me how to assign null to the expression value in derived column transfromation editor?

thanks,

In the editor dialog, expand the Null Functions folder, and drag the appropriate NULL(your data type here) item to the expression box. For example, you'd use NULL(DT_I4) to create a null integer.

Monday, March 19, 2012

How to add the NOT NULL and PRIMAY KEY Constraints with ALTER command?

Dear All,

I wanted to alter a column in a table for adding both NOT NULL and PRIMARY KEY constraints to an existing column. How do I go about that.

I need your help.

First use alter table alter column for redefine column with not null, then use alter table add constraint

Code Snippet

createtable MyTable

(

id intnull,

namevarchar(100)

)

altertable MyTable altercolumn id intnotnull

altertable MyTable addconstraint pk_id primarykey(id)

How to add the Column in Table which is in replication

Dear Friends
I have setup a merge replicaion between my two servers
and now i want to add some column in one table kindly
suggest how i can do the same without stoping the
replicaiton.
your earlier reply will guide me to solve the problem.
Best regards
Sharad
Sharad,
have a look in BOL for the command sp_repladdcolumn.
HTH,
Paul Ibison
|||Dear Fiend
Getting the following error.
Server: Msg 8114, Level 16, State 4, Procedure
sp_repladdcolumn, Line 0
Error converting data type nvarchar to bit.
for the code
sp_repladdcolumn @.source_object = 'OTHALLOCATION'
, @.column = 'CCICAPPNUM'
, @.typetext = 'NVARCHAR(50)'
, @.publication_to_add = 'GEMREPLGNETINFOTECH'
, @.schema_change_script = ''
, @.force_invalidate_snapshot =
force_invalidate_snapshot
, @.force_reinit_subscription =
force_reinit_subscription
Please suggest.
Best regards
Shailesh

>--Original Message--
>Sharad,
>have a look in BOL for the command sp_repladdcolumn.
>HTH,
>Paul Ibison
>
>.
>
|||Shailesh,
please can you try this script instead:
sp_repladdcolumn @.source_object = 'OTHALLOCATION'
, @.column = 'CCICAPPNUM'
, @.typetext = 'NVARCHAR(50) NULL'
, @.publication_to_add = 'GEMREPLGNETINFOTECH'
HTH,
Paul Ibison

Monday, March 12, 2012

How to add IDENTITY constraint

Dear All,

I wanted to add a IDENTITY constraint in a column using the ALTER command, How do I go about doing that?

Please help.

Code Snippet

create table #what (something varchar(10))

alter table #what
add anIdentity int identity

insert into #what
select 'This' union all
select 'is' union all
select 'just' union all
select 'another' union all
select 'test'
select * from #what

/*
something anIdentity
- --
This 1
is 2
just 3
another 4
*/

|||

Sorry for incomplete or unclear question.

What I mean is Altering the existing column and adding Identity to that column

|||

I think to do that you will need to do the work of creating a new table and move the data into the new table. Care needs to be take to pay attention to the seeding of the new identity column and all constraint work that needs to be done.

Will someone please check me on this?

|||

Unfortunately, i don't think you are able to alter an existing column to be an identity. Your best workaround is to create a new table with the correct schema, dump the data into it and then rename it.

However, I appreciate this can be a messy and impractical approach. Your only other option is to just add an extra identity column.

HTH

|||The following should work:

Code Snippet

CREATE TABLE TestTable
(
ToBeIdentity INT,
TestCol VARCHAR(20)
)

INSERT INTO TestTable(ToBeIdentity, TestCol) VALUES(1, 'Col1')
INSERT INTO TestTable(ToBeIdentity, TestCol) VALUES(2, 'Col2')
INSERT INTO TestTable(ToBeIdentity, TestCol) VALUES(3, 'Col3')
INSERT INTO TestTable(ToBeIdentity, TestCol) VALUES(4, 'Col4')

SELECT
*
INTO
#TempTransfer
FROM
TestTable

ALTER TABLE TestTable DROP COLUMN ToBeIdentity

DELETE FROM TestTable

ALTER TABLE TestTable ADD ToBeIdentity INT IDENTITY(1, 1)

SET IDENTITY_INSERT TestTable ON
INSERT INTO TestTable
(ToBeIdentity,
TestCol)
SELECT
*
FROM
#TempTransfer
SET IDENTITY_INSERT TestTable OFF

INSERT INTO TestTable(TestCol) VALUES('Col5')

SELECT
*
FROM
TestTable

DROP TABLE #TempTransfer


Unfortunately, you have to specify a column list when inserting the data into the table again. Other than that, you should be able to use it for most tables without modification.
|||

It is not necessary to create a new table and transfer the data -with a very large table that could be quite a performance hit.

As this example demonstrates, you can add an IDENTITY column to an existing table. (NOTE: This does not guarantee any order to the data.)

Code Snippet


CREATE TABLE #MyTable
( RowID int,
MyValue varchar(20)
)


INSERT INTO #MyTable VALUES ( 25, 'Value 1' )
INSERT INTO #MyTable VALUES ( 2, 'Value 2' )
INSERT INTO #MyTable VALUES ( 61, 'Value 3' )
INSERT INTO #MyTable VALUES ( 33, 'Value 4' )


-- Keep Existing Column and Data
ALTER TABLE #MyTable
ADD NewRowID int IDENTITY(1, 1)


SELECT *
FROM #MyTable


RowID MyValue NewRowID
-- -- --
25 Value 1 1
2 Value 2 2
61 Value 3 3
33 Value 4 4

-- If you want to keep the Column Name -but not data
ALTER TABLE #MyTable
DROP COLUMN RowID, NewRowID


ALTER TABLE #MyTable
ADD RowID int IDENTITY(1, 1)


SELECT *
FROM #MyTable


DROP TABLE #MyTable

MyValue RowID
-- --
Value 1 1
Value 2 2
Value 3 3
Value 4 4

If you want to maintain or coerce order, creating a new table and transferring the data would allow you to use ORDER BY on the insert statement.


|||

Arnie Rowland wrote:

It is not necessary to create a new table and transfer the data -with a very large table that could be quite a performance hit.

As this example demonstrates, you can add an IDENTITY column to an existing table.

Code Snippet


CREATE TABLE #MyTable
( RowID int,
MyValue varchar(20)
)

...

Your foreign keys on the original column become messed up unless you preserve the original values.

Friday, March 9, 2012

How to add Drill down option in the Dataset ( + )

Dear Friends,

I have created a Dataset and displayed in the report. In the report, its showing as expanded values by default. There is no option for me to Collapse and Expand.

If I create a Table with Report Wizard, I am getting the Collapse & Expand ( + ) option in the report.

Is there anyway, I can add the Collapse / Expand in the Dataset kind of reports.

Thanks.

Warm Regards
Rakin
Singapore.

If you investigate the report that is generated through the report wizard, you will notice that the toggle-effect is achieved by setting the Visibility property e.g. of table rows to be toggled by a certain other textbox.

Please also check RS BOL for more information how to add toggles in report designer: http://msdn2.microsoft.com/en-us/library/ms156456.aspx

-- Robert

|||

Thanks Robert.

Now I am able to do that. It works fine.

Regards
Rakin.

How to add document type in full - text search (ifiter)

Dear all,
I use full-text search in SQL2005, but i discover it only support some
document types (.doc \ .xls .. , select * from sys.fulltext_document_types
),i want to it also support PDF type, well then, how i can do it?
by the way. I download a ifilter from adobe website and installed. but
also can't support the pdf type.
thanks & regards
jp ye
"jp" <yjp_it@.yahoo.com.cn> wrote in message
news:3E9576E9-041E-496E-91C4-4AD1B39360CB@.microsoft.com...
> Dear all,
> I use full-text search in SQL2005, but i discover it only support some
> document types (.doc \ .xls .. , select * from sys.fulltext_document_types
> ),i want to it also support PDF type, well then, how i can do it?
> by the way. I download a ifilter from adobe website and installed. but
> also can't support the pdf type.
You might get a faster answer if you re-post this to
microsoft.public.sqlserver.fulltext. They deal specifically with FTS
issues.

How to add document type in full - text search (ifiter)

Dear all,
I use full-text search in SQL2005, but i discover it only support some
document types (.doc \ .xls .. , select * from sys.fulltext_document_types
),i want to it also support PDF type, well then, how i can do it?
by the way. I download a ifilter from adobe website and installed. but
also can't support the pdf type.
thanks & regards
jp ye"jp" <yjp_it@.yahoo.com.cn> wrote in message
news:3E9576E9-041E-496E-91C4-4AD1B39360CB@.microsoft.com...
> Dear all,
> I use full-text search in SQL2005, but i discover it only support some
> document types (.doc \ .xls .. , select * from sys.fulltext_document_types
> ),i want to it also support PDF type, well then, how i can do it?
> by the way. I download a ifilter from adobe website and installed. but
> also can't support the pdf type.
You might get a faster answer if you re-post this to
microsoft.public.sqlserver.fulltext. They deal specifically with FTS
issues.

How to add document type in full - text search (ifiter)

Dear all,
I use full-text search in SQL2005, but i discover it only support some
document types (.doc \ .xls .. , select * from sys.fulltext_document_types
),i want to it also support PDF type, well then, how i can do it?
by the way. I download a ifilter from adobe website and installed. but
also can't support the pdf type.
--
thanks & regards
jp ye"jp" <yjp_it@.yahoo.com.cn> wrote in message
news:3E9576E9-041E-496E-91C4-4AD1B39360CB@.microsoft.com...
> Dear all,
> I use full-text search in SQL2005, but i discover it only support some
> document types (.doc \ .xls .. , select * from sys.fulltext_document_types
> ),i want to it also support PDF type, well then, how i can do it?
> by the way. I download a ifilter from adobe website and installed. but
> also can't support the pdf type.
You might get a faster answer if you re-post this to
microsoft.public.sqlserver.fulltext. They deal specifically with FTS
issues.

Friday, February 24, 2012

How to add a calculated field in query out-put?

Dear All,

I wanted include a calculated field (column) in my query output. how do i go about that?

Please help me.

SELECT amtcol1, amtcol2, amtcol1 + amtcol2 as 'Total'

or

SELECT amtcol1, amtcol2, 'Total' = amtcol1 + amtcol2