Search Results

Search found 169694 results on 6788 pages for 'sql server user group'.

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 195/6788 | < Previous Page | 191 192 193 194 195 196 197 198 199 200 201 202  | Next Page >

  • mysql query: SELECT DISTINCT column1, GROUP BY column2

    - by Adam
    Right now I have the following query: SELECT name, COUNT(name), time, price, ip, SUM(price) FROM tablename WHERE time >= $yesterday AND time <$today GROUP BY name And what I'd like to do is add a DISTINCT by column 'ip', i.e. SELECT DISTINCT ip FROM tablename So my final output would be all the columns, from all the rows that where time is today, grouped by name (with name count for each repeating name) and no duplicate ip addresses. What should my query look like? (or alternatively, how can I add the missing filter to the output with php)? Thanks in advance.

    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 server procedure error

    - by Mohan
    CREATE PROCEDURE USP_SEARCH_HOTELS ( @Text varchar(50), @Type varchar(40) ) AS BEGIN Declare @Query VARCHAR(60) IF @Type = 'By Country' BEGIN SET @Query = 'Hotel.countryName like '+ @Text+'%' END ELSE IF @Type = 'By State' BEGIN SET @Query = 'HOTEL.stateName like '+ @Text+'%' END ELSE IF @Type='By Property Name' BEGIN SET @Query='hotel.propertyname like'+ @Text+'%' End ELSE IF @Type='By Rating' BEGIN SET @Query='hotel.starRating='+ Cast(@Text as INT) END ELSE IF @Type='By City' BEGIN SET @Query='hotel.cityName like '+ @Text+'%' END begin select * from hotel,tbl_cust_info where hotel.agentID=Tbl_Cust_Info.Cust_ID and (@Query) end END WHAT IS THE ERROR IN THIS PROCEDURE PLEASE HELP.

    Read the article

  • Optimizing Disk I/O & RAID on Windows SQL Server 2005

    - by David
    I've been monitoring our SQL server for a while, and have noticed that I/O hits 100% every so often using Task Manager and Perfmon. I have normally been able to correlate this spike with SUSPENDED processes in SQL Server Management when I execute "exec sp_who2". The RAID controller is controlled by LSI MegaRAID Storage Manager. We have the following setup: System Drive (Windows) on RAID 1 with two 280GB drives SQL is on a RAID 10 (2 mirroed drives of 280GB in two different spans) This is a database that is hammered during the day, but is pretty inactive at night. The DB size is currently about 13GB, and is used by approximately 200 (and growing) users a day. I have a couple of ideas I'm toying around with: Checking for Indexes & reindexing some tables Adding an additional RAID 1 (with 2 new, smaller, HDs) and moving the SQL's Log Data File (LDF) onto the new RAID. For #2, my question is this: Would we really be increasing disk performance (IO) by moving data off of the RAID 10 onto a RAID 1? RAID 10 obviously has better performance than RAID 1. Furthermore, SQL must write to the transaction logs before writing to the database. But on the flip side, we'll be reducing both the size of the disks as well as the amount of data written to the RAID 10, which is where all of the "meat" is - thereby increasing that RAID's performance for read requests. Is there any way to find out what our current limiting factor is? (The drives vs. the RAID Controller)? If the limiting factor is the drives, then maybe adding the additional RAID 1 makes sense. But if the limiting factor is the Controller itself, then I think we're approaching this thing wrong. Finally, are we just wasting our time? Should we instead be focusing our efforts towards #1 (reindexing tables, reducing network latency where possible, etc...)?

    Read the article

  • cannot connect to <server_name>\sqlexpress

    - by Jackson Sunuwar
    I have tried disabling firing wall and checked sqlbrowser is started but for some reason I cannnot connect to my datbase... called server_name\sqlexpress.. I have a virtual machine and a full scale MS SQL Server 2008 R2 running on it... and I have several other vm running sqlexpress. they run fine and I can connect to them using sqlexpress... but when i try to access from sqlserver... I get this error. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1) Digging deep into the error, I found this Error Number: -1 Severity: 20 State: 0 and finally this... Program Location: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser() Firewall is turned off on the VM that's running mssqlserver... I turned of firewall on one of the vm that's running the sqlexpress but I still get the error... can someone please help... thank you

    Read the article

  • SQL Overlapping and Multi-Column Indexes

    - by durilai
    I am attempting to tune some stored procedures and have a question on indexes. I have used the tuning advisor and they recommended two indexes, both for the same table. The issue is one index is for one column and the other is for multiple columns, of which it includes the same column from the first. My question is why and what is the difference? CREATE NONCLUSTERED INDEX [_dta_index_Table1_5_2079723603__K23_K17_K13_K12_K2_K10_K22_K14_K19_K20_K9_K11_5_6_7_15_18] ON [dbo].[Table1] ( [EfctvEndDate] ASC, [StuLangCodeKey] ASC, [StuBirCntryCodeKey] ASC, [StuBirStOrProvncCodeKey] ASC, [StuKey] ASC, [GndrCodeKey] ASC, [EfctvStartDate] ASC, [StuHspncEnctyIndctr] ASC, [StuEnctyMsngIndctr] ASC, [StuRaceMsngIndctr] ASC, [StuBirDate] ASC, [StuBirCityName] ASC ) INCLUDE ( [StuFstNameLgl], [StuLastOrSrnmLgl], [StuMdlNameLgl], [StuIneligSnorImgrntIndctr], [StuExpctdGrdtngClYear] ) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] go CREATE NONCLUSTERED INDEX [_dta_index_Table1_5_2079723603__K23] ON [dbo].[Table1] ( [EfctvEndDate] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

    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

  • What does SQL Server execution plan show?

    - by tim
    There is the following code: declare @XmlData xml = '<Locations> <Location rid="1"/> </Locations>' declare @LocationList table (RID char(32)); insert into @LocationList(RID) select Location.RID.value('@rid','CHAR(32)') from @XmlData.nodes('/Locations/Location') Location(RID) insert into @LocationList(RID) select A2RID from tblCdbA2 Table tblCdbA2 has 172810 rows. I have executed the batch in SSMS with “Include Actual execution plan “ and having Profiler running. The plan shows that the first query cost is 88% relative to the batch and the second is 12%, but the profiler says that durations of the first and second query are 17ms and 210 ms respectively, the overall time is 229, which is not 12 and 88.. What is going on? Is there a way how I can determine in the execution plan which is the slowest part of the query?

    Read the article

  • slow SQL command

    - by Retrocoder
    I need to take some data from one table (and expand some XML on the way) and put it in another table. As the source table can have thousands or records which caused a timeout I decided to do it in batches of 100 records. The code is run on a schedule so doing it in batches works ok for the customer. If I have say 200 records in the source database the sproc runs very fast but if there are thousands it takes several minutes. I'm guessing that the "TOP 100" only takes the top 100 after it has gone through all the records. I need to change the whole code and sproc at some point as it doesn't scale but for now is there a quick fix to make this run quicker ? INSERT INTO [deviceManager].[TransactionLogStores] SELECT TOP 100 [EventId], [message].value('(/interface/mac)[1]', 'nvarchar(100)') AS mac, [message].value('(/interface/device) [1]', 'nvarchar(100)') AS device_type, [message].value('(/interface/id) [1]', 'nvarchar(100)') AS device_id, [message].value('substring(string((/interface/id)[1]), 1, 6)', 'nvarchar(100)') AS store_id, [message].value('(/interface/terminal/unit)[1]', 'nvarchar(100)') AS unit, [message].value('(/interface/terminal/trans/event)[1]', 'nvarchar(100)') AS event_id, [message].value('(/interface/terminal/trans/data)[1]', 'nvarchar(100)') AS event_data, [message].value('substring(string((/interface/terminal/trans/data)[1]), 9, 11)', 'nvarchar(100)') AS badge, [message].value('(/interface/terminal/trans/time)[1]', 'nvarchar(100)') AS terminal_time, MessageRecievedAt_UTC AS db_time FROM [deviceManager].[TransactionLog] WHERE EventId > @EventId --WHERE MessageRecievedAt_UTC > @StartTime AND MessageRecievedAt_UTC < @EndTime ORDER BY terminal_time DESC

    Read the article

  • T-SQL Table Variable Creating PHYSICAL Table!

    - by Mike
    OMG! What am I doing wrong? declare @WTF TABLE ( OrderItemId int ) SELECT TOP 20 OrderItemId as OrderItemId INTO [@WTF] FROM ac_OrderItems SELECT * FROM [@WTF] Problem A: This creates a PHYSICAL table called @WTF. WHY?? I thought this was in memory only?! Problem B: The last line of code, if I do select * from @WTF... WITHOUT the [ ], it returns NOTHING. What is the significance of the [ ]? I need serious help. I'm losing my MIND! Thanks in advance.

    Read the article

  • How to enforce this constraint in sql server

    - by Jeremy
    I have a table called city, and a table called city_city. city_city correlates two city records, so it has a fromcity_id and a tocity_id. I can enforce uniqueness on fromcity_id and and tocity_id through a unique key, but how do I enforce uniqueness so that I cant insert a record if fromcity_id and tocity_id are reversed. For example, the following records are conceptually the same: id fromcity_id tocity_id 1 100 200 2 200 100

    Read the article

  • Comma-separated value insertion In SQL Server 2005

    - by Asim Sajjad
    How can I insert values from a comma-separated input parameter with a stored procedure? For example: exec StoredProcedure Name 17,'127,204,110,198',7,'162,170,163,170' you can see that I have two comma-separated value lists in the parameter list. Both will have the same number of values: if the first has 5 comma-separated values, then the second one also has 5 comma-separated values. 127 and 162 are related 204 and 170 are related ...and same for the others. How can I insert these two values? One comma-separated value is inserted, but how do I insert two?

    Read the article

  • SQL Server Express Uninstall / Enterprise Install Issue

    - by user19049
    I need help installing SQL Server 2005 Enterprise edition.I really need to remove the current SQL Server 2005.installation that is no longer on my Add/Remove software list but yet still installed on the machine. I tried to uninstall SQL Server Express / Developer Edition but it only removed it from my Add/Remove software list. It returned immediately but did NOT actually remove the product. (I'm now in a bad state.) i tried to install SQL Server 2005 Enterprise and its says I'm blocked as all components are already installed - but they are not. How can I remove all instance of previous one and install clean Enterprise edition on my server Thanks

    Read the article

  • Best way to randomly select rows *per* column in SQL Server

    - 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

  • T-SQL: Omit/Ignore repetitive data from a specific column

    - by Dsyfa
    Hi, For my question lets consider the following sample table data: ProductID    ProductName    Price   Category 1                Apple                 5.00       Fruits 2                Apple                 5.00       Food 3                Orange               3.00       Fruits 4                Banana                 2.00       Fruits I need a query which will result in the following data set: ProductID    ProductName    Price   Category 1                Apple                 5.00       Fruits 3                Orange               3.00       Fruits 4                Banana                 2.00       Fruits As you can see ProductID 2 has been omitted/ignored because Apple is already present in the result i.e. each product must appear only once irrespective of Category or Price. Thanks

    Read the article

  • FTS: Searching across multiple fields 'intelligently'

    - by Wild Thing
    Hi, I have a SP using FTS (Full Text Search). I want searches across multiple fields, 'intelligently' ranking results based on the weights I assign. Consider a search on a view fetching data from tables: Book, Author and Genre. Now, I want the searcher to be able to do: "Ludlum Fiction", "Robert Ludlum Bourne", "Bourne Ludlum", etc. Unfortunately, the only way I have been able to do that at present is this: http://pastebin.com/fdce11ff This is pretty bad, because I am manually breaking up the search string. I know I am doing this completely the wrong way, but can't figure out the right way to search across multiple fields in FTS. Can somebody help please?

    Read the article

  • Cross Join 'n' times a table

    - by SDReyes
    It is possible to write a generic function/procedure/select/somethingElse to cross-join a table against himself 'n' times? (yes, 'n' is a given parameter : ) How would you do it? Example Having this table: Value ------- 1 2 3 cross join it 2 times, would return: Value | Value ------------------ 1 1 1 2 1 3 2 1 2 2 2 3 3 1 3 2 3 3

    Read the article

  • Group functions of outer query inside inner query

    - by superdario
    Hello, I'm using Oracle. I'm trying to compose something like this: SELECT trans_type, (SELECT parameter_value FROM transaction_details WHERE id = MAX(t.trans_id)) FROM (SELECT trans_id, trans_type FROM transactions) t GROUP BY trans_type So, I am trying to use a result of grouping inside an inner query. But I am getting the error that I cannot use a group function inside the inner query: ORA-00934: group function is not allowed here Can you offer an alternative other than resorting to another outer query?

    Read the article

  • Optimizing ROW_NUMBER() in SQL Server

    - by BlueRaja
    We have a number of machines which record data into a database at sporadic intervals. For each record, I'd like to obtain the time period between this recording and the previous recording. I can do this using ROW_NUMBER as follows: WITH TempTable AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Machine_ID ORDER BY Date_Time) AS Ordering FROM dbo.DataTable ) SELECT [Current].*, Previous.Date_Time AS PreviousDateTime FROM TempTable AS [Current] INNER JOIN TempTable AS Previous ON [Current].Machine_ID = Previous.Machine_ID AND Previous.Ordering = [Current].Ordering + 1 The problem is, it goes really slow (several minutes on a table with about 10k entries) - I tried creating separate indicies on Machine_ID and Date_Time, and a single joined-index, but nothing helps. Is there anyway to rewrite this query to go faster?

    Read the article

  • Returning partial address matches and mismatch position using L2S or SQL

    - by peter3
    I need to implement a method that takes an address split up into individual parts and returns any matching items from an address table. If no matches are found, I want to be able to return a value indicating where it failed. Each input param has a corresponding field in the table. The signature would look something like this: List<Address> MatchAddress(string zipCode, string streetName, string houseNumber, string houseLetter, string floor, string appartmentNo, out int mismatchPosition) { // return matching addresses // if none found, return the position where it stopped matching // zipCode is position 0, appartmentNo is position 5 // // an empty param value indicates "don't check" } I know I can construct the method such that I start with all the parameters, execute the query and then remove param by param (from the right side) until either a match is found or I run out of parameters, but can I construct a query that is more effective than that, i.e minimizing the number of calls to the db, maybe even as a single call?

    Read the article

  • not including null values in sql join

    - by Ashanti
    Hi, I have two tables CustomerAddress(CustomerId, City, Country) and CustomerTransactions(TransactionId, CustomerId, CustomerContact). Here are the values in the tables: For CustomerAddress: 1001, El Paso, USA 1002, Paris, France 1003, Essen, Germany For CustomerTransactions: 98, 1001, Phillip 99, 1001, NULL 100, 1001, NULL 101, 1003, Carmen 102, 1003, Carmen 103, 1003, Lola 104, 1003, NULL 105, 1002, NULL I'm trying to join both tables and have the following result set: 1001, El Paso, USA, Phillip 1002, Paris, France, (empty string) 1003, Essen, Germany, Carmen 1003, Essen, Germany, Lola It seems like a simple join but I'm having trouble coming up with the above result set. Please help. Thanks.

    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 | 191 192 193 194 195 196 197 198 199 200 201 202  | Next Page >