Search Results

Search found 117232 results on 4690 pages for 'sql user group'.

Page 406/4690 | < Previous Page | 402 403 404 405 406 407 408 409 410 411 412 413  | Next Page >

  • Exception handling in Linq to SQL for customers without orders

    - by stackoverflowuser
    I have the following code to retrieve customer name, total (orders ), sum (order details) for reach customer in Northwind database. The problem with below code is that it raises an exception since a few customers dont have any entry in orders table. I know using the query syntax (join) the exception can be avoided. I want to know if the same can be handled with the extension method syntax. var customerOrders = db.Customers .Select(c => new { CompanyName = c.CompanyName, TotalOrders = c.Orders.Count(), TotalQuantity = c.Orders .SelectMany(o => o.Order_Details).Sum(o=>o.Quantity) });

    Read the article

  • SQL Server stored procedure return code oddity

    - by gbn
    Hello The client that calls this code is restricted and can only deal with return codes from stored procs. So, we modified our usual contract to RETURN -1 on error and default to RETURN 0 if no error If the code hits the inner catch block, then the RETURN code default to -4. Where does this come from, does anyone know...? IF OBJECT_ID('dbo.foo') IS NOT NULL DROP TABLE dbo.foo GO CREATE TABLE dbo.foo ( KeyCol char(12) NOT NULL, ValueCol xml NOT NULL, Comment varchar(1000) NULL, CONSTRAINT PK_foo PRIMARY KEY CLUSTERED (KeyCol) ) GO IF OBJECT_ID('dbo.bar') IS NOT NULL DROP PROCEDURE dbo.bar GO CREATE PROCEDURE dbo.bar @Key char(12), @Value xml, @Comment varchar(1000) AS SET NOCOUNT ON DECLARE @StartTranCount tinyint; BEGIN TRY SELECT @StartTranCount = @@TRANCOUNT; IF @StartTranCount = 0 BEGIN TRAN; BEGIN TRY --SELECT @StartTranCount = 'fish' INSERT dbo.foo (KeyCol, ValueCol, Comment) VALUES (@Key, @Value, @Comment); END TRY BEGIN CATCH IF ERROR_NUMBER() = 2627 --PK violation UPDATE dbo.foo SET ValueCol = @Value, Comment = @Comment WHERE KeyCol = @Key; ELSE RAISERROR ('Tits up', 16, 1); END CATCH IF @StartTranCount = 0 COMMIT TRAN; END TRY BEGIN CATCH IF @StartTranCount = 0 AND XACT_STATE() <> 0 ROLLBACK TRAN; RETURN -1 END CATCH --Without this, we'll send -4 if we hit the UPDATE CATCH block above --RETURN 0 GO --Run with RETURN 0 and fish line commented out DECLARE @rtn int EXEC @rtn = dbo.bar 'abcdefghijkl', '<foobar />', 'testing' SELECT @rtn; SELECT * FROM dbo.foo DECLARE @rtn int EXEC @rtn = dbo.bar 'abcdefghijkl', '<foobar2 />', 'testing2' --updated OK but we get @rtn = -4 SELECT @rtn; SELECT * FROM dbo.foo --uncomment fish line DECLARE @rtn int EXEC @rtn = dbo.bar 'abcdefghijkl', '<foobar />', 'testing' --Hit outer CATCH, @rtn = -1 as expected SELECT @rtn; SELECT * FROM dbo.foo

    Read the article

  • SQL server virtual memory usage and perofrmance

    - by user365035
    Hello, I have a very large DB used mostly for analytics. The performance overall is very sluggish. I just noticed that when running the query below, the amount of virtual memory used greatly exceed the amount of physical memory available. Currently, phsycial memory is 10GB (10238 bytes) where as the virtual memory returns significantly more 8388607 bytes. That seems really wrong, but I'm at a bit of a loss on how to proceed. USE [master]; GO select cpu_count , hyperthread_ratio , physical_memory_in_bytes / 1048576 as 'mem_MB' , virtual_memory_in_bytes / 1048576 as 'virtual_mem_MB' , max_workers_count , os_error_mode , os_priority_class from sys.dm_os_sys_info

    Read the article

  • Problem with passing parameters to SQL procedure using VBA

    - by Stefan Åstrand
    Hi, I am trying to pass @intDocumentNo and @intCustomerNo to a stored procedure using VBA but only @intCustomerNo is updated in dbo.tblOrderHead. I don't think the problem is with the procedure because if I enter the values manually it runs properly. What am I doing wrong? VBA Code: Private Sub intCustomerNo_Click() Dim cmdCommand As New ADODB.Command With cmdCommand .ActiveConnection = CurrentProject.Connection .CommandType = adCmdStoredProc .CommandText = "uspSelectCustomer" '@intDocumentNo .Parameters("@intDocumentNo").Value = Forms![frmSalesOrder].[IntOrderNo] '@intCustomerNo .Parameters("@intCustomerNo").Value = Me![intCustomerNo] .Execute End With DoCmd.Close Forms![frmSalesOrder].Requery End Sub Procedure: UPDATE dbo.tblOrderHead SET dbo.tblOrderHead.intCustomerNo = @intCustomerNo , dbo.tblOrderHead.intPaymentCode = dbo.tblCustomer.intPaymentCode, dbo.tblOrderHead.txtDeliveryCode = dbo.tblCustomer.txtDeliveryCode, dbo.tblOrderHead.txtRegionCode = dbo.tblCustomer.txtRegionCode, dbo.tblOrderHead.txtCurrencyCode = dbo.tblCustomer.txtCurrencyCode, dbo.tblOrderHead.txtLanguageCode = dbo.tblCustomer.txtLanguageCode FROM dbo.tblOrderHead INNER JOIN dbo.tblCustomer ON dbo.tblOrderHead.intCustomerNo = dbo.tblCustomer.intCustomerNo AND dbo.tblOrderHead.intOrderNo = @intDocumentNo

    Read the article

  • Using a RegEx in a SQL Query

    - by Jim B
    Hey Everyone, Here's the situation I'm in: We have a field in our database that contains a 3 digit number, surrounded by some text. This number is actually a PK in another table, and I need to extract this out so I can implement a proper FK relationship. Here's an example of what would currently reside in the column: Some Text Goes Here - (305) Followed By Some More Text So, what I'm looking to do is extract the '305' from the column, and hopefully end up with a result that looks something like this (pseudo code) SELECT <My Extracted Value>, Original Column Text, Id FROM dbo.MyTable It seems to me that using a Regex match in my query is the most effective way to do this. Can anybody point me in the right direction?

    Read the article

  • Simplest way to use a DatagridView with Linq to SQL

    - by Martín Marconcini
    Hi, I have never used datagrids and such, but today I came across a simple problem and decided to "databind" stuff to finish this faster, however I've found that it doesn't work as I was expecting. I though that by doing something as simple as: var q = from cust in dc.Customers where cust.FirstName == someString select cust; var list = new BindingList<Customer>(q.ToList()); return list; Then using that list in a DataGridView1.DataSource was all that I needed, however, no matter how much I google, I can't find a decent example on how to populate (for add/edit/modify) the results of a single table query into a DataGridView1. Most samples talk about ASP.NET which I lack, this is WinForms. Any ideas? I've came across other posts and the GetNewBindingList, but that doesn't seem to change much. What am I missing (must be obvious)? Thanks in advance. Martin.

    Read the article

  • Return number of rows affected by SQL UPDATE statement in Java

    - by Krt_Malta
    I'm using a MySQL database and accessing it through Java. PreparedStatement prep1 = this.connection.prepareStatement("UPDATE user_table SET Level = 'Super' WHERE Username = ?"); prep1.setString(1, username); The update statement above works fine however I'd like to get the number of rows affected with this statement. Is this possible please?

    Read the article

  • sql stored procedure in visual studio 2008

    - by Greg
    Hi, I want to write stored procedure in visual studio that as a parameter recieves the name of project and runs in database TT and copies data from TT.dbo.LCTemp (where the LC is the name of the project recieved as a parameter) table to "TT.dbo.Points" table. both tables have 3 columns: PT_ID, Projectname and DateCreated I think I have written it wrong, here it is: ALTER PROCEDURE dbo.FromTmpToRegular @project varchar(10) AS BEGIN declare @ptID varchar(20) declare @table varchar(20) set @table = 'TT.dbo.' + @project + 'Temp' set @ptID = @table + '.PT_ID' Insert into TT.dbo.Points Select * from [@table] where [@ptID] Not in(Select PT_ID from TT.dbo.Points) END Any idea what I did wrong? Thanks! :) Greg

    Read the article

  • Stored procedure does not return result when using Temp table

    - by Muhammad Akhtar
    I have used temp table in my stored procedure and unable to get the result when executing. It return result fine when running the same as query. my query structure is something like... ALTER PROCEDURE [dbo].[test] as begin SET NOCOUNT ON; SELECT * INTO #Dates FROM Dates -- Used temp table SET @Query = ' SELECT [Name], [HotelName], '+@Dates+' FROM ( SELECT , HD.[Name], HD.[HotelName], HD.Price FROM #Dates D LEFT JOIN #HotelData HD ON D.DateVal = HD.OccDate) T PIVOT ( SUM(Price) FOR [Date] IN ('+@Dates+') ) AS PT' execute(@Query) end Exec test -- Exectuting, I am getting message command completed successfully

    Read the article

  • how to order a group result with Linq?

    - by Aaron
    How can I order the results from "group ... by... into..." statement in linq? For instance: var queryResult = from records in container.tableWhatever where records.Time >= DateTime.Today group records by tableWhatever.tableHeader.UserId into userRecords select new { UserID = userRecords.Key, Records = userRecords }; The query returns records in table "contain.tableWhatever" grouped by "UserId". I want the returned results within each group ordered by time decending. How can I do that? More specific, assume the above query return only one group like the following: {UserID = 1, Records= {name1 5/3/2010_7:10pm; name2 5/3/2010_8:10pm; name3 5/3/2010_9:10pm} } After insert the orderby statement in the above query, the returned results should be like this: {UserID = 1, Records= {name3 5/3/2010_9:10pm; name2 5/3/2010_8:10pm; name1 5/3/2010_7:10pm} } Thanks for help!

    Read the article

  • Dynamic Like Statement in SQL

    - by Peter McElhinney
    Hey there! I've been racking my brain on how to do this for a while, and i know that some genius on this site will have the answer. Basically i'm trying to do this: SELECT column FROM table WHERE [table][column] LIKE string1 OR [table][column] LIKE string2 OR [table][column] LIKE string3... for a list of search strings stored in a column of a table. Obviously I can't do a like statement for each string by hand because i want the table to be dynamic. Any suggestions would be great. :D EDIT: I'm using MSSQL :(

    Read the article

  • SQL AND operator not working properly

    - by Chandana De Silva
    I have following two tables LandParcels Table Blockid ParcelNo storPri ======= ======== ======= 52000105 3 State 52000105 4 Private 52000105 5 State Actions Table Blockid ParcelNo ActionTaken ======= ======== =========== 52000105 3 Received 52000105 3 Send to Computer 52000105 4 Received 52000105 5 Received I want to find the records Received but not Send to Computer Here is my query select l.blockid, l.parcelno from landparcels l left join actions ac on l.blockid = ac.blockid and l.parcelno = ac.parcelno where ac.actiontaken = 'Received' and ac.actiontaken <> 'Send to Computer' and ac.blockid = 52000105 The result is Blockid ParcelNo ======= ======== 52000105 3 52000105 4 52000105 5 I want ParcelNo 4 and 5

    Read the article

  • deleting and reusing a temp table in a stored precedure

    - by Sheagorath
    Hi I need to SELECT INTO a temp table multiple times with a loop but I just can't do it, because after the table created by SELECT INTO you can't simply drop the table at the end of the loop, because you can't delete a table and create it again in the same batch. so how can I delete a table in a stored procedure and create it again? is it possible to this without using a temp table? here is a snippet of where I am actualy using the temp table which is supposed to be a pivoting algorithm: WHILE @offset<@NumDays BEGIN SELECT bg.*, j.ID, j.time, j.Status INTO #TEMP1 FROM #TEMP2 AS bg left outer join PersonSchedule j on bg.PersonID = j.PersonID and bg.TimeSlotDateTime = j.TimeSlotDateTime and j.TimeSlotDateTime = @StartDate + @offset DROP TABLE #TEMP2; SELECT * INTO #TEMP2 FROM #TEMP1 DROP TABLE #TEMP1 SET @offset = @offset + 1 END

    Read the article

  • Help with SQL Server query

    - by Travis
    Sorry* this is what I should have put My query is creating duplicate entries for any record that has more than 1 instance (regardless of date) <asp:SqlDataSource ID="EastMonthlyHealthDS" runat="server" ConnectionString="<%$ ConnectionStrings:SNA_TRTTestConnectionString %>" SelectCommand="SELECT [SNA_Parent_Accounts].[Company], (SELECT [Monthly_HIP_Reports].[AccountHealth] from [Monthly_HIP_Reports] where ([Monthly_HIP_Reports].[YearMonth] = @ToDtRFC) AND ([SNA_Parent_Accounts].[CompID] = [Monthly_HIP_Reports].[CompID])) as [AccountHealth], [SNA_Parent_Accounts].[CompID] FROM [SNA_Parent_Accounts] LEFT OUTER JOIN [Monthly_HIP_Reports] ON [Monthly_HIP_Reports].[CompID] = [SNA_Parent_Accounts].[CompID] WHERE (([SNA_Parent_Accounts].[Classification] = 'Business') OR ([SNA_Parent_Accounts].[Classification] = 'Business Ihn')) AND ([SNA_Parent_Accounts].[Status] = 'active') AND ([SNA_Parent_Accounts].[Region] = 'east') ORDER BY [SNA_Parent_Accounts].[Company]"> <SelectParameters> <asp:ControlParameter ControlID="ddMonths" Name="ToDtRFC" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource> Using SELECT DISTINCT appears to correct the problem, but I don't consider that a solution. There are no duplicate entries in the database. So it appears my query is superfically creating duplicates. The query should grab a list of companies that meet the criteria in the where clause, but also grab the Health status for each company in that particular [YearMonth] if present which is what the subquery is for. If an entry for that YearMonth is not present, then leave the Health status blank. but as stated earlier.. if you have an entry say for 2009-03 for CompID 2 and an entry for 2009-04 for CompID 2.. Doesn't matter what month you select it will list that company 2-3 times.

    Read the article

  • Pass a variable into a trigger

    - by Codesleuth
    I have a trigger which deals with some data for logging purposes like so: CREATE TRIGGER trgDataUpdated ON tblData FOR UPDATE AS BEGIN INSERT INTO tblLog ( ParentID, OldValue, NewValue, UserID ) SELECT deleted.ParentID, deleted.Value, inserted.Value, @intUserID -- how can I pass this in? FROM inserted INNER JOIN deleted ON inserted.ID = deleted.ID END How can I pass in the variable @intUserID into the above trigger, as in the following code: DECLARE @intUserID int SET @intUserID = 10 UPDATE tblData SET Value = @x PS: I know I can't literally pass in @intUserID to the trigger, it was just used for illustration purposes.

    Read the article

  • SQL Count in View as column

    - by alex
    I'm trying to get the result of a COUNT as a column in my view. Please see the below query for a demo of the kind of thing I want (this is just for demo purposes) SELECT ProductID, Name, Description, Price, (SELECT COUNT(*) FROM ord WHERE ord.ProductID = prod.ProductID) AS TotalNumberOfOrders FROM tblProducts prod LEFT JOIN tblOrders ord ON prod.ProductID = ord.ProductID This obviously isn't working... but I was wondering what the correct way of doing this would be?

    Read the article

  • Hibernate - strange order of native SQL parameters

    - by Xorty
    Hello, I am trying to use native MySQL's MD5 crypto func, so I defined custom insert in my mapping file. <hibernate-mapping package="tutorial"> <class name="com.xorty.mailclient.client.domain.User" table="user"> <id name="login" type="string" column="login"></id> <property name="password"> <column name="password" /> </property> <sql-insert>INSERT INTO user (login,password) VALUES ( ?, MD5(?) )</sql-insert> </class> </hibernate-mapping> Then I create User (pretty simple POJO with just 2 Strings - login and password) and try to persist it. session.beginTransaction(); // we have no such user in here yet User junitUser = (User) session.load(User.class, "junit_user"); assert (null == junitUser); // insert new user junitUser = new User(); junitUser.setLogin("junit_user"); junitUser.setPassword("junitpass"); session.save(junitUser); session.getTransaction().commit(); What actually happens? User is created, but with reversed parameters order. He has login "junitpass" and "junit_user" is MD5 encrypted and stored as password. What did I wrong? Thanks EDIT: ADDING POJO class package com.xorty.mailclient.client.domain; import java.io.Serializable; /** * POJO class representing user. * @author MisoV * @version 0.1 */ public class User implements Serializable { /** * Generated UID */ private static final long serialVersionUID = -969127095912324468L; private String login; private String password; /** * @return login */ public String getLogin() { return login; } /** * @return password */ public String getPassword() { return password; } /** * @param login the login to set */ public void setLogin(String login) { this.login = login; } /** * @param password the password to set */ public void setPassword(String password) { this.password = password; } /** * @see java.lang.Object#toString() * @return login */ @Override public String toString() { return login; } /** * Creates new User. * @param login User's login. * @param password User's password. */ public User(String login, String password) { setLogin(login); setPassword(password); } /** * Default constructor */ public User() { } /** * @return hashCode * @see java.lang.Object#hashCode() */ @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((null == login) ? 0 : login.hashCode()); result = prime * result + ((null == password) ? 0 : password.hashCode()); return result; } /** * @param obj Compared object * @return True, if objects are same. Else false. * @see java.lang.Object#equals(java.lang.Object) */ @Override public boolean equals(Object obj) { if (this == obj) { return true; } if (obj == null) { return false; } if (!(obj instanceof User)) { return false; } User other = (User) obj; if (login == null) { if (other.login != null) { return false; } } else if (!login.equals(other.login)) { return false; } if (password == null) { if (other.password != null) { return false; } } else if (!password.equals(other.password)) { return false; } return true; } }

    Read the article

  • sql error, NULL??

    - by Luke
    So I have a query, can someone let me know if it looks ok content wise? "INSERT INTO ".TBL_MESSAGES." (NULL, 'Your ranking points have changed', 'Due to your recent activity, your ranking points have increased by $r', '2', '$u', '0', '0', '0', '0', NULL, NULL, NULL, NULL, now())"; I can add further information if the query doesnt appear to have a problem? Thanks

    Read the article

  • SQL - Outer Join 2 queries?

    - by Stuav
    I have two querys. Query 1 gives me this result: Day New_Users 01-Jan-12 45 02-Jan-12 36 and so on. Query 2 gives me this result: Day Retained_Users 01-Jan-12 33 02-Jan-12 30 and so on. I want a new query that will join this together and read: Day New_Users Retained_Users 01-Jan-12 45 33 02-Jan-12 36 30 Do I use some sort of outer join?

    Read the article

< Previous Page | 402 403 404 405 406 407 408 409 410 411 412 413  | Next Page >