How to shorten column length of nvarcha type in Transact-SQL?

When i use alter table alter column,i can lengthen the length of a column of nvarchar type.

But when i use the same way to shorten column length,it issue an error.

I know how to do this in Enterprise Manager,but not in Transact-SQL.If you make the change in Enterprise Manager but, innstead of saving your changes, click on the "Save Change Script" button on the toolbar and it will give you the T-SQL you need to make this change.

In this instance you will see that an ALTER COLUMN is not issued. Behind the scenes the table is actually dropped and recreated.

-Jamie|||You can use ALTER TABLE...ALTER COLUMN to reduce the length of the column as long as the data in your column will fit. See Books Online for more details. In general, you do not want to rely on the UI to make schema changes since it doesn't do the operation in the most efficient way and lot of cases it drops & recreates the table which is resource intensive.|||Thanksql

How to share user-defined data type in different database?

Hi,everyone.

I have defined a data type "OC_BUN_NAME" in database "CVPRO". I want to create a table tempdb.dbo.CVPRO in SQL SERVER 2005 system Database tempdb. But SQL SERVER 2005 DBMS gives a Error Messages:"Can not find the data type OC_BUN_NAME".

How can I do? How to use data types in the other database?

Please give me some advice. Thank you in advance.Hi
You should define the same data type in tempdb.
Also it would be good to define it in model database.
The tempdb is destroyed and recreated (from model) after each SQL Server restart. So if you define your data type in model database you do not have to redefine it in tempdb each time you reboot the server or restart SQL Server service.

Regards,
Kris Zywczyk|||Follow Kris, and also FYI,
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm (http://vyaskn.tripod.com/sql_server_administration_best_practices.htm)|||Thank you,Kris Zywczyk.
Thank you, rudra.

However, after I define my data types in MODEL system databse, if I create a new database, the defined data types will automatically be added into the new database. I hope it can not happen. How can I do?

Thank you!|||Why do you want to create any tables in tempdb?|||Good lord. Don't even touch tempdb.
And in my opinion, don't bother with user-defined datatypes. They have little purpose.|||In SQL Server 2005, we can create a table in tempdb database so that different users can get access to data in the table. At the same time, when the SQL Server Services stop, tables created in tempdb database will be automatically destroyed. So in our project we want ot create a table in the tempdb database. However when we create the table, we can not use user-defined data type(the data types are defined in database "CVPRO"). Please give me some help.

Thanks!|||Hi
If you want to use user-defined data type you need to define it in current database (database where you create the table).

Can't you use standard tables in your 'CVPRO' database and delete it when it is not used?
You can always create the SQL Server Agent job dropping not needed tables and schedule it to run daily at i.e. 11 PM...

What exactly you want to do?

How to setup Replication with Access

hi all,
I want to setup replication on Access Database.. I want data will be updated
in SQL Server from Access database..
1) Which replication type will be best suited (merge or transactional)
2) How I will setup subscription on Access database. Whenever I try to
create a push subscription I only found option to create subsription on SQL
Server database. Although I have checked "Hetrogeneous datasources such as
Access" option when creating publication.
any suggesstion or link to article will be highly appreiciated..
Thanks
Ansari
Hi all
I succeeded in creating push subscription to an access datatbase. But
whenever I snapshot agest want to apply initial snap shot the process fails
and give me the following errors
"The process could not initialize 'MsJet'. Check to see if the component is
registered correctly"
any sugestion
Ansari
"Ansari" <mudasar_ansari@.yahoo.com> wrote in message
news:O6I4OqSpEHA.1160@.tk2msftngp13.phx.gbl...
> hi all,
> I want to setup replication on Access Database.. I want data will be
updated
> in SQL Server from Access database..
> 1) Which replication type will be best suited (merge or transactional)
> 2) How I will setup subscription on Access database. Whenever I try to
> create a push subscription I only found option to create subsription on
SQL
> Server database. Although I have checked "Hetrogeneous datasources such as
> Access" option when creating publication.
> any suggesstion or link to article will be highly appreiciated..
> Thanks
> Ansari
>
>

How to setup Link Server properly

Hi,

I want to access another database seating on another server so I configured the Linked server with SQL Server as the server type. I set the security as NT_AUTHORITY\SYSTEM and checked the Impersonate. In Server Options, I set the RPC Out to true then I clicked ok.

I got this error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB Provider "SQLNCLI" for linked server "server1" reported an error. Authentication failed.

Cannot initialize the datasource object of OLE DB Provider "SQLNCLI" for linked server "server1".

OLE DB Provider "SQLNCLI" for linked server "server1" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)

Note that my NT login is added as "Administrators" on the Server i want to connect to.

Have I missed anything?

cherriesh

I tend to use sp_addlinkedserver and sp_addlinkedsrvlogin for this.

http://msdn2.microsoft.com/en-us/library/ms190479.aspx

http://msdn2.microsoft.com/en-us/library/ms189811.aspx

The trick is to specify 'true' for @.useself for sp_addlinkedsrvlogin.

e.g.

Code Snippet

exec sp_addlinkedsrvlogin 'remotesrv', 'true'

how to set up unique combinational constraint

Hi I have a table and I do not want to allow entries if two column value
match, for example
table 1
id type name value
int varchar20 varchar20 int
1 test paul 2
if name is paul and value is 3 allow insert
if name is paul and value is 2 do not allow insert.
I am trying to do this with under the index/keys menu, not quite sure how to
do it and if this is correct.
Thanks.
Paul G
Software engineer.
If you just want to prevent the combination from being inserted into the
table, you can use an INSERT trigger. If you want the condition to always
hold for all the rows in the table, you can create a CHECK constraint on the
table.
Linchi
"Paul" wrote:

> Hi I have a table and I do not want to allow entries if two column value
> match, for example
> table 1
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> if name is paul and value is 3 allow insert
> if name is paul and value is 2 do not allow insert.
> I am trying to do this with under the index/keys menu, not quite sure how to
> do it and if this is correct.
> Thanks.
> --
> Paul G
> Software engineer.
|||Consider putting a UNIQUE constraint on (name, value)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:23467935-67F3-418E-A24E-200CE74DD637@.microsoft.com...
If you just want to prevent the combination from being inserted into the
table, you can use an INSERT trigger. If you want the condition to always
hold for all the rows in the table, you can create a CHECK constraint on the
table.
Linchi
"Paul" wrote:

> Hi I have a table and I do not want to allow entries if two column value
> match, for example
> table 1
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> if name is paul and value is 3 allow insert
> if name is paul and value is 2 do not allow insert.
> I am trying to do this with under the index/keys menu, not quite sure how
to
> do it and if this is correct.
> Thanks.
> --
> Paul G
> Software engineer.
|||I think I need the check constraint, I do not want any duplicates in the
table using both columns.
for example
id type name value
int varchar20 varchar20 int
1 test paul 2
2 test1 paul 3 ok
3 test2 tom 2 ok
4 test3 tom 3 ok
5 test4 tom 2 **do not allow this
entry**
Paul G
Software engineer.
"Linchi Shea" wrote:
[vbcol=seagreen]
> If you just want to prevent the combination from being inserted into the
> table, you can use an INSERT trigger. If you want the condition to always
> hold for all the rows in the table, you can create a CHECK constraint on the
> table.
> Linchi
> "Paul" wrote:
|||Actually, you want a UNIQUE constraint on (name, value):
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
I think I need the check constraint, I do not want any duplicates in the
table using both columns.
for example
id type name value
int varchar20 varchar20 int
1 test paul 2
2 test1 paul 3 ok
3 test2 tom 2 ok
4 test3 tom 3 ok
5 test4 tom 2 **do not allow this
entry**
Paul G
Software engineer.
"Linchi Shea" wrote:

> If you just want to prevent the combination from being inserted into the
> table, you can use an INSERT trigger. If you want the condition to always
> hold for all the rows in the table, you can create a CHECK constraint on
the[vbcol=seagreen]
> table.
> Linchi
> "Paul" wrote:
how to[vbcol=seagreen]
|||ok thanks. Since I do not care if they are unique in a single column but
need to look at both columns together would I need to change the statement or
would the statement below still work
thanks?
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
column1 column2
paul 1 ok
paul 2 ok
paul 1 do not allow this entry match(value
in column1 and column2 matches value in column1 and column2 in previous
entry).
Paul G
Software engineer.
"Tom Moreau" wrote:

> Actually, you want a UNIQUE constraint on (name, value):
> alter table MyTable
> add
> constraint UK_MyTable UNIQUE (name, value)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
> I think I need the check constraint, I do not want any duplicates in the
> table using both columns.
> for example
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> 2 test1 paul 3 ok
> 3 test2 tom 2 ok
> 4 test3 tom 3 ok
> 5 test4 tom 2 **do not allow this
> entry**
>
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
> the
> how to
>
>
|||The code I gave you is what you need, since it specifies that the uniqueness
is to be applied to the combination of both columns.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D044B7E2-3B9A-4B19-84A9-AADDA4AEBE2A@.microsoft.com...
ok thanks. Since I do not care if they are unique in a single column but
need to look at both columns together would I need to change the statement
or
would the statement below still work
thanks?
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
column1 column2
paul 1 ok
paul 2 ok
paul 1 do not allow this entry match(value
in column1 and column2 matches value in column1 and column2 in previous
entry).
Paul G
Software engineer.
"Tom Moreau" wrote:

> Actually, you want a UNIQUE constraint on (name, value):
> alter table MyTable
> add
> constraint UK_MyTable UNIQUE (name, value)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
> I think I need the check constraint, I do not want any duplicates in the
> table using both columns.
> for example
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> 2 test1 paul 3 ok
> 3 test2 tom 2 ok
> 4 test3 tom 3 ok
> 5 test4 tom 2 **do not allow
this[vbcol=seagreen]
> entry**
>
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
always[vbcol=seagreen]
> the
value
> how to
>
>
sql

how to set up unique combinational constraint

Hi I have a table and I do not want to allow entries if two column value
match, for example
table 1
id type name value
int varchar20 varchar20 int
1 test paul 2
if name is paul and value is 3 allow insert
if name is paul and value is 2 do not allow insert.
I am trying to do this with under the index/keys menu, not quite sure how to
do it and if this is correct.
Thanks.
--
Paul G
Software engineer.If you just want to prevent the combination from being inserted into the
table, you can use an INSERT trigger. If you want the condition to always
hold for all the rows in the table, you can create a CHECK constraint on the
table.
Linchi
"Paul" wrote:
> Hi I have a table and I do not want to allow entries if two column value
> match, for example
> table 1
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> if name is paul and value is 3 allow insert
> if name is paul and value is 2 do not allow insert.
> I am trying to do this with under the index/keys menu, not quite sure how to
> do it and if this is correct.
> Thanks.
> --
> Paul G
> Software engineer.|||Consider putting a UNIQUE constraint on (name, value)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:23467935-67F3-418E-A24E-200CE74DD637@.microsoft.com...
If you just want to prevent the combination from being inserted into the
table, you can use an INSERT trigger. If you want the condition to always
hold for all the rows in the table, you can create a CHECK constraint on the
table.
Linchi
"Paul" wrote:
> Hi I have a table and I do not want to allow entries if two column value
> match, for example
> table 1
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> if name is paul and value is 3 allow insert
> if name is paul and value is 2 do not allow insert.
> I am trying to do this with under the index/keys menu, not quite sure how
to
> do it and if this is correct.
> Thanks.
> --
> Paul G
> Software engineer.|||I think I need the check constraint, I do not want any duplicates in the
table using both columns.
for example
id type name value
int varchar20 varchar20 int
1 test paul 2
2 test1 paul 3 ok
3 test2 tom 2 ok
4 test3 tom 3 ok
5 test4 tom 2 **do not allow this
entry**
Paul G
Software engineer.
"Linchi Shea" wrote:
> If you just want to prevent the combination from being inserted into the
> table, you can use an INSERT trigger. If you want the condition to always
> hold for all the rows in the table, you can create a CHECK constraint on the
> table.
> Linchi
> "Paul" wrote:
> > Hi I have a table and I do not want to allow entries if two column value
> > match, for example
> > table 1
> >
> > id type name value
> > int varchar20 varchar20 int
> > 1 test paul 2
> >
> > if name is paul and value is 3 allow insert
> > if name is paul and value is 2 do not allow insert.
> >
> > I am trying to do this with under the index/keys menu, not quite sure how to
> > do it and if this is correct.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||Actually, you want a UNIQUE constraint on (name, value):
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
I think I need the check constraint, I do not want any duplicates in the
table using both columns.
for example
id type name value
int varchar20 varchar20 int
1 test paul 2
2 test1 paul 3 ok
3 test2 tom 2 ok
4 test3 tom 3 ok
5 test4 tom 2 **do not allow this
entry**
Paul G
Software engineer.
"Linchi Shea" wrote:
> If you just want to prevent the combination from being inserted into the
> table, you can use an INSERT trigger. If you want the condition to always
> hold for all the rows in the table, you can create a CHECK constraint on
the
> table.
> Linchi
> "Paul" wrote:
> > Hi I have a table and I do not want to allow entries if two column value
> > match, for example
> > table 1
> >
> > id type name value
> > int varchar20 varchar20 int
> > 1 test paul 2
> >
> > if name is paul and value is 3 allow insert
> > if name is paul and value is 2 do not allow insert.
> >
> > I am trying to do this with under the index/keys menu, not quite sure
how to
> > do it and if this is correct.
> > Thanks.
> > --
> > Paul G
> > Software engineer.|||ok thanks. Since I do not care if they are unique in a single column but
need to look at both columns together would I need to change the statement or
would the statement below still work
thanks?
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
column1 column2
paul 1 ok
paul 2 ok
paul 1 do not allow this entry match(value
in column1 and column2 matches value in column1 and column2 in previous
entry).
--
Paul G
Software engineer.
"Tom Moreau" wrote:
> Actually, you want a UNIQUE constraint on (name, value):
> alter table MyTable
> add
> constraint UK_MyTable UNIQUE (name, value)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
> I think I need the check constraint, I do not want any duplicates in the
> table using both columns.
> for example
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> 2 test1 paul 3 ok
> 3 test2 tom 2 ok
> 4 test3 tom 3 ok
> 5 test4 tom 2 **do not allow this
> entry**
>
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
> > If you just want to prevent the combination from being inserted into the
> > table, you can use an INSERT trigger. If you want the condition to always
> > hold for all the rows in the table, you can create a CHECK constraint on
> the
> > table.
> >
> > Linchi
> >
> > "Paul" wrote:
> >
> > > Hi I have a table and I do not want to allow entries if two column value
> > > match, for example
> > > table 1
> > >
> > > id type name value
> > > int varchar20 varchar20 int
> > > 1 test paul 2
> > >
> > > if name is paul and value is 3 allow insert
> > > if name is paul and value is 2 do not allow insert.
> > >
> > > I am trying to do this with under the index/keys menu, not quite sure
> how to
> > > do it and if this is correct.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.
>
>|||The code I gave you is what you need, since it specifies that the uniqueness
is to be applied to the combination of both columns.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D044B7E2-3B9A-4B19-84A9-AADDA4AEBE2A@.microsoft.com...
ok thanks. Since I do not care if they are unique in a single column but
need to look at both columns together would I need to change the statement
or
would the statement below still work
thanks?
alter table MyTable
add
constraint UK_MyTable UNIQUE (name, value)
column1 column2
paul 1 ok
paul 2 ok
paul 1 do not allow this entry match(value
in column1 and column2 matches value in column1 and column2 in previous
entry).
--
Paul G
Software engineer.
"Tom Moreau" wrote:
> Actually, you want a UNIQUE constraint on (name, value):
> alter table MyTable
> add
> constraint UK_MyTable UNIQUE (name, value)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:65E03382-8002-4FCE-8531-6EA764838D75@.microsoft.com...
> I think I need the check constraint, I do not want any duplicates in the
> table using both columns.
> for example
> id type name value
> int varchar20 varchar20 int
> 1 test paul 2
> 2 test1 paul 3 ok
> 3 test2 tom 2 ok
> 4 test3 tom 3 ok
> 5 test4 tom 2 **do not allow
this
> entry**
>
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
> > If you just want to prevent the combination from being inserted into the
> > table, you can use an INSERT trigger. If you want the condition to
always
> > hold for all the rows in the table, you can create a CHECK constraint on
> the
> > table.
> >
> > Linchi
> >
> > "Paul" wrote:
> >
> > > Hi I have a table and I do not want to allow entries if two column
value
> > > match, for example
> > > table 1
> > >
> > > id type name value
> > > int varchar20 varchar20 int
> > > 1 test paul 2
> > >
> > > if name is paul and value is 3 allow insert
> > > if name is paul and value is 2 do not allow insert.
> > >
> > > I am trying to do this with under the index/keys menu, not quite sure
> how to
> > > do it and if this is correct.
> > > Thanks.
> > > --
> > > Paul G
> > > Software engineer.
>
>

How to set up search on text stored in an image type of data?

I am saving large text document in an image type of column in a SQL Server 2000 table.

How will I set up searching of words/ phrases for data stored in this column?

what type of documents are they? If it is just plaintext you really should store it in an nvarhcar(max) field instead. That way you can use SQL queries to search the text.

|||

But nvarchar has a length problem. If it's a word document that is stored and this doc's length is greater than varchar max size, then we have to use image or ntext datatype. Because of this I have to use image data type.

|||

That's going to be a challenge because the image data type is storing a byte array so i'm not sure if it is possible to search it without first pulling it down and manipulating it in your code behind.

how to set the default value

I have database in sql server. One field called datesent, i would like to set the default value as now(), i just type in the default value in database design, it didn't work. Please help, many thanks.try this: getdate()|||It works, thank you very much!|||I use the getdate() as the default value. But i found the value like "
11/8/2005 10:14:00 AM. How can i set the default value like the format as:
11/8/2005. Thanks.|||hi,

try this

create table yourTable
(
ColumnName datetime default convert(varchar,getdate(),107)
)

How to set precision of a decimal number

there is a column which type is float in a table, i want to set the precision of its value, for example if its value is 10.333888, i want to get its value as 10.33, how to complete it in a select Sql?

thks

You can change yourcolumn to numeric or decimal data type, for example, decimal(18,2) or numeric(18,2), which will return 10.33 in your case.

You can look up the difference between numeric and float data type from Books Online which covers everything you need for SQL Server.

HTH

|||thks for HTH, i have solved my problem with your method, thks again

How to set a default value for a parameter - Fromperiod 7 ToPeriod from script

I have two parameters called FromPeriod & ToPeriod of type String. It
is actually a parameter for a dimension calendarYearMonth.
The default value of ToPeriod I want is 2006009 for the current month
which is Sept 2006 and I want FromPeriod to be 2005009 which is current
date - 365.
I want the default value to be derived from the current system date and
assign the current year and current month concatenated together to
FromPeriod & ToPeriod
Appreciate if you could kindly share the approach to implement this.
Thanks
KarenCreate a new dataset which can either select the current system date using
getDate() or can calculate the full text string you are after based on
getDate().
In the parameters window reached through the report menu, set the default
value to derive from query and reference the new dataset there.
Hope that helped.
"KarenM" wrote:
> I have two parameters called FromPeriod & ToPeriod of type String. It
> is actually a parameter for a dimension calendarYearMonth.
> The default value of ToPeriod I want is 2006009 for the current month
> which is Sept 2006 and I want FromPeriod to be 2005009 which is current
> date - 365.
> I want the default value to be derived from the current system date and
> assign the current year and current month concatenated together to
> FromPeriod & ToPeriod
> Appreciate if you could kindly share the approach to implement this.
> Thanks
> Karen
>