Search Results

Search found 43145 results on 1726 pages for 'sql select'.

Page 115/1726 | < Previous Page | 111 112 113 114 115 116 117 118 119 120 121 122  | Next Page >

• SQL Server Express with Advanced Services (with Reporting Services)???

  - by Fretwizard

  I have tried to download SQL Server 2005 Express edition about 4 times trying to find the correct version that has business intelligence studio and reporting services in it? Every time I try to unhide the advanced configuration during install, it's never there... Can anyone point me to the correct download? Looking for 2005 (not 2008) because my work SQL server that I am trying to learn this for is 2005, and the training material I have is for 2005 and VS 2008 does not want to integrate with SQL2008 express.

  Read the article

• Alias a linked Server in SQL server management studio?

  - by absentmindeduk

  Hoping someone can help - is there a way in SQL server management studio 2008 R2 that I can alias a linked SQL server? I have a server, added by IP address, to which I do not have the login credentials - however as the connection is already setup I can login ok. Issue is that, this is a dev environment, prior to a live deployment and the IP I have as a linked server needs to be 'accessible' by my stored procs under a different name, eg 'myserver' not 192.168.xxx.xxx... Any help much appreciated.

  Read the article

• .Net Windows Service Throws EventType clr20r3 system.data.sqlclient.sql error

  - by William Edmondson

  I have a .Net/c# 2.0 windows service. The entry point is wrapped in a try catch block yet when I look at the server's application event log I seem a number of "EventType clr20r3" errors that are causing the service to die unexpectedly. The catch block has a "catch (Exception ex)". Each sql commands is of the type "CommandType.StoredProcedure" and are executed with SqlDataReader's. These sproc calls function correctly 99% of time and have all been thoroughly unit tested, profiled, and QA'd. I additionally wrapped these calls in try catch blocks just to be sure and am still experiencing these unhandled exceptions. This only in our production environment and cannot be duplicated in our dev or staging environments (even under heavy load). Why would my error handling not catch this particular error? Is there anyway to capture more detail as to the root cause of the problem? Here is an example of the event log: EventType clr20r3, P1 RDC.OrderProcessorService, P2 1.0.0.0, P3 4ae6a0d0, P4 system.data, P5 2.0.0.0, P6 4889deaf, P7 2490, P8 2c, P9 system.data.sqlclient.sql, P10 NIL. Additionally The Order Processor service terminated unexpectedly. It has done this 1 time(s). The following corrective action will be taken in 60000 milliseconds: Restart the service.

  Read the article

• LINQ To SQL ignore unique constraint exception and continue

  - by Martin

  I have a single table in a database called Users Users ------ ID (PK, Identity) Username (Unique Index) I have setup a unique index on the Username table to prevent duplicates. I am then enumerating through a collection and creating a new user in the database for each item. What I want to do is just insert a new user and ignore the exception if the unique key constraint is violated (as it's clearly a duplicate record in that case). This is to avoid having to craft where not exists kind of queries. First off, is this going to be any more efficient or should my insert code be checking for duplicates instead? I'm drawn more to the database having that logic as this prevents any other type of client from inserting duplicate data. My other issue is related to LINQ To SQL. I have the following code: public class TestRepo { DatabaseDataContext database = new DatabaseDataContext(); public void Add(string username) { database.Users.InsertOnSubmit(new User() { Username = username }); } public void Save() { database.SubmitChanges(); } } And then I iterate over a collection and insert new users, ignoring any exceptions: TestRepo repo = new TestRepo(); foreach (var name in new string[] { "Tim", "Bob", "John" }) { try { repo.Add(name); repo.Save(); } catch { } } The first time this is run, great I have three users in the table. If I remove the second one and run this code again, nothing is inserted. I expected the first insert to fail with the exception, the second to succeed (as I just removed that item from the DB) and the third to then fail. What seems to be happening is that once the SqlException is thrown (even though the loop continues to iterate) all of the next inserts fail - even when there isn't a row in the table that would cause a unique violation. Can anyone explain this? P.S. The only workaround I could find was to instantiate the repo each time before the insert, then it worked exactly as excepted - indicating that it's something to do with the LINQ To SQL DataContext. Thanks.

  Read the article

• Complex SQL Query similar to a z order problem

  - by AaronLS

  I have a complex SQL problem in MS SQL Server, and in drawing on a piece of paper I realized that I could think of it as a single bar filled with rectangles, each rectangle having segments with different Z orders. In reality it has nothing to do with z order or graphics at all, but more to do with some complex business rules that would be difficult to explain. Howoever, if anyone has ideas on how to solve the below that will give me my solution. I have the following data: ObjectID, PercentOfBar, ZOrder (where smaller is closer) A, 100, 6 B, 50, 5 B, 50, 4 C, 30, 3 C, 70, 6 The result of my query that I want is this, in any order: PercentOfBar, ZOrder 50, 5 20, 4 30, 3 Think of it like this, if I drew rectangle A, it would fill 100% of the bar and have a z order of 6. 66666666666 AAAAAAAAAAA If I then layed out rectangle B, consisting of two segments, both segments would cover up rectangle A resulting in the following rendering: 4444455555 BBBBBBBBBB As a rule of thumb, for a given rectangle, it's segments should be layed out such that the highest z order is to the right of the lower Z orders. Finally rectangle C would cover up only portions of Rectangle B with it's 30% segment that is z order 3, which would be on the left. You can hopefully see how the is represented in the output dataset I listed above: 3334455555 CCCBBBBBBB Now to make things more complicated I actually have a 4th column such that this grouping occurs for each key: Input: SomeKey, ObjectID, PercentOfBar, ZOrder (where smaller is closer) X, A, 100, 6 X, B, 50, 5 X, B, 50, 4 X, C, 30, 3 X, C, 70, 6 Y, A, 100, 6 Z, B, 50, 2 Z, B, 50, 6 Z, C, 100, 5 Output: SomeKey, PercentOfBar, ZOrder X, 50, 5 X, 20, 4 X, 30, 3 Y, 100, 6 Z, 50, 2 Z, 50, 5 Notice in the output, the PercentOfBar for each SomeKey would add up to 100%. This is one I know I'm going to be thinking about when I go to bed tonight. Just to be explicit and have a question: What would be a query that would produce the results described above?

  Read the article

• Fastest way to remove non-numeric characters from a VARCHAR in SQL Server

  - by Dan Herbert

  I'm writing an import utility that is using phone numbers as a unique key within the import. I need to check that the phone number does not already exist in my DB. The problem is that phone numbers in the DB could have things like dashes and parenthesis and possibly other things. I wrote a function to remove these things, the problem is that it is slow and with thousands of records in my DB and thousands of records to import at once, this process can be unacceptably slow. I've already made the phone number column an index. I tried using the script from this post: http://stackoverflow.com/questions/52315/t-sql-trim-nbsp-and-other-non-alphanumeric-characters But that didn't speed it up any. Is there a faster way to remove non-numeric characters? Something that can perform well when 10,000 to 100,000 records have to be compared. Whatever is done needs to perform fast. Update Given what people responded with, I think I'm going to have to clean the fields before I run the import utility. To answer the question of what I'm writing the import utility in, it is a C# app. I'm comparing BIGINT to BIGINT now, with no need to alter DB data and I'm still taking a performance hit with a very small set of data (about 2000 records). Could comparing BIGINT to BIGINT be slowing things down? I've optimized the code side of my app as much as I can (removed regexes, removed unneccessary DB calls). Although I can't isolate SQL as the source of the problem anymore, I still feel like it is.

  Read the article

• Finding duplicate rows in SQL Server

  - by xtine

  I have a SQL Server database of organizations, and there are many duplicate rows. I want to run a select statement to grab all of these and the amount of dupes, but also return the ids that are associated with each organization. A statement like: SELECT orgName, COUNT(*) AS dupes FROM organizations GROUP BY orgName HAVING (COUNT(*) > 1) Will return something like orgName | dupes ABC Corp | 7 Foo Federation | 5 Widget Company | 2 But I'd also like to grab the IDs of them. Is there any way to do this? Maybe like a orgName | dupeCount | id ABC Corp | 1 | 34 ABC Corp | 2 | 5 ... Widget Company | 1 | 10 Widget Company | 2 | 2 The reason being that there is also a separate table of users that link to these organizations, and I would like to unify them (therefore remove dupes so the users link to the same organization instead of dupe orgs). But I would like part manually so I don't screw anything up, but I would still need a statement returning the IDs of all the dupe orgs so I can go through the list of users. Any help would be greatly appreciated. Thanks :)

  Read the article

• Concatenate row values T-SQL

  - by Robert

  I am trying to pull together some data for a report and need to concatenate the row values of one of the tables. Here is the basic table structure: Reviews ReviewID ReviewDate Reviewers ReviewerID ReviewID UserID Users UserID FName LName This is a M:M relationship. Each Review can have many Reviewers; each User can be associated with many Reviews. Basically, all I want to see is Reviews.ReviewID, Reviews.ReviewDate, and a concatenated string of the FName's of all the associated Users for that Review (comma delimited). Instead of: ReviewID---ReviewDate---User 1----------12/1/2009----Bob 1----------12/1/2009----Joe 1----------12/1/2009----Frank 2----------12/9/2009----Sue 2----------12/9/2009----Alice Display this: ReviewID---ReviewDate----Users 1----------12/1/2009-----Bob, Joe, Frank 2----------12/9/2009-----Sue, Alice I have found this article describing some ways to do this, but most of these seem to only deal with one table, not multiple; unfortunately, my SQL-fu is not strong enough to adapt these to my circumstances. I am particularly interested in the example on that site which utilizes FOR XML PATH() as that looks the cleanest and most straight forward. SELECT p1.CategoryId, ( SELECT ProductName + ', ' FROM Northwind.dbo.Products p2 WHERE p2.CategoryId = p1.CategoryId ORDER BY ProductName FOR XML PATH('') ) AS Products FROM Northwind.dbo.Products p1 GROUP BY CategoryId; Can anyone give me a hand with this? Any help would be greatly appreciated!

  Read the article

• SQL Syntax to count unique users completing a task

  - by Belliez

  I have the following code which shows me what users has completed ticket and this lists each user and the date they close a ticket. i.e. Paul Matt Matt Bob Matt Paul Matt Matt At the moment I manually count each user myself to see their totals for the day. EDIT: Changed output as columns instead of rows: What I have been trying to do is get SQL Server to do this for me i.e. the final result to look like: Paul | 2 Matt | 5 Bob | 1 My code I am currently using is and I would be greatful if someone can help me change this so I can get it outputting something similar to above? DECLARE @StartDate DateTime; DECLARE @EndDate DateTime; -- Date format: YYYY-MM-DD SET @StartDate = '2013-11-06 00:00:00' SET @EndDate = GETDATE() -- Today SELECT (select Username from Membership where UserId = Ticket.CompletedBy) as TicketStatusChangedBy FROM Ticket INNER JOIN TicketStatus ON Ticket.TicketStatusID = TicketStatus.TicketStatusID INNER JOIN Membership ON Ticket.CheckedInBy = Membership.UserId WHERE TicketStatus.TicketStatusName = 'Completed' and Ticket.ClosedDate >= @StartDate --(GETDATE() - 1) and Ticket.ClosedDate <= @EndDate --(GETDATE()-0) ORDER BY Ticket.CompletedBy ASC, Ticket.ClosedDate ASC Thank you for your help and time.

  Read the article

• T-SQL While Loop and concatenation

  - by JustinT

  I have a SQL query that is supposed to pull out a record and concat each to a string, then output that string. The important part of the query is below. DECLARE @counter int; SET @counter = 1; DECLARE @tempID varchar(50); SET @tempID = ''; DECLARE @tempCat varchar(255); SET @tempCat = ''; DECLARE @tempCatString varchar(5000); SET @tempCatString = ''; WHILE @counter <= @tempCount BEGIN SET @tempID = ( SELECT [Val] FROM #vals WHERE [ID] = @counter); SET @tempCat = (SELECT [Description] FROM [Categories] WHERE [ID] = @tempID); print @tempCat; SET @tempCatString = @tempCatString + '<br/>' + @tempCat; SET @counter = @counter + 1; END When the script runs, @tempCatString outputs as null while @tempCat always outputs correctly. Is there some reason that concatenation won't work inside a While loop? That seems wrong, since incrementing @counter works perfectly. So is there something else I'm missing?

  Read the article

• How to Convert using of SqlLit to Simple SQL command in C#

  - by Nasser Hajloo

  I want to get start with DayPilot control I do not use SQLLite and this control documented based on SQLLite. I want to use SQL instead of SQL Lite so if you can, please do this for me. main site with samples http://www.daypilot.org/calendar-tutorial.html The database contains a single table with the following structure CREATE TABLE event ( id VARCHAR(50), name VARCHAR(50), eventstart DATETIME, eventend DATETIME); Loading Events private DataTable dbGetEvents(DateTime start, int days) { SQLiteDataAdapter da = new SQLiteDataAdapter("SELECT [id], [name], [eventstart], [eventend] FROM [event] WHERE NOT (([eventend] <= @start) OR ([eventstart] >= @end))", ConfigurationManager.ConnectionStrings["db"].ConnectionString); da.SelectCommand.Parameters.AddWithValue("start", start); da.SelectCommand.Parameters.AddWithValue("end", start.AddDays(days)); DataTable dt = new DataTable(); da.Fill(dt); return dt; } Update private void dbUpdateEvent(string id, DateTime start, DateTime end) { using (SQLiteConnection con = new SQLiteConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString)) { con.Open(); SQLiteCommand cmd = new SQLiteCommand("UPDATE [event] SET [eventstart] = @start, [eventend] = @end WHERE [id] = @id", con); cmd.Parameters.AddWithValue("id", id); cmd.Parameters.AddWithValue("start", start); cmd.Parameters.AddWithValue("end", end); cmd.ExecuteNonQuery(); } }

  Read the article

• What does SQL Server's BACKUPIO wait type mean?

  - by solublefish

  I'm using Sql Server 2008 ("R1"), with some maintenance plans that back up my databases to a network share. Some of my backup jobs show long waits of type "BACKUPIO". Of course it seems like this is an I/O subsystem limitation, but I'm skeptical. Perfmon stats for I/O on the production (source) server are well within normal trends for that server. The destination server shows a sustained 7MB/s write rate, which seems incredibly low, even for a slow disk. The network link is gigabit ethernet and nowhere near saturated. The few docs I've turned up about BACKUPIO indicate that it's not specifically a wait on I/O, surprisingly enough. This MSFT doc says it's abnormal unless you're using a tape drive, which I'm not. But it doesn't say (or I don't understand) exactly what resource is missing. http://www.docstoc.com/docs/24580659/Performance-Tuning-in-SQL-Server-2005 And this piece says it's not related to I/O performance at all. http://www.informit.com/articles/article.aspx?p=686168&seqNum=5 "Note that BACKUPIO and IO_AUDIT_MUTEX are not related to IO performance." Anyway, does anyone know what BACKUPIO actually means and/or what I can do to diagnose or eliminate it?

  Read the article

• SQL Server 2005 Reporting Services and the Report Viewer

  - by Kendra

  I am having an issue embedding my report into an aspx page. Here's my setup: 1 Server running SQL Server 2005 and SQL Server 2005 Reporting Services 1 Workstation running XP and VS 2005 The server is not on a domain. Reporting Services is a default installation. I have one report called TestMe in a folder called TestReports using a shared datasource. If I view the report in Report Manager, it renders fine. If I view the report using the http ://myserver/reportserver url it renders fine. If I view the report using the http ://myserver/reportserver?/TestReports/TestMe it renders fine. If I try to view the report using http ://myserver/reportserver/TestReports/TestMe, it just goes to the folder navigation page of the home directory. My web application is impersonating somebody specific to get around the server not being on a domain. When I call the report from the report viewer using http ://myserver/reportserver as the server and /TestReports/TestMe as the path I get this error: For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method. When I change the server to http ://myserver/reportserver? I get this error when I run the report: Client found response content type of '', but expected 'text/xml'. The request failed with an empty response. I have been searching for a while and haven't found anything that fixes my issue. Please let me know if there is more information needed. Thanks in advance, Kendra

  Read the article

• SQL Server 2005 standard filegroups / files for performance on SAN

  - by Blootac

  Ok so I've just been on a SQL Server course and we discussed the usage scenarios of multiple filegroups and files when in use over local RAID and local disks but we didn't touch SAN scenarios so my question is as follows; I currently have a 250 gig database running on SQL Server 2005 where some tables have a huge number of writes and others are fairly static. The database and all objects reside in a single file group with a single data file. The log file is also on the same volume. My interpretation is that separate data files should be used across different disks to lessen disk contention and that file groups should be used for partitioning of data. However, with a SAN you obviously don't really have the same issue of disk contention that you do with a small RAID setup (or at least we don't at the moment), and standard edition doesn't support partitioning. So in order to improve parallelism what should I do? My understanding of various Microsoft publications is that if I increase the number of data files, separate threads can act across each file separately. Which leads me to the question how many files should I have. One per core? Should I be putting tables and indexes with high levels of activity in separate file groups, each with the same number of data files as we have cores? Thank you

  Read the article

• SQL Server: A Grouping question that's annoying me

  - by user366729

  I've been working with SQL Server for the better part of a decade, and this grouping (or partitioning, or ranking...I'm not sure what the answer is!) one has me stumped. Feels like it should be an easy one, too. I'll generalize my problem: Let's say I have 3 employees (don't worry about them quitting or anything...there's always 3), and I keep up with how I distribute their salaries on a monthly basis. Month Employee PercentOfTotal -------------------------------- 1 Alice 25% 1 Barbara 65% 1 Claire 10% 2 Alice 25% 2 Barbara 50% 2 Claire 25% 3 Alice 25% 3 Barbara 65% 3 Claire 10% As you can see, I've paid them the same percent in Months 1 and 3, but in Month 2, I've given Alice the same 25%, but Barbara got 50% and Claire got 25%. What I want to know is all the distinct distributions I've ever given. In this case there would be two -- one for months 1 and 3, and one for month 2. I'd expect the results to look something like this (NOTE: the ID, or sequencer, or whatever, doesn't matter) ID Employee PercentOfTotal -------------------------------- X Alice 25% X Barbara 65% X Claire 10% Y Alice 25% Y Barbara 50% Y Claire 25% Seems easy, right? I'm stumped! Anyone have an elegant solution? I just put together this solution while writing this question, which seems to work, but I'm wondering if there's a better way. Or maybe a different way from which I'll learn something. WITH temp_ids (Month) AS ( SELECT DISTINCT MIN(Month) FROM employees_paid GROUP BY PercentOfTotal ) SELECT EMP.Month, EMP.Employee, EMP.PercentOfTotal FROM employees_paid EMP JOIN temp_ids IDS ON EMP.Month = IDS.Month GROUP BY EMP.Month, EMP.Employee, EMP.PercentOfTotal Thanks y'all! -Ricky

  Read the article

• SQL Server Clustered Index: (Physical) Data Page Order

  - by scherand

  I am struggling understanding what a clustered index in SQL Server 2005 is. I read the MSDN article Clustered Index Structures (among other things) but I am still unsure if I understand it correctly. The (main) question is: what happens if I insert a row (with a "low" key) into a table with a clustered index? The above mentioned MSDN article states: The pages in the data chain and the rows in them are ordered on the value of the clustered index key. And Using Clustered Indexes for example states: For example, if a record is added to the table that is close to the beginning of the sequentially ordered list, any records in the table after that record will need to shift to allow the record to be inserted. Does this mean that if I insert a row with a very "low" key into a table that already contains a gazillion rows literally all rows are physically shifted on disk? I cannot believe that. This would take ages, no? Or is it rather (as I suspect) that there are two scenarios depending on how "full" the first data page is. A) If the page has enough free space to accommodate the record it is placed into the existing data page and data might be (physically) reordered within that page. B) If the page does not have enough free space for the record a new data page would be created (anywhere on the disk!) and "linked" to the front of the leaf level of the B-Tree? This would then mean the "physical order" of the data is restricted to the "page level" (i.e. within a data page) but not to the pages residing on consecutive blocks on the physical hard drive. The data pages are then just linked together in the correct order. Or formulated in an alternative way: if SQL Server needs to read the first N rows of a table that has a clustered index it can read data pages sequentially (following the links) but these pages are not (necessarily) block wise in sequence on disk (so the disk head has to move "randomly"). How close am I? :)

  Read the article

• sp_addlinkedserver on sql server 2005 giving problem

  - by Jit

  I am trying to create a link server of a remote database(both the servers are SQL serve2005). I am able to connect that remote server from my SQL Server management studio. I used the following syntax to create it. EXEC sp_addlinkedserver @server = N'LINKSQL2005', @srvproduct = N'', @provider = N'SQLNCLI', @provstr = N'SERVER=IP Address of remote server ;User ID=XXXXXX;Password=***' I have provided the IP addressntax. and user name and password in the above syntax. The link server is getting created. But when I try to execute a query on it I get the error below. Query Used. select * from LINKSQL2005.<DBName>.dbo.<TableName> OLE DB provider "SQLNCLI" for linked server "LINKSQL2005" returned message "Communication link failure". Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host. Msg 18456, Level 14, State 1, Line 0 Login failed for user 'sa'. OLE DB provider "SQLNCLI" for linked server "LINKSQL2005" returned message "Invalid connection string attribute". Pls help me, where am I making mistake.

  Read the article

• How can I stop SQL Server Management Studio replacing 'SELECT *' with the column list ?

  - by Ben McIntyre

  SQL Server Mgmt Studio is driving me crazy. If I create a view and SELECT '*' from a table, it's all OK and I can save the view. Looking at the SQL for the view (eg.by scripting a CREATE) reveals that the 'SELECT *' really is saved to the view's SQL. But as soon as I reopen the view using the GUI (right click modify), SELECT * is replaced with a column list of all the columns in the table. How can I stop Management Studio from doing this ? I want my 'SELECT *' to remain just that. Perhaps it's just the difficulty of googling 'SELECT *' that prevented me from finding anything remotely relevant to this (i did put it in double quotes). Please, I am highly experienced in Transact-SQL, so please DON'T give me a lecture on why I shouldn't be using SELECT *. I know all the pros and cons and I do use it at times. It's a language feature, and like all language features can be used for good or evil (I emphatically do NOT agree that it is never appropriate to use it). Edit: I'm giving Marc the answer, since it seems it is not possible to turn this behaviour off. Problem is considered closed. I note that Enterprise Manager did no similar thing. The workaround is to either edit SQL as text, or go to a product other than Managment Studio. Or constantly edit out the column list and replace the * every time you edit a view. Sigh.

  Read the article

• Repeatedly execute a stored procedure

  - by manivineet

  I have a situation where I need to repeatedly execute a stored procedure Now this procedure (spMAIN) has a cursor inside which looks for a value from a table as T1,which has the following structure ID Status ---- -------- 1 New 2 New 3 success 4 Error now the cursor looks for all rows with a status of 'New' Now while processing , if that instance of the cursor encounters an error, another SP say spError needs to be called, the 'Status' column in T1 needs to be updated to 'Error' and spMAIN needs to be called again which again repeats the process, looking for rows with 'new' how do I do it? Also, also, while we are at it, what if an SP has other SPs inside it and if any of those SP raises an error, same thing needs to be done, the T1 table needs to be updated ('Error') and spMAIN needs to be called again. can you also recommend something ? here's some code ALTER PROC zzSpMain AS BEGIN DECLARE @id INT BEGIN TRY IF EXISTS ( SELECT * FROM dbo.zzTest WHERE istatus = 'new' ) BEGIN DECLARE c CURSOR FOR SELECT id FROM zztest WHERE istatus = 'new' OPEN c FETCH NEXT FROM c INTO @id WHILE @@FETCH_STATUS = 0 BEGIN PRINT @id IF @id = 2 BEGIN UPDATE zztest SET istatus = 'error' WHERE id = @id RAISERROR ( 'Error occured', 16, 1 ) END UPDATE zztest SET istatus = 'processed' WHERE id = @id FETCH NEXT FROM c INTO @id END CLOSE c DEALLOCATE c END END TRY begin CATCH EXEC zzSpError END CATCH END

  Read the article

• SQL Query with multiple values in one column

  - by lp1

  I've been beating my head on the desk tring to figure this one out. I have a table that stores job information, and reasons for a job not being completed. The reasons are numeric,01,02,03,etc. You can have two reason for a pending job. If you select two reasons, they are stored in the same column, seperated by a comma. This is anExample from the JOBID table: Job_Number User_Assigned PendingInfo 1 user1 01,02 Now, there is another table named Pending, that stores what those values actually represent. 01=Not enough info, 02=Not enough time, 03=Waiting Review. Example: Pending_Num PendingWord 01 Not Enough Info 02 Not Enough Time What I'm trying to do is query the database to give me all the job numbers, users, pendinginfo, and pending reason. I can break out the first value, but can't figure out how to do the second. What my limited skills have so far: *select Job_number,user_assigned,SUBSTRING(pendinginfo,0,3),pendingword from jobid,pending where SUBSTRING(pendinginfo,0,3)=pending.pending_num and pendinginfo!='00,00' and pendinginfo!='NULL'* What I would like to see for this example would be: Job_Number User_Assigned PendingInfo PendingWord PendingInfo PendingWord 1 User1 01 Not Enough Info 02 Not Enough Time Thanks in advance

  Read the article

• Need to combine common result of two select statements

  - by Anup

  i have to select the common coloumns c1 ,c2,c3 from result of two sql satements. 1) select c1, c2, c3,count(c3) from (select * from form_name where data_created >'1273446000' and data_creazione<'1274569200') group by c1,c2, c3 having count(c3)>1 2) select c1, c2, c3,count(c3) from (select * from form_name where data_created>'1272236400' and data_creazione<'1274569200') group by c1,c2, c3 having count(c3)>2 I need to select c1,c2,c3 all same and common found in both the result of query. how could this be done...could anyone help please?

  Read the article

• General SQL Server query performance

  - by Kiril

  Hey guys, This might be stupid, but databases are not my thing :) Imagine the following scenario. A user can create a post and other users can reply to his post, thus forming a thread. Everything goes in a single table called Posts. All the posts that form a thread are connected with each other through a generated key called ThreadID. This means that when user #1 creates a new post, a ThreadID is generated, and every reply that follows has a ThreadID pointing to the initial post (created by user #1). What I am trying to do is limit the number of replies to let's say 20 per thread. I'm wondering which of the approaches bellow is faster: 1 I add a new integer column (e.x. Counter) to Posts. After a user replies to the initial post, I update the initial post's Counter field. If it reaches 20 I lock the thread. 2 After a user replies to the initial post, I select all the posts that have the same ThreadID. If this collection has more than 20 items, I lock the thread. For further information: I am using SQL Server database and Linq-to-SQL entity model. I'd be glad if you tell me your opinions on the two approaches or share another, faster approach. Best Regards, Kiril

  Read the article

• Best way to randomly select columns from random rows of SQL results.

  - by LesterDove

  A search of SO yields many results describing how to select random rows of data from a database table. My requirement is a bit different, though, in that I'd like to select individual columns from across random rows in the most efficient/random/interesting way possible. To better illustrate: I have a large Customers table, and from that I'd like to generate a bunch of fictitious demo Customer records that aren't real people. I'm thinking of just querying randomly from the Customers table, and then randomly pairing FirstNames with LastNames, Address, City, State, etc. So if this is my real Customer data (simplified): FirstName LastName State ========================== Sally Simpson SD Will Warren WI Mike Malone MN Kelly Kline KS Then I'd generate several records that look like this: FirstName LastName State ========================== Sally Warren MN Kelly Malone SD Etc. My initial approach works, but it lacks the elegance that I'm hoping the final answer will provide. (I'm particularly unhappy with the repetitiveness of the subqueries, and the fact that this solution requires a known/fixed number of fields and therefore isn't reusable.) SELECT FirstName = (SELECT TOP 1 FirstName FROM Customer ORDER BY newid()), LastName= (SELECT TOP 1 LastNameFROM Customer ORDER BY newid()), State = (SELECT TOP 1 State FROM Customer ORDER BY newid()) Thanks!

  Read the article

• SQL Server database change workflow best practices

  - by kubi

  The Background My group has 4 SQL Server Databases: Production UAT Test Dev I work in the Dev environment. When the time comes to promote the objects I've been working on (tables, views, functions, stored procs) I make a request of my manager, who promotes to Test. After testing, she submits a request to an Admin who promotes to UAT. After successful user testing, the same Admin promotes to Production. The Problem The entire process is awkward for a few reasons. Each person must manually track their changes. If I update, add, remove any objects I need to track them so that my promotion request contains everything I've done. In theory, if I miss something testing or UAT should catch it, but this isn't certain and it's a waste of the tester's time, anyway. Lots of changes I make are iterative and done in a GUI, which means there's no record of what changes I made, only the end result (at least as far as I know). We're in the fairly early stages of building out a data mart, so the majority of the changes made, at least count-wise, are minor things: changing the data type for a column, altering the names of tables as we crystallize what they'll be used for, tweaking functions and stored procs, etc. The Question People have been doing this kind of work for decades, so I imagine there have got to be a much better way to manage the process. What I would love is if I could run a diff between two databases to see how the structure was different, use that diff to generate a change script, use that change script as my promotion request. Is this possible? If not, are there any other ways to organize this process? For the record, we're a 100% Microsoft shop, just now updating everything to SQL Server 2008, so any tools available in that package would be fair game.

  Read the article

• TSQL Help (SQL Server 2005)

  - by Mick Walker

  I have been playing around with a quite complex SQL Statement for a few days, and have gotten most of it working correctly. I am having trouble with one last part, and was wondering if anyone could shed some light on the issue, as I have no idea why it isnt working: INSERT INTO ExistingClientsAccounts_IMPORT SELECT DISTINCT cca.AccountID, cca.SKBranch, cca.SKAccount, cca.SKName, cca.SKBase, cca.SyncStatus, cca.SKCCY, cca.ClientType, cca.GFCID, cca.GFPID, cca.SyncInput, cca.SyncUpdate, cca.LastUpdatedBy, cca.Deleted, cca.Branch_Account, cca.AccountTypeID FROM ClientsAccounts AS cca INNER JOIN (SELECT DISTINCT ClientAccount, SKAccount, SKDesc, SKBase, SKBranch, ClientType, SKStatus, GFCID, GFPID, Account_Open_Date, Account_Update FROM ClientsAccounts_IMPORT) AS ccai ON cca.Branch_Account = ccai.ClientAccount Table definitions follow: CREATE TABLE [dbo].[ExistingClientsAccounts_IMPORT]( [AccountID] [int] NOT NULL, [SKBranch] [varchar](2) NOT NULL, [SKAccount] [varchar](12) NOT NULL, [SKName] [varchar](255) NULL, [SKBase] [varchar](16) NULL, [SyncStatus] [varchar](50) NULL, [SKCCY] [varchar](5) NULL, [ClientType] [varchar](50) NULL, [GFCID] [varchar](10) NULL, [GFPID] [varchar](10) NULL, [SyncInput] [smalldatetime] NULL, [SyncUpdate] [smalldatetime] NULL, [LastUpdatedBy] [varchar](50) NOT NULL, [Deleted] [tinyint] NOT NULL, [Branch_Account] [varchar](16) NOT NULL, [AccountTypeID] [int] NOT NULL ) ON [PRIMARY] CREATE TABLE [dbo].[ClientsAccounts_IMPORT]( [NEWClientIndex] [bigint] NOT NULL, [ClientGroup] [varchar](255) NOT NULL, [ClientAccount] [varchar](255) NOT NULL, [SKAccount] [varchar](255) NOT NULL, [SKDesc] [varchar](255) NOT NULL, [SKBase] [varchar](10) NULL, [SKBranch] [varchar](2) NOT NULL, [ClientType] [varchar](255) NOT NULL, [SKStatus] [varchar](255) NOT NULL, [GFCID] [varchar](255) NULL, [GFPID] [varchar](255) NULL, [Account_Open_Date] [smalldatetime] NULL, [Account_Update] [smalldatetime] NULL, [SKType] [varchar](255) NOT NULL ) ON [PRIMARY] The error message I get is: Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated.

  Read the article

< Previous Page | 111 112 113 114 115 116 117 118 119 120 121 122  | Next Page >