I want to update column (email) from one table to another validating the
Customerid column in both table. Update the email address in production
table with the email address in temp table if the customerid is same in both
tables.
I have a column named 'RELATED' of type text in a table. I want to update the contents of this colums...........but still keep what is currently stored in it. Basically i want to add some text onto the end of the string it currently contains.
I know this is possible in DB2 and Oracle, but what about for SQL-server 2005
1) select X number of rows from table1 2) I need colums for each row of table1 in a new table 3) As such, Select (select * from X where x.id = @ID), a,b,c from table Y where y.Id = @ID And I dont want to use IfExists. Thanks DK
Hi folks. This is my first post around here. So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?
I’d like to get some data which includes month values bound to a data grid. The data is stored in a table like so:
Measure Month Value A June 10.00 A July 9.00 A Aug 11.00 B Jun 100.00 B Jul 98.00 B Aug 99.00 C Jun 0.75 C Jul 0.8 C Aug 0.91
I need to report the data like this: Measure Jun Jul August A 10 9 11 B 100 98 99 C 75% 80% 91%
This was simple in classic ASP. Just use two recordsets, create a new table cell for each month using the first recordset then use the second recordset for each row.
But is there a way to “Pivot� or rotate the data so I can use the DataGrid? It only seems possible if each month has its own column field in table. Each month add a new column.
I can restructure the database, if needed.
I thought about creating a Cube, but that seems to have its own limitations. For example what if I want to add a Column for Quarter and year totals? I don’t think it’s possible to show multiple planes like that in an query of a cube.
It seems that this might be resolved in the presentation layer or the data layer. Any Suggestions?
FROM Mdrfoit INNER JOIN DevTable ON Mdrfoit.MDRReportKey = DevTable.MDRReportKey INNER JOIN TextTable ON Mdrfoit.MDRReportKey = TextTable.MDRReportKey LEFT OUTER JOIN probLink ON Mdrfoit.MDRReportKey probLink.MDRReportKey
WHERE Mdrfoit.DateReceived BETWEEN '1/1/1995' AND '1/1/2007') AND AND (DevTable.BrandName LIKE '&PLV%')
My problom is that when i do this i get back 3 rows in my result set for every 1 result that i want So ill get like
So my result set is on average 3 time larger then its suppose to be, This is also causing my query time to be very very slow.
There are no Duplicate entries in the database where all of the fields are the same. I am confused on if this is a problom in my query. Any advice would be great
"When you have everything to loose and nothing to gain, All-In is not an option"
I have this problem that I do not now how to solve it:
I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).
If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.
Nr. Of Orders Nr. of Lines Average Delivery Time
Open Order 10 20 3.2
Closed Order 15 30 5
Total 25 50 8.2 (it should be, for example, 4.5)
Is there a way in which I can tell the system to not calculate the total for the third column?
If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.
The joined view is named "dbo.viewExecView" and is like:
SELECT Bank_No, data_center FROM [ALPHASQL2000].ev_db.dbo.Bank
The new view that joins to the above view is like:
SELECT bank.BankID, evBank.data_center AS DataCenterID FROM dbo.Bank AS bank INNER JOIN dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)
Note: The data_center column (an int) was recently added to the Bank table in the linked ev_db database and it shows up there. It also shows up in the view "dbo.viewExecView". It does not appear in the new view that joins to "dbo.viewExecView". And when I run the 'new' view, I get an Error Message: Invalid column name 'data_center'.
I've tried to simplify this as much as possible while still including the pertinent information. Any help very much appreciated, I am currently stumped.
I'm stuck on something here. Any help would be great. This is a relational database question.
I'm trying to inner join two columns of one table with one column of another. The follwoing code doesn't work, but I think you can see what I'm trying to do.
Procedure _Links_List AS CREATE TABLE #TempTable ( LinkId int, LinkCategory varchar(50), LinkStatus varchar(50), LinkName varchar(50) ) INSERT INTO #TempTable ( LinkId, LinkCategory, LinkStatus, LinkName ) SELECT LinkId, _SubCategories.SubCategoryName, _SubCategories.SubCategoryName, LinkName FROM _Links INNER JOIN _SubCategories ON _Links.CategoryId = _SubCategories.SubCategoryId INNER JOIN _SubCategories ON _Links.StatusId = _SubCategories.SubCategoryId SELECT LinkId, LinkCategory, LinkStatus, LinkName FROM #TempTable
Also, I know how to do this if I had seperate category tables for each category (LinkCategory, LinkStatus). For examlple:
Procedure _Links_List AS CREATE TABLE #TempTable ( LinkId int, LinkCategory varchar(50), LinkStatus varchar(50), LinkName varchar(50) ) INSERT INTO #TempTable ( LinkId, LinkCategory, LinkStatus, LinkName ) SELECT LinkId, _Links_Categories.CategoryName, _Links_StatusCategories.StatusName, LinkName FROM _Links INNER JOIN _Links_Categories ON _Links.CategoryId = _Links_Categories.CategoryId INNER JOIN _Links_StatusCategories ON _Links.StatusId = _Links_StatusCategories.StatustId SELECT LinkId, LinkCategory, LinkStatus, LinkName FROM #TempTable
I know the above works but I'm trying to figure out how to have just one category table and one subcategory table for all of my categories of all my tables.
So instead of having to create a new table for every category and all the procedures for them for all my tables, I want to be able to just use these two tables.
If anyone knows how I go about this, especially when a table uses two category columns, I Thank you.
Hello, and thanks for taking the time to read this.
NOOB question:
In dealing with, say, shirts -- I have a DB that serves as a template for several customers. Each customer may have different ranges of sizes (one may have S,M,L and the other might also have XL,XXL). So the CATALOG table is:
CREATE TABLE [dbo].[T_Catalog]( [StlyeID] [int] ) ON [PRIMARY] with data: 1 2
the SIZES table (filled in by the customer with all the size ranges they carry) is:
CREATE TABLE [dbo].[T_Sizes]( [SizeID] [int], [SizeName] [nchar](10) ) ON [PRIMARY] with data:
1,Small 2,Medium 3,Large 4,Xtra-Large
and the AVAILSIZES table would be:
CREATE TABLE [dbo].[T_AvailSizes]( [StyleID] [int], [SizeID] [int] ) ON [PRIMARY]
1,1 1,2 1,3 2,1 2,3 2,4
Basically, then, we know that:
style 1 comes in Small, Medium, and Large style 2 xomes in Small, Large, and Xtra large
WE know that, but getting SQL to tell us that is a major PIA!!
Now,
SELECT t_Catalog.StyleId, t_AvailSizes.SizeID FROM t_Catalog INNER JOIN t_AvailSizes ON t_Catalog.StyleId = t_AvailSizes.StyleId
will give me a nice list of each item number with a separate row for each size number.
My questions are:
How do I get the size NAMES?
How would I get all of the sizes into a single row, so that there is a single row for each catalog StyleID (that's all I ever wanted to begin with)?
Is this the right way of doing this?
In reality I have about six columns that can contain multiple and variable items like this, and when trying to even think about resolving it all into a single record my brain tries desparately to crawl out my left ear.
Thanks for any help and information you can provide.
Okay I have a problem and I have no idea to do it. I need the SQL syntax to come up with this: the formatting is messed up though
a query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of People, and Salary, respectively. Round the average salary to two decimal places.
DNAMELOCNumber of PeopleSalary ------------------------------------------------------------------- ACCOUNTINGNEW YORK3 2916.67 RESEARCHDALLAS52175 SALESCHICAGO61566.67
I have a table users where there is a user_id and an department column. Also i have a table called KRS where there are the same columns, when a userid is given i want to auto fill in the departmentid,
What is the way we could implement a business logic from a Table bystoring it statemnnets in a colums and defining an execute sql toexecute it.Some legal requirements make it diffcult for us to createmodify stored procdures so Iwant to have a process where we create newrows in a table and execute it to execute business logic.All views are welcome.Havin g one table two tables different approaches to store ststementsand execute them....Case logic how to implement it?Flags in the tble colums in the tablesetcThanksAjay Garg
The first line of code works fine but when I try to set the value of the property I get the following excpetion:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in MyDll.dll Additional information: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Hello All again, I have another question, I would like to validate the data in a sql table before my code runs and inserts duplicate information. Can anyone help out with this via example?
I have one table called product 1 and the second table is called product 2 i have DirNo, ProdNo, UpdCode and BranchOffice as fields i whant to loop trought every record in table product 1 and check in table product 2 if the record is the same and exist i f it does go to next record, if not delete the record in product 1 and move to the next record and so on. could someone please help me. iam very new at this. thanks
This question is rather open ended. Basically, I'm wondering the different approaches people use when validating input into a stored procedure. The rest of my post just describes a rather simple approach I'm using, and some ideas I have, but I'm eager to know what others are doing. So, if you'd like to comment on this, feel free to do so without reading the following.
I often find myself calling a stored procedure, and needing to validate some of the inputs before proceeding with the rest of the tasks. Such as, making sure a matching record isn't already in the database before adding a new record. Ideally, this sort of validation will report back to the user interface immediately, so that the user can get real-time response to their form input, instead of the all-too-common approach of redirecting to an error page if something goes wrong in the database transaction.
It's also convenient, at times (though perhaps not efficient in all cases) to let your database perform business rules validation of inputs (let the user interface make sure data types are valid, and such), so that you duplicate less code in the event that the stored procedure is called in more than one context.
To address these issues, I've taken to making two stored procedures instead of one. Let the stored procedure be called AddRecord. I'd create that, as well as the procedure named AddRecord_Validate, which would take identical inputs as AddRecord, whenever possible. Nearly the first thing done in AddRecord would be to execute AddRecord_Validate. The user interface would also call AddRecord_Validate, and return any validation errors to the user interface.
This seems rather convenient to me, in many cases, but often it doesn't work as well as I prefer. It's not uncommon for me to end up performing the same queries in the _Validate SP as I do in the parent SP. For example, let's say I'm passing a parameter that I use to look up a record I plan to edit. In my validation, I query the database to verify that the record is found. But in the parent SP, I run the same query again in order to get the stored ID of the record I need to edit. This ends up duplicating queries, making the pair of procedures less efficient on the whole, as well as introducing the likelihood of bugs when code changes in one of the SPs, but not the other.
So I've been brainstorming other approaches. The first idea is to execute a single stored procedure, but when I want to run it in "validation" mode, I simply rollback the transaction. This would let me know if the stored procedure would have run with the specified input, but won't ultimately change anything. Unfortunately, I see some badness in this, such as the entire SP perhaps taking a lot longer to execute that simple validation would have, and side-effects such as incrementing Sequences, or locking the database, and basically just doing a whole lot more work during validation than needs to be done.
The next idea was to require that the user specify the "run mode" via a parameter, either 'validate' or 'run'. Then, all of the validation would be performed at the top of the stored procedure, and a simple IF statement would exit the SP before actually making any changes if it's run in 'validate' mode. Otherwise, it will continue, and do the real work. The only real downside I see to this is forcing the developer to deal with an extra parameter. And, perhaps, it's not really a "relational" approach.
i am using visual studio 2008 to create an inventory management system and i am having trouble checking for a constraint violation. i have a partnumber colum in the database that needs to be unique and i am using datasets / tableadapters to interact with the database. when a user adds a new part number to the database i need to make sure its not already there i have tried to use catch ce as constraintexception debug.print ce.tostring end try
but the program still crashes with
System.Data.ConstraintException was unhandled ( see full error below)
no matter where i put the try statement it says its unhandeled
this error triggers when when i leave the combo box (when its validated) i have even tried placing it here
Private Sub PartNumberComboBox_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles PartNumberComboBox.Validating
Try
Catch CE As ConstraintException
Debug.Print(CE.Message)
Catch EX As Exception
Debug.Print(EX.Message)
End Try
End Sub
if someone could point me in the right direction id greatley appriciate it
System.Data.ConstraintException was unhandled Message="Column 'PartNumber' is constrained to be unique. Value '36LDVRRP' is already present." Source="System.Data" StackTrace: at System.Data.UniqueConstraint.CheckConstraint(DataRow row, DataRowAction action) at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent) at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Int32 position, Boolean fireEvent, Exception& deferredException) at System.Data.DataTable.SetNewRecord(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Boolean fireEvent) at System.Data.DataRow.EndEdit() at System.Data.DataRowView.EndEdit() at System.Windows.Forms.CurrencyManager.EndCurrentEdit() at System.Windows.Forms.CurrencyManager.ChangeRecordState(Int32 newPosition, Boolean validating, Boolean endCurrentEdit, Boolean firePositionChange, Boolean pullData) at System.Windows.Forms.CurrencyManager.set_Position(Int32 value) at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(EventArgs e) at System.Windows.Forms.ComboBox.WmReflectCommand(Message& m) at System.Windows.Forms.ComboBox.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.SendMessage(HandleRef hWnd, Int32 msg, IntPtr wParam, IntPtr lParam) at System.Windows.Forms.Control.SendMessage(Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.Control.ReflectMessageInternal(IntPtr hWnd, Message& m) at System.Windows.Forms.Control.WmCommand(Message& m) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.GroupBox.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.CallWindowProc(IntPtr wndProc, IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam) at System.Windows.Forms.NativeWindow.DefWndProc(Message& m) at System.Windows.Forms.Control.DefWndProc(Message& m) at System.Windows.Forms.Control.WmCommand(Message& m) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ComboBox.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(ApplicationContext context) at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at Inventory_System.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException:
I am using the following SQl statement in a DTS vb file. The data is coming from an Excel spreadsheet and being put into a SQl server table.
oCustomTask1.SourceSQLStatement = "select `Order No`,`Country`,`Desc`,`Amount` from `Sheet1$` WHERE `Order No` = 1 "
I want to validate the data being put into the table to ensure no duplicates records are entered UNLESS the record has changed in any way. E.g. If record 1 had a column called "NumberOne" which changed to "Number1" change this information and move the original value to another table.
I'm totally new to SSIS and need some direction.I'ved worked with the Import/Export wizard to create a package that imports a text file into a SQL Server table. However, I'm told the format of the text file changes over time and that's not good. I need to program in a format validation check on the source file before it gets imported. If it changes, I'm suppose to throw an alert or something.Let's say the file has the columns: field1 (string[10]), field2 (date), field3 (integer), field4 (decimal).I did some testing and tried to change the data to a longer string in field1, and SSIS recognizes that and errors out. How do I get it to send the bad record to an bad record file? Do I just set a destination file connection for bad records and connect the red arrow from the source file to the destination file?I forget if the source file connection recognizes a bad date and errors out. I'll have to check again.But when I changed the data in the datafile for field3 from integer to decimal. It didn't recognize that as an error. It read it in "successfully". That's not good.Similar thing happened when I changed field4 from decimal to integer in the data file. But I'm not too worried about that.Any hints on how to do this or a better approach on checking for file format changes would be appreciated.Ken
I have one date parameter in my report. The user will be able to manually enter or use the calendar control. In the case of entering manually, is there any way to validate the input string? How can we prevent users from entering alphabets?
Also, is there any way to reduce the length of the parameter textbox. Can you please help?
I have a Problem with my SSIS and it is that when you open the package it says "Validating <Data Flow>" and it takes a loooooong time to finaly open ( by long I mean like 3 or 4 hours to just for open!!!). In 3 of the DF, I have conections to a Unisys server and in exactly in this DF is where it takes its time. This Unisys system its kind a slow but not that slow. I am running on a server that have 4 G and 8 processors, so it cant be the machine.
my question is, How can I make it validate faster ? I have tried to work Offline but when I run the package I need to be online.
I want to do some error checking on the parameters found in a SQLDataSource before I run the insert. The problem is these are ControlParameters and I want to do this dynamically so I can't just call the Control.Text property and grab its value. So how can I get access to what the ControlParameter evaluates to? Secondly, is there a way to access what the update parameters evaluate to in order to check them before they're inserted - if so how do I get access to these? Here's an example of one of the data sources i'm using:<asp:SqlDataSource ID="sqlContact" runat="server" ConnectionString="<%$ ConnectionStrings:strConn %>" SelectCommand="SELECT [ContactID], [FirstName], [LastName], , [Address], [Phone], [Grade], [Contacted], [ListServe] FROM [Contact]" UpdateCommand="UPDATE Contact SET FirstName = @FirstName, LastName = @LastName, Email = @Email, Address = @Address, Phone = @Phone, Grade = @Grade WHERE ContactID = @ContactID" DeleteCommand="DELETE FROM [Contact] WHERE ContactID = @ContactID" InsertCommand="INSERT INTO [Contact] ([FirstName],[LastName],,[Address],[Phone],[Grade],[Contacted],[ListServe]) VALUES (@FirstName,@LastName,@Email,@Address,@Phone,@Grade,0,0)"> <UpdateParameters> <asp:Parameter Name="FirstName" Type="String" /> <asp:Parameter Name="LastName" Type="String" /> <asp:Parameter Name="Email" Type="String" /> <asp:Parameter Name="Address" Type="String" /> <asp:Parameter Name="Phone" Type="String" /> <asp:Parameter Name="Grade" Type="String" /> </UpdateParameters> <InsertParameters> <asp:ControlParameter Name="FirstName" Type="String" ControlID="txtContactFirst" PropertyName="Text" /> <asp:ControlParameter Name="LastName" Type="String" ControlID="txtContactLast" PropertyName="Text" /> <asp:ControlParameter Name="Email" Type="String" ControlID="txtContactEmail" PropertyName="Text" /> <asp:ControlParameter Name="Address" Type="String" ControlID="txtContactAddress" PropertyName="Text" /> <asp:ControlParameter Name="Phone" Type="String" ControlID="txtContactPhone" PropertyName="Text" /> <asp:ControlParameter Name="Grade" Type="String" ControlID="ddlContactGrade" PropertyName="SelectedValue" /> </InsertParameters> </asp:SqlDataSource> An Event is fired when I click add on a button which looks similar to this: //Add btnAddContact_Clickprotected void btnAddContact_Click(object sender, EventArgs e) { InsertRow(sqlContact); }The InsertRow() function is then what i'm using to evaluate the values... So how can get the values those controlparameters actually are in order to evaluate them before I actually insert. Or is there a better way to do it?
Has anyone figured out how to test if a string is a valid date without risking a sql conversion error?
I'd really like to avoid parsing the strings myself in an import procedure I am writing.
I can run an insert/select with the text being converted to datetime data, but if it hits text that won't convert correctly (such as '13-1-1999') it fails with a sql error. This could mess me up quite a bit.