Search Results

Search found 27447 results on 1098 pages for 'sql warrior'.

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 320/1098 | < Previous Page | 316 317 318 319 320 321 322 323 324 325 326 327  | Next Page >

  • Multiple IN statements for WHERE. Would this return good data?

    - by TheDudeAbides
    SELECT ['VISA CK - 021810$'].[ACCT NBR #1], ['VISA CK - 021810$'].[ALT CUST NM #1], ['VISA CK - 021810$'].[LAST USED] FROM ['VISA CK - 021810$'] WHERE ['VISA CK - 021810$'].[ALT CUST NM #1] IN ( SELECT ['VISA CK - 021810$'].[ALT CUST NM #1] FROM ['VISA CK - 021810$'] GROUP BY ['VISA CK - 021810$'].[ALT CUST NM #1] HAVING COUNT(['VISA CK - 021810$'].[ALT CUST NM #1]) > 1 ) AND ['VISA CK - 021810$'].[ACCT NBR #1] IN ( SELECT ['VISA CK - 021810$'].[ACCT NBR #1] FROM ['VISA CK - 021810$'] GROUP BY ['VISA CK - 021810$'].[ACCT NBR #1] HAVING COUNT(['VISA CK - 021810$'].[ACCT NBR #1]) > 1 )

    Read the article

  • What to do with syncobj in SQL Server

    - by hgulyan
    Hi. I run this script to search particular text in sys.columns and I get a lot of "dbo.syncobj_0x3934443438443332" this kind of result. SELECT c.name, s.name + '.' + o.name FROM sys.columns c INNER JOIN sys.objects o ON c.object_id=o.object_id INNER JOIN sys.schemas s ON o.schema_id=s.schema_id WHERE c.name LIKE '%text%' If I get it right, they are replication objects. Is it so? Can i just throw them away from my query just like o.name NOT LIKE '%syncobj%' or there's another way? Thank you.

    Read the article

  • SQL Pivot table error-using variable gives syntax error

    - by Antoni
    Hi my coworker came to me with this error and now I am hooked and trying to figure it out, hope some of the experts can help us! Thanks so much! When I execute Step6 we get this error: Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '@cols'. --Sample of pivot query --Creating Test Table Step1 CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT) GO -- Inserting Data into Table Step2 INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','VEG',2) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','SODA',6) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','MILK',1) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','BEER',12) INSERT INTO Product(Cust, Product, QTY) VALUES('FRED','MILK',3) INSERT INTO Product(Cust, Product, QTY) VALUES('FRED','BEER',24) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','VEG',3) GO -- Selecting and checking entires in table Step3 SELECT * FROM Product GO -- Pivot Table ordered by PRODUCT Step4 select * FROM ( SELECT * FROM Product) up PIVOT (SUM(QTY) FOR CUST IN ([FRED], [KATE])) AS pvt ORDER BY PRODUCT GO --dynamic pivot???? Step5 DECLARE @cols NVARCHAR(2000) select @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT '],[' + b.Cust FROM (select top 100 Cust from tblProduct)b ORDER BY '],[' + b.Cust FOR XML PATH('') ), 1, 2, '') + ']' --Show Step6 SELECT * FROM (SELECT * FROM tblProduct) p PIVOT (SUM(QTY) FOR CUST IN (@cols)) as pvt Order by Product

    Read the article

  • Return a value if no rows are found SQL

    - by Matt
    Here's my simple query. If I query a record that doesn't exist then I will get nothing returned. I'd prefer that false (0) is returned in that scenario. Looking for the simplist method to account for no records. SELECT CASE WHEN S.Id IS NOT NULL AND S.Status = 1 AND (S.WebUserId = @WebUserId OR S.AllowUploads = 1) THEN 1 ELSE 0 END AS [Value] FROM Sites S WHERE S.Id = @SiteId

    Read the article

  • Merge two rows in SQL

    - by Jason
    Assuming I have a table containing the following information: FK | Field1 | Field2 ===================== 3 | ABC | *NULL* 3 | *NULL* | DEF is there a way I can perform a select on the table to get the following FK | Field1 | Field2 ===================== 3 | ABC | DEF Thanks Edit: Fix field2 name for clarity

    Read the article

  • Identity column SQL Server 2005 inserting same value twice

    - by DannykPowell
    I have a stored procedure that inserts into a table (where there is an identity column that is not the primary key- the PK is inserted initially using the date/time to generate a unique value). We then use SCOPEIDENTITY() to get the value inserted, then there is some logic to generate the primary key field value based on this value, which is then updated back to the table. In some situations the stored procedure is called simultaneously by more than one process, resulting in "Violation of PRIMARY KEY constraint..." errors. This would seem to indicate that the identity column is allowing the same number to be inserted for more than one record. First question- how is this possible? Second question- how to stop it...there's no error handling currently so I'm going to add some try/ catch logic- but would like to understand the problem fully to deal with properly

    Read the article

  • How can i solve "An explicit value for the identity column in table"?

    - by Phsika
    if i try to add some data into my table error occurs: Error:Msg 8101, Level 16, State 1, Line 1 An explicit value for the identity column in table 'ENG_PREP' can only be specified when a column list is used and IDENTITY_INSERT is ON. insert into ENG_PREP VALUES('572012-01-1,572012-01-2,572012-01-3,572013-01-1,572013-01-2', '', '500', '', 'A320 P.001-A', 'Removal of the LH Wing Safety Rope', '', '', '', '0', '', 'AF', '12-00-00-081-001', '', '', '', '', '', '', '' )

    Read the article

  • B-trees, databases, sequential inputs, and speed.

    - by IanC
    I know from experience that b-trees have awful performance when data is added to them sequentially (regardless of the direction). However, when data is added randomly, best performance is obtained. This is easy to demonstrate with the likes of an RB-Tree. Sequential writes cause a maximum number of tree balances to be performed. I know very few databases use binary trees, but rather used n-order balanced trees. I logically assume they suffer a similar fate to binary trees when it comes to sequential inputs. This sparked my curiosity. If this is so, then one could deduce that writing sequential IDs (such as in IDENTITY(1,1)) would cause multiple re-balances of the tree to occur. I have seen many posts argue against GUIDs as "these will cause random writes". I never use GUIDs, but it struck me that this "bad" point was in fact a good point. So I decided to test it. Here is my code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[T1]( [ID] [int] NOT NULL CONSTRAINT [T1_1] PRIMARY KEY CLUSTERED ([ID] ASC) ) GO CREATE TABLE [dbo].[T2]( [ID] [uniqueidentifier] NOT NULL CONSTRAINT [T2_1] PRIMARY KEY CLUSTERED ([ID] ASC) ) GO declare @i int, @t1 datetime, @t2 datetime, @t3 datetime, @c char(300) set @t1 = GETDATE() set @i = 1 while @i < 2000 begin insert into T2 values (NEWID(), @c) set @i = @i + 1 end set @t2 = GETDATE() WAITFOR delay '0:0:10' set @t3 = GETDATE() set @i = 1 while @i < 2000 begin insert into T1 values (@i, @c) set @i = @i + 1 end select DATEDIFF(ms, @t1, @t2) AS [Int], DATEDIFF(ms, @t3, getdate()) AS [GUID] drop table T1 drop table T2 Note that I am not subtracting any time for the creation of the GUID nor for the considerably extra size of the row. The results on my machine were as follows: Int: 17,340 ms GUID: 6,746 ms This means that in this test, random inserts of 16 bytes was almost 3 times faster than sequential inserts of 4 bytes. Would anyone like to comment on this? Ps. I get that this isn't a question. It's an invite to discussion, and that is relevant to learning optimum programming.

    Read the article

  • Linq to SQL gives NotSupportedException when using local variables

    - by zwanz0r
    It appears to me that it matters whether you use a variable to temporary store an IQueryable or not. See the simplified example below: This works: List<string> jobNames = new List<string> { "ICT" }; var ictPeops = from p in dataContext.Persons where ( from j in dataContext.Jobs where jobNames.Contains(j.Name) select j.ID).Contains(p.JobID) select p; But when I use a variable to temporary store the subquery I get an exception: List<string> jobNames = new List<string> { "ICT" }; var jobs = from j in dataContext.Jobs where jobNames.Contains(j.Name) select j.ID; var ictPeops = from p in dataContext.Persons where jobs.Contains(p.JobID) select p; "System.NotSupportedException: Queries with local collections are not supported" I don't see what the problem is. Isn't this logic that is supposed to work in LINQ?

    Read the article

  • Checking inherited attributes in an 'ancestry' based SQL table

    - by Brendon Muir
    I'm using the ancestry gem to help organise my app's tree structure in the database. It basically writes a childs ancestor information to a special column called 'ancestry'. The ancestry column for a particular child might look like '1/34/87' where the parent of this child is 87, and then 87's parent is 34 and 34's is 1. It seems possible that we could select rows from this table each with a subquery that checks all the ancestors to see if a certain attribute it set. E.g. in my app you can hide an item and its children just by setting the parent element's visibility column to 0. I want to be able to find all the items where none of their ancestors are hidden. I tried converting the slashes to comma's with the REPLACE command but IN required a set of comma separated integers rather than one string with comma separated string numbers. It's funny, because I can do this query in two steps, e.g. retrieve the row, then take its ancestry column, split out the id's and make another query that checks that the id is IN that set of id's and that visibility isn't ever 0 and whala! But joining these into one query seems to be quite a task. Much searching has shown a few answers but none really do what I want. SELECT * FROM t1 WHERE id = 99; 99's ancestry column reads '1/34/87' SELECT * FROM t1 WHERE visibility = 0 AND id IN (1,34,87); kind of backwards, but if this returns no rows then the item is visible. Has anyone come across this before and come up with a solution. I don't really want to go the stored procedure route. It's for a rails app.

    Read the article

  • SQL - How to join on similar (not exact) columns

    - by BlueRaja
    I have two tables which get updated at almost the exact same time - I need to join on the datetime column. I've tried this: SELECT * FROM A, B WHERE ABS(DATEDIFF(second, A.Date_Time, B.Date_Time) = ( SELECT MIN(ABS(DATEDIFF(second, A.Date_Time, B2.Date_Time))) FROM B AS B2 ) But it tells me: Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression. How can I join these tables?

    Read the article

  • SQL: many-to-many relationship, IN condition

    - by Maarten
    I have a table called transactions with a many-to-many relationship to items through the items_transactions table. I want to do something like this: SELECT "transactions".* FROM "transactions" INNER JOIN "items_transactions" ON "items_transactions".transaction_id = "transactions".id INNER JOIN "items" ON "items".id = "items_transactions".item_id WHERE (items.id IN (<list of items>)) But this gives me all transactions that have one or more of the items in the list associated with it and I only want it to give me the transactions that are associated with all of those items. Any help would be appreciated.

    Read the article

  • Sql query - selecting top 5 rows and further selecting rows only if User is present

    - by Gublooo
    Hello, I kind of stuck on how to implement this query - this is pretty similar to the query I posted earlier but I'm not able to crack it. I have a shopping table where everytime a user buys anything, a record is inserted. Some of the fields are * shopping_id (primary key) * store_id * user_id Now what I need is to pull only the list of those stores where he's among the top 5 visitors: When I break it down - this is what I want to accomplish: * Find all stores where this UserA has visited * For each of these stores - see who the top 5 visitors are. * Select the store only if UserA is among the top 5 visitors. The corresponding queries would be: select store_id from shopping where user_id = xxx select user_id,count(*) as 'visits' from shopping where store_id in (select store_id from shopping where user_id = xxx) group by user_id order by visits desc limit 5 Now I need to check in this resultset if UserA is present and select that store only if he's present. For example if he has visited a store 5 times - but if there are 5 or more people who have visited that store more than 5 times - then that store should not be selected. So I'm kind of lost here. Thanks for your help

    Read the article

  • Concerned with Top in sql

    - by ramyatk06
    hi guys, I have variable @count of datatype int.I am setting values to this @count. I want to select top @count number of rows from table.When i use Select top @count,its showing error. Delete from ItemDetails where GroupId in (Select Top @count Id from ItemDetails where GroupId=@Prm_GroupId ) The error is Incorrect syntax near '@count'.Can anybody help?

    Read the article

  • InvalidOperationException when executing SqlCommand with transaction

    - by Serhat Özgel
    I have this code, running parallel in two separate threads. It works fine for a few times, but at some random point it throws InvalidOperationException: The transaction is either not associated with the current connection or has been completed. At the point of exception, I am looking inside the transaction with visual studio and verify its connection is set normally. Also command.Transaction._internalTransaction. _transactionState is set to Active and IsZombied property is set to false. This is a test application and I am using Thread.Sleep for creating longer transactions and causing overlaps. Why may the exception being thrown and what can I do about it? IDbCommand command = new SqlCommand("Select * From INFO"); IDbConnection connection = new SqlConnection(connectionString); command.Connection = connection; IDbTransaction transaction = null; try { connection.Open(); transaction = connection.BeginTransaction(); command.Transaction = transaction; command.ExecuteNonQuery(); // Sometimes throws exception Thread.Sleep(forawhile); // For overlapping transactions running in parallel transaction.Commit(); } catch (ApplicationException exception) { if (transaction != null) { transaction.Rollback(); } } finally { connection.Close(); }

    Read the article

  • SQL to return dates that fall in period and range

    - by Nate
    Hey stackers, I’ve been grinding my head on this for a while… My goal is to return all dates that fall between a start and end date and have a certain period as a factor, from the start date. (hard to explain) For example… Start Date: Nov 20, 1987; End Date: Jan 01, 1988; Period: 10 days; I want these dates: Nov 20, 1987; Nov 30, 1987; Dec 10, 1987; Dec 20, 1987; Dec 30, 1987; I already have a date table with all dates from 1900 to 2099. The period could be by days, months or years. Any ideas? Let me know if you need more info.

    Read the article

  • VB working with SQL DB - end of row count, keeps looping

    - by Tramd
    I'm adding to a combo box an ID and a name that i'm pulling from a database. My problem is that for some reason my loop doesnt end once it reaches the end of the records in the database table. Here's my code: For intcount = 0 To dtOrders.Rows.Count - 1 cmbSearch.Items.Add(dtOrders.Rows(intcount)("EmployeeID").ToString & " " & dtOrders.Rows(intcount)("EmployeeLastName").ToString & ", " & dtOrders.Rows(intcount)("EmployeeFirstName").ToString) Next Shouldnt the .rows.count - 1 stop it once it reaches the last record? It loops 4 times through.

    Read the article

  • sql query problem

    - by benjamin button
    why this query give me an error:ORA-01790 SELECT TO_CHAR(logical_date,'MM') MONTH FROM logical_date WHERE logical_date_type='B' UNION SELECT TO_CHAR(logical_date,'MM')+1 MONTH FROM logical_date WHERE logical_date_type='B' but when i run them separately,they give the proper output.

    Read the article

  • Is READ UNCOMMITTED / NOLOCK safe in this situation?

    - by Ben Challenor
    I know that snapshot isolation would fix this problem, but I'm wondering if NOLOCK is safe in this specific case so that I can avoid the overhead. I have a table that looks something like this: drop table Data create table Data ( Id BIGINT NOT NULL, Date BIGINT NOT NULL, Value BIGINT, constraint Cx primary key (Date, Id) ) create nonclustered index Ix on Data (Id, Date) There are no updates to the table, ever. Deletes can occur but they should never contend with the SELECT because they affect the other, older end of the table. Inserts are regular and page splits to the (Id, Date) index are extremely common. I have a deadlock situation between a standard INSERT and a SELECT that looks like this: select top 1 Date, Value from Data where Id = @p0 order by Date desc because the INSERT acquires a lock on Cx (Date, Id; Value) and then Ix (Id, Date), but the SELECT acquires a lock on Ix (Id, Date) and then Cx (Date, Id; Value). This is because the SELECT first seeks on Ix and then joins to a seek on Cx. Swapping the clustered and non-clustered index would break this cycle, but it is not an acceptable solution because it would introduce cycles with other (more complex) SELECTs. If I add NOLOCK to the SELECT, can it go wrong in this case? Can it return: More than one row, even though I asked for TOP 1? No rows, even though one exists and has been committed? Worst of all, a row that doesn't satisfy the WHERE clause? I've done a lot of reading about this online, but the only reproductions of over- or under-count anomalies I've seen (one, two) involve a scan. This involves only seeks. Jeff Atwood has a post about using NOLOCK that generated a good discussion. I was particularly interested in a comment by Rick Townsend: Secondly, if you read dirty data, the risk you run is of reading the entirely wrong row. For example, if your select reads an index to find your row, then the update changes the location of the rows (e.g.: due to a page split or an update to the clustered index), when your select goes to read the actual data row, it's either no longer there, or a different row altogether! Is this possible with inserts only, and no updates? If so, then I guess even my seeks on an insert-only table could be dangerous. Update: I'm trying to figure out how snapshot isolation works. It seems to be row-based, where transactions read the table (with no shared lock!), find the row they are interested in, and then see if they need to get an old version of the row from the version store in tempdb. But in my case, no row will have more than one version, so the version store seems rather pointless. And if the row was found with no shared lock, how is it different to just using NOLOCK?

    Read the article

  • Getting SQL Syntax Error in INSERT INTO statement in Access 2010

    - by hello123
    I have written the following Insert Into statement in Access 2010 VBA: Private Sub AddBPSSButton_Click() ' CurrentDb.Execute "INSERT INTO TabClearDetail(C_Site) VALUES(" & Me.C_Site & ")" Dim strSQL As String 'MsgBox Me.[Clearance Applying For] 'MsgBox Me.[Contract Applying for] 'MsgBox Me.[C_Site] 'MsgBox Me.[C_SponsorSurname] 'MsgBox Me.[C_SponsorForename] 'MsgBox Me.[C_SponsorContactDetails] 'MsgBox Me.[C_EmploymentDetail] 'MsgBox Me.[C_SGNumber] 'MsgBox Me.[C_REF1DateRecd] 'MsgBox Me.[C_REF2DateRecd] 'MsgBox Me.[C_IDDateRecd] 'MsgBox Me.[C_IDNum] 'MsgBox Me.[C_CriminalDeclarationDate] 'MsgBox Me.[Credit Check Consent] 'MsgBox Me.[C_CreditCheckDate] 'MsgBox Me.[Referred for Management Decision] 'MsgBox Me.[Management Decision Date] 'MsgBox Me.[C_Comment] 'MsgBox Me.[C_DateCleared] 'MsgBox Me.[C_ClearanceLevel] 'MsgBox Me.[C_ContractAssigned] 'MsgBox Me.[C_ExpiryDate] 'MsgBox Me.[C_LinKRef] 'MsgBox Me.[C_OfficialSecretsDate] strSQL = "INSERT INTO TabClearDetail(Clearance Applying For, Contract Applying for, " & _ "C_Site, C_SponsorSurname, C_SponsorForename, C_SponsorContactDetails, C_EmploymentDetail, " & _ "C_SGNumber, C_REF1DateRecd, C_RED2DateRecd, C_IDDateRecd, C_IDNum, " & _ "C_CriminalDeclarationDate, Credit Check Consent, C_CreditCheckDate, Referred for Management Decision, " & _ "Management Decision Date, C_Comment, C_DateCleared, C_ClearanceLevel, C_ContractAssigned, " & _ "C_ExpiryDate, C_LinkRef, C_OfficialSecretsDate) VALUES('" & Me.[Clearance Applying For] & "', " & _ "'" & Me.[Contract Applying for] & "', '" & Me.[C_Site] & "', '" & Me.[C_SponsorSurname] & "', " & _ "'" & Me.[C_SponsorForename] & "', '" & Me.[C_SponsorContactDetails] & "', " & _ "'" & Me.[C_EmploymentDetail] & "', '" & Me.[C_SGNumber] & "', '" & Me.[C_REF1DateRecd] & "', " & _ "'" & Me.[C_REF2DateRecd] & "', '" & Me.[C_IDDateRecd] & "', '" & Me.[C_IDNum] & "', " & _ "'" & Me.[C_CriminalDeclarationDate] & "', '" & Me.[Credit Check Consent] & "', '" & Me.[C_CreditCheckDate] & "', " & _ "'" & Me.[Referred for Management Decision] & "', '" & Me.[Management Decision Date] & "', " & _ "'" & Me.[C_Comment] & "', '" & Me.[C_DateCleared] & "', '" & Me.[C_ClearanceLevel] & "', " & _ "'" & Me.[C_ContractAssigned] & "', '" & Me.[C_ExpiryDate] & "', '" & Me.[C_LinKRef] & "', " & _ "'" & Me.[C_OfficialSecretsDate] & "');" DoCmd.RunSQL (strSQL) 'MsgBox strSQL End Sub All The MsgBox calls work, so I believe I have typed all column names and text box names correctly. I am getting a Syntax error when I get to the DoCmd.RunSQL line. Have been staring at this for quite a while trying to see if I have missed a comma or speech mark or something, but am hoping maybe another set of eyes will see my mistake. Any help will be greatly appreciated. Thanks!

    Read the article

  • SQL - Finding continuous entries of a given size.

    - by ByteMR
    I am working on a system for reserving seats. A user inputs how many seats they wish to reserve and the database will return a set of suggested seats that are not previously reserved that matches the number of seats being reserved. For instance if I had the table: SeatID | Reserved ----------------- 1 | false 2 | true 3 | false 4 | false 5 | false 6 | true 7 | true 8 | false 9 | false 10 | true And the user inputs that they wish to reserve 2 seats, I would expect the query to return that seats (3, 4), (4, 5), and (8, 9) are not reserved and match the given number of input seats. Seats are organized into sections and rows. Continuous seats must be in the same row. How would I go about structuring this query to work in such a way that it finds all available continuous seats that match the given input?

    Read the article

  • Turn Function or Stored Procedure Result into "live" Result for LINQ

    - by Alex
    Is it possible to turn result sets obtained in LINQ through a stored procedure or function call into a "live" set of objects of which I can retrieve Foreign Key related objects? If, for example, my stored procedure returns a set of rows (= LINQ objects) of type "Contact", then I can't seem to obtain Contact.BillingAddress (which is related by Foreign Key). Any idea how to make this work?

    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 | 316 317 318 319 320 321 322 323 324 325 326 327  | Next Page >