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.
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;
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.
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
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.
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).
The following was not written by me but I need to modify it and I am definitely no expert in SQL. It returns a list of files that a user is allowed to access based on whether or not they belong to a specific user group and that group has been granted access to the file.
FROM AnswerFile AS a INNER JOIN User AS u ON a.UserID = u.ID
WHERE (a.UserID = 2) AND (a.ID NOT IN SELECT AnswerFileID FROM GroupAnswerFile))
I need to modify this code to add an additional check. I have added a file that allows specific users to be granted access to files. It is called AnswerFileUser and contains AnswerFileID (the file) and UserID (the user that has been given access). I need the query to return as part of the results those files where a user has been given access regardless of their group or group access. I hope this all makes sense. Thanks for your help!
How do you change the time out value for a specific connection? I'm having problems when I connect to SQL Server from my website and I want to test if this would help.
How do I modify data in a sqlDataSource object (like inserting records, editing, deleting) similiary to how I modify data in a GradView using a DataList or repeater? I want to be able to have my own EditTemplate and be able to edit say four fields while I'm only viewing like two of them in the datalist/repeater. <asp:DataList ID="DataList1" runat="server" DataKeyField="GameId" DataSourceID="SqlDataSource1" RepeatColumns="1"> <ItemTemplate> <%# Eval("field1") %><%# Eval("field2") %>.... </ItemTemplate> <EditTemplate>......</EditTemplate>
When I'm trying to add a edit-button (<asp:Button runat="Server" ID="EditButton" Text="Edit" CommandName="Edit" />) and click it nothing happens....What is missing?
I have a DTS was created by another programmer which is for move online Database to a offline Database and now I have to modify it the DTS package have 1 Source DB and 63 distinateDB between SourceDB and DistinateDB are "Black Line" and almost of DistinateDB have a green line between a Task ( excute SQL work) -- some of SQL work are Delete Table and some are Truncate Table and now I have to add some limits for the DTS 1. First, move all the Data From online Database to Offline Database 2. to Delete Data from online DB , Online Database will only keep Data for 6 months ( older than 6 months have to delete except table A and table B) can you help me? or tell me where have a reference information I can take a look... have no idea how to modify this DTS package..
I write a Sql Query for select UPdateTime between 2007/09/01~ 2007/09/30 select * from ZT_Master where TDateTime < DateAdd(Month,-1,GetDate()) the result return all the column.. why?
I have the following query that produces this: 1 0 Arts & Entertainment6 1 Arts & EntertainmentDance2 1 Arts & EntertainmentMovies9 0 Automotive10 0 Business11 0 Cancer12 0 Communications3 0 Computers13 3 ComputersE-Learning4 3 ComputersHardware14 3 ComputersJava16 3 ComputersLink Popularity17 3 ComputersMicrosoft.net15 3 ComputersRSS5 3 ComputersSoftware7 0 Real Estate8 7 Real EstateFinance Here is the query: SELECT tblArticleCategory.ACategoryID, tblArticleCategory.AParentID, ISNULL(Parent.ParentCategory + '', '') + ISNULL(tblArticleCategory.ACategoryname, '') AS ACategory FROM tblArticleCategory FULL OUTER JOIN(SELECT ACategoryName AS ParentCategory, ACategoryID FROM tblArticleCategory AS tblArticleCategory_1 WHERE (AParentID =0) AND (AActive = 1)) AS Parent ON Parent.ACategoryID = tblArticleCategory.AParentID WHERE (tblArticleCategory.AActive = 1) ORDER BY ACategory How do I modify this so that just the category name is returned and not the parent cetegory. In other words, everything shows up just once. Arts and Entertainment, once, then on the next line Dance, then Music then the next parent category which is automotive. Here is the table: ACategoryID int UncheckedACategoryName nvarchar(150) CheckedADescription nvarchar(300) CheckedAParentID int CheckedAActive bit Checked Unchecked
When you first come into the web page I want to exam the querystring and the modify the SQL Statement accordingly in a SQLDatasource.What is the right event of the SQLDataSource to modify the SQLDataSource.SelectCommand = MySQLString property?Thanks
I have a problem with a publication which is a transactional replication on SQL Server 7.0 . I want to add stored procedure to the existing publication along with the tables . But the Enterprise manager does not allow to select or check the box against the required stored procedure although it displays when i right click on the publication and select proeprties .
Can i have some help in this regard ? Does anybody knows to add a stored procedure by means of a query .
Is there any other way to modify publication in SQL Server 7.0 although it is possible in SQL Server 2000 .?
1) Mothly :- -payday is equal to @today -pay frequency is = 1 -payday between 1-31
2) Weekly -payday is between 1-5 ( 1 =Monday,2 =Tuesday,3=Weds,4=Thurs,5=Friday) -payfrequency is = 2
3) Fortnightly -after two weeks -payfrequency =3
How do l modify this procedure to be able to extract using the listed conditions ? Need help
CREATE Procedure Collections_Cats AS BEGIN
Declare @today int Set @today = (SELECT Day(GETDATE()))
DECLARE Collections_Cats_Cursor CURSOR FOR SELECT distinct n.loan_No AS Loan_No, n.customer_No AS Customer_No, c.first_name AS First_name, c.second_name AS Second_name, c.surname AS Surname, c.initials AS Initials, b.Bank_name AS Bank_name, br.branch_code AS Branch_code, d.bank_acc_type AS Bank_acc_type, pay_sheet.pay_frequency AS Pay_Frequency, n.monthly_Payment AS monthly_Payment, pay_sheet.payday AS payday FROM Transaction_Record tr
INNER JOIN Loan n ON tr.loan_No = n.loan_No INNER JOIN Customer c ON n.customer_No = c.customer_no INNER JOIN Bank_detail d ON c.customer_no = d.customer_no INNER JOIN Branch br ON d.Branch = br.Branch INNER JOIN Bank b ON br.Bank = b.Bank INNER JOIN pay_sheet ON c.customer_no = pay_sheet.customer_no WHERE Pay_sheet.Payday = @today AND pay_sheet.pay_frequency =1
Order by l.loan_No
OPEN Collections_Cats_Cursor
-- Perform the first fetch. FETCH NEXT FROM Collections_Cats_Cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM Collections_Cats_Cursor END
CLOSE Collections_Cats_Cursor DEALLOCATE Collections_Cats_Cursor END; GO
i have a replication job going on for one particular table from one server to another now i have added two new columns in source and replication works fine. once i add these two new columns in destination the replication is failing.
i went to properties of the publisher and select the filter columns where i can find all the columns checked except the two new columns when i try to select these two its not responding pls help me in this.
I am using Sql server 2005, i am trying to find out all the tables that modified recently. i cant find any last modified date!!.
My situation is; i have altered so many tables, usually when i do alter i save the sql file for doing the same alteration in the production database. but somehow i lost the scripts and i am not sure what are the tables that i have modified and what is new.
I have converted Access database to sql express. Access Database had AutoNumber FIelds for which trigger was generated by Upsizing wizard.
Now when I import data from client the autonumber field value changes because trigger is fired which distroys all links
I want to modify trigger so that it generates new number only when it is not supplied in a insert command.
Please help. Code is given below. Also suggest how to save because when I use save, it asks for a new .sql file name and a new file is generated instead of modifying the same trigger
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[T_AcControlLimit_ITrig] ON [dbo].[AcControlLimit] FOR INSERT AS SET NOCOUNT ON DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */ /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'IntAcControlCode' */ SELECT @randc = (SELECT convert(int, rand() * power(2, 30))) SELECT @newc = (SELECT IntAcControlCode FROM inserted) UPDATE AcControlLimit SET IntAcControlCode = @randc WHERE IntAcControlCode = @newc
Hi all,I have been left to modify an old unique index, which no longer works,as the two fields, which were used, are no longer unique. I’ve found twoothers that are unique. But as I’m new to this I need some advice first.What happens if one table inserts from another (the second containingthe index). However if there is a duplicate will the records which arenot duplicates be inserted? How could I insure this?ThanksSam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I am trying to create a report with unknown number of report items. (as I dont know how many columns the stored proc is going to return).Is there a way to create these type of reports using SSRS other than creating the rdl programatically using vb.net or c#?
HiI have developed a sql procedure for updating some xml content using SQL 2005 xQuery features.For example:Declare @xmlContent = '<books><book name="asp"/>< book name = "sql"/></books>'--Now I want to add attribute "book_id" to the xml content.SET @xmlContent.modify('insert attribute book_id{"1" } into (/books/book[@name="asp"])[1] ')SET @xmlContent.modify('insert attribute book_id{"2" } into (/books/book[@name="sql"])[1] ')SELECT @xmlContent--OUTPUT will be<books><book name="asp" book_id="1"/><book name="sql" book_id="2"/></books>QUESTIONQ1. Do we have any extension or plugin such that this SQl 2005 xquery feature can be incorporated in SQL 2000?Q2. If the answer for Q1. is no (as I expect), then do we have any TSQL feature in SQL 2000 for modifying xml content?I found all examples in SQl 2000 related with "openxml" function which performs the select kind of operations only. Like selecting data in XML format OR converting xml data in to tabular format. But I couldn't find any xml modification TSQL in SQl 2000 which can add element/attributes or change values.one workaround could be to consider xml content as string and use string functions to modify the content.But is there any more sensible approach available in SQL 2000?
Hi, Below is the SP that I want to modify it such that it does another thing apart from what its doing now. I want to to delete all the records corresponding to the BpDate value before this insert happens. by meaning deleting all the records, I meant to say delete all records where BpDate = @BpDate and SiteCode = @SiteCode.
