sorry all help does not work , if the value like this
table = test
magusageid playid msgtype mchangeprice
------------- --------- ---------- ---------------
35 6 a 400
36 8 a 450
======================================
and other question is if magusageid is use int IDENTITY(1,1)
how can i edit char in my magusageid which like this
magusageid playid msgtype mchangeprice
------------- --------- ---------- ---------------
A000_35 6 a 400
A000_36 8 a 450
sorry the question is
when i insert one row into this table and the data type my want to auto increase 1 to z
in sql i can use IDENTITY this data type to increas but the column of data use only number without char
so i need to know how can i use IDENTITY + char to save data into one row
thank's
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'm from Argentina. I'm not an expert at all in SQL, I know very little about it. I've read in MSDN Library that in order to edit a stored procedure I must right-click the procedure to modify, and then click Design.
I'm using SQL Server Management Studio (not Express) and I don't see any "design" option. I do see a "modify" option. I clicked there and modified just a number I wanted to. Once modified I clicked on the X button to shut the file hoping the system would ask me if I wanted to save the changes made. When I clicked "yes" the file saved into "my documents > sql management studio > proyects".
I checked the original Store Procedure file inside "Programmability > Stored Procedures" and obviously it wasn't altered. I have now a file called "SQLQuery31" in "my documents > sql management studio > proyects" that seems to have the modification made.
Why do I have it there (inside My Documents) instead of modifying the original one? What must I do to get that stored procedure modified?
Hi, I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time. thanks. varun
Because of testing and deletion, my table of user groups starts at 15 or so. Now I want to insert a group for administrators but I would like to have the groupID be 1. I tried to turn off the identity seed property and insert it manually but that didn't work.
What query should I run? 1. To determine whether a column is a rowguid or not using C# .NET 1.1 2. To add/modify column information and be able to set/change: - Primary key - Column Name - Data Type - Length - Allow Null - Default value - Precision - Scale - Identity - Identity Seed - Identity Increment - Row guid
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
I initially modified the column 'ID' from not allowing NULL to allow, and saved it. But now I will like to change it back to 'not allow'. But it gives me the following error message when I try to save my changes:
'Pages' table - Unable to modify table. Cannot insert the value NULL into column 'ID', table <databasename>.Tmp_Pages'; column does not allow nulls. INSERT fails. The statement has been terminated.
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
using sql database, i have a smallmoney column. when i enter an amount, 50.00 for example, i have 50.0000 displayed. is there a way to only have 50.00 displayed? same with the smalldatetime, is there a way to limit the display to "mm/dd/yyyy" without the "hh:mms am"
Trying to change some of the rows in a table specifically one column. column type is varchar ex. of data current desired $345,434.0 345434.0 (stripping out of $ & commas) 435.0 leave as is general txt leave as is
having trouble updating data in table tried using temp table, but update command make it so it won't see table
ex. piece of code update currency_conversion set currency_varchar = cast (cast (currency_varchar as money)as varchar) select * from currency_conversion where substring (currency_varchar from 1 for 1) = '$' ;
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?
I have a column that is a currently set as nvarchar(50) and is called DateEmployed. There are over a hundred rows that contain dates which is in nvarchar format.
This column now needs to be changed to a DateTime datatype. (Don't ask me it was not set to a dateTime when this was first designed - I wasn't here)
However, I have to change this column to a DateTime without destroying the data.
Is there any easy way to write some script or use studio management to change this.
Currently the data is displayed like this in this column dd/MM/yyyy i.e. 25/8/2007.
The method I am using to try and change this is by going to studio management clicking modify on the column and changing the datatype from a nvarchar(50) to a DateTime.
I get this following error message: - Unable to modify table. Arithmetic overflow error converting expression to data type datetime. The statement has been terminated.
I put a blank or ‘0’ in one of column of a text file and then I used BCP to load this file to a table of SQL server 6.5. The field in SQL server table is char type with size 1. After I run this process, all rows with this column received ‘0’ and no blank or null at a black value place. Could you please help me to fix this problem and make some of rows in the column get a blank or null value.
i'm going nuts with this, i suppose i will crack it eventually, but i thought i'd ask around here, seems like all the smart SQL Server guys hang out here
(i'm an SQL guy, not an SQL Server guy)
how does one place 5 spaces into a CHAR(5) column? create table testzeros ( id smallint not null primary key identity , myfield char(5) ) insert into testzeros (myfield) values (' 1') insert into testzeros (myfield) values (' 11') insert into testzeros (myfield) values (' 111') insert into testzeros (myfield) values (' 1111') insert into testzeros (myfield) values ('11111') insert into testzeros (myfield) values (' ')
select id , myfield , len(myfield) as L from testzerosno matter what i do, id=6 shows up with L=0, just like an empty string
i've even tried inserting 4 spaces and a non-blank character, which enters just fine, just as you would expect, but when i update the value and replace the non-blank character with a blank, all 5 spaces collapse back to an empty string
is there some kind of server setting like SET ALL_SPACE_EQUALS_EMPTY_YOU_IDIOT to OFF or something?
I have a column in my db which is char(40). There are entries in this with the same text, but some come up with a length of 24 and some 25. This sugggests to me that there is a blank trailing space. Am I right?
However, if I update it to iteslf using a Rtrim, nothing changes.
Is is something to do with ANSI padding?
How can I get these entries to be identical, please?
I have a char(11) for SSN, and I would like to default it to123-45-6789 so I can avoid having nulls in this column, and so I caneasily find the rows in which I need to have a 'correct' SSNentered/updated.I tried using just 123-45-6789, and SQL2005 doesn't seem to bedefaulting to this value, it seems to be keeping it as(((123)-(45))-(6789), and not placing it into this column when a newrow is created....Is there some special way I must specify defaults for a char(11) field(Yes, I will include the dashes).Thank you,Tom
I'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
I have created a DB and up until yesterday I have been using SQL commands to add/remove tables, columns, constraints etc. However today when I attempt to modify the datatype of a column using
alter table Person modify Gender nchar(2)
I get this error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'modify'.
I can still add colums but get the same error if I try to drop them and I can amend the tables in any way via the Design view so I don't think it's permission related.
Hello everyone, I have searched and seached for an answer to something that I know has to be simple but have been unsuccessful. I appreciate any help...
I am trying to take a char (6) column named col001 and convert it to datetime. The column is in mmddyy format. I am using SQL 2000, but have available sql 7.0 servers if there is a difference. I expect that I have to write a cursor but have been unable to get the correct syntax. Thanks everyone
Hi All, Is there a way by which we can modify the width of a column of a table which is being replicated without touching the ongoing transactional replication? This is for MSSQL2000 Transactional Replication.
I know (and successfully tried) that we can add a column to a table and that gets propaged to the replicate database and indeed the added column gets reflected there. How to add a column? sp_repaddcolumn or Right Click on the Publication-Properties and it shows a button to Add a Column.
This is what I have tried for modifying the width of a column of a table participating in Transactional Replication from varchar(10) to varchar(100)
MH (source) -> MH1 (Replicate)
The column €œcol1€? had width of varchar(10) and this was altered to varchar(100).
I have a file that contains internet addresses accessed by users. When I load this file into DTS I only want to insert into a table the first part of the address, ie,
I only want to insert the http://www.microsoft.com.au part of the address. Each address will be of varying lengths depending on the site visited, but still only want the first part. Up to the third instance of a /.
I have a table column can save english(single byte) or chinese(double byte) char, how to distinguish the records containing chinese(double byte) char thru sql command
I am working with Excel, then within Excel I am using MS Query to query a database. I am trying to use the CAST function on a field with numbers (1,2 or 3 digits) so I can convert it to a text value with three digits, i.e. 1 would read 001, 12 would read 012, etc.
I am not using CAST in the design grid. Is this even possible?
I am modifying the underlying SQL code. Here is the line that is giving me trouble:
CAST(GL02GLF.GLF_SEQ_NUM as CHAR(3)) as “Sequenceâ€
