How can I alter a userdefined datatype.
eg: A user defined datatype of Dt_Quantity numeric(16,0)
Want to change it to numeric(16,3).
Is it possible?
If it requires a system catelog updation please help.
This requirment is came after the implementation of the DataBase.
I Have table Emp contains 3 columns one column is EName that datatype is char. now I want to change char to Varchar(4) . but column is primary key . and it is refernced by othertable columns .
i write query
alter table emp alter column ename varchar(4)
but it throws an error is
Msg 5074, Level 16, State 1, Line 1
The object 'PK_emp is dependent on column 'Ename'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN ename failed because one or more objects access this column.
please help me
asap.
Regards
Swamy
I need to alter the datatype of a column from smallint to decimal (14,2) but the column was originally created with the following:
alter my_table
add col_1 smallintNot Null
constraint df_my_table__col_1 default 0
go
I want to keep the default constraint, but i get errors when I try to do the following to alter the datatype:
alter table my_table
alter column col_1 decimal(14,2)Not Null
go
Do I need to drop the constraint before I alter the column and then rebuild the constraint? An example would be helpful.
Thx
I am trying to change a column from a decimal(18,2) to a decimal(18,3). What is the SQL command to alter this table?
SQL Command:
alter table TableName
alter column ColumnName decimal(18,3) [null]
the above command is right ?
IF EXISTS (SELECT * FROM SYS.COLUMNS WHERE NAME =N'Case_Escalated__c' AND OBJECT_ID = OBJECT_ID(N'[Case]'))
begin
alter table [Case] alter column Case_Escalated__c bit null
print 'Case_Escalated__c altered sucessfully for [Case]'
end
Msg 1792, Level 16, State 1, Line 3
Alter table 'Case' failed because the added fixed column might cause existing data to go beyond the maximum allowable table row size of 8060 bytes.
Hello All,
How do i call a user defined function from within a stored procedure,
I have created a simple function which takes firstname and lastname as parameters and returns the concatenated name string.
That part works.
declare @fullname varchar(400)
@fullName=getFullName(@firstname,@lastname)
As always thanks for all your input
Hi All!This is the first time i am tryin to write a sql server 2000 function.I need to clean up some of my stored procedures..Can any one please give me skeleton for a userdefined function orcorrect my function. I get the following error.Select statements included within a function cannot return data to aclient.This returns a bit and it does a query to the database to decide thevalue of the bitCREATE FUNCTION GoodAd(@adid int,@currentdate datetime)RETURNS bitASBEGINdeclare @Return bitselect @return = 1if exists (select null from adqas where adid=@adid)select @return 0return @returnend
View 2 Replies View RelatedNeed to create a user defined role with grant permissions for below .
View Definition
Execute all Function
Grant View
Grant Synonym
dbo
View Definition
Not getting grant statements for above permissions.
I mean like below.
-----------------------------------------------------------------
CREATE ROLE [Role1]
GRANT EXECUTE ON SCHEMA ::dbo TO [Role1]
-----------------------------------------------------------------
I am developing a UDT using Hashtable as the member of the UDT and the UDT is defined as Format.UserDefined. After deployed in sql, I tried the UDT as below:
declare @_prodtype ProductSearch
SET @_prodtype = Convert(ProductSearch, 'Product,Certification;Region,China')
where ProductSearch is the UDT.
The error occurred:
Msg 6522, Level 16, State 2, Line 3
A .NET Framework error occurred during execution of user defined routine or aggregate 'ProductSearch':
System.Security.SecurityException: Request failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)
at ProductSearch.Parse(SqlString s)
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedNeed to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?
create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp
[Code] .....
Good afternoon,
I have an issue with an ssis variable datatype.
The scenario is as follows:
I have a stored procedure:
PROCEDURE [dbo].[sp_newTransaction]
@sourceSystem varchar(50),
@txOut NUMERIC(18,0) OUTPUT
AS
insert into scn_transaction (sourceSystemName) values(@sourceSystem);
SELECT @txOut = @@identity
Whose purpose is to perform an insert into a table and return me the identity value of the inserted record, which I'll then use throughout the rest of my package. The identity column in the inserted table is numeric(18,0).
I execute the stored proc with the following sql with an OLE DB connection manager:
exec sp_newTransaction ?, ?
The first parameter is a string variable from earlier in the package, and the second is the output parameter. I have the following parameter mappings to the execute sql task:
User:ystxId output numeric 1 -1
User:ourceSys input varchar 0 -1
The proc is correctly called, and the row insesrted, however I get a type conversion error when SSIS attempts to map the return parameter to my package variable... I've tried all sorts of combonations, and can't seem to get it to execute.
At one point I wasn't returning a numeric, but rather an int from the stored proc, and all was well until I went to use the variable in a derived column later in the package, and the type was converted quite incorrectly (a 1 was 77799789080 or some such), indicating a type conversion error likely related to the encoding of the number.
I'd like to keep the datatypes as numeric and make ssis use those - any pointers are greatly appreciated as to what type my package variable should be to allow proper assignment of a sql server numeric type to it.
Thanks much,
B
Database is SQL Server 2000
I have a field in a table that stores date of birth. The field's datatype is char(6) and looks like this: 091703 (mmddyy). I want to convert this value to a datetime datatype.
What is the syntax to convert char(6) to datetime?
Thank you in advance.
Hi,
I imported a table from Accees to SQL 7 with data in it.
I need to modify one of the datatype columns to "datetime" from nvarchar.
I tried to convert it manually, in SQL Server Enterprise Manager tool, but it gave me an error.
I also tried, creating another column "DATE2-datatype:datetime" and updating the column with the old one.
UPDATE users SET DATE2 = DATE.. But it also faild,..
How can I modify the column?
Thank you.
I would like to add an Identity to an existing column in a table using astored procedure then add records to the table and then remove the identityafter the records have been added or something similar.here is a rough idea of what the stored procedure should do. (I do not knowthe syntax to accomplish this can anyone help or explain this?Thanks much,CBLCREATE proc dbo.pts_ImportJobsas/* add identity to [BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL/* add records from text file here *//* remove identity from BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] NOT NULLreturnGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOhere is the original tableCREATE TABLE [ItemTest] ([BarCode Part#] [int] NOT NULL ,[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Description] DEFAULT (''),[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]DEFAULT (0),[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]DEFAULT (0),[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT(0),[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT(getdate()),CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED([BarCode Part#]) ON [PRIMARY]) ON [PRIMARY]GO
View 2 Replies View RelatedI am using sql server ce.I am changing my tables sometimes.how to use 'alter table alter column...'.for example:I have table 'customers', I delete column 'name' and add column 'age'.Now I drop Table 'customers' and create again.but I read something about 'alter table alter column...'.I use thi command but not work.I thing syntax not true,that I use..plaese help me?
my code:
Alter table customers alter column age
HI,I have a table with IDENTITY column with the datatype as INTEGER. Nowthis table record count is almost reaching its limt. that is totalrecord count is almost near to 2^31-1. It will reach the limit with inanother one or two months.In order to avoid the arithmentic overflow error 8115, we would likechange the datatype from INT to BIGINT. we hope this will solve ourproblem.How do I approch this datatype conversion?. Since the data count ishuge, that leads to a long down time of database.we need better approach or solution for this problem?. kindly give mea better solution that will reduce the total downtime of the productiondatabase.?.Regards
View 1 Replies View Related
Hi guys..
i have so doubts in my mind and that i want to discuss with you guys... Can i use more then 5/6 fields in a table with datatype of Text as u know Text can store maximu data... ? acutally i am trying to store a very long strings values into the all fields. it's just popup into my mind that might be table structer would not able to store that my amount of data when u use more then 5/6 text datatypes...
and another thing... is which one is better to use as data type "Text" or "varchar(max)"... ?
if any article to read more about these thing,, can you refere to me...
Thanks and looking forward.-MALIK
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have SQL Server 7.0 running on both development and production boxes. The syntax below runs fine on my development box, but I am getting an error on my production box. Thanks for your help
ALTER TABLE SUPPORTINFO
ALTER COLUMN STORENUMBER VARCHAR(20)
Error Message:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'COLUMN'.
Hello everyone.
I want to make a stored procedure that alters another stored procedure.
Is there a way to do it?
I need to to be able to execute a string as sql:
Something like
execute statement 'ALTER PROCEDURE ....'
What is the ALTER statement for ?
for example in some stored procedures
you have
ALTER PROCEDURE (procedure name..)
Hi
I'm trying to write a trigger to insert data into an archive file. I added a new trigger using database explorer, wrote the trigger and then saved it. The trigger has an error in it and I need to alter it. Can you tell me how to access the trigger ?
Many thanks
Chris
In my SQL SERVER 2005, sa login is enabled, despite Windows Authentication mode being set.
When I'm trying to change it to disabled, I get the error "cannot alter 'sa' login. it doesn't exist or you don't have the permission"
How can I change the status?
Hello:
I have a database in SQL with the following collate name: SQL_Latin1_General_CP1_CI_AS... I am trying to change the accent sensetive to accent insensitive... how would I do this? I tried re-installing the SQL and setting the default to CI_AI, but since the database that is backed up uses CI_AS, the DB settings overrides the default settings...
Any suggestions?
Is there a way to add a column to an existing table and do it all in C#If my query string is as follows how do I execute the query?ALTER TABLE interests ADD COLUMN Swim VARCHAR(1) NOT NULL DEFAULT('n')ThanksMoonWa
View 2 Replies View RelatedHello,
I tried to do this myself, but couldn't figure it out since I am very unfamiliar with sql. I have a script that exports data from our store database to a file that is used to update our webstore database. I want to add a chunk to the script that will copy an image file from one directory to another as it loops through the database. I want to copy a picture from directory1 to directory2 that has a name SKU.jpg.
I was told in this forum by Ray Miao that the format of the statement would be:
xp_cmdshell 'Copy c:directory1SKU.jpg c:directory2SKU.jpg'
My problem is that I don't know how or where in my script to place this statement so that it will work. If this is simplke and someone can tell me the setup that would be great. If not, I would be willing to hire someone to do what needs to be done to this script and send it to me. I can be reached at darren@jbjgifts.com or 217-369-2686.
Here is the script:
-------------------------------------------------------------------
use TAMDATA
go
set NOCOUNT on
/* GET SKUS THAT ARE NOT ON SALE HERE */
select s.sku SKU,
s.Description Description,
CONVERT(DECIMAL(9,2),s.SURetail) Price,
null Weight,
Replace(Replace(w.commentary,CHAR(13)+ CHAR(10),'<BR>'),Char(39),'') MarketingDescription,
'Yes' Taxable,
v.Company+' '+c.description SoftCartCategory,
null SoftCartTemplate,
/*lower('template_'+REPLACE(v.company,' ','_')) SoftCartTemplate,*/
null VendorNo,
null ListPrice,
null Graphic,
null Thumbnail,
null SoftCartAttributes,
ISNULL(s.vendstock,'none listed') vin,
/*CONVERT(DECIMAL(6,0),(sl.OHUnits-sl.PRUnits-LYUnits-2)) InventoryQuantity,*/
CONVERT(DECIMAL(6,0),(sl.OHUnits-sl.PRUnits-LYUnits)) InventoryQuantity,
v.company vn,
cl.description cl,
d.description dt,
c.description ct,
ISNULL(sz.description,'none listed') Size1,
ISNULL(clr.description,'none listed') Color,
' ' OnSale,
'N' OnSaleNow
from SKU S,
VENDORS V,
CATEGORIES C,
DEPARTMENTS D,
CLASS CL,
SKU_LOCATION SL,
SKU_WEB_INFO W,
SIZES SZ,
COLORS CLR
wheres.skuid=sl.skuid and
s.skuid=w.skuid and
s.vendorid=v.vendorid and
s.categoryid=c.categoryid and
s.deptid=d.deptid and
s.skuid=sl.skuid and
s.classid*=cl.classid and /* May not be a Class Outer Join */
s.size1id*=sz.sizeid and /* May not be a Size Outer Join - presume only first size is used */
s.colorid*=clr.colorid and /* May not be a Color Outer Join */
/*(sl.OHUnits-sl.PRUnits-sl.LYUnits-2)>0 and */ /* available must be > 2 */
(sl.OHUnits-sl.PRUnits-sl.LYUnits)>0 and
w.PublishToWeb=1 and
sl.location=1 and /* JBJ has only 1 Location */
(s.SalePrice=0 or s.saleprice is null
or getdate() < s.SaleStartDt or getdate() > s.SaleEndDt )
Union /* Pulls the two result sets together */
/* GET SKUS THAT ARE ON SALE HERE */
select s.sku SKU,
s.Description Description,
CONVERT(DECIMAL(9,2),s.SalePrice) Price,
null Weight,
Replace(Replace(w.commentary,CHAR(13)+ CHAR(10),'<BR>'),Char(39),'') MarketingDescription,
'Yes' Taxable,
v.Company+' '+c.description SoftCartCategory,
null SoftCartTemplate,
null VendorNo,
null ListPrice,
null Graphic,
null Thumbnail,
null SoftCartAttributes,
ISNULL(s.vendstock,'none listed') vin,
/*CONVERT(DECIMAL(6,0),(sl.OHUnits-sl.PRUnits-LYUnits-2)) InventoryQuantity,*/
CONVERT(DECIMAL(6,0),(sl.OHUnits-sl.PRUnits-LYUnits)) InventoryQuantity,
v.company vn,
cl.description cl,
d.description dt,
c.description ct,
ISNULL(sz.description,'Not Applicable') Size1,
ISNULL(clr.description,'Not Applicable') Color,
'On Sale! Normally $'+CONVERT(VARCHAR(13),s.suretail) OnSale,
'Y' OnSaleNow
from SKU S,
VENDORS V,
CATEGORIES C,
DEPARTMENTS D,
CLASS CL,
SKU_LOCATION SL,
SKU_WEB_INFO W,
SIZES SZ,
COLORS CLR
wheres.skuid=sl.skuid and
s.skuid=w.skuid and
s.vendorid=v.vendorid and
s.categoryid=c.categoryid and
s.deptid=d.deptid and
s.skuid=sl.skuid and
s.classid*=cl.classid and /* May not be a Class Outer Join */
s.size1id*=sz.sizeid and /* May not be a Size Outer Join - presume only first size is used */
s.colorid*=clr.colorid and /* May not be a Color Outer Join */
/*(sl.OHUnits-sl.PRUnits-sl.LYUnits-2)>0 and /* available must be > 2 */*/
(sl.OHUnits-sl.PRUnits-sl.LYUnits)>0 and
w.PublishToWeb=1 and
sl.location=1 and /* JBJ has only 1 Location */
(s.SalePrice>0 or s.saleprice is not null) and
getdate() between s.SaleStartDt and SaleEndDt /* Presumes this export will be run daily be Darren */
go
--------------------------------------------------------------------
Thanks,
Darren
Hi smart people!
I would like to know how to alter a column to have a default value. For instance I have a column AreaCode Char(3) in a table. I have data in the table and now I want to add a default value of '123' to the AreaCode column.
I tried the following but did not work.
Alter Table Phone
Alter Column AreaCode Char(3) Default '123'
Can we even do it using SQL?
Thanks
I think I have blown a gasket..... a elementary simple sp that will not work but I am unable to find an explanation as to why..... please help.
CREATE PROCEDUREbrsp_table_alteration
@var_1 varchar(45)
,@var_2varchar(45)
,@var_3varchar(30)
AS
ALTER TABLE @var_1
ADD@var_2@var_3NULL
Thanks.
Is there any way to alter a column's name using Transact SQL and not the GUI interface?
View 1 Replies View RelatedDo anyone knows the syntax for changing the name of a column in a table with the
alter statement or any other statements????
Thanks in advance,
Vic
Hi
Is it possible to remove Identity property of a column using ALTER statement in SQL Server 7.0.
Thanks in advance
Rahul
I am trying to move one of my database log to different location but I am geeting an error when I try to do this.
I am moving Registration DB log file from 'c:mssql7
egistrstion_log.ldf'
to 'e:sql
egistration_log.ldf'
Here the syntax I am using to do this:
alter database registration modify file (name='registration_log',filename= 'e:sql
egistration_log.ldf')
But I get the following error:
Server: Msg 5037, Level 16, State 1, Line 2
MODIFY FILE failed. Do not specify physical name.
Thank You,
John