Search Results

Search found 87875 results on 3515 pages for 'server pool'.

Page 871/3515 | < Previous Page | 867 868 869 870 871 872 873 874 875 876 877 878  | Next Page >

• How do you think while formulating Sql Queries. Is it an experience or a concept ?

  - by Shantanu Gupta

  I have been working on sql server and front end coding and have usually faced problem formulating queries. I do understand most of the concepts of sql that are needed in formulating queries but whenever some new functionality comes into the picture that can be dont using sql query, i do usually fails resolving them. I am very comfortable with select queries using joins and all such things but when it comes to DML operation i usually fails For every query that i never done before I usually finds uncomfortable with that while creating them. Whenever I goes for an interview I usually faces this problem. Is it their some concept behind approaching on formulating sql queries. Eg. I need to create an sql query such that A table contain single column having duplicate record. I need to remove duplicate records. I know i can find the solution to this query very easily on Googling, but I want to know how everyone comes to the desired result. Is it something like Practice Makes Man Perfect i.e. once you did it, next time you will be able to formulate or their is some logic or concept behind. I could have get my answer of solving above problem simply by posting it on stackoverflow and i would have been with an answer within 5 to 10 minutes but I want to know the reason. How do you work on any new kind of query. Is it a major contribution of experience or some an implementation of concepts. Whenever I learns some new thing in coding section I tries to utilize it wherever I can use it. But here scenario seems to be changed because might be i am lagging in some concepts.

  Read the article

• Updating a Foreign Key constraint with ON DELETE CASCADE not updating?

  - by Alastair Pitts

  We've realised in our SQL Server 2005 DB that some Foreign Keys don't have the On Delete Cascade property set, which is giving us a couple of referential errors when we try and delete some records. Use the Management Studio I scripted the DROP and CREATESQL's, but it seems that the CREATE isn't working correctly. The DROP: USE [FootprintReports] GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__SUBSCRIPTIONS_Reports]') AND parent_object_id = OBJECT_ID(N'[dbo].[_SUBSCRIPTIONS]')) ALTER TABLE [dbo].[_SUBSCRIPTIONS] DROP CONSTRAINT [FK__SUBSCRIPTIONS_Reports] and the CREATE USE [FootprintReports] GO ALTER TABLE [dbo].[_SUBSCRIPTIONS] WITH CHECK ADD CONSTRAINT [FK__SUBSCRIPTIONS_Reports] FOREIGN KEY([PARAMETER_ReportID]) REFERENCES [dbo].[Reports] ([ID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[_SUBSCRIPTIONS] CHECK CONSTRAINT [FK__SUBSCRIPTIONS_Reports] If I manually change the value of the On Delete in the GUI, after dropping and recreating, the On Delete isn't correctly updated. As a test, I set the Delete rule in the GUI to Set Null. It dropped correctly, and recreated without error. If I got back into the GUI, it is still showing the Set Null as the Delete Rule. Have I done something wrong? or is there another way to edit a constraint to add the ON DELETE CASCADE rule?

  Read the article

• Which non-clustered index should I use?

  - by Junior Mayhé

  Here I am studying nonclustered indexes on SQL Server Management Studio. I've created a table with more than 1 million records. This table has a primary key. CREATE TABLE [dbo].[Customers]( [CustomerId] [int] IDENTITY(1,1) NOT NULL, [CustomerName] [varchar](100) NOT NULL, [Deleted] [bit] NOT NULL, [Active] [bit] NOT NULL, CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [CustomerId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] This is the query I'll be using to see what execution plan is showing: SELECT CustomerName FROM Customers Well, executing this command with no additional non-clustered index, it leads the execution plan to show me: I/O cost = 3.45646 Operator cost = 4.57715 Now I'm trying to see if it's possible to improve performance, so I've created a non-clustered index for this table: 1) First non-clustered index CREATE NONCLUSTERED INDEX [IX_CustomerID_CustomerName] ON [dbo].[Customers] ( [CustomerId] ASC, [CustomerName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO Executing again the select against Customers table, the execution plan shows me: I/O cost = 2.79942 Operator cost = 3.92001 It seems better. Now I've deleted this just created non-clustered index, in order to create a new one: 2) First non-clustered index CREATE NONCLUSTERED INDEX [IX_CustomerIDIncludeCustomerName] ON [dbo].[Customers] ( [CustomerId] ASC ) INCLUDE ( [CustomerName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO With this new non-clustered index, I've executed the select statement again and the execution plan shows me the same result: I/O cost = 2.79942 Operator cost = 3.92001 So, which non-clustered index should I use? Why the costs are the same on execution plan for I/O and Operator? Am I doing something wrong or this is expected? thank you

  Read the article

• Finding values from a table that are *not* in a grouping of another table and what group that value

  - by Bkins

  I hope I am not missing something very simple here. I have done a Google search(es) and searched through stackoverflow. Here is the situation: For simplicity's sake let's say I have a table called "PeoplesDocs", in a SQL Server 2008 DB, that holds a bunch of people and all the documents that they own. So one person can have several documents. I also have a table called "RequiredDocs" that simply holds all the documents that a person should have. Here is sort of what it looks like: PeoplesDocs: PersonID DocID -------- ----- 1 A 1 B 1 C 1 D 2 C 2 D 3 A 3 B 3 C RequiredDocs: DocID DocName ----- --------- A DocumentA B DocumentB C DocumentC D DocumentD How do I write a SQL query that returns some variation of: PersonID MissingDocs -------- ----------- 2 DocumentA 2 DocumentB 3 DocumentD I have tried, and most of my searching has pointed to, something like: SELECT DocID FROM DocsRequired WHERE NOT EXIST IN ( SELECT DocID FROM PeoplesDocs) but obviously this will not return anything in this example because everyone has at least one of the documents. Also, if a person does not have any documents then there will be one record in the PeoplesDocs table with the DocID set to NULL. Thank you in advance for any help you can provide, Ben

  Read the article

• MS SQL datetime precision problem

  - by Nailuj

  I have a situation where two persons might work on the same order (stored in an MS SQL database) from two different computers. To prevent data loss in the case where one would save his copy of the order first, and then a little later the second would save his copy and overwrite the first, I've added a check against the lastSaved field (datetime) before saving. The code looks roughly like this: private bool orderIsChangedByOtherUser(Order localOrderCopy) { // Look up fresh version of the order from the DB Order databaseOrder = orderService.GetByOrderId(localOrderCopy.Id); if (databaseOrder != null && databaseOrder.LastSaved > localOrderCopy.LastSaved) { return true; } else { return false; } } This works for most of the time, but I have found one small bug. If orderIsChangedByOtherUser returns false, the local copy will have its lastSaved updated to the current time and then be persisted to the database. The value of lastSaved in the local copy and the DB should now be the same. However, if orderIsChangedByOtherUser is run again, it sometimes returns true even though no other user has made changes to the DB. When debugging in Visual Studio, databaseOrder.LastSaved and localOrderCopy.LastSaved appear to have the same value, but when looking closer they some times differ by a few milliseconds. I found this article with a short notice on the millisecond precision for datetime in SQL: Another problem is that SQL Server stores DATETIME with a precision of 3.33 milliseconds (0. 00333 seconds). The solution I could think of for this problem, is to compare the two datetimes and consider them equal if they differ by less than say 10 milliseconds. My question to you is then: are there any better/safer ways to compare two datetime values in MS SQL to see if they are exactly the same?

  Read the article

• Query for rows including child rows

  - by MAD9

  A few weeks ago, I asked a question about how to generate hierarchical XML from a table, that has a parentID column. It all works fine. The point is, according to the hierarchy, I also want to query a table. I'll give you an example: Thats the table with the codes: ID CODE NAME PARENTID 1 ROOT IndustryCode NULL 2 IND Industry 1 3 CON Consulting 1 4 FIN Finance 1 5 PHARM Pharmaceuticals 2 6 AUTO Automotive 2 7 STRAT Strategy 3 8 IMPL Implementation 3 9 CFIN Corporate Finance 4 10 CMRKT Capital Markets 9 From which I generate (for displaying in a TreeViewControl) this XML: <record key="1" parentkey="" Code="ROOT" Name="IndustryCode"> <record key="2" parentkey="1" Code="IND" Name="Industry"> <record key="5" parentkey="2" Code="PHARM" Name="Pharmaceuticals" /> <record key="6" parentkey="2" Code="AUTO" Name="Automotive" /> </record> <record key="3" parentkey="1" Code="CON" Name="Consulting"> <record key="7" parentkey="3" Code="STRAT" Name="Strategy" /> <record key="8" parentkey="3" Code="IMPL" Name="Implementation" /> </record> <record key="4" parentkey="1" Code="FIN" Name="Finance"> <record key="9" parentkey="4" Code="CFIN" Name="Corporate Finance"> <record key="10" parentkey="9" Code="CMRKT" Name="Capital Markets" /> </record> </record> </record> As you can see, some codes are subordinate to others, for example AUTO << IND << ROOT What I want (and have absolutely no idea how to realise or even, where to start) is to be able to query another table (where one column is this certain code of course) for a code and get all records with the specific code and all subordinate codes For example: I query the other table for "IndustryCode = IND[ustry]" and get (of course) the records containing "IND", but also AUTO[motive] and PHARM[aceutical] (= all subordinates) Its an SQL Express Server 2008 with Advanced Services.

  Read the article

• Query gives an unsorted result set when run from stored procedure using CTE

  - by irtizaur

  I am trying to create a paging query using CTE. It works fine when I execute it from Microsoft SQL Server Management Studio Query Editor. And the result set is perfectly sorted as I want. But when I modify it for a stored procedure it gives me a unsorted result and I don't have any clue why. Here is my Query, with items as ( select ROW_NUMBER() over (order by create_time desc) number , i.item_name item_name , i.create_time create_time , c.category_name category_name , i.category_id category_id from cb_item i, cb_category c where i.category_id = c.category_id and c.category_id = '4E5248FE-05DD-4D01-ABBB-80C6E3BA5CDA' ) select item_name , create_time , category_name , category_id from items where number between 1 and 25 And this is the Stored Procedure Version, create procedure ItemPage @category_id uniqueidentifier , @from int , @to int , @sortby nvarchar(50) as begin with items as ( select ROW_NUMBER() over (order by @sortby) number , i.item_name item_name , i.create_time create_time , c.category_name category_name , i.category_id category_id from cb_item i, cb_category c where i.category_id = c.category_id and c.category_id = @category_id ) select item_name , create_time , category_name , category_id from items where number between @from and @to end exec itempage '4E5248FE-05DD-4D01-ABBB-80C6E3BA5CDA' , 1, 25, 'create_time desc' The first one gives me sorted result but procedure gives me unsorted result. I don't know why?

  Read the article

• encrypt apache and mysql servers

  - by stormdrain

  I have a question about encrypting disks. I have 2 servers: 1 is apache for web/frontend and it talks to server 2 which is mysql. They are all for intranet only; no external access. I was looking into using PGP or GnuPG to encrypt the disks. I'm not clear, though, as to exactly how this would work. Where would the keys be stored? On the client? On apache? If there is a key on apache to access mysql, does there need to be a key for each user? If so, if key 1 is used to alter some data, would then that data be inaccessible to a user using key 2? And the apache key, would that only be accessible to users with local keys? Is encryption done on the fly? Does it degrade performance? What would be the best approach to encrypt the data on these servers, but have them accessible to users? Thanks!

  Read the article

• SQL GUID Vs Integer

  - by Dal

  Hi I have recently started a new job and noticed that all the SQL tables use the GUID data type for the primary key. In my previous job we used integers (Auto-Increment) for the primary key and it was a lot more easier to work with in my opinion. For example, say you had two related tables; Product and ProductType - I could easily cross check the 'ProductTypeID' column of both tables for a particular row to quickly map the data in my head because its easy to store the number (2,4,45 etc) as opposed to (E75B92A3-3299-4407-A913-C5CA196B3CAB). The extra frustration comes from me wanting to understand how the tables are related, sadly there is no Database diagram :( A lot of people say that GUID's are better because you can define the unique identifer in your C# code for example using NewID() without requiring SQL SERVER to do it - this also allows you to know provisionally what the ID will be.... but I've seen that it is possible to still retrieve the 'next auto-incremented integer' too. A DBA contractor reported that our queries could be up to 30% faster if we used the Integer type instead of GUIDS... Why does the GUID data type exist, what advantages does it really provide?... Even if its a choice by some professional there must be some good reasons as to why its implemented?

  Read the article

• C# and SQL - sub select from a table parameter

  - by Dr.HappyPants

  Below is a code snippet for passing a table as a parameter to a query that can be used in Sql Server 2008. I'm confused though about the "SELECT id.custid FROM @custids id". Why does it use id.custid and @custids id...? private static void datatable_example() { string [] custids = {"ALFKI", "BONAP", "CACTU", "FRANK"}; DataTable custid_list = new DataTable(); custid_list.Columns.Add("custid", typeof(String)); foreach (string custid in custids) { DataRow dr = custid_list.NewRow(); dr["custid"] = custid; custid_list.Rows.Add(dr); } using(SqlConnection cn = setup_connection()) { using(SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = @"SELECT C.CustomerID, C.CompanyName FROM Northwind.dbo.Customers C WHERE C.CustomerID IN (SELECT id.custid FROM @custids id)"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add("@custids", SqlDbType.Structured); cmd.Parameters["@custids"].Direction = ParameterDirection.Input; cmd.Parameters["@custids"].TypeName = "custid_list_tbltype"; cmd.Parameters["@custids"].Value = custid_list; using (SqlDataAdapter da = new SqlDataAdapter(cmd)) using (DataSet ds = new DataSet()) { da.Fill(ds); PrintDataSet(ds); } } }

  Read the article

• Why use INCLUDE in a SQL index

  - by StarLite

  I recently encountered an index in a database I maintain that was of the form: CREATE INDEX [IX_Foo] ON [Foo] ( Id ASC ) INCLUDE ( SubId ) In this particular case, the performance problem that I was encountering (a slow SELECT filtering on both Id and SubId) could be fixed by simply moving the SubId column into the index proper rather than as an included column. This got me thinking however that I don't understand the reasoning behind included columns at all, when generally, they could simply be a part of the index itself. Even if I don't particularly care about the items being in the index itself is there any downside to having column in the index rather than simply being included. After some research, I am aware that there are a number of restrictions on what can go into an indexed column (maximum width of the index, and some column types that can't be indexed like 'image'). In these cases I can see that you would be forced to include the column in the index page data. The only thing I can think of is that if there are updates on SubId, the row will not need to be relocated if the column is included (though the value in the index would need to be changed). Is there something else that I'm missing? I'm considering going through the other indexes in the database and shifting included columns in the index proper where possible. Would this be a mistake? I'm primarily interested in MS SQL Server, but information on other DB engines is welcome also.

  Read the article

• Help! The log file for database 'tempdb' is full. Back up the transaction log for the database to fr

  - by michael.lukatchik

  We're running SQL Server 2000. In our database, we have an "Orders" table with approximately 750,000 rows. We can perform simple SELECT statements on this table. However, when we want to run a query like SELECT TOP 100 * FROM Orders ORDER BY Date_Ordered DESC, we receive the following message: Error: 9002, Severity: 17, State: 6 The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space. We have other tables in our database which are similar in size of the amount of records that are in the tables (i.e. 700,000 records). On these tables, we can run any queries we'd like and we never receive a message about 'tempdb being full'. To resolve this, we've backed up our database, shrunk the actual database and also shrunk the database and files in the tempdb system database, but this hasn't resolved the issue. The size of our log file is set to autogrow. We're not sure where to go next. Are there any ideas why we still might be receiving this message? Error: 9002, Severity: 17, State: 6 The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.

  Read the article

• Procedure or function AppendDataCT has too many arguments specified

  - by salvationishere

  I am developing a C# VS 2008 / SQL Server website application. I am a newbie to ASP.NET. I am getting the above compiler error. Can you give me advice on how to fix this? Code snippet: public static string AppendDataCT(DataTable dt, Dictionary<int, string> dic) { string connString = ConfigurationManager.ConnectionStrings["AW3_string"].ConnectionString; string errorMsg; try { SqlConnection conn2 = new SqlConnection(connString); SqlCommand cmd = conn2.CreateCommand(); cmd.CommandText = "dbo.AppendDataCT"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn2; SqlParameter p1, p2, p3; foreach (string s in dt.Rows[1].ItemArray) { DataRow dr = dt.Rows[1]; // second row p1 = cmd.Parameters.AddWithValue((string)dic[0], (string)dr[0]); p1.SqlDbType = SqlDbType.VarChar; p2 = cmd.Parameters.AddWithValue((string)dic[1], (string)dr[1]); p2.SqlDbType = SqlDbType.VarChar; p3 = cmd.Parameters.AddWithValue((string)dic[2], (string)dr[2]); p3.SqlDbType = SqlDbType.VarChar; } conn2.Open(); cmd.ExecuteNonQuery(); It errors on this last line here. And here is that SP: ALTER PROCEDURE [dbo].[AppendDataCT] @col1 VARCHAR(50), @col2 VARCHAR(50), @col3 VARCHAR(50) AS BEGIN SET NOCOUNT ON; DECLARE @TEMP DATETIME SET @TEMP = (SELECT CONVERT (DATETIME, @col3)) INSERT INTO Person.ContactType (Name, ModifiedDate) VALUES( @col2, @TEMP) END

  Read the article

• Creating LINQ to SQL Data Models' Data Contexts with ASP.NET MVC

  - by Maxim Z.

  I'm just getting started with ASP.NET MVC, mostly by reading ScottGu's tutorial. To create my database connections, I followed the steps he outlined, which were to create a LINQ-to-SQL dbml model, add in the database tables through the Server Explorer, and finally to create a DataContext class. That last part is the part I'm stuck on. In this class, I'm trying to create methods that work around the exposed data. Following the example in the tutorial, I created this: using System; using System.Collections.Generic; using System.Linq; using System.Web; namespace MySite.Models { public partial class MyDataContext { public List<Post> GetPosts() { return Posts.ToList(); } public Post GetPostById(int id) { return Posts.Single(p => p.ID == id); } } } As you can see, I'm trying to use my Post data table. However, it doesn't recognize the "Posts" part of my code. What am I doing wrong? I have a feeling that my problem is related to my not adding the data tables correctly, but I'm not sure. Thanks in advance.

  Read the article

• Debugging in VS 2008 locks a stored procedure

  - by larryq

  Hi everyone, I've got a strange one here. I have a .Net executable that, under the hood, calls a few stored procedures. For whatever reason, one of the stored procs hangs when I'm debugging. If I run the executable outside of visual studio things go fine, including this stored proc. It's when I'm debugging that this hangs, and it really hangs. If I stop the debugging session the IDE freezes and I have to kill it via taskmanager. I know which stored procedure has the trouble, as well as the actual statement within it that's the problem. It's calling an update statement that doesn't stand out as particularly special. I can run the identical statement (and the stored procedure itself) from SQL management studio wtih no problem. And, as I mentioned, the exe runs just fine outside the debugger. If I use the SQL activity monitor to see why things are hanging, the wait type says PREEMPTIVE_DEBUG. I'm not sure if that's helpful but if you need more info I'll try to get it to you. I've rebooted my machine (the SQL Server in question is on this box as well) and that didn't do anything, nor did rebuilding the executable. I'm scratching my head on this one and if you have any ideas what to check on next, I'm be happy to listen. Thanks!

  Read the article

• ADO.NET zombie transaction bug? How to ensure that commands will not be executed on implicit transac

  - by TN

  e.g. When deadlock occurs, following SQL commands are successfully executed, even if they have assigned SQL transaction that is after rollback. It seems, it is caused by a new implicit transaction that is created on SQL Server. Someone could expect that ADO.NET would throw an exception that the commands are being executed on a zombie transaction. However, such exception is not thrown. (I think this is a bug in ASP.NET.) Moreover, because of zombie transaction the final Dispose() silently ignores the rollback. Any ideas, how can I ensure that nobody can execute commands on implicit transaction? Or, how to check that transaction is zombie? I found that Commit() and Rollback() check for zombie transaction, however I can call them for a test:) I also found that also reading IsolationLevel will do the check, but I am not sure whether simple calling transaction.IsolationLevel.ToString(); will not be removed by a future optimizer. Or do you know any other safe way invoke a getter (without using reflection or IL emitting)?

  Read the article

• Full Text Index type column is empty

  - by RemotecUk

  I am trying to create an index on a VarBinary(max) field in my SQL Server 2008 database. The steps I am taking are as follows: Table: dbo.Records Right click on table and select "Full Text Index" Then select "Define Index..." I choose the primary key which is the PK of my table (field name Id, type UniqueIndentifier). I then get the screen with the options Available Columns, Language for Word Breaker and Type Column I select my VarBinary(max) field called Chart as the Available Column by ticking the box. I select "English" as the Language for Word Breaker field. Then... I try to select the Type Column but there are no entries in here. I cannot proceed by clicking "Next" until this column is populated. Why are there no entries in this column for selection and what should be in there? Note 1: The VarBinary(max) field is linked to a file group if that makes any difference. Note 2: Also noticed that in the table designer I cannot set the full text option on that same field to "Yes" - its permanently stuck on "No". Thanks.

  Read the article

• Insert records using ExecuteNonQuery, showing exception that invalid column name

  - by tina

  Hi all, I am using SQL Server 2008. I want to insert records into a table using ExecuteNonQuery, for that I have written: customUtility.ExecuteNonQuery("insert into furniture_ProductAccessories(Product_id, Accessories_id, SkuNo, Description1, Price, Discount) values(" + prodid + "," + strAcc + "," + txtSKUNo.Text + "," + txtAccDesc.Text + "," + txtAccPrices.Text + "," + txtAccDiscount.Text + ")"); & following is ExecuteNonQuery function: public static bool ExecuteNonQuery(string SQL) { bool retVal = false; using (SqlConnection con = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["dbConnect"].ToString())) { con.Open(); SqlTransaction trans = con.BeginTransaction(); SqlCommand command = new SqlCommand(SQL, con, trans); try { command.ExecuteNonQuery(); trans.Commit(); retVal = true; } catch(Exception ex) { //HttpContext.Current.Response.Write(SQL + "<br>" + ex.Message); //HttpContext.Current.Response.End(); } finally { // Always call Close when done reading. con.Close(); } return retVal; } } but it showing exception that invalid column name to Description1 and even it's value which coming from txtAccDesc.Text. I have tried by removing Description1 column, other records are getting inserted successfully. Could you please help me? Thanks.

  Read the article

• Django aggregate query generating SQL error

  - by meepmeep

  I'm using Django 1.1.1 on a SQL Server 2005 db using the latest sqlserver_ado library. models.py includes: class Project(models.Model): name = models.CharField(max_length=50) class Thing(models.Model): project = models.ForeignKey(Project) reference = models.CharField(max_length=50) class ThingMonth(models.Model): thing = models.ForeignKey(Thing) timestamp = models.DateTimeField() ThingMonthValue = models.FloatField() class Meta: db_table = u'ThingMonthSummary' In a view, I have retrieved a queryset called 'things' which contains 25 Things: things = Thing.objects.select_related().filter(project=1).order_by('reference') I then want to do an aggregate query to get the average ThingMonthValue for the first 20 of those Things for a certain period, and the same value for the last 5. For the first 20 I do: averageThingMonthValue = ThingMonth.objects.filter(turbine__in=things[:20],timestamp__range="2009-01-01 00:00","2010-03-00:00")).aggregate(Avg('ThingMonthValue'))['ThingMonthValue__avg'] This works fine, and returns the desired value. For the last 5 I do: averageThingMonthValue = ThingMonth.objects.filter(turbine__in=things[20:],timestamp__range="2009-01-01 00:00","2010-03-00:00")).aggregate(Avg('ThingMonthValue'))['ThingMonthValue__avg'] But for this I get an SQL error: 'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.' The SQL query being used by django reads: SELECT AVG([ThingMonthSummary].[ThingMonthValue]) AS [ThingMonthValue__avg] FROM [ThingMonthSummary] WHERE ([ThingMonthSummary].[thing_id] IN (SELECT _row_num, [id] FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY [AAAA].[id] ASC) as _row_num, [AAAA].[id] FROM ( SELECT U0.[id] FROM [Thing] U0 WHERE U0.[project_id] = 1 ) AS [AAAA]) as QQQ where 20 < _row_num) AND [ThingMonthSummary].[timestamp] BETWEEN '01/01/09 00:00:00' and '03/01/10 00:00:00') Any idea why it works for one slice of the Things and not the second? I've checked and the two slices do contain the desired Things correctly.

  Read the article

• Sql Serve - Cascade delete has multiple paths

  - by Anders Juul

  Hi all, I have two tables, Results and ComparedResults. ComparedResults has two columns which reference the primary key of the Results table. My problem is that if a record in Results is deleted, I wish to delete all records in ComparedResults which reference the deleted record, regardless of whether it's one column or the other (and the columns may reference the same Results row). A row in Results may deleted directly or through cascade delete caused by deleting in a third table. Googling this could indicate that I need to disable cascade delete and rewrite all cascade deletes to use triggers instead. Is that REALLY nessesary? I'd be prepared to do much restructuring of the database to avoid this, as my main area is OO programming, and databases should 'just work'. It is hard to see, however, how a restructuring could help as I would just move the problem around... Or am I missing something? I am also a bit at a loss as to why my initial construct should even be a problem for the Sql Server?! Any comments welcome and much appreciated! Anders, Denmark

  Read the article

• Error in datatype (nvarchar instead of ntext)

  - by prabu R

  I am importing data from excel(.xls) to SQL Server 2008 using SSIS. I have included IMEX=1 in the connection string of excel connection manager. But a column consists of a value as below: 4-Hour Engineer Dispatch ASPP Engr Dispatch 1: Up to 1 dispatch (8 hours) per year. Hours exceeding allocation billed @ 1.5x hourly rate w/ 8-hr min Engr Dispatch: 8-hrs to arrive on-site from Ciena's determination of need On-Site Engineer Dispatch - 8 Hour ASPP Engr Dispatch 8: Up to 8 dispatch (64 hours) per year. Hours exceeding allocation billed @ 1.5x hourly rate w/ 8-hr min Engr Dispatch: NBD to dispatch from Ciena's determination of need Per Incident On Site Support ASPP Engr Dispatch 12: Up to 12 dispatch (96 hours) per year. Hours exceeding allocation billed @ 1.5x hourly rate w/ 8-hr min Engr Dispatch: Next day to arrive on-site from Ciena's determination of need Resident Engineer Engr Dispatch: 2-hrs to arrive on-site from Ciena's determination of need Engr Dispatch: 4-hrs to arrive on-site from Ciena's determination of need ASPP Engr Dispatch 2: Up to 2 dispatch (16 hours) per year. Hours exceeding allocation billed @ 1.5x hourly rate w/ 8-hr min N Actually there are about 600 rows in that excel file. But the above mentioned value is present after 450 rows only. So, the datatype of that column is taken as nvarchar(255) as default instead of ntext and so i am getting error. Anybody please help out... Thanks in advance...

  Read the article

• deadlock because of foreign key?

  - by George2

  Hello everyone, I am using SQL Server 2008 Enterprise. I met with deadlock in the following store procedure, but because of my fault, I did not record the deadlock graph. But now I can not reproduce deadlock issue. I want to have a postmortem to find the root cause of deadlock to avoid deadlock in the future. The deadlock happens on delete statement. For the delete statement, Param1 is a column of table FooTable, Param1 is a foreign key of another table (refers to another primary key clustered index column of the other table). There is no index on Param1 itself for table FooTable. FooTable has another column which is used as clustered primary key, but not Param1 column. Here is my guess why there is deadlock, and I want to let people review whether my analysis is correct? Since Param1 column has no index, there will be a table scan, and will acquire table level lock, because of foreign key, the delete operation will also need to check master table (e.g. to acquire lock on master table); Some operation on master table acquires master table lock, but want to acquire lock on FooTable; (1) and (2) cause cycle lock which makes deadlock happen. My analysis correct? Any reproduce scenario? create PROCEDURE [dbo].[FooProc] ( @Param1 int ,@Param2 int ,@Param3 int ) AS DELETE FooTable WHERE Param1 = @Param1 INSERT INTO FooTable ( Param1 ,Param2 ,Param3 ) VALUES ( @Param1 ,@Param2 ,@Param3 ) DECLARE @ID bigint SET @ID = ISNULL(@@Identity,-1) IF @ID > 0 BEGIN SELECT IdentityStr FROM FooTable WHERE ID = @ID END thanks in advance, George

  Read the article

• how can I speed up insertion of many rows to a table via ADO.NET?

  - by jcollum

  I have a table that has 5 columns: AcctId (int), Address1 (varchar), Address2 (varchar), Person1 (varchar), Person2 (varchar) . I'm generating random data to insert into this table via a C# console application. I've tried doing this random data insert via SQL-Server and decided it was not a good solution -- SQL is not good at random on an each-row basis. Generating the random data -- 975k rows of it -- takes a minimal amount of time. It's in a List of custom objects. I need to take this random data and update many rows in the database with the new random data. I tried updating the rows one at a time, very slow because of the repeated searching of the List object in code. So I think the best approach is to put all the randomized data into a table in the database, then update all the other tables that use this data. I.e. UPDATE t SET t.Address1=d.Address1 FROM Table1 t INNER JOIN RandomizedData d ON d.AcctId = t.Acct_ID. The database is very un-normalized so this Acct data is sprinkled all over the place. I've got no control of the normalization. So, having decided to insert all of the randomized data into a single table, I set out to create insert scripts: USE TheDatabase Insert tmp_RandomizedData SELECT 1,'4392 EIGHTH AVE','','JENNIFER CARTER','BARBARA CARTER' UNION ALL SELECT 2,'2168 MAIN ST','HNGR F','DANIEL HERNANDEZ','SUSAN MARTIN' // etc another 98 times... // FYI, this is not real data! I'm building this INSERT script in batches of 100. It's taking on average 175 ms to run each insert. Does this seem like a long time? It's going to take about 35 mins to run the whole insert. The table doesn't have a primary key or any indexes. I was planning on adding those after all the data in inserted (thinking that that would be faster). Is there a better way to do this?

  Read the article

• Asp.net mvc - trying to display images pulled from db \

  - by Trey Carroll

  //Inherits="System.Web.Mvc.ViewPage<FilmFestWeb.Models.ListVideosViewModel>" <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server"> <h2>ListVideos</h2> <% foreach(BusinessObjects.Video vid in Model.VideoList){%> <div class="videoBox"> <%= Html.Encode(vid.Name) %> <img src="<%= vid.ThumbnailImage %>" /> </div> <%} %> </asp:Content> //ListVideosViewModel public class ListVideosViewModel { public IList<Video> VideoList { get; set; } } //Video public class Video { public long VideoId { get; set; } public long TeamId { get; set; } public string Name { get; set; } public string Tags { get; set; } public string TeamMembers { get; set; } public string TranscriptFileName { get; set; } public string VideoFileName { get; set; } public int TotalNumRatings { get; set; } public int CumulativeTotalScore { get; set; } public string VideoUri { get; set; } public Image ThumbnailImage { get; set; } } I am getting the "red x" that I usually associate with image file not found. I have verified that my database table shows after the stored proc that uploads the image executes. Any insight or advice would be greatly appreciated.

  Read the article

• copy entire row (without knowing field names)

  - by Todd Webb

  Using SQL Server 2008, I would like to duplicate one row of a table, without knowing the field names. My key issue: as the table grows and mutates over time, I would like this copy-script to keep working, without me having to write out 30+ ever-changing fields, ugh. Also at issue, of course, is IDENTITY fields cannot be copied. My code below does work, but I wonder if there's a more appropriate method than my thrown-together text string SQL statement? So thank you in advance. Here's my (yes, working) code - I welcome suggestions on improving it. Todd alter procedure spEventCopy @EventID int as begin -- VARS... declare @SQL varchar(8000) -- LIST ALL FIELDS (*EXCLUDE* IDENTITY FIELDS). -- USE [BRACKETS] FOR ANY SILLY FIELD-NAMES WITH SPACES, OR RESERVED WORDS... select @SQL = coalesce(@SQL + ', ', '') + '[' + column_name + ']' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'EventsTable' and COLUMNPROPERTY(OBJECT_ID('EventsTable'), COLUMN_NAME, 'IsIdentity') = 0 -- FINISH SQL COPY STATEMENT... set @SQL = 'insert into EventsTable ' + ' select ' + @SQL + ' from EventsTable ' + ' where EventID = ' + ltrim(str(@EventID)) -- COPY ROW... exec(@SQL) -- REMEMBER NEW ID... set @EventID = @@IDENTITY -- (do other stuff here) -- DONE... -- JUST FOR KICKS, RETURN THE SQL STATEMENT SO I CAN REVIEW IT IF I WISH... select EventID = @EventID, SQL = @SQL end

  Read the article

< Previous Page | 867 868 869 870 871 872 873 874 875 876 877 878  | Next Page >