hi,
i have a DB and it has some tables that the tables has related link (diagram).now when i wanna to change a table's field , the Sql Server errors that the table is not empty.when i try to delete the table's content , Sql server errors that the table is use a relation with another table.
so can i change a table's structure?
by the way before i forget , the Sql Server's error is below:
'UserManagement' table
- Unable to modify table.
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'isadmin' cannot be added to non-empty table 'UserManagement' because it does not satisfy these conditions.
I have a table that I need to add an identity field to. I created a field in the table as an INT and added values to all of the existing records. When I try to change it to an IDENTITY field I continually get an error saying 'Invalid cursor state' . The help function tells me that this is caused by not having enough space in the transaction log but I don't understand this b/c the trans log is configured to expand as needed. Anyone know how I can do this?
I am new to SQL Server 2005 and I am trying to add two fields to an existing table. The table has 15 Million records in it and the save is not completing. How do I add the new fields?
I have a query that I need a hand on. I am trying to add togther somefiends based on values of another.What I would like to add a billing total by saying more or less thefollowing:SELECT labor_hours, labor_cost, expidite_fee, flat_rate,include_repair_cost, include_cal, include_flat_rate, include_parts,cur_bill,(labor_hours * labor_cost) AS labor_total,(ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BYdateCAL DESC),0)) AS cal_total,(ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROMrepair_partsID WHERE orderID=79559),0) +ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROMmisc_part_assocID WHERE orderID=79559),0)) AS parts_total,((labor_hours * labor_cost) + expidite_fee + flat_rate +ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BYdateCAL DESC),0) +ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROMrepair_partsID WHERE orderID=79559),0) +ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROMmisc_part_assocID WHERE orderID=79559),0)) AS actual_total,(expidite_feeIF include_repair_cost = 1+ (labor_hours * labor_cost)IF include_flat_rate = 1+ flat_rateIF include_cal = 1+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDERBY dateCAL DESC),0)IF include_parts = 1+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROMrepair_partsID WHERE orderID=79559),0) +ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROMmisc_part_assocID WHERE orderID=79559),0)) AS billing_totalFROM view_inventoryWHERE orderID=79559I know the IF part is whacked, that's where I need the help. Is thistype of thing even possible? Or even efficent? Is it wise to subqueryfor totals (not like I have a choice based on the applicationrequirements)? help.
I have a table with some fields. break_mon, lunch_mon, dinner_mon, ent_mon,break_tue, lunch_tue, dinner_tue, ent_tue, .....etcI want to output the sum of the four monday columns as well as the sum ofthe four tuesday columns.I did this withSELECT break_mon+lunch_mon+dinner_mon+ent_mon AS mon_tot,break_tue+lunch_tue+dinner_tue+ent_tue AS tue_totFROM expense_reportI now want to add mon_tot and tue_tot AS total, but SQL is telling me thatit can not find mon_tot. Is there a way to do this? Thanks!DarrenMCP
I am attempting to add additional fields and data to the default users database that is created as a result of enabling roles on my website. Is it possible to add additional data fields to this file? Where can I find the commands to do this?
EX: I have a table for products, and each product has a quantity. How can I add up the QTY field in all the rows to find out the total QTY of all the products.Any help would be greatly appreciated.gkc
We have three fields in a table: firstname, Surname & Organisation. Firstname & surname will always be filled but in most cases organisation is NULL.
Part of what these fields will be used for is a mailshot. If there is no organisation data then the mailshot will open with 'Dear Firstname Surname, ' but if the organisation is present they would like 'Dear Organisation'.
Is it possible to create a select state that checks the organisation field, and if a value is present return that value else return the firstname, surname combination? I have tried various things but I cannot get it to work.
I know the ideal situation would be to do this type of condition check at scripting level (PHP, ASP, Visual Basic) but my bosses would like to try it at SQL level.
This is driving me crazy! The SQL Statement refenced is shown at the end of this email.
When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?
SQL View.......
SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,
Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,
Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR
(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total
How can I create a script that updates a table with new fields withoutlosing the data in the table (i.e., without dropping and recreating thetable)?Thanks.
I have an existing table which has about 70 columns with 3 million rows in it. I was asked to add additional 50 new columns into the table. I have tried to add them in through the Enterprise manager design table but experiencing some problems. The adding process seemed never going to be end. Is there any good efficient way to do it??? I appreciate the help!
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure: Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK) Categories: - CategoryID (int, PK) - CategoryName (varchar) I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query: CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID) But this gave me the error: Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns. I tried converting the Content to a varchar(max) within my view but it still didn't like. Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu". can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like: UPDATE "TABLENAME" SET DATAFIELD1 = DATAFIELD2 WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
Hi people My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?
hey i have a very simple form here: www.syscpupower.com I would like to have the information from the form saved to a sql db with a time stamp. here is the code i have for the page below:"C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
I have a details view with several parameters set up in my asp.net 2.0 code, I want to add a parameter before the sql parameter is executed. I need to use the find control of the details view because I am using items/edit item templates in my details view control. I tried this(see below) as well as the detailsview item command event args to no avail. It doesn't see the other parameters that have already been declared in my asp.net code. I don't want to have to declare all my varibles that are already in my asp.net code. I just want to add another parameter. Sub InsertNew(ByVal sender As Object, ByVal e As DetailsViewInsertEventArgs) _ Handles dvEvents.ItemInserting Dim dvr As DetailsViewRow For Each dvr In dvEvents.Rows Dim CatIDup As Integer = CType(dvr.FindControl("ddlCat"), DropDownList).SelectedValue sdsevents.InsertParameters.Add("evCatID", CatIDup)sdsevents.Insert()
Looking for any help someone can offer on adding an image to SQL.Working on what will hopefully be quite a big site and the user will be asked for some details, one of which will be an image (jpg, gif or bmp).Rather than add the image to a folder on the server, a folder which could end up being quite large, I was hoping someone could give me some help, suggestions or even point me towards a tutorial on how to store the image in an SQL database.Any helps appreciated, thanks :)
Hi Everyone, I have a page with a textbox and a dropdown list. The user will enter a company name in the text box and select a number from 1 - 20 (number of delegates for that company) in the dropdown list. I've got the text box and dropdown writing to tblCompany but I would also like it to write to tblUsers at the same time. The reason for this is that i need it to set up the number of users that have been selected in the dropdown list. Here is the codebehind file:Imports System.Data.SqlClient Imports System.Web.Configuration
Partial Class cms_Management_Company_NewCompany Inherits System.Web.UI.Page
Dim companyName As String Dim companyActive As Boolean Dim companyArchived As Boolean Dim companyDelegates As Integer
Dim userForeName As String Dim userSurname As String Dim userEmail As String Dim userUsername As String Dim userPassword As String Dim userActive As Boolean Dim userTypeID As Integer Dim companyID As Integer
Dim i As Integer Dim NoLoops As Integer
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnSave.Click Dim conString As String = WebConfigurationManager.ConnectionStrings("General").ConnectionString
Dim con As New SqlConnection(conString) Dim cmd As New SqlCommand("INSERT INTO tblCompany (CompanyName, CompanyActive, CompanyArchived, CompanyDelegates) VALUES (@CompanyName, @CompanyActive, @CompanyArchived, @CompanyDelegates)", con)
Using con con.Open() cmd.ExecuteNonQuery() con.Close() End Using
Dim con2 As New SqlConnection(conString) Dim cmd2 As New SqlCommand("INSERT INTO tblUsers (UserForeName, UserSurname, UserEmail, UserUsername, UserPassword, UserActive, UserTypeID, CompanyID) VALUES (@UserForeName, @UserSurname, @UserEmail, @UserUsername, @UserPassword, @UserActive, @UserTypeID, @CompanyID)", con2)
cmd2.Parameters.AddWithValue("@UserForeName", userForeName) cmd2.Parameters.Item("@UserForeName").Value = "First Name - Delegate 1"
Using con2 con2.Open() For i = 1 To NoLoops cmd2.ExecuteNonQuery() Next i con2.Close() End Using
Response.Redirect("~/cms/Management/Company/Company.aspx") End Sub
End ClassThe other thing I am not sure of is getting the ID of the new company and assiging it to the delegates in tblUsers (to associate them with the new company)I hope this makes sense.Thank you very much guys.Scott.
I am trying to insert a value numeric + 1 in to db table but i get error when i do this this is the code Const SQL As String = "INSERT INTO [PageHits] ([DefaultPage]) VALUES (@defaultP)"Dim myCommand As New Data.SqlClient.SqlCommand(SQL, myConnection)myCommand.Parameters.AddWithValue("@DefaultP" + "1", DefaultP.Text.Trim()) myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close() The Error: Must declare the variable '@defaultP'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@defaultP'.
i want to add vales in a query this is my scenario (how can i add the price column values and display as one record) ex. 222 17 table = table1 tep1 price (column) 222 5 222 2 222 10
