I have a table that has integrity constraints in place and it is populated with the data. I need to rename one of the columns in the table. I am hesitant to use sp_rename procedure because when I used it before I get a warning message that says "changing any part of an object name could break scriptd and stored procedure." how big of a problem is it? Is there any other way to do it without hearting anything? Thanks.
ie if i havex 1 2 3 4 5------------1| a b c d e2| f g h i j3| k l m n o4| p q r s t5| u v w x yand i want to change it tox 1 2 3 4 5------------1| a b c d e2| a b c d e3| a b c d e4| a b c d e5| a b c d eany ideas on how to do that?
Does anybody know how I can change the default value for a column?I was trying to remove the default value in order to add the new oneafterwards. This is what I tried:alter table /table-name/ drop default for /column-name/alter table /table-name/ alter column /column-name/(/new-decl-without-default/)It did not work. I cannot find a solution in the documentation. Maybeyou can help me out?Thank you,Johan
Hi All,I am trying to change column name on an existing table. I am using SQLServer 7.As the table is quite big, it is taking quite long time to do it.By the way I could change the column name only through the EnterprizeManager.Is it possible to change the column name using SQL script?Why the change of column name will depend on the size of the table?Thanks for your answer.-Mokles
Hi. Is there a way to remove a gap between identity column values? Some records were deleted and now we have identity column values that jump all over the place.
I want to change a column's data type from bit to int. There are data in the table already. I'm wondering if it is save/correct way to issue the following command to change the data type for that column.ALTER TABLE database_tableALTER COLUMN my_bit_column INT; Thanks.
Is there any way or method to CHANGE the DATATYPE of a column in a published table being used for transactional replication (MSSQL 2000), WITHOUT DROPPING THE SUBSCRIPTION ????
Im stuck in this mess and do have the option to drop the subscription, alter the table, create the subscription and rerun the snapshot or to recreate it by Manual Synchronisation either.
Can anyone help? Has anyone been across this dilemma before and have troubleshooted the problem? If yes, help is much appreciated.
MY PROBLEM: ~~~~~~~~~~~~~ 'MyTable' is currently being published and has subscriptions to it. The PRIMARY KEY column 'id' has an Identity property as well. 'id' is of datatype smallint, however because of bad planning, i now need to change that datatype to an integer to support a larger range WITHOUT DROPPING SUBSCRIPTIONS. I CANT DROP THE COLUMN EITHER AS IT IS BEING THE PRIMARY KEY COLUMN.
IS THERE ANY OTHER WAY I CAN DO TO ARCHIEVE MY GOAL? THANKYOU.
I have a problem with a union all that seems to be changing the results of a column based on using the union, when I run the querys separatly I get the expected results in the field!
Basically 3 queries pulled together for reporting purposes.
Orders (which can include a gift so the gifts are separated in the second query). OrderGifts (pulled out of the orders). Gifts (straight gifts with no associated orders).
The problem is showing up in the first ORDERS query. I can pull a specific order and the calculation for the AMOUNT(eg:$15.20) is correct, but when I combine the tables I get a different AMOUNT(eg:$15.199999999999999).
Here are the values for the fields in the amount calc: totitems=12.9500 totcredits=.0000 discount=.0000 tax=.0000 refund=.0000 convrate=1.0 postage=2.2500 giftamt=15.0000
I tried converting each of the fields to an decimal befre the calc and same results
I tried ROUND and same thing!
I have been trying to chase this down all day, cant figure out what the problem is or how to get around it...whats going on here I am missing?
Here is the query, if you need any other info just let me know, I would GREATLY appreciate ANY direction of figuring this out!
select * from ( --orders select acctnbr as PartnerId, 'O' as TrnType, ordnbr as TrnId, 0 as FundId, odate as WorkDate, adate as PostDate, sdate as ShipDate, cast( ( IsNull(totitems, 0) - IsNull(totcredits, 0) - IsNull(discount, 0) - IsNull(tax, 0) - IsNull(refund, 0) + IsNull(postage, 0) ) * IsNull(convrate, 1) as Decimal(10,2)) as Amount, batchnbr as BatchId, motvcode as MotivationCode, currcode as CurrencyType, Status, paycode as PaymentType, paytype as CardType, paynbr as CardNbr, expire as CardExpire, ChargeAuth, ConvRate, MediaCode, hcf.dbo.KmaiProjMotvTest(0,motvcode) as ProjMotvName from kmai.dbo.o01omst
UNION all
--orders gifts select acctnbr as PartnerId, 'G' as TrnType, giftref as TrnId, giftfundid as FundId, odate as WorkDate, null as PostDate, null as ShipDate, ROUND((giftamt * IsNull(convrate, 1)),2) as Amount, batchnbr as BatchId, giftmotvcode as MotivationCode, currcode as CurrencyType, Status, paycode as PaymentType, paytype as CardType, paynbr as CardNbr, expire as CardExpire, ChargeAuth, ConvRate, MediaCode, hcf.dbo.KmaiProjMotvTest(giftfundid,giftmotvcode) as ProjMotvName from kmai.dbo.o01omst where adate is null and giftfundid <> ''
UNION ALL --gifts select acctnbr as PartnerId, 'G' as TrnType, gh.giftref as TrnId, convert(int, fundid) as FundId, gdate as WorkDate, applydate as PostDate, null as ShipDate, ROUND((IsNull(gd.amt, 0) * IsNull(convrate, 1)),2) AS Amount, batchnbr as BatchId, motvcode as MotivationCode, currcode as CurrencyType, Status, paycode as PaymentType, paytype as CardType, paynbr as CardNbr, expire as CardExpire, ChargeAuth, ConvRate, MediaCode, hcf.dbo.KmaiProjMotvTest(fundid,motvcode) as ProjMotvName from kmai.dbo.g03ghdr gh inner join kmai..g04gdtl gd on gh.giftref = gd.giftref
Dear All,I'm trying to alter the name of several columns' in a table which gets created in a stored procedure.trying to use:exec sp_rename '#tblBd.week1', '2007_18', 'COLUMN'I get:Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.There is no mistype, the table name and column name are correct.Can temp table's column names not be altered?If yes, how?Thanks in advance!
I need to change the size of a column of a table from char(255) to char(500). I used the line:
alter table table_name alter column column_name char(500)
When I run that command, I get a message that it was sucessful. However, when I try to enter data into the changed column, the number of characters I can enter is still 255. I check the information schema for the column and the 'character_maximum_length' field is 500.
What is the problem here? Is the maximum allowable length for char 255? How can I get a column to have 500 characters?
I am trying to make the following update: All the columns are fine except for the 'name' column. datatype for 'name' column in the target table is varchar(30) and the datatype for 'name' in the sourcetable is varchar(40), I cannot change the datatype of the column 'name' to varchar(40) because I am told it may affect performance. what I want to do is just update the first 'name' column of the target table by the first 30 characters of the source table column 'name'
I am using the following query, is it possible to do it or are there any other ways I can update the column without changing the datatype?
MERGE INTO [S].[dbo].[AF_Copy] AS TargetTable USING (SELECT source_code, name, addr1, city, zip FROM [D].[D_TEST].[dbo].[SO_Copy]) AS SourceTable ON ([TargetTable].[Code] = [SourceTable].[source_code])
I was wondering how to modify a column's properties, like its datatype or length.
I know I'm supposed to do something like this: ALTER TABLE MyTable ALTER COLUMN MyColumn int(20)
My main concern is, what will the database do to all the data in the column, if the column used to be, say nvarchar(50)? Will I lose the data in the column if I change the datatype? And what if I had data in this column that was longer than 20 characters? Will the data now be truncated? What can I do to make sure that nothing happens to the data once I change the datatype?
Is it possible to change the format of the identity column? i.e. I have and integer value that will be the ID for the records to follow in the database. Instead of the values being 1,2,3,4,5,6,7,8,9 etc I want them still to be in sequence but I want the format to be 001,002,003,004,005,006,007,008,009 etc.
Is it possible to do that in SQL when you're creating the table?
This subject has been posted several times, but I haven't seen a goodanswer.Problem:I want to change the order of the columns in a table using T-SQL only.Explanation:After running your code, I want to see the following table...CREATE TABLE [dbo].[TableName] ([First_Column] [int] NULL ,[Second_Column] [varchar] (20) NULL) ON [PRIMARY]look like this...CREATE TABLE [dbo].[TableName] ([Second_Column] [varchar] (20) NULL ,[First_Column] [int] NULL) ON [PRIMARY]Limitations:Don't post if your post would fall in the following categories:1. If you don't think it can be done2. If you think Enterprise Manager is the only way to do this3. If you think I should just change the order of my Selectstatements4. If you want to state that order column doesn't matter in arelational database5. If you want to ask me why I want to do thisWish:Hopefully the answer WON'T involve creating a brand new table, movingthe data from old to new, dropping the old table, then renaming thenew table to the old name. Yes, I can do that. The table I'm workingwith is extremely huge -- I don't want to do the data juggling.Thanks in advance!
I am having difficulty constructing a where clause. I have 4 columns in a table. Col1 is never NULL. Col2,3,4 may or may not contain NULL values. Here is the situation.
1. Col2,3,4 are NULL WHERE Col1 = condition
2. Col2 is NULL, 3 may or may not be NULL. When Col3 is NOT NULL, Col4 may or may not be NULL
where col1 and (col3(Not Null values) OR Col4(Not Null values)) = condition.
I must increase column (filed) size in existing datebase but without usingEnterprise manager....(Becouse we use MSDE on our clients PCs)The Filed is part of primary and foreign key constraints....And every constraint has diferent index number in each database...for example (PK_something_9e382hjl8), and I don't know how to pick thisvalue before "drop constraint" command.....Thank you very much....
First let me say, I really can't believe this chain of events myself--and they are happening to me.
I am upgrading several DTS packages to SSIS on what will be my new production server. These packages create tables, export them to a flat file, and ftp them off to other locations.
What is happening (on the SSIS side) is that the OLE DB Source is reordering some of the columns on its own (moving them to the end of the table/file. Then when my pickup/load routines run, the data is out of place and they fail.
Can anyone please explain what is happening here with the mapping. I have evaluated the table and the columns are in the order that I expect. When I preview the source table in the OLE DB Source Editor the columns are in the correct order/alignment, but when them in the OLE DB Source Editor --Columns section within BIDS the order is changed arbitrarily.
I have been somewhat successful (2 out of 3) in being able to re-map the data, but this last table just doesn't want to change.
Thanks in advance for any help and/or information you can provide
Hi I have a table, which contains Char and NChar NOT NULL columns Now I need to change it to NULL, when I use the following command, it fails for the following error,
The command I used, ALTER TABLE <TableName> ALTER COLUMN <ColName> CHAR NULL ALTER TABLE <TableName> ALTER COLUMN <ColName> NCHAR NULL
Msg 8152, Level 16, State 13, Line 1 String or binary data would be truncated. The statement has been terminated.
But for the same table, the below command executes fine, ALTER TABLE <TableName> ALTER COLUMN <ColName> SMALLINT NULL
Also I can change the NULLABILITY from NOTNULL to NULL using Enterprise Manger, editing the table using Table Design and selecting Allow Nulls option.
Hello,i have a table and if a record is inserted i will test a numeric valuein this table. If the this value is greather as 1 million, than anstatus column should be changed from 'A' to 'B'.Yes and sorry, this is a newbie question.On Oracle this work simple:create trigger myTrigger on tableXasbeginif :old.x 100000 then:new.y:='B'end if;end;ThanksMaik
I'm working with merge replication between Sql Server 2005 and Sql Server 2005 Mobile. I'm using dynamic filtering by function HOST_NAME (... where Table.FilteredColumn = HOST_NAME())
When I'm trying to chagne values from filtered column on the client side (Table.FileteredColumn) I get error message that the column is read-only.
How can I change data in filtered column? Is this possible?
I need a trigger to know who and when a char(1) column is changed. Would like to write the audit trail to its own table I can query and record before and after values.
DDL:
CREATE TABLE [dbo].[Test]( [Customer] [varchar](12) NULL, [Active] [char](1) NULL DEFAULT ('N') --Must use char 1 b/c more than 2 possible values )
Insert into Test (Customer, Active) Values ('Acme','Y')..I want trigger to tell me whowhenwhere this value was changed. If using sql auth capture client windows id if possible and write to audit table Update Test set Active = 'N'
I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?
I read in another thread that "It is not possible to create packages or new objects within packages using SSIS." I also read in the books online that "The input and the input columns of the Excel destination have no custom properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong. So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)
Our company wants to allow our customers the ability to import employee data. Each customer's employee data changes depending on things like organisation structure etc. so the format of the data to be imported needs to change. We can do a lot of this with dynamic SQL, but are looking at moving it to SSIS as we think it will save us a lot of pain later on and for other features in SSIS.
We're stuck at a fairly early part of the process as we don't know how to dynamically import our spreadsheet. So far our best idea is to connect to the excel connection as the only step in the data task and then to use a script task on the control task, write code to connect to the excel source (set the excel source to not treat the first row as headers so we can do a 'select top 1 *...' ) then create a dynamic sql command to create the table.
Once this is done we would then have another data task that actually puts the data into the newly created table. This all sounds very difficult though. What are the options for doing what we want to do... have a feeling that we're missing something basic.
I build my SSIS package based on the above file.But now i receive files with different columns order let say
lastName,FirstNamr,Address l1,f1,a1 L2,f2,a2 or Address,FirstName,LastName a1,f1,l1 a2,f2,l2
every time i receive multiple files in different order and i have to remap all my mappings. These are just a few columns and i have like 20 columns and the order can potentially change any time. so every time i have build new packages remap them etc.
through normal c# code it pretty easy. I tried to add script here but the script also needs a source and mapping so there is also a mapping issue. Is there a better way to do this.
in the following procedure i want to pass in a value and as a result of the value I want to change the actual body of the query:
example, I pass in the value 'TS2' and I now want my query to see if TS2MIN < 70
i think I am doing it right, but it is throwing an error saying that "Conversion failed when converting the varchar value 'TS2MIN' to data type int."
any idea on what i am doing wrong?
ALTER proc SPU_YearlyTemp 905,'TS2',70,254,2007 @SITEID INT, @SENSOR VARCHAR(10), @MIN INT, @MAX INT, @YEAR INT
AS DECLARE @S AS VARCHAR(12) SET @S=( SELECT CASE @SENSOR WHEN 'TS1' THEN 'TS1MIN' WHEN 'TS2' THEN 'TS2MIN' WHEN 'TS3' THEN 'TS3MIN' WHEN 'TS4' THEN 'TS4MIN' WHEN 'TS5' THEN 'TS5MIN' WHEN 'TS6' THEN 'TS6MIN' WHEN 'TS7' THEN 'TS7MIN' WHEN 'TS8' THEN 'TS8MIN' END AS SENSORVALUE)
select top(10)* into #tempMin from sitecontroldataarchive where siteid = @SITEID and @S < @MIN --this is where the error occurs, SELECT * FROM #tempMin
I have added a Slowly Changing Dimension transformation to an SSIS package and have launched the Wizard to edit it. After selecting the source (a SQL Server 2005 instance), if I select a very large table (9+ million rows), I'm encountering two strange behaviors: 1. The wizard hangs for several minutes before displaying the columns from that table. 2. The wizard does not display the primary key column. This, of course, is the column I want to designate as the "business key", but can't because it's not displayed. I know this is more like a fact table than a dimension, but this is not a data warehouse. This is just a very large table, and I need to update a field in certain records based on the contents of a source text file. Is there another transformation I should use to perform updates?
