Search Results

Search found 29121 results on 1165 pages for 'sql 2000'.

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 315/1165 | < Previous Page | 311 312 313 314 315 316 317 318 319 320 321 322  | Next Page >

  • MS SQL Database with clustered GUID PKs - switch clustered index or switch to sequential (comb) GUID

    - by Eyvind
    We have a database in which all the PKs are GUIDs, and most of the PKs are also the clustered index for the table. We know that this is bad (due to the random nature of GUIDs). So, it seems there are basically two options here (short of throwing out GUIDs as PKs altogether, which we cannot do (at least not at this time)). We could change the GUID generation algorithm to e.g. the one that NHibernate uses, as detailed in this post, or we could, for the tables that are under the heaviest use, change to a different clustered index, e.g. an IDENTITY column, and keep the "random" GUIDs as PKs. Is it possible to give any general recommendations in such a scenario? The application in question has 500+ tables, the largest one presently at about 1,5 million rows, a few tables around 500 000 rows, and the rest significantly lower (most of them well below 10K). Furthermore, the application is installed at several customer sites already, so we have to take any possible negative effects for existing customer into consideration. Thanks!

    Read the article

  • SQL - Rank() on a table

    - by Abhi
    create table v (mydate,value) as select to_date('20/03/2010 00','dd/mm/yyyy HH24'),98 from dual union all select to_date('20/03/2010 01','dd/mm/yyyy HH24'),124 from dual union all select to_date('20/03/2010 02','dd/mm/yyyy HH24'),140 from dual union all select to_date('20/03/2010 03','dd/mm/yyyy HH24'),138 from dual union all select to_date('20/03/2010 04','dd/mm/yyyy HH24'),416 from dual union all select to_date('20/03/2010 05','dd/mm/yyyy HH24'),196 from dual union all select to_date('20/03/2010 06','dd/mm/yyyy HH24'),246 from dual union all select to_date('20/03/2010 07','dd/mm/yyyy HH24'),176 from dual union all select to_date('20/03/2010 08','dd/mm/yyyy HH24'),124 from dual union all select to_date('20/03/2010 09','dd/mm/yyyy HH24'),128 from dual union all select to_date('20/03/2010 10','dd/mm/yyyy HH24'),32010 from dual union all select to_date('20/03/2010 11','dd/mm/yyyy HH24'),384 from dual union all select to_date('20/03/2010 12','dd/mm/yyyy HH24'),368 from dual union all select to_date('20/03/2010 13','dd/mm/yyyy HH24'),392 from dual union all select to_date('20/03/2010 14','dd/mm/yyyy HH24'),374 from dual union all select to_date('20/03/2010 15','dd/mm/yyyy HH24'),350 from dual union all select to_date('20/03/2010 16','dd/mm/yyyy HH24'),248 from dual union all select to_date('20/03/2010 17','dd/mm/yyyy HH24'),396 from dual union all select to_date('20/03/2010 18','dd/mm/yyyy HH24'),388 from dual union all select to_date('20/03/2010 19','dd/mm/yyyy HH24'),360 from dual union all select to_date('20/03/2010 20','dd/mm/yyyy HH24'),194 from dual union all select to_date('20/03/2010 21','dd/mm/yyyy HH24'),234 from dual union all select to_date('20/03/2010 22','dd/mm/yyyy HH24'),328 from dual union all select to_date('20/03/2010 23','dd/mm/yyyy HH24'),216 from dual From this table, how to rank() over 'value', partitioning by each hour of the day? and select only the 1st ranked result?

    Read the article

  • Don't Understand Sql Server Error

    - by Jonathan Wood
    I have a table of users (User), and need to create a new table to track which users have referred other users. So, basically, I'm creating a many-to-many relation between rows in the same table. So I'm trying to create table UserReferrals with the columns UserId and UserReferredId. I made both columns a compound primary key. And both columns are foreign keys that link to User.UserID. Since deleting a user should also delete the relationship, I set both foreign keys to cascade deletes. When the user is deleted, any related rows in UserReferrals should also delete. But this gives me the message: 'User' table saved successfully 'UserReferrals' table Unable to create relationship 'FK_UserReferrals_User'. Introducing FOREIGN KEY constraint 'FK_UserReferrals_User' on table 'UserReferrals' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors. I don't get this error. A cascading delete only deletes the row with the foreign key, right? So how can it cause "cycling cascade paths"? Thanks for any tips.

    Read the article

  • 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 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

  • 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

  • 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

  • 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

  • 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

  • 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

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 | 311 312 313 314 315 316 317 318 319 320 321 322  | Next Page >