Which Database Object Is Updating One Table's Coumn?
Jun 6, 2006
Hi all,
We have sql sever 2000 as our backend database system. on the front, we have vb medical application screen for users to do the data entry.
for 7th screen, it is assoicated with table Case_Custom. user enter data througth 7th screen to populate Case_Custom table.
There is one column called Case_Custom.CaseID ( a foreign key to to parent table Cases) was populated automatically instead of user input.
However, I could not find which stored procedure, views or trigger or application code to populate this field? I have queried INFORMATION_SCHEMA.ROUTINES, dbo.syscomments and did not find anything updating Case_Custom.CaseID.
I have contacted our app vendor and have got any answer from them yet.
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing: dbo.Categories dbo.CustomerCustomerDemo dbo.CustomerDemographics dbo.Customers dbo.Employees dbo.EmployeeTerritories dbo.Order Details dbo.Orders dbo.Products dbo.Regions dbo.Shippers dbo.Suppliers dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing: dbo.Categories New Table... Modify Open Table Script Table as |> CREATYE To |> DROP To |> SELECT To |> INSERT To |> New Query Editor Window File.... Clipboard UPDATE To |> DELETE to |> From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance, Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist.
How do I insert data that I have collected in a local database onto a table on my online ie hosted database which is on a different server?
At the moment I am just uploading all the data to the hosted DB but this is wasting bandwith as only a small percentage of data is actually selected and used.
I thought that if i used a local DB and then update the table on my hosted DB this would be much more efficient, but I am not sure how to write the SQL code to do this!
I have created a sql login account called "webuser" and has given public role in my database. In my asp.net application i build connection string using above account and its password . We give permission on store procedure for for the above account to execute .We dont give table level permission for the above account . When we run the application with the above settings it runs fine on test server . However Now i have transfered the databse object to live server with its permissions . Now while I executing the aspx page , I am getting above error . I have checked that the store procedure has execute permission for webuser account and dbo(i.e SA) has all the permissions for all database objects . Still why i am getting error ? (Please note , the thing is working fine in test server)
alter table userphotos add constraint IX_userid unique (userid)
and got the following error:
Quote: Msg 2714, Level 16, State 4, Line 3 There is already an object named 'IX_userid' in the database. Msg 1750, Level 16, State 0, Line 3 Could not create constraint. See previous errors.
I previously created the same constraint in a different table (using IX_userid). Is it not allowed to use the same naming scheme for constraints in different tables? What am I doing wrong?
Hi,Using VB.net I have created a custom object (hope my terminology is correct here), it’s just a class that defines a few string, integer, and hash table variables. After creating an instance of this class and populating it with data I need to be able to store this instance of my object in a sql server data base table. How do I do this? I saw an article that used the image data type to achieve this (code was in java unfortunately) is this the correct approach. Could you please give me some code examples Thanks
In the master database for a SQL Server instance I noticed the dt... objects (procedures, etc.) in create date of today about 7 minutes before I looked in sysobjects. No changes have been made to that instance for months. Also in other cases I noticed the same thing just different dates for each instance. Any ideas on why the dates would change?
Hi,Facts:I created a database to support an application that tracks events ondifferent objects. The two main tables are tbl_Object andtbl_EventLog. Each table has unique ID and on the tbl_EventLog thereis FK for a record in the tbl_Object. The events are inserted all thetime for the same or different objects from the tbl_Object. There areabout 600,000 objects in the tbl_Object and 1,500,000 (and growing)events in tbl_EventLog.Question:The user often wants to know what the last event was for a specificobject.What is the best way of retrieving the last event?Should I simply do a max(eventdatetime) on a specific object? orShould I add a LastEventID column to tbl_Object and update it everytime a new event is inserted? or any other way to implement it?I chose the second method because I didn't think it made sense searchthe event table everytime the user wants to know the last event, but Iwanted to know what the experts thought.Please let me know what you think.Thank you,Oran Levin
When I run:select * from testtableI get this error message:Invalid object name 'testtable'.However, if I run:select * from testuser.testtableIt works. I'm logged in as testuser, and testuser is the databaseobject owner and tableowner. This was a restored database from adifferent server in SQL Server 2000. The testuser login was in theprevious database. I created the same login in this new test databaseand made it database owner.I've also tried running: sp_change_users_login 'Update_One','testuser', 'testuser'It completed successfully but I still get:select * from testtableInvalid object name 'testtable'.I've also tried creating a different user and making it database ownerand when I change table object owner to this new user, I still get thesame problem. I need to specify:select * from newtestuser.testtableEven though I'm logged in as newtestuser.I also then tried changing table owner to dbo. This seems to worklogged in as either user, but I want to have the tables owned by theuser not dbo. How can I resolve this?Any help would be appreciated.Erin
I have a select statement running on the client machine linking to different tables in 1 database. All with the same schema. When I ran it, i had this error. I had trial and error, removing 1 table at a time until i hit the one which is causing it. when i removed it, everything's ok. i just wonder if all the tables were using dbo schema what is causing this particular table to throw this error?
I am getting following error on "Table Import Wizard" of Tabular model Cannot update the 'Database' object 'Tabular Sample_', it needs to be part of a connected Server object.
Does any one has any clue for this error ? I did went through a lot of articles on this error but none helped . I am working in Visual studie 2005 and trying to upload image in sql database through a simple form. Here is the code
using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.Web.Configuration; using System.IO; public partial class Binary_frmUpload : System.Web.UI.Page {protected void Page_Load(object sender, EventArgs e) { }protected void btnUpload_Click(object sender, EventArgs e) {if (FileUpload.HasFile == false) { // No file uploaded!lblUploadDetails.Text = "Please first select a file to upload..."; } else {string str1 = FileUpload.PostedFile.FileName; string str2 = FileUpload.PostedFile.ContentType; string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString; //Initialize SQL Server Connection SqlConnection con = new SqlConnection(connectionString); //Set insert query string qry = "insert into Officers (Picture,PictureType ,PicttureTitle) values(@ImageData, @PictureType, @PictureTitle)"; //Initialize SqlCommand object for insert. SqlCommand cmd = new SqlCommand(qry, con); //We are passing Original Image Path and Image byte data as sql parameters. cmd.Parameters.Add(new SqlParameter("@PictureTitle", str1)); cmd.Parameters.Add(new SqlParameter("@PictureType", str2));Stream imgStream = FileUpload.PostedFile.InputStream; int imgLen = FileUpload.PostedFile.ContentLength;byte[] ImageBytes = new byte[imgLen]; cmd.Parameters.Add(new SqlParameter("@ImageData", ImageBytes)); //Open connection and execute insert query. con.Open(); cmd.ExecuteNonQuery(); con.Close(); //Close form and return to list or images.
} } }
Object reference not set to an instance of an object. 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.NullReferenceException: Object reference not set to an instance of an object.Source Error:
Line 32: Line 33: string str2 = FileUpload.PostedFile.ContentType; Line 34: string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString; Line 35: Line 36: //Initialize SQL Server Connection Source File: c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs Line: 34 Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.] Binary_frmUpload.btnUpload_Click(Object sender, EventArgs e) in c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs:34 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Hi, I have an application where I'm filling a dataset with values from a table. This table has no primary key. Then I iterate through each row of the dataset and I compute the value of one of the columns and then update that value in the dataset row. The problem I'm having is that when the database gets updated by the SqlDataAdapter.Update() method, the same value shows up under that column for all rows. I think my Update Command is not correct since I'm not specifying a where clause and hence it is using just the value lastly computed in the dataset to update the entire database. But I do not know how to specify a where clause for an update statement when I'm actually updating every row in the dataset. Basically I do not have an update parameter since all rows are meant to be updated. Any suggestions? SqlCommand snUpdate = conn.CreateCommand(); snUpdate.CommandType = CommandType.Text; snUpdate.CommandText = "Update TestTable set shipdate = @shipdate"; snUpdate.Parameters.Add("@shipdate", SqlDbType.Char, 10, "shipdate"); string jdate =""; for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++) { jdate = ds.Tables[0].Rows[i]["shipdate"].ToString(); ds.Tables[0].Rows[i]["shipdate"] = convertToNormalDate(jdate); } da.Update(ds, "Table1"); conn.Close();
Each time I press submit to insert data into the database I receive the following message. I use the same code on another page and it works fine. Here is the error:
Object reference not set to an instance of an object. 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.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 125: MyCommand.Parameters("@Balance").Value = txtBalance.Text Line 126: Line 127: MyCommand.Connection.Open() Line 128: Line 129: Try
[NullReferenceException: Object reference not set to an instance of an object.] CreditRepair.CreditRepair.Vb.Creditor_Default.btnSaveAdd_Click(Object sender, EventArgs e) in c:inetpubwwwrootCreditRepairCreditor_Default.aspx.vb:127 System.Web.UI.WebControls.Button.OnClick(EventArgs e) System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) System.Web.UI.Page.ProcessRequestMain()
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
If (Page.IsValid) Then
Dim DS As DataSet Dim MyCommand As SqlCommand
Dim AddAccount As String = "insert into AccountDetails (Account_ID, Report_ID, Balance) values (@Account_ID, @Report_ID, @Balance)"
MyCommand = New SqlCommand(AddAccount, MyConnection)
Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create procedure sp_myst_proc --- 1995/11/28 15:48 as declare @spidlowint .....
how to remove column heading from output ?
I am putting output to file so I don't want .... Text ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Is there any way i can find some certain fields in the whole database? like i know a column called Type_ID in table Sys_Type, how can i know there are same Type_ID column in other tables? Thanks in advance.
I've been playing around with the new data controls (DetailsView,FormView) and have been having problems when attempting to update a record that has a uniqueidentifier as its primary key.I get the error message: Object must implement IConvertible. 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.InvalidCastException: Object must implement IConvertible.I gather this is because there is a bug that has propagated from the beta version.One suggested work around is from http://64.233.183.104/search?q=cache:GDjA62POtgcJ:scottonwriting.net/sowBlog/archive/11162005.aspx+Implicit+conversion+from+data+type+sql_variant+to+uniqueidentifier+is+not+allowed.+Use+the+CONVERT+function+to+run+this+query.&hl=en The crux of the problem, it appears, is that the <asp:Parameter> value for the uniqueidentifier field is, by default, set to Type=�Object�. To fix this, simply remove the Type property altogether. That is, change the SqlDataSource parameter setting from something like: <asp:SqlDataSource ...> <InsertParameters> <asp:Parameter Name=�UserId� Type=�Object� /> ... </InsertParameters></asp:SqlDataSource> to: <asp:SqlDataSource ...> <InsertParameters> <asp:Parameter Name=�UserId� /> ... </InsertParameters></asp:SqlDataSource> This change worked for me; once the Type was removed the exception ceased and the updates/inserts worked as expected.Unfortunately this only partially worked for me as while it is fine for deletes it won't work for updates.If anyone can help shed any light on this I would greatly appreciate it.CheersMark
When I add a new item to project in VS2005: Project-->Add New Item --> Sql Database I got a error msg "object reference not set to an instance of an object" after click "Add" button. The SQL Server Express is running. How do I fix this problem?
I have an application providing me with multiple headers which I havemergerd into one big header (below), this header my not always be thesame but I need to be able to extract a periodstart and periodend fromit. The periodstart will always be the third substring from the end(or 3rd from right) and the periodend will always be the firstsubstring from the end (or 1st from the right).How can I extract the periodstart and periodend?E.g:- Header'Jensen Alpha TR UKN GBP BM: Caut Mgd BM (50% FTAllSh 50% ML £ BroadMkt) RF DEF:RFI 3Y 31/08/2004 To 31/08/2007'I currently have the sql: convert(Datetime,(dbo.FDHGetWord(@FullHeader, 20)) ,103) but this only works in thisinstance, I need to use someting like the RIGHT function or REVERSEfunction but I can't get the sql right.Can someone please help!????
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
Hi All, I have a Problem while updating one table data from another table's data using sql server 2000. I have 2 tables named TableA(PID,SID,MinForms) , TableB(PID,SID,MinForms) I need to update TableA with TableB's data using a single query that i have including in a stored procedure.
An error has occurred during report processing. (rsProcessingAborted) Query execution failed for data set 'TestID'. (rsErrorExecutingCommand) For more information about this error navigate to the report server on the local server machine, or enable remote errors
The log file reads:
---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.
***Background***
General Users got an error message when trying to access any reports we have created. All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection) For more information about this error navigate to the report server on the local server machine, or enable remote errors
I'll add this from the report logs...
w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed. Login failed for user €˜DOMAINUsername€™.
The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.
I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)
I need to grant select, insert, update, and delete permission to an object residing in Database "X" from a store procedure in database "Y". I have already tried USE database statement. Any suggestion. Thanks Maroof Khan
I have my first small SQl Server 2005 database developed on my localserver and I have also its equivalent as an online database.I wish to update the local database (using and asp.net interface) andthen to upload the data (at least the amended data, but given thesmall size all data should be no trouble) to the online database.I think replication is the straight answer but I have no experience ofthis and I am wondering what else I might use which might be lesscomplicated. One solution is DTS (using SQL 2000 terms) but i am notsure if I can set this up (1) to overwrite existing tables and (2) notto seemingly remove identity attributes from fields set as identities.I know there are other possibilities but I would be glad of advice asto the likely best method for a small database updated perhaps onceweekly or at less frequent intervals,Best wishes, John Morgan
Guys,1st of all.. sorry about my english ok? Im from Brazil...Im trying to get a RS connecting through 2 databases with the querybelow:SELECTscar..osbalcao.numeroos,ccell.dbo.atrib_os.osFROMccell.dbo.atrib_osJOIN scar.dbo.osbalcao on ccell.dbo.atrib_os.os =scar.dbo.osbalcao.numeroosWHERE scar.dbo.osbalcao.localprod = '257'AND scar.dbo.osbalcao.status in ('EM')On a SQL IDE i got a good RS, but, when i try to use that same query onDelphi + ZEOS, the SQL Server tells me that "The database namecomponent of the object qualifier must be the name of the currentdatabase".I really dont understand why. I can only tell that the error isreturned from SQL Server.Have anyone got the same trouble?[]'sTiago Gigli
Im trying to create an update statement which references two tables (join) and has a CASE clause attached. Not sure where im going wrong...
Using T-sql!!!
update import set import.gone = from import inner join stat ON stat.id = import.id CASE WHEN stat.A = import.field2 THEN import.gone = sec.A WHEN stat.B = import.field2 THEN import.gone = sec.B WHEN stat.C = import.field2 THEN import.gone = sec.C WHEN stat.D = import.field2 THEN import.gone = sec.D WHEN stat.E = import.field2 THEN import.gone = sec.E WHEN stat.F = import.field2 THEN import.gone = sec.F ELSE import.gone = null END
