Search Results

Search found 27339 results on 1094 pages for 'sql dmv'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 306/1094 | < Previous Page | 302 303 304 305 306 307 308 309 310 311 312 313  | Next Page >

  • LINQ to SQL: filter nested objects with soft deletes

    - by Alex
    Hello everyone, I'm using soft deleting in my database (IsDeleted field). I'm actively using LoadWith and AssociateWith methods to retrieve and filter nested records. The thing is AssociateWith only works with properties that represents a one-to-many relationship. DataLoadOptions loadOptions = new DataLoadOptions(); loadOption.LoadWith<User>(u = > u.Roles); loadOption.AssociateWith<User>(u = > u.Roles.Where(r = > !r.IsDeleted)); In the example above I just say: I want to retrieve users with related (undeleted) roles. But when I have one-to-one relationship, e.g. Document - File (the only one file is related to the document) I'm unable to filter soft deleted object: DataLoadOptions loadOptions = new DataLoadOptions(); loadOption.LoadWith<Document>(d = > d.File); // the next certainly won't work loadOption.AssociateWith<File>(f = > !f.IsDeleted); So, is there any idea how to filter records within the one-to-one relationship? Thanks!

    Read the article

  • For Nvarchar(Max) I am only getting 4000 characters in TSQL?

    - by Malcolm
    Hi, This is for SS 2005. Why I am i only getting 4000 characters and not 8000? It truncates the string @SQL1 at 4000. ALTER PROCEDURE sp_AlloctionReport( @where NVARCHAR(1000), @alldate NVARCHAR(200), @alldateprevweek NVARCHAR(200)) AS DECLARE @SQL1 NVARCHAR(Max) SET @SQL1 = 'SELECT DISTINCT VenueInfo.VenueID, VenueInfo.VenueName, VenuePanels.PanelID, VenueInfo.CompanyName, VenuePanels.ProductCode, VenuePanels.MF, VenueInfo.Address1, VenueInfo.Address2, '' As AllocationDate, '' As AbbreviationCode, VenueInfo.Suburb, VenueInfo.Route, VenueInfo.ContactFirstName, VenueInfo.ContactLastName, VenueInfo.SuitableTime, VenueInfo.OldVenueName, VenueCategories.Category, VenueInfo.Phone, VenuePanels.Location, VenuePanels.Comment, [VenueCategories].[Category] + '' Allocations'' AS ReportHeader, ljs.AbbreviationCode AS PrevWeekCampaign FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID = VenuePanels.VenueID) INNER JOIN VenueCategories ON VenueInfo.CategoryID = VenueCategories.CategoryID) LEFT JOIN (SELECT CampaignProductions.AbbreviationCode, VenuePanels.PanelID, CampaignAllocations.AllocationDate FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID=VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID=CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID=CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID=VenueCategories.CategoryID WHERE ' + @alldateprevweek + ') ljs ON VenuePanels.PanelID = ljs.PanelID) INNER JOIN (SELECT VenueInfo.VenueID, VenuePanels.PanelID, VenueInfo.VenueName, VenueInfo.CompanyName, VenuePanels.ProductCode, VenuePanels.MF, VenueInfo.Address1, VenueInfo.Address2, CampaignAllocations.AllocationDate, CampaignProductions.AbbreviationCode, VenueInfo.Suburb, VenueInfo.Route, VenueInfo.ContactFirstName, VenueInfo.ContactLastName, VenueInfo.SuitableTime, VenueInfo.OldVenueName, VenueCategories.Category, VenueInfo.Phone, VenuePanels.Location, VenuePanels.Comment, [Category] + '' Allocations'' AS ReportHeader, ljs2.AbbreviationCode AS PrevWeekCampaign FROM ((((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID = VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID = CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID = CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID = VenueCategories.CategoryID) LEFT JOIN (SELECT CampaignProductions.AbbreviationCode, VenuePanels.PanelID, CampaignAllocations.AllocationDate FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID=VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID=CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID=CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID=VenueCategories.CategoryID WHERE ' + @alldateprevweek + ') ljs2 ON VenuePanels.PanelID = ljs2.PanelID WHERE ' + @alldate + ' AND ' + @where + ') ljs3 ON VenueInfo.VenueID = ljs3.VenueID WHERE (((VenuePanels.PanelID)<>ljs3.[PanelID] And (VenuePanels.PanelID) Not In (SELECT PanelID FROM CampaignAllocations WHERE ' + @alldateprevweek + ')) AND ' + @where + ') UNION ALL SELECT VenueInfo.VenueID, VenueInfo.VenueName, VenuePanels.PanelID, VenueInfo.CompanyName, VenuePanels.ProductCode, VenuePanels.MF, VenueInfo.Address1, VenueInfo.Address2, CampaignAllocations.AllocationDate, CampaignProductions.AbbreviationCode, VenueInfo.Suburb, VenueInfo.Route, VenueInfo.ContactFirstName, VenueInfo.ContactLastName, VenueInfo.SuitableTime, VenueInfo.OldVenueName, VenueCategories.Category, VenueInfo.Phone, VenuePanels.Location, VenuePanels.Comment, [Category] + '' Allocations'' AS ReportHeader, ljs.AbbreviationCode AS PrevWeekCampaign FROM ((((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID = VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID = CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID = CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID = VenueCategories.CategoryID) LEFT JOIN (SELECT CampaignProductions.AbbreviationCode, VenuePanels.PanelID, CampaignAllocations.AllocationDate FROM (((VenueInfo INNER JOIN VenuePanels ON VenueInfo.VenueID=VenuePanels.VenueID) INNER JOIN CampaignAllocations ON VenuePanels.PanelID=CampaignAllocations.PanelID) INNER JOIN CampaignProductions ON CampaignAllocations.CampaignID=CampaignProductions.CampaignID) INNER JOIN VenueCategories ON VenueInfo.CategoryID=VenueCategories.CategoryID WHERE ' + @alldateprevweek + ') ljs ON VenuePanels.PanelID = ljs.PanelID WHERE ' + @alldate + ' AND ' + @where Select @SQL1

    Read the article

  • SQL Server CONTAINS with digits gives no results

    - by dale
    Hi, I have a database table which is full-text indexed and i use the CONTAINS-function to perform a search-query on it. When I do: SELECT * FROM Plants WHERE CONTAINS(Plants.Description, '"Plant*" AND "one*"'); I get back all correct results matching a description with the words "Plant" and "one". Some plant are named like "Plant 1", "Plant 2" etc. and this is the problem. When i do this, i get no results: SELECT * FROM Plants WHERE CONTAINS(Plants.Description, '"Plant*" AND "1*"'); Anyone know why?

    Read the article

  • Import Excel 2007 into SQL 2000 using Classic ASP and ADO

    - by jeff
    I have the following code from a legacy app which currently reads from an excel 2003 spreadsheet on a server, but I need this to run from my machine which uses excel 2007. When I debug on my machine ADO does not seem to be reading the spreadsheet. I have checked all file paths etc. and location of spreadsheet that is all fine. I've heard that you cannot use the jet db engine for excel 2007 anymore? Can someone confirm this? What do I need to do to get this to work? Please help! set obj_conn = Server.CreateObject("ADODB.Connection") obj_conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Application("str_folder") & "CNS43.xls;" & _ "Extended Properties=""Excel 8.0;""" set obj_rs_cns43 = Server.CreateObject("ADODB.RecordSet") obj_rs_cns43.ActiveConnection = obj_conn obj_rs_cns43.CursorType = 3 obj_rs_cns43.LockType = 2 obj_rs_cns43.Source = "SELECT * FROM [CNS43$]" obj_rs_cns43.Open

    Read the article

  • sql group by with left join

    - by cometta
    fail statement:Error: ORA-00979: not a GROUP BY expression select org_division.name , org_department.name , org_surveylog.division_code as divisionCode,org_surveylog.department_code as departmentCode , max(org_surveylog.actiondate) from org_surveylog left join org_division on (org_surveylog.division_code= org_division.division_code and org_surveylog.SURVEY_NUM= org_division.survey_num) left join org_department on (org_surveylog.department_code = org_department.department_code and org_surveylog.SURVEY_NUM = org_department.survey_num) group by org_surveylog.division_code,org_surveylog.department_code but below is ok select org_surveylog.division_code as divisionCode,org_surveylog.department_code as departmentCode , max(org_surveylog.actiondate) from org_surveylog left join org_division on (org_surveylog.division_code= org_division.division_code and org_surveylog.SURVEY_NUM= org_division.survey_num) left join org_department on (org_surveylog.department_code = org_department.department_code and org_surveylog.SURVEY_NUM = org_department.survey_num) group by org_surveylog.division_code,org_surveylog.department_code how to use group by with left join when i need to show value of org_division.name , org_department.name ?

    Read the article

  • Saving a select count(*) value to an integer (SQL Server)

    - by larryq
    Hi everyone, I'm having some trouble with this statement, owing no doubt to my ignorance of what is returned from this select statement: declare @myInt as INT set @myInt = (select COUNT(*) from myTable as count) if(@myInt <> 0) begin print 'there's something in the table' end There are records in myTable, but when I run the code above the print statement is never run. Further checks show that myInt is in fact zero after the assignment above. I'm sure I'm missing something, but I assumed that a select count would return a scalar that I could use above?

    Read the article

  • Microsoft SQL Server 2005 Inserting into tables from child procedure which returned multiple tables

    - by Kevin
    I've got a child procedure which returns more than table. child: PROCEDURE KevinGetTwoTables AS BEGIN SELECT 'ABC' Alpha, '123' Numeric SELECT 'BBB' Alpha, '123' Numeric1, '555' Numeric2 END example: PROCEDURE KevinTesting AS BEGIN DECLARE @Table1 TABLE ( Alpha varchar(50), Numeric1 int ) DECLARE @Table2 TABLE ( Alpha varchar(50), Numeric1 int, Numeric2 int ) --INSERT INTO @Table1 EXEC KevinGetTwoTables END

    Read the article

  • Show SQL query results in Sharepoint

    - by JoeOD
    Hi All I support Rational clearquest for my company and have been asked to show some of the data in Sharepoint. I can query the data in Clearquest no problem (Using Toad) but have no idea about how to go displaying it in Sharepoint. The data I'm querying contains dates and ideally I'd like to be able display it in a sharepoint calendar but list format would do for the moment. Can anybody offer advice on even where to start? Thanks, Joe

    Read the article

  • Other solution instead of Cursoring

    - by dewacorp.alliances
    Hi there I have the following pivoting table that I manage to do and here's the result and I want to put a bit further. **NTRITCode; NTRIId; Parameter; Usage; Rate** CURRENT; 4; Peak; 100; 0.1 CURRENT; 4; NonPeak; 200; 0.2 PROPOSED; 6; Peak; 100; 0.2 PROPOSED; 6; NonPeak; 200; 0.3 PROPOSED; 8; Peak; 200; 0.3 PROPOSED; 8; NonPeak; 200; 0.5 As you can see there is 2 sets of proposed (ID=6 and 8). I want somehow display like this below so each set has a pair of CURRENT as well as the PROPOSED one as follow: **Sequence; NTRITCode; NTRIId; Parameter; Usage; Rate** 1; CURRENT; 4; Peak; 100; 0.1 1; CURRENT; 4; NonPeak; 200; 0.2 1; PROPOSED; 6; Peak; 100; 0.2 1; PROPOSED; 6; NonPeak; 200; 0.3 2; CURRENT; 4; Peak; 100; 0.1 2; CURRENT; 4; NonPeak; 200; 0.2 2; PROPOSED; 8; Peak; 200; 0.3 2; PROPOSED; 8; NonPeak; 200; 0.5 Again all I can think off is using combination of CURSOR and UNION but is there any TSQL that can do this? Thanks

    Read the article

  • Adapt beginner C# SQL Server INSERT example to work with my database

    - by Mike Bertelsen
    I have read TONS of tutorials, articles and whatever regarding my issue and honestly, due to my lack of experience I can't twist my fingers around this one so I hope some of you guys can help me out :) I am working on a project (simply to learn how to program so it's probably very basic), but I have this "News" page where I can update and delete data using a GridView. Now I would like to INSERT something into my database using 3 textboxes and 1 submit button. I have 3 rows that has to be inserted: Headline Date Content/the news itself. Which are stored under NyhedTB from the connectionstring: BoligStjernenConnectionString My query looks like this: INSERT INTO [NyhedTB] ([NyhedDato], [NyhedTitel], [NyhedTekst]) VALUES (@NyhedDato, @NyhedTitel, @NyhedTekst) I read on the internet that this code should do the magic for me (I will have to insert my own values ofc.): static void Insert() { try { string connectionString = "server=.;" + "initial catalog=employee;" + "user id=sa;" + "password=sa123"; using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand("INSERT INTO EmployeeDetails VALUES(" + "@Id, @Name, @Address)", conn)) { cmd.Parameters.AddWithValue("@Id", 1); cmd.Parameters.AddWithValue("@Name", "Amal Hashim"); cmd.Parameters.AddWithValue("@Address", "Bangalore"); int rows = cmd.ExecuteNonQuery(); //rows number of record got inserted } } } catch (SqlException ex) { //Log exception //Display Error message } } I looked at this code and thought it should be easy enough but really, I can't figure it out.

    Read the article

  • Increasing SQL Server / Sage performance with SSD? (Dell PE T410)

    - by Anthony
    I have a client wanting better performance of their Sage (Accpac & CRM) server (v5.5, soon to be v7). It's running on 1 of 2 Hyper-V VMs (Svr2008) on a Dell PE T410 server with 24GB of RAM (1333MHz) & dual quad-core, and both VMs (only their C: drives) are on a single RAID5 array. All clients connect via 1Gb ethernet. The 2nd VM is SBS2008 with 9GB RAM (& all SBS dbs & company data are on a separate RAID5 array), & 3GB RAM for the Svr2008 hypervisor. I've given the Sage/SQL Server VM all the RAM I can (12GB) & SQL Server RAM caching (~8GB, never exceeds ~7.5GB, eg. entire db can now be cached in RAM) and that's helped significantly. Upgrading the Hypervisor to Svr2012 is an obvious step, but probably not a dramatic improvement? What about an SSD for this Sage/SQL Server VM (VM = 100GB, <10GB for the actual live DB) ? Can SSDs be put into the SAS hot-swap bays? Or will I have to use the mobo SATA(3Gbps?) ports, or PCI-E SSD card? Should SSDs be RAIDed for this situation? Or is SSD's higher reliability offsetting the need for RAID1/5/10? (I have nightly full disk backups) New territory for me, would appreciate some feedback. Thanks, Anthony.

    Read the article

  • MS SQL SELECT stored procedure according to combobox.selectedvalue

    - by Jay
    Hello, In order to fill a datagridview according to the selectedvalue of a combobox I've tried creating a stored procedure. However, as I'm not 100% sure what I'm doing it, depending on the WHERE statement at the end of my stored procedure, either returns everything within the table or nothing at all. This is what's in my class: Public Function GetAankoopDetails(ByRef DisplayMember As String, ByRef ValueMember As String) As DataTable DisplayMember = "AankoopDetailsID" ValueMember = "AankoopDetailsID" If DS.Tables.Count > 0 Then DS.Tables.Remove(DT) End If DT = DAC.ExecuteDataTable(My.Resources.S_AankoopDetails, _Result, _ DAC.Parameter(Const_AankoopID, AankoopID), _ DAC.Parameter("@ReturnValue", 0)) DS.Tables.Add(DT) Return DT End Function Public Function GetAankoopDetails() As DataTable If DS.Tables.Count > 0 Then DS.Tables.Remove(DT) End If DT = DAC.ExecuteDataTable(My.Resources.S_AankoopDetails, _Result, _ DAC.Parameter(Const_AankoopID, AankoopID), _ DAC.Parameter("@ReturnValue", 0)) DS.Tables.Add(DT) Return DT End Function This is the function in the code behind the form I've written in order to fill the datagridview: Private Sub GridAankoopDetails_Fill() Try Me.Cursor = Cursors.WaitCursor dgvAankoopDetails.DataSource = Nothing _clsAankoopDetails.AankoopDetailsID = cboKeuze.SelectedValue dgvAankoopDetails.DataSource = _clsAankoopDetails.GetAankoopDetails Catch ex As Exception MessageBox.Show("An error occurred while trying to fill the data grid: " & ex.Message, "Oops!", MessageBoxButtons.OK) Finally Me.Cursor = Cursors.Default End Try End Sub And finally, this is my stored procedure: (do note that I'm not sure what I'm doing here) USE [Budget] GO /****** Object: StoredProcedure [dbo].[S_AankoopDetails] Script Date: 04/12/2010 03:10:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[S_AankoopDetails] ( @AankoopID int, @ReturnValue int output ) AS declare @Value int set @Value =@@rowcount if @Value = 0 begin SELECT dbo.tblAankoopDetails.AankoopDetailsID, dbo.tblAankoopDetails.AankoopID, dbo.tblAankoopDetails.ArtikelID, dbo.tblAankoopDetails.Aantal, dbo.tblAankoopDetails.Prijs, dbo.tblAankoopDetails.Korting, dbo.tblAankoopDetails.SoortKorting, dbo.tblAankoopDetails.UitgavenDeelGroepID FROM dbo.tblAankoopDetails INNER JOIN dbo.tblAankoop ON dbo.tblAankoopDetails.AankoopID = dbo.tblAankoop.AankoopID INNER JOIN dbo.tblArtikel ON dbo.tblAankoopDetails.ArtikelID = dbo.tblArtikel.ArtikelID INNER JOIN dbo.tblUitgavenDeelGroep ON dbo.tblAankoopDetails.UitgavenDeelGroepID = dbo.tblUitgavenDeelGroep.UitgavenDeelGroepID WHERE dbo.tblAankoopDetails.Deleted = 0 and dbo.tblAankoopDetails.AankoopID = @AankoopID ORDER BY AankoopID if @@rowcount >0 begin set @ReturnValue=999 end else begin set @ReturnValue=997 end end if @Value >0 begin --Dit wil zeggen dat ik een gebruiker wil ingeven die reeds bestaat. (998) set @ReturnValue=998 end Does anyone know what I'm need to do to resolve this? Kind regards, Jay

    Read the article

  • From varchar(36) to UNIQUEIDENTIFIER

    - by jgonchik
    I am trying to cast an AuctionId that is a UNIQUEIDENTIFIER to an varchar(36) and then back to an UNIQUEIDENTIFIER. Please help me. CAST((SUBSTRING(CAST([AuctionId] as VARCHAR(36)), 0, 35) + '1') AS UNIQUEIDENTIFIER) But I keep getting this error: Msg 8169, Level 16, State 2, Line 647 Conversion failed when converting from a character string to uniqueidentifier. Thanks in advance

    Read the article

  • TSQL Shred XML - Working with namespaces (newbie @ shredding XML)

    - by drachenstern
    Here's a link to my previous question on this same block of code with a working shred example Ok, I'm a C# ASP.NET dev following orders: The orders are to take a given dataset, shred the XML and return columns. I've argued that it's easier to do the shredding on the ASP.NET side where we already have access to things like deserializers, etc, and the entire complex of known types, but no, the boss says "shred it on the server, return a dataset, bind the dataset to the columns of the gridview" so for now, I'm doing what I was told. This is all to head off the folks who will come along and say "bad requirements". Task at hand: Current code that doesn't work: And if we modify the previous post to include namespaces on the XML elements, we lose the functionality that the previous post has... DECLARE @table1 AS TABLE ( ProductID VARCHAR(10) , Name VARCHAR(20) , Color VARCHAR(20) , UserEntered VARCHAR(20) , XmlField XML ) INSERT INTO @table1 SELECT '12345','ball','red','john','<sizes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><size xmlns="http://example.com/ns" name="medium"><price>10</price></size><size xmlns="http://example.com/ns" name="large"><price>20</price></size></sizes>' INSERT INTO @table1 SELECT '12346','ball','blue','adam','<sizes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><size xmlns="http://example.com/ns" name="medium"><price>12</price></size><size xmlns="http://example.com/ns" name="large"><price>25</price></size></sizes>' INSERT INTO @table1 SELECT '12347','ring','red','john','<sizes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><size xmlns="http://example.com/ns" name="medium"><price>5</price></size><size xmlns="http://example.com/ns" name="large"><price>8</price></size></sizes>' INSERT INTO @table1 SELECT '12348','ring','blue','adam','<sizes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><size xmlns="http://example.com/ns" name="medium"><price>8</price></size><size xmlns="http://example.com/ns" name="large"><price>10</price></size></sizes>' INSERT INTO @table1 SELECT '23456','auto','black','ann','<auto xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><type xmlns="http://example.com/ns">car</type><wheels xmlns="http://example.com/ns">4</wheels><doors xmlns="http://example.com/ns">4</doors><cylinders xmlns="http://example.com/ns">3</cylinders></auto>' INSERT INTO @table1 SELECT '23457','auto','black','ann','<auto xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><type xmlns="http://example.com/ns">truck</type><wheels xmlns="http://example.com/ns">4</wheels><doors xmlns="http://example.com/ns">2</doors><cylinders xmlns="http://example.com/ns">8</cylinders></auto><auto xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><type xmlns="http://example.com/ns">car</type><wheels xmlns="http://example.com/ns">4</wheels><doors xmlns="http://example.com/ns">4</doors><cylinders xmlns="http://example.com/ns">6</cylinders></auto>' DECLARE @x XML -- I think I'm supposed to use WITH XMLNAMESPACES(...) here but I don't know how SELECT @x = ( SELECT ProductID , Name , Color , UserEntered , XmlField.query(' for $vehicle in //auto return <auto type = "{$vehicle/type}" wheels = "{$vehicle/wheels}" doors = "{$vehicle/doors}" cylinders = "{$vehicle/cylinders}" />') FROM @table1 table1 WHERE Name = 'auto' FOR XML AUTO ) SELECT @x SELECT ProductID = T.Item.value('../@ProductID', 'varchar(10)') , Name = T.Item.value('../@Name', 'varchar(20)') , Color = T.Item.value('../@Color', 'varchar(20)') , UserEntered = T.Item.value('../@UserEntered', 'varchar(20)') , VType = T.Item.value('@type' , 'varchar(10)') , Wheels = T.Item.value('@wheels', 'varchar(2)') , Doors = T.Item.value('@doors', 'varchar(2)') , Cylinders = T.Item.value('@cylinders', 'varchar(2)') FROM @x.nodes('//table1/auto') AS T(Item) If my previous post shows there's a much better way to do this, then I really need to revise this question as well, but on the off chance this coding-style is good, I can probably go ahead with this as-is... Any takers?

    Read the article

  • Linq-to-sql Compiled Query is caching result from disposed DataContext

    - by Vladimir Kojic
    Compiled query: public static Func<OperationalDataContext, short, Machine> QueryMachineById = CompiledQuery.Compile((OperationalDataContext db, short machineID) => db.Machines.Where(m => m.MachineID == machineID).SingleOrDefault()); It looks like compiled query is caching Machine object and returning the same object even if query is called from new DataContext (I’m disposing DataContext in the service but I’m getting Machine from previous DataContext). I use POCOs and XML mapping. Getting cached object from the same datacontext is ok, but when I call query with new DataContext I don’t want to get object from old datacontext. Is there something that I don’t do right ? Thanks, Vladimir

    Read the article

  • complex sql which runs extremely slow when the query has order by clause

    - by basit.
    I have following complex query which I need to use. When I run it, it takes 30 to 40 seconds. But if I remove the order by clause, it takes 0.0317 sec to return the result, which is really fast compare to 30 sec or 40. select DISTINCT media.* , username from album as album , album_permission as permission , user as user, media as media where ((media.album_id = album.album_id and album.private = 'yes' and album.album_id = permission.album_id and (permission.email = '' or permission.user_id = '') ) or (media.album_id = album.album_id and album.private = 'no' ) or media.album_id = '0' ) and media.user_id = user.user_id and media.media_type = 'video' order by media.id DESC LIMIT 0,20 The id on order by is primary key which is indexed too. So I don't know what is the problem. I also have album and album permission table, just to check if media is public or private, if private then check if user has permission or not. I was thinking maybe that is causing the issue. What if I did this in sub query, would that work better? Also can someone help me write that sub query, if that is the solution? If you can't help write it, just at least tell me. I'm really going crazy with this issue.. SOLUTION MAYBE Yes, I think sub-query would be best solution for this, because the following query runs at 0.0022 seconds. But I'm not sure if validation of an album would be accurate or not, please check. select media.*, username from media as media , user as user where media.user_id = user.user_id and media.media_type = 'video' and media.id in (select media2.id from media as media2 , album as album , album_permission as permission where ((media2.album_id = album.album_id and album.private = 'yes' and album.album_id = permission.album_id and (permission.email = '' or permission.user_id = '')) or (media.album_id = album.album_id and album.private = 'no' ) or media.album_id = '0' ) and media.album_id = media2.album_id ) order by media.id DESC LIMIT 0,20

    Read the article

  • Help with SQL query (add 5% to users with conditions)

    - by Mestika
    Hi everyone, I’m having some difficulties with a query which purpose is to give users with more than one thread (called CS) in current year a 5% point “raise”. My relational schema looks like this: Thread = (threadid, threadname, threadLocation) threadoffering = (threadid, season, year, user) user = (name, points) Then, what I need is to check: WHERE thread.threadid = threadoffering.threadid AND where threadoffering.year AND threadoffering.season = currentDate AND where threadoffering.User 1 GIVE 5 % raise TO user.points I hope it is explained thoroughly but otherwise here it is in short text: Give a 5 % “point raise” to all users who has more than 1 thread in threadLocation CS in the current year and season (always dynamic, so for example now is year = 2010 and season is = spring). I am looking forward to your answer Sincerely, Emil

    Read the article

  • Select query 2-3 times faster than view

    - by Richard Knop
    This query run alone: SELECT -- lots of columns FROM (((((((((((`table1` `t1` LEFT JOIN `table2` `t2` ON(( `t2`.`userid` = `t1`.`userid` ))) LEFT JOIN `table3` `t3` ON(( `t1`.`orderid` = `t3`.`orderid` ))) LEFT JOIN `table4` `t4` ON(( `t4`.`orderitemlicenseid` = `t3`.`orderitemlicenseid` ))) LEFT JOIN `table5` `t5` ON(( `t1`.`orderid` = `t5`.`orderid` ))) LEFT JOIN `table6` `t6` ON(( `t5`.`transactionid` = `t6`.`transactionid` ))) LEFT JOIN `table7` `t7` ON(( `t7`.`transactionid` = `t5`.`transactionid` ))) LEFT JOIN `table8` `t8` ON(( `t8`.`voucherid` = `t7`.`voucherid` ))) LEFT JOIN `table9` `t9` ON(( `t8`.`voucherid` = `t9`.`voucherid` ))) LEFT JOIN `table10` `t10` ON(( ( `t10`.`vouchergroupid` = `t9`.`vouchergroupid` ) AND ( `t2`.`territoryid` = `t10`.`territoryid` ) ))) LEFT JOIN `table11` `t11` ON(( `t11`.`voucherid` = `t8`.`voucherid` ))) LEFT JOIN `table12` `t12` ON(( `t12`.`orderid` = `t1`.`orderid` ))) GROUP BY `t5`.`transactionid` Takes about 2.5 seconds to finish. When I save it to a view and run it as: SELECT * FROM viewName; It takes 7 seconds to finish. What is the reason and how can I make the view faster?

    Read the article

  • Checkbox on Sql Server Reporting Services Report

    - by George Handlin
    I'm working on a report in SSRS 2005 that is a questionnaire with yes/no answers. Trying to get a checkbox on the report. Have tried using windings for the font and an iif statement to set the character, but that doesn't come out correctly when exporting to PDF. I'm using local reports, not from a report server.

    Read the article

  • Linq to SQl over WCF Timesout after several calls

    - by Redeemed1
    I have a L2S Repository class which instantiates the L2S DataContext in its constructor. The repository is instantiated at run time (using Unity) in a service hosted in IIS with WCF. When I run up the client MVC applicaton the calls to the backend WCF service work for a while and then timeout. I suspected perhaps a database issue as I was depending on IIS garbage collection to dispose of unused DataContext instances in the IIS host but when I checked the characteristics of the problem I notice the following: The client makes the call to WCF but the WCF service does not respond. Next, the client times out Some time later (several minutes) the service actually executes the request by instantiating the repository and servicing the call. I have checked both client and server traces logs and only the client shows WCF errors (the timeout error). Where should I look? Is it something in WCF or is L2S possibly blocking with unfreed conenctions, resources etc.? Many thanks Brian

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 302 303 304 305 306 307 308 309 310 311 312 313  | Next Page >